Chiquilin Site■18.02.08_Excel：関数を使って郵便番号を住所に変換する
ChiquilinSite
郵便番号を住所に変換する方法は色々ありますが 一番有名なのは「郵便番号変換ウィザード」アドインでしょう。ただしこのアドインは 現在 Microsoftから正式な提供がありません。例えネット上で探してアドインできたとしても情報自体が古いです。
もう一つは Microsoft IMEの郵便番号辞書を使って一つずつ郵便番号を住所情報に変換していく方法です。この方法は１つずつ入力する時には便利ですが 一括で処理するのには向いていません。
今回は Excel2013で追加された関数を使って Webから住所情報を検索して結果を表示させる方法を考えてみます。１つずつ検索するので多少時間はかかりますが 終わった後で値にしてしまえばいいので知っていると便利かと思います。
★その１：郵便番号からハイフン記号を抜く
まずは検索に使えるようにデータを調整します。今回は郵便番号を 7桁の数値にしておく必要がありますので もし間にハイフンが入っている場合は取り除いてしまいましょう。
A2に「540-0002」のように入力している場合
B2に「=TEXT(SUBSTITUTE(A2,"-",""),"0000000")」と入力
こんな形でハイフンが抜けます。必ず「-」が 4文字目に入っていると分かっている場合は REPLACE関数を使って
B2に「=TEXT(REPLACE(A2,4,1,""),"0000000")」と入力
のようにしてもいいです。 TEXT関数は７桁に揃える為に入れています。
★その２：郵便検索用 WebAPIを利用する
「API (Application Programming Interface)」というのは 名前の通り「プログラム用のインターフェイス（接続用の規格）」です。最初から書き出すと長大になる高度なプログラムでも APIを利用すると短いコードで表現することができるようになります。特に Web上で提供されている「WebAPI」は 結果を XMLなどの利用しやすい形式で返してくれるので大変便利です。
今回は「
郵便番号検索API」サービスを利用して 郵便番号から住所情報を呼び出してみます。
★その３：都道府県・市区町村・地名情報を呼び出す
ではいよいよ関数で住所情報を検索してみましょう。
A2に「540-0002」と入力
■B2：都道府県名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A2,"-",""),"0000000")),"//＠state"),"")
■C2：市区町村名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A2,"-",""),"0000000")),"//＠city"),"")
■D2：地名
=IFERROR(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A2,"-",""),"0000000")),"//＠address"),"")
これだけです。
3つまとめるなら下記のようにします。
=CONCAT(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&TEXT(SUBSTITUTE(A2,"-",""),"0000000")),"//@"&{"state","city","address"}))
Excel2019以上でないと CONCAT関数は使えないので 古いバージョンの場合はい一つずつ「&」で繋ぐ必要があります。
ここからはおまけです。上のやり方だと式が長くなるので いくつもセルに入れると重苦しい感じになります。見た目にも分かりにくいので少し工夫しましょう。名前定義やテーブル書式を有効活用するとすっきりします。
[ホーム]→[スタイル]の[テーブル として書式設定]で「A1:D4」を選択
→[先頭行をテーブルの見出しとして使用する]にチェックを入れる
→[OK]
これでテーブル書式にはなりますが 後々のことを考えるならちゃんと名前を適切なものにしておいた方がいいでしょう。
[数式]→[定義された名前]の[名前の管理]
「テーブル1」を選択して [編集]
名前の「テーブル1」を「T_住所情報」に変更して[OK]
「T」は「テーブル」の意味です。後は他の数式も名前定義します。
名前：URL
参照範囲：="http://zip.cgis.biz/xml/zip.php?zn="
名前：郵便番号
参照範囲：=TEXT(SUBSTITUTE(T_住所情報[@郵便番号],"-",""),"0000000")
名前：都道府県名
参照範囲：=IFERROR(FILTERXML(WEBSERVICE(URL&郵便番号),"//@state"),"")
名前：市区町村名
参照範囲：=IFERROR(FILTERXML(WEBSERVICE(URL&郵便番号),"//@city"),"")
名前：地名
参照範囲：=IFERROR(FILTERXML(WEBSERVICE(URL&郵便番号),"//＠address"),"")
こうしておけば それぞれの数式は
B2：=都道府県名
C2：=市区町村名
D2：=地名
となってすっきりします。
