FILTER関数の使い方｜条件に合う行をまとめて抜き出す

FILTERは、条件に合う行をまとめて抜き出す関数です。

VLOOKUP が「1件を取り出す」のに対し、FILTERは該当する行を全部返します。1つの数式で表が丸ごと出てくるのが特徴です。

書式

=FILTER(範囲, 条件1, [条件2], ...)

引数	内容
範囲	抜き出す元の表
条件	`A2:A100="東京"` のような判定式。範囲と同じ行数にする

条件は比較式そのものを書きます。SUMIF のように "東京" と文字列で書くのではない点に注意してください。

基本の使い方

	A	B	C
1	支店	商品	売上
2	東京	A	1200
3	大阪	A	800
4	東京	B	1500

東京の行だけを抜き出します。

=FILTER(A2:C4, A2:A4="東京")

結果： 2行分（東京/A/1200 と東京/B/1500）が、数式を入れたセルから下・右に展開されます。

1つの数式で複数のセルに結果が出るのがFILTERの特徴です。展開先にデータが入っていると、後述の #REF! になります。

複数条件

AND（すべて満たす）

条件をカンマで並べます。

=FILTER(A2:C100, A2:A100="東京", C2:C100>=1000)

OR（どれかを満たす）

カンマでは書けません。 + で足します。

=FILTER(A2:C100, (A2:A100="東京") + (A2:A100="大阪"))

TRUE は1、FALSE は0として扱われるため、足して1以上なら成立、という理屈です。各条件を括弧で囲むのを忘れないでください。

同様に、* を使うとANDになります。

=FILTER(A2:C100, (A2:A100="東京") * (C2:C100>=1000))

カンマで並べるのと同じ意味です。ANDとORを混ぜるときは、この書き方に統一するほうが分かりやすくなります。

=FILTER(A2:C100, ((A2:A100="東京") + (A2:A100="大阪")) * (C2:C100>=1000))

「東京または大阪、かつ1000以上」です。

部分一致で絞る

FILTERの条件は完全一致の比較なので、部分一致には別の関数を挟みます。

=FILTER(A2:C100, ISNUMBER(SEARCH("営業", B2:B100)))

SEARCH は見つかった位置を返し、見つからなければエラーになります。それを ISNUMBER で TRUE/FALSE に変換しています。

正規表現を使うなら REGEXMATCH です。

=FILTER(A2:C100, REGEXMATCH(B2:B100, "営業|販売"))

該当がないときの表示

条件に合う行が1つもないと #N/A になります。最後の引数で表示を指定できます。

=FILTER(A2:C100, A2:A100=E1, "該当なし")

IFERROR で包む方法もありますが、FILTERの最終引数のほうが安全です。IFERRORは範囲指定のミスまで隠してしまいます。

並べ替えと組み合わせる

抽出した結果を並べ替えるには SORT で包みます。

=SORT(FILTER(A2:C100, A2:A100="東京"), 3, FALSE)

3列目（売上）で降順に並びます。「東京の売上ランキング」が1つの数式で作れます。

必要な列だけ取り出すなら、範囲の指定を絞ります。

=FILTER({B2:B100, C2:C100}, A2:A100="東京")

{} で列を結合できます。元の表の列順と違う順番で取り出せるのが利点です。

別ファイルのデータを絞る

IMPORTRANGE と組み合わせられます。

=FILTER(IMPORTRANGE("キー", "売上!A2:C1000"),
        IMPORTRANGE("キー", "売上!A2:A1000") = "東京")

同じIMPORTRANGEを2回書く必要があり冗長です。読み込み専用のシートを1枚作って、そこを参照するほうが軽く、読みやすくなります。

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 これがFILTERで最も多いエラーです。

FILTERは下・右に結果を広げるため、その領域が空いている必要があります。数式の下や右に何か入力されていないか確認してください。

#VALUE! が出る

条件の行数が範囲と合っていません。 範囲が A2:C100 なら条件も A2:A100 にしてください。A:A（列全体）と A2:C100 を混ぜるとずれます。

#N/A が出る

条件に合う行がありません。最終引数で表示を指定してください。

空行が大量に出る

範囲を A2:C1000 のように大きく取ると、データがない行も条件次第で拾われます。空でない行だけに限定してください。

=FILTER(A2:C1000, A2:A1000="東京", A2:A1000<>"")

QUERYとの使い分け

やりたいこと	使う関数
条件で行を絞るだけ	FILTER
絞る＋集計する（合計・件数）	QUERY
絞る＋グループ化する	QUERY
列の順番を入れ替える	どちらでも

単純な絞り込みならFILTERのほうが読みやすいです。集計やグループ化が入ったらQUERYに切り替えてください。

Excelとの違い

Excelにも FILTER があります（Microsoft 365 / Excel 2021以降）。書式もほぼ同じです。

ただし違いがあります。

	Excel	スプレッドシート
複数条件	`*` と `+` のみ	カンマ区切りも可
該当なしの指定	第3引数	最後の引数
古いバージョン	使えない	常に使える

Excel 2019以前では使えないので、その場合は INDEX や作業列を使った方法に置き換える必要があります。

一件だけ取り出す関数との決定的な違い

検索系の関数を選ぶとき、最初に決めるべきなのは「取り出したいのは一件か、全件か」です。ここを取り違えると、そもそも関数の選択を間違えます。

VLOOKUPやXLOOKUPは、条件に合う最初の一件だけを返します。同じ商品コードが表の中に十行あっても、返るのは一番上の一行だけです。残りの九行は無視されます。これは仕様であって、不具合ではありません。

FILTERは、条件に合う行をすべて返します。十行該当すれば十行が返り、下方向にあふれて表示されます。担当者ごとの明細を一覧にする、特定の期間の取引をすべて抜き出す、といった用途はこちらです。

つまり、マスタから対応する値を一つ引いてくるのがVLOOKUP系、条件に合うデータを一覧として抜き出すのがFILTER、という住み分けになります。「検索」という言葉でひとくくりにせず、目的の形で選んでください。

もうひとつの違いは、結果が動的であることです。FILTERの結果は元データと連動しているので、元データが増えれば結果も自動的に増えます。手作業でフィルタをかけて別シートに貼り付ける、という作業が不要になります。

あふれるという挙動を理解する

FILTERは結果が複数行になるため、数式を入れた一つのセルから下方向、場合によっては右方向にも結果が広がります。この広がりを妨げるものがあると、数式全体がエラーになります。

もっとも多いのは、結果が広がる先に何かが入力されている場合です。一行でも文字が入っていれば、そこで衝突してエラーになります。数式を入れる前に、下方向を十分に空けておいてください。

見落としやすいのは、目に見えない値が入っている場合です。過去に何か入力して削除したつもりでも、空白文字が残っていることがあります。範囲を選択して完全に消去してから、数式を入れ直してください。

もうひとつは、結合されたセルがある場合です。あふれる範囲に結合セルがあると展開できません。結合を解除する必要があります。

また、表の形式に変換されている範囲の中では、あふれる数式が使えないことがあります。通常の範囲に戻すか、別の場所に数式を置いてください。

エラーの表示自体は原因を教えてくれないので、まず下方向と右方向に何もないかを確認する、という手順を習慣にしておくと早く解決します。

条件の書き方を組み立てる

FILTERの条件は、対象の範囲と同じ行数の真偽値の並びとして渡します。この考え方が分かると、条件を自由に組み立てられるようになります。

たとえば、担当者の列全体を特定の名前と比較すると、行ごとに真か偽かが並びます。それをそのまま条件として渡せば、真の行だけが返ります。

条件を二つ以上にしたい場合、「かつ」で結ぶなら掛け算、「または」で結ぶなら足し算を使います。真偽値は内部的に一と零として扱われるため、掛ければ両方が真のときだけ一になり、足せばどちらかが真なら一以上になります。この仕組みを理解しておくと、複雑な条件も書けるようになります。

条件に使う範囲は、すべて同じ行数にしてください。対象の範囲が二行目から百行目までなら、条件に使う列も同じ範囲にします。片方だけずれていると、対応する行が食い違います。

期間で絞る場合は、開始日以上という条件と終了日以下という条件を掛け合わせます。日付は別のセルに置いて参照するのが確実です。数式に直接書くと、環境によって解釈が変わることがあります。

部分一致で絞りたい場合は、条件の中で検索の関数を使います。特定の文字を含む行だけを抜き出す、という処理ができます。

並べ替えや重複除去と組み合わせる

FILTERの結果は、そのまま他の関数に渡せます。この組み合わせが実務では効いてきます。

抜き出した結果を金額の大きい順に並べたいなら、並べ替えの関数で包みます。抜き出しと並べ替えを一本の数式で完結できます。

抜き出した結果から重複を除きたいなら、重複除去の関数で包みます。担当者の一覧を条件付きで作る、といった処理ができます。

上位五件だけを取り出したい場合は、並べ替えた結果の先頭部分を切り出す関数と組み合わせます。ランキング表が数式だけで作れます。

このように、抜き出し、並べ替え、重複除去、切り出しという処理を関数の組み合わせで表現できるのが、新しい世代の関数の特徴です。作業列やコピー貼り付けを挟まずに済むため、元データが更新されればすべてが自動で追従します。

ただし、組み合わせが深くなるほど数式は読みにくくなります。他人が触るファイルでは、途中の結果を別の列に出しておくほうが親切な場合もあります。

よくある質問

Q. 該当がないとエラーになります

該当がない場合に返す値を、三つ目の引数として指定できます。空文字や「該当なし」といった文字を渡しておけば、エラーになりません。

Q. 結果が展開されずエラーになります

あふれる先に何か入っています。下方向と右方向を確認し、完全に空にしてください。

Q. 特定の列だけ取り出せますか

対象の範囲を必要な列だけにすれば、その列だけが返ります。離れた列を組み合わせたい場合は、選択の関数を使う方法もあります。

Q. 結果を並べ替えたいのですが

並べ替えの関数で包んでください。抜き出しと並べ替えを一度に書けます。

Q. 件数だけ知りたいのですが

条件に合う件数を数える関数を使うほうが簡単です。FILTERの結果を数える必要はありません。

Q. 元データを消したら結果も消えました

FILTERの結果は元データと連動しています。結果を固定したい場合は、値として貼り付けてください。

Q. 古い環境でも使えますか

比較的新しい関数のため、古い環境では使えません。共有する相手の環境を確認してください。

Q. 重くなることはありますか

対象範囲を列全体で指定すると負荷がかかります。実際にデータのある範囲に絞ってください。

まとめ

書式は =FILTER(範囲, 条件1, 条件2, ...)
条件は比較式そのものを書く（A2:A100="東京"）
ANDはカンマか *、ORは +
部分一致は REGEXMATCH か ISNUMBER(SEARCH(...))
#REF! は展開先にデータが入っているという意味
集計が入るなら QUERY に切り替える