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に切り替えてください。
Excelとの違い
Excelにも FILTER があります（Microsoft 365 / Excel 2021以降）。書式もほぼ同じです。
ただし違いがあります。
| Excel | スプレッドシート | |
|---|---|---|
| 複数条件 | * と + のみ |
カンマ区切りも可 |
| 該当なしの指定 | 第3引数 | 最後の引数 |
| 古いバージョン | 使えない | 常に使える |
Excel 2019以前では使えないので、その場合は INDEX や作業列を使った方法に置き換える必要があります。
まとめ
- – 書式は
=FILTER(範囲, 条件1, 条件2, ...) - – 条件は比較式そのものを書く（
A2:A100="東京"） - – ANDはカンマか
*、ORは+ - – 部分一致は
REGEXMATCHかISNUMBER(SEARCH(...)) - –
#REF!は展開先にデータが入っているという意味 - – 集計が入るなら QUERY に切り替える
関連する関数
- – QUERY — 絞り込み＋集計をまとめて書く
- – SORT — 抽出結果を並べ替える
- – UNIQUE — 重複を除いた一覧
- – IMPORTRANGE — 別ファイルのデータを絞る
- – 関数一覧に戻る
コメントを残す