おつかれさまです。tyamonです。
先日、人事部門から「EXCELに入力した住所から最寄りの駅を取得する方法ない？できれば無料で」と相談を受けました。
世の中にはそういったサービスはいくつかあるかと思いますが、「無料」という条件がつくと悩ましいものです。そこで無料で公開されているAPIを組み合わせてできるだけシンプルに最寄り駅を取得する方法を考えてやってみました。
ご利用にあたっての注意点
本記事で紹介する方法は、無料で公開されている外部のサービスを組み合わせることで実現しています。
そのため、サービスの仕様変更や停止、また地名解釈の精度により、必ずしも100%正確な結果が得られるとは限りません。
あくまで「参考情報の取得」といったレベルで捉えていただき、最終的には人間の目でチェックするなど、補助的なツールとしてご活用くださいね。
完成イメージ
先に完成イメージをお見せしますね。こんな感じで、A列に住所を入れると、F列とG列に最寄り駅と路線名が自動で表示されます。
今回はこの仕組みをExcelの標準機能だけで作っていきます！（マクロは使いません）
全体の流れ
仕組みは意外とシンプルで、2つの無料APIを連携させています。
- 住所を緯度・経度に変換する (ジオコーディング)
- その緯度・経度から一番近い最寄り駅を探す
この2段階の処理を、Excelの WEBSERVICE 関数と FILTERXML 関数を使って実現します。
ステップ1：住所から緯度・経度を取得しよう
まずは住所を位置情報（緯度・経度）に変えてくれるAPIを使います。
今回は、東京大学CSIS様が提供してくれているジオコーディングサービスを利用させていただきます。感謝！
このステップでは、B, C, D, E列を順番に使っていきます。
B列：APIのURLを生成する
A列に住所が入力されているとして、B列にAPIを呼び出すためのURLを作ります。
住所には日本語やスペースが含まれるので、ENCODEURL関数を使ってURLとして安全な形式に変換するのがポイントです。
- B2セルに入力する数式
="http://geocode.csis.u-tokyo.ac.jp/cgi-bin/simple_geocode.cgi?charset=UTF8&addr="&ENCODEURL(A2)C列：XMLデータを取得する
B列で作成したURLを WEBSERVICE 関数に渡して、APIから返されるXMLデータを丸ごと取得します。
- C2セルに入力する数式
=WEBSERVICE(B2)D列・E列：緯度と経度を抜き出す
C列に取得したXMLデータから、FILTERXML関数を使って緯度と経度をそれぞれ抜き出します。
- D2セル（緯度）に入力する数式
=FILTERXML(C2,"//latitude")- E2セル（経度）に入力する数式
=FILTERXML(C2,"//longitude")これで、住所から緯度と経度を自動で取得できるようになりました！
ステップ2：緯度・経度から最寄り駅を検索しよう
次に、取得した緯度・経度を使って最寄り駅を検索します。
こちらは、HeartRails様が提供するHeartRails Express APIを利用させていただきます。こちらも無料で使える素晴らしいAPIです。
F列・G列：最寄り駅と路線名を抜き出す
今度はURL生成から情報抽出までを1つの数式でやってしまいましょう。WEBSERVICE関数の中にURLを直接組み立て、その結果をFILTERXML関数に渡します。
FILTERXMLは複数の結果を返すことがあるので、INDEX関数を組み合わせて「1番目の結果」だけを取り出すのが確実です。
- F2セル（最寄り駅）に入力する数式
=INDEX(FILTERXML(WEBSERVICE("http://express.heartrails.com/api/xml?method=getStations&x="&E2&"&y="&D2),"//station/name"),1)- G2セル（路線名）に入力する数式
=INDEX(FILTERXML(WEBSERVICE("http://express.heartrails.com/api/xml?method=getStations&x="&E2&"&y="&D2),"//station/line"),1)INDEX(…, 1) の部分で、APIから返された駅リストの1番目（＝最も近い駅）を指定しています。
まとめ
これで、Excelに住所を入力するだけで、交通費計算などに便利な最寄り駅が自動で表示されるようになりました。
マクロを使わないので、セキュリティの警告が出ないのも良い点ですね。
無料で素晴らしいAPIを提供してくださっている開発者の方々に感謝しつつ、ぜひ試してみてください！
コメント