a関数FILTERを「部分一致」でも検索可能にする裏技

前回の連載で紹介したように、残念ながら「関数FILTER」はワイルドカードに対応していない。せっかく複数件（複数レコード）のデータを抽出できるのに、「完全一致」でしか検索できない・・・、というのは少し不便だ。そこで今回は、関数FILTERで「部分一致」の検索を可能にするテクニックを紹介していこう。

関数FILTERはワイルドカードが使えない・・・

前回の復習も兼ねて、まずは「関数FILTER」の基本的な動作を確認しておこう。

ワイルドカードが使えない関数FILTERの弱点を克服

今回も、会員名簿をまとめた表を使って使い方を紹介していこう。この名簿には、ワークシートの16行目から515行目までに計500件のデータが入力されている。

関数FILTERを使って、この表からデータを抽出するときは、以下の図のように関数を記述すればよい。なお、ここではC2セルに入力した「氏名カナ」でデータを検索するように引数を指定している（詳しくは前回の連載を参照）。

関数FILTERの入力

まずは「タカハシヨシタカ」で検索した例だ。この表には「タカハシヨシタカ」さんが2名いるため、以下の図のように2件のデータが取得された。

「完全一致」するデータが見つかった場合

続いては、「タカハシ」だけで検索した例だ。この場合、データは1件も取得されない。というのも、氏名カナが「タカハシ」だけのデータは1件も存在しないからだ。関数FILTERはキーワードに「完全一致」するデータだけを取得する仕様になっている。

「完全一致」するデータが存在しない場合

念のため、「*」を追加してワイルドカードの使用を試みた例も紹介しておこう。この場合も結果に変わりはない。関数FILTERはワイルドカードに対応していないため、「前方一致」や「後方一致」の検索は行えない。

ワイルドカードの使用を試みた場合

このように、VLOOKUPやXLOOKUPでは使用可能なワイルドカードが、関数FILTERでは使用不可となっている。

関数FILTERは複数件（複数レコード）のデータを取得可能で、ルックアップ系の関数にはない利点を有している。その一方で「ワイルドカードを使えない・・・」という弱点もある。今回は、この弱点を克服するテクニックを紹介していこう。

関数FILTERを「部分一致」に対応させるテクニック

このテクニックは少し複雑なので、先に結論から紹介していこう。関数FILTERを「部分一致」でも検索できるようにするときは、「関数FIND」と「関数IFERROR」を併用して、以下のように関数を記述する。

◆「部分一致」にも対応する関数FILTERの書式
　=FILTER(取得範囲, IFERROR(FIND(キーワード,検索範囲),0), [#CALC!代替])

具体的な例を紹介していこう。たとえば、C2セルに入力した文字（氏名カナ）で検索して、全項目のデータを取得するときは、以下の図のように関数を記述すればよい。

「部分一致」にも対応する関数FILTERの入力

簡単に補足しておこう。今回の例では全項目についてデータを取得するので、「取得範囲」にA16:H515（表全体）を指定している。検索用の「キーワード」はC2セルに入力するので、C2のセル参照を指定する。「検索範囲」は、氏名カナのデータが入力されているセル範囲（C16:C515）を指定すればよい。

このように関数を記述すると、「指定したキーワードを含む」の条件でデータを取得できるようになる。いくつか例を紹介しておこう。

たとえば「カワバタ」というキーワードで検索すると、氏名カナに「カワバタ」を含むデータを取得できる。

「カワバタ」で検索した場合

次は「スガ」のキーワードで検索した場合の例だ。この場合は、須賀（スガ）さんをはじめ、菅井（スガイ）さん、菅野（スガノ）さん、といった会員のデータが取得される。つまり、氏名カナに「スガ」を含む会員のデータをすべて取得できたことになる。

「スガ」で検索した場合

もちろん、後方一致でデータを検索することも可能だ。続いては、「アユミ」のキーワードで検索した例を紹介しておこう。この場合、氏名カナに「アユミ」を含む会員のデータをすべて取得できる。

「アユミ」で検索した場合

このようにFINDとIFERRORを併用すると、関数FILTERを「部分一致」でも検索できるように改良することが可能となる。関数の記述は少し複雑になるが、関数FILTERの使い勝手を大幅に向上させるテクニックとして、覚えておいても損はないだろう。

なお、今回のテクニックでは、関数FILTERの引数を以下のように指定している。

・第1引数（取得範囲）
普通に関数FILTERを使用する場合と同じ。データを取得するセル範囲を指定する。

・第2引数（検索の条件式）
「関数IFERROR」と「関数FIND」で条件式を指定している。この部分の挙動を把握することが、今回のテクニックを理解するポイントとなる。

・第3引数（#CALC!代替）
普通に関数FILTERを使用する場合と同じ。データが1件も見つからなかったとき（#CALC!のエラーが発生したとき）に表示する代替文字を指定する。この引数は、省略しても構わない。

ということで、続いては、第2引数に相当する部分の挙動について詳しく説明していこう。

関数FINDを使ったキーワード検索

関数が入れ子になって記述されているときは、内側にある関数から挙動を調べていくのが基本だ。今回のテクニックでは「関数FIND」が最も内側に記述されている。

関数FINDは「指定した文字列が何文字目に登場するか？」を調べる関数で、以下のような書式で記述する決まりになっている。

◆関数FINDの書式　=FIND(検索文字列, 対象, [開始位置])

第1引数には、検索する文字（キーワード）を指定する。第2引数には、検索となる対象をセル参照などで指定する。第3引数の[開始位置]は省略しても構わない。

簡単な例を紹介しておこう。たとえば、以下の図のように関数FINDを記述すると、「D2（ナビ）の文字がB2（マイナビニュース）の何文字目に登場するか？」を調べることができる。

関数FINDの使用例

「ナビ」の文字は「マイナビニュース」の中で3文字目から始まるので、この結果は「3」という数値になる。

関数FINDの結果（キーワードが見つかった場合）

今度は、検索文字列を「毎」に変更した例を紹介しておこう。「毎」の文字は「マイナビニュース」の中に存在しないため、「#VALUE!」のエラーが発生する。

関数FINDの結果（キーワードが存在しない場合）

このように、関数FINDは「指定した文字列が何文字目に登場するか？」を調べる関数となる。

今回のテクニックでは、FIND(キーワード,検索範囲)という形で関数FINDを記述している。この場合、検索範囲にある各セルに対して関数FINDが実行され、以下のような値が返ってくることになる。

（キーワードが含まれていた場合）
　　→N文字目を示す「数値」が返される

（キーワードが存在しない場合）
　　→「#VALUE!」のエラーが発生する

関数IFERRORを使ったエラー処理

続いては、関数FINDの外側にある「関数IFERROR」について解説していこう。関数IFERRORは、エラーが発生したときに、そのエラーを「指定した値」に置き換える役割を担っている。

◆関数IFERRORの書式
　=IFERROR(関数・数式など, エラー時の値)

第1引数には、関数や数式などを記述する。この関数・数式でエラーが発生したときに「エラーの代わりに返す値」を第2引数に指定する。

こちらも簡単な例を紹介しておこう。以下の図は、B3/C3の計算を実行し、その結果に応じて値を変化させる関数IFERRORの例だ。

関数IFERRORの使用例

まずは、エラーが発生しなかった場合の例だ。この場合は、B3/C3の計算結果がそのまま表示される。以下の図では、8000÷4＝2,000という結果になる。

関数IFERRORの結果（エラーが発生しない場合）

次は、エラーが発生した場合の例だ。C3セルの値を「0」（ゼロ）に変更すると、数式は8000÷0という計算になる。この場合、0での割り算は実行できないことを示す「#DIV/0!」のエラーが発生するが、関数IFERRORにより、このエラーは"人数が不正"という文字列に置き換えられる。

関数IFERRORの結果（エラーが発生した場合）

話を元に戻して、今回のテクニックにおける関数IFERRORの役割を考えていこう。今回のテクニックでは、IFERROR(関数FIND,0)という形で関数IFERRORを記述している。この場合、関数FINDの結果に応じて以下の値が返ってくることになる。

（キーワードが含まれていた場合）
　　→N文字目を示す「数値」がそのまま返される

（キーワードが存在しない場合）
　　→「#VALUE!」のエラーが発生する
　　→エラーが0（ゼロ）に置き換えられる

TRUE/FALSEと数値の対応

これで関数FILTERの「第2引数」に相当する部分の挙動を解明できた。この部分は「検索の条件式」を記述する部分になるため、通常はTRUE（真）またはFALSE（偽）のいずれかが返されることになる。一方、今回のテクニックでは、N文字目を示す「数値」または「0」が返されている。

Excelに詳しい方ならご存じのように、TRUEとFALSEは数値で示すことも可能となっている。一般的に、TRUEは数値の「1」、FALSEは数値の「0」に対応すると言われているが、厳密にはそうではない。正しくは、TRUEは「0以外」の数値、FALSEは数値の「0」となる。これを今回のテクニックに当てはめてみよう。

関数FINDでキーワードが見つかった場合は、N文字目を示す「数値」が返される。この「数値」は1文字目、2文字目、3文字目、・・・といった具合に、必ず1以上の整数になる。よって、条件式の判定はTRUEになる。

一方、関数FINDでキーワードが見つからなかった場合はエラーが発生し、それが関数IFERRORにより「0」に置き換えられる。よって、条件式の判定はFALSEになる。

（キーワードが含まれていた場合）
　　→N文字目の「数値」がそのまま返される
　　→Nは0以外の数値になるのでTRUE

（キーワードが存在しない場合）
　　→エラーが発生するため、0（ゼロ）が返される
　　→数値の0はFALSE

つまり、キーワードに指定した文字が含まれていた場合のみTRUEとして処理され、そのデータだけが取得される、という挙動になる。

理解しやすくするために、データ数を10件に減らした例で「数式の検証」を行ってみよう。この例では「ヤマ」をキーワードにしてデータを取得している。

※「数式の検証」の使い方は、第21回の連載を参照。