FILTER関数の使い方: 複数条件(AND・OR)で抽出する

=FILTER(A2:C7,B2:B7="North")は、A2:C7のうちB列の地域がNorthの行をすべて返します。1つのセルに入力すると、一致した行が下と右のセルにスピルします。B列の地域をNorthに変えたり、NorthをSouthに変えたりすると、リストが更新されます。

数式リセット

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

数式が入っているのはE2だけです。E:G列のほかの埋まったセルはそのスピルした結果で、F3をクリックするとE2の数式に属しているのがわかります。その範囲に何かが入力されていると、FILTERは行の代わりに#SPILL!を表示します(#SPILL!エラーを参照)。

FILTER関数の構文

=FILTER(array, include, [if_empty])

• array(配列)は返してほしいものです。1つの列、複数の列、表全体のどれでも構いません。
• include(含む)は、B2:B7="North"のようにarrayの各行に1つずつTRUEかFALSEを持つ条件です。行数はarrayとちょうど同じでなければなりません(列を抽出するときは、列ごとに1つの値を指定します)。
• if_empty(空の場合)は、一致する行がないときに表示するものです。指定しないと、空の結果は#CALC!エラーになります。

FILTERにはExcel 2021、Excel 2024、Microsoft 365のいずれかが必要です。Excel 2019以前では#NAME?になり、そこではデータタブのフィルターボタンで抽出します。GoogleスプレッドシートにもFILTERがあり、そこでは条件をそれぞれ別の引数として渡すこともできます。

文字列の比較は大文字と小文字を区別しないので、B2:B7="north"もNorthに一致します。FILTERは行を元の順番のまま残します。結果の並べ替えは別の手順で、この下で説明しています。

セルの値で抽出する

数式に"North"と直接書くと、変えるたびに数式を編集することになります。値をセルに入れて、そのセルと比べます。F1で別の地域を選ぶと、結果も変わります：

数式リセット

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Westの行はないので、Westを選ぶとif_emptyの文字列No salesが表示されます。

数値も同じです。F1に100を入れてC2:C7>=F1とすると売上が100以上の行がすべて残り、C2:C7>F1なら100より大きい行になります。

複数条件のFILTER(AND)

2つの条件が両方TRUEのときだけ行を残すには、条件を掛けます。次の数式は売上が100を超えるNorthの行を返します：

数式リセット

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Ann(120)とCara(200)が残ります。FinnはNorthですが、60は100を超えないので除かれます。

掛ける理由はこうです。それぞれの条件はTRUEとFALSEの列で、計算ではTRUEは1、FALSEは0として扱われます。すべての掛ける数が1のときだけ行が1になるので、*がANDとして働きます。条件はそれぞれかっこで囲み、いくつでもつなげられます：(B2:B7="North")*(C2:C7>100)*(C2:C7<500)。

ここではAND()は使えません。AND(B2:B7="North",C2:C7>100)は行ごとに1つではなく、範囲全体を1つのTRUEかFALSEにまとめてしまうので、FILTERが受け取る形が違ってしまいます。

FILTERでORを使う

少なくとも1つの条件がTRUEのときに行を残すには、条件を足します。次の数式はNorthとEastの行を返します：

数式リセット

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

両方の条件を満たす行は合計が2になりますが、FILTERは結果が0でない行をすべて残すので、足し算がORとして働きます。2つを組み合わせることもできます。((B2:B7="North")+(B2:B7="East"))*(C2:C7>100)は「NorthかEast」かつ「100超」という意味で、ここではAnn、Cara、Danが返ります。

一致するものがないとFILTERは#CALC!を返す

条件を満たす行がないと、FILTERには返すものがありません。3つ目の引数がなければ#CALC!エラーに、あれば自分で決めた文字列になります：

数式リセット

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! 計算結果がありません。たとえば何も見つからない FILTER です。

E2は#CALC!を、F2はNo matchを表示します。B3をSouthからWestに変えると、どちらの数式もBenを返します。何も表示しないなら空の文字列を使います：=FILTER(A2:A7,B2:B7="West","")。

このシートは1つの列だけの抽出も示しています。arrayがA2:A7なので、名前だけが返ります。表の一部の列だけを返すには、結果をCHOOSECOLSで囲みます。=CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3)は地域を除いて名前と売上を返します。CHOOSECOLSにはMicrosoft 365かExcel 2024が必要です。

FILTERの結果を並べ替える

FILTERは表に出てくる順に行を返します。結果を並べ替えるにはSORTで囲みます。ここではNorthの行を売上の大きい順に並べています。3は並べ替えの基準にする結果の列、-1は降順という意味です。

数式リセット

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Cara(200)が最初で、Ann(120)、Finn(60)と続きます。上位の行だけを返すには、さらにTAKEで囲みます。=TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2)は最初の2行を残します(TAKEにはMicrosoft 365かExcel 2024が必要です)。ほかの並べ替えのオプションはSORTとSORTBYで説明しています。

文字列を含む行をFILTERで抽出する

FILTERにはワイルドカードがないので、B2:B7="*th*"は文字列*th*そのものを探します。名前にある文字列を含む行を残すには、各セルをSEARCHで調べます。SEARCHは文字列が見つかれば位置を、見つからなければエラーを返すので、それをISNUMBERで囲みます：

数式リセット

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

これはAnnとDanを返します。SEARCHは大文字と小文字を区別しないので、"an"はAnnのAnにも一致します。大文字と小文字を区別するなら、SEARCHの代わりにFINDを使います。

練習: 2つの条件でFILTERする

数式リセット

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: E2で、売上が85を超えるSouthの担当者の行(3列すべて)を返してください。

答え合わせ答えを見る

練習: セルの値でFILTERし、該当なしにも備える

数式リセット

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: F3で、F1に入力した地域の担当者の名前(A列だけ)を並べてください。いなければNoneと表示します。

答え合わせ答えを見る

FILTERのよくある間違い

• 高さの違う範囲。 =FILTER(A2:C7,B2:B6="North")は6行の表に対して5行しか調べないので、Excelは#VALUE!を返します。includeはarrayと同じ行で始まり、同じ行で終わるようにします。
• 元が空なのに0になる。 FILTERはarrayの空のセルに0を返します。抽出する前に空白を空の文字列に置き換えます：=FILTER(IF(A2:C7="","",A2:C7),B2:B7="North")。
• 列全体の指定。 =FILTER(A:C,B:B="North")は動きますが、数式自体がA列からC列にあると自分自身を参照してしまいます。結果は表の横に置くか、A2:C1000のような固定の範囲を使います。
• 数値を引用符で囲む。 C2:C7>"100"は数値と文字列を比べるので、何も残りません。C2:C7>100と書きます。
• フィルターボタンと同じだと思う。 FILTERは一致する行を新しい場所にコピーし、表には手を付けません。表そのものの行を隠すには、データ > フィルター を使います。