ExcelのFILTERXML関数の使い方｜文字列分割まで解説

スポンサーリンク

「ExcelでXMLデータから値を取り出したいけど、やり方がわからない…」

XMLデータの扱いは難しそうに見えますよね。専用のツールが必要だと思うかもしれません。しかしExcelには、XML（構造化されたデータ記述形式）から必要な値だけを抜き出せる関数があります。

この記事では、FILTERXML関数の基本から応用まで解説します。XMLデータの取得はもちろん、文字列分割テクニックまでカバーしました。

ExcelのFILTERXML関数とは

FILTERXML関数は、XML形式の文字列から指定した要素を取得する関数です。XPath（XMLの中の位置を指定する記法）を使って、ほしいデータだけを抜き出せます。

たとえば、商品リストのXMLから特定の商品名だけを取り出す、といった使い方ができます。

FILTERXML関数の真価はXMLの処理だけではありません。SUBSTITUTE関数と組み合わせれば、カンマ区切りやスペース区切りの文字列を分割できます。TEXTSPLIT関数が使えない環境で重宝するテクニックです。

FILTERXML関数の読み方

読み方は「フィルター エックスエムエル」です。「Filter」（絞り込む）と「XML」（データ記述形式）を組み合わせた名前で、XMLデータをフィルタリングする機能を表しています。

対応バージョンと動作環境（Windows限定・Mac/Web版非対応）

FILTERXML関数はWindowsの機能に依存しています。そのため、使える環境が限られている点に注意してください。

環境対応状況
Excel 2013（Windows）○
Excel 2016（Windows）○
Excel 2019（Windows）○
Excel 2021（Windows）○
Excel 2024（Windows）○
Microsoft 365（Windows）○
Excel for Mac✕（関数一覧に表示されるが結果を返さない）
Excel for the web✕

WARNING

Mac版ではギャラリーに表示されることがあります。しかし実際には結果を返しません。Windows環境でのみ使用してください。

FILTERXML関数の構文と引数

基本構文は次のとおりです。

=FILTERXML(xml, xpath)

引数は2つとも必須です。省略するとエラーになります。

第1引数 xml：XML形式の文字列を指定する

有効なXML形式の文字列を指定します。セル参照でも直接入力でも構いません。

指定する文字列は、XMLの構文ルールに従っている必要があります。タグの開始と終了が正しく対応していないと #VALUE! エラーが返ります。

NOTE

XML内に < > & などの特殊文字が含まれると、エラーの原因になります。これらの文字はXMLの予約文字として扱われるためです。

第2引数 xpath：取得するデータのパスを指定する

XPath（XML Path Language）の記法で、取得したい要素の位置を指定します。

たとえば "//商品名" と書くと、XML内のすべての「商品名」タグの値を取得できます。XPathの詳しい書き方は次のセクションで解説します。

XPath記法早見表

FILTERXML関数で使うXPathの記法をまとめました。よく使うパターンを押さえておけば十分です。

XPath記法意味使用例
//タグ名任意の階層でタグを検索"//name"
/root/child絶対パスで指定"/items/item"
root/child相対パスで指定"items/item"
//@属性名属性値を取得"//@id"
//タグ名[n]n番目の要素を取得"//item[2]"
//タグ名[last()]最後の要素を取得"//item[last()]"
//タグ名[position()<=n]先頭n件を取得"//item[position()<=3]"

TIP

XPathのインデックスは 1始まり です。Excelの行番号と同じ感覚で指定できます。

FILTERXML関数の使用例

基本例：XMLデータから特定の要素を取得する

次のようなXML文字列がセルA1に入っているとします。

<fruits><item>りんご</item><item>みかん</item><item>ぶどう</item></fruits>

ここから2番目の要素「みかん」を取り出すには、次の数式を使います。

=FILTERXML(A1,"fruits/item[2]")

結果は みかん です。

すべての要素を取得したい場合は、インデックスを省略します。

=FILTERXML(A1,"fruits/item")

Microsoft 365やExcel 2021以降では、スピル機能で複数セルに結果が展開されます。Excel 2013〜2019では配列数式（Ctrl+Shift+Enter）として入力してください。

WEBSERVICE関数と組み合わせてWebデータを取得する

WEBSERVICE関数（Webからデータを取得する関数）と組み合わせると、Web上のXMLデータを直接取得できます。

=FILTERXML(WEBSERVICE(ENCODEURL(URL)),"XPath")

ENCODEURL関数でURLをエンコードしてから渡すのがポイントです。日本語を含むURLでもエラーを防げます。

NOTE

WEBSERVICE関数の参照先はサービスの仕様変更で動作しなくなる可能性があります。業務で使う場合は、データが取得できなかったときの代替手段も用意しておきましょう。

SUBSTITUTE+FILTERXMLで文字列を分割するテクニック

FILTERXML関数の人気の使い方が、文字列の分割です。仕組みはシンプルで、区切り文字をXMLタグに変換してからFILTERXMLで配列として取り出します。

処理の流れを整理すると次のようになります。

  1. SUBSTITUTE関数で区切り文字をXMLの閉じタグ＋開きタグに変換
  2. 先頭と末尾にルートタグを付けて有効なXML形式にする
  3. FILTERXML関数でXPathを使い、各要素を配列として取得

パターンを一度覚えれば、どんな区切り文字にも応用できます。

カンマ区切りで分割する

セルA2に りんご,みかん,ぶどう という文字列が入っている場合です。

=FILTERXML("<r><n>"&SUBSTITUTE(A2,",","</n><n>")&"</n></r>","r/n")

この数式は内部で次のように変換されます。

  1. SUBSTITUTE で , を に置換
  2. 前後にタグを追加して りんごみかんぶどう になる
  3. FILTERXML が r/n で各 要素を配列として返す

結果は りんご みかん ぶどう の3つの値です。横方向に展開したい場合は、数式全体をTRANSPOSE関数で囲みます。

スペース区切りで分割する

セルA2に 東京 大阪 名古屋 が入っている場合です。区切り文字をスペースに変えるだけです。

=FILTERXML("<r><n>"&SUBSTITUTE(A2," ","</n><n>")&"</n></r>","r/n")

結果は 東京 大阪 名古屋 になります。半角スペースと全角スペースは区別されるので注意してください。

改行（CHAR(10)）で分割する

セル内改行（Alt+Enter）で区切られたデータを分割する場合です。CHAR(10)が改行コードを表します。

=FILTERXML("<r><n>"&SUBSTITUTE(A2,CHAR(10),"</n><n>")&"</n></r>","r/n")

セル内の改行をそのまま区切り文字として扱えます。複数行のデータを個別のセルに展開したいときに便利です。

N番目の要素だけを取り出す

分割した中から特定の位置の値だけがほしい場合は、XPathにインデックスを付けます。

=FILTERXML("<r><n>"&SUBSTITUTE(A2,",","</n><n>")&"</n></r>","r/n[2]")

r/n[2] で2番目の要素だけを返します。最後の要素がほしい場合は r/n[last()] を使ってください。先頭3件なら r/n[position()<=3] です。

MID関数やCONCAT関数を使った文字列操作より、はるかにシンプルに書けるのがメリットです。

FILTERXML関数のエラーと対処法

#VALUE!エラーの原因と対処

FILTERXML関数で #VALUE! エラーが出る主な原因は次の4つです。

原因具体例対処法
XMLの構文が正しくないタグの閉じ忘れ、入れ子の不整合タグの開始と終了を確認する
特殊文字が含まれている< > & がデータ内にあるSUBSTITUTEで事前に除去する
無効な名前空間プレフィックスがある未定義のプレフィックスをXPathで使用XPath記法を見直す
連続した区切り文字があるりんご,,ぶどう のように空要素が生まれる空要素を除去してから処理する

連続区切り文字は見落としやすいポイントです。りんご,,ぶどう のようなデータでは、カンマの間に空の 要素ができます。これが予期しない空白セルとして展開されるため、事前にデータをクリーニングしておきましょう。

IFERRORで空白に置き換える

配列で結果を返す場合、一部のセルでエラーが出ることがあります。IFERROR関数で囲めば、エラーセルを空白に置き換えられます。

=IFERROR(FILTERXML("<r><n>"&SUBSTITUTE(A2,",","</n><n>")&"</n></r>","r/n"),"")

文字列分割で使う場合は、この書き方をセットで覚えておくと安心です。

TEXTSPLITとの使い分け

文字列分割の用途では、TEXTSPLIT関数も選択肢に入ります。それぞれの特徴を比較してみましょう。

比較項目TEXTSPLITFILTERXML+SUBSTITUTE
対応バージョンMicrosoft 365 / Excel 2021以降Excel 2013以降
対応OSWindows / Mac / WebWindows版のみ
数式のシンプルさ◎（1関数で完結）△（3関数の組み合わせ）
N番目の要素指定✕（分割のみ）○（XPathで指定可能）
空要素の扱い空文字で対応可予期しない空白セルが入る

TEXTSPLITが使える環境ならTEXTSPLITを優先してください。数式がシンプルで、Mac・Web版でも動作します。

一方、Excel 2013〜2019を使っている場合はTEXTSPLITが使えません。FILTERXML+SUBSTITUTEが実質的な代替手段になります。また、N番目の要素だけを取り出したい場合は、XPathで柔軟に指定できるFILTERXMLの方が便利です。

まとめ

FILTERXML関数は、XMLデータから必要な値を取り出せる関数です。この記事のポイントをおさらいしましょう。

  • 構文: =FILTERXML(xml, xpath) で、XPathを使って要素を指定する
  • 対応環境: Excel 2013以降のWindows版のみ（Mac・Web版は非対応）
  • 文字列分割: SUBSTITUTEと組み合わせれば、カンマ・スペース・改行で分割できる
  • エラー対策: IFERRORで囲んでおくと安心
  • TEXTSPLITとの使い分け: 使える環境ならTEXTSPLIT優先。Excel 2019以前ならFILTERXML

文字列分割のテクニックは、Excel 2013〜2019ユーザーにとって特に役立ちます。ぜひ実際のデータで試してみてください。他のExcel関数の使い方も、あわせてご覧ください。

スポンサーリンク
タイトルとURLをコピーしました