全Ver対応Excel 2016でも可

住所から郵便番号を逆引きする方法（最長一致検索）

⚠ このテクニックは「完璧」ではありません

巷の簡易的なVLOOKUP方式やワイルドカード方式と比べてかなり高い精度を出せますが、日本の住所体系の複雑さにより100%の正解は保証できません。必ず少数テストから始め、結果を目視確認しながら段階的に展開してください。完全に一致しない・おかしな結果が出る場合のために、あえてエラーや警告が出る安全装置も後半で紹介します。

💡 やりたいこと

手元にある住所データから、郵便番号を自動で逆引きしたい。VBAは使わず、関数だけで。

シート構成（2つだけ）

① 郵便番号簿シート ── A列＝郵便番号、B列＝住所

郵便番号簿

	A（郵便番号）	B（住所）
1	郵便番号	住所
2	1830022	東京都府中市宮西町
3	7260004	広島県府中市府川町
4	6048141	京都府京都市中京区泉正寺町
5	9300106	富山県富山市高木東
6	9300106	富山県富山市高木西

日本郵便の utf_all.csv をもとに、都道府県＋市区町村＋町域を結合した列。作り方は後半「郵便番号簿の準備」で詳しく説明します。

② 入力シート ── A列＝住所（検索したいデータ）

入力シート郵便番号簿

	A（住所）	B（郵便番号）	C（一致数）
1	住所	郵便番号	一致文字数
2	東京都府中市宮西町２−２４	1830022	10
3	広島県府中市府川町３１５	7260004	9
4	京都府京都市中京区泉正寺町３３４	6048141	11

💡 まずはこのシンプルな構成で動作を理解してください。 正規化ヘルパー列・安全装置などの発展的な内容は、基本が動いてから後半で追加します。

最長一致検索のアイデア

📜 なぜ「最長一致」なのか？
郵便番号は町域レベルまでしか設定されていないのに、入力住所には番地・建物名まで入っている。完全一致では絶対にヒットしない。かといって短い文字列で探すと大雑把すぎて別の場所がヒットしてしまう。だから「一番長く一致するもの＝一番詳しいもの」を探す。

右端から1文字ずつ「食べていく」イメージ

15文字東京都府中市宮西町２−２４→該当なし

14文字東京都府中市宮西町２−２４→該当なし

…（省略）

10文字東京都府中市宮西町２−２４→★ ヒット！ → 1830022

番地・建物名は右から削る過程で自動的に捨てられます。これが最長一致検索の強みです。

逆に短い方から探すと…（最短一致がダメな理由）

「東」     → ヒット（東京都？東大阪市？） ← 全然違う！
「東京」   → ヒット（東京都全域？）       ← まだ曖昧

→ 短い方から見つけると大雑把すぎて使えない。
→ だから長い方から探して「一番詳しくマッチしたもの」を採用する！

数式は「1つのセルの中」でこれを一瞬でやる

入力住所
A2セル → 先頭N文字を切り出し
LEFT(A2, N) → 郵便番号簿と比較
約12万行を一括照合 → 最長一致の行を特定
MAX で最大N → 郵便番号を返す
INDEX

数式（たった2つ）

前提
・郵便番号簿シート名＝「郵便番号簿」
・郵便番号簿のA列＝郵便番号、B列＝住所（2行目からデータ開始）
・入力シートのA列＝検索したい住所

B2 ── 郵便番号を返す

=INDEX(郵便番号簿!A$2:A$125000,
  MATCH(
    LEFT(A2,
      MAX(
        (LEFT(A2,LEN(郵便番号簿!B$2:B$125000))=郵便番号簿!B$2:B$125000)
        *LEN(郵便番号簿!B$2:B$125000)
      )
    ),
    郵便番号簿!B$2:B$125000,
    0
  )
)

C2 ── 一致文字数（確認用）

=MAX(
  (LEFT(A2,LEN(郵便番号簿!B$2:B$125000))=郵便番号簿!B$2:B$125000)
  *LEN(郵便番号簿!B$2:B$125000)
)

バージョン	確定方法
Excel 2019以前	Ctrl+Shift+Enter（数式バーに { } が付く）
Excel 2021 ／ 365	普通に Enter

数式の分解（6パーツ）

❶ LEN(郵便番号簿!B$2:B$125000)→郵便番号簿の各住所の文字数を配列で取得

❷ LEFT(A2, ❶)→入力住所の先頭からその文字数ぶん切り出す

❸ ❷ = 郵便番号簿!B$2:B$125000→一致するか？（TRUE/FALSE の配列）

❹ ❸ × ❶→一致 → 文字数／不一致 → 0

❺ MAX(❹)→最大の文字数＝最長一致

❻ LEFT → MATCH → INDEX→最長一致文字列を復元 → 行番号 → 郵便番号を返す

1つのセルで何が起きているか ── 処理トレース

A2 = 「東京都府中市宮西町２−２４」の場合

❶

LEN → 郵便番号簿の各住所の文字数を取得。「東京都府中市宮西町」は10文字、「広島県府中市府川町」は9文字…。

❷

LEFT → 入力住所から各文字数ぶん先頭を切り出す。10文字なら「東京都府中市宮西町」。

❸

=比較 →「東京都府中市宮西町」＝「東京都府中市宮西町」→ TRUE！

❹❺

×LEN → MAX → TRUE×10=10。全配列の最大値=10。

❻

LEFT(A2,10)→「東京都府中市宮西町」→ MATCH → INDEX → 1830022

ここから先：精度を上げる・安全装置をつける

上記の基本数式だけでも動作しますが、実務で使うには以下の追加対策が重要です。

郵便番号簿シートの準備（utf_all.csv）

ダウンロード

日本郵便の公式サイトから「住所の郵便番号（1レコード1行、UTF-8形式）（CSV形式）」をダウンロードします。

ダウンロード先：https://www.post.japanpost.jp/zipcode/dl/utf-zip.html

なぜ utf_all.csv？
従来の KEN_ALL.CSV は町域名が長い場合に複数行に分割される悪名高い仕様でした。UTF-8版では1レコード1行に統一されており、前処理が不要です。

⚠ そのまま開くと文字化けする

utf_all.csv はUTF-8で保存されています。Excelでダブルクリックして開くと Shift-JIS として解釈されて文字化けします。

方法① データの取得から読み込む（推奨）

Excelで新規ブック → 「データ」タブ → 「テキストまたはCSVから」

utf_all.csv を選択して「インポート」

プレビュー画面で「元のファイル」を「65001: Unicode (UTF-8)」に変更 → 日本語が正しく表示されることを確認

「読み込み」で完了

方法② メモ帳で開いてANSIで保存し直す

utf_all.csv をメモ帳で開く（右クリック →「プログラムから開く」→ メモ帳）

「ファイル」→「名前を付けて保存」→ 文字コードを「ANSI」にして保存

保存したファイルをExcelで普通に開く → 文字化けしない

B列（住所）の作り方

utf_all.csv のG列（都道府県名）、H列（市区町村名）、I列（町域名）を結合します。括弧は除去し、「以下に掲載がない場合」は町域を空にします。

=G2&H2&IF(I2="以下に掲載がない場合","",IF(FIND("（",I2&"（")<=LEN(I2),LEFT(I2,FIND("（",I2)-1),I2))

A列にはC列（郵便番号）をそのまま配置。シート名を「郵便番号簿」に変更。

入力住所の正規化（精度を上げるための前処理）

入力住所と郵便番号簿の表記が食い違っていると一致しません。主な対策：

対策	数式例	注意
半角→全角統一	`=JIS(A2)`	郵便番号簿が全角なので合わせる
旧字体→新字体	`=SUBSTITUTE(…,"飛弾","飛騨")`	必要な置換だけ。過剰にやらない
ヶ/ケ/が統一	`=SUBSTITUTE(…,"ヶ","ケ")`	問題が出た地名だけ個別対応
都道府県名の補完	手動または別数式	なしだと同名市町村で誤マッチ

⚠ 「大字」除去は事故が多い
愛知県知多郡阿久比町「植大」のように、地名の末尾が「大」で次に「字」が来ると「大字」と誤認識して地名が壊れます。「大字」「字」の除去は最初から入れず、必要性が確認できてから慎重に追加してください。

正規化を入れる場合は入力シートに列を追加して、B列の数式のA2を正規化列に変更します。

安全装置 ── 問題のある行を自動で検知する

「以下に掲載がない場合」がフォールバック先になる

郵便番号簿のB列に「愛知県名古屋市東区」のような都道府県＋市区町村だけの短い行が残っています（「以下に掲載がない場合」の行で町域を空にしたため）。町域レベルで一致しなかった住所は、この短い行にフォールバックしてヒットします。

郵便番号簿シート（イメージ）

	A（郵便番号）	B（住所）
…	4610000	愛知県名古屋市東区
…	4610001	愛知県名古屋市東区泉
…	4610002	愛知県名古屋市東区代官町

入力：愛知県名古屋市東区○○町１−２（○○町が表記揺れで一致しない場合）

愛知県名古屋市東区○○町  ← 一致しない（表記揺れ）
  …右から削っていく…
愛知県名古屋市東区        ← ★ フォールバック行にヒット！

→ 郵便番号は返る（4610000）が、町域まで到達していない
→ 「何か問題がある」と判定列で検知できる

判定列の追加（D列）

前提：判定列で「以下に掲載がない場合」を検出するには、郵便番号簿シートに元のI列（町域名）が残っている必要があります。utf_all.csv を読み込んだ際にI列を削除せず残しておいてください。

=IF(C2=0,"一致なし",
  IF(C2<6,"要確認（一致"&C2&"文字）",
    IF(INDEX(郵便番号簿!I$2:I$125000,
         MATCH(LEFT(A2,C2),郵便番号簿!B$2:B$125000,0)
       )="以下に掲載がない場合",
      "要確認（市区町村止まり）",
      ""
    )
  )
)

安全装置つき完成イメージ

入力シート（D列追加）

	A	B	C	D
1	住所	郵便番号	一致数	判定
2	東京都府中市宮西町２−２４	1830022	10
3	広島県府中市府川町３１５	7260004	9
4	愛知県名古屋市東区○○町…	4610000	8	要確認（市区町村止まり）

💡 D列の使い方：D列が空白でない行だけフィルタで抽出すれば「要確認行の一覧」が得られます。正規化を修正すると判定が消え、改善状況が可視化されます。

IFERRORで隠してはいけない

⚠ IFERROR(…,"") は逆効果
エラーを空白で隠すと「問題なし」に見えてしまいます。エラーは隠すのではなく、判定列で原因を追跡するのが正しいアプローチです。

既知の落とし穴

落とし穴	内容	対策
同名市町村	府中市（東京/広島）、伊達市（北海道/福島）など	都道府県名を必ず補完
京都の通り名	「上る/下る」の独自体系で町域と一致しない	参考値として扱い目視確認必須
大口事業所	utf_all.csv に含まれない	別ファイルで対応
「大字」除去事故	阿久比町「植大」+「字」→「大字」と誤認識	最初は除去しない。段階的に
1郵便番号に複数住所	9300106＝高木東 / 高木西	住所→郵便番号は問題なし。逆方向はFILTERで全件抽出

パフォーマンスの注意

🔶 この数式は重い ── 1セルにつき約12万回の比較が走ります。

工夫	効果
手動計算モード	数式タブ→「手動」。F9で必要なときだけ再計算
都道府県フィルタ	郵便番号簿を都道府県別に分割で大幅高速化
値貼り付け	結果確認後、B〜C列をコピー→値のみ貼り付け

推奨ワークフロー

STEP 1

郵便番号簿の準備
utf_all.csv を文字化けなく読み込む → A列＝郵便番号、B列＝住所（結合済み） → シート名を「郵便番号簿」に

STEP 2

10〜20件で試行
手動計算モードでB2・C2に数式を入力 → F9で再計算 → 結果を目視確認

STEP 3

正規化を調整
一致しない行があれば正規化列を追加して表記を統一 → 再計算で確認

STEP 4

安全装置を追加して全件展開
判定列を追加 → 全行にコピー → 「要確認」行をフィルタで抽出 → 正規化を修正 → 繰り返す

STEP 5

値貼り付けで確定
結果をコピー → 値のみ貼り付け（計算コストをゼロに）

まとめ

項目	内容
基本アイデア	入力住所の先頭と郵便番号簿を照合し、最も長く一致した行の郵便番号を採用
使用関数	INDEX・MATCH・MAX・LEFT・LEN（全バージョン共通）
郵便番号簿	A列＝郵便番号、B列＝住所（都道府県＋市区町村＋町域を結合）
入力シート	A列＝住所、B列＝郵便番号（数式）、C列＝一致文字数（数式）
確定方法	2019以前：Ctrl+Shift+Enter ／ 2021・365：Enter
精度の目安	都道府県名あり＋正規化済みで90〜95%程度

バージョン互換性

バージョン	対応	確定方法
Excel 2007〜2019	○	Ctrl+Shift+Enter（CSE配列数式）
Excel 2021	○	Enter のみ（スピル対応）
Microsoft 365	○	Enter のみ（スピル対応）