【データ配布あり】Excel2013で郵便番号から住所を自動で入力する方法
どうも、(@こふす)です。
最近PC内のファイルを整理しているんですが、１～２年前に作成した「郵便番号から住所を自動で入力する」というファイル名のExcelファイルを発見しまして、開いてみると我ながら以外にも面白いものだったので公開しようかなと思います。
ついでに簡単ですが郵便番号から住所を自動で入力する方法についても紹介します。
Excel2013で郵便番号から住所を取得するぞ！
なんでこんなものを作ったのかは忘れましたが（汗）沢山のデータを入力する中で、郵便番号や住所を入力する機会ってあると思います。10件～20件程度だったら普通に入力していけばいいと思いますが、100件単位になってくると中々しんどいですよね。Excelにもアドオンという形で郵便番号から住所や住所から郵便番号を表示してくれるものがありますが、セルに入力してくれないし、一々ウインドウを表示しないといけなくてスマートではありません。
調べてみると現在最新版のExcel2013からは、ネット上のAPIからデータを取得できるFILTERXML 関数とWEBSERVICE 関数が新たに加わったということで、この2つの関数を利用して郵便番号から住所を自動的に入力してみたいと思います。
ということでここからはExcel2013をお持ちの方のみが試すことが出来る内容となっています。ではまずは私が気まぐれで作ったファイルをDLしてみてください。（お手数ですがスパム避け用にパスワードを設置しています。「1234」と半角で入力して下さい。）
[wpdm_file id=2]
画面はこんな感じです。一応使い方も載せているので簡単に実行できるかと思います。
環境によっては開いた時にセキュリティの警告が出る（外部からデータを取得するため）こともあるのでコンテンツの有効化で許可して下さい。
フォームが２つありますが、左の緑色が郵便番号検索というサイトで提供されているAPIをFILTERXML関数で取得したタイプで右の黄色がGoogle日本語入力（WEB版）で公開されているAPIをWEBSERVICE関数で取得したタイプになります。どちらも郵便番号を入力することで多少の違いはありますが、基本的に同じ結果が隣のセルに住所という形で自動的に入力されます。
では実際に入力される様子を紹介しましょう。
まずは緑色の郵便番号検索APIを使用したタイプで郵便番号を入力し、住所を取得し自動で入力してみます。
郵便番号の列で半角数字でハイフン無しの郵便番号を入力します（例：1000001）。Enterキーを押すことで住所列のセルに自動的に住所が入力されます。
これだけ。実にシンプルですよね！アドオンの様に別ウインドウを表示する必要もないのでバンバン入力できちゃいます。
ではもう１つのGoogle日本語入力APIを使用したタイプもでもやってみます。
同じ様に郵便番号列のセルに半角数字＆ハイフン無しで郵便番号を入力し、Enterキーを押します。
同じく表示されます。基本的には一緒ですが、使用しているAPIが別なので細かなところで住所が違ったりします。ココらへんは実際に使用してみて好きな方をチョイスするといいかと思います。
仕組み
実際に開いてみるとわかるかと思いますが、実は郵便番号を入力する列と住所が表示される列との間に幾つか非表示になっている列があります。マクロを組める方ならもっとシンプルに出来るかもしれませんが、生憎VBが苦手なので全てセルに入力した関数でもって住所情報を取得しています。
郵便番号検索APIの仕組み
郵便番号検索APIを使用したタイプだと3列別で使用しています。ちょうどオレンジに色が変わっているところですね。
D列のセルはC1で入力された郵便番号をAPIでもって県名を取得しています。
=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="& $C1),"/ZIP_result/ADDRESS_value/value[@state]/@state")
E列のセルもC1で入力された郵便番号をAPIでもって市名を取得しています。
=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="& $C1),"/ZIP_result/ADDRESS_value/value[@city]/@city")
F列のセルもC1で入力された郵便番号をAPIでもって市名以下を取得しています。
=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="& $C1),"/ZIP_result/ADDRESS_value/value[@address]/@address")
後はG列の住所を表示する列のセルにD,E,F列の値を繋げれば完成です。
=D1&E1&F1
１つのセルで県名も市名も市名以下も全て取得する方法がうまくいかなかったので別で用意していますが、やり方がわかる方ならもっとシンプルにすることが出来るかと思います。
Google日本語入力APIの仕組み
Google日本語入力APIの場合は2列別で使用しています。オレンジの所です。
まずK列のセルは郵便番号を入力したJ1セルの内容をGoogle日本語APIに送り変換候補を取得しますが、ここでちょっとだけポイントがあります。というのもGoogle日本語入力では全角で途中にハイフンを使用した（例：１００－０００１）形でないと変換候補として住所が取得できません。全角で入力するやり方でも良かったんですが、入力切替するのが嫌だったので半角で入力しつつもAPIに送る際は全角にしながら途中にハイフンを入れてやる処理が必要になります。ここではLEFT関数で左から3文字分を抽出しハイフンを足して、またLEFT関数で左から数えて4文字分から4文字抽出し後ろにつけてハイフンが入った形に整形します。全角に関してはURLの「=ja-Hira」だけでOKです。
=WEBSERVICE("http://www.google.com/transliterate?langpair=ja-Hira|ja&text="&LEFT(J1,3) & "-" & MID(J1,4,4))
次はL列です。郵便番号検索APIと違い、Google日本語のAPIの場合は変換元と変換候補がテキストとして返ってきます。それも２セットになっているので形を整形する必要があります。MID関数を使用して指定位置から必要な住所の先頭を抽出します。
=MID(K1,"16","100")
最後はM列。L列で先頭は整形されているので後方を同じくMID関数で整形します。後方の位置には丁度「コンマ」があるのでFIND関数で探せばOK。
=LEFT(L1,FIND(",",L1)-2)
これで郵便番号から住所を自動で入力することが出来ます。
住所の入力って面倒いじゃん？
日本の住所って漢字もひらがなも更には数字も入ってて入力するのってハッキリ言って面倒くさいです。10件~20件ならイイですがそれ以上となると「ふぅ～」と疲れますよね。
今回紹介したやり方や配布したシートはあくまでも私が個人的に趣味範囲で適当に作ったものなのでやり方に粗い箇所もあるかと思いますが、こういったやり方もあるんだという寛容な目で見てくれると嬉しいです。
直ぐ試せるシートも配布しているので是非使ってみてはいかがでしょうか！ライセンスとかは全てオールフリーですのでご自由に^^
