Excel 2019までのExcelでFILTER関数風の数式を作る
はじめに
上掲の記事は、FILTER関数が実装されたExcel 2021より前のバージョンで、FILTER関数のような機能を数式で作るのを紹介したものです。
この記事を読んでみた所、私がその内に書こうとした事と重なっている部分があったので、ちょっとその内容も含めて、同じテーマ、つまり、
Excel 2019までのExcelで、FILTER関数のような機能を持つ数式を作成する
事を試みます。今回は簡潔に行きましょう。FILTER関数などの扱いに慣れているのを前提します。
FILTER関数を使わずに、同じような機能を数式で実現する
成績表の準備
まず、リンク先と同じように、フィルターの元になる表を用意します。いつものごとく、テーブルで作ります。テーブル名はstudent_scoresとします。
左端の列名は変えてあります。
条件テーブルの準備
各教科の成績を判定するための条件を記入したテーブルを用意します。以前に書いた記事で紹介したように、条件をCOUNTIF関数などで判定する場合、比較演算の式を外に置いて参照させる事で、見やすくなり、また変更も容易になります。
成績表の右に、フィルターするテーブルを置きたいので、条件テーブルはO列に作りました。あいだの列は非表示にしています。もちろん、並びは好きなようで構いません。conditionテーブルとします。
条件判定列の追加
今回は、国語・算数・英語の各教科が同時に条件を満たした行をフィルターします。したがって、COUNTIF関数では無く、COUNTIFS関数を使います。COUNTIFS関数は、
同じ大きさの範囲複数箇所について
それぞれの範囲に条件を設定し
条件を全て満たしたセルの個数を数える
機能を持っています。いまは、列を追加して、一人の生徒の各教科を判定し、それをANDで結んで判定します。ですから、
同じ行の国語セルが条件を満たす
同じ行の算数セルが条件を満たす
同じ行の英語セルが条件を満たす
この3条件を総合します。各範囲は1セルなので、COUNTIFS関数が返すのは、0か1です。先ほども書いたように、全部満たすか否かしか判定させないので、この3条件をCOUNTIFS関数でまとめて判定させれば事足ります。数式は次のように書きます。
=COUNTIFS(
[@国語],condition[国語条件],
[@算数],condition[算数条件],
[@英語],condition[英語条件]
)
列名は、AND条件としてあります。各条件は冒頭の紹介記事に合わせたので、各教科が70点以上であるかを判定しています。そして、全てが70点以上であれば、1を返します。COUNTIFS関数の、検索条件範囲の大きさが1セルだからです。
抽出番号列の追加
いま追加した条件判定列で、フィルターをかける際に抽出すべき行に目印、つまり1をつける事が出来ました。FILTER関数を使わずに同様の機能を実現する場合に重要なのは、ここからです。FILTER関数は、条件判定列で出来たような数値あるいはブーリアンの集まりを配列として渡せば、所望の範囲を自動的に絞り込めますが、FILTER関数が使えない場合にどうするか、です。そのためには、
抽出する行に連番をつける
ようにします。条件列の右に更に、抽出番号、つまり、
抽出する行として何番目か
の情報を付加する列を追加します。数式は次の通りです。
=IF(
[@AND条件]=1,
SUM(student_scores[[#見出し],[AND条件]]:[@AND条件]),
0
)
言葉で書きます。
もし
隣にある条件セルが1なら
見出し行からこの行までの
条件セルの値を足し合わす
こうです。
隣の条件判定セルの値が0なら、それは抽出しない、FILTER関数的には不可視行なので無視します。いっぽう、1である場合は、それがいくつめの1であるかを数えます。隣のセルは0か1しか無いので、数えるのは総和、つまりSUM関数の結果と同じです。ポイントは
student_scores[[#見出し],[AND条件]]:[@AND条件]ここ。student_scores[[#見出し],[AND条件]]は、条件判定の見出しセルで、[@AND条件]は、今いる行のAND条件のセル（フィールド）です。このように書くと、
見出しセルが固定されたまま、動的に参照範囲が変化
します。冒頭紹介記事で同じような事をしていますがそこでは、片側を絶対参照にして、もう片側を相対参照にしています。対して上記数式は、両方とも構造化参照です。そして、見出しセルの参照は、絶対参照として機能します。これは極めて便利です。参照元をトレースした画像を示します。
このように、行が下がるたびに、参照される範囲が広がります。こうすれば、
いま見ている行までに、隣の列の1が何個あるか
が数えられます。これはつまり、
いまいる行は何番目の抽出行か（0なら抽出しない）
の情報を与えます。
数える所でIF関数を使うとごちゃごちゃする、と感ずるのであれば、
=SUM(student_scores[[#見出し],[AND条件]]:[@AND条件])*[@AND条件]このようにも書けます。*[@AND条件]がポイント。先ほどは、IF関数を使って、1の時だけ足すようにしました。そうしないと、条件列が0の時も、総和が残ったままになるからです。だから、IFを使わずに単純にSUM関数で足したら、
こうなります。これを、1の時だけ足すようにしたのがIF関数でしたが、これを使わなくとも、いま見ているのが抽出不要の行であれば、条件判定列が0で、抽出行なら1だから、総和した数値にそれをかければ、
条件判定が0→0
条件判定が1→総和がそのまま
になって、結局、IF関数を使ったのと同じになる寸法です。
どちらを使うかはお好みですが、数式だけを見て何をしているかが解るのは、IF関数を使うほうです。
抽出用テーブルを作る
いよいよ、フィルターを施したテーブルを作成します。まず、番号を担う列を作ります。テーブル名はextraction_tableとします。
この番号は言うまでも無く、何番目に抽出した行（つまり生徒のデータ）かを示します。そして当然、この番号は、成績表の抽出番号と対応しています。これを合わせて抽出表を作っていく算段です。
ちなみに、抽出表での番号は、別に直打ちでも構いませんが、ここでは数式で作っています。
=ROW()-ROW(extraction_table[#見出し])連番作成の常套手段。つまり、
いまいる行の行番号を取得し
基準となる場所の行番号を取得して
それを引く
ようにします。今はテーブルで構造化参照だから、
いまいる行の行番号を取得し
見出し行の行番号を取得して
それを引く
のが明白です。これは、テーブルの移動に何の影響も受けません。
データの抽出
後は、実際にデータを抽出します。ではどうしましょうか。一つ考えられるのは、No.列の番号を、成績表に作った抽出番号列からVLOOKUP関数で探し、それぞれの値を引っ張ってくる事です。
しかしそれでは、
成績表の抽出番号列を左端に置く
生徒IDや点数が何列目かを指定する
などの必要があります。いまはExcel 2021より前を想定しているので、XLOOKUPは使えません。したがって、
検索と抽出を分離
します（ここが、その内に書こうと思っている所）。
抽出行列の追加
発想しやすいのは、VLOOKUP関数で元の表を指定して、ターゲット（ここではNo.）を検索して値を持ってくる、というものですが、上で書いたように、検索と抽出を分離します。まず、列を作り、No.の番号が、成績表の抽出番号列の何番目にあるかを検索させます。
=IFNA(MATCH([@[No.]],student_scores[抽出番号],0),-1)
抽出行という名前の列、なので、抽出行列となっていますが、これが行列と紛らわしいなら、抽出行番号列などと名付けても構いません。この数式を言葉で書きます。
MATCH([@[No.]],student_scores[抽出番号],0)↑これは、
いま見ている行のNo.列にあるセルの値を
成績表の抽出番号列に探しに行って
完全に一致したら何番目であるかを返す
このような手順です。ただし、見つからない場合はMATCH関数は#N/Aエラーを返すので、IFNA関数でくるんで、#N/Aの場合には-1を返すようにします。
=IFNA(
MATCH([@[No.]],student_scores[抽出番号],0),
-1)エラー時に返すのは文字列等でも構いませんが、MATCH関数は数値を求めるものである事や、他の言語で、存在しない場合に-1を返す関数があるので、それに合わせました。なお、後述しますが、0は返さないようにします。
この列の値は、上から
8
9
13
16
となっています。成績表の行番号と一致していますね。
※ワークシートの行番号では無く、テーブルのデータ内での番号。なぜならMATCH関数の検索範囲を成績表のデータ部にしているから
データの抽出
ついに、データを抽出します。検索と抽出を分離しましたので、ここでは抽出のみに専念出来ます。生徒IDをまず抽出します。これをすれば、教科の点数は全く同じように出来ます。
=IF(
[@抽出行]<>-1,
INDEX(student_scores[生徒ID],[@抽出行]),
condition[なし]
)INDEX関数を使います。INDEX関数は、前の記事でも書いたように、ある範囲について、行番号と列番号を指定して、その交差したセルの値を返すものでした。
INDEX関数には便利な性質があります。それは、
幅が1セルの範囲であれば、行番号か列番号のどちらかのみ指定すれば良い
というものです。今は、成績表の生徒IDを抽出しようとしていますので、指定する範囲は、生徒ID列のデータ部分で構いません。それの幅は1セルです。ですから、INDEX関数では単純に、何番目を抽出するかだけ入力すれば良いのです。それが、いま示した数式の
INDEX(student_scores[生徒ID],[@抽出行])この部分です。[@抽出行]は、隣で取得した、成績表での行番号ですから、それを、成績表の生徒ID列に適用してあげれば良いのです。
実際に作った数式は、INDEX関数をIF関数でくるんでいます。隣で取得した番号は、成績表で抽出する行の番号ですから、抽出する行が無くなれば-1を返します。INDEX関数で-1番目を指定する事は出来ませんから、それをIF関数で判定しています。
なお、ここで、該当が無いからといって、0を指定してはいけません。なぜなら、INDEX関数の引数で0を入れると、第1引数で指定した（範囲に基づいた）配列をそのまま返すからです。だから-1を返すようにしたわけです。
IF関数では、-1で無ければ抽出行があるのを意味しますので、その番号を取るようにINDEX関数で指定します。いっぽう、-1であった場合は、無い旨を示す必要があります。直接に文字列を指定しても良いですが、そうすれば、生徒IDや各教科の点数で、それぞれリテラル（直打ち）で指定しなくてはなりません。そこで、最初のほうで作った条件列に、見つからない場合に表示する文字列を入れておいて、そこを参照させるのです。
下図が、抽出データの列を追加したものです。
後は、同じように教科ごとの点数の列も追加すれば、完成です。
重要なポイント
重要なのは、先ほどから言っているように
検索と抽出を分離
する事です。それを担うのはそれぞれ、MATCH関数とINDEX関数であり、その組み合わせは知られた所ですが、今は、検索するキーは同じで、同じ場所から探すわけですので、MATCHとINDEXの組み合わせを、いちいち繰り返して書く必要はありません。それをすると、MATCH関数での検索が4列分おこなわれますが、不要ですよね。だから、MATCH関数での検索を作業列に入れて一括し、抽出はINDEX関数のみでやらせているのです。
紹介記事との違い
冒頭の紹介記事では、
COUNTIFS関数の引数に動的参照（片側固定の相対参照）を使っている
所が、私の方法と違っています。COUNTIFS関数は、指定した範囲で条件を全て満たしたセルの数を返します。だから、リンク先での表は、検索番号列が、0が続き、
1
2
2
2
2
3
3
3
のように続いて行きます。そして、これをVLOOKUP関数で見つけます。VLOOKUP関数は、最初に見つかった所を返すから、動作としては目的通りに働きますが、実際にやりたい手順をそのまま数式と表示に反映させる、という観点からは、改良の余地があるという事です。
ここでおこなったのは、
COUNTIFS関数での検索範囲を1セルにする：返すのが0か1になる
抽出番号は、同じ数値が重複しないようにする：表を見た時の直感的理解に機能を合わせる
検索と抽出を分離する：検索処理の重複を無くし、数式をシンプルにする
条件判定および、該当なしメッセージを別テーブルに入れる：管理や変更をしやすくする
このようなものです。FILTER関数とCOUNTIF関数の組み合わせの記事で解説したように、COUNTIF関数は、あたかもTRUEとFALSEを返すように使えますが、それをCOUNTIFS関数にも適用しました。これを作業列でおこなう事で、条件を満たすか否かを判定しているのが明確になります。また、抽出番号はそのまま、何番目に抽出するかを示します。ここに重複した数値があると、そこを漏れ無く抽出するかのように見えてしまいます。同じ数値はタイに見えるかも知れません。それを防ぐべく、0以外の数値は一回しか出ないようにしたのです。重要なのは、表示されているデータや数式が何をしているかを、出来るだけ解りやすくする事です。