ノートの落書き感覚で発信する情報ブログ、その名も「デジテクノート2」。 沖縄から不定期で好き勝手更新しています!6周年目達成 !!

【データ配布あり】Excel2013で郵便番号から住所を自動で入力する方法

Ads by Google

001_20150123_excel2013-auto-input-address

どうも、(@こふす)です。

最近PC内のファイルを整理しているんですが、1~2年前に作成した「郵便番号から住所を自動で入力する」というファイル名のExcelファイルを発見しまして、開いてみると我ながら以外にも面白いものだったので公開しようかなと思います。

ついでに簡単ですが郵便番号から住所を自動で入力する方法についても紹介します。

Ads by Google

Excel2013で郵便番号から住所を取得するぞ!

なんでこんなものを作ったのかは忘れましたが(汗)沢山のデータを入力する中で、郵便番号や住所を入力する機会ってあると思います。10件~20件程度だったら普通に入力していけばいいと思いますが、100件単位になってくると中々しんどいですよね。Excelにもアドオンという形で郵便番号から住所住所から郵便番号を表示してくれるものがありますが、セルに入力してくれないし、一々ウインドウを表示しないといけなくてスマートではありません。

調べてみると現在最新版のExcel2013からは、ネット上のAPIからデータを取得できるFILTERXML 関数WEBSERVICE 関数が新たに加わったということで、この2つの関数を利用して郵便番号から住所を自動的に入力してみたいと思います。

ということでここからはExcel2013をお持ちの方のみが試すことが出来る内容となっています。ではまずは私が気まぐれで作ったファイルをDLしてみてください。(お手数ですがスパム避け用にパスワードを設置しています。「1234」と半角で入力して下さい。)

Enter password
ダウンロード544 downloads

 

画面はこんな感じです。一応使い方も載せているので簡単に実行できるかと思います。

002_20150123_excel2013-auto-input-address

環境によっては開いた時にセキュリティの警告が出る(外部からデータを取得するため)こともあるのでコンテンツの有効化で許可して下さい。

009_20150123_excel2013-auto-input-address

フォームが2つありますが、左の緑色が郵便番号検索というサイトで提供されているAPIをFILTERXML関数で取得したタイプで右の黄色がGoogle日本語入力(WEB版)で公開されているAPIをWEBSERVICE関数で取得したタイプになります。どちらも郵便番号を入力することで多少の違いはありますが、基本的に同じ結果が隣のセルに住所という形で自動的に入力されます。

では実際に入力される様子を紹介しましょう。

まずは緑色の郵便番号検索APIを使用したタイプで郵便番号を入力し、住所を取得し自動で入力してみます。

003_20150123_excel2013-auto-input-address

郵便番号の列で半角数字でハイフン無しの郵便番号を入力します(例:1000001)。Enterキーを押すことで住所列のセルに自動的に住所が入力されます。

005_20150123_excel2013-auto-input-address

これだけ。実にシンプルですよね!アドオンの様に別ウインドウを表示する必要もないのでバンバン入力できちゃいます。

ではもう1つのGoogle日本語入力APIを使用したタイプもでもやってみます。

004_20150123_excel2013-auto-input-address

同じ様に郵便番号列のセルに半角数字&ハイフン無しで郵便番号を入力し、Enterキーを押します。

006_20150123_excel2013-auto-input-address

同じく表示されます。基本的には一緒ですが、使用しているAPIが別なので細かなところで住所が違ったりします。ココらへんは実際に使用してみて好きな方をチョイスするといいかと思います。

 

仕組み

実際に開いてみるとわかるかと思いますが、実は郵便番号を入力する列住所が表示される列との間に幾つか非表示になっている列があります。マクロを組める方ならもっとシンプルに出来るかもしれませんが、生憎VBが苦手なので全てセルに入力した関数でもって住所情報を取得しています。

郵便番号検索APIの仕組み

郵便番号検索APIを使用したタイプだと3列別で使用しています。ちょうどオレンジに色が変わっているところですね。

007_20150123_excel2013-auto-input-address

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

1つのセルで県名も市名も市名以下も全て取得する方法がうまくいかなかったので別で用意していますが、やり方がわかる方ならもっとシンプルにすることが出来るかと思います。

 

Google日本語入力APIの仕組み

Google日本語入力APIの場合は2列別で使用しています。オレンジの所です。

008_20150123_excel2013-auto-input-address

まずK列のセルは郵便番号を入力したJ1セルの内容をGoogle日本語APIに送り変換候補を取得しますが、ここでちょっとだけポイントがあります。というのもGoogle日本語入力では全角で途中にハイフンを使用した(例:100-0001)形でないと変換候補として住所が取得できません。全角で入力するやり方でも良かったんですが、入力切替するのが嫌だったので半角で入力しつつも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の場合は変換元と変換候補がテキストとして返ってきます。それも2セットになっているので形を整形する必要があります。MID関数を使用して指定位置から必要な住所の先頭を抽出します。

=MID(K1,"16","100")

最後はM列。L列で先頭は整形されているので後方を同じくMID関数で整形します。後方の位置には丁度「コンマ」があるのでFIND関数で探せばOK。

=LEFT(L1,FIND(",",L1)-2)

これで郵便番号から住所を自動で入力することが出来ます。

 

住所の入力って面倒いじゃん?

日本の住所って漢字もひらがなも更には数字も入ってて入力するのってハッキリ言って面倒くさいです。10件~20件ならイイですがそれ以上となると「ふぅ~」と疲れますよね。

今回紹介したやり方や配布したシートはあくまでも私が個人的に趣味範囲で適当に作ったものなのでやり方に粗い箇所もあるかと思いますが、こういったやり方もあるんだという寛容な目で見てくれると嬉しいです。

直ぐ試せるシートも配布しているので是非使ってみてはいかがでしょうか!ライセンスとかは全てオールフリーですのでご自由に^^

Ads by Google

PICK UP RELATED ARTICLE

RELATED ARTICLE

関連する記事をピックアップ! 関連記事
Powered by Google(関連コンテンツ)