LOOKUPやVLOOKUP、XLOOKUPといった関数には共通した弱点がある。それは1件分（1レコード分）のデータしか取得できないことだ。検索値に合致するデータが複数件あったとしても、実際には1件のデータしか取得できない仕様になっている。そこで、条件に合うデータをリスト化して全取得する関数FILTERの使い方も覚えておくとよい。

ルックアップ関数で習得できるデータは1件のみ

大きな表から「目的のデータ」を取得するときは、VLOOKUPやXLOOKUPといったルックアップ系の関数がよく利用されている。ただし、これらの関数には共通する弱点がある。それは、1件分（1レコード分）のデータしか取得できないことだ。このため、目的としているデータを取得できない、他の合致データを見落としてしまう、などのトラブルが生じる恐れがある。

このような場合に備えて「関数FILTER」の使い方も覚えておくとよい。なお、この関数は2019年にリリースされた新しい関数となるため、Microsoft 365（旧Office 365）またはExcel 2021でのみ使用できる関数となる。

  • 複数レコードを抽出可能な関数FILTERの使い方

今回も、会員名簿をまとめた表を使って具体的な例を紹介していこう。この名簿には、ワークシートの16行目から515行目までに計500件のデータが入力されている。前回までの連載で用いた表とよく似ているが、今回の表は、H列に「生年月日」ではなく「誕生日」が入力されている。

  • 会員情報をまとめた表

この「誕生日」を検索値にして会員データを取得するケースを考えてみよう。まずは、前回の連載で紹介した関数XLOOKUPを使ってデータを取得する場合の例だ。引数は、それぞれ以下のように指定した。

　第1引数（検索値）・・・・ C2（以下の図の場合、6/15が検索値になる）
　第2引数（検索範囲）・・・ H16:H515（誕生日のデータのセル範囲）
　第3引数（取得範囲）・・・ A16:H515（表全体）

  • 関数XLOOKUPの入力

関数XLOOKUPはスピルに対応しているため、1つの関数で「ID」～「誕生日」の全項目を取得できる（スピルの詳細については前回の連載を参照）。今回の例では、以下のような結果が表示された。

  • 関数XLOOKUPにより取得されたデータ

確かに「誕生日が6/15」の会員データを正しく取得できている。しかし、「ほかにも誕生日が6/15の会員が存在するのでは？」という不安は拭えない。会員数が500人もいれば、誕生日が6/15の会員が2人以上いたとしても不思議はないだろう。

ただし、これをルックアップ系の関数で調べることはできない。というのも、VLOOKUPやXLOOKUPは、検索値に合致するデータのうち「最初に発見したデータ」だけを取得する仕様になっているからだ。複数件（複数レコード）のデータ抽出には対応していない。

関数FILTERを使ったデータの抽出

このような場合に活用できるのが「関数FILTER」だ。この関数はExcelのフィルター機能を関数化したようなもので、以下に示した書式で記述する仕様になっている。

◆関数FILTERの書式
　=FILTER(取得範囲, 検索の条件式, [#CALC代替])

第1引数には「データを取得する範囲」を指定する。関数FILTERもスピルに対応しているため、複数列のセル範囲を指定することも可能だ。

第2引数には「検索範囲」と「検索値」を条件式で記述する。たとえば、「誕生日がC2セルに等しい」という条件で検索するときは、「H16:H515=C2」と条件式を記述すればよい。なお、検索対象が数値の場合は、「<」（より小さい）や「=>」（以上）などの比較演算子を用いることも可能だ。

ここでは、それぞれの引数を以下のように指定した。

　第1引数（検索範囲）・・・ A16:H515（表全体）
　第2引数（条件式）・・・・ H16:H515=C2（誕生日がC2セルに等しい）

  • 関数FILTERの入力

「Enter」キーを押して関数FILTERを実行すると、下図のような結果が得られた。

  • 関数FILTERにより取得されたデータ

この結果を見ると、誕生日が6/15の会員は「桑原 鉄夫」さんだけでなく、ほかにも2名存在していることを確認できる。

このように関数FILTERを使うと、条件に合致するデータを複数件にわたって取得することが可能となる。スピルに対応しているため、1つの関数で複数フィールド、複数レコードのデータを取得することが可能だ。

誕生日を変更して検索した例も紹介しておこう。たとえば、誕生日を11/14に変更すると、下図に示したように6件のデータが取得された。つまり、誕生日が11/14の会員は6名いるという訳だ。

  • 検索条件（誕生日）を変更した例

なお、合致データの件数が多く、取得データを表示するスペース（空白セル）が足りなくなってしまった場合は、「#スピル!」というエラーが表示される。このため、関数FILTERを利用するときは、その下に十分な数の空白行を設けておく必要がある。

条件に合致するデータが1件も見つからなかった例も紹介しておこう。この場合は「#CALC!」というエラーが表示される。

  • 該当データが存在しないことを示すエラー

この「#CALC!」のエラーが発生したときに、代替文字を表示することも可能だ。この場合は、関数FILTERの第3引数に「表示する文字」を指定しておけばよい。たとえば、以下の図のように第3引数を指定しておくと、合致データが見つからなかったときに「該当なし」の文字列が表示されるようになる。

  • 第3引数の入力

  • 「#CLAC!」のエラーを代替文字で表示した例

取得範囲と検索範囲の関係

続いては、「取得範囲」と「検索範囲」の関係について紹介しておこう。これまでの例では表全体を取得範囲に指定していたが、表の一部分だけを取得範囲に指定しても特に問題は生じない。

たとえば、取得範囲をA～D列だけに限定して、以下のように関数FILTERを記述してもよい。

  • 「取得範囲」と「検索範囲」が異なる関数FILTER

この場合、「誕生日」の列を条件にして、「ID」、「氏名」、「氏名カナ」、「性別」のデータを取得する、という動作になる。

  • 関数FILTERにより取得されたデータ

このように「検索範囲」が「取得範囲」に含まれていなくても、関数FILTERを問題なく使用することが可能だ。

極端な話、「取得範囲」を1列だけにして、関数VLOOKUPのような使い方をすることも可能だ。合致するデータが複数件、見つかった場合は、

　・下のセルにもデータが表示される
　・「#スピル!」のエラーが表示される

のいずれかの状態になるので、「他の合致データ」を見落としてしまうトラブルを回避できるかもしれない。工夫次第で便利に活用できる可能性を秘めているので、各自でも色々と研究してみるとよいだろう。

ワイルドカードの使用について

最後にワイルドカードを使った「部分一致」の検索について触れておこう。今度は「氏名カナ」で検索するように、関数FILTERを書き換えた例で挙動を紹介していく。

  • 「氏名カナ」でデータを検索する関数FILTER

上図の例では、指名カナが「タカハシ ヨシタカ」に一致するデータを一括取得することになる。その結果は、以下の図のとおり。

  • 関数FILTERにより取得されたデータ

どうやら、この会員名簿には「タカハシ ヨシタカ」さんが2名いるようだ。では、もっと曖昧にして「タカハシ」だけで検索した場合はどうなるだろうか？

  • 「部分一致」で検索した場合

この場合は「該当なし」（#CLAC!エラーの代替文字）という結果が表示された。それもそのはず。関数FILTERは「完全一致」でデータを検索する仕様になっているからだ。この表には、氏名カナが「タカハシ」だけのデータは存在しない。よって、エラー（または代替文字の表示）になってしまう。

ワイルドカード（*）を使って検索した場合も同様の結果になる。関数FILTERはワイルドカードに対応していないため、「前方一致」や「後方一致」などの検索は基本的に不可となる。

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

複数件のデータをリスト化して取得できるのに、部分一致には対応していない・・・、という状況では「用途が限定されてしまう」と感じる方もいるだろう。そこで、関数FILTERを部分一致に対応させるテクニックを紹介しておこう。

このテクニックを使うと、その文字を含むデータを一括取得することが可能となる。たとえば、「タカハシ」で検索すると、氏名カナに「タカハシ」を含むデータをすべて取得できる。

  • 「部分一致」の検索も可能にした関数FILTER（1）

上図の例では、先ほどと同じ、2名の「タカハシ ヨシタカ」さんのデータが取得された。少し面白みに欠けるので、「ヨシタカ」だけで検索した例も紹介しておこう。

  • 「部分一致」の検索も可能にした関数FILTER（2）

今度は、「タカハシ ヨシタカ」さんに加えて、「ミズシマ ヨシタカ」さんのデータも取得された。つまり、氏名カナに「ヨシタカ」を含む会員は3名いるという訳だ。

このように多少の工夫を施すことで、関数FILTERを「部分一致」に対応させることも可能である。少し話が長くなってしまったので、このテクニックの詳細については次回の連載で紹介することにしよう。