ExcelのFILTER関数を使えば、表の中から条件に合う行だけを別の場所へ自動で取り出せます。シートの「フィルター機能」と違い、元のデータには一切触れません。
とはいえ実務で使い始めると、「複数条件の指定がうまくいかない」「#CALC!や#SPILL!が出る」「自分のExcelでは使えない」といった壁にぶつかりがちです。
この記事では、FILTER関数の基本構文から、AND/OR/NOTの複数条件、エラー別の診断、Excel 2019以前の代替数式までを一気通貫で解説します。すべての数式は下の共通サンプルで結果を確認しているので、手元で再現しながら読み進めてください。
この記事はこんな人におすすめです。
- 「FILTER関数 複数条件」の正しい書き方を知りたい
- #CALC!や#SPILL!エラーの原因を切り分けたい
- 自分のExcelで使えないとき、何で代替すればいいか知りたい
FILTER関数とは？できることと対応バージョン
ExcelのFILTER関数は、指定した条件に一致する行だけを抽出する関数です。読み方は「フィルター関数」。検索や並べ替えと違い、元の表をそのまま残したまま、別のセルに結果を吐き出せるのが最大の特徴です。
この記事全体で使う共通サンプルデータを先に示します。A1からD6に、次の小さな売上表が入っているとします。
| 行 | A（No） | B（部署） | C（氏名） | D（売上） |
|---|---|---|---|---|
| 1 | No | 部署 | 氏名 | 売上 |
| 2 | 1 | 営業 | 田中 | 120 |
| 3 | 2 | 総務 | 鈴木 | 0 |
| 4 | 3 | 営業 | 佐藤 | 85 |
| 5 | 4 | 経理 | 伊藤 | 200 |
| 6 | 5 | 営業 | 山田 | 150 |
1行目が見出しなので、抽出対象のデータ範囲は「A2:D6」です。以降の数式はすべて、この表を前提に結果を示します。
基本構文と3つの引数
FILTER関数の構文は、3つの引数で構成されます。
=FILTER(配列, 含む, [空の場合])各引数の役割は次のとおりです。
- 第1引数 配列（必須）: 抽出の対象となるデータ範囲。今回は
A2:D6 - 第2引数 含む（必須）: 抽出条件。データと同じ行数の論理値（TRUE/FALSE）の並び
- 第3引数 空の場合（省略可）: 条件に合う行が0件のときに返す値
第2引数は「範囲=値」の形で書きます。たとえば B2:B6="営業" は、各行が営業かどうかを判定し、TRUE/FALSEの並びを作ります。FILTERはこのTRUEの行だけを残す仕組みです。
抽出結果は1つのセルに入力するだけで、自動的に複数行・複数列へあふれて表示されます。この挙動を「スピル」（数式の結果が隣接セルへ自動展開される機能）と呼びます。あふれ先のセルは空けておく必要があります。
=FILTER(A2:D6, B2:B6="営業", "該当なし")この数式の結果は、営業の3行（田中120／佐藤85／山田150）が上から順に展開されます。FILTER関数はMicrosoft 365、Excel for the web、Excel 2021、Excel 2024で使えます。Excel 2019以前は動的配列関数に未対応のため、後半で紹介する代替数式が必要です。
オートフィルターとの使い分け早見表
「シートのオートフィルターで十分では？」と思うかもしれません。どちらを使うべきかは、やりたいことで決まります。判断軸ごとに整理しました。
| やりたいこと | オートフィルター | FILTER関数 |
|---|---|---|
| 元データを変えずに抽出したい | △（行を隠すだけ） | ○ |
| 結果を別シート・別の場所に出したい | ×（同じ表上のみ） | ○ |
| 毎回手動で操作したくない | ×（都度クリック） | ○（自動更新） |
| 条件をセルやドロップダウンで切り替えたい | × | ○ |
| 印刷レイアウトを崩したくない | △ | ○ |
ざっくり言えば、「一度きりの目視確認」ならオートフィルター、「結果を別の場所に置いて自動更新したい」ならFILTER関数です。レポートやダッシュボードを組むならFILTER関数が圧倒的に向いています。
単一条件・複数条件と別シートの指定方法
ここがFILTER関数で最もつまずきやすいポイントです。「FILTER関数 複数条件」で検索する人の多くは、AND条件やOR条件の演算子の書き方で詰まっています。順番に見ていきましょう。
単一条件で抽出する
まずは1つの条件で抽出します。サンプル表から「営業」の行だけを取り出してみます。
=FILTER(A2:D6, B2:B6="営業", "該当なし")第2引数の B2:B6="営業" が、各行について営業かどうかを判定します。結果は田中（120）、佐藤（85）、山田（150）の3行です。第3引数の「該当なし」は、もし0件だったときに表示する文字列です。
数値の比較も同じように書けます。「売上が100以上」なら次のとおりです。
=FILTER(A2:D6, D2:D6>=100, "該当なし")この結果は田中（120）、伊藤（200）、山田（150）の3行になります。
AND条件（*）で複数条件を同時指定
複数の条件を「すべて満たす行」だけ抽出したいときは、条件同士を *（アスタリスク）でつなぎます。これがAND条件（論理積。すべての条件が同時に成り立つこと）の書き方です。
「営業」かつ「売上が100以上」の行を抽出してみます。
=FILTER(A2:D6, (B2:B6="営業")*(D2:D6>=100), "該当なし")各条件は必ずカッコで囲んでください。結果は田中（120）と山田（150）の2行です。佐藤（85）は営業ですが売上が100未満なので除外されます。
なぜ掛け算でANDになるのか、仕組みは単純です。TRUEは1、FALSEは0として扱われ、1×1=1（TRUE）、1×0=0（FALSE）となります。両方TRUEのときだけ1が残るので、AND条件になるわけです。
OR条件（+）でいずれか一致を指定
「どちらか一方でも満たせばよい」場合は、条件同士を +（プラス）でつなぎます。これがOR条件（論理和。いずれかの条件が成り立つこと）です。
「総務」または「経理」の行を抽出します。
=FILTER(A2:D6, (B2:B6="総務")+(B2:B6="経理"), "該当なし")結果は鈴木（0）と伊藤（200）の2行です。足し算でORになる理由も同じで、0+1=1、1+0=1、0+0=0 となり、どちらかがTRUEなら1が残ります。
なお、同じ列で3つ以上の値を許可したいなら、条件を + で並べ続ければ対応できます。たとえば (B2:B6="営業")+(B2:B6="総務")+(B2:B6="経理") のように書けます。
NOT条件（）で除外フィルターをかける
「特定の値を除いたすべて」を抽出したいときは、不等号 <>（〜ではない）を使います。除外フィルターです。
「営業以外」の行を抽出してみます。
=FILTER(A2:D6, B2:B6<>"営業", "該当なし")結果は鈴木（0）と伊藤（200）の2行です。AND・ORと組み合わせることもできます。たとえば「営業以外で、かつ売上が100以上」なら次のように書きます。
=FILTER(A2:D6, (B2:B6<>"営業")*(D2:D6>=100), "該当なし")この結果は伊藤（200）の1行だけです。鈴木（0）は営業以外ですが、売上が100未満なので除外されます。「○○を含む文字列を除外したい」といった部分一致の除外は、NOT(ISNUMBER(SEARCH("除外語", 範囲))) を条件に使うと実現できます。
別シート・別ファイルから抽出する
FILTER関数は、別シートのデータを参照して抽出することもできます。範囲の前に「シート名!」を付けるだけです。元データが「Sheet1」のA2:D6にあるとします。
=FILTER(Sheet1!A2:D6, Sheet1!B2:B6="営業", "該当なし")ポイントは、第1引数と第2引数の両方に「Sheet1!」を付けることです。条件範囲だけ付け忘れると、サイズ不一致のエラーになります。抽出したい部署名をG1セルに入れ、Sheet1!B2:B6=G1 とすれば、G1を書き換えるだけで結果が切り替わる動的レポートが作れます。
別ブック（別ファイル）も参照できますが、注意が必要です。参照先のブックを開いていないと #REF! エラーになります。動的配列の結果は、ブックを閉じた状態では正しく取得できません。別ブック参照は管理が煩雑になりやすいので、元データは同じブックの別シートに集約するのがおすすめです。シート名をセルから動的に組み立てたい場合はINDIRECT関数も使えます。ただし別ブック参照に弱く、再計算が重い点には注意してください。
エラーの原因と対処法
FILTER関数で出るエラーは、原因を切り分ければ怖くありません。代表的な3種類を、症状から逆引きできるように整理します。エラー値全般の意味はExcelエラー値一覧も参考にしてください。
#CALC!エラー｜第3引数で空時の表示を指定する
#CALC! は、条件に合う行が1つもないのに、第3引数（空の場合）を省略したときに発生します。FILTERは「0件の結果」をそのまま返せないため、エラーになる仕組みです。
たとえばサンプル表で存在しない「人事」を条件にすると、0件なので #CALC! が出ます。
=FILTER(A2:D6, B2:B6="人事")対処は簡単で、第3引数に表示したい値を指定するだけです。
=FILTER(A2:D6, B2:B6="人事", "該当なし")これで0件のとき「該当なし」と表示されます。何も表示したくなければ ""（空文字）を指定します。FILTERをさらに他の関数で包んでいて、第3引数だけでは制御しきれない場合は、IFERRORで全体を囲む方法も有効です。
=IFERROR(FILTER(A2:D6, B2:B6="人事"), "該当なし")#SPILL!エラー｜3パターン別チェックリスト
#SPILL! は、抽出結果を展開（スピル）しようとしたスペースが確保できないときに出ます。競合記事は「スピル先にデータがある」で終わりがちですが、実際の原因は複数あります。よくある3パターンを症状別にまとめました。
| 症状・状況 | 原因 | 解決手順 |
|---|---|---|
| スピル予定範囲に文字や数式がある | あふれ先のセルが空でない | エラーセルを選ぶと破線枠で展開予定範囲が出る。その枠内の既存データを削除・移動する |
| 結合セルが範囲に重なっている | 結合セルにはスピルできない | 該当範囲の「セルの結合」を解除する |
| テーブル（Ctrl+T）の中で使っている | テーブル内は動的配列を展開できない | 数式をテーブルの外に移す。または「テーブルデザイン > 範囲に変換」でテーブルを解除する |
診断のコツは、エラーセルを選択することです。スピルしようとした範囲が破線枠で表示されるので、その枠の中に何が邪魔しているかを目で確認できます。エラーチェックの警告メニューから「障害のあるセルを選択」を使うと、干渉しているセルを直接特定できます。
このほか、A:A のように列全体を参照すると、1,048,576行分を返そうとして #SPILL! になります。条件範囲は A2:A100 のように必要な範囲に限定しましょう。
条件範囲のサイズ不一致エラー
第1引数のデータ範囲と、第2引数の条件範囲で、行数がそろっていないとエラーになります。たとえばデータが A2:D6（5行）なのに、条件を B2:B10（9行）と書いてしまうケースです。
（誤）=FILTER(A2:D6, B2:B10="営業", "該当なし")解決策は、データ範囲と条件範囲の行数を完全に一致させることです。
（正）=FILTER(A2:D6, B2:B6="営業", "該当なし")複数条件を * や + でつなぐときも、すべての条件範囲の行数をそろえる必要があります。別シート参照のときは、条件範囲側のシート名付け忘れもこのエラーの原因になります。
旧Excel（2019以前）ユーザー向け代替数式
「FILTER関数が使えない」という人の多くは、Excel 2019以前を使っています。動的配列関数が実装されていないため、FILTERそのものは入力できません。ただし、配列数式を使えば同じ結果を再現できます。
INDEX＋IF配列数式（Ctrl+Shift+Enter）で再現する
旧Excelで条件抽出を再現する定番が、INDEX・SMALL・IFを組み合わせた配列数式（CSE数式）です。結果を表示したい先頭セルに、次の数式を入力します。
=IFERROR(INDEX($A$2:$A$6, SMALL(IF($B$2:$B$6="営業", ROW($B$2:$B$6)-ROW($B$2)+1), ROW(A1))), "")入力のあとに Enterではなく Ctrl + Shift + Enter で確定するのが鉄則です。これで数式が配列数式として扱われ、両端に { } が付きます。あとは数式を下方向へコピーすれば、該当行が順番に表示されます。なお、Microsoft 365やExcel 2021以降なら、この数式はCtrl + Shift + Enterなしの通常確定でも動作します。
仕組みは次のとおりです。IF($B$2:$B$6="営業", ...) で営業の行番号だけを抜き出し、SMALL(..., ROW(A1)) で小さい順に1件ずつ取り出します。ROW(A1) はコピーするたびに2、3と増えるので、2件目・3件目と順に表示されます。
氏名や売上など別の列も並べたいときは、INDEXの対象範囲を $C$2:$C$6（氏名）や $D$2:$D$6（売上）に変えた数式を、横の列にそれぞれ用意します。
COUNTIF＋VLOOKUP組み合わせパターン
条件に一致する件数を数えるだけなら、配列数式を使わずCOUNTIFで足ります。
=COUNTIF($B$2:$B$6, "営業")この結果は「3」です。「営業の人数だけ知りたい」のように、抽出ではなく集計が目的なら、こちらの方がはるかに軽くて簡単です。抽出した結果から特定の値を引きたい場合は、上のINDEX配列数式で抽出列を作業列として置きます。そこにXLOOKUP関数やVLOOKUPを当てると、旧Excelでも実務に耐える表が組めます。
可能であれば、Microsoft 365へのアップデートが根本的な解決策です。FILTER関数を含む動的配列関数は、日々のデータ処理を大幅に楽にしてくれます。
実務フロー別レシピと他関数との組み合わせ
最後に、FILTER関数を他の動的配列関数と組み合わせる実務レシピを紹介します。「やりたいこと」起点で見てください。
抽出結果をSORTで並べ替える
抽出したあと、特定の列で並べ替えたいケースは多いはずです。FILTERをSORT関数で包めば、抽出と並べ替えを一気に行えます。
「営業の行を抽出し、売上（4列目）の降順で並べる」なら次のとおりです。
=SORT(FILTER(A2:D6, B2:B6="営業", ""), 4, -1)結果は山田（150）、田中（120）、佐藤（85）の順に並びます。SORTの第2引数「4」が並べ替えの基準列、第3引数「-1」が降順の指定です。昇順にしたいなら「1」にします。
UNIQUEと組み合わせて重複なしリストを作る
抽出結果から重複を除いたリストが欲しいときは、UNIQUE関数で包みます。たとえば「営業の氏名一覧（重複なし）」なら、対象列を氏名に絞って次のように書きます。
=UNIQUE(FILTER(C2:C6, B2:B6="営業", ""))部署マスタから「実際に登場する部署名の一覧」を作るときなどに重宝します。FILTERとUNIQUE、SORTを組み合わせたスピル活用は、UNIQUE・FILTER・SORT・SEQUENCEのスピル活用まとめでさらに深掘りしています。
HSTACKで離れた列を結合してから抽出する
「氏名（C列）と売上（D列）だけ、しかも順番を入れ替えて抽出したい」のように、離れた列や順序を組み替えたいときはHSTACKが便利です。HSTACKは複数の範囲を横方向に連結する関数で、Microsoft 365とExcel 2024で使えます。
=FILTER(HSTACK(C2:C6, D2:D6), B2:B6="営業", "")この結果は、営業の行について氏名と売上だけを横並びにした表になります。表示したい列だけをHSTACKで組み立ててからFILTERにかけるのがコツです。
部署別の在席リストを自動生成する
セル参照と組み合わせると、入力欄を1つ変えるだけで切り替わるリストが作れます。G1セルに見たい部署名を入れておき、次の数式を別の場所に置きます。
=FILTER(A2:D6, B2:B6=G1, "該当なし")G1を「営業」にすれば営業の一覧、「経理」にすれば経理の一覧へ瞬時に切り替わります。G1をドロップダウン（データの入力規則）にしておけば、クリックだけで部署別リストを呼び出せる簡易ツールの完成です。
Googleスプレッドシートでも使えるか
FILTER関数はGoogleスプレッドシートにも存在し、考え方は同じです。ただし複数条件の書き方に違いがあります。Excelでは第2引数の中で * や + を使いましたが、スプレッドシートでは引数をカンマで並べるだけでAND条件になります。
（スプレッドシート）=FILTER(A2:D6, B2:B6="営業", D2:D6>=100)また0件のときのエラーも異なり、スプレッドシートは #N/A を返します（Excelは #CALC!）。回避策は IFERROR(FILTER(...), "") で囲む方法が定番です。スプレッドシート側の詳しい使い方はスプレッドシートのFILTER関数の使い方で解説しています。
まとめ
ExcelのFILTER関数は、条件に合う行を元データを崩さずに別の場所へ抽出できる、動的配列関数の代表格です。最後に要点を整理します。
- 複数条件: AND条件は
*、OR条件は+、除外は<>。各条件はカッコで囲む - #CALC!: 0件のときは第3引数（空の場合）を指定すれば回避できる
- #SPILL!: スピル先のデータ・結合セル・テーブル内使用の3パターンを切り分ける
- 旧Excel: INDEX＋SMALL＋IFの配列数式（Ctrl+Shift+Enter）で代替できる
- 使い分け: 別の場所に出して自動更新したいならFILTER、目視だけならオートフィルター
SORTやUNIQUE、HSTACKと組み合わせれば、抽出から並べ替え・重複除去まで1つの数式で完結します。まずは手元の表で =FILTER(範囲, 条件, "該当なし") を打ち込み、結果がスピルする感覚をつかんでみてください。