日本郵便の公式データとINDEX+MATCH関数を組み合わせることで、エクセルの住所データから「市区町村」だけを正確に抽出する方法をご紹介します。市区町村以降のデータを抽出する方法も解説しているので、ぜひ参考にしてみてください。
住所から「市区町村」だけ抽出する
【手順1】住所一覧をダウンロードする
- 「郵便番号データダウンロード」にアクセスし、「住所の郵便番号（CSV形式）」をダウンロードします。
- ダウンロードした圧縮（zip）ファイルを展開（解凍）し、中の「KEN_ALL.CSV」ファイルを開きます
【手順2】住所一覧をエクセルにコピーする
- データ量が多いので、必要な部分（G~I列）だけ残してほかの列は削除しておきます。（A列＝都道府県、B列＝市区町村 の状態）
- シートタブ（KEN_ALL）の上で右クリックし、「移動またはコピー」を選択します。
- 移動先ブックから、抽出したい住所が入っているファイルを選びます。
- 「コピーを作成する」にチェックを入れ、「OK」を押します。
【手順3】住所一覧から市区町村を抽出する
- 市区町村を抽出したいセル（例：C2）に、以下の数式を入力して下にコピーします。
- 例：
=INDEX(KEN_ALL!B:B,MATCH(TRUE, ISNUMBER(SEARCH( KEN_ALL!A:A& KEN_ALL!B:B, A2)), 0))
- 例：
💡 ポイント：
KEN_ALL!B:B：コピーした住所一覧シートの「市区町村（B列）」を指定します。KEN_ALL!A:A：コピーした住所一覧シートの「都道府県（A列）」を指定します。A2：元となる住所が入っているセルを指定します。
※Excelのバージョンよって、Enter で確定した際にエラーが出る場合があります。その場合はキーボードの Ctrl + Shift + Enter を押して数式を確定します。
📖 数式の解説
- SEARCH関数：「都道府県と市区町村を結合したデータ」が、元住所（A2セル）の中に含まれているかを判定します。
- ISNUMBER関数：元住所の中に「都道府県と市区町村を結合したデータ」が見つかれば TRUE、見つからなければ FALSE を返します。
- MATCH関数：TRUEになった位置（行番号）を返します。
- INDEX関数：市区町村の範囲から、元住所と市区町村が一致した行のデータを抽出します。
「市区町村」以降の住所を抽出する
SUBSTITUTE関数を使い、住所から「都道府県」と「市区町村」を削除します。
例：=SUBSTITUTE(SUBSTITUTE(A2, C2, ""), D2, "")
SUBSTITUTE関数の引数：(検索対象のテキスト, 検索文字列, 置換文字列, [出現回数])
📖 住所から「都道府県」だけ抽出する方法は「住所から都道府県を抽出！」の記事をご覧ください。