FILTER関数 ● 表や配列からデータを抽出する (2)抽出条件の指定例

［Excel 2021以降］

Excelの FILTER（フィルター）関数の抽出条件の指定例を紹介します。FILTER関数は、セルに入力された表や表形式の配列から条件に合致するデータを抽出する関数です。基本的な使い方や列を抽出する方法は「FILTER関数 ● 表や配列からデータを抽出する (1)基本の使い方」を参照してください。

書式

表や配列からデータを抽出する
=FILTER(配列, 条件, ［見つからない場合］)

［配列］から［条件］に合致するデータを抽出します。条件に合致するデータが存在しない場合は［見つからない場合］に指定した値を表示します。関数式は、動的配列数式として入力されます。

■引数

使用例1…基本中基本　完全一致の条件

完全一致の条件で抽出するには、引数［条件］に「セル範囲=値」の形式の論理式を指定します。
図1では、ランクのセルに「A」が入力されている会員データを抽出します。条件となる「A」はセルF3に入力されているものとします。なお、FILTER関数は［配列］に含まれる日付をシリアル値（日付に対応する数値）で戻すので、抽出結果の日付には適宜日付の表示形式を設定する必要があります。

セル I3 | =FILTER(A3:D10,C3:C10=F3,"該当なし")

図1　ランクが「A」である会員データが抽出される。
セルL3～L5には、［ホーム］タブの［数値］グループにある［数値の書式］の一覧から［日付］の表示形式が設定してある。

ちなみに、条件となる値を数式の中で直接指定する場合は、値の種類に応じて下表のように指定してください。特に日付は「"2024/4/1"」形式では正しい抽出結果にならないので注意してください。

■値を直接指定する場合の指定例

使用例2…「○○以外」の条件で抽出する

「○○以外」のような条件を表すには比較演算子の「<>」を使用します。
図2では、ランクが「A」以外という抽出条件を指定しています。

セル I3 | =FILTER(A3:D10,C3:C10<>F3,"該当なし")

図2　ランクが「A」以外である会員データが抽出される。

使用例3…AND条件で抽出する

「条件A かつ 条件B」のようなAND条件を指定するには、複数の条件を半角のアスタリスク「*」でつなぎます。
図3では、「住所が東京都」かつ「勤務地が東京都」という抽出条件を指定しています。住所と勤務地の両方が東京都である会員データが抽出されます。

セル I3 | =FILTER(A3:D10,(C3:C10=F3)*(D3:D10=G3),"該当なし")

図3　住所と勤務地の両方が東京都である会員データが抽出される。

使用例4…OR条件で抽出する

「条件A または 条件B」のようなOR条件を指定するには、複数の条件を半角のプラス「+」でつなぎます。
図4では、「住所が東京都」または「勤務地が東京都」という抽出条件を指定しています。住所と勤務地の少なくとも一方が東京都である会員データが抽出されます。

セル I3 | =FILTER(A3:D10,(C3:C10=F3)+(D3:D10=G3),"該当なし")

図4　住所と勤務地の少なくとも一方が東京都である会員データが抽出される。

図4では異なる列に対してOR条件を指定しましたが、同じ列に対してOR条件を指定することも可能です。例えば次の数式では、住所が「千葉県」または「埼玉県」のデータが抽出されます。

住所が「千葉県」または「埼玉県」のデータを抽出する
=FILTER(A3:D10,(C3:C10="千葉県")+(C3:C10="埼玉県"),"該当なし")

使用例5…数値の範囲を指定して抽出する

数値の範囲を指定するには、「>（より大きい）」「>=（以上）」「<（より小さい）」「<=（以下）」などの比較演算子を使用します。 図5では、年齢が30歳以上の会員データが抽出されます。

セル I3 | =FILTER(A3:D10,C3:C10>=F3,"該当なし")

図5　年齢が30歳以上の会員データが抽出される。

「○以上○以下」「○以上○未満」のような条件を指定したい場合は、2つの条件を「*」でつないだAND条件を指定してください。

「30歳以上40歳未満」のデータを抽出する
=FILTER(A3:D10,(C3:C10>=30)*(C3:C10<40),"該当なし")

使用例6…日付の範囲を指定して抽出する

日付の範囲を指定する場合も、「>（より後）」「>=（以降）」「<（より前）」「<=（以前）」などの比較演算子を使用します。 図6では、入会日が「2024/4/1」以降の会員データが抽出されます。

セル I3 | =FILTER(A3:D10,D3:D10>=F3,"該当なし")

図6　入会日が「2024/4/1」以降の会員データが抽出される。

「2024/4/1から2024/12/31まで」のような条件を指定したい場合は、2つの条件を「*」でつないだAND条件を指定してください。

「2024/4/1から2024/12/31まで」のデータを抽出する
=FILTER(A3:D10,(D3:D10>=DATE(2024,4,1))*(D3:D10<=DATE(2024,12,31)),"該当なし")

使用例7…日付の「年」を指定して抽出する

日付が入力されたセル範囲から特定の「年」のデータを抽出するには、YEAR関数で日付から「年」を取り出して条件を指定します。

セル I3 | =FILTER(A3:D10,YEAR(D3:D10)=F3,"該当なし")

図7　入会日が「2024年」である会員データが抽出される。

同様に、「年月」を抽出するにはTEXT関数、「四半期」を抽出するにはCHOOSE関数とMONTH関数を使用します。

「2024年4月」のデータを抽出する
=FILTER(A3:D10,TEXT(D3:D10,"yyyy/mm")="2024/04","該当なし")

年度の始まりを4月として「第3四半期」のデータを抽出する
=FILTER(A3:D10,CHOOSE(MONTH(D3:D10),4,4,4,1,1,1,2,2,2,3,3,3)=3,"該当なし")

使用例8…「○○で始まる」「○○で終わる」の条件で抽出する

「○○で始まる」という条件に合うデータを抽出するには、LEFT関数で文字列の先頭から部分文字列を取り出して「○○」に一致するかチェックします。
図8では、住所が「東京都」で始まる会員データを抽出します。住所から先頭3文字分を取り出して「東京都」と比較します。

セル I3 | =FILTER(A3:D10,LEFT(D3:D10,3)=F3,"該当なし")

図8　住所が「東京都」で始まる会員データが抽出される。

反対に、「○○で終わる」という条件に合うデータを抽出するには、RIGHT関数で文字列の末尾から部分文字列を取り出します。

住所が「市」で終わるデータを抽出する
=FILTER(A3:D10,RIGHT(D3:D10,1)="市","該当なし")

使用例9…「○○を含む」の条件で抽出する

「○○を含む」という条件に合うデータを抽出するには、IFERROR関数とFIND関数を使用します。FIND関数は、引数を「FIND(検索文字列,対象)」のように指定して、［対象］の文字列から［検索文字列］の位置を返す関数です。［対象］の中に［検索文字列］が見つかった場合は何文字目に見つかったかの数値が返り、見つからなかった場合はエラー値が返ります。つまり、FIND関数の戻り値が数値であれば［対象］の中に［検索文字列］が含まれ、エラー値の場合は含まれないと判断できます。
図9では、住所に「県」を含む会員データを抽出します。

セル I3 | =FILTER(A3:D10,IFERROR(FIND(F3,D3:D10)>0,FALSE),"該当なし")

図9　住所に「県」を含む会員データが抽出される。

文字列の中に特定の文字列が含まれるかどうかを調べる関数には、FIND関数のほかにSERACH関数（書式は「SEARCH(検索文字列,対象)」）があります。図9の例は、どちらの関数を使用しても同じ結果になります。
検索する文字列がアルファベットの場合、FIND関数が大文字と小文字を区別するのに対して、SEARCH関数は区別しないので、目的に応じて使い分けてください。
また、ワイルドカードを使用した検索にはSEARCH関数を使用します。FIND関数では、「*」や「?」をワイルドカードではなくそのままの文字として検索するので注意してください。

住所が「東京都○○区」のデータを抽出する
=FILTER(A3:D10,IFERROR(SEARCH("東京都*区",D3:D10)>0,FALSE),"該当なし")

使用例10…抽出結果に表示される空白セルの「0」を空白セルのまま表示する

引数［配列］に指定したセル範囲に空白のセルがあると、抽出結果のセルに「0」が表示されます。

セル I3 | =FILTER(A3:D10,C3:C10=F3,"該当なし")

図10　元の表に空白セルがあると抽出結果に「0」が表示される。

「0」を表示せずに空白セルのままにしておきたい場合は、［配列］を指定する際に空白セルを「""」に置き換えてください。なお、抽出結果の空白セルは見た目が空白なだけで、実際には「""」が入力されたのと同じ状態になります。

セル I3 | =FILTER(IF(A3:D10="","",A3:D10),C3:C10=F3,"該当なし")

図11　抽出結果も空白のまま表示する。

使用例11…抽出したデータを並べ替えて表示する

抽出結果を並べ替えて表示するには、SORT関数とFILETR関数を組み合わせて使用します。
図12では、30歳以上の会員データを抽出して、年齢の昇順に並べ替えて表示します。

セル I3 | =SORT(FILTER(A3:D10,C3:C10>=F3,"該当なし"),3)

図12　30歳以上の会員を年齢の昇順に並べ替えて表示する。

使用例12…テーブルからデータを抽出する

ここまでの使用例の抽出対象は、すべて通常の表のセル範囲でした。しかし実際にはテーブルから抽出を行うケースも多いでしょう。数式の入力中にテーブル内のセルをクリックしたりドラッグしたりすると、セル番号ではなく、「構造化参照」と呼ばれる特殊なセル参照が入力されます。
例えばテーブル名が「テーブル1」の場合、テーブルの見出しを除いたデータ範囲（下図ではセルA3～D10）をドラッグすると、「A3:D10」の代わりに「テーブル1」という構造化参照が入力されます。また、年齢の列（下図ではセルC3～C10）をドラッグすると、「C3:C10」の代わりに「テーブル1[年齢]」という構造化参照が入力されます。数式の中で特定のセル番号の代わりに構造化参照を使うことで、テーブルのデータ数に増減があった場合でも、常にデータ範囲全体や列全体を対象とした抽出を行えます。

セル I3 | =FILTER(テーブル1,テーブル1[年齢]>=F3,"該当なし")

図13　「テーブル1」の「年齢」列の値が30以上の会員データを抽出する。

関連記事

• FILTER関数 ● 表や配列からデータを抽出する (1)基本の使い方
• SORT関数 ● データを指定した順序で並べ替える
• 条件付き書式…スピルした範囲に自動で色や罫線を表示