エクセルで郵便番号から住所自動入力する関数!変換の全手順

名簿や顧客リストを作っているとき、エクセルで郵便番号から住所自動入力する関数があれば作業が一気に終わるのにな、と手が止まってしまうことはありませんか。私も大量の住所録を前にして、1件ずつ調べて手打ちするのはさすがに無理があると感じ、効率的なやり方を徹底的に調べたことがあります。

実はエクセルには、単体で郵便番号を住所へ変えてくれる専用の標準関数は最初から入っていないんですね。そのため、WEBSERVICE関数で外部のWeb APIと連携してサクッと取得するか、日本郵便のCSVデータをダウンロードしてVLOOKUPやXLOOKUPで引き当てるか、あるいは数件だけなら関数を使わずに日本語入力のIME郵便番号辞書でスペースキーを使って変換するか、自分の環境やデータ件数に合った方法を選ぶ必要があります。

郵便番号

また、いざ数式を組んでみても、北海道などの郵便番号で先頭の0が消える問題が起きたり、ハイフンありとハイフンなしが混ざってエラーになったり、郵便番号変換ウィザードのアドインが64bit版で使えないと悩んだりするケースもすごく多いかなと思います。さらに、逆に住所から郵便番号をPHONETIC関数などで逆引きしたい場面や、1つのセルに入った郵便番号と住所や都道府県を分割したいとき、MacやGoogleスプレッドシートで同じことをしたいときなど、関連する疑問も次々に出てきますよね。

この記事では、そんな住所入力にまつわるモヤモヤをまとめて解消できるように、コピペですぐ使える具体的な数式からエラー対策、便利な応用テクニックまでを分かりやすく整理しました。ご自身の作業スタイルにぴったりの方法がきっと見つかるはずですので、ぜひ順番にチェックしてみてくださいね。

  • CSV不要のWEBSERVICE関数や大量処理に向いたVLOOKUP・XLOOKUPの具体的な数式
  • 数件の手入力に便利なIME郵便番号辞書の設定と郵便番号変換ウィザードが使えない理由
  • 先頭の0落ちやハイフンによるエラー対策と以下に掲載がない場合を消す整形テクニック
  • 住所から郵便番号を出す逆引き抽出やセル分割、Mac・Googleスプレッドシートでの代替法
スポンサーリンク
  1. エクセルで郵便番号から住所自動入力する関数の基本
    1. WEBSERVICEとAPIで取得する数式
      1. ExcelAPIを使った最短取得と列分けのコツ
      2. FILTERXML関数を組み合わせる抽出ロジック
    2. 日本郵便のCSVとVLOOKUPを使う手順
      1. 2023年6月に新設された「UTF-8形式(1レコード1行)」を選ぶのが鉄則
      2. 日本郵便CSV(全15列)の列構造と参照ルール
      3. VLOOKUP関数で都道府県・市区町村・町域を取り出す数式
    3. XLOOKUPで都道府県から町域まで一括抽出
    4. 関数不要のIME郵便番号辞書で手入力を時短
      1. スペースキーを押しても住所に変換されない原因と設定確認
    5. 郵便番号変換ウィザードが使えない理由と代替案
  2. エクセルで郵便番号から住所自動入力する関数の応用
    1. 先頭の0が消える問題とハイフンなしへの整形法
      1. 北海道や東北で先頭の「0」が消えてしまう原因と対策
      2. ハイフン(-)の揺れもまとめて吸収する最強の整形数式
    2. 以下に掲載がない場合を消去してエラーを防ぐ
    3. 住所から郵便番号を逆引き抽出する2つの方法
      1. 方法1:PHONETIC関数とASC関数でフリガナ情報から抜く(IME変換セル限定)
      2. 方法2:WEBSERVICE関数+ENCODEURL関数でWeb APIから逆引きする(コピペ住所OK)
    4. 1セルの郵便番号と住所や都道府県を分割する技
    5. GoogleスプレッドシートやMacでの対応法
      1. Googleスプレッドシートでは「IMPORTXML関数」を使う
      2. Mac版Excelでの最適解
    6. エクセルで郵便番号から住所自動入力する関数まとめ

エクセルで郵便番号から住所自動入力する関数の基本

まずは、エクセルで郵便番号から住所を自動的に呼び出すための代表的なアプローチを見ていきましょう。事前のファイル準備なしで今すぐ関数だけで完結させたい場合と、数百件以上のリストをオフラインで安定して一括処理したい場合とでは、選ぶべき関数が変わってきます。さらに、そもそも関数を組むまでもない少量の入力テクニックや、昔の定番ツールが使えなくなった背景まで、基本となる全体像を順番に解説していきますね。

WEBSERVICEとAPIで取得する数式

「日本郵便のサイトから重いCSVファイルをダウンロードして、別シートに貼り付けて……という事前準備はちょっと面倒だな」と感じる方にまず試してみてほしいのが、Windows版のExcel 2013以降で使えるWEBSERVICE関数を活用したやり方です。この関数は、インターネット上のWeb API(外部のプログラム)に郵便番号を送り、返ってきた住所データをそのままセルに表示してくれるという、とても便利な機能を持っています。

専用の変換関数が標準搭載されていないエクセルにおいて、外部ファイルを一切用意せずに数式だけで郵便番号から住所自動入力を実現できる唯一の方法と言ってもいいかもしれません。利用する無料のWeb APIにはいくつか種類がありますが、大きく分けて「ExcelAPI」というサービスを使ってWEBSERVICE関数単体でシンプルに抜く方法と、XML形式でデータを返すAPIにFILTERXML関数を組み合わせて抜く方法の2パターンがあります。

たとえば、A2セルに郵便番号(1000014 や 100-0014 など)が入力されている場合、それぞれの具体的な数式は以下のようになります。

【Web API連携で郵便番号(A2セル)から住所を取得する数式一覧】

利用するAPI・方式取得したい項目セルに入力する具体的な数式特徴とポイント
ExcelAPI(テキスト応答)住所全体(一括)=WEBSERVICE("https://api.excelapi.org/post/address?zipcode="&A2)最もシンプル。都道府県から町域までが1つのセルにまとめて返ってきます。
ExcelAPI(パーツ分割)都道府県のみ=WEBSERVICE("https://api.excelapi.org/post/address?zipcode="&$A2&"&parts=1")末尾に &parts=1 を付けると都道府県だけを取り出せます。
ExcelAPI(パーツ分割)市区町村のみ=WEBSERVICE("https://api.excelapi.org/post/address?zipcode="&$A2&"&parts=2")末尾に &parts=2 を付けると市区町村だけを取り出せます。
ExcelAPI(パーツ分割)町域のみ=WEBSERVICE("https://api.excelapi.org/post/address?zipcode="&$A2&"&parts=3")末尾に &parts=3 を付けると町域名だけを取り出せます。
XML系API+FILTERXML都道府県のみ=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&A2),"//value/@state")取得したXMLデータからXPathクエリで都道府県属性を抽出します。
XML系API+FILTERXML市区町村のみ=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&A2),"//value/@city")同様に @city を指定して市区町村を抽出します。
XML系API+FILTERXML町域のみ=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&A2),"//value/@address")同様に @address を指定して町域を抽出します。
XML系API(エラー対策済)都道府県(堅牢版)=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A2,"-",""),"0000000")),"//value/@state"),"")ハイフン除去と7桁のゼロ埋め、さらに空欄時のエラー非表示まで一括で行います。

ExcelAPIを使った最短取得と列分けのコツ

表計算ソフト向けに無償公開されている「ExcelAPI」を使うと、余計な解析関数を重ねる必要がなく、WEBSERVICE関数1つだけで本当にあっさりと住所が表示されます。URLの最後にある ?zipcode= の後ろに、アンパサンド(&)で郵便番号のセル番地をつなげるだけですね。これだけで「東京都千代田区永田町」のように一続きの文字列が返ってきます。

また、実務で名簿を作るときに「都道府県」「市区町村」「町域」を別々の列に分けて管理したい場合は、URLの末尾に &parts=1 などのオプションパラメータを付け足すだけで綺麗に分割取得できます。ExcelAPIのデータは日々更新されていて法人や商用でも利用できますが、無料枠では同一IPアドレスごとに1日1万件まで(あくまで一般的な目安)というアクセス制限が設けられています。同じオフィスのネットワーク内で何人ものスタッフが一斉に大量のデータを処理するような場面では、上限に達して取得できなくなる可能性がある点だけ頭に入れておくと安心かなと思います。

FILTERXML関数を組み合わせる抽出ロジック

もうひとつの「zip.cgis.biz」などのXML系APIを使う方法は、WEBSERVICE関数で受け取ったXML形式のデータから、必要な部分だけをFILTERXML関数で抜き出すという二段構えの仕組みになっています。数式は少し長くなりますが、第2引数に "//value/@state" や "//value/@city" といった抽出条件(XPath)を書くことで、狙った項目をピンポイントで取り出せます。もし1つのセルに住所全体をまとめたいときは、これら3つの数式を & で連結すればOKです。

なお、Microsoft 365やExcel 2021以降のスピル機能が使える環境であれば、FILTERXMLの第2引数に {"//value/@state","//value/@city","//value/@address"} と配列定数を指定することで、1回の通信で3つの項目を横のセルへ一気に展開したり、CONCAT関数で包んで1セルに結合したりするスマートな書き方もできますよ。

【WEBSERVICE関数を使う際の3つの注意点】

  • Mac版およびWeb版のExcelでは動作しない:WEBSERVICE関数とFILTERXML関数はWindows OS固有の機能を使って動くため、Mac版Excelやブラウザで使うWeb版Excelではサポートされておらずエラーになります。
  • 大量データでは動作が重くなる:セル1つにつき毎回ネット通信が発生するため、数百〜数千行にオートフィルで一気にコピーするとエクセルが固まったように重くなります。住所が表示されたら、その列をコピーして「値として貼り付け」を行い、数式をただの文字データに確定させておくのが鉄則です。
  • 初回通信時のセキュリティ警告:初めて数式を入れた際、「一部の数式で、外部関係者とのデータの送受信が行われようとしています」という黄色い警告バーが出ることがあります。その場合はコンテンツの有効化(通信の許可)を行ってください。

日本郵便のCSVとVLOOKUPを使う手順

数百件から数万件といった大量の顧客データを一気に処理したいときや、インターネットに接続できないオフライン環境、あるいはMacを使っていて先ほどのWEBSERVICE関数が動かない環境において、最も確実で王道と言えるのが日本郵便の郵便番号データ(CSV)とVLOOKUP関数を組み合わせる方法です。

手元のエクセルブック内に全国の郵便番号マスタを取り込んでしまうため、通信のタイムラグやアクセス制限を気にせず、何千件でも一瞬で変換できるのが最大の強みですね。

2023年6月に新設された「UTF-8形式(1レコード1行)」を選ぶのが鉄則

ここで、これからマスタデータを用意する方にぜひ知っておいていただきたい重要なポイントがあります。日本郵便の「郵便番号データダウンロード」ページを開くと、昔からよく使われている「読み仮名データの促音・拗音を小書きで表記するもの(ken_all.zip / KEN_ALL.CSV)」というファイルが並んでいます。

多くの解説サイトでは今でもこの従来の KEN_ALL.CSV をダウンロードするよう案内されているのですが、実はこの旧形式には「文字数が長い町域名(京都の通り名や北海道・東北の小字など)が、同じ郵便番号のまま複数行にぶつ切りで分割されている」という厄介な仕様があるんです。VLOOKUP関数は上から検索して「最初に見つかった1行目」しか持ってこられないため、分割された郵便番号を引くと、住所が途中でプツッと切れた不完全な状態で入力されてしまうトラブルが長年起きていました。

しかし、日本郵便が2023年6月の更新分から、新たに「住所の郵便番号(1レコード1行、UTF-8形式)」(utf_all.csv / utf_ken_all.csv)というデータを追加公開してくれました。こちらは複数行に分かれていたデータが1つの郵便番号につき1行へ綺麗に統合されているため(全国で約12万4千行)、VLOOKUPで引いても途中で住所が途切れる心配がありません。これから新しくシートを作るなら、間違いなくこの「1レコード1行、UTF-8形式」をダウンロードするのがおすすめです。

日本郵便CSV(全15列)の列構造と参照ルール

ダウンロードしたZIPファイルを解凍してCSVを開くと、1行目の見出し(ヘッダー)はなく、A列からO列まで全15列にわたってデータがびっしり並んでいます。VLOOKUP関数を組むうえで知っておきたい列の配置は以下の通りです。

【日本郵便CSVデータの列構成(シート名を「KEN_ALL」とした場合)】

エクセルの列先頭からの列番C列を1とした列番号入っているデータ内容関数での役割・備考
A列〜B列1〜2–団体コード・旧郵便番号(5桁)住所変換では使いません。
C列31(検索キー)郵便番号(7桁・ハイフンなし)VLOOKUPの検索範囲の左端(起点)になります。
D列〜F列4〜62〜4都道府県・市区町村・町域(カナ)カタカナのフリガナが必要な場合はここを参照します。
G列75(都道府県)都道府県名(漢字)VLOOKUPの列番号に「5」を指定して取り出します。
H列86(市区町村)市区町村名(漢字)VLOOKUPの列番号に「6」を指定して取り出します。
I列97(町域名)町域名(漢字)VLOOKUPの列番号に「7」を指定して取り出します。
J列〜O列10〜15–各種管理用フラグ(0〜6の数値)住所変換では使いません。

このCSVデータを、住所録を作っているエクセルブックの別シート(ここでは分かりやすくシート名を KEN_ALL とします)にコピーして取り込みます。不要な列を消して4列だけにスリム化してから使うやり方もありますが、今後毎月更新される日本郵便のCSVを丸ごと貼り替えてメンテナンスする手間を考えると、元の15列の構造はいじらずに、そのままC列からI列(KEN_ALL!$C:$I)を検索範囲として指定するのが一番ラクかなと思います。

VLOOKUP関数で都道府県・市区町村・町域を取り出す数式

準備ができたら、住所録シートのA2セルに「ハイフンなしの7桁郵便番号」が入っているとして、隣のセルにVLOOKUP関数を入力していきます。C列を1列目として数えるため、G列(都道府県)は「5」、H列(市区町村)は「6」、I列(町域)は「7」が列番号になります。

【VLOOKUP関数による住所自動入力の数式パターン】

  • 都道府県のみを取り出す:
    =VLOOKUP(A2,KEN_ALL!$C:$I,5,FALSE)
  • 市区町村のみを取り出す:
    =VLOOKUP(A2,KEN_ALL!$C:$I,6,FALSE)
  • 町域名のみを取り出す:
    =VLOOKUP(A2,KEN_ALL!$C:$I,7,FALSE)
  • 1つのセルに住所全体を結合して出す:
    =CONCAT(VLOOKUP(A2,KEN_ALL!$C:$I,5,FALSE),VLOOKUP(A2,KEN_ALL!$C:$I,6,FALSE),VLOOKUP(A2,KEN_ALL!$C:$I,7,FALSE))
  • 未入力時や該当なしのエラー(#N/A)を空白にする実務向け数式:
    =IF(A2="","",IFERROR(VLOOKUP(A2,KEN_ALL!$C:$I,5,FALSE),""))

数式を下の行へコピーする際、検索範囲である KEN_ALL!$C:$I の部分にドルマーク($)を付けておかないと、参照する場所がズレてエラーになってしまいます。もしドルマークの意味やF4キーでの固定方法に少し不安がある場合は、エクセルの計算式でセルを固定してズレを防ぐ基本と応用もあわせて確認しておくと、VLOOKUPのミスがグッと減るはずです。

XLOOKUPで都道府県から町域まで一括抽出

もしお使いのエクセルがMicrosoft 365やExcel 2021以降のバージョンであれば、先ほどのVLOOKUP関数よりもさらに直感的で圧倒的に便利なXLOOKUP関数を使うことができます。私自身、新しい環境で住所録を作るときは、もう迷わずこちらのXLOOKUP一択になっています。

VLOOKUP関数だと「C列から数えてG列は何番目だっけ……1、2、3、4、5番目か」と指で列番号を数える必要がありましたよね。しかも、1つのセルに住所をまとめたいときは、同じようなVLOOKUPを3回も繰り返し書いて連結しなければなりませんでした。

その点、XLOOKUP関数なら「探す列(C列)」と「取り出したい列(G列〜I列)」を直接指定するだけで済みますし、何より素晴らしいのが「スピル機能」によって1つの数式で都道府県・市区町村・町域の3列を同時にパッと埋められるところです。

【XLOOKUP関数によるスマートな住所抽出数式】

  • 都道府県だけを単一セルに出す:
    =XLOOKUP(A2,KEN_ALL!$C:$C,KEN_ALL!$G:$G,"")
  • 1つのセルに数式を入れて右隣2列まで(計3列)一気に自動展開させる(スピル):
    =XLOOKUP(A2,KEN_ALL!$C:$C,KEN_ALL!$G:$I,"")
  • 都道府県〜町域を1つのセルに結合してスッキリ出す(最推奨):
    =CONCAT(XLOOKUP(A2,KEN_ALL!$C:$C,KEN_ALL!$G:$I,""))

特に3つ目の =CONCAT(XLOOKUP(A2,KEN_ALL!$C:$C,KEN_ALL!$G:$I,"")) という書き方は本当におすすめです。戻り値の範囲として KEN_ALL!$G:$I と3列分をまとめて指定し、それをそのままCONCAT関数で包んであげるだけで、たった1回の検索処理で「東京都千代田区永田町」と繋がった住所が出力されます。さらに、第4引数に ""(空白)を指定しておけば、郵便番号が未入力のときや見つからないときにも自動で空白にしてくれるため、わざわざIFERROR関数を重ねる必要すらありません。

なお、別シートのマスタデータを参照して自動反映させる仕組みや、VLOOKUPとXLOOKUPの詳しい違いについては、エクセルで別シートへ自動反映させる究極のガイドでも掘り下げて解説していますので、シート間連携をもっとスムーズにしたい方は参考にしてみてくださいね。

【XLOOKUP関数を使う際の互換性に関する注意】
XLOOKUP関数はExcel 2019以前の古いバージョンには搭載されていません。もし作成したファイルを取引先や他部署の古いパソコンで開く可能性がある場合は、数式が #NAME? エラーになって動かなくなってしまいます。ファイルを共有する相手の環境が分からないときは、念のため従来のVLOOKUP関数で組んでおくか、作業後に「値として貼り付け」で数式を消してから渡すようにしましょう。

ちなみに、約12万行ある日本郵便のCSVをシートに直接貼り付けると、どうしてもエクセルのファイルサイズが大きくなって動作が重くなりがちです。もし毎月何万件もの名簿を処理するようなヘビーな業務であれば、エクセルの「データ」タブにあるPower Query(パワークエリ)の「クエリのマージ」機能を使って、シート上にマスタを展開せずにバックグラウンドで郵便番号を結合する手法や、VBA(マクロ)から無料の郵便番号検索API(zipcloudなど)を呼び出してセルに直接「値」を書き込む手法へとステップアップするのも非常に効果的ですよ。

関数不要のIME郵便番号辞書で手入力を時短

ここまで関数を使った自動入力の方法を見てきましたが、「そもそも手元にデータがあるわけじゃなくて、今から10件だけ新規で住所を手打ちしたいんだよね」という場面もありますよね。そんなときにわざわざCSVをダウンロードしたり数式を組んだりするのは、かえって時間がかかってしまいます。

数件から十数件程度のちょっとした手入力なら、パソコンの日本語入力システム(Microsoft IMEやGoogle日本語入力、Macの日本語入力)に最初から入っている「郵便番号辞書」を使ったスペースキー変換が圧倒的に早いです。

【IME郵便番号辞書で郵便番号から住所へ変換する手順】

  1. 日本語入力モード(全角の「あ」の状態)にします。
  2. エクセルのセルに、ハイフン付きで郵便番号を入力します(例:100ー0001)。
  3. そのままキーボードの「スペースキー(変換キー)」を押します。
  4. 変換候補の中に「東京都千代田区千代田」と住所が出てくるので、Enterキーで確定します。

たったこれだけの操作で、普通の漢字変換とまったく同じ感覚で郵便番号がパッと住所に変わります。Google日本語入力を使っている場合は、郵便番号を打った時点で予測変換(サジェスト)に住所が表示されるので、Tabキーで選ぶだけで入力できてさらに快適です。

スペースキーを押しても住所に変換されない原因と設定確認

もしこのやり方で住所が出てこない場合、一番よくある原因は「ハイフンを入れずに7桁の数字だけを打っている」か「半角英数モード(A)のまま入力している」のどちらかです。特にWindows標準のMicrosoft IMEでは、真ん中にハイフンを入れて打たないと郵便番号だと認識してくれないため注意してくださいね。

全角・ハイフンありで打っても変換候補に住所が出ないときは、IMEの設定で郵便番号辞書がオフになっている可能性があります。Windowsなら画面右下のタスクバーにある「あ」または「A」のアイコンを右クリックし、「設定」→「学習と辞書」を開いて、「システム辞書」の中にある「郵便番号辞書」のスイッチがオンになっているかチェックしてみてください。Macの場合も、「システム設定」→「キーボード」→「入力ソース」の日本語設定から変換候補の設定を見直すことで使えるようになります。

郵便番号変換ウィザードが使えない理由と代替案

昔からエクセルをよく使っている方の中には、「たしかマイクロソフト公式の『郵便番号変換ウィザード』っていうアドインを入れれば、ボタンひとつで一括変換できたはずだけど、どこからダウンロードするの?」と探している方も多いかなと思います。

結論からお伝えすると、現在「郵便番号変換ウィザード」は新規で導入することができず、最新のエクセル環境では事実上使えないツールになってしまっています。これには明確な2つの理由があります。

  • 2021年7月以降に公式の配布ページが閉鎖された:マイクロソフトの公式ダウンロードセンターからファイル(excelzipcode7japan)が削除され、正規のルートで入手することができなくなりました。
  • 現在の主流である64bit版Excelに非対応:このアドインは古い32bit版のエクセル向けに作られたプログラムのため、現在のMicrosoft 365やExcel 2021などで標準となっている64bit版のエクセルでは一切動作しません。

仮にネット上のアーカイブサイトなどから古いインストーラーを探し出してきたとしても、Windowsのセキュリティ警告(SmartScreen)にブロックされたり、64bit版のOfficeでは組み込めずにエラーになったりします。このアドインを動かすためだけにOffice全体をアンインストールして32bit版を入れ直すのは現実的ではありませんので、きっぱりと諦めて、ここまでご紹介してきた「WEBSERVICE関数」や「日本郵便CSV+XLOOKUP / VLOOKUP関数」、あるいは大量処理向けの「Power Query」へ切り替えるのが、現在のエクセルにおける安全で確実な解決策になります。

エクセルで郵便番号から住所自動入力する関数の応用

郵便番号1

基本の数式が分かったところで、ここからは実務で必ずと言っていいほどぶつかる「関数を入れたのにエラーになる!」「変な文字がくっついてくる!」といったトラブルの解決法と、さらに作業をラクにする応用テクニックに入っていきます。逆引きやセル分割、Mac・スプレッドシートでの代替手段まで網羅していますので、困っている項目からチェックしてみてください。

先頭の0が消える問題とハイフンなしへの整形法

「VLOOKUPの数式は絶対に合っているはずなのに、なぜか #N/A エラーばっかり出る……」「東京や大阪は住所が出るのに、北海道や青森の郵便番号だけエラーになる!」というのは、郵便番号からの自動入力で最も多いつまずきポイントです。私も最初、北海道のデータだけごっそり抜け落ちていてかなり焦りました。

この原因は、エクセルのお節介な自動変換機能による「先頭の0落ち問題」と、「ハイフンのあり・なし不一致」の2つにあります。

北海道や東北で先頭の「0」が消えてしまう原因と対策

北海道(00〜09)や東北地方(01〜09)などの郵便番号は、一番最初の数字が「0」から始まりますよね。ところが、エクセルの標準設定のセルに 0600001 とそのまま入力すると、エクセルが「あ、これは数値だな」と判断して、先頭の0を勝手に省略し 600001 という6桁の数字に変えてしまうんです。さらに厄介なことに、日本郵便からダウンロードしたCSVファイルをそのままダブルクリックしてエクセルで開いたときにも、マスタ側の先頭の0が勝手に消えてしまい、7桁の文字列と6桁の数値が食い違って検索エラーを引き起こします。

これを防ぐには、以下のような対策をとるのが確実です。

  • これから郵便番号を入力する場合:あらかじめ郵便番号を入力する列の表示形式を「文字列」にしておくか、先頭に半角のアポストロフィ(')を付けて '0600041 のように入力します。
  • 日本郵便のCSVを取り込む場合:CSVファイルを直接ダブルクリックで開くのではなく、エクセルの「データ」タブ→「テキストまたはCSVから」を選び、読み込み設定で郵便番号列のデータ型を「テキスト」に指定して取り込みます。
  • すでに先頭の0が消えて6桁になっているデータを関数で引く場合:後述する TEXT(A2,"0000000") を使って、数式の中で強制的に7桁の文字へ復元してあげます。

ハイフン(-)の揺れもまとめて吸収する最強の整形数式

もうひとつのエラー原因がハイフンです。日本郵便のCSVマスタ(C列)には「ハイフンなしの7桁数字(例:1000001)」が入っています。そのため、住所録側のセルに 100-0001 とハイフンが入っていたり、全角の数字や長音符(ー)が混ざっていたりすると、別物とみなされて一致しません。

元データのハイフンを Ctrl + H の置換機能で一括削除してから関数を組むのもひとつの手ですが、実務で一番おすすめしたいのは、検索関数の第1引数(検索値)の中に、全角の半角化・ハイフン除去・7桁ゼロ埋めをすべて自動で行うクレンジング数式を組み込んでしまう方法です。目的別の整形方法を一覧にまとめました。

【郵便番号のハイフン整形・先頭0落ち対策の方法まとめ】

やりたいこと・目的具体的な数式または操作手順仕組みと実務上のメリット
数式内で入力揺れを完全吸収して検索する(最推奨)検索値の部分を
TEXT(SUBSTITUTE(ASC(A2),"-",""),"0000000")
にする
ASCで全角を半角にし、SUBSTITUTEでハイフンを消し、TEXTで6桁数値を7桁に直します。VLOOKUPやXLOOKUPの検索値に入れれば、どんな打ち方の郵便番号でも一発でヒットします。
元データのハイフンを一括で消す(操作)列を選択 → Ctrl + H(置換)→ 検索文字列に -、置換後を空欄にして「すべて置換」関数を使わずに元のセルから直接ハイフンを消去する一番手軽な操作です。
データは7桁のまま見た目だけハイフンを付けるセルの書式設定 → 表示形式「その他」→「郵便番号」(またはユーザー定義で 000-0000)セルの中身はハイフンなしの数値のまま、画面上の見た目だけを 100-0001 にできます。
実データとしてハイフンや〒マークを付ける=TEXT(A2,"000-0000")
または ="〒"&LEFT(A2,3)&"-"&RIGHT(A2,4)
他システムへのCSV取り込み用などで、文字データ自体にハイフンを含めたいときに使います。
縦書き印刷用にハイフンの向きを揃える=SUBSTITUTE(SUBSTITUTE(A2,"-","‐"),"-","‐")半角ハイフン等を日本語の「全角ハイフン(‐)」に統一します。

たとえばXLOOKUP関数と組み合わせるなら、=CONCAT(XLOOKUP(TEXT(SUBSTITUTE(ASC(A2),"-",""),"0000000"),KEN_ALL!$C:$C,KEN_ALL!$G:$I,"")) と書いておけば、A2セルに「060-0001」と入っていようが「600001」と入っていようが、ピタッと北海道札幌市中央区の住所を引き当ててくれます。

また、表の最後にある「縦書き印刷用のハイフン」も実は盲点になりやすいポイントです。封筒の宛名ラベルなどで文字を縦書きにしたとき、半角ハイフンや全角マイナスが混ざっていると、そこだけ棒が横に寝てしまってカッコ悪くなるんですよね。縦書き時の記号のズレ対策については、エクセルで文字を縦にする!縦中横やズレ解消の完全ガイドで詳しく解説していますので、宛名印刷まで行う方はぜひチェックしてみてください。

以下に掲載がない場合を消去してエラーを防ぐ

日本郵便のCSVデータを使って関数で住所を取り出してみると、ときどき「東京都千代田区以下に掲載がない場合」といった、なんだか不思議な日本語がくっついた住所が表示されることがあります。初めてこれを見ると「えっ、数式を間違えたかな?」とびっくりしてしまいますよね。

これは関数のミスではなく、日本郵便のCSVデータの仕様によるものです。町域名が存在しない地域や、市区町村のすぐ後ろに番地が続く地域のI列(町域名)に、目印として「以下に掲載がない場合」という文字が最初からそのまま登録されているんですね。

もちろんこのままでは宛名ラベルや名簿として使えませんので、SUBSTITUTE関数を使って、この不要な文字列だけを自動的に空欄("")へ置き換えて消してしまいましょう。

【「以下に掲載がない場合」を自動で消去する数式例】

  • XLOOKUP+CONCATと組み合わせる場合:
    =SUBSTITUTE(CONCAT(XLOOKUP(A2,KEN_ALL!$C:$C,KEN_ALL!$G:$I,"")),"以下に掲載がない場合","")
  • 町域名の単一セル(VLOOKUP)と組み合わせる場合:
    =SUBSTITUTE(VLOOKUP(A2,KEN_ALL!$C:$I,7,FALSE),"以下に掲載がない場合","")

このように数式全体を SUBSTITUTE(元の数式, "以下に掲載がない場合", "") でぐるっと囲んであげるか、あるいは最初に日本郵便のCSVシートを取り込んだ段階で、マスタ側のI列に対して Ctrl + H の置換機能を使い、「以下に掲載がない場合」をすべて空欄に置換して消しておくのもスッキリしておすすめですよ。

【豆知識:大口事業所個別郵便番号や複数町域の扱いについて】
通常の住所用CSV(utf_all.csv 等)には、大企業や官公庁、大規模ビルなどに割り当てられている「大口事業所個別郵便番号」は含まれていません。もし法人の個別番号も関数で引きたい場合は、日本郵便のサイトから別途「事業所の個別郵便番号データ」をダウンロードしてマスタに足すか、事業所番号にも対応しているWeb APIを使う必要があります。また、1つの郵便番号に複数の町域が紐づいている一部地域では、郵便番号だけでは町域が1つに絞り込めないケースがある点も知っておくと安心です。

住所から郵便番号を逆引き抽出する2つの方法

郵便番号から住所を出す作業と同じくらいよくあるのが、「すでに住所だけは入力されているリストがあるんだけど、ここから逆に郵便番号を自動入力(逆引き)できないかな?」というニーズです。この逆引きには、その住所データがどうやって入力されたものなのかによって、2つのアプローチを使い分けることができます。

方法1:PHONETIC関数とASC関数でフリガナ情報から抜く(IME変換セル限定)

もしその住所が、先ほどご紹介した「IMEの郵便番号辞書」を使って(郵便番号を打ってスペースキーで変換して)手入力されたセルであれば、PHONETIC関数とASC関数を組み合わせる定番の裏技が使えます。

郵便番号から変換して入力したセルには、エクセルの裏側の「ふりがな情報」として、最初に入力した全角の郵便番号がそのまま記憶されているんですね。そのため、隣のセルに以下の数式を入れるだけで、隠れていた郵便番号をポロッと取り出すことができます。

【IME変換で入力された住所(A2セル)から郵便番号を取り出す数式】

  • ハイフンありの半角で取り出す: =ASC(PHONETIC(A2))
  • ハイフンなしの7桁で取り出す: =SUBSTITUTE(ASC(PHONETIC(A2)),"-","")

ただし、このやり方には明確な弱点があります。それは「郵便番号から変換して入力したセルでしか使えない」ということです。普通に「とうきょうと…」と読み仮名でタイピングしたセルではカタカナのフリガナが出てきてしまいますし、Webサイトや別システムからコピー&ペーストしてきた住所データにはそもそもフリガナ情報が入っていないため、元の漢字の住所がそのまま表示されてしまいます。

方法2:WEBSERVICE関数+ENCODEURL関数でWeb APIから逆引きする(コピペ住所OK)

「外部からコピペした既存の住所リストから、一括で郵便番号を逆引きしたい!」という場合に大活躍するのが、前半でも登場した「ExcelAPI」の逆引き機能とWEBSERVICE関数、そして日本語をURL用の文字に変換するENCODEURL関数を組み合わせる方法です(Windows版Excel 2013以降対応)。

【任意の住所文字列(A2セル)からWeb APIで郵便番号を逆引きする数式】
=WEBSERVICE("https://api.excelapi.org/post/zipcode?address="&ENCODEURL(A2))

この数式を使えば、コピペしたデータであっても、さらには都道府県が省略されていたり京都の通り名が入っていたりする多少の表記ゆれがある住所であっても、API側が解析して7桁の郵便番号を返してくれます。なお、別の有名な郵便番号APIである「zipcloud」は郵便番号から住所を引く片方向専用で、住所からの逆引き機能は持っていないため、逆引きではこのExcelAPIのエンドポイント(/post/zipcode)を使うのがポイントです。

もしオフライン環境で既存の住所から逆引きしたい場合は、日本郵便CSV側で都道府県〜町域を連結した検索列を作り、INDEX関数やMATCH関数、LEFT関数などを組み合わせて一致する行を探すか、Power Queryで結合するといった少し高度な設計が必要になります。

1セルの郵便番号と住所や都道府県を分割する技

他社から受け取った名簿などを開いたとき、「1つのセルの中に『〒100-0001 東京都千代田区…』と郵便番号も住所も全部ごちゃ混ぜに入っていて使いにくい!」「住所から都道府県名だけを別セルに抜き出したい!」と困ることもよくありますよね。こうしたデータの切り分けも、文字列操作の関数を使えば一瞬で綺麗に分割できます。

【住所データの分割・抽出・結合に使える便利関数まとめ】

やりたい処理・分割の目的入力する具体的な数式数式の仕組みとポイント
「〒100-0001 住所…」が入ったB2セルから郵便番号のみを抜き出す〒付き:=LEFT(B2,9)(または =MID(B2,1,9))
数字とハイフンのみ:=MID(B2,2,8)
先頭にある「〒+3桁+-+4桁」は必ず9文字になる法則を利用して、左から文字数を指定して切り出します。
郵便番号を抜いた残りの住所部分のみを抜き出す=SUBSTITUTE(B2,C2,"")
(※C2に抽出した郵便番号がある場合)
元の全体文字列(B2)から、先ほど抜き出した郵便番号(C2)を空白に置換して消すことで、住所だけを残します。
住所(D2セル)から都道府県名のみを正確に抜き出す=IF(MID(D2,4,1)="県",LEFT(D2,4),LEFT(D2,3))47都道府県のうち4文字なのは「神奈川県・和歌山県・鹿児島県」の3つだけ。左から4文字目が「県」なら4文字、違えば3文字を抜く黄金パターンです。
都道府県を抜いた残りの市区町村以下を抜き出す=SUBSTITUTE(D2,E2,"")
(※E2に抽出した都道府県がある場合)
元の住所(D2)から、抽出した都道府県名(E2)を空白に置換して市区町村以下を取り出します。
複数セルに分かれた住所(C2:F2)をスペース区切りで1セルに結合する=TEXTJOIN(" ",TRUE,C2:F2)第2引数を TRUE にすると、マンション名などの空欄セルを自動で飛ばして、余計なスペースを作らず綺麗に1行へ繋げてくれます。

特に都道府県の抽出数式 =IF(MID(D2,4,1)="県",LEFT(D2,4),LEFT(D2,3)) は、覚えておくとどんな住所リストの整理にも使い回せるすごく便利なテクニックです。左から4文字目が「県」かどうかを見るだけで、3文字の都・道・府・県と4文字の3県(神奈川・和歌山・鹿児島)を100%正確に振り分けられるなんて、最初に考えた人は本当にすごいなと感心してしまいます。なお、郵便番号と住所の間にスペースが入っているデータなら、エクセルの「データ」タブにある「区切り位置」機能を使って一括で列を分けることもできますよ。

GoogleスプレッドシートやMacでの対応法

最後に、Windows版のエクセル以外の環境、つまりGoogleスプレッドシートやMac版のエクセル(Excel for Mac)で郵便番号から住所を自動入力したい場合のやり方を整理しておきます。

Googleスプレッドシートでは「IMPORTXML関数」を使う

GoogleスプレッドシートにはエクセルのWEBSERVICE関数がないため、代わりにWeb上のデータを取得するIMPORTXML関数と「ExcelAPI」を組み合わせて数式を組みます。第2引数のXPathクエリにピリオド "." を指定するのがコツです。

【Googleスプレッドシート用の自動取得・逆引き数式(A2セル参照)】

  • 郵便番号から住所を自動入力する:
    =IMPORTXML("https://api.excelapi.org/post/address?zipcode="&A2,".")
  • 住所から郵便番号を逆引きする:
    =IMPORTXML("https://api.excelapi.org/post/zipcode?address="&ENCODEURL(A2),".")

【スプレッドシートでIMPORTXML関数を使う際の重大な注意点】
スプレッドシートのIMPORTXML関数は、自分のパソコンからではなく「Googleのサーバー(共通のIPアドレス)」を経由してAPIへアクセスする仕組みになっています。そのため、ExcelAPI側の「同一IPにつき1日1万件まで」という制限枠を、全国の他のスプレッドシートユーザー全員と共有することになり、たった数十件の処理でもすぐに上限エラー(読み込みエラー)になりやすいという弱点があります。

そのため、Googleスプレッドシートで数十件〜数百件以上のデータを安定して処理したいときは、素直に日本郵便のCSVを別シートにインポートして =XLOOKUP(A2,'KEN_ALL'!C:C,'KEN_ALL'!G:I) で引くか、拡張機能のGoogle Apps Script(GAS)を使って UrlFetchApp.fetch で「zipcloud」などのAPIを呼び出し、セルにテキストとして書き込むスクリプトを組むのが確実かなと思います。

Mac版Excelでの最適解

前半でも少し触れた通り、Mac版のエクセルではWindows固有機能を使うWEBSERVICE関数やFILTERXML関数が動作しません。ですので、Macユーザーの方が郵便番号から住所を一括変換したい場合は、迷わず「日本郵便のUTF-8形式CSVをダウンロードして、XLOOKUP関数(またはVLOOKUP関数)で参照する」か、「Power Query(Microsoft 365のMac版で利用可能)」を使うアプローチを選んでくださいね。また、数件の手入力であれば、Mac標準の日本語入力でもハイフン付き郵便番号からのスペースキー変換がバッチリ使えますよ。

エクセルで郵便番号から住所自動入力する関数まとめ

今回は、エクセルで郵便番号から住所自動入力する関数と、それにまつわるエラー対策や応用ワザを幅広く見てきました。最後に、どの方法を選べばいいか迷ったときの判断基準をもう一度シンプルに振り返っておきましょう。

【状況別!郵便番号から住所を自動入力するベストな選び方】

  • 数件〜十数件を手打ちしたいだけなら:関数は使わず、全角・ハイフンありで打ってスペースキーを押す「IME郵便番号辞書」が最速。
  • Windows版で数十件をCSV準備なしでサクッと出したいなら:=WEBSERVICE("https://api.excelapi.org/post/address?zipcode="&A2) を使って、表示されたら「値として貼り付け」で確定させる。
  • 数百件以上の大量データ、またはMac・オフライン環境なら:日本郵便の「1レコード1行、UTF-8形式」のCSVをダウンロードし、XLOOKUP や VLOOKUP で引き当てる(王道かつ最安定)。
  • エラー(#N/A)が出るなら:検索値を TEXT(SUBSTITUTE(ASC(A2),"-",""),"0000000") で包んで、ハイフンと先頭0落ちをまとめて解消する。

最初は「専用の関数がないなんて不便だな」と感じるかもしれませんが、一度自分の作業スタイルに合った数式のテンプレートを作ってしまえば、次からはコピー&ペーストするだけで何百件もの住所入力があっという間に片付くようになります。

なお、本記事でご紹介した外部Web APIのアクセス上限数や日本郵便のCSVデータ仕様などは、あくまで執筆時点での一般的な目安となります。サービスの仕様変更やデータの更新が行われる場合もありますので、業務で本格的に導入される際や正確な情報については、日本郵便や各API提供元の公式サイトをご確認くださいね。また、社内の重要な顧客データベースの設計やセキュリティに関わる最終的な判断は、システム管理部門や専門家にご相談いただくことをおすすめします。

ぜひ今回ご紹介した関数や整形テクニックを取り入れて、面倒な住所録づくりの時間をグッと短縮してみてくださいね!

タイトルとURLをコピーしました