過去にアップした「Excel FILTER関数」記事の中で「FILTER関数では（残念なことに）ワイルドカード文字が使えません」とご説明しました。
できないと言われるとやってみたいとも思いますし、FILTER関数のようにデータベースから所望のデータを取り出す関数ではワイルドカードによる指定は是非とも対応して欲しいと思います。
ちなみに、Excel関数の中でワイルドカードによる指定ができるものについては「Excel ワイルドカードが使える関数」記事でご紹介しております。
それでは、下図の表データを例に試してみましょう。
G2セルにはワイルドカードによる指定を含ませて「=FILTER(B2:B10,"*山*")」と入力しましたが、「#VALUE!」エラーとなってしまいました。
つまり、「名前」列の中から「山」という文字を含むデータを取り出そうとしました。
(1) ワイルドカードが使える関数を使う
ワイルドカードによる指定をしたいのですから、上記「Excel ワイルドカードが使える関数」記事でご紹介した関数を、FLITER関数に組み入れて（ネストして）実現しようと考えます。
ここでは「COUNTIFS」関数を使ってみます。
数式がやや煩雑になるので、検索セル範囲 B2～B10に「名前」と名前付けしました。
下図の G2セルには次のように入力します：
=FILTER(名前,COUNTIFS(名前,名前,名前,"*山*"))
これで「山」という文字を含むデータを取り出すことができます。
この動作を紐解いてみましょう。
まず「=COUNTIFS(名前,名前)」とすると、「名前」データの数だけ「1」が並びます。
COUNTIFS関数の第1引数は検索範囲、第2引数は検索条件で、ここではそれらが同じセル範囲を参照しているので、「名前」データのすべてのデータはもちろん「名前」範囲内に存在するので一致する個数として「1」が返されます。
次に「=COUNTIFS(名前,名前,名前,"*山*")」とすると、2番目の検索条件「*山*」で「名前」データを検索し、結果として合致する名前データに対して「1」、合致しない名前データに対して「0」と返されます。
一見すると「=COUNTIFS(名前,"*山*")」だけでも良さそうですが、これですと「山」を含む名前データの個数「3」が返されるだけです。
そして、得られた数列「0,0,1,1,1,0,0,0,0」を条件として FILTER関数で取り出します。
数列中「1」となっているものに対応する「名前」データが返されるわけです。
(2) FIND関数と組み合わせる
下図の G2セルには次のように入力します：
=FILTER(名前,IFERROR(FIND("山",名前),0))
先の例と同じく、「山」という文字を含むデータを取り出すことができています。
ここでも分解して動作をみてみます。
まず一番内側の「FIND("山",名前)」部分を実行してみると、名前データ範囲について「山」という文字を見つけます。
その結果「#VALUE!,#VALUE!,1,2,2,#VALUE!,#VALUE!,#VALUE!,#VALUE!」と返されます。
「山」という文字が見つからなかったデータについては「#VALUE!」エラーが返され、「山」という文字を含んでいるデータについては先頭からの文字数が返されます。
この「#VALUE!」エラーのままでは FILTER関数の検索条件とできないので、IFERROR関数を使ってエラーとなっているデータについては「0」としました。
よって「IFERROR(FIND("山",名前),0)」の結果「0,0,1,2,2,0,0,0,0」という数列が得られます。
最後に、得られた数列「0,0,1,2,2,0,0,0,0」を条件として FILTER関数で取り出します。
今回は上記 2つ方法をご紹介しましたが、工夫すると他の方法があるかもしれません。