FILTER：条件に合うすべてのデータを動的に一括抽出するスピルの王様
💡 やりたいこと
「営業部のデータだけを全部抜き出したい」
「売上100万以上の行だけを別シートに自動一覧化させたい」
1件だけ（XLOOKUP）ではなく、複数件該当するデータを全て引っ張ってきたい。
サンプルデータ（この記事で使う元テーブル）
| A | B | C | D | |
| 1 | 名前 | 部署 | 売上 | 年齢 |
| 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 佐藤 | 企画部 | 80 | 35 |
| 4 | 鈴木 | 営業部 | 200 | 42 |
| 5 | 高橋 | 総務部 | 60 | 30 |
| 6 | 伊藤 | 営業部 | 150 | 25 |
| 7 | 渡辺 | 企画部 | 90 | 38 |
| 8 | 山本 | 総務部 | 45 | 50 |
| 9 | 中村 | 営業部 | 180 | 33 |
| 10 | 小林 | 企画部 | 110 | 29 |
以下、すべてこのデータを使って説明します。B列のオレンジ色が「営業部」のヒット行です。
新関数での解決法：FILTER関数での動的抽出
XLOOKUPが「1件見つけて終わり」なのに対し、FILTER関数は「条件を満たすすべての行と列（配列全体）」をガバッとスピル機能で展開してくれます。
長年VBAマクロ（AutoFilterのコピペ処理）で作られてきた機能の多くを、この数式1つで駆逐できます。
基本例：「営業部」だけ全行抽出
意味：A2からD10の中で、B列（部署）が「営業部」の行だけ全部抜き出す。
数式を入力するのは左上の1セルだけでOK。下方向・右方向へ全データが自動展開（スピル）されます。
| F | G | H | I | |
| 1 | 名前 | 部署 | 売上 | 年齢 |
| 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 鈴木 | 営業部 | 200 | 42 |
| 4 | 伊藤 | 営業部 | 150 | 25 |
| 5 | 中村 | 営業部 | 180 | 33 |
FILTER関数 実践パターン集
パターン1：AND条件（「かつ」）── 「*」で掛け算する
「営業部」かつ「売上150以上」 → 鈴木(200)・伊藤(150)・中村(180) の3件
条件式
(B2:B10="営業部") は各行ごとに TRUE(1) か FALSE(0) の配列を返します。2つの条件を
* で掛けると「1×1＝1（両方TRUE）」だけが残り、「1×0＝0」や「0×0＝0」は除外されます。これがAND条件の仕組みです。
パターン2：OR条件（「または」）── 「+」で足し算する
「営業部」または「企画部」 → 営業部4件＋企画部3件 ＝ 計7件
足し算だと「1+0＝1」「0+1＝1」「1+1＝2」はいずれも0以外 → TRUE扱い。「0+0＝0」だけがFALSE。
つまり「どちらか片方でもTRUEなら残る」＝OR条件になります。
パターン3：数値の範囲指定 ──「1以上50以下」を抜き出す
年齢（D列）が「25歳以上 かつ 35歳以下」の人だけ抽出
| F | G | H | I | |
| 1 | 名前 | 部署 | 売上 | 年齢 |
| 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 佐藤 | 企画部 | 80 | 35 |
| 4 | 高橋 | 総務部 | 60 | 30 |
| 5 | 伊藤 | 営業部 | 150 | 25 |
| 6 | 中村 | 営業部 | 180 | 33 |
| 7 | 小林 | 企画部 | 110 | 29 |
F1セルに下限値、G1セルに上限値を入れておけば、
=FILTER(A2:D10, (D2:D10>=F1) * (D2:D10<=G1), "該当なし")と書くだけで「スライダー感覚」で条件を切り替えられます。
パターン4：ANDとORの合わせ技
（「営業部」または「企画部」）かつ「売上100以上」
| F | G | H | I | |
| 1 | 名前 | 部署 | 売上 | 年齢 |
| 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 鈴木 | 営業部 | 200 | 42 |
| 4 | 伊藤 | 営業部 | 150 | 25 |
| 5 | 中村 | 営業部 | 180 | 33 |
| 6 | 小林 | 企画部 | 110 | 29 |
( ) でひとかたまりにして、それ全体を * でAND結合する。「OR → 足し算(+)をカッコで囲む」「AND → カッコ同士を掛け算(*)」と覚えれば迷いません。
パターン5：必要な列だけ選んで抽出
CHOOSECOLS で「1列目（名前）と3列目（売上）」だけ取り出してから FILTER に渡す。
全4列は不要で「名前と売上だけほしい」というときに便利です。
旧バージョンでの代替法
FILTER関数が登場する前のExcelでは、「条件に合う複数行を全部抜き出す」ために、大きく2つの方法が取られていました。
方法1：配列数式（CSE数式）── 玄人向きだが激重
「一致する行の一覧を、上から詰めて表示する」ために、以下のような呪文を数十行〜数百行分コピーペーストしていました。
【解読】
1. IF関数で「営業部ならその相対行番号、違えばFALSE」という配列を作る。
2. SMALL(配列, ROW(A1)) で、その配列の中から「1番目に小さい行番号（＝1件目のヒット）」を取得する。下にコピーすると ROW(A1)→ROW(A2)→… と増えていき、2件目・3件目と順番に取得される。
3. 出た行番号を INDEX に渡して実際のデータを取得する。
4. ヒット件数を超えるとSMALLがエラーになるので、IFERROR で空白に変換する。
この数式は1セルごとに全1000行を総当たりでチェックします。これを10列×1000行（計1万セル）にコピーペーストすると、
「1000×1万＝1000万回」もの内部配列演算が走り、Excelが完全にフリーズして「計算中 0%...」から戻ってこなくなる原因でした。
AGGREGATE関数の「15(SMALL)」を使った改良版もありますが、内部で配列を抱える重さは本質的に同じです。
さらに、後任者がセルをダブルクリックして普通にEnterを押すと
{} が外れて数式が壊れる事故（通称：CSE崩壊）も頻発しました。
方法2：作業列方式（ROW関数）── 軽量・安全・読みやすい
元の表の横に1列「作業列」を追加するだけで、配列数式の地獄から解放されます。
COUNTIFで連番を振る方式もありますが、COUNTIFは全行を対象にした範囲拡張カウントになるため行数が増えると重くなります。ここではROW関数を使い、各セルが自分の行番号だけを返す（他の行を参照しない）超軽量な方法を紹介します。
ステップ1：作業列に条件判定＋行番号を入れる
E列を作業列として使います。E2セルに以下を入力し、E10まで下へコピーします。
B列が「営業部」なら自分の行番号を表示。違えば空白。
ROW() は自分の行番号を返すだけなので、他のセルを参照する計算負荷がゼロです。
| A | B | C | D | E（作業列） | |
| 1 | 名前 | 部署 | 売上 | 年齢 | 判定 |
| 2 | 田中 | 営業部 | 120 | 28 | 2 |
| 3 | 佐藤 | 企画部 | 80 | 35 | |
| 4 | 鈴木 | 営業部 | 200 | 42 | 4 |
| 5 | 高橋 | 総務部 | 60 | 30 | |
| 6 | 伊藤 | 営業部 | 150 | 25 | 6 |
| 7 | 渡辺 | 企画部 | 90 | 38 | |
| 8 | 山本 | 総務部 | 45 | 50 | |
| 9 | 中村 | 営業部 | 180 | 33 | 9 |
| 10 | 小林 | 企画部 | 110 | 29 |
ステップ2：抽出先にSMALL + INDEXで取り出す
抽出先（例えばG列）のG2セルに以下を入力し、下へコピーします。
E列（作業列）にある行番号を SMALL で小さい順に取り出し、INDEX で A列のデータを取得。
ROW(A1)=1 → 1番目に小さい行番号 → 2行目（田中）
ROW(A2)=2 → 2番目に小さい行番号 → 4行目（鈴木）
ROW(A3)=3 → 3番目に小さい行番号 → 6行目（伊藤）
ROW(A4)=4 → 4番目に小さい行番号 → 9行目（中村）
5番目以降はエラー → IFERROR で空白に。
B列〜D列も同様に =IFERROR(INDEX(B:B, SMALL(E:E, ROW(A1))), "") などと書いて横に並べます。
| G | H | I | J | |
| 1 | 名前 | 部署 | 売上 | 年齢 |
| 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 鈴木 | 営業部 | 200 | 42 |
| 4 | 伊藤 | 営業部 | 150 | 25 |
| 5 | 中村 | 営業部 | 180 | 33 |
COUNTIF($B$2:B2,"営業部") による連番方式は、セルをコピーするたびに範囲が1行ずつ拡張され、下の行ほど参照セル数が増えていきます（2行目は1セル、1000行目は999セルを数える）。合計すると約50万回のカウント処理が発生します。一方
ROW() は自分自身の行番号を返すだけで、他のセルを一切参照しません。どの行でも計算コストは一定（定数時間）です。1000行程度なら体感差はわずかですが、1万行・10万行になるとCOUNTIFの範囲拡張は目に見えて重くなるので、最初からROW方式で作っておくのが安全です。
参考：COUNTIF連番方式（非推奨）との違い
参考までに、COUNTIF連番方式の作業列はこう書きます。
「ここまでに営業部が何回目か」を連番として出す方式。これを MATCH で検索して INDEX で取り出す組み合わせになります。
動作はしますが、範囲拡張カウントにより大量データで重くなるため、ROW方式を推奨します。
3方式の比較
| 配列数式（CSE） | 作業列 + ROW（推奨） | 作業列 + COUNTIF | |
|---|---|---|---|
| 計算速度 | 激重（爆発的に増大） | 超軽量（定数時間） | 中程度（範囲拡張で増大） |
| CSE必要 | 必要（崩壊リスク大） | 不要 | 不要 |
| 読みやすさ | 解読困難 | 2段階で明快 | やや読みやすい |
| 作業列 | 不要 | 1列必要（非表示で対処可） | 1列必要（非表示で対処可） |
| 1万行での体感 | フリーズ級 | 一瞬 | 数秒〜もっさり |
それでもVBAが使われた理由
当時の実務で「VBAマクロが組まれるのが一般的」だった最大の理由は、「作業列を作ること（表の形を変えること）」を極端に嫌う上司やクライアントが多かったという、Excelの機能以外の人間的な事情です。
「余計な列を見せるな」「元のフォーマットを崩すな」というルールの下では、作業列を非表示にしても「なぜ列が飛んでいるんだ」と指摘される現場が多く、結果として見た目を一切変えずに裏側で処理できるVBAに頼らざるを得ませんでした。
作業列を1本追加して非表示にしてしまえば、実はVBAすら不要なケースが大半だったのです。
まとめ
FILTER関数が使える環境（Excel 2021 / 2024 / Microsoft 365など）であれば、迷わずFILTER関数を使いましょう。数式1本、CSE不要、スピルで自動展開、条件変更も即反映。旧来の苦労がすべて過去のものになります。
やむを得ず旧バージョン（Excel 2019以前）を使う場合は、配列数式の呪文に手を出すよりも、作業列＋ROW方式を使ってください。読みやすく、軽く、CSE崩壊の心配もありません。