[記事数:1,202] 日本を元気にするキーワード、地域活性化×中小企業× ITのTips and Quotes(秘訣と引用文)
creative  link  memo 

Excel WEBSERVICE関数を使ってWeb APIを利用する方法 Wikipedia、郵便番号、Bing、ニコ動など



Microsoft Office 2013で「WEBSERVICE」「ENCODEURL」「FILTERXML」の3つの関数が追加され、これらの関数を使うと、Webサービス、つまり公開されているWeb APIをVBAでコードを書くことなく利用できるようになります。

関数 説明 書式 引数
ENCODEURL URL 形式でエンコードされた文字列を返します。 ENCODEURL(文字列) 文字列:URL 形式でエンコードする文字列です。
WEBSERVICE インターネットかイントラネットの Web サービスからのデータを返します。 WEBSERVICE(URL) URL:必ず指定します。Web サービスの URL を指定します。
FILTERXML 指定された XPath に基づいて XML コンテンツの特定のデータを返します。 FILTERXML(XML, XPath) XML:必ず指定します。有効な XML 形式の文字列を指定します。

XPath:必ず指定します。標準 XPath 形式の文字列を指定します。

使い方としては、ENCODEURL関数でエンコードしたクエリ文字列を、WEBSERVICE関数で指定したURLに渡し、結果として得られるXMLからFILTERXML関数を使って任意の値を取得する(値はXPath式で指定)、という流れになります。

WikipediaAPIを利用してWikipediaの記事内容を取得

SimpleAPI「WikipediaAPI」を利用してWikipediaの記事内容を取得する場合

SimpleAPI WikipediaAPI 出力仕様

language : 言語名
id : Wikipedia内の管理コード
url : Wikipediaにリンクする際のURL
title : キーワードのタイトル
body : 本文のダイジェスト
length : 本文の長さ
redirect : 別キーワードへのリダイレクトがあるかどうか。あれば1、なければ0。
strict : そのキーワードと完全一致する場合には1。完全一致しない場合は0。
datetime : 更新日付

郵便番号検索API 郵便番号から住所を取得

郵便番号検索APIを利用すれば、下記のように郵便番号から住所を取得することもできます。

Bingの検索結果を取得

Bingの検索結果はRSS(XML)形式で取得することができるので、Web関数を使ってBingの検索結果を取得する例を紹介します。

使用するセルは下記の通りです。

C2 : 検索するキーワード
C3 : ENCODEURL関数でURLエンコードしたキーワード
C4 : WEBSERVICE関数で取得したデータ
C5 : FILTERXML関数で抽出した値

1. セルC2にBingで検索したいキーワードを入力します。

2. セルC3に[

]と入力します。

3. セルC4に[

]と入力します。

4. セルC5に[

]と入力します。ここで指定しているXPathは、複数ある「/rss/channel/item」要素の中から1番目の要素の子要素である「title」の値を取得する、という意味を表しています。「/rss/channel/item[2]/title」とすれば、2番目の要素の値を取得することができます。

5. 上記式は、[

]のように1つにまとめることもできます。

以上のように、Excel 2013で追加されたWeb関数を活用すれば、プログラムを組むことなく標準の関数だけでインターネット上のデータを取得することができます。

郵便番号検索API

D列のセルはC1で入力された郵便番号をAPIでもって県名を取得しています。

E列のセルもC1で入力された郵便番号をAPIでもって市名を取得しています。

F列のセルもC1で入力された郵便番号をAPIでもって市名以下を取得しています。

後はG列の住所を表示する列のセルにD,E,F列の値を繋げれば完成です。

Google日本語入力APIで郵便番号

まずK列のセルは郵便番号を入力したJ1セルの内容をGoogle日本語APIに送り変換候補を取得しますが、ここでちょっとだけポイントがあります。というのもGoogle日本語入力では全角で途中にハイフンを使用した(例:100-0001)形でないと変換候補として住所が取得できません。全角で入力するやり方でも良かったんですが、入力切替するのが嫌だったので半角で入力しつつもAPIに送る際は全角にしながら途中にハイフンを入れてやる処理が必要になります。ここではLEFT関数で左から3文字分を抽出しハイフンを足して、またLEFT関数で左から数えて4文字分から4文字抽出し後ろにつけてハイフンが入った形に整形します。全角に関してはURLの「=ja-Hira」だけでOKです。

次はL列です。郵便番号検索APIと違い、Google日本語のAPIの場合は変換元と変換候補がテキストとして返ってきます。それも2セットになっているので形を整形する必要があります。MID関数を使用して指定位置から必要な住所の先頭を抽出します。

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

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

ニコニコ動画search APIで検索結果の情報を取得

タグ検索のURLは以下の形式になっています。

またタグ検索のオプションでページ、ソートの種類、昇降順を指定できます。URLに以下のパラメータを付ける事で指定できます。

ソートの種類
値 ソートの種類
n コメントが新しい(default)
v 再生数
m マイリスト数
r コメント数
f 投稿日時
l 再生時間

昇降順
値 昇降順
d 降順(default)
a 昇順

例えば投稿日の古い順(投稿日の昇順)に検索したときの1ページ目が見たい場合

をURLに付け足します。

最後にRSSとして取得するために&rss=2.0を付け足します。
例として「早見沙織」を投稿日の古い順にタグ検索の2ページ目のRSSは以下のURLになります。

このURLをEXCEL上でつくります。

セルA2:任意の語句

セルB2

セルC2

セルD3

これらを使えば以下の様にタイトルのリストを作ることも可能です。

XPath の例

GoogleニュースのRSS





FavoriteLoadingお気に入りAdd to favorites
マクロVBA 連続数値を入れる 連続数値を入れる|Excelマクロの記録で覚えるVBA 数式の結果を値として取得する エクセルVBAについて数式の結果を値として取得するコードはあります... - Yahoo!知恵袋 =WEBSERVICE($C$3&C14&$C$6&$C$5&...
国土数値情報 ダウンロードサービス... 国土数値情報 ダウンロードサービス Web API 「国土数値情報」とは、国土形成計画、国土利用計画の策定等の国土政策の推進に資するために、地形、土地利用、公共施設などの国土に関する基礎的な情報をGISデータとして整備したものです。そのうち公開に差し支えないものについて、「地理空間情報活...
Excel VLOOKUP関数で#N/Aエラーを表示させない&結果を0にしない方法... エクセル(Excel)で関数の値が空だったり、検索対象の範囲内に該当する検索値がなかった場合、#N/Aエラーになります。 IFERROR関数を使うと#N/Aの表示を消す(表示しない)ように指定することができます。 IFERROR関数を使って#N/Aエラーを表示しない方法 ...
Excel「12月30日(月)」のように日付を入力すると曜日も表示する書式設定... Excelではセルの書式設定の表示形式を変えると、同じデータをさまざまな形式で表示できます。 その中でも「日付を入力すると曜日も自動表示される方法」を覚えておくと、使えるシーンも多いので重宝します。かんたんなのでぜひマスターしてください。 まず、曜日を表示させたいセルに日付を入力します。...
年間シングルランキング(日本国内) ■年間音楽ダウンロードランキング(2005~) ■年間ヒットシングル(1950~) ■年間ヒットアルバム(1970~) ■年間ヒット音楽DVD/Blu-ray(1994~) ■年間ヒットアーティスト(1970~) ■年間音楽CDレンタルランキング(2006~) ■年間ビルボードJAP...
Excel データ整理・名寄せに使える技 COUNTIFS関数により「重複」と表示させる 1.セル① 重複しているか確認する列のデータの一番上のセルを指定 2.セル② 数値①と同じ列で、数式と同じ行のセルを指定 3.セル③ 重複しているか確認する列のデータの一番上のセルを指定 4.セル④ 数値①と同じ列で、数式と同じ行の...
住所⇔緯度・経度変換 地理院マップシート... 「地理院マップシート」とは、Microsoft(R)Excel のマクロ機能を利用したファイルです。 住所録等の帳票データを「地理院地図」に簡単に展開して「地図上に見える化」することができます。 「地理院マップシート」は平面直角座標系、UTM・UTMポイントの座標値変換も可能です。ま...
Excel ハイパーリンクのURLを別のセルに表示したい... 現在アクティブなシートのすべてのハイパーリンクのURL を 右隣のセルに書き出すマクロです。 マクロのセキュリティを中にしてエクセルを再起動する(ご利用のエクセルのバージョンに応じた方法で) ブックを開く ALT+F11でVBE画面を出す 挿入メニューから標準モジュールを挿入する 現れ...
Excel関数 住所の数字を半角数字に変換する(ASC関数)... ●概要● 複数の人が作成した住所録データなどを集めると、入力方法の違いにより全角数字と半角数字が混ざってしまうことがあります。 今回は、文字列内の全角英数カナ文字を半角英数カナ文字に変換するASC関数を使い、文字列に含まれる数字を半角に統一する方法を紹介します。 ●詳細● 変換後のデータを...
ExcelGmap Excelで入力した住所を経度緯度変換しGooglemapに表示... ExcelGmap Excelで入力した住所を経度緯度変換しGooglemapに表示 ExcelGmapとは Excelに入力した住所から経度・緯度を求め、画面いっぱいにGoogleマップを表示してポイントアイコンを表示するEXCELファイルです。ポイントアイコンをクリックすることで...




コメントを残す