ExcelのFILTER関数をマスターしよう!動的データ抽出の基本ガイド

Microsoft ExcelのFILTER関数は、習得しておくべき最も重要な関数のひとつです。この関数を使いこなせないと、必要なデータを探し出すのに苦労することになります。本記事では、ExcelでFILTER関数を使うための基本を短期間で身につけられるよう、わかりやすく解説します。

なお、FILTER関数はExcelでデータを絞り込む唯一の方法ではありません。「オートフィルター」や「フィルター(詳細設定)」といったツールでも同様の操作が可能ですが、それぞれに重要な注意点があります。これらについても本ガイドで触れていきます。

FILTER関数とは?

関数(数式)はExcelの根幹をなす存在であり、大量のデータセットから平均値を求めたり、正規分布曲線(ベルカーブ)のグラフを作成したりと、さまざまな処理を可能にします。各関数には固有の構文があり、Excelに関数名を入力するだけで、その内容を確認できるのが一般的です。

その名の通り、FILTER関数は指定した範囲の値を、特定の条件に従って「フィルタリング(抽出)」するために使われます。範囲も条件も関数の中で指定するため、非常に柔軟なカスタマイズが可能です。

適切な引数を設定すれば、スプレッドシート全体を目視で確認して一致するデータを探す手間なく、必要な情報だけを正確に取り出せます。さらに、結果がセルに出力されるため、他の関数と組み合わせて計算したり、グラフで結果を可視化したりすることもできます。

FILTER関数が「詳細設定フィルター」より優れている理由

多くのExcel初心者は、関数の構文を学ぶよりも、標準搭載のデータフィルタリングツールを使う傾向があります。オートフィルターは最も手軽で、メニュー形式のインターフェースから列の除外や条件設定が行えます。また、「フィルター(詳細設定)」を使えば、複数条件を組み合わせた複雑な絞り込みも実現できます。

では、なぜわざわざFILTER関数を使う必要があるのでしょうか?

Excel関数を手作業(他のExcelツールや別のソフトウェアでの操作を含む)と比べた場合の最大の利点は、「動的」であることです。オートフィルターや詳細設定フィルターの結果は一度きりのもので、元データを変更しても更新されません。一方、FILTER関数はデータが変化すると、結果も自動的に更新されます。

FILTER関数の構文

FILTER関数の構文はとてもシンプルです。

=FILTER(array, include, [if_empty])

「array」はスプレッドシート内の矩形範囲を指し、左上のセルと右下のセルを指定します。たとえばA3:E10なら、A列~E列・3行目~10行目で構成される配列です。

次の「include」は抽出条件で、正確にはブール値(TRUE/FALSE)の配列です。通常は、セル範囲(多くの場合1つの列)の値を評価する式として入力し、TRUEまたはFALSEを返します。たとえばA3:A10="Pass"は、セルの値が指定した文字列と一致する場合にTRUEを返します。

最後の「[if_empty]」には、条件に一致する行が1つもなかった場合に返す値を指定できます。"該当なし"のような単純な文字列を設定できます。

関連記事: サンプルデータが母集団にどれほど適合しているかを確認したい方は、Excelで標準誤差を計算する方法のガイドもぜひご覧ください。

FILTER関数の構文がわかったところで、実際にスプレッドシートで使ってみましょう。

今回のデモで使用するサンプルデータはA2~F11の配列で、10名の生徒の生物の点数と正規分布が記録されています。

D列に記録された試験の点数をもとに、30点未満のエントリだけを抽出する関数を作成してみましょう。構文は以下のようになります。

=FILTER(A2:F11,D2:D11<30,"該当なし")

フィルタリング結果は元の配列の一部として展開表示されるため、関数を入力するセルの右側や下側に十分な空きスペースが必要です。ここでは元の表の下に入力します。

期待どおりの結果が得られました。30点未満のエントリだけが選択され、同じ表形式で表示されています。

条件は1つに限られません。AND演算子(*)を使えば、複数の式を1つの引数として連結でき、より複雑なフィルターを作成できます。

今回は、30点台から70点台の間に該当するエントリを抽出する関数を作ってみましょう。構文と結果は以下のとおりです。

=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),"該当なし")

排他的でない条件(OR条件)を指定したい場合は、OR演算子(+)を使用します。含まれる条件のうち1つでもTRUEになれば、その行はフィルターに一致します。

次の数式では、30点未満または70点超という条件で外れ値を抽出しています。

=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),"該当なし")

最後に、FILTER関数が何も見つけられなかったときに単一の値や文字列を返す代わりに、列ごとの値を指定することで、出力が常に一貫したフォーマットを保つようにできます。

まず、デフォルトの挙動を確認するために、確実に一致しない条件を試してみましょう。

=FILTER(A2:F11,D2:D11>90,"該当なし")

ご覧のとおり、結果は文字列1つだけで、想定していた表形式と異なります。これは通常問題になりませんが、結果(またはその一部の値)を別の数式に渡したい場合には支障となることがあります。
そこで、配列の1行分と同じ形式でデフォルト値を指定してみましょう。波括弧{}で囲んだカンマ区切りの値を指定します。次のようにです。

=FILTER(A2:F11,D2:D11>90,{"該当なし","該当なし","該当なし",0})

これにより、スプレッドシート全体のフォーマットと整合性の取れた、見やすい結果が得られます。

FILTER関数を使う価値はある?

MS Excelを記録管理にしか使わず、凝った計算をする予定がない方でも、FILTER関数は覚えておくべき数少ない関数のひとつです。

ワークブックがある程度の規模に達すると、手作業でデータを探すのは大きな負担になります。オートフィルターや詳細設定フィルターも便利ですが、結果が自動更新され、他の関数とも自由に組み合わせられるFILTER関数の方が、長期的にははるかに便利です。