Excel(エクセル）のデータから必要な情報を抽出するとき、「フィルター」機能が役立ちます。
ただ、表にフィルターを設定してから、複数の条件でデータを絞るのは意外と時間がかかってしまいます。
そんな時に便利なのが、新たに登場したFILTER関数です。
FILTER関数を使うと、元データは動かさずに、別の空いたセルに抽出後のデータを瞬時に作成することができます。
この記事では、FILTER関数で複数の条件でデータを抽出する方法と、応用編の使い方を紹介します。
FILTER関数とは？
FILTER関数の読み方は、『フィルター関数』です。
その名前のとおり、FILTER関数は、『複数の条件に合うデータを抽出する』関数です。
皆さんお馴染みの『フィルター』機能も便利ですが、FILTER関数を使うと、より高度な設定が可能となります。
例えば、AND条件やOR条件といった条件も自在に設定することができるうえ、３つや４つの複数条件を設定することもできます。
さらに、FILTER関数を使うと、別の空いたセルに抽出後のデータを作成することができるので、『元のデータの形は動かしたくない！』という方にはおすすめです。
ただ、FILTER関数を利用できるExcelバージョンは、Microsoft365(サブスク)とExcel2021以降と限定的です。使えないという方は、フィルター機能を利用してください。
FILTER関数の書式と引数
FILTER関数の書式は以下のとおりで、３つの引数を設定する必要があります。
＝FILTER( 配列,含む,[空の場合]）
第１引数『配列』：データを絞りたいセル範囲を指定します。
第２引数『含む』：抽出する条件を指定します。
第３引数『空の場合』：省略可能です。指定した条件にあうデータが存在しない(＝空)のときに返す値を指定します。※省略した場合、「#CALC!」が表示
FILTER関数の「条件」の設定方法
FILTER関数の第２引数『含む』には、データを抽出する条件を設定します。
『以上』『より上』『以下』『未満』『等しい』『以外』を条件に設定するためには、下表の比較演算子(記号)を利用してください。
例えば、データの中から30以上の数値を抽出したい場合は、『=FILTER(B3:D9,D3:D9>=30,"")』といったように入力します。
| 条件 | 符号 | 直接入力する場合 | セルを指定する場合 |
| 以上 | >= | D3:D9>=30 | D3:D9>=E5 |
| より大きい | > | D3:D9>30 | D3:D9>E5 |
| 未満 | < | D3:D9<30 | D3:D9<E5 |
| 以下 | <= | D3:D9<=30 | D3:D9<=E5 |
| 等しい | = | D3:D9=30 | D3:D9=E5 |
| 以外 | <> | D3:D9<>30 | D3:D9<>E5 |
【基本編】FILTER関数の使い方
FILTER関数の基本的な使い方を３つ紹介します。
FILTER関数の基本編
１．単一列に条件を指定して抽出する
２．複数列にOR条件(もしくは)を指定して抽出する
３．複数列にAND条件(なおかつ)を指定して抽出する
事例１．単一列に条件をつける
下のB～C列のデータから、性別が『男』のデータをFILTER関数で抽出します。
まず、FILTER関数を挿入する前に、横の空いたスペースに行見出しを作成します。
行見出しの左下のセルに、以下のFILTER関数を挿入します。
第１引数「配列」 ⇒ データ範囲は「B3:D11」を指定
第２引数「含む」 ⇒『C列が「男」』という条件を設定するため「C3:C11="男"」と入力
第３引数「空の場合」 ⇒ 条件に合わないものが無い場合は空白を表示させるため、「””」と入力
=FILTER(B3:D11,C3:C11="男","")
Enterキーを押してFILTER関数を確定させてください。そうすると、C列が「男」だけのデータを抽出することができます。
FILTER関数はF3セルの１つのセルにしか挿入していませんが、スピル機能によりF73セル以外のセルにも抽出された結果が拡張されます。
D列の値が「70以上」のデータを抽出する場合は、以下のように「>=」の記号を使ってFILTER関数を利用します。
=FILTER(B3:D11,D3:D11>=70,"")
第3引数の「空の場合」は省略できますが、該当するデータが無い場合は、セルに「CALC!」というエラー値が表示されます。
このエラー値を表示させたくない場合は、第３引数は「””」や「”該当なし”」「”-”」などを指定するようにしましょう。
事例２．複数列×OR条件で利用
FILTER関数で複数の列に対してOR条件でデータを抽出することができます。
OR条件とは、AかBのどちらかを満たしている条件のことを言います。
OR条件でFILTER関数を利用する書式は以下のとおりです。
FILTER関数の第２引数の「含む」に、２つの条件式をそれぞれカッコで囲って、『+』を間に挟んで、『(条件式1)+(条件式2)』という形で指定します。
=FILTER(配列,(条件式1)+(条件式2),[空の場合])
実際にOR条件でFILTER関数を使った事例を１つ紹介します。
下の表から、「算数が60点以上」もしくは「国語が60点以上」という条件で、FILTER関数でデータを抽出することができます。
=FILTER(B3:D10,(C3:C10>=60)+(D3:D10>=60),"")
事例３．複数列×AND条件で利用
FILTER関数で複数の列に対してAND条件でデータを抽出することができます。
AND条件とは、AなおかつBを満たしている条件のことを言います。つまり、指定した２つの条件が重なる部分だけを抽出することをいいます。
AND条件でFILTER関数を利用する書式は以下のとおりです。
FILTER関数の第２引数の「含む」に、２つの条件式をそれぞれカッコで囲って、『*』を間に挟んで、『(条件式1)*(条件式2)』という形で指定します。
=FILTER(配列,(条件式1)*(条件式2),[空の場合])
実際にAND条件でFILTER関数を使った事例を１つ紹介します。
下の表から、「性別が”男”」でなおかつ「体重が60キロ以上」という条件で、FILTER関数でデータを抽出することができます。
=FILTER(B3:D11,(C3:C11="男")*(D3:D11>60),"")
【応用編】FILTER関数の特殊な使い方
FILTER関数の応用テクニックを３つ紹介します。
FILTER関数の応用テクニック
１．３つ以上の複数条件を指定して抽出する
２．SORT関数と組み合わせて、抽出と並べ替えを同時に行う
３．UNIQUE関数と組み合わせて、重複しないリストを抽出
事例１．３つ以上の複数条件を指定
FILTER関数を３つ以上の複数条件で利用して、データを抽出する方法を紹介します。
３つのOR条件（AもしくはBもしくはC）で利用するには、以下のように書式を作成します。
=FILTER(配列,(条件式1)+(条件式2)+(条件式3),[空の場合])
たとえば、下のFILTER関数を挿入することで、「算数が60点以上」もしくは「国語が60点以上」もしくは「社会が60点以上」のデータを抽出することができます。
=FILTER(B3:E10,(C3:C10>=60)+(D3:D10>=60)+(E3:E10>=60),"")
AND条件で３つの複数条件でデータを抽出する場合は、以下の書式を使用します。
=FILTER(配列,(条件式1)*(条件式2)*(条件式3),[空の場合])
また、OR条件とAND条件を組み合わせて利用することもできます。
下のFILTER関数に「*」と「*」と両方の記号をもちいることで、「男性」でさらに、「国語が60点以上」もしくは「社会が60点以上」を抽出することができます。
=FILTER(B3:E10,(C3:C10="男")*(D3:D10>=60)+(E3:E10>=60),"")
事例２．FILTER関数とSORT関数を組み合わせる
FILTER関数とSORT関数を組み合わせると、条件に合うデータの抽出と並べ替えを同時に行うことができます。
SORT関数は、データを並べ替えすることができる関数で、書式と引数は以下のとおりです。
＝SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
第１引数「配列」：並べ替えするデータのセル範囲
第２引数「並べ替えインデックス」：何列目(何行目)を並べ替えするか数値で指定
第３引数「並べ替え順序」：昇順で並べ替えする場合は「1」は、降順の場合は 「-1」を指定
第４引数「並べ替え基準」：縦に並べ替えする場合は「0」、横であれば「1」
SORT関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
SORT関数とFILTER関数を組み合わせた使い方を１つ紹介します。
以下のFILTER関数し、B列からE列のデータに対して、性別が「男」に合致するデータを抽出します。
=FILTER(B3:D11,C3:C11="男","")
このFILTER関数で表示されたデータの『体重』列は、「65⇒80⇒45⇒38⇒75」と数値の大きさがバラバラです。
そのため、FILTER関数をSORT関数の第１引数に挿入することで、「男」のデータを抽出して、さらに「体重」を昇順で「38⇒45⇒65⇒75⇒80」に並べ替えすることができます。
=SORT(FILTER(B3:D11,C3:C11="男",""),3,1,FALSE)
事例３．FILTER関数とUNIQUE関数を組み合わせる
FILTER関数とUNIQUE関数を組み合わせると、抽出したデータの中から重複したものは除外して表示させることができます。
UNIQUE 関数とは、範囲内のユニークな値の一覧を返す関数です。
=UNIQUE(配列,[列の比較],[回数指定])
第１引数「配列」：抽出する元データの範囲
第２引数「列の比較」：省略可能。行を返すか列を返すかを「TRUE」か「FALSE」で指定
第３引数「回数指定」：省略可能。1回だけ出現する値を返す場合は「TRUE」、重複しないすべての値を返す場合は「FALSE」を指定
例えば、下の表のB列の氏名は複数セル重複しています。このB列に対してUNIQUE関数を使うことで、F列にユニークな氏名だけのリストを表示させることができます。
=UNIQUE(B3:B14)
このUNIQUE関数の中にFILTER関数を組み合わせることで、表から「80点以上を取ったことがある人の氏名」を重複せずに抽出することができます。
=UNIQUE(FILTER(B3:B14,D3:D14>=80,""))
フィルターの抽出条件を登録するのもアリ！
今回は、FILTER関数でデータを抽出する方法を紹介しました。
高度な条件でデータ抽出する際は、大変便利な関数です。
ただ、従来のフィルターにある「ユーザー設定のビュー」という機能を利用することで、フィルターの抽出条件を登録することができます。
登録した抽出条件はいつでも使用することができ、データ抽出を瞬時に行うことができます。
以下のリンク記事で、フィルターの「ユーザー設定のビュー」の使い方を詳しく紹介しているので参考にしてください。
