FILTER関数の使い方｜複数条件に合うデータを抽出する方法など

エクセルのFILTER関数の使い方を紹介します。指定した条件に一致するデータを抽出したい場合に重宝する関数です。FILTER関数はその名前の通り、エクセルの「フィルター」機能の関数版と言えます。

複数条件に合うデータを抽出する方法も詳しく解説していますので、データ抽出が必要な業務に活かしてみてください。

FILTER関数とは

FILTER関数とは、指定した条件に一致するデータを抽出する関数です。従来のフィルター機能に比べて柔軟性が高く、複数の条件を指定してデータを細かく絞り込めます。またフィルター機能と違って、元の表を残したまま別のセルに抽出できるのが特徴です。

例えば画像のような顧客データで、20代かつ女性の顧客のみを抽出したい時にFILTER関数が活躍します。

FILTER関数を使えるのは、今のところ「Office365」と「Excel2021」のみですので覚えておきましょう。

FILTER関数の書式

FILTER関数の書式は「=FILTER(配列,含む,[空の場合])」です。慣れないと引数の名称が少し分かりづらいと思うので、それぞれ引数の中身を詳しく解説していきます。

第1引数：配列

第1引数にはデータ範囲を指定します。任意の範囲をセル参照で指定することができます。例えばA列の1〜10行目を対象にしたい時は、「A1:A10」というように記述すればOKです。

第2引数：含む

第2引数には、抽出したい条件を設定します。指定した条件に一致するデータだけが抽出されます。例えば性別の欄が「女性」に一致するデータを抜き出したい場合は「”女性”」と指定します。

第3引数：空の場合

第3引数には、条件に一致するデータがない場合に表示させたい値を指定します。例えば「”該当なし”」と指定すると、条件に一致するデータがない場合に「該当なし」というメッセージが表示されます。

この引数は省略可能ですので、FILTER関数は実質2つ引数を指定すれば使用することができます。

FILTER関数の基本的な使い方

それでは早速FILTER関数の基本的な使い方を見てみましょう。ここでは例として、画像のような顧客データを使用して解説していきます。

顧客データの中から、性別が女性である顧客のみのデータを抽出してみましょう。抜き出したデータを表示させるスペースがあるところにFILTER関数を入力します。例では元の表の下に入力していきます。

入力する関数は「=FILTER(A2:E11,B2:B11=”女性”)」となります。対象となる表の範囲を「A2:E11」で指定し、条件として「B2:B11=”女性”」を指定しています。「女性」という文字列をダブルクォーテーション「”」で囲むのを忘れないようにしてください。

関数を入力し終えたら「Enter」キーを押しましょう。これで、セルA14からE20までのように、条件に当てはまるデータが元の表から全て抽出されました。

このように、FILTER関数を使用すれば元の表を変更することなく、条件に合致したデータのみを抜き出すことができます。

FILTER関数で「#CALC!」エラーになる場合の対応方法

FILTER関数を使っていると、「#CALC!」というエラーが出ることがあります。「#CALC!」エラーは、エクセルの計算エンジンがサポートしていないシナリオが発生したときに表示されます。

FILTER関数では第3引数を省略した場合に「#CALC!」エラーが指定されると考えましょう。つまり対処方法としては第3引数に何らかの文字列を指定すればOKです。具体的な対策をみていきましょう。

指定の値（数字や文字列）を表示させる

数字や文字列などを指定して表示させてみましょう。FILTER関数の第2引数で指定した条件に該当するデータがない場合、「該当データがありません」と表示させてみましょう。

例えば、画像のようなデータで指定した住所（都道府県）に該当する顧客を抽出してみましょう。ここではわざと該当するデータがないような条件を指定してください。例では「=FILTER(A2:E11,D2:D11=”福岡”,”該当データがありません”)」と入力しています。

関数を入力し終えたら「Enter」キーを押しましょう。指定した範囲の住所（都道府県）の中に「福岡」がなかったため、「該当データがありません」というメッセージが表示されました。

表示内容は、数値や文字列、セル参照、関数など様々に指定することができます。条件や表の内容に合わせて実行するアクションをカスタマイズしてみてください。

また該当データがない時は何も表示させない（空白を表示する）ことも可能です。やり方を次の章で詳しく説明します。

セルを空白で表示する

条件に合致するデータがない場合、セルを空白で表示させることも可能です。FILTER関数の第3引数に「””」と入力しましょう。

指定した条件に該当するデータがない場合は画像のように空白となります。このように、「””」を指定して空白を表示させることは他の関数でも使える技なので覚えておくとよいでしょう。

FILTER関数で複数条件に合うデータを抽出する方法

応用的な使い方として、FILTER関数で複数の条件に合うデータを抽出する方法を紹介します。「OR」「AND」などの論理演算子を使用して、少し複雑な条件を指定していきます。

FILTER関数でOR条件を指定

FILTER関数でOR条件を指定する時の書式は「=FILTER(配列, 条件1+条件2,[空の場合])」です。第2引数にプラス「+」の記号を使って複数の条件を設定します。OR条件を指定することで、複数の異なる条件のいずれかを満たすデータを抽出することができます。

例えば、20代または30代の顧客を抽出してみましょう。例では「=FILTER(A2:E11, (C2:C11=”20代”)+(C2:C11=”30代”))」となります。

これは、(C2:C11=”20代”)の箇所で20代の顧客、(C2:C11=”30代”)の箇所で30代の顧客のデータを条件として指定しており、それぞれの条件を「+」記号で繋いで「または」となるように指定しています。

関数を入力して「Enter」キーを押すと、20代と30代の顧客のデータが抽出されます。

このように、複数ある条件のうちいずれかを満たすデータを抽出したい時に「+」の記号を用いてOR条件を指定します。

FILTER関数でAND条件を指定

FILTER関数でAND条件を指定する時の書式は「=FILTER(配列, 条件1*条件2,[空の場合])」です。第2引数にアスタリスク「*」の記号を使って複数の条件を設定します。AND条件を指定することで、複数の異なる条件を全て満たすデータを抽出することができます。

例えば、20代かつ女性の顧客を抽出してみましょう。例では「=FILTER(A2:E11, (C2:C11=”20代”)*(B2:B11=”女性”))」となります。

これは、(C2:C11=”20代”)の箇所で20代の顧客、(B2:B11=”女性”)の箇所で女性の顧客のデータを条件として指定しており、それぞれの条件を「*」記号で繋いで「かつ」となるように指定しています。

関数を入力して「Enter」キーを押すと、20代かつ女性の顧客のデータが抽出されます。

このように、複数ある条件の全てを満たすデータを抽出したい時に「*」の記号を用いてAND条件を指定します。

FILTER関数とUNIQUE関数を組み合わせた使い方

最後に、FILTER関数とUNIQUE関数を連携させた使い方を紹介しましょう。素早くドロップダウンリストを作成して、より本格的なフィルタリングシステムをつくれるようになります。

UNIQUE関数とは

UNIQUE関数とは、複数の値の中から重複を取り除き、固有の値だけを取り出すことができる関数です。例えば「A,A,B,A,C,A,B,B,C,C,D」というデータがあった時、UNIQUE関数を使えば「A,B,C,D」が抽出されます。

手動で行なうと時間がかかり、ミスも増える作業ですが、UNIQUE関数を使えば大量のデータから重複なく値を取り出せます。

参考：UNIQUE関数の使い方｜重複なしのデータをリストにまとめる方法

条件に合う重複なしのデータを抽出する方法

重複なしのデータを取り出してドロップダウンリストをつくり、条件に合うデータを抽出してみましょう。例ではドロップダウンリストから年代を選択できるようにして、選んだ年代に合う顧客データ全てを抽出します。

まずUNIQUE関数を使用してドロップダウンリストを作成します。ドロップダウンリストの元となる固有値を取得するため、空セルに「=UNIQUE(C2:C11)」と入力しましょう。

Enterキーを押すと、選択した範囲から重複のない固有の値のみが表示されます。例では「10代以下,20代,30代,40代,50代以上」のカテゴリーが抽出されました。

次にドロップダウンリストを作成したいセル（例ではA13）を選択した状態で「データ」タブの「データツール」にある「データの入力規則」から「データの入力規則（V）」を左クリックしましょう。

「設定」タブの「入力値の種類(A)」の「リスト」を選択します。そして、「元の値(S)」に「$G$2:$G$6」と入力してOKボタンを押してください。

これで年代を選択するドロップダウンリストの完成です。

後はFILTER関数を使用して、ドロップダウンリストから選んだ値に合致するデータの抽出を行ないます。結果を表示させたいセルに「=FILTER(A2:E11,C2:C11=A13,””)」と入力しましょう。これでドロップダウンリストから選んだ項目と合致するデータを抽出する仕組みが完成しました。

試しにドロップダウンリストから「30代」を選択してみましょう。30代の顧客のデータが全て抽出されます。

FILTER関数についておさらい

FILTER関数は、指定した条件に合致するデータを抽出するのにとても便利な関数です。フィルター機能があれば不要に思われるかもしれませんが、関数を使用するメリットには次のようなものがあります。

• 元の表はそのままで変更されない
• 他の関数と組み合わせることができる
• 関数をコピーすれば他の表にも適用できる

このように柔軟で高度な使い方ができますので、自分の環境に合わせてオリジナルのシステムを構築してみましょう。