FILTER関数は、単体ではワイルドカードを使って部分一致を使うことができない。
しかし、ワイルドカードを使えるCOUNTIFS関数と組み合わせることで部分一致のフィルタリングをすることができる。
FILTER関数の基本
FILTER関数の基本的な使い方は下の記事を参考。
ここでは、書式のみ確認する。
FILTER関数の書式は下記です。
=FILTER(配列, 含む, [空の場合])
配列：データを抽出する全体を指定する。
含む：抽出したい列と抽出条件を指定する。
空の場合(省略可)：含むの条件に該当するデータがない場合の表示を指定する。指定しない場合は「#CALC!」と表示される。
第一引数でフィルタリングしたいデータ全体を選択、第二引数で抽出条件を指定する。
FILTER関数で部分一致のやり方
FILTER関数の第二引数にCOUNTIFS関数を使うことでワイルドカードを使った部分一致を実現できる。
FILTER関数の書式は下記です。
=FILTER(配列, COUNTIFS(検索範囲, 検索範囲, 検索範囲, 検索値), [空の場合])
配列：データを抽出する全体を指定する。
空の場合(省略可)：含むの条件に該当するデータがない場合の表示を指定する。指定しない場合は「#CALC!」と表示される
COUNTIFS関数：
第一引数、第二引数、第三引数は全て検索範囲。
第四引数はワイルドカードを含んだ検索値。
ワイルドカードは「*」と「?」が使える。
| ＊ | 任意の文字数の文字列を表す |
| ？ | 任意の一文字を表す |
任意の文字の文字数を指定したい場合は、文字数分の「？」を使う。
文字数が不明な場合もしくは変動する場合は「＊」を使う。
FILTER関数の部分一致の例
市区町村のデータから特定の文字を含む市区町村を部分一致で抽出する例。
市区町村のデータが約2200行のデータでワイルドカードを使ってデータをフィルタリングする。
テーブル名は「市区町村のデータ」である。テーブルについてはこちらを参照
「市区町村のデータ」のテーブルの「市区町村」の列で部分一致のフィルタリングをするときの関数は下記。
K1はワイルドカードを使った検索値が記載されたセル番号である。
=FILTER(市区町村データ,COUNTIFS(市区町村データ[市区町村],市区町村データ[市区町村],市区町村データ[市区町村],K1))
例えば、前方一致で「札幌」から始まる文字列の行を抽出するなら検索値は「札幌*」とする。
参考までに、抽出した研修をヒット数として表示している。
後方一致で「村」で終わる行を抽出するなら検索値を「*村」とする。
部分一致で「赤」を含む市区町村を抽出するなら検索値を「*赤*」とする。
【補足】なぜFILTER関数の部分一致がCOUNTIFS関数で指定できるのか
COUNTIFS関数は複数の条件に一致するデータの個数を求めるための関数です。
書式は下記になる。
=COUNTIFS（範囲1, 検索条件1, 範囲2, 検索条件2, …）
範囲X：データを抽出する全体を指定する。
検索条件X：抽出したい列と抽出条件を指定する。
ここで、検索条件を範囲で選択するとことで、スピル機能を使うことができる。
範囲１と検索条件１を同じ範囲で選択すると、下記のようにスピル機能で複数の検索範囲の結果を返すことになる。（もし同じデータがあれば、2とか3とかの結果もある。）
=COUNTIFS(市区町村データ[市区町村],市区町村データ[市区町村])
例え
範囲2も市区町村で検索値をワイルドカードを使った値を使用すると、その検索値に一致する行は１以上となり、合致しなければ０となる。
=COUNTIFS(市区町村データ[市区町村],市区町村データ[市区町村],市区町村データ[市区町村],K1)
検索値を「札幌*」とすると下記のようになる。
FILTER関数の第二引数の「含む」は一致した値はTRUEが返り、一致しなかった値は「FALSE」が返る。
０はFALSEと同じ意味となり、1以上の値はTUREと同じ意味となるためCOUNTIFS関数を上記のように使用することで、ワイルドカードを使ったFILTERができる。
