郵便番号の一覧から住所を自動入力したいときは、郵便番号データを検索用の表として用意し、XLOOKUP関数で住所を呼び出す方法が最も確実です。Excel単体には、日本の郵便番号を住所へ変換する標準関数はありません。しかし、一度住所マスターを作れば、毎回の入力作業を大幅に減らせますよ。
結論：XLOOKUP関数で郵便番号から住所を自動表示できます
この方法では、H列に検索したい郵便番号、I列にその郵便番号に対応する住所を登録します。たとえば、A2に「1000001」と入力すると、B2に「東京都千代田区千代田」のような住所を表示できます。
なお、日本の郵便番号には複数の町名が対応するケースがあります。そのため、郵便番号だけで番地まで含めた完全な住所を一意に出せるとは限りません。まずは都道府県・市区町村・町域までを自動入力し、番地や建物名は別途入力する運用が実務ではスムーズです。
郵便番号から住所を変換する準備手順
関数を使う前に、郵便番号と住所を対応させた「郵便番号マスター」を用意しましょう。社内で管理している顧客住所一覧を使ってもよいですが、全国の住所を対象にするなら日本郵便が公開している郵便番号データを利用すると便利です。
- 日本郵便の郵便番号データをダウンロードします。
- ダウンロードしたCSVファイルをExcelで開きます。
- 郵便番号の列と、都道府県・市区町村・町域の列を確認します。
- 空いている列に、都道府県・市区町村・町域をつなげた住所列を作成します。
- 検索用の郵便番号列と住所列を、作業中のブックまたは別シートに配置します。
住所をつなげる列は、たとえば都道府県がB列、市区町村がC列、町域がD列の場合、E2セルに次の式を入れます。
=B2&C2&D2
入力後、セル右下のフィルハンドルをダブルクリックすると、データ末尾まで数式をコピーできます。郵便番号データは件数が多いため、最初にExcelテーブル化しておくと後の管理が楽になります。
郵便番号は文字列としてそろえるのがコツです
郵便番号は計算に使う数字ではなく、検索用の番号です。そのため、先頭のゼロが消えないように文字列として扱いましょう。たとえば「0123456」が「123456」になると、検索結果が見つかりません。
- 郵便番号を入力する列を選択します。
- ［ホーム］タブの［数値の表示形式］から［文字列］を選択します。
- すでに入力済みの場合は、セルを編集してEnterキーを押し直すか、郵便番号を貼り直します。
- ハイフンあり・なしを統一します。関数で検索するなら「1000001」のようにハイフンなしにそろえると扱いやすいです。
XLOOKUP関数で住所を表示する方法
ここでは、入力用シートのA列に郵便番号を入力し、B列に住所を表示する例で進めます。郵便番号マスターは「郵便番号マスター」シートにあり、A列が郵便番号、B列が住所という想定です。
- 住所を表示したいB2セルをクリックします。
- 次の数式を入力します。=XLOOKUP(A2,郵便番号マスター!$A$2:$A$100000,郵便番号マスター!$B$2:$B$100000,"該当なし",0)
- Enterキーを押します。
- A2セルに郵便番号を入力し、住所が表示されることを確認します。
- B2セル右下のフィルハンドルをダブルクリックして、下の行にも数式をコピーします。
XLOOKUP関数の最後にある「0」は完全一致検索です。郵便番号は一部一致ではなく、必ず完全一致で探すようにしましょう。「該当なし」は、マスターにない郵便番号を入力したときに表示するメッセージです。空白にしたい場合は、"該当なし"の部分を""に変更してください。
Excel 2019以前ではVLOOKUP関数を使います
XLOOKUP関数が使えないExcelでは、VLOOKUP関数でも同じように検索できます。B2セルには次の数式を入力してください。
=IFERROR(VLOOKUP(A2,郵便番号マスター!$A$2:$B$100000,2,FALSE),"該当なし")
VLOOKUP関数は、検索する郵便番号列が検索範囲の一番左にある必要があります。また、最後の引数は必ずFALSEにして完全一致検索にしてください。TRUEや省略のままだと、近い番号を誤って返す可能性があります。
ハイフン付き郵便番号をそのまま検索する方法
入力する郵便番号が「100-0001」のようにハイフン付きで、マスター側が「1000001」のようにハイフンなしの場合は、SUBSTITUTE関数でハイフンを取り除いてから検索できます。
=XLOOKUP(SUBSTITUTE(A2,"-",""),郵便番号マスター!$A$2:$A$100000,郵便番号マスター!$B$2:$B$100000,"該当なし",0)
この式なら、利用者がハイフンを入力しても、入力しなくても検索できるようになります。住所録を複数人で使う場合は、入力ルールのばらつきを吸収できるのでおすすめです。
うまくいかない場合のチェックポイント
「該当なし」や「#N/A」になる
- 郵便番号の桁数を確認します。7桁になっているか、先頭のゼロが消えていないかを見ましょう。
- ハイフンの有無を確認します。入力側とマスター側で形式を統一するか、SUBSTITUTE関数を使います。
- 全角数字が混ざっていないか確認します。「１０００００１」のような全角数字は、半角数字と一致しません。
- 郵便番号マスターの範囲を確認します。検索範囲の最終行が実際のデータより短いと、後半の郵便番号を検索できません。
- 最新の郵便番号データか確認します。市町村合併や住所表記の変更があるため、定期的な更新が必要です。
住所が途中までしか表示されない
郵便番号データでは、町域が「以下に掲載がない場合」や「一円」などの表記になっていることがあります。また、1つの郵便番号に複数の町域が登録されている場合もあります。完全な住所として使う前に、対象地域の検索結果を確認しましょう。顧客宛名や請求書で使う場合は、番地・建物名を入力する列を別に持たせると安心です。
数式が表示されてしまい、住所に変換されない
セルに数式そのものが表示されるときは、セルの表示形式が文字列になっている可能性があります。対象セルを選択し、［ホーム］タブから表示形式を［標準］に変更してください。その後、F2キーを押してからEnterキーを押すと数式が再計算されます。
さらに時短する便利ワザ
郵便番号マスターをテーブル化する
マスター表を選択してCtrl＋Tを押すと、Excelテーブルに変換できます。データを追加しても検索範囲が自動で広がるため、数式の「$A$2:$A$100000」のような固定範囲を毎回修正する手間を減らせます。
テーブル名を「郵便番号表」、列名を「郵便番号」「住所」にした場合は、次のように読みやすい数式も使えます。
=XLOOKUP(A2,郵便番号表[郵便番号],郵便番号表[住所],"該当なし",0)
住所を確定値に変える
関数で出した住所を他の人に渡す前や、マスターを更新する予定がある前には、値として貼り付けて確定しておくと安心です。
- 住所が表示されているセル範囲を選択します。
- Ctrl＋Cでコピーします。
- 同じ場所で右クリックします。
- ［値の貼り付け］を選択します。
郵便番号から住所を自動変換する仕組みは、一度作れば住所録、発送リスト、顧客管理表などに繰り返し使えます。まずは少ない件数の表でXLOOKUP関数を試し、問題なく検索できることを確認してから本番データへ広げていきましょう。
