[記事数: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
Excel関数 SUMPRODUCTでデータ種類をカウント... Excel 関数で重複を除いてデータの件数を求める方法 重複を除いてデータの件数、すなわちデータの種類を、Excel の関数を用いて求める方法を紹介します。 方法1 SUMPRODUCT関数とCOUNTIF関数を用いて求めます。 データ範囲内に空白セルがある場...
Word 2つの文書を比較して差分を表示 Wordにも、2つの文書を比較して差分を教えてくれる機能があった! 2つのファイルを差分比較するのに何を使っていますか? WordにもDiff(ファイル比較)ソフトと同等の機能が備わっているんです! 複数ファイルのテキスト内容を横並びに表示し、隣のファイルと比較して相違がある箇所を教えてくれ...
amazonSearch100 amazon一括検索・一覧表示ツール... amazon一括検索・一覧表示ツール 1.検索条件の設定 検索条件イメージ ※検索条件の設定について、対象範囲は画像の赤枠内を参照して下さい。 設定可能な検索条件は以下となります。以下の組合せによって検索が可能です。 国 検索キーワード 検索モード ...
住所⇔緯度・経度変換 地理院マップシート... 「地理院マップシート」とは、Microsoft(R)Excel のマクロ機能を利用したファイルです。 住所録等の帳票データを「地理院地図」に簡単に展開して「地図上に見える化」することができます。 「地理院マップシート」は平面直角座標系、UTM・UTMポイントの座標値変換も可能です。ま...
Excel IF関数で、あいまいな条件(……を含む)の指定をするには... のなかにExcelという文字列を含めば、○を含まなければ、何も表示しないという式を入力 B2セルに入力するCOUNTIFの式は、 COUNTIF(B2,"*Excel*") これをIF関数の書式にネスト(入れ子)します。 数式 =IF(COUNTIF(範囲,条件),真の場合,偽の場合) ...
Google画像検索から一括ダウンロード ImageSpider Google画像検索から指定した語句の画像を一括ダウンロードする「ImageSpider」 キーワードをGoogle画像検索と同じ要領で、いくつか指定してダウンロードボタンをクリックするだけ 指定のキーワードでGoogle画像検索を行い検索結果の画像を...
Excel「12月30日(月)」のように日付を入力すると曜日も表示する書式設定... Excelではセルの書式設定の表示形式を変えると、同じデータをさまざまな形式で表示できます。 その中でも「日付を入力すると曜日も自動表示される方法」を覚えておくと、使えるシーンも多いので重宝します。かんたんなのでぜひマスターしてください。 まず、曜日を表示させたいセルに日付を入力します。...
Excel 複数の条件に一致するデータの個数を求めるCOUNTIFS関数... COUNTIFの複数条件 COUNTIFS関数 下のような表で「女性の人数を数えたい」といった場合、COUNTIF関数を使って「性別」欄に「女」と入力されているセルの個数を数えれば女性の人数を調べられます。 ところが「35歳以上の女性の人数を数えたい」といった条件だった場合、「性別」...
iタウンページのデータ取得をしてエクセルやCSVに変換して利用する方法... 賢早くんってなに? Excel だけでインターネットタウンページの情報を簡単に一括収集することができます。 また、住所から郵便番号の一括検索機能も搭載しています。 DM(ダイレクトメール)の住所検索などにも利用できます。 ◆ タウンページ検索 シートに、キーワードを羅列し対象となる地域...




コメントを残す