いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)関数を使って郵便番号から住所を取得する

 Excelで、入力された郵便番号に対応する住所を取得する方法についてです。
 ここで紹介するのはIMEの設定によるものではなく関数を使って郵便番号を住所に変換するもので、無料で利用できるAPIから都道府県・市区町村・町域名を取得します。

  • WEBSERVICE関数等を使い、ネット上で利用できる郵便番号検索用のAPIを通じて郵便番号に対応する住所を取得することができます。

手順

f:id:accs2014:20200927000042p:plain:right:w550

 画像ではB3,B4セルにそれぞれ郵便番号が入力されており、これらに対応する都道府県名等を取得するものとします。
 郵便番号は数値でもいいのですが0で始まる郵便番号については0で始まる必要があるので、セルの書式設定により表示形式を「文字列」にして入力しています。また、郵便番号はハイフンを含んでいてはいけませんのでSUBSTITUTE関数などで取り除くなどの対応が必要です。なお、これらの注意点は利用するAPIの仕様によるものです。

 

 早速ですがC3セルに次のような式を入力して都道府県・市区町村・町域名を取得したところです。

f:id:accs2014:20200927000046p:plain:right:w780

 C3セル

=FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B3),{"//value/@state","//value/@city","//value/@address"})

 WEBSERVICE関数を使って「郵便番号検索API」のURLに郵便番号を送信(「?zn="&B3」の部分)してデータを取得しています。
 APIと取得できるXMLデータの詳細については公式サイトをご覧ください。

zip.cgis.biz

 取得したXMLに対しFILTERXML関数を使い、第2引数を「{"//value/@state","//value/@city","//value/@address"}」とすることで都道府県・市区町村・町域名を一括して取得しています。「"//value/@state"」だけだと都道府県名が、「"//value/@city"」だと市区町村名が、「"//value/@address"」町域名が得られます。

 

f:id:accs2014:20200927000050p:plain:right:w550

 さらに式を下のセルにコピーした様子です。
 レアケースですが、複数の町域名に対応する郵便番号については(APIはきちんと複数の結果を返すものの上記の式の詰めが甘いため)エラーとなりますのでご了承願います。

 

 また、次の画像では都道府県から町域名までを結合して表示している例です。
 C3セルに次の式を入力して、C4セルにコピーしています。

f:id:accs2014:20200927000054p:plain:right:w780

=CONCAT(FILTERXML(WEBSERVICE("http://zip.cgis.biz/xml/zip.php?zn="&B3),{"//value/@state","//value/@city","//value/@address"}))

 CONCAT関数で3つの文字列を連結しているだけで、あとは最初の式と同じです。

 なお住所については(常に最新の情報に更新する必要がなければ)値に変換して貼り付けておいた方がよいと思います。
 APIの利用にあたってはサイトに示されている利用条件等をよくご確認ください。