郵便番号から住所を検索 | エクセルマクロ(VBA)実践蔵(じっせんぐら)

トップ項目一覧

前の項目 - フローチャート入力補助
次の項目 - Excelマクロ(VBA)入門編 - エラーの対処

エクセル関数で郵便番号から住所を求める方法はあります。ただ、使い勝手が完ぺきではないため、注意も必要です。

問題点を考慮して完ぺきを求めるには、マクロが必要になります。

ひとまず、エクセル関数を使用して、郵便番号から住所を求めてみたい方は、こちらを参照してください。

エクセル関数を使用して、住所から郵便番号を検索する方法はこちらを参照してください。

次の項目は、マクロで求める方法になります。

データベースは、郵便局のホームページからダウンロードできるようになっていましたので、そちらをエクセルマクロ有効ブックのシートに貼り付けてあるので、そこから情報を拾っています。

郵便局データベース　全国一括

郵便番号から住所を検索するマクロの使い方

ひとつの住所検索を行う場合

マクロの入っているブックを開き、作業するエクセルブックを開く。作業するエクセルブックで、郵便番号を入力し、そのセルを選択した状態で、「Ctrl」「Shift」「G」を同時に押すと、マクロが起動します。

①郵便番号のセルは、マクロを起動したときのセルのアドレスが自動で入ります。
　　修正する場合は、エクセルシートの郵便番号が記述されているセルをクリックした後に、
　　ダイアログの上側の「選択中セル」を選択してください。
　②エクセルシートの住所を出力するセルをクリックし、ダイアログの下側の「選択中セル」
　　をクリックする。
　③実行ボタンをクリックする。

郵便番号は、７桁の数値、又は、ハイフンありの表記でも実行されます。

桁数が足りない場合は、先頭に0が入っている場合として、考慮します。
(例)0600000の場合、先頭の0のない、600000となっていても、0600000として検索されます。
但し、郵便番号下４桁の先頭の0は、省略できません。(例)245-53 は検索できません。

複数の住所検索を行う場合

郵便番号開始セルから下に向かって、郵便番号検索を行います。
出力する住所の列を指定してから実行をクリックします。
(開始セルの列を住所を出力する列に指定しないでください。)

住所候補が複数ある場合は、ダイアログが表示されます。

「リスト表示をパスする」にチェックをつけてから実行すると、リスト表示が必要な郵便番号検索をパスします。「住所が空白のセルのみ検索」にチェックをつけると、住所データが入っていいない郵便番号を対象に、再検索することができるため、リスト表示が必要な住所だけをまとめて再実行することができます。

セル指定タブでひとつの住所検索して、住所が見つからない場合、「見つかりませんでした。」というダイアログが表示されますが、列指定タブの複数の住所検索を行う場合に、住所が見つからなかった場合は、ダイアログは表示させずに、次に進みます。

エクセル（マクロ有効）ブックのダウンロード

下記からダウンロードしたエクセルファイルを起動し、コンテンツの有効化を行う。

マクロの有効化手順については、こちらも参考にしてください。

郵便番号から住所を検索 マクロ有効ファイル(xlsm)のダウンロード

エクセル関数で郵便番号から住所を検索する方法

エクセル関数で郵便番号から住所を求めることもできます。ウェブ上からデータを取得するため、オフラインの環境では使用できません。エクセル2013以降に対応されたWEBSERVICE関数を使用するのですが、Excel Onlineでは非対応になります。

エクセル関数を使用するときの問題は、一つの郵便番号に対して、複数の住所が割あたっている場合になります。そのため、一覧になっている郵便番号に対して、一意に住所が決まらず、この候補を入力規則のリストにするためには、別にリストを生成するセル領域を確保する必要があります。

使い方の例です。
A2セルに郵便番号「9300927」と入力しておき、別のセルに下記のエクセル関数を入力すると

=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&$A2),"/ZIP_result/ADDRESS_value/value/@state"),"")

該当する都道府県として、「富山県」と表示されます。下記の図では、C2セルに記述しています。

都道府県を表す「@state」の部分を、「@city」とすると、市区名を取得でき、「@address」とすると町村名を取得できます。これらを連結させると住所になります。

上記図と同じように表示させるには、D2セルに下記を入力します。
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&$A2),"/ZIP_result/ADDRESS_value/value/@city"),"")

E2セルに下記を入力しします。
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&$A2),"/ZIP_result/ADDRESS_value/value/@address"),"")

セルF2に下記を記述すると、セルの文字列の連結になります。
=C2&D2&E2

G2セルの一致数を表示するには、「/ZIP_result/result/@result_values_count」で取得できます。

=IFERROR(@FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&$A2),"/ZIP_result/result/@result_values_count"),"")

郵便番号「9300927」のときは、１が返ります。ここで、すべての郵便番号で住所が一意に決まるのであれば、問題はないのですが、同じ郵便番号7桁でも住所が異なる地域が存在します。

郵便番号「4380078」で調べると、住所の一致数が17と表示されます。上記図のG3セル参照。

候補すべてを表示する場合、町村名で分かれるれるため、下記の関数をオートフィルで下に向かって17セルに適応させると郵便番号が一致する町村名の17件がそれぞれ表示されます。
図では、セルG3に下記を記述し、G4～G19セルまでオートフィルで埋めています。

=INDEX(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&$A$2),"/ZIP_result/ADDRESS_value/value/@address"),ROW(A1))

※関数式内にセルA1を使用していますが、セルの内容を取得しているのではなく、INDEX関数で何番目の候補を取得するかという番号を取得するために用いており、オートフィルによってA2,A3…と行数が変わることによって、何番目を取得してくるかという番号が変わります。そのため、A列は参照していません。

一覧をセルに出力した後であれば、データの入力規則リストを作成できるのですが、エクセル関数入力だけで、データの入力規則を作成できなかったので、複数候補がある場合を考慮すると一覧で管理するのは、むずかしいと思います。

一致数が1の郵便番号のみであれば、この方法で一覧表にすることはできます。一致数が2以上と表示されたセルに条件付き書式を設定しておいて、あとから求める等の手順が必要になります。

郵便番号から住所を検索　エクセル関数版(xlsx)のダウンロード

エクセル関数で住所から郵便番号を検索する方法

郵便番号から住所を求めた方法と同様にして、住所から郵便番号を求めることも可能です。

(使用例：)A2セルに住所「神奈川県横浜市戸塚区上矢部町」と入力しておき、
別のセルに下記のエクセル関数を入力すると

=FILTERXML(WEBSERVICE("http://groovelab.asia/zipcode/search.xml?keyword="&$A2),"//count")

検出数として、「5」が表示されますが、一番先頭の郵便番号をひとまず表示させます。
セルのC2には、下記を入力します。
=@INDEX(FILTERXML(WEBSERVICE("http://groovelab.asia/zipcode/search.xml"&"?keyword="&A2),"/response/results/address/item/zipcode"),ROW(A1))

「zipcode」の部分を「ken」に変更すると、県名が取得で、「address1」に変更すると、市区名を取得でき、「address2」に変更すると、町村名が取得できます。確認用に表示方法を記載しています。

上記、画像のように表示させるには、
D2セルに下記を入力し、
=@INDEX(FILTERXML(WEBSERVICE("http://groovelab.asia/zipcode/search.xml"&"?keyword="&$A2),"/response/results/address/item/ken"),1)
E2セルに下記を入力します。
=INDEX(FILTERXML(WEBSERVICE(""http://groovelab.asia/zipcode/search.xml?keyword="&$A2),"/response/results/address/item/addres"),1)
F2セルに下記を入力します。
=INDEX(FILTERXML(WEBSERVICE(""http://groovelab.asia/zipcode/search.xml?keyword="&$A2),"/response/results/address/item/addres2"),1)

注意が必要なのは、上記の図セルB2で検出数が5件と表示される部分です。検出された5件は「上矢部町」「上矢部町６９０」「上矢部町１９６８」「上矢部町２３８４」「上矢部町２３３６」の5件であり、郵便番号が異なるのですが、確かにすべて「神奈川県横浜市戸塚区上矢部町」を含んでいます。

郵便番号を求めるのに必要な情報が正しく入力できているのであれば、検出された先頭の郵便番号で正しいといえます。

住所の入力不足があると、別の郵便番号となってしまう場合もあるという点を注意する必要があります。

住所から郵便番号を検索　エクセル関数版(xlsx)のダウンロード

前の項目 - フローチャート入力補助
次の項目 - Excelマクロ(VBA)入門編 - エラーの対処