エクセル FILTER 関数:セルの範囲を条件に一致する値に絞り込む

はじめに

エクセルの FILTER 関数の使い方を紹介します。

FILTER 関数は指定したセルの範囲や配列から条件に一致する値に絞り込みます。

特定の行や列だけ抽出・フィルタリングしたいときに使用します。

一意の値を取得するには「UNIQUE 関数」をご覧ください。
フィルターを使用するには「フィルタでデータを抽出する」をご覧ください。
この関数は 365 または 2021 から使用できます。
  • 目次
    • FILTER 関数
    • 使用例
    • 解説

FILTER 関数

FILTER(範囲, 含める)
範囲の中から含める行に対応した範囲を抽出します。
範囲の中から含める列に対応した範囲を抽出します。

FILTER(範囲, 含める, 空の場合)
結果が空の場合に返す値を指定できます。

引数「範囲」セルの範囲や配列を指定します。
引数「含める」引数「範囲」と同じ行数の配列を指定すると、配列の値が TRUE の行を配列で取得します。
引数「範囲」と同じ列数の配列を指定すると、配列の値が TRUE の列を配列で取得します。
引数「空の場合」省略できます。
結果が空のときに返す値を指定します。数値、文字列、セル参照、関数などを指定できます。

使用例

結果は配列になり複数のセルに表示されます。またセルの範囲に対して比較しています。これはスピルという機能によるものです。詳しくは「スピル、動的配列数式の使い方」をご覧ください。

行を抽出する。

=FILTER(B3:D7,E3:E7)
1

列を抽出する。

=FILTER(B3:D7,B10:D10)
2

名前が「エクセル」の行を抽出する。

=FILTER(B3:D7,B3:B7="エクセル")
3

個数が「30」以上の行を抽出する。

=FILTER(B3:D7,D3:D7>=30)
4

何も抽出されないときに「空」を表示する

=FILTER(B3:D7,D3:D7>=100,"空")
5

何も表示しないなら、引数「空の場合」に "" を指定します。

AND 条件

名前が「エクセル」でバージョンが「2022」の行を抽出する。

=FILTER(B3:D7,(B3:B7="エクセル")*(C3:C7=2022))
6

入力時の注意として条件を () で囲む必要があります。 (条件1)*(条件2)

OR 条件

名前が「エクセル」以外で個数が「40」以上の行を抽出する。

=FILTER(B3:D7,(B3:B7<>"エクセル")+(D3:D7>=40))
7

入力時の注意として条件を () で囲む必要があります。 (条件1)+(条件2)

部分一致

指定した文字列が含まれているかを条件にするには「SEARCH 関数」を使用します。検索値が見つからないときはエラーになるので、それを FALSE にするために「IFERROR 関数」を使用します。

名前に「エク」が含まれる行を抽出する。

=FILTER(B3:D7,IFERROR(SEARCH("エク",B3:B7),FALSE))
8

名前が「エク」から始まる行を抽出する。

=FILTER(B3:D7,IFERROR(SEARCH("エク",B3:B7)=1,FALSE))

SEARCH 関数」は大文字と小文字を区別しません。区別するには「FIND 関数」を使用します。

解説

引数「含める」が 1 行のとき、その値が TRUE の列を引数「範囲」から取得します。

引数「含める」が 1 列のとき、その値が TRUE の行を引数「範囲」から取得します。

引数「範囲」と引数「含める」の行数または列数が一致していないときはエラー #VALUE! になります。

引数「含める」に複数の行列を指定するとエラー #VALUE! になります。

引数「含める」に TRUE や FLASE に変換できない文字列などが含まれているときはエラー #VALUE! になります。

引数「含める」の中にエラーが含まれているときは結果もそのエラーになります。

引数「含める」の値がすべて FALSE でなにも取得されないときはエラー #CALC! になります。引数「空の場合」が指定されているときはその値を返します。

引数「含める」の指定方法

引数「含める」には {TRUE;FALSE;TRUE} のように TRUE と FALSE の配列を渡します。その中の TRUE に対応する行や列を引数「範囲」から抽出します。

なぜ B3:B5=365 のようにセルの範囲と比較演算子を使用して条件を指定できるのかは、スピルという機能によるものです。スピルが自動で配列を作成しています。AND や OR 条件、部分一致などができるのも、すべてスピルによるものです。

詳しくは「スピル、動的配列数式の使い方」をご覧ください。

簡単にまとめると次のようなことをしています。

  • セルの範囲を配列に変換する。 B3:B5{B3;B4;B5}
  • 配列と比較すると、各要素との比較になる。 {B3;B4;B5}=365{B3=365;B4=365;B5=365}
  • その結果が TRUE と FALSE の配列になる。 {TRUE;FALSE;TRUE}

このように最終的に TRUE と FALSE の配列になり、引数「含める」の条件になります。

使用できる比較演算子には次のものがあります。

演算子 演算名 使用例 結果
=等しい1=2False
<>等しくない1<>2True
>大きい1>2False
<小さい1<2True
>=以上1>=2False
<=以下1<=2True

大文字と小文字は区別しません。 "ABC""abc" は同じものとして一致します。