本記事では、Excel 2021を使用して検証および画像の作成を行っています。
はじめに：FILTER関数が使えなかった、あの頃へ…
Excelでのデータ抽出といえば、今やFILTER関数の独壇場です。
指定した条件に合うデータを、スピル機能で一瞬にして抜き出してくれます。
そのシンプルさと強力さから、もはや無くてはならない存在ですよね。
「でも、もし…もしも、このFILTER関数が使えなかったとしたら？」
そう、Excel 2019以前のバージョンや、特定の環境ではFILTER関数は使えません。
そんな時、先人たちはどのようにして同じ機能を実現していたのでしょうか。
今回の記事は、そんな「もしも」の世界を探求する、思考のパズルです。
FILTER関数を使わずに、その挙動を再現するテクニックに挑戦します。
目的は、関数の組み合わせや、数式の組み立て方の「引き出し」を増やすこと！
今回は、3つの代替アプローチを紹介します。
- いにしえの配列数式：古くから伝わる、知恵と工夫の結晶。
- 集計の鬼、降臨：より安定し、強力になった現代的な代替案。
- 驚愕の文字列操作ハック：本来の目的とは全く違う使い方で実現する、まさに裏ワザ。
この関数パズルを通して、Excelの奥深い世界の扉を、また一つ開いてみませんか。
今回のミッション
現在の状態： まず、以下のような「関数リスト」がシートにある状態です。
A列に関数名、B列に関数の種類が入力されています。
目指すゴール： 上記のリストから、B列の種類が「数学」の関数を数式一つで全て抽出します。
そして、別のセルに一覧で表示させます。
FILTER関数（現代の王道）
まずは比較対象として、現代の王道であるFILTER関数の使い方を見ておきましょう。
これが使える環境なら、迷わずこれ一択です。
数式
抽出結果を表示したいセル（例: D2セル）に、以下の数式を入力します。
=FILTER(A2:A8,B2:B8=”数学”)
解説
なんとシンプル！
引数はたったの2つ（基本は）。
- 第1引数 A2:A8：抽出したいデータが含まれる範囲（配列）を指定します。今回は関数名ですね。
- 第2引数 B2:B8=”数学”：抽出するための条件を指定します。B列が”数学”である行をTRUE、それ以外をFALSEとする配列を作ります。
これだけで、条件がTRUEの行に対応するデータだけが、自動的に複数のセルに展開（スピル）されます。
直感的で、誰が見ても何をしたいのかが一目瞭然ですね。
さて、ここからが本番です。
この便利なFILTER関数を使わずに、この結果を再現していきます。
アプローチ1：INDEX + SMALL + IF（いにしえの配列数式）
考えかた
最初に挑むのは、古くからExcelユーザーの間で受け継がれてきた「いにしえの技」。
INDEX、SMALL、IFという3つの関数を組み合わせた配列数式です。
そのロジックは、まるで手作業を関数に置き換えたかのようです。
- まず、リストの上から順にチェックします。そして条件に合う行の「行番号」だけをリストアップします。
- 次に、集めた行番号を「小さい順」に並べ替えます。（1番目、2番目、3番目…と）
- 最後に、その順番に従って、元のリストからデータを「一つずつ取り出す」のです。
この一連の流れを、たった一つの数式で実現します。
少し複雑ですが、分解すれば必ず理解できます。
数式と解説
抽出結果を表示したいセル（例: D2セル）に、以下の数式を入力します。
=INDEX(A2:A8,SMALL(IF(B2:B8=”数学”,ROW(A2:A8)-ROW(A1)),ROW(INDIRECT(“A1:A”&COUNTIF(B2:B8,”数学”)))))
【重要】Excel 2019以前のバージョンをお使いの方へ
この数式は「配列数式」です。入力後、単にEnterキーを押すだけでは正しく計算されません。
数式を入力したら、Ctrl + Shift + Enter を同時に押して確定してください。
成功すると、数式が自動的に波括弧 { } で囲まれます。
うわっ、長い！
まるで暗号のようですね（笑）
でも、大丈夫。
内側から一つずつ、皮をむくように解読していきましょう。
この数式は、大きく分けて5つの関数が連携して動いています。
それぞれの関数の役割を先に見ておきましょう。
- IF関数: 条件を判定し、真の場合と偽の場合で処理を分岐させます。
- ROW関数: セルの行番号を返します。配列数式の中では、連続した行番号の配列を作ります。
- COUNTIF関数: 指定した範囲の中から、条件に合うセルの個数を数えます。
- INDIRECT関数: 文字列を、Excelが認識できる「セル参照」に変換します。
- SMALL関数: 数値の集まりの中から、指定した順番（K番目）に小さい値を返します。
- INDEX関数: 表や範囲の中から、指定した行と列の位置にある値を取り出します。
では、これらの関数がどのように連携しているのか、数式の内側から見ていきます。
Step 1: IF(B2:B8=”数学”,ROW(A2:A8)-ROW(A1)) – 条件に合う行番号の配列を作る
数式の心臓部の一つ目です。
ここで行っているのは、「もしB列が”数学”だったら、そのデータが範囲内の何番目にあるか、という番号を返す」という処理です。
まず、ROW(A2:A8)-ROW(A1)の部分を見てみましょう。
ROW(A2:A8)は、A2からA8までの各セルの行番号を配列として返します。結果は {2;3;4;5;6;7;8} となります。
そこからROW(A1)、つまり 1 を引いています。
{2;3;4;5;6;7;8} - 1 という計算が行われ、{1;2;3;4;5;6;7} という配列が作られます。
これは、データ範囲の「1番目、2番目、3番目…」という相対的な位置を示す連番を作るための常套句です。
次に、IF関数がこの連番を使って条件分岐をします。
B2:B8="数学" の条件判定により、”数学”である行はTRUE、そうでない行はFALSEとなります。
このIF文全体が実行されると、Excelのメモリ上ではこんな配列が出来上がります。
見ての通り、”数学”に該当する2番目、5番目、6番目のデータだけが、その位置を示す番号になり、他はFALSEになっていますね。
Step 2: ROW(INDIRECT(“A1:A”&COUNTIF(B2:B8,”数学”))) – 抽出件数分の連番を生成
次が、この数式で最も巧妙な部分です。
これは、SMALL関数に「何番目に小さい値を取り出すか？」を指示するための、1, 2, 3…という連番を動的に作り出す部分です。
ここでも内側から見ていきましょう。
COUNTIF(B2:B8,"数学")
まず、COUNTIF関数が、B2:B8の範囲に”数学“が全部で何件あるかを数えます。今回の例では「3」件ですね。
"A1:A" & COUNTIF(...)
次に、文字列の “A1:A” と先ほど数えた「3」を & で連結します。結果、"A1:A3" という一つの文字列が出来上がります。
INDIRECT("A1:A3")
INDIRECT関数が、このただの文字列だった “A1:A3” を、Excelにとっての「本物のセル範囲 A1:A3」として認識させます。
ROW(A1:A3)
最後に、ROW関数がそのセル範囲の行番号を取得します。A1:A3の行番号なので、結果は {1;2;3} という、1から始まる3つ分の連番配列になります。
これは、SEQUENCE関数がなかった時代に、抽出したい件数に合わせて動的に連番を生成するための、まさに「いにしえの知恵」と呼ぶべきテクニックです。
Step 3: SMALL(IFの結果, 連番) と INDEX(…) – 最終組み立て
さあ、役者は揃いました。
SMALL関数が、Step1で作った配列 {FALSE;2;FALSE;FALSE;5;6; から、Step2で作った連番 FALSE}{1;2;3} を使って値を取り出します。
SMALL関数は、配列の中の数値を小さい順に返す関数で、文字列や論理値（FALSE）は無視するという特性があります。
- 1番目に小さい数値は → 2
- 2番目に小さい数値は → 5
- 3番目に小さい数値は → 6
最後に、INDEX関数が、この {2;5;6} という順番に従って、抽出元の範囲 A2:A8 から関数名を一つずつ取り出します。
INDEX関数は、範囲の中から「何番目」のデータをください、と指示する関数です。
アプローチ2：INDEX + AGGREGATE（集計の鬼、降臨）
考えかた
「Ctrl+Shift+Enterって、忘れそうだし面倒…
もう少し安定した、現代的な方法はないの？」
そんな声にお応えするのが、このアプローチです。
ここで登場するのが、当ブログ初登場の「集計の鬼」、AGGREGATE関数です。
この関数は、その名の通り集計のための万能選手です。
合計、平均、個数など19種類もの集計ができます。
さらに、エラー値や非表示行を無視するなど、多彩なオプションを兼ね備えています。
この強力なオプション機能を利用して、Ctrl+Shift+Enterを使わずに配列を処理します。
数式と解説
D2セルに、以下の数式を入力します。
=INDEX(A2:A8,AGGREGATE(15,6,(ROW(A2:A8)-ROW(A1))/(B2:B8=”数学”),ROW(INDIRECT(“A1:A”&COUNTIF(B2:B8,”数学”)))))
お気づきでしょうか。
数式の構造は、先ほどのSMALLの部分がAGGREGATEに変わっただけで、ほとんど同じです。
しかし、その中身の動きが全く異なります。
ここでも、まずAGGREGATE関数の役割を説明します。
AGGREGATE(集計方法, オプション, 配列, [順位]) という書式で使います。
今回は、この関数の「エラーを無視する」という強力なオプションを最大限に活用します。
Step 1: (ROW(A2:A8)-ROW(A1))/(B2:B8=”数学”) – 割り算でエラーを意図的に作る
この数式の最大のポイントがここです。
先ほどのIF文の代わりに、なんと「割り算」を使います。
まず、分母となる (B2:B8="数学") の部分を見てみましょう。
これは、B列の各セルが”数学“かどうかを判定し、{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE} という論理値の配列を返します。
Excelでは、計算式の中で論理値を使うと、TRUEは 1、FALSEは 0 として扱われます。
一方、分子の (ROW(A2:A8)-ROW(A1)) は、先ほどと同じく {1;2;3;4;5;6;7} という連番です。
つまり、Excelのメモリ上では以下の計算が行われています。
{1/0; 2/1; 3/0; 4/0; 5/1; 6/1; 7/0} → {#DIV/0!; 2; #DIV/0!; #DIV/0!; 5; 6; #DIV/0!}
そうです。
条件に合わない行（FALSE= 0）を、意図的に「0で割るエラー（#DIV/0!）」に変換しているのです。
Step 2: AGGREGATE(15, 6, …) – エラーを無視して小さい順に並べる
ここで、集計の鬼 AGGREGATE の真価が発揮されます。
- 第1引数 15：これは「SMALL」と同じ機能、つまり小さい順に値を取り出す、という指示です。
- 第2引数 6：これがキモ！「エラー値を無視する」という最強のオプションです。
このオプション 6 のおかげで、Step1で意図的に大量発生させた #DIV/0! エラーは全て無視されます。
そして、エラーを無視した結果残った数値 {2; 5; 6} の中から、小さい順に値を取り出してくれます。
残りの部分はアプローチ2と全く同じ動きです。
この方法なら、Ctrl+Shift+Enterは不要です。
いにしえの技を、より安定して強力にした現代的なアプローチと言えるでしょう。
アプローチ3：FILTERXML（驚愕の文字列操作ハック）
考えかた
さあ、いよいよ本日のメインディッシュです。
これから紹介する FILTERXML関数 を使った方法は、度肝を抜かれること間違いなし。
データ抽出とは全く関係のない、「文字列操作」と「データ形式の偽装」を駆使します。
まさに驚愕のハックです。
「XMLって…ウェブサイトとかで使う、あの専門的なやつでしょ？
なんでそれがExcelのデータ抽出に…？」
その疑問、ごもっともです。
このアプローチの面白さは、まさにその「ありえなさ」にあります。
数式と解説
D2セルに、以下の数式を入力します。
=FILTERXML(“<t><s>”&TEXTJOIN(“</s><s>”,TRUE,IF(B2:B8=”数学”,A2:A8,””))&”</s></t>”,”//s”)
もはや呪文ですね（笑）。
しかし、これも一つずつ分解すれば、驚くほど合理的なロジックで成り立っていることがわかります。
本来、<t>や<s>などは使うべきでありません。数式を短くするために意図的に使用しています。
そもそもFILTERXML関数とは？
この関数は、その名の通り「XML形式のデータから、指定した情報を抽出する」ための専門家です。
XMLとは、データを意味のわかるタグで囲んで構造化した、メモ帳のようなものです。
例えば、以下のようなXMLデータがあったとします。
<items>
<item category=”文房具”>ペン</item>
<item category=”家電”>マウス</item>
</items>
この中から「itemというタグの中身だけ全部抜き出して！」といった命令を実行するのが、FILTERXMLの本来の仕事です。
では、あの呪文のような数式が、内部で何をしているのか見ていきましょう。
Step 1: IF(B2:B8="数学",A2:A8,"")
これはもうお馴染みですね。
条件に合う関数名だけの配列を作ります。
結果： {"";"SUM";"";"";"INT";"RAND";""}
Step 2: TEXTJOIN("</s><s>",TRUE, IFの結果)
次に、TEXTJOIN関数が登場します。
これは、配列の各要素を、指定した区切り文字で連結して、一つの長い文字列にする関数です。
TEXTJOIN(区切り文字, 空のセルを無視するか, 連結したい配列) という書式で使います。
第二引数がTRUEなので、Step1でできた配列の中の空白(“”)は無視されます。
ここで注目すべきは、区切り文字に </s><s> という、なんとも奇妙な文字列を使っている点です。
この結果、Excelのメモリ上には以下のような、ながーい文字列が一つ生成されます。
結果： "SUM</s><s>INT</s><s>RAND"
Step 3: "<t><s>" & ... & "</s></t>" – XML形式への「偽装」
「なぜ、あんな変な区切り文字を…？」
その答えが、このステップにあります。
先ほど作った長い文字列の前後に、さらに <t><s> と </s></t> というタグを付け足します。
すると、どうでしょう。
結果："<t><s>SUM</s><s>INT</s><s>RAND</s></t>"
見てください。
ただの関数名のリストだったものが、完全に有効なXMLデータ構造に「偽装」されたのです。
Step 4: FILTERXML(偽装したXML, "//s") – 偽装データから情報抽出
最後の仕上げです。
専門家である FILTERXML関数が、この偽装XMLデータを読み込みます。
第2引数の "//s" は、XPathと呼ばれる問い合わせ言語です。
これは「このデータ構造の中にある、全ての s タグの要素を持ってきなさい」という命令です。
命令を受けたFILTERXMLは、律儀に <s> と </s> で囲まれた要素を全て抜き出します。
つまり「SUM」「INT」「RAND」です。
そして、それらをスピル機能でセルに展開します。
…見事に、FILTER関数と同じ結果が得られました。
関数の本来の目的を逆手に取ったハックですね。
まとめ
今回は、「FILTER関数なしでその挙動を再現する」という、少しマニアックな関数パズルに挑戦しました。
- いにしえの配列数式（INDEX+SMALL+IF）：先人たちの知恵と工夫が詰まった、ロジックの塊。
- 集計の鬼（INDEX+AGGREGATE）：いにしえの技を、より安定かつ強力にした現代の正攻法。
- 驚愕のハック（FILTERXML）：全く違う分野の関数を「偽装」して利用する、発想の転換が生んだ裏ワザ。
これらのテクニックを知ることで、改めてFILTER関数のシンプルさと偉大さを実感できたのではないでしょうか。
もちろん、実務で使うなら素直にFILTER関数が一番です（笑）。
今回紹介した代替案、特にFILTERXMLハックなどは、他の人が見たときに解読が非常に困難です。
ですが、こうした関数パズルに取り組むことは、単なる「暇つぶし」ではありません。
関数の本質的な動作への理解を深め、数式を組み立てる際の「思考の引き出し」を格段に増やしてくれます。
普段何気なく使っている関数も、組み合わせや発想次第で、思いもよらない力を発揮するのです。
今回の挑戦が、みなさんのExcelライフをより豊かにする、知的な刺激となれば嬉しいです。
