旧ExcelでFILTER関数を代用する方法
― ROW・SMALL・INDEXで複数行抽出する仕組み

💡 この記事で理解すること

Excel 2019以前にはFILTER関数がありません。それでも、条件に合う行を1件目、2件目、3件目……と上から順に取り出すことはできます。

使う中心の関数は ROW・SMALL・INDEX の3つです。長い数式を暗記するのではなく、3人の担当者が順番に仕事をすると考えると理解しやすくなります。

① ROW条件に合うデータの
行番号を記録する
② SMALL小さい行番号から
1件ずつ選ぶ
③ INDEX選ばれた行の
名前や売上を表示する
先に結論
作業列:=IF(C2="営業部",ROW(),"")
抽出側:=IFERROR(INDEX(B:B,SMALL($A:$A,ROW(A1))),"")

この記事では、この2本がなぜ動くのかをセル単位で最後まで追いかけます。

最初に完成図を見る

元データの左側に作業列Aを追加します。名前はB列、部署はC列、売上はD列です。「営業部」の行だけを右側へ抽出します。

図1:元データから「営業部」だけを抜き出す全体像
元データ+作業列
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へ次の式を入力し、元データの最終行までコピーします。

A2:作業列
=IF(C2="営業部",ROW(),"")

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()を実行し、違えば空白文字 "" を返します。

図2:作業列を1行ずつ判定する流れ
行C列の部署条件判定A列の結果
2営業部営業部 = 営業部 → TRUEROW() → 2
3企画部企画部 = 営業部 → FALSE空白
4営業部営業部 = 営業部 → TRUEROW() → 4
5総務部総務部 = 営業部 → FALSE空白
6営業部営業部 = 営業部 → TRUEROW() → 6
作業列の役割は「抽出」ではなく「目印」です
この時点では名前を取り出していません。営業部がある場所へ、後から見つけられる行番号の目印を置いただけです。

STEP 2 SMALLで1件目、2件目を決める

作業列Aには 2、4、6、9 が残りました。SMALL関数は、この中からk番目に小さい数を返します。

SMALLの形
=SMALL(数値の範囲,何番目に小さい数か)
図3:SMALLが行番号を上から順に取り出す
欲しい件数SMALLの式選ばれる行番号元データ上の人
1件目=SMALL($A:$A,1)22行目の田中
2件目=SMALL($A:$A,2)44行目の鈴木
3件目=SMALL($A:$A,3)66行目の伊藤
4件目=SMALL($A:$A,4)99行目の中村

ROW(A1)が「何件目」を自動で作る

数式を下へコピーするたびに1、2、3……と増やすため、SMALLの第2引数へ ROW(A1) を入れます。

抽出先コピー後の式ROWの結果SMALLの意味
G2ROW(A1)11番目に小さい行番号
G3ROW(A2)22番目に小さい行番号
G4ROW(A3)33番目に小さい行番号
G5ROW(A4)44番目に小さい行番号
2種類のROWを混同しないでください
作業列の ROW() は「元データの場所」を返します。
SMALL内の ROW(A1) は「何件目を取り出すか」を1、2、3……と作ります。

STEP 3 INDEXで選ばれた行の値を表示する

SMALLが返すのは行番号だけです。そこでINDEXへ行番号を渡し、B列の名前を取り出します。

INDEXの形
=INDEX(取り出したい範囲,行番号)
図4:SMALLの答えをINDEXへ渡す
SMALLの答え2件目なので
行番号「4」
INDEX(B:B,4)B列4行目を見る
「鈴木」を返す

名前ならB列、部署ならC列、売上ならD列をINDEXの第1引数に指定します。選ばれた行番号は共通なので、列を変えるだけで同じ行の各項目を取り出せます。

名前
=IFERROR(INDEX(B:B,SMALL($A:$A,ROW(A1))),"")
部署
=IFERROR(INDEX(C:C,SMALL($A:$A,ROW(A1))),"")
売上
=IFERROR(INDEX(D:D,SMALL($A:$A,ROW(A1))),"")

長い数式は、いちばん内側から読む

完成式を左から読むと複雑に見えます。Excelは内側から計算するので、ROW → SMALL → INDEX → IFERROR の順に追います。

完成式
=IFERROR(INDEX(B:B,SMALL($A:$A,ROW(A1))),"")
④ IFERROR:対象がなくなった後のエラーを空白にする IFERROR( INDEXの結果 , "" )
③ INDEX:B列の指定行から名前を返す INDEX(B:B, SMALLの結果)
② SMALL:作業列からk番目に小さい行番号を返す SMALL($A:$A, ROWの結果)
① ROW:下へコピーすると1、2、3……を作る ROW(A1)

G2の1件目を実際に計算する

順番計算結果
1ROW(A1)1
2SMALL($A:$A,1)2
3INDEX(B:B,2)田中
4IFERROR("田中","")田中

下へコピーしたときの全体追跡

抽出先ROWで件数SMALLで行番号INDEXで名前
G21件目2田中
G32件目4鈴木
G43件目6伊藤
G54件目9中村
G65件目#NUM!IFERRORで空白

なぜIFERRORが必要なのか

営業部が4件しかないのに5件目を求めると、SMALLには5番目の数がないため #NUM! になります。IFERRORで完成式全体を包み、抽出が終わった後を空白にします。

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をそのまま使えます。

セルの条件
=IF(C2=$M$1,ROW(),"")
部分一致
=IF(AND($M$1<>"",ISNUMBER(SEARCH($M$1,C2))),ROW(),"")
複数条件
=IF(AND(C2="営業部",D2>=100),ROW(),"")

部分一致では検索セルM1が空白だとSEARCHがすべての行に一致するため、$M$1<>"" を先に確認しています。

実務では範囲を絞ると軽くなる

説明では分かりやすさを優先して列全体を使いました。ただし SMALL($A:$A,...) を多数コピーすると、毎回100万行以上(上限は1,048,576行)を確認します。行数が分かるなら範囲を絞ります。

範囲を限定
=IFERROR(INDEX(B:B,SMALL($A$2:$A$1000,ROW(A1))),"")

作業列には実際のシート行番号が入っているため、INDEXはB:Bのままにします。INDEXもB2:B1000へ限定したい場合は、作業列を相対番号に変えます。

相対番号
=IF(C2="営業部",ROW()-ROW($C$2)+1,"")
範囲INDEX
=IFERROR(INDEX($B$2:$B$1000,SMALL($A$2:$A$1000,ROW(A1))),"")
実際の行番号と相対番号を混ぜないこと
作業列が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だけ=SMALL($A:$A,1) が最初の対象行を返すか。
③ INDEXだけ=INDEX(B:B,2) がB2の名前を返すか。
④ 最後に合体3つが単独で正しければ完成式へ組み合わせる。

なぜ最初の基本としておすすめなのか

ROW + SMALL + INDEX方式は最短の式ではありません。しかし、処理が3段階に分かれ、作業列を見れば途中結果を確認できます。旧Excelの複数抽出を初めて理解する方法として非常に優れています。

特徴この方式の利点
役割が分離される条件判定、順位付け、値の取得を個別に確認できる
CSE不要各行で通常の数式としてEnterだけで確定できる
COUNTIF不要範囲拡張カウントを使わず、各行の条件だけを判定する
条件を変えやすい作業列のIF部分だけを完全一致・部分一致・複数条件へ変更できる
ここまでの要点
ROW = 対象データの住所を記録
SMALL = 住所を上から1件ずつ選択
INDEX = 選ばれた住所の値を表示
IFERROR = 抽出終了後を空白に整える

次に読む記事:別方式のメリットを比較する

この基本形が理解できたら、次は「作業列を減らしたい」「大量データでもっと速くしたい」といった目的別の方式へ進めます。

🔗 関連記事