旧ExcelでFILTER関数の代わりに複数抽出する方法
― 分かりやすさと計算負荷を比較して解説
💡 やりたいこと
Excel 2021 / Microsoft 365 では、FILTER関数を使えば条件に合うデータをまとめて抽出できます。
しかし、Excel 2019以前の旧ExcelではFILTER関数が使えません。
この記事では、旧Excelでも VBA不要・Ctrl+Shift+Enter不要 で、FILTER関数のように条件一致データを複数行抽出する方法を解説します。
複数抽出を初めて作る場合は、仕組みが追いやすい 対象行だけROW + SMALL + INDEX を基本としておすすめします。
この記事では、COUNTIF方式から始めて、ROW方式、作業列を減らす方式、参照範囲を抑えた方式まで詳しく比較します。
そこから、COUNTIFをやめる方法、作業列を減らす方法、最後に 上行参照方式 へ順番に改善していきます。
以前よく見かける COUNTIF + SMALL + INDEX は、COUNTIFとSMALLを重ねるだけで実務上のメリットが薄いため、この記事では方式として採用しません。
計算速度はデータ量やブック構成によって変わります。本記事では、一般に計算負荷を抑えやすい構成を「基本形」として紹介します。重い場合は、実際のブックのコピーで比較してください。
先に結論:基本はROW + SMALL + INDEX
対象行だけROW番号を出し、SMALLで上から順に選び、INDEXで値を表示します。3つの役割が見えやすく、COUNTIFも使いません。
連番が見えるため考え方は分かりやすいですが、実務上の強いメリットはありません。
通常は方法2を使います。
+ SMALL + INDEX
COUNTIFを使わず、対象行だけ行番号を出します。
SEARCH/FINDを使った部分一致も分かりやすく作れます。
+ INDEX参照
元データ側の作業列を増やさないため、ファイルサイズを小さくしやすいです。
行番号だけ出して各列はINDEX参照にします。
+ INDEX + MATCH
本記事の比較では負荷を抑えやすい方式ですが、作業列の番号が分かりにくくなります。
方法2で速度が足りない場合に検討します。
数百〜数千行程度なら、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 |
|---|---|---|---|---|
| 位置づけ | 理解用 基本は使わない | 第1候補 通常の基本 | 元データの 作業列が不要 | 第2候補 速度特化の上級版 |
| おすすめ度 | ★☆☆☆☆ 分かりやすさだけ | ★★★★★ 基本のおすすめ | ★★★★☆ 作業列を置けない場合 | ★★★★☆ 高速化する場合 |
| 計算負荷の傾向 | 高め COUNTIFがボトルネックになりやすい | 中程度 SMALLの順位計算が必要 | 中〜低め 行番号だけなら実用的 | 低め 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では比較表だけ横幅を広く使って表示します。スマホや幅の狭い画面では横にスクロールできます。
上行参照 + MATCH > ROW + SMALL > COUNTIF + MATCH
方法4は作業列が直上セルだけを参照し、COUNTIFとSMALLを使わないため、本記事の比較条件では負荷を抑えやすい方式です。
方法2はCOUNTIFを使わないため軽くなりますが、抽出するたびにSMALLが範囲全体から「k番目に小さい数」を選ぶため中速です。
方法1は最後にMATCHを使っていても、元データ側のCOUNTIFが広がる範囲を繰り返し数えるため、全体では遅くなりやすいです。ここでいうCOUNTIFの遅さは、作業列を作る段階の話です。
位置を探すだけ
k番目を順位計算
各行で数え直す
第1候補:作業列に対象行だけが表示され、仕組みを確認しやすいROW + SMALL + INDEX方式。
第2候補:実際に再計算が重い場合の上行参照 + MATCH方式。比較条件では軽くなりましたが、条件外行にも番号が残るため作業列が直感的ではありません。
別の選択肢:元データ側へ作業列を追加できない場合のAGGREGATE方式。
基本は使わない:COUNTIF方式。連番が分かりやすい以外に、方法2より優先する実務上のメリットがありません。
COUNTIFで作業列を作るときに、広がる範囲を各行で何度も数え直します。
その抽出側へSMALLを組み合わせると、今度は結果を1セル出すたびに、作業列全体から「k番目に小さい数」を順位計算します。
つまり、作業列ではCOUNTIFの範囲拡張、抽出側ではSMALLの順位計算という2つの負担が重なります。
この記事の比較条件では、上行参照 + MATCH > ROW + SMALL > COUNTIF + MATCH > COUNTIF + SMALL の順に軽くなる傾向でした。COUNTIF + SMALLは、COUNTIF方式とSMALL方式の負荷が重なるため、この記事では採用していません。
※ AGGREGATE方式は、式を各列へ直接書くか、行番号を1列だけ計算してINDEX参照するかで速度が大きく変わるため、この単純順位とは別に考えます。
理解用・非推奨方法1:COUNTIF + INDEX + MATCH
| 向いている | 仕組みの学習や説明だけ。新しく実務用の式を作る場合は、基本的に方法2を使います。 |
|---|---|
| メリット | ・条件に合う行だけに「1件目、2件目、3件目」と連番が出る ・考え方を目で追いやすい |
| デメリット | ・COUNTIFの範囲拡張が重い ・行数が増えるほど急激に遅くなる ・10万行級では実務上かなり厳しくなることがある |
考え方だけは一番分かりやすい方法です。
COUNTIFで「条件に合う行が何件目か」を作業列に出し、INDEX + MATCHで取り出します。
STEP 1 作業列を左側に追加する
元データの左に作業列を追加します。
作業列A、名前B、部署C、売上D、年齢Eという配置にします。
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のワイルドカードでも書けます。
ワイルドカードで部分一致はできますが、
COUNTIF($C$2:C2,"*営業*") のように範囲が下へ広がる点は変わりません。つまり、部分一致にしてもCOUNTIFの重さは残ります。
STEP 2 INDEX + MATCHで抽出する
作業列に「1件目、2件目、3件目」と表示されるため分かりやすいことだけがメリットです。
同じ作業列1列を使うなら、COUNTIFの範囲拡張がなく、各行を判定するだけの方法2の方が実用的です。新しく作る場合は方法2を選びます。
基本のおすすめ方法2:対象行だけROW + SMALL + INDEX
| 向いている | COUNTIFを避けたい場合、あいまい検索を分かりやすく作りたい場合、対象行だけを見える形にしたい場合 |
|---|---|
| メリット | ・COUNTIFを使わない ・各行の判定だけなので作業列が軽い ・対象行だけROW番号が出るため見た目が分かりやすい ・FIND関数やSEARCH関数で部分一致にも対応しやすい |
| デメリット | ・作業列が必要 ・抽出側でSMALLを使う ・抽出列が多いとSMALLの回数が増える ・速度最優先なら上行参照方式に劣る |
COUNTIFが重いなら、まずCOUNTIFをやめます。
この方法では、作業列に「対象行なら実際の行番号、対象外なら空白」を出します。
STEP 1 完全一致:対象行だけROWを出す
部分一致・あいまい検索:SEARCHまたはFINDを使う
| 行 | 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で抽出する
COUNTIF方式は、下の行へ行くほど参照範囲が広がります。
一方、このROW方式は、各行で「この行が対象かどうか」を判定するだけです。
そのため、作業列側の計算は非常に軽くなります。
SMALLは、抽出結果を1セル表示するたびに、作業列の範囲から「k番目に小さい行番号」を探します。
抽出結果が100行・4列なら、同じ作業列に対するSMALLの順位計算が最大400回発生します。
一方、方法4のMATCH方式は、軽い上行参照で作った連番から目的の番号の位置を探すだけです。
そのため、本記事の比較条件では 上行参照 + MATCH > ROW + SMALL > COUNTIF + MATCH の順に軽くなる傾向でした。COUNTIFの作業列にSMALLまで組み合わせる場合は、両方の負担が重なるため、さらに遅くなりやすくなります。
SMALL方式を軽くするには、
$A:$A ではなく $A$2:$A$1000 のように範囲を絞るか、行番号を1列だけ計算して各列はINDEX参照にします。
元データの作業列不要方法3:AGGREGATEで行番号だけ抽出 + INDEX
| 向いている | 元データ側に作業列を追加したくない場合、ファイルサイズを小さくしたい場合、表の構造を崩したくない場合 |
|---|---|
| メリット | ・元データ側に作業列が不要 ・CSE不要で複数抽出できる ・行番号だけAGGREGATEで出せば計算回数を減らせる ・作業列が少ないためファイルサイズを小さくしやすい ・完全一致もあいまい検索も対応できる |
| デメリット | ・AGGREGATEの数式が長い ・各列に直接AGGREGATEを書くと重い ・速度最優先では上行参照方式に劣る ・抽出側に行番号列は必要になる |
元データ側に作業列を追加したくない場合は、AGGREGATE関数を使います。
ただし、各列に直接AGGREGATEを書くと重くなりやすいため、まず行番号だけを出し、その行番号を各列で参照するのがおすすめです。
AGGREGATEは便利ですが、各列に何度も書くと同じ条件判定を繰り返して重くなります。
そのため、まず1列だけで必要な行番号を出します。
名前・部署・売上・年齢は、その行番号を参照してINDEXで取り出します。
また、元データ側に大量の作業列数式を持たないため、ブックのファイルサイズを小さくしやすいというメリットもあります。
STEP 1 抽出結果の左側に行番号列を作る
例として、元データはA:D、部署はB列にある前提です。
抽出結果側の左にG列を作り、G2セルに以下を入力して下へコピーします。
| G列:抽出行番号 | H列:名前 | I列:部署 | J列:売上 | K列:年齢 |
|---|---|---|---|---|
| 2 | 田中 | 営業部 | 120 | 28 |
| 4 | 鈴木 | 営業部 | 200 | 42 |
| 6 | 伊藤 | 営業部 | 150 | 25 |
| 9 | 中村 | 営業部 | 180 | 33 |
AGGREGATEの最後の引数は「何番目に小さい値を返すか」です。
ROWS($G$2:G2) はG2では1、G3では2、G4では3……と増えます。
STEP 2 行番号を参照して各列を取り出す
G列には、表の中で何番目かではなく、実際のシート行番号 が入ります。
そのため、取り出し側は列全体を参照するとズレません。
このAGGREGATE式は、表の中で何番目かではなく、実際のシート行番号を返します。
そのため、
INDEX(A:A,$G2) のように列全体を参照すればズレません。一方、
INDEX(A2:A1000,$G2) のようにデータ範囲だけを指定すると、G2の値が実際の行番号であるため、1行ずれることがあります。
範囲指定で取り出したい場合:相対番号方式
AGGREGATE方式は速度だけを優先する方法ではありません。
しかし、元データ側に作業列を追加しなくてよいため、ファイルサイズを小さくしやすく、表の構造も崩しにくいです。
低負荷候補・上級者向け方法4:上行参照で連番 + INDEX + MATCH
| 向いている | 大量データ、速度最優先、再計算を軽くしたいブック、10万行級の住所録・顧客リスト・明細データ |
|---|---|
| メリット | ・COUNTIFを使わない ・SMALLも使わない ・各行の計算が軽い ・抽出側もINDEX + MATCHで高速 ・完全一致もあいまい検索も対応できる |
| デメリット | ・作業列が必要 ・条件外行にも番号が残る ・COUNTIF方式より連番の作り方が少し分かりにくい |
最後に、本記事の比較では負荷を抑えやすい方式です。
COUNTIFもSMALLも使わず、作業列はすぐ上のセルだけを参照して連番を作ります。
COUNTIF方式は「何件目か」を数えるので分かりやすいです。
一方、上行参照方式は、条件に合わない行にも直前の番号が残ります。
STEP 1 完全一致で作業列を作る
部分一致・あいまい検索にする場合
| 行 | 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行目、つまり鈴木の行が取得されます。
STEP 2 INDEX + MATCHで抽出する
COUNTIFもSMALLも使わないため参照負荷を抑えやすい一方、条件外行にも番号が残り、作業列の仕組みが分かりにくくなります。
通常は方法2のROW + SMALL + INDEXを使い、それでは速度が足りない大量データの場合に限って方法4を検討するのがおすすめです。
なぜCOUNTIFが重いのか
COUNTIFで連番を作るとき、よく以下のような式を使います。
この式は、下へコピーするほど参照範囲が広がります。
| 行 | 数式 | 確認するセル数 |
| 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で各行を判定するだけの方が扱いやすくなります。
最終的な使い分け
+ INDEX + MATCH
連番が見えるため仕組みは説明しやすいですが、実務上の強いメリットはありません。
新しく作るなら方法2を使います。
+ SMALL + INDEX
SEARCH/FINDで条件判定し、対象行だけROW番号を出します。
どの行がヒットしているか見やすく、通常はこの方式がおすすめです。
+ INDEX参照
元データ側に作業列を追加したくない場合におすすめです。
作業列が少ないため、ファイルサイズを小さくしやすいです。
+ INDEX + MATCH
比較条件では軽くなりましたが、作業列の仕組みが分かりにくい方式です。
方法2では速度が足りない場合に使います。
FILTER関数が使えるならFILTERが最短
ここまで紹介した方法は、FILTER関数が使えない旧Excel向けの代替手段です。
Excel 2021 / Microsoft 365 が使える場合は、FILTER関数を使うのが最も簡単です。
完全一致で「営業部」だけを抽出する場合は、FILTER関数なら以下の1本で済みます。
部分一致にしたい場合は、FILTER関数でもSEARCHを組み合わせられます。
例えば、B列に「営業」という文字を含む行だけを抽出する場合は以下です。
ただし、この記事で紹介した方法は、FILTER関数が使えない旧Excel向けです。
FILTER関数が使える環境では、基本的にはFILTER関数を使うのが最短です。