旧ExcelでFILTER関数を代用する方法
― ROW・SMALL・INDEXで複数行抽出する仕組み
💡 この記事で理解すること
Excel 2019以前にはFILTER関数がありません。それでも、条件に合う行を1件目、2件目、3件目……と上から順に取り出すことはできます。
使う中心の関数は ROW・SMALL・INDEX の3つです。長い数式を暗記するのではなく、3人の担当者が順番に仕事をすると考えると理解しやすくなります。
行番号を記録する
1件ずつ選ぶ
名前や売上を表示する
作業列:
=IF(C2="営業部",ROW(),"")抽出側:
=IFERROR(INDEX(B:B,SMALL($A:$A,ROW(A1))),"")この記事では、この2本がなぜ動くのかをセル単位で最後まで追いかけます。
最初に完成図を見る
元データの左側に作業列Aを追加します。名前はB列、部署はC列、売上はD列です。「営業部」の行だけを右側へ抽出します。
| A:行番号 | B:名前 | C:部署 | D:売上 |
|---|---|---|---|
| 2 | 田中 | 営業部 | 120 |
| 佐藤 | 企画部 | 80 | |
| 4 | 鈴木 | 営業部 | 200 |
| 高橋 | 総務部 | 60 | |
| 6 | 伊藤 | 営業部 | 150 |
| 渡辺 | 企画部 | 90 | |
| 山本 | 総務部 | 45 | |
| 9 | 中村 | 営業部 | 180 |
| 名前 | 部署 | 売上 |
|---|---|---|
| 田中 | 営業部 | 120 |
| 鈴木 | 営業部 | 200 |
| 伊藤 | 営業部 | 150 |
| 中村 | 営業部 | 180 |
重要なのは、作業列Aの 2、4、6、9 です。これは連番ではなく、営業部が存在するシート上の本当の行番号です。この番号をSMALLが順番に選び、INDEXへ渡します。
STEP 1 ROWで「対象データの住所」を作る
A2へ次の式を入力し、元データの最終行までコピーします。
ROW()だけなら「今いる行」を返す
A2に =ROW() と入力すると2、A4なら4、A9なら9が返ります。ROWはセルの値ではなく、セルがある位置を数字にします。
| 数式がある行 | ROW()の結果 | 意味 |
|---|---|---|
| 2行目 | 2 | このデータはシートの2行目 |
| 4行目 | 4 | このデータはシートの4行目 |
| 9行目 | 9 | このデータはシートの9行目 |
IFが「営業部のときだけ」に絞る
IF(C2="営業部",ROW(),"") は、C2が営業部ならROW()を実行し、違えば空白文字 "" を返します。
| 行 | C列の部署 | 条件判定 | A列の結果 |
|---|---|---|---|
| 2 | 営業部 | 営業部 = 営業部 → TRUE | ROW() → 2 |
| 3 | 企画部 | 企画部 = 営業部 → FALSE | 空白 |
| 4 | 営業部 | 営業部 = 営業部 → TRUE | ROW() → 4 |
| 5 | 総務部 | 総務部 = 営業部 → FALSE | 空白 |
| 6 | 営業部 | 営業部 = 営業部 → TRUE | ROW() → 6 |
この時点では名前を取り出していません。営業部がある場所へ、後から見つけられる行番号の目印を置いただけです。
STEP 2 SMALLで1件目、2件目を決める
作業列Aには 2、4、6、9 が残りました。SMALL関数は、この中からk番目に小さい数を返します。
| 欲しい件数 | SMALLの式 | 選ばれる行番号 | 元データ上の人 |
|---|---|---|---|
| 1件目 | =SMALL($A:$A,1) | 2 | 2行目の田中 |
| 2件目 | =SMALL($A:$A,2) | 4 | 4行目の鈴木 |
| 3件目 | =SMALL($A:$A,3) | 6 | 6行目の伊藤 |
| 4件目 | =SMALL($A:$A,4) | 9 | 9行目の中村 |
ROW(A1)が「何件目」を自動で作る
数式を下へコピーするたびに1、2、3……と増やすため、SMALLの第2引数へ ROW(A1) を入れます。
| 抽出先 | コピー後の式 | ROWの結果 | SMALLの意味 |
|---|---|---|---|
| G2 | ROW(A1) | 1 | 1番目に小さい行番号 |
| G3 | ROW(A2) | 2 | 2番目に小さい行番号 |
| G4 | ROW(A3) | 3 | 3番目に小さい行番号 |
| G5 | ROW(A4) | 4 | 4番目に小さい行番号 |
作業列の
ROW() は「元データの場所」を返します。SMALL内の
ROW(A1) は「何件目を取り出すか」を1、2、3……と作ります。
STEP 3 INDEXで選ばれた行の値を表示する
SMALLが返すのは行番号だけです。そこでINDEXへ行番号を渡し、B列の名前を取り出します。
行番号「4」
「鈴木」を返す
名前ならB列、部署ならC列、売上ならD列をINDEXの第1引数に指定します。選ばれた行番号は共通なので、列を変えるだけで同じ行の各項目を取り出せます。
長い数式は、いちばん内側から読む
完成式を左から読むと複雑に見えます。Excelは内側から計算するので、ROW → SMALL → INDEX → IFERROR の順に追います。
IFERROR( INDEXの結果 , "" )
INDEX(B:B, SMALLの結果)
SMALL($A:$A, ROWの結果)
ROW(A1)
G2の1件目を実際に計算する
| 順番 | 計算 | 結果 |
|---|---|---|
| 1 | ROW(A1) | 1 |
| 2 | SMALL($A:$A,1) | 2 |
| 3 | INDEX(B:B,2) | 田中 |
| 4 | IFERROR("田中","") | 田中 |
下へコピーしたときの全体追跡
| 抽出先 | ROWで件数 | SMALLで行番号 | INDEXで名前 |
|---|---|---|---|
| G2 | 1件目 | 2 | 田中 |
| G3 | 2件目 | 4 | 鈴木 |
| G4 | 3件目 | 6 | 伊藤 |
| G5 | 4件目 | 9 | 中村 |
| G6 | 5件目 | #NUM! | IFERRORで空白 |
なぜIFERRORが必要なのか
営業部が4件しかないのに5件目を求めると、SMALLには5番目の数がないため #NUM! になります。IFERRORで完成式全体を包み、抽出が終わった後を空白にします。
作成途中からIFERRORを付けると、参照ミスまで空白になり原因を見つけにくくなります。まずIFERRORなしで4件が正しく抽出できることを確認し、最後に付けてください。
| 段階 | おすすめの式 | 目的 |
|---|---|---|
| 動作確認中 | =INDEX(B:B,SMALL($A:$A,ROW(A1))) | エラーが出た場所を確認する |
| 完成時 | =IFERROR(INDEX(B:B,SMALL($A:$A,ROW(A1))),"") | 対象終了後だけ空白にする |
$記号は「作業列を固定する」ためにある
$A:$A の$は、数式を横や下へコピーしても作業列Aを動かさないという意味です。
$A:$A列Aを完全に固定。部署や売上の抽出式へ横コピーしてもB:BやC:Cへずれません。ROW(A1)固定しない。下へコピーしたときA1 → A2 → A3と変わり、1件目 → 2件目 → 3件目を作ります。作業列の範囲は動かしたくない →
$A:$A何件目かは増やしたい →
ROW(A1)
条件を変えても3段階の仕組みは同じ
変えるのはSTEP 1の条件判定だけです。ROWで行番号を出した後は、SMALLとINDEXをそのまま使えます。
部分一致では検索セルM1が空白だとSEARCHがすべての行に一致するため、$M$1<>"" を先に確認しています。
実務では範囲を絞ると軽くなる
説明では分かりやすさを優先して列全体を使いました。ただし SMALL($A:$A,...) を多数コピーすると、毎回100万行以上(上限は1,048,576行)を確認します。行数が分かるなら範囲を絞ります。
作業列には実際のシート行番号が入っているため、INDEXはB:Bのままにします。INDEXもB2:B1000へ限定したい場合は、作業列を相対番号に変えます。
作業列が2、4、6なら
INDEX(B:B,...)。作業列が1、3、5の相対番号なら INDEX(B2:B1000,...)。ここを混ぜると1行ずれます。
よくある失敗を先に確認
| 症状 | 主な原因 | 確認する場所 |
|---|---|---|
| 同じ人ばかり出る | ROW(A1) が固定されている | ROW($A$1) にしていないか |
| 2件目からずれる | INDEXの範囲と行番号の種類が不一致 | 実行番号か相対番号か |
| 途中から#NUM! | 対象件数を超えた | 最後にIFERRORで包む |
| 全行が対象になる | SEARCHの検索セルが空白 | $M$1<>"" を追加 |
| 横コピーで結果が変 | 作業列Aがずれた | $A:$A と$で固定 |
| 何も表示されない | IFERRORが元のミスを隠している | 一度IFERRORを外してエラーを見る |
確認は3段階に分ける
=SMALL($A:$A,1) が最初の対象行を返すか。=INDEX(B:B,2) がB2の名前を返すか。なぜ最初の基本としておすすめなのか
ROW + SMALL + INDEX方式は最短の式ではありません。しかし、処理が3段階に分かれ、作業列を見れば途中結果を確認できます。旧Excelの複数抽出を初めて理解する方法として非常に優れています。
| 特徴 | この方式の利点 |
|---|---|
| 役割が分離される | 条件判定、順位付け、値の取得を個別に確認できる |
| CSE不要 | 各行で通常の数式としてEnterだけで確定できる |
| COUNTIF不要 | 範囲拡張カウントを使わず、各行の条件だけを判定する |
| 条件を変えやすい | 作業列のIF部分だけを完全一致・部分一致・複数条件へ変更できる |
ROW = 対象データの住所を記録
SMALL = 住所を上から1件ずつ選択
INDEX = 選ばれた住所の値を表示
IFERROR = 抽出終了後を空白に整える
次に読む記事:別方式のメリットを比較する
この基本形が理解できたら、次は「作業列を減らしたい」「大量データでもっと速くしたい」といった目的別の方式へ進めます。