【VLOOKUP関数とXLOOKUP関数】Excelを使って郵便番号から住所を調べる：Tech TIPS
会員名簿や顧客リストの住所を正規化したい場合、Excelを使って郵便番号から住所を調べると簡単だ。手動入力した住所の誤りなどを簡単にチェックできる。その方法を紹介しよう。
この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。
対象：Office 2013／2016／2019／365
顧客リストの郵便番号または住所の誤りをチェックする古くから管理している顧客リストには、入力時のミスがあったり、市町村合併前の住所が含まれていたりすることがある。こうした住所は、最新の郵便番号データを使って誤りを正さないと、大事な案内などが届かなくなる。
会員名簿や顧客リストのような住所を含む表を「Microsoft Excel（エクセル）」で扱う場合、郵便番号から住所の一部を推定できると便利なことも多い。例えば、郵便番号から調べた住所の一部と手動入力した住所を比較することで、間違いを発見できることがある。
また、郵便番号を記載することで都道府県名を省略できるため、入力された住所が省略形になっていることもある。郵便番号は、地域に対して割り振られるため、完全な住所にはなり得ないが、都道府県、市町村などの大きな地域は郵便番号から判定することができる。このため、リストを都道府県別に分類することは可能だ。また、ソフトウェア開発は必要になるが、郵便番号から住所の一部を検索できるようにすれば、住所入力を簡易化することも不可能ではない。
ここでは、日本郵便が公開している郵便番号データを使って、Excelで郵便番号から住所の一部を検索する方法を解説する。また、Excelの新機能であるXLOOKUP関数を取り上げ、合わせて従来のVLOOKUPとの比較を行う。XLOOKUPは、Office 365（サブスクリプション版）で最新のアップデートを適用していれば利用できる。一方、Office 2016など買い切り版では利用できない点に注意してほしい。
なお、Excelには、Office 2007／2010向けに郵便番号変換ウィザードなどが提供されていたことがあったが、既に配布は終了しており、Office 2013以降のバージョンでは利用できない（インターネットにあるMicrosoft以外のダウンロードサイトにあるものは、マルウェアが含まれている可能性が高いので注意）。また、IMEにも郵便番号から住所への変換辞書が含まれることがあるが、後述するように郵便番号に関しては、毎月何らかの更新があり、配布タイミングによっては、旧データを使うことになる可能性がある。
郵便番号データを用意する
下記の日本郵便のサイトから郵便番号データをダウンロードする。CSV形式ファイルを入手できるので、これをそのままExcelに読み込ませることができる。だが、郵便番号データは、幾つか種類があり、適切なものをダウンロードする必要がある。まずは、この郵便番号データに関して説明しておく。
- 郵便番号データダウンロード（日本郵便）
日本郵便が提供する郵便番号データには、大きく分けて住所用と事業所用の2種類がある。前者は全国の地域に対して割り振られているもので、後者は大量の郵便物を扱う日本郵便が言うところの「大口事業所」に対して割り振られている郵便番号だ。
一般に住所を扱うデータの中には、個人でありながら事業所となる勤務先の住所を登録している可能性や、工場敷地内の寮といった可能性もあるため、原則、両方を検索対象にする必要がある。もちろん処理するデータの中に、事業所の郵便番号が絶対に含まれないというのであれば、住所用の郵便番号データだけを扱うことも可能だ。ただし、ここでは両方を対象としているとして解説を進める。
個人用の郵便番号データは大きく3種類あり
住所用の郵便番号データは、複数のファイルで提供されている。1つは、読み仮名データの形式の違いである。「読み仮名データの促音・拗音を小書きで表記しないもの」と「読み仮名データの促音・拗音を小書きで表記するもの」である。小書きで表記しない場合は「北海道」の読み仮名が「ホツカイドウ」になり、小書き表記する場合は「ホッカイドウ」になる。
郵便番号データには、住所の読み仮名がローマ字になっているものもある。このために、都合3種類の形式があるのだが、住所の読み仮名を利用しないのであれば、どれを使っても問題はない。ここでは、片仮名を小書きするタイプのファイルを使うと仮定する。
また、住所用は都道府県別と全国のファイルがある。Excelを含め最近のPCでは、サイズの大きな全国のファイルでも問題なく読み込めるため、全国用のものを利用する。事業所用のデータには、地域別ファイルはなく1種類だ。
郵便番号データは毎月更新
郵便番号は、毎月、月末頃に更新が行われる（原則平日）。市町村の統廃合など、これまでのところほぼ毎月何らかの更新がある。大口事業所も、一日当たりの郵便量など一定の条件を満たせば、誰でも郵便番号の割り当てを受けられ、逆に条件を満たさなくなれば、割り当てが廃止される。このため、やはり毎月更新が行われているようだ。
この更新には、「新規追加」と「廃止」があり、それぞれ別ファイルになっている。全国の郵便番号データでも1MB程度の容量なので、最新ファイルを全国まとめてダウンロードしてもたいした負荷にはならない。
郵便番号は「文字列」として読み込む
1つ郵便番号を扱う場合に注意が必要なのは、郵便番号データの保持方式である。郵便番号は先頭に「0（ゼロ）」を持つため、これを数値として保持してしまうと、先頭の「0」がない状態となる。文字列として保持すると、先頭に「0」を持つことが可能になる。
例えば、住所用の全国データ（CSVファイル）に入っている札幌市の郵便番号「0010000」は、数値としてセルに格納すると「10000」となる。Excelは、直接CSVファイルを読み込んだ場合、数値のみからなる文字列は、数値に自動変換するため、郵便番号データにある先頭部分の「0」が全て消える。ただし、読み込み時にファイル拡張子をCSVからTXTに変更すると、読み込み時に「テキストファイルのインポートウィザード」（区切り位置の機能と同じ）が起動され、セルに読み込む前に列が数値なのか文字列なのかを指定することが可能だ。
住所用の郵便番号データをExcelに読み込む（1）拡張子が「.csv」だとExcelは数字のみの列を自動的に数値に変換する。しかし、拡張子を「.txt」に変更しておくと、読み込み時にウィザードが起動し、列が文字列なのか、自動変換なのかなどを指定できるようになる。
住所用の郵便番号データをExcelに読み込む（3）郵便番号の列を文字列に指定したことで、先頭の「0」も表示される。数値として読み込むと、先頭の「0」が表示されなくなり、札幌市などの郵便番号が分かりにくくなってしまう。
ただし郵便番号では、先頭の「0」を省略した場合でも、他の郵便番号と一致することはないため、郵便番号を全て数値のまま扱ってもほとんど問題は起きないはずである。ただし、既に入力したデータで郵便番号を文字列として扱っていて、先頭にゼロが入っているような場合、CSVファイルの郵便番号を数値として読み込んでしまうと、比較計算で一致しない可能性がある。郵便番号を数値として扱うか、文字列として扱うかは全てのExcel作業で統一しておいた方がよい。
郵便番号CSVファイルの構造
郵便番号CSVファイルには、ヘッダ部分が含まれておらず、そのフィールド構成は、別途Webページに紹介されている。
- 郵便番号データの説明（日本郵便）
- 大口事業所個別番号データの説明（日本郵便）
簡単にまとめると、住所用の郵便番号データは、下表のようになっている。
| ＃ | 列 | 内容 | データ形式 |
|---|---|---|---|
| 1 | A | 該当地域の全国地方公共団体コード | 半角数字（JISX0401、X0402） |
| 2 | B | （旧）郵便番号（5桁） | 半角数字 |
| 3 | C | 郵便番号（7桁） | 半角数字 |
| 4 | D | 都道府県名 | 半角カタカナ |
| 5 | E | 市区町村名 | 半角カタカナ |
| 6 | F | 町域名 | 半角カタカナ |
| 7 | G | 都道府県名 | 漢字 |
| 8 | H | 市区町村名 | 漢字 |
| 9 | I | 町域名 | 漢字 |
| 10 | J | 町域が2つ以上の郵便番号で表される | 「1」は該当、「0」は該当せず |
| 11 | K | 小字ごとに番地が起番 | 「1」は該当、「0」は該当せず |
| 12 | L | 丁目を有する町域 | 「1」は該当、「0」は該当せず |
| 13 | M | 1つの郵便番号で2つ以上の町域を表す | 「1」は該当、「0」は該当せず |
| 14 | N | 更新の表示 | 「0」は変更なし、「1」は変更あり、「2」廃止 |
| 15 | O | 変更理由 | 「0」は変更なし、「1」は市政・区政・町政・分区・政令指定都市施行、「2」は住居表示の実施、「3」は区画整理、「4」は郵便区調整等、「5」は訂正、「6」は廃止（廃止データのみ使用） |
| 住所用の郵便番号データのフォーマット | |||
また事業所用の郵便番号データは、下表のようになっている。
| ＃ | 列 | 内容 | データ形式 |
|---|---|---|---|
| 1 | A | 該当地域の全国地方公共団体コード | JISX0401、X0402/半角数字 |
| 2 | B | 大口事業所名（カナ） | 半角カタカナ |
| 3 | C | 大口事業所名（漢字） | 漢字 |
| 4 | D | 都道府県名（漢字） | 漢字 |
| 5 | E | 市区町村名（漢字） | 漢字 |
| 6 | F | 町域名（漢字） | 漢字 |
| 7 | G | 小字名、丁目、番地など（漢字） | 漢字 |
| 8 | H | 郵便番号（大口事業所個別番号） | 半角数字 |
| 9 | I | 旧郵便番号 | 半角数字 |
| 10 | J | 取扱局 | 漢字 |
| 11 | K | 個別番号の種別の表示 | 「0」大口事業所、「1」私書箱 |
| 12 | L | 複数番号の有無 | 「0」複数番号無し、「1」複数番号を設定している場合の個別番号の1、「2」複数番号を設定している場合の個別番号の2、「3」複数番号を設定している場合の個別番号の3 |
| 13 | M | 修正コード（1byte） | 「0」修正なし、「1」新規追加、「5」廃止 |
| 事業所用の郵便番号データのフォーマット | |||
郵便番号データでは、現在の郵便番号は3列目、都道府県の漢字表記は7列目である。また、10列目以降は、同一の町域に複数の郵便番号がある、1つの郵便番号が複数の町域に対応するなどの特殊な条件を示す論理値（「1」が「True」、「0」が「False」）になっている。都道府県レベルの処理ならば気にする必要はないが、住所を細かく見ていく場合には、これらのフラグの値にも注意する必要がある。郵便番号から住所を調べる場合に特に注意するのは、下表の3点だ。
| 列 | 注意点 |
|---|---|
| 11列 | 小字ごとに番地が起番されている町域 |
| 12列 | 丁目を有する町域の場合 |
| 13列 | 1つの郵便番号で2以上の町域を表す場合 |
| 郵便番号から住所を調べる際に注意する | |
これらは、郵便番号から調べることができる町域以下に複数の住所名が含まれていることを示す。住所の照合を行うような場合には、このフラグを見て、複数の候補を見つける必要がある。また、事業所データの構造では、7桁郵便番号よりも前の列（列番号の小さい側。PC画面のExcelウィンドウを見たときには左側。以下同様）に住所や事業所名などがあるため、VLOOKUPを使うならば郵便番号の列が住所などより前の列になるように移動させる必要がある（VLOOKUP関数の使い方については、Tech TIPS「ExcelのVLOOKUP関数の最も簡単な使い方」参照のこと）。
VLOOKUPを使って郵便番号から都道府県を求める
まずは、どのバージョンのExcelでも利用できるVLOOOKUP関数を使って、郵便番号から都道府県を検索させてみる。例としては都道府県だが、結果列の位置を変更することで、他の情報も取り出せる。
事前準備として、日本郵便のサイトからデータをダウンロードして、Excelに読み込ませ、XLSXファイルとして保存しておく。XLSX形式とすることで、次回以降の読み込みを高速化できる。これを「YUBIN.xlsx」とする。保存場所は、適当で構わないが、Excelは外部参照を絶対パスで記憶するため、できれば、郵便番号検索を利用するブックと同じフォルダがよい。
また、前述のように郵便番号データは毎月更新されるものであるため、処理対象のデータとは別のブックファイルにしておいた方が取り扱いはラクになり、複数のブックで行う作業で、郵便番号データを共有して同じ数式を利用できる。
セルの範囲に名前を付ける
郵便番号データをExcelに読み込ませたら、参照を簡略化するため列Cから列Oまでを選択し、「zip_addr」という名前を定義しておく（セルの範囲に名前を定義する方法は、Tech TIPS「Excelの『名前の定義』でセルの範囲に名前を付ける」参照のこと）。このようにすることで後からの参照が楽になる。
同じようにC列に「zip」、G列に「prf」、H列に「city」などの名前を付けておくとよい。名前を付けるのは必須ではないが、後で参照するときにシート名が入るため、セル範囲のままだと名前が煩雑になることと、「C」や「D」といった列の番号では、対象データが分かりにくく、エラーのときに対処が面倒になる。LOOKUP系のコマンドを使う場合や、他のブックから参照される場合には、名前を付けておくと引数が分かりやすくなる。
全国データで郵便番号から都道府県を調べてみよう
全国データのみを使って、郵便番号から都道府県を調べてみる。検索対象のデータは、「TEST.xlsx」に入っているとする（前述の「YUBIN.xlsx」は開いたままにしておく）。このとき郵便番号は、「C列」にあるとする。なお、この「TEST.xlsx」と前述の「YUBIN.xlsx」は同じフォルダに置いた方がよい。
セル「D2」に都道府県を表示させるには、以下のような数式を使う。最初の引数が「$C2」と列のみ絶対参照になっているのは、このあと右側のセルに数式をオートフィルなどでコピーして市区町村などの住所などを検索させることを想定している。このようにするとオートフィルで右側のセルに数式をコピーしたときに同じセルを検索対象のままにすることができる。
=VLOOKUP($C2,YUBIN.xlsx!zip_addr,5,FALSE)
後は、必要に応じてセル「D2」と同じ数式を下にコピーすれば、都道府県を検索できる。なお、「行5」と「行6」が「#N/A」となっているのは、ここに事業所用の郵便番号が入っていたため、住宅用の郵便番号データから住所を探すことができなかったためだ。これについては後で対処する。
XLOOKUPを使って郵便番号から都道府県を求める
同じものをXLOOKUP関数で記述してみよう。
=XLOOKUP($C2,YUBIN.xlsx!zip,YUBIN.xlsx!prf)
同じように名前を使った。この例では、VLOOKUP関数もXLOOKUP関数もそうそう大きな違いはない。
どちらの場合でも、調査対象の郵便番号に事業所が含まれていると、結果は「#N/A」エラーになってしまう。ただしXLOOKUPの場合には、見つからなかった場合に返す文字列を4番目の引数で指定できる。後で、エラーになった行をフィルターで抜き出すような場合は、適当な文字列を指定しておくと便利だ。
=XLOOKUP($C2,YUBIN.xlsx!zip,YUBIN.xlsx!prf,,"No DATA")
XLOOKUP関数を使った場合の結果XLOOKUPでは、結果列を直接セル参照として指定できるだけでなく、検索列に該当データがなかったときに返す文字列も定義できる。ここでは「No Data」という文字列を返しているので、事業所の郵便番号の場合に#N/Aエラーにならない。
なお、XLOOKUPに関しては、以下にヘルプページがある。
- XLOOKUP関数 - Office サポート（Microsoft）
事業所に対応する
結果を「#N/A」エラーにしないためには、全国データで検索ができなかったら、事業所データの検索結果を返すようにすればよい。そこで、次に事業所に対応しよう。まずは、CSVファイルをExcelで読み込む。読み込んだら、ワークシートを先ほどの郵便番号ブック（YUBIN.xlsx）にコピー（または移動）する。
Copyright© Digital Advantage Corp. All Rights Reserved.
ITmediaはアイティメディア株式会社の登録商標です。