FILTER関数(フィルター),FILTERXML関数(フィルター・エックスエムエル)のご紹介です。

FILTER関数は、定義した条件に基づいてデータの範囲をフィルター処理できます。
データの抽出としては使い勝手の良い関数ですが、条件付きの計算にも重宝しそうな関数になります。
FILTERXML関数は、Web用のXMLコンテンツの特定のデータを返すことが出来ます。

FILTER関数

定義した条件に基づいてデータの範囲をフィルター処理ができます。

注意※FILTER関数がExcel for Office365(Excel2016:サブスクリプション型)で使えるようになりました。
   (Office365は2020/4/22からは Microsoft365となりました)
   ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
書式は、=FILTER(配列,含む,[空の場合])

配列   :フィルターするデータの範囲または配列
含む   :フィルターする条件を書きます(フィルターで残すデータの条件を書きます)
空の場合 :(省略可)フィルターした後にデータがない場合に表示するテキスト
      (#CALC! エラーとなるときに表示する文字列を指定します)

例を見て確認してみましょう。

例1:中間テストの結果で個人の合計が300以上のデータを抽出します。
「I4」のセルに、=FILTER(G4:G12,G4:G12>=300)と入力します。

結果、330~307が導き出されました。

例2:テーブルにして、FILTER関数を利用する例です。
テーブル名は「成績表」にしています。

例3:今度は、合計ではなく名前で抽出してみましょう!

どうですか!300点以上の名前が抽出されました。便利じゃないでしょうか。
テーブルを使っても同じ結果が出ますよ!!

FILTERXML関数

XMLコンテンツ(Web)の特定のデータを返します。
書式は、FILTERXML(XML1, パス2)となります。

XML1  :XML形式の文字列を指定します。
パス2  :XMLのパスを指定します。

例1:Yahoo!天気・災害から東京都の天気情報を取得し、表示してみます。
yahooの天気のXMLは「https://rss-weather.yahoo.co.jp/rss/days/13.xml」
パスは「//channel/item[1]/title」になります。
「A1」に、=FILTERXML(WEBSERVICE(“https://rss-weather.yahoo.co.jp/rss/days/13.xml”),”//channel/item[1]/title”)と入力します。

結果は、【 18日(火) 東京(東京) 】 曇時々雨 – 24℃/18℃ – Yahoo!天気・災害 となります。

FILTERXML関数とは、WEBSERVICE関数でサーバーから取り出したXMLデータから値を取り出したいといった場合に使います。
HTMLもXMLのルールの中にあるため利用可能です。
多くの場合は、FILTERXML関数単独ではなくWEBSERVICE関数と併用してExcelからマクロなしでWebAPIにアクセスする用途に使用します。
XMLパスにはルールは色々ありますが、以下を押さえれば最低限利用可能です。
「//」で始まり「/」区切りでタグの階層を指定します。「@」でタグの属性を指定します。この2点になります。

例1:では、日本語を含む検索語など、Webサービスに渡したい文字列をENCODEURL関数を使ってURLエンコードします。
1.でエンコードした文字列を含めたURLを、WEBSERVICE関数を使ってWebサービスに渡します。
2.のWEBSERVICE関数で返ってきたXMLデータから、FILTERXML関数を使って必要な値だけ取得します。値はXPathで指定する必要があります。

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

「C3」には、ENCODEURL関数でURLエンコードしたキーワードを挿入します。
「C3」に=ENCODEURL($C$2)と入力します。結果です。

「C4」には、WEBSERVICE関数を使用して取得したデータを表示します。
「C4」に=WEBSERVICE(“http://www.bing.com/search?format=RSS&q=” & $C$3)と入力します。結果です。

次に、「C5」には、FILTERXML関数で抽出した値を返してみます。
「C5」に=FILTERXML($C$4,”/rss/channel/item[1]/title”)と入力します。結果です。

「日本マイクロソフト-Official Home Page」と表示されます。

FILTERXML関数のまとめ

Excel2013から「ENCODEURL」「WEBSERVICE」「FILTERXML」という3つのWeb関数が追加され使用することができます。
上記の関数を利用することで、インターネット上のデータをExcelに簡単に取り込むことができるようになりました。
ENCODEURL :URL 形式でエンコードされた文字列を返します。
ENCODEURL(文字列) :文字列:URL 形式でエンコードする文字列です。
WEBSERVICE :インターネットかイントラネットの Web サービスからのデータを返します。
WEBSERVICE(URL) :URL:必ず指定します。Web サービスの URL を指定します。
FILTERXML :指定された XPath に基づいて XML コンテンツの特定のデータを返します。
FILTERXML(XML, XPath):XML:必ず指定します。有効な XML 形式の文字列を指定します。
XPath :必ず指定します。標準 XPath 形式の文字列を指定します。

・関数の使い方
関数の使い方は、大まかには下記のようになります。

日本語を含む検索語など、Webサービスに渡したい文字列をENCODEURL関数を使ってURLエンコードします。
1.でエンコードした文字列を含めたURLを、WEBSERVICE関数を使ってWebサービスに渡します。
2.のWEBSERVICE関数で返ってきたXMLデータから、FILTERXML関数を使って必要な値だけ取得します。
値はXPathで指定する必要があります。

以上のように、Excel2013から追加されたWeb関数を活用すれば、
プログラムを組むことなく標準の関数だけでインターネット上のデータを取得することができます。
ExcelはWeb APIを操作出来る関数が追加されており、これまでJSONやVBA無しでは実現出来なかったWeb上の情報取得が可能となりました。
例えば郵便番号API(http://zip.cgis.biz/)にアクセスして郵便番号検索を行うことができたり、
WEBSERVICE関数のURLを変えれば、SNSの更新情報や、特定サイトのhtmlソースを取得することも出来ます。
便利な関数なので、利用してみてくださいね!
今まで関数をあまり活用してこなかった方も、関数をより効率よく使いこなしたいという方も、
この動画とブログを見て頂き参考にして頂ければ嬉しく思います。
これからも引き続き関数動画・ブログをアップして参りますので、気になる関数があったらまずはソフトキャンパスの動画を確認してみてください!