旧ExcelでFILTER関数の代わりに複数抽出する方法
― 分かりやすい方法から最速方式まで課題解決で解説
💡 やりたいこと
Excel 2021 / Microsoft 365 では、FILTER関数を使えば条件に合うデータをまとめて抽出できます。
FILTER
=FILTER(A2:D1000,B2:B1000="営業部")
しかし、Excel 2019以前の旧ExcelではFILTER関数が使えません。
この記事では、旧Excelでも VBA不要・Ctrl+Shift+Enter不要 で、FILTER関数のように条件一致データを複数行抽出する方法を解説します。
この記事の流れ：
まず、分かりやすい COUNTIF方式 から説明します。
COUNTIF方式は理解しやすい一方で、大量データでは重くなります。
そこから、COUNTIFをやめる方法、作業列を減らす方法、最後に 最速の上行参照方式 へ順番に改善していきます。
以前よく見かける COUNTIF + SMALL + INDEX は、COUNTIFとSMALLを重ねるだけで実務上のメリットが薄いため、この記事では方式として採用しません。
まず、分かりやすい COUNTIF方式 から説明します。
COUNTIF方式は理解しやすい一方で、大量データでは重くなります。
そこから、COUNTIFをやめる方法、作業列を減らす方法、最後に 最速の上行参照方式 へ順番に改善していきます。
以前よく見かける COUNTIF + SMALL + INDEX は、COUNTIFとSMALLを重ねるだけで実務上のメリットが薄いため、この記事では方式として採用しません。
先に結論：目的別のおすすめ
小規模・分かりやすさ
COUNTIF + INDEX + MATCH
数百〜数千行程度なら動くことも多く、連番の考え方が分かりやすいです。
ただし大量データでは重くなりやすいです。
あいまい検索向き
対象行だけROW
+ SMALL + INDEX
+ SMALL + INDEX
COUNTIFを使わず、対象行だけ行番号を出します。
SEARCH/FINDを使った部分一致も分かりやすく作れます。
軽量化重視
AGGREGATEで行番号
+ INDEX参照
+ INDEX参照
元データ側の作業列を増やさないため、ファイルサイズを小さくしやすいです。
行番号だけ出して各列はINDEX参照にします。
最速
上行参照で連番
+ INDEX + MATCH
+ INDEX + MATCH
COUNTIFもSMALLも使わないため、最速クラスです。
10万行級の住所録・顧客リスト・明細データでは本命です。
行数による目安：
数百〜数千行程度なら、COUNTIF方式でも問題なく動くことが多いです。
1万行前後になると、ブックの数式量やPC性能によっては再計算の重さを感じることがあります。
住所録・顧客リスト・販売明細のように10万行以上になると、COUNTIFの範囲拡張が大きなボトルネックになりやすく、ROW方式・AGGREGATE方式・上行参照方式への切り替えを検討した方が安全です。
数百〜数千行程度なら、COUNTIF方式でも問題なく動くことが多いです。
1万行前後になると、ブックの数式量やPC性能によっては再計算の重さを感じることがあります。
住所録・顧客リスト・販売明細のように10万行以上になると、COUNTIFの範囲拡張が大きなボトルネックになりやすく、ROW方式・AGGREGATE方式・上行参照方式への切り替えを検討した方が安全です。
サンプルデータ
以下の表から、部署が「営業部」の行だけを抽出します。
| 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 |
抽出結果は、田中・鈴木・伊藤・中村の4行になります。
抽出条件の作り方：完全一致・部分一致・あいまい検索
旧関数でFILTER代替を作る場合、重要なのは「条件式」の作り方です。
完全一致なら B2="営業部" で済みますが、部分一致やあいまい検索をしたい場合は、FIND関数やSEARCH関数を使います。
| やりたいこと | 条件式の例 | 説明 |
|---|---|---|
| 完全一致 | B2="営業部" | B2セルが営業部と完全に一致する行だけを抽出します。 |
| 部分一致 大文字小文字を区別する | ISNUMBER(FIND("営業",B2)) | FINDは大文字小文字を区別します。 |
| 部分一致 大文字小文字を区別しない | ISNUMBER(SEARCH("営業",B2)) | SEARCHは大文字小文字を区別しません。 |
| 検索語をセル参照にする | ISNUMBER(SEARCH($M$1,B2)) | M1セルに入力した文字を含む行だけを抽出します。 |
| 含まない行を抽出 | NOT(ISNUMBER(SEARCH($M$1,B2))) | M1セルの文字を含まない行だけを抽出します。 |
| AND条件 | (B2="営業部")*(C2>=100) | 部署が営業部、かつ売上が100以上の行だけを抽出します。 |
| OR条件 | ((B2="営業部")+(B2="企画部"))>0 | 営業部または企画部の行を抽出します。 |
| 部分一致 + 数値条件 | ISNUMBER(SEARCH($M$1,B2))*(C2>=100) | M1セルの文字を含み、かつ売上が100以上の行を抽出します。 |
実務でのおすすめ：
完全一致なら
「営業部」「営業一課」「法人営業」など、文字を含むデータをまとめて拾いたい場合は
実務では、大文字小文字を区別しない
完全一致なら
= でOKです。「営業部」「営業一課」「法人営業」など、文字を含むデータをまとめて拾いたい場合は
SEARCH または FIND を使います。実務では、大文字小文字を区別しない
SEARCH の方が扱いやすいことが多いです。
方法比較：どれを使うべきか
ここでは、あえて「分かりやすいが重い方法」から「最速方式」へ並べています。
1つずつ問題点を解決していく流れで見ると、なぜ速くなるのかが分かりやすくなります。
| 比較項目 | 方法1COUNTIF + INDEX + MATCH |
方法2対象行だけROW + SMALL + INDEX |
方法3AGGREGATEで行番号 + INDEX参照 |
方法4上行参照で連番 + INDEX + MATCH |
|---|---|---|---|---|
| 位置づけ | 分かりやすい基本形 | COUNTIFをやめる改善版 | 作業列を減らす軽量版 | 最速版 |
| おすすめ度 | ★★★☆☆ 小規模なら可 | ★★★★☆ シンプルで高速 | ★★★★☆ 軽量化に有効 | ★★★★★ 大量データの本命 |
| 速度 | 小規模なら可 大量では重い | 高速 COUNTIFを使わない | 中〜高速 行番号だけなら実用的 | 最速 COUNTIFもSMALLも使わない |
| 作業列 | 元データ左側に必要 | 元データ左側に必要 | 元データ側は不要 抽出側左に行番号列 | 元データ左側に必要 |
| ファイルサイズ | 作業列1列分増える | 作業列1列分増える | 小さくしやすい 元データ側に大量の作業列を持たない | 作業列1列分増える |
| 完全一致 | =IF(C2="営業部",COUNTIF($C$2:C2,"営業部"),"") | =IF(C2="営業部",ROW(),"") | ROW範囲/(部署範囲="営業部") | =IF(C3="営業部",A2+1,A2) |
| あいまい検索 | COUNTIFのワイルドカードで可能 ただし範囲拡張が重い | =IF(ISNUMBER(SEARCH($M$1,C2)),ROW(),"") | ROW範囲/ISNUMBER(SEARCH($M$1,部署範囲)) | =IF(ISNUMBER(SEARCH($M$1,C3)),A2+1,A2) |
| メリット | 連番が分かりやすい 学習用に向く | 対象行だけROWで分かりやすい あいまい検索に強い | 作業列が少ない ファイルサイズを小さくしやすい | 最速クラス 大量データに強い |
| デメリット | COUNTIFの範囲拡張が重い | 抽出側でSMALLを使う | AGGREGATE式が長い 最速ではない | 作業列の番号の考え方が少し難しい |
| 向いている場面 | 小規模データ 仕組み説明 | あいまい検索 COUNTIF回避 | ファイル軽量化 元表を崩したくない | 10万行級の住所録・顧客リスト・明細 |
※ PCでは比較表だけ横幅を広く使って表示します。スマホや幅の狭い画面では横にスクロールできます。
小規模向け方法1：COUNTIF + INDEX + MATCH
| 向いている | 小規模データ、学習用、仕組みを説明したい場合 |
|---|---|
| メリット | ・考え方が分かりやすい ・条件に合う行だけに「1件目、2件目、3件目」と連番が出る ・数百〜数千行程度なら問題ないことも多い ・抽出側はINDEX + MATCHで比較的分かりやすい |
| デメリット | ・COUNTIFの範囲拡張が重い ・行数が増えるほど急激に遅くなる ・10万行級では実務上かなり厳しくなることがある |
まずは一番分かりやすい方法です。
COUNTIFで「条件に合う行が何件目か」を作業列に出し、INDEX + MATCHで取り出します。
STEP 1 作業列を左側に追加する
元データの左に作業列を追加します。
作業列A、名前B、部署C、売上D、年齢Eという配置にします。
A2
=IF(C2="営業部",COUNTIF($C$2:C2,"営業部"),"")
A3以降
=IF(C3="営業部",COUNTIF($C$2:C3,"営業部"),"")
COUNTIF方式では、条件に合う行だけに「1件目、2件目、3件目」と番号が表示されます。
| 行 | A列：抽出番号 | B列：名前 | C列：部署 | D列：売上 | E列：年齢 |
|---|---|---|---|---|---|
| 2 | 1 | 田中 | 営業部 | 120 | 28 |
| 3 | 佐藤 | 企画部 | 80 | 35 | |
| 4 | 2 | 鈴木 | 営業部 | 200 | 42 |
| 5 | 高橋 | 総務部 | 60 | 30 | |
| 6 | 3 | 伊藤 | 営業部 | 150 | 25 |
| 7 | 渡辺 | 企画部 | 90 | 38 | |
| 8 | 山本 | 総務部 | 45 | 50 | |
| 9 | 4 | 中村 | 営業部 | 180 | 33 |
| 10 | 小林 | 企画部 | 110 | 29 |
部分一致で「営業」を含む行を対象にする場合は、COUNTIFのワイルドカードでも書けます。
部分一致
=IF(COUNTIF(C2,"*営業*")>0,COUNTIF($C$2:C2,"*営業*"),"")
ただし注意：
ワイルドカードで部分一致はできますが、
つまり、部分一致にしてもCOUNTIFの重さは残ります。
ワイルドカードで部分一致はできますが、
COUNTIF($C$2:C2,"*営業*") のように範囲が下へ広がる点は変わりません。つまり、部分一致にしてもCOUNTIFの重さは残ります。
STEP 2 INDEX + MATCHで抽出する
名前
=IFERROR(INDEX(B:B,MATCH(ROW(A1),A:A,0)),"")
部署
=IFERROR(INDEX(C:C,MATCH(ROW(A1),A:A,0)),"")
売上
=IFERROR(INDEX(D:D,MATCH(ROW(A1),A:A,0)),"")
年齢
=IFERROR(INDEX(E:E,MATCH(ROW(A1),A:A,0)),"")
COUNTIF方式のメリット：
この方式は、作業列に「1件目、2件目、3件目」と出るため、仕組みが非常に分かりやすいです。
小規模データでは十分実用的なこともあります。
ただし、大量データではCOUNTIFの範囲拡張がボトルネックになります。
この方式は、作業列に「1件目、2件目、3件目」と出るため、仕組みが非常に分かりやすいです。
小規模データでは十分実用的なこともあります。
ただし、大量データではCOUNTIFの範囲拡張がボトルネックになります。
高速・シンプル方法2：対象行だけROW + SMALL + INDEX
| 向いている | COUNTIFを避けたい場合、あいまい検索を分かりやすく作りたい場合、対象行だけを見える形にしたい場合 |
|---|---|
| メリット | ・COUNTIFを使わない ・各行の判定だけなので作業列が軽い ・対象行だけROW番号が出るため見た目が分かりやすい ・FIND関数やSEARCH関数で部分一致にも対応しやすい |
| デメリット | ・作業列が必要 ・抽出側でSMALLを使う ・抽出列が多いとSMALLの回数が増える ・速度最優先なら上行参照方式に劣る |
COUNTIFが重いなら、まずCOUNTIFをやめます。
この方法では、作業列に「対象行なら実際の行番号、対象外なら空白」を出します。
STEP 1 完全一致：対象行だけROWを出す
A2以降
=IF(C2="営業部",ROW(),"")
部分一致・あいまい検索：SEARCHまたはFINDを使う
SEARCH
=IF(ISNUMBER(SEARCH($M$1,C2)),ROW(),"")
FIND
=IF(ISNUMBER(FIND($M$1,C2)),ROW(),"")
複数条件
=IF(ISNUMBER(SEARCH("営業",C2))*(D2>=100),ROW(),"")
| 行 | A列：抽出行 | B列：名前 | C列：部署 | D列：売上 | E列：年齢 |
|---|---|---|---|---|---|
| 2 | 2 | 田中 | 営業部 | 120 | 28 |
| 3 | 佐藤 | 企画部 | 80 | 35 | |
| 4 | 4 | 鈴木 | 営業部 | 200 | 42 |
| 5 | 高橋 | 総務部 | 60 | 30 | |
| 6 | 6 | 伊藤 | 営業部 | 150 | 25 |
| 7 | 渡辺 | 企画部 | 90 | 38 | |
| 8 | 山本 | 総務部 | 45 | 50 | |
| 9 | 9 | 中村 | 営業部 | 180 | 33 |
| 10 | 小林 | 企画部 | 110 | 29 |
STEP 2 SMALL + INDEXで抽出する
名前
=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))),"")
年齢
=IFERROR(INDEX(E:E,SMALL($A:$A,ROW(A1))),"")
なぜCOUNTIFより速くなりやすいのか：
COUNTIF方式は、下の行へ行くほど参照範囲が広がります。
一方、このROW方式は、各行で「この行が対象かどうか」を判定するだけです。
そのため、作業列側の計算は非常に軽くなります。
COUNTIF方式は、下の行へ行くほど参照範囲が広がります。
一方、このROW方式は、各行で「この行が対象かどうか」を判定するだけです。
そのため、作業列側の計算は非常に軽くなります。
作業列少なめ方法3：AGGREGATEで行番号だけ抽出 + INDEX
| 向いている | 元データ側に作業列を追加したくない場合、ファイルサイズを小さくしたい場合、表の構造を崩したくない場合 |
|---|---|
| メリット | ・元データ側に作業列が不要 ・CSE不要で複数抽出できる ・行番号だけAGGREGATEで出せば計算回数を減らせる ・作業列が少ないためファイルサイズを小さくしやすい ・完全一致もあいまい検索も対応できる |
| デメリット | ・AGGREGATEの数式が長い ・各列に直接AGGREGATEを書くと重い ・速度最優先では上行参照方式に劣る ・抽出側に行番号列は必要になる |
元データ側に作業列を追加したくない場合は、AGGREGATE関数を使います。
ただし、各列に直接AGGREGATEを書くと重くなりやすいため、まず行番号だけを出し、その行番号を各列で参照するのがおすすめです。
AGGREGATE高速化と軽量化のコツ：
AGGREGATEは便利ですが、各列に何度も書くと同じ条件判定を繰り返して重くなります。
そのため、まず1列だけで必要な行番号を出します。
名前・部署・売上・年齢は、その行番号を参照してINDEXで取り出します。
また、元データ側に大量の作業列数式を持たないため、ブックのファイルサイズを小さくしやすいというメリットもあります。
AGGREGATEは便利ですが、各列に何度も書くと同じ条件判定を繰り返して重くなります。
そのため、まず1列だけで必要な行番号を出します。
名前・部署・売上・年齢は、その行番号を参照してINDEXで取り出します。
また、元データ側に大量の作業列数式を持たないため、ブックのファイルサイズを小さくしやすいというメリットもあります。
STEP 1 抽出結果の左側に行番号列を作る
例として、元データはA:D、部署はB列にある前提です。
抽出結果側の左にG列を作り、G2セルに以下を入力して下へコピーします。
完全一致
=IFERROR(AGGREGATE(15,6,ROW($B$2:$B$1000)/($B$2:$B$1000="営業部"),ROWS($G$2:G2)),"")
SEARCH
=IFERROR(AGGREGATE(15,6,ROW($B$2:$B$1000)/ISNUMBER(SEARCH($M$1,$B$2:$B$1000)),ROWS($G$2:G2)),"")
FIND
=IFERROR(AGGREGATE(15,6,ROW($B$2:$B$1000)/ISNUMBER(FIND($M$1,$B$2:$B$1000)),ROWS($G$2:G2)),"")
複数条件
=IFERROR(AGGREGATE(15,6,ROW($B$2:$B$1000)/(ISNUMBER(SEARCH("営業",$B$2:$B$1000))*($C$2:$C$1000>=100)),ROWS($G$2:G2)),"")
| G列：抽出行番号 | H列：名前 | I列：部署 | J列：売上 | K列：年齢 |
|---|---|---|---|---|
| 2 | 田中 | 営業部 | 120 | 28 |
| 4 | 鈴木 | 営業部 | 200 | 42 |
| 6 | 伊藤 | 営業部 | 150 | 25 |
| 9 | 中村 | 営業部 | 180 | 33 |
ROWS($G$2:G2) の意味：
AGGREGATEの最後の引数は「何番目に小さい値を返すか」です。
AGGREGATEの最後の引数は「何番目に小さい値を返すか」です。
ROWS($G$2:G2) はG2では1、G3では2、G4では3……と増えます。
STEP 2 行番号を参照して各列を取り出す
G列には、表の中で何番目かではなく、実際のシート行番号 が入ります。
そのため、取り出し側は列全体を参照するとズレません。
名前
=IF($G2="","",INDEX(A:A,$G2))
部署
=IF($G2="","",INDEX(B:B,$G2))
売上
=IF($G2="","",INDEX(C:C,$G2))
年齢
=IF($G2="","",INDEX(D:D,$G2))
横コピー用
=IF($G2="","",INDEX($A:$D,$G2,COLUMNS($H:H)))
行番号のズレに注意：
このAGGREGATE式は、表の中で何番目かではなく、実際のシート行番号を返します。
そのため、
一方、
このAGGREGATE式は、表の中で何番目かではなく、実際のシート行番号を返します。
そのため、
INDEX(A:A,$G2) のように列全体を参照すればズレません。一方、
INDEX(A2:A1000,$G2) のようにデータ範囲だけを指定すると、G2の値が実際の行番号であるため、1行ずれることがあります。
範囲指定で取り出したい場合：相対番号方式
相対番号
=IFERROR(AGGREGATE(15,6,(ROW($B$2:$B$1000)-ROW($B$2)+1)/ISNUMBER(SEARCH($M$1,$B$2:$B$1000)),ROWS($G$2:G2)),"")
範囲INDEX
=IF($G2="","",INDEX($A$2:$D$1000,$G2,COLUMNS($H:H)))
方法3の結論：
AGGREGATE方式は最速ではありません。
しかし、元データ側に作業列を追加しなくてよいため、ファイルサイズを小さくしやすく、表の構造も崩しにくいです。
AGGREGATE方式は最速ではありません。
しかし、元データ側に作業列を追加しなくてよいため、ファイルサイズを小さくしやすく、表の構造も崩しにくいです。
最速方法4：上行参照で連番 + INDEX + MATCH
| 向いている | 大量データ、速度最優先、再計算を軽くしたいブック、10万行級の住所録・顧客リスト・明細データ |
|---|---|
| メリット | ・COUNTIFを使わない ・SMALLも使わない ・各行の計算が軽い ・抽出側もINDEX + MATCHで高速 ・完全一致もあいまい検索も対応できる |
| デメリット | ・作業列が必要 ・条件外行にも番号が残る ・COUNTIF方式より連番の作り方が少し分かりにくい |
最後に、最速方式です。
COUNTIFもSMALLも使わず、作業列はすぐ上のセルだけを参照して連番を作ります。
最速ですが、少し分かりにくいです：
COUNTIF方式は「何件目か」を数えるので分かりやすいです。
一方、上行参照方式は、条件に合わない行にも直前の番号が残ります。
COUNTIF方式は「何件目か」を数えるので分かりやすいです。
一方、上行参照方式は、条件に合わない行にも直前の番号が残ります。
STEP 1 完全一致で作業列を作る
A2
=IF(C2="営業部",1,0)
A3以降
=IF(C3="営業部",A2+1,A2)
部分一致・あいまい検索にする場合
A2 SEARCH
=IF(ISNUMBER(SEARCH($M$1,C2)),1,0)
A3以降 SEARCH
=IF(ISNUMBER(SEARCH($M$1,C3)),A2+1,A2)
A3以降 FIND
=IF(ISNUMBER(FIND($M$1,C3)),A2+1,A2)
| 行 | A列：連番 | B列：名前 | C列：部署 | D列：売上 | E列：年齢 |
|---|---|---|---|---|---|
| 2 | 1 | 田中 | 営業部 | 120 | 28 |
| 3 | 1 | 佐藤 | 企画部 | 80 | 35 |
| 4 | 2 | 鈴木 | 営業部 | 200 | 42 |
| 5 | 2 | 高橋 | 総務部 | 60 | 30 |
| 6 | 3 | 伊藤 | 営業部 | 150 | 25 |
| 7 | 3 | 渡辺 | 企画部 | 90 | 38 |
| 8 | 3 | 山本 | 総務部 | 45 | 50 |
| 9 | 4 | 中村 | 営業部 | 180 | 33 |
| 10 | 4 | 小林 | 企画部 | 110 | 29 |
条件外行にも番号が残る理由：
営業部でない行は、すぐ上の番号をそのまま引き継ぎます。
MATCH関数は上から探して最初に見つかった位置を返すため問題ありません。
「2」を探せば、最初に2が出る4行目、つまり鈴木の行が取得されます。
営業部でない行は、すぐ上の番号をそのまま引き継ぎます。
MATCH関数は上から探して最初に見つかった位置を返すため問題ありません。
「2」を探せば、最初に2が出る4行目、つまり鈴木の行が取得されます。
STEP 2 INDEX + MATCHで抽出する
名前
=IFERROR(INDEX(B:B,MATCH(ROW(A1),A:A,0)),"")
部署
=IFERROR(INDEX(C:C,MATCH(ROW(A1),A:A,0)),"")
売上
=IFERROR(INDEX(D:D,MATCH(ROW(A1),A:A,0)),"")
年齢
=IFERROR(INDEX(E:E,MATCH(ROW(A1),A:A,0)),"")
方法4の結論：
速度最優先ならこの方法です。
COUNTIFもSMALLも使わないため、旧Excelで大量データを抽出する場合の本命です。
作業列の番号の考え方は少し難しいですが、一度理解すれば非常に強力です。
速度最優先ならこの方法です。
COUNTIFもSMALLも使わないため、旧Excelで大量データを抽出する場合の本命です。
作業列の番号の考え方は少し難しいですが、一度理解すれば非常に強力です。
なぜCOUNTIFが重いのか
COUNTIFで連番を作るとき、よく以下のような式を使います。
COUNTIF
=COUNTIF($C$2:C2,"営業部")
この式は、下へコピーするほど参照範囲が広がります。
| 行 | 数式 | 確認するセル数 |
| 2 | COUNTIF($C$2:C2,"営業部") | 1 |
| 3 | COUNTIF($C$2:C3,"営業部") | 2 |
| 4 | COUNTIF($C$2:C4,"営業部") | 3 |
| ... | ... | ... |
| 10001 | COUNTIF($C$2:C10001,"営業部") | 10,000 |
1万行では、1 + 2 + 3 + ... + 10,000 で、約5,000万セル分を確認することになります。
このように、COUNTIFの範囲拡張は行数が増えるほど急激に重くなります。
高速化の基本方針：
大量データでは、COUNTIFで毎回数え直すのではなく、以下のどちらかにします。
・対象行だけROWで行番号を出す
・上行参照で前の結果を使う
どちらも各行の判定だけで済むため、COUNTIF方式より軽くなりやすいです。
あいまい検索をする場合も、SEARCH/FINDで各行を判定するだけの方が扱いやすくなります。
大量データでは、COUNTIFで毎回数え直すのではなく、以下のどちらかにします。
・対象行だけROWで行番号を出す
・上行参照で前の結果を使う
どちらも各行の判定だけで済むため、COUNTIF方式より軽くなりやすいです。
あいまい検索をする場合も、SEARCH/FINDで各行を判定するだけの方が扱いやすくなります。
最終的な使い分け
小規模・分かりやすさ
COUNTIF
+ INDEX + MATCH
+ INDEX + MATCH
数百〜数千行程度なら十分使えることがあります。
連番が見えるため、仕組みを説明しやすいです。
あいまい検索向き
対象行だけROW
+ SMALL + INDEX
+ SMALL + INDEX
SEARCH/FINDで条件判定し、対象行だけROW番号を出します。
どの行がヒットしているか見やすく、シンプルです。
軽量化重視
AGGREGATEで行番号
+ INDEX参照
+ INDEX参照
元データ側に作業列を追加したくない場合におすすめです。
作業列が少ないため、ファイルサイズを小さくしやすいです。
速度最優先
上行参照で連番
+ INDEX + MATCH
+ INDEX + MATCH
大量データではこれが本命です。
COUNTIFもSMALLも使わないため、再計算が重くなりにくいです。
FILTER関数が使えるならFILTERが最短
ここまで紹介した方法は、FILTER関数が使えない旧Excel向けの代替手段です。
Excel 2021 / Microsoft 365 が使える場合は、FILTER関数を使うのが最も簡単です。
完全一致で「営業部」だけを抽出する場合は、FILTER関数なら以下の1本で済みます。
完全一致
=FILTER(A2:D1000,B2:B1000="営業部")
部分一致にしたい場合は、FILTER関数でもSEARCHを組み合わせられます。
例えば、B列に「営業」という文字を含む行だけを抽出する場合は以下です。
部分一致
=FILTER(A2:D1000,ISNUMBER(SEARCH("営業",B2:B1000)))
ただし、この記事で紹介した方法は、FILTER関数が使えない旧Excel向けです。
FILTER関数が使える環境では、基本的にはFILTER関数を使うのが最短です。
FILTER関数（2021～）― 条件に合うすべてのデータを動的に一括抽出
基本のAND/OR条件、数値の範囲指定、SEARCHを使った部分一致、CHOOSECOLSとの合わせ技など、FILTER関数の実践パターンを解説しています。