2017年6月19日月曜日

Excelの使えなくなった郵便番号ウィザード(ZIPCODE7)の代わりはこれ!

Excelには便利な機能がたくさんありますが、その一つに郵便番号ウィザードというのがあります。通称zipcode7というものです。

Be COOL Usersより「郵便番号変換ウィザード

郵便番号から住所を、住所から郵便番号を呼び出す非常に強力な追加機能(アドオン)で、使い勝手のいいものでした。ところが、2017年の更新によりExcel2013,2016で使えなくなってしまいました。

マイクロソフトコミュニティより「Excel2016で郵便番号検索ウィザードで変換ができない

私も非常に困ってしまい、手作業で行っていたところ、調べてみたらExcel2013から非常に便利な関数が登場したのでご紹介します。

=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B3),"//value/@state")&FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B3),"//value/@city")&FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B3),"//value/@address")
B3がセル指定になります。これは1つのセルの場合ですが、複数セルに住所の情報が必要の場合はこのようになります。

=INDEX(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B4),"//value/@state|//value/@city|//value/@address"),1)
B4がセル指定になります。INDEX関数の最後の1を2とか3に変更すると市町村などが取得できます。

さて、仕組みについて知りたい方は続きをご覧ください。

↓へのアクセスもよろしくお願いします!

ニッチな市場ですがニーズはあるようです!↓


使っている関数はExcel2013から登場したインターネットの情報を取得できるもので、非常に強力な機能を持っています。

WEBSERVICE関数

この関数は、ただのテキストデータ(ソースファイル)としてインターネットの情報を拾ってくることができます。特に有効なのが、JSON・XML・RSSなど構造化(階層化)したデータを得る場合にFILTERXML関数と組み合わせるとリアルタイムの情報を得ることができます。

今回は、郵便番号検索APIというサイトで提供しているXMLを利用します。XMLとは、情報についてわかりやすく意味づけして構造化することでデータとして管理できる手法です。例えば、神奈川県や千葉県が「都道府県」、横浜市や千葉市が「市区町村」という意味づけに関連しているようにデータを作り上げるものです。

FILTERXML関数

この関数は、構造化(階層化)されたXMLを元に属性のデータを引っ張り出すことができます。XPATHの書き方に癖があるため、理解が難しいのですが、今回の例でいうと、「//value/@state」という風に指定した場合、「//」は全体を表していて、全体の中のvalueを探して、その中「/」のstate属性「@」の情報を出しなさいという感じになっています。

複数セルに渡る場合は、INDEX関数を利用しても構いません。

XPATHで「|」は「情報の結合」を表します。つまり、今回の例では、都道府県・市区町村・それ以下の情報をくっつけます。ただし、くっつけた場合にエクセルは配列定数となり、文字列結合とは違う形になります。そのため、INDEX関数で配列の番号を指定します。

0 件のコメント:

コメントを投稿