FILTERでデータを絞り込む

The FILTER function returns only the rows of a range that meet a condition you set. Instead of manually hiding rows or copying matches, FILTER spills the matching rows automatically.

It is dynamic: when your data changes, the filtered output updates instantly. This makes it perfect for live reports that always show the current matches.

FILTERには最大3つの引数を指定できます。

=FILTER(array, include, [if_empty])

• arrayは、返したい範囲です。
• includeは、各行に対してTRUEまたはFALSEを返す論理テストです。
• if_emptyは、一致するものがない場合に表示する任意の値です。

includeのテストは、各行にTRUEまたはFALSEを割り当てられるよう、arrayと同じ高さでなければなりません。

A2:A10に営業担当者名、B2:B10に担当地域が入っているとします。East地域の名前だけを一覧表示するには、次のようにします。

=FILTER(A2:A10, B2:B10="East")

B2:B10="East"というテストは、TRUEとFALSEの列を返します。FILTERは、結果がTRUEになった行を残し、その結果をスピル表示します。

arrayには複数の列を指定できます。East地域の名前と売上額の両方を返すには、範囲全体をarrayに指定します。

=FILTER(A2:C10, B2:B10="East")

FILTERは、一致した行のすべての列を返し、小さな表としてスピル表示します。includeのテストは、引き続き1つの条件列だけを参照します。

条件はテキストに限りません。C2:C10の売上が500を超えるすべての行を返すには、次のようにします。

=FILTER(A2:C10, C2:C10>500)

大なり、小なり、等しくないなどの比較演算子は、通常の論理テストと同じように、include引数の中で使用できます。

2つの条件を同時に満たす必要がある場合は、テストを掛け合わせます。TRUEは1、FALSEは0として扱われるため、乗算はANDと同じ働きをします。両方が1の場合だけ、行が条件を満たします。

=FILTER(A2:C10, (B2:B10="East")*(C2:C10>500))

これは、East地域にあり、かつ売上が500を超える行を返します。それぞれのテストは必ず括弧で囲んでください。

どちらかの条件がTRUEであれば通過させるには、テストを足し合わせます。いずれかのテストがTRUEなら合計は少なくとも1になるため、加算はORと同じ働きをします。

=FILTER(A2:C10, (B2:B10="East")+(B2:B10="West"))

これは、EastまたはWest地域の行を返します。値が1または2になる行は残り、0になる行は除外されます。

条件を満たす行がない場合、FILTERは既定で#CALC!エラーを返します。3番目の引数を指定すると、代わりにわかりやすいメッセージを表示できます。

=FILTER(A2:C10, B2:B10="South", "No matches found")

South地域が存在しない場合、セルにはエラーではなく指定したテキストが表示されます。実際のレポートでは、必ずif_emptyの値を追加してください。

対話型レポートでは、固定値ではなくセルと比較します。E1にユーザーが選択する地域が入っている場合は、次のようにします。

=FILTER(A2:C10, B2:B10=E1, "No matches")

E1をWestに変更すると、スピルされた一覧がWestの行にすぐ更新されます。これは、ドロップダウンで操作するダッシュボードの基礎になります。

FILTERは、元の順序で一致するデータを返します。並べ替えるには、FILTERをSORTで囲みます。East地域の行を売上の降順で表示するには、次のようにします。

=SORT(FILTER(A2:C10, B2:B10="East"), 3, -1)

SORTはスピルされた表を3列目で並べ替えます。-1は降順を意味します。このようにスピル関数を組み合わせる方法は、一般的で非常に強力です。

FILTERはExcel 365とGoogle Sheetsの両方で使用でき、構文もほぼ同じです。Google Sheetsでは、条件を掛け合わせる代わりに、複数の条件を個別の引数として指定できます。

=FILTER(A2:C10, B2:B10="East", C2:C10>500)

Google Sheetsでは、追加した各引数がAND条件として扱われます。Excelでは、1つのinclude引数の中で、ANDには乗算、ORには加算を使います。

FILTERの理解度を確認しましょう。

FILTERを使って、一致する行だけを返す方法を学びました。

• 構文は=FILTER(array, include, [if_empty])です。
• includeのテストは、arrayと同じ高さでなければなりません。
• ANDにはテストを掛け合わせ、ORには足し合わせます。
• if_emptyのメッセージを追加して、#CALC!エラーを防ぎます。
• FILTERをSORTで囲むと、結果を並べ替えられます。

次は、UNIQUEを使って重複しない値の一覧を抽出します。