Excel の FILTER 関数をマスターする:動的データ フィルタリングのクイック ガイド

Microsoft Excel の FILTER 関数は、習得することが最も重要な関数の 1 つです。これがないと、必要なデータを見つけるのが困難になります。これは、Excel で FILTER を使用するための短期集中コースです。

この関数が MS Excel でデータをフィルタリングする唯一の方法ではないことにも注意してください。同じことを実現するための自動フィルタや高度なフィルタなどのツールがありますが、いくつかの重要な注意事項については、このガイドで説明します。

FILTER 関数とは何ですか?

関数または Excel 数式は Excel の基本であり、大規模なデータセットの平均を見つけたり、ベル曲線グラフを作成したりすることができます。各関数には構文があり、通常は Excel に関数名を入力するだけで確認できます。

Excel の FILTER 関数は、その名前が示すように、特定の条件に従って指定された範囲の値を「フィルター」するために使用されます。使用する範囲と条件を関数で入力するため、非常にカスタマイズ可能です。

適切なパラメーターを使用すると、スプレッドシート全体を調べて一致するエントリを手動で見つける必要がなく、スプレッドシートから必要な正確な情報を抽出できます。また、出力はセルに含まれるため、さらに多くの関数を連鎖させて計算を実行したり、結果をグラフで視覚化したりできます。

高度なフィルターよりも FILTER 関数が推奨されるのはなぜですか?

Excel 初心者のほとんどは、関数の構文を学ぼうとするのではなく、Excel に組み込まれているデータ フィルター ツールに固執します。自動フィルターが最も簡単で、メニューベースのインターフェイスから列を除外したりフィルター条件を設定したりできます。次に、複雑なフィルタリング スキームを実装するために複数の基準を適用する機能を備えた高度なフィルタがあります。

では、なぜわざわざ FILTER 関数を使用するのでしょうか?

Excel 関数を使用することの主な利点は、(別の Excel ツールまたは他のプログラムを使用して) 手動で操作を行う場合と比べて、関数が動的であることです。自動フィルターまたは詳細フィルターを使用すると、ソース データが変更されても変化しない 1 回限りの結果が得られます。一方、FILTER 関数は、データが変更されると、それに応じて結果を更新します。

FILTER 関数の構文

FILTER 式の構文は非常に単純です。

=FILTER(配列、インクルード、[if_empty])

配列はスプレッドシートの長方形のサブセットであり、左上のセルと右下のセルの間の範囲を指定することで示されます。たとえば、A3:E10 は、列 A ～ E と行 3 ～ 10 で構成される配列です。

次のパラメータは単に使用する基準、またはより技術的にはブール配列です。これは、TRUE または FALSE を返すセル範囲 (通常は列) の値を評価する式の形式で入力されます。たとえば、A3:A10=”Pass” は、セルの値が指定された文字列と一致する場合に TRUE を返します。

最後に、条件に一致する行がない場合に FILTER 関数によって返される値を入力できます。これは、「レコードが見つかりません」のような単純な文字列にすることができます。

関連: サンプル データが大規模なデータセットにどの程度正確に適合するかを確認する必要がありますか? Excel での標準誤差の計算に関するガイドをご覧ください。

FILTER 関数の構文を理解したところで、スプレッドシートで実際に FILTER を使用する方法を見てみましょう。

このデモンストレーションに使用しているサンプル データには、A2 から F11 までの配列があり、10 人の学生の生物学のスコアを正規分布に従って表にしています。

試験のスコア (D 列にリストされている) に基づいてエントリをフィルタリングし、スコアが 30 未満のエントリのみを返す関数を作成しましょう。これは次のような構文になります。

=FILTER(A2:F11,D2:D11<30,”一致するものが見つかりませんでした”)

フィルター処理された結果は配列のサブセットであるため、関数の後ろに十分なスペースを確保したセルで関数を使用してください。元のテーブルの下でこれを実行します。

そして期待通りの結果が得られます。スコアが 30 未満のすべてのエントリが選択され、同じ表形式で表示されます。

単一の条件に限定されることもありません。 AND 演算子 (*) を使用すると、複数の式を 1 つのパラメータとして連結し、より複雑なフィルタを作成できます。

30 から 70 マークの間にあるエントリを返す関数を作成してみましょう。構文と結果は次のとおりです。

=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),“一致するものが見つかりません”)

非排他的な基準の場合は、OR 演算子 (+) を使用することもできます。これは、含まれている条件のうち 1 つだけが TRUE と評価される場合でも、フィルターに一致します。

次の式では、これを使用して、15 未満または 70 を超える結果をフィルタリングして、外れ値を見つけます。

=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),”レコードが見つかりません”)

最後に、FILTER 関数が何も見つからなかった場合に単一の値または文字列を返す代わりに、すべての列に値を指定して、出力が常に一貫した形式に保たれるようにすることができます。

まず、偽であることがわかっている条件を試して、デフォルトでどのようになるかを確認してみましょう。

=FILTER(A2:F11,D2:D11>90,”一致するものが見つかりませんでした”)

ご覧のとおり、結果には 1 つの文字列のみが含まれており、予想される形式と異なります。結果 (またはその結果の一部の値) を別の数式に入力する場合を除き、これは通常問題にはなりません。
そこで、配列のエントリと同じ形式でデフォルト値を与えてみましょう。これを行うには、中括弧で囲んでカンマ区切りの値を指定します。このように:

=FILTER(A2:F11,D2:D11>90,{“記録なし”, “記録なし”, “記録なし”, 0})

これにより、スプレッドシートの他の形式と一貫性のある、より満足のいく結果が得られます。

FILTER 関数には価値がありますか?

MS Excel を記録の管理にのみ使用し、複雑な計算を行うつもりがない場合でも、FILTER 関数は検討すべき数少ない関数の 1 つです。

ワークブックが一定のサイズに達すると、手動でデータを見つけるのが面倒になる可能性があるためです。また、自動フィルタ ツールと高度なフィルタ ツールは便利ですが、結果が自動的に更新され、他の関数と組み合わせることができるため、長期的には関数を使用した方が実際には便利です。