FILTER関数の突っ込んだ話

2024年7月10日 21:56

はじめに

ご注意

この記事は初学者向けではありません。基本的な数式・関数の仕組みや、引数と戻り値、行・列などの用語を把握しており、テーブルと構造化参照を理解しているのが前提です。

流れ

以前、Excelのワークシート関数であるFILTER関数について詳細に説明しました。

ここでは、FILTER関数がどのような機能を持つか、それぞれの引数がどのような役割を持つか、などを細かく書きました。
特に、第2引数が、真偽値か数値を要素とした配列である事を強調しました。多くの解説で、第2引数を条件と書いていますが、あくまでそれは、比較演算の数式が結果的に配列になるからそう出来るという事であって、正確な引数は配列です。
これを把握しておかないと、上記記事でも説明したような、作業列に数値を入れておき第2引数でその範囲を参照させる、などという用法が理解しにくいですし、その用法を理解していて初めて、条件を表す数式を直接に引数に入れてもFILTER関数が機能する事の、詳しいしくみが頭に描き出せるわけです。

本記事では、FILTER関数について、更に詳しく検討します。

追記：2024年7月19日

補足説明の記事を書きました。

FILTER関数の詳細な挙動

おさらい

例のごとく、寿司食べた表を使います。デザインを少し変更してあります。

前の記事では、FILTER関数を使って、このテーブルをネタなどでフィルターした新たな表を作ったのでした。

第1引数には左のテーブルのデータ部を入れ、第2引数には、比較演算の数式として、皿数列のデータ部と3を比較しています。ここが、よくFILTER関数の引数として紹介される、条件です。
この比較演算数式（sushi_tabeta[皿数]=3）は配列を返すので、その結果がFILTER関数の引数として入力され、最終的に、
第2引数に入力された配列でTRUEに位置する行が可視となった配列
が生成されて、スピルして表となる、という寸法です。

上図を見れば明らかなように、あくまで引数自体は、真偽あるいは数値の配列であって、比較演算はそれを生成するための数式であるのが重要なのでした。

可視/不可視を指定する配列引数

いまは、第1引数で参照した範囲に対する行（縦）方向の可視条件を、第2引数で指定しました。ここは、列（横）方向の配列でも構いません。
実験のために、新しくテーブルを作ります。

左テーブルの下に追加しました。テーブル名は、col_visibleとしてあります。
先ほど作成したFILTER関数の第2引数として、いま作ったテーブルのデータ部を参照させてみましょう。

すると、列方向でフィルターされました。3列の表になっていますね。このフィルターの可視状態は、左下で作成したテーブルの真偽値と一致しています。1・2・5列がTRUEで可視列です。
FILTER関数の数式を確認しましょう。

=FILTER(sushi_tabeta,col_visible)

第2引数に、左下のテーブル名col_visibleが入れてあります。テーブル名だけで名前参照すると、テーブルのデータ部が参照されます。ここを参照する事で、横方向すなわち列方向にフィルターがかかるわけです。
行方向のフィルターの場合、見出し部分は参照テーブルの見出しをそのまま参照させてスピルさせましたが、列方向でデータ部にだけフィルターをかけると、見出しがはみ出してしまいます。ですので、見出し部にもFILTER関数を入れています。

もちろん、見出し含めたテーブル全体をフィルターしても構いません。

行方向（縦）と列方向（横）

いまは列方向（横方向）にフィルターするために、左下テーブルにある横方向のデータ部を参照させました。いっぽう、よく説明される基本のやりかたでは、FILTER関数の引数として比較演算の数式が用いられ、それは行方向、つまり縦方向のフィルターとして機能します。この違いは何でしょうか。

行方向でフィルターした時の数式を再掲します。

それぞれの引数について、等式が表示され、右側には波括弧（ブレース）で囲まれたものがあります。これが配列定数です。これまで説明したように、FILTER関数の引数は配列ですが、ここに比較演算数式を入れた場合には、その結果が引数として入力されます。それが右側の配列定数であるわけです。
第2引数の配列に注目すると、真偽値、つまりTRUEとFALSEが一つずつセミコロンで区切られています。ここがポイントです。Excelにおいて、
配列定数のセミコロンは行区切りを表す
のです。ですから、入力されている{FALSE;FALSE;TRUE;FALSE;FALSE…}なる配列定数は、n×1の行方向の配列を表現しています。比較演算の数式は

sushi_tabeta[皿数]=3

と書きましたが、この左辺は列のデータ部、つまり行方向の範囲です。これに3を比較しているので、結果が行方向の配列、n×1の配列として返ってきます。
この事を明確にするために、数式として直接、配列定数を入れてみます。

このように、セミコロンで行が区切られるので、結果は縦方向、下側にスピルされます。セミコロンで改行して行くのですね。
次に、列方向にフィルターした場合の引数を見てみましょう。

これまでの流れから、
配列定数のカンマは列区切りを表す
事が容易に想像出来るでしょう。この場合、1×mの配列定数が返って来て、それがFILTER関数の引数として入力されたのです。ここから、FILTER関数でフィルターをかけたい範囲が、n（行）×m（列）であるとすれば、第2引数は、

n×1の配列定数⇒行方向のフィルターとして機能
1×mの配列定数⇒列方向のフィルターとして機能

であるのが解ります。前の記事で、第2引数は
行の可視状態のスイッチ
を表していると言いましたが、スイッチとなる配列定数には方向があるというわけです。

行と列の組み合わせ

配列において、カンマが列を区切り、セミコロンが行を区切るのでした。であれば、これを組み合わせれば、行列が表現出来ます。やってみましょう。

ネタが5種類、ネタ名と単価を組み合わせた表を、1つの配列定数で表現しました。結果はもちろん配列ですので、それがスピルされて表を形成します。数式は

={"うに",290;"中トロ",210;"ぶり",150;"いくら",290;"サーモン",150}

このようです。名前と価格をカンマで区切り、行をセミコロンで区切っています。これは5×2の2次元の配列定数を表しています。
ここでピンと来たかたがあるかも知れません。そうです。FILTER関数の第1引数です。通常ここは、フィルターをかけたいセル範囲を参照させますが、この部分の引数を改めて見てみます。

等式の右側を見れば判るように、そもそも
第1引数が配列
なのです。普通は範囲を参照させるので、意識することはほとんどありませんが、実は
範囲を参照させた結果として配列が返り、それがFILTER関数の引数として入力されていた
のです。実際、Microsoftの説明には、第1引数は配列やarrayと書いてあります。arrayは配列を表します。
これが解れば、FILTER関数は配列定数だけで構成出来るのが想像出来るでしょう。

2つの引数を直接、配列定数にしています。数式は

=FILTER(
{"うに",290;"中トロ",210;"ぶり",150;"いくら",290;"サーモン",150},
{TRUE;TRUE;FALSE;FALSE;TRUE}
)

このようです。第1引数で2次元配列を入れ、第2引数で、行方向の可視状態を定義したのです。

もちろん、FILTER関数で直接に配列定数を入れる事に、実用性は全くありません。重要なのは、セル範囲やテーブルを指定して、フィルターの条件を数式や別のセル範囲で生成する所だからです。しかし、そもそもFILTER関数なる関数がどのような構造を持っているか、を知っていて損はありません。これを知っていれば、より詳細に、関数の挙動が頭の中で描けるようになるはずです。

条件

何度か言及しているように、FILTER関数を解説する多くでは、第2引数を、条件であると説明しています。しかし、ここまで見て来たように、実際の引数は、真偽値か数値からなる配列定数です。そこに条件を表現する数式を入れても機能するのは、その数式が配列を返すからです。

FILTER関数の用法の基本は、

sushi_tabeta[皿数]=3

このように、抽出したい条件、ここでは3と、列データとを比較する数式を入れるものです。列のデータ部のセル数は、データ部の行数に等しいですから、それと3を比較した場合、TRUEとFALSEからなる、要素数（サイズ）が行数の配列定数が返ります。前の記事でも説明したように、上の数式を単独で入力すれば、返って来るのは配列となるので、結果的にスピルされます。

よくあるFILTER関数の説明では、この配列を返す数式を、直接に関数の引数として投入しています。この配列があたかも、行または列の可視状態を制御するスイッチのような役割を担っているというのも、前の記事で紹介した通りです。

複数条件

関数の応用として、第2引数に、複数条件の組み合わせを入れてフィルターする、というものが紹介されます。

上図は、ネタがまぐろで皿数が3であるのを両方満たした行でフィルターしてあります。数式は

=FILTER(sushi_tabeta,(sushi_tabeta[ネタ]="まぐろ")*(sushi_tabeta[皿数]=3))

こうです。各条件は、丸括弧で囲む必要があります。そうしないと、配列同士の演算が成り立たず、配列定数が返って来ないからです。条件部の数式を抜き出して入力してみると、

1つの条件式で返ってくるのは、TRUEとFALSEの真偽値からなる配列でしたが、それらをかけると、0と1からなる配列が返ります。試しに、配列定数をそのままかけてみましょう。

やはり、0と1からなる配列です。数式は

={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}
*
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

こうです。同じ位置にある要素同士をかけたものを要素とする新しい配列を返すわけですね。1つでもFALSEがあれば、FALSEはゼロなので、他にいくつ配列、すなわち条件があって、それの同じ位置の要素がTRUEであっても、結果はゼロになります。ゼロに何をかけてもゼロだからです。実際、複数条件をまとめたものを第2引数に入れた場合、それは結局、0と1からなる1つの配列定数が引数として投入されます。

条件を、かけ算では無く足し算にすると、またはの条件となります。条件の内いずれかが成り立っていれば良い、というものです。先ほどは、
ネタがまぐろ、かつ皿数が3
でしたが、足し算にする事で、
ネタがまぐろ、または皿数が3
の条件に変わります。

これも同じように、条件部の数式を抜き出してみます。

見やすいように色をつけました。今度は、
条件が成り立っている数
が出力されています。論理演算では、TRUEとFALSEや、または、かつなどの条件をいくつ組み合わせても、演算結果はTRUEかFALSEですが、Excelにおいては、

TRUE：1
FALSE：0

と数で表され、*や+で演算すれば、単なる数の演算として扱われます。ですから、または（OR）条件を足し算にして配列を繋げた場合、その要素は、条件が成り立っている数がそのまま表示されます。という事は、条件が3つになって、それを+で繋いだ場合、全部が成り立っていれば、結果は3が返ってきます。試しに、3つ目の条件として、金額が450を追加します。

全部を満たしている行の所が3になっていますね。という事は、このような複数条件を直接FILTER関数に投入すれば、その数式が返す配列の要素が1以上の所の行が表示されます。真偽値の配列に対して、数の配列が

TRUE：ゼロ以外
FALSE：ゼロ

このように対応するのですね。
では、3番目の条件として、金額が290である、としましょう。

うまく抽出されていますね。
数式は、

=FILTER(sushi_tabeta,
(sushi_tabeta[ネタ]="まぐろ")+
(sushi_tabeta[皿数]=3)+
(sushi_tabeta[金額]=290)
)

こうです。

可読性

このように、FILTER関数は、条件を増やしていってもうまく機能します。ですがそうすると、どんどん可読性が損なわれます。

この数式は、ネスト（入れ子）は深くありませんが、第2引数の数式が、3つの比較演算数式の和となっています。上の式はまだ、数式内改行を入れて、構造化参照をしているから読みやすいほうですが、テーブルを使わなければ番地参照となるので、どんどん読みづらくなるでしょう。そこで、条件が増えてくれば、それを直接関数に投入するのでは無く、テーブルに作業列を追加する事も検討に値します。

見やすいように色分けしてあります。右側に作業列を追加して、各条件を判定させています。

ネタがまぐろ
皿数が3
金額が450

そして、更に右側に、これら条件を、かけ合わせるのと足し合わせるのを追加しました。数式は次のようです。

テーブルはデフォルトでは、セルに数式を入れると、列に同じ数式が上書きされます。構造化参照は、同行を@で表現できますので、全く同じ数式を入れられます。これは構造化参照の圧倒的強みです。そして、AND条件列とOR条件列で、それぞれの条件をかけ合わせたり足し合わせたりしてあります。この結果が、非負の整数で構成される範囲となるわけですね。
ちなみに、真偽値の演算ですが、*や+で連結すれば、0と1の演算として機能しますが、SUM関数やPRODUCT関数の引数に、セル参照でTRUEとFALSEを入れても機能しません。どうやら、ブーリアンからなる配列定数を引数に入れても、それを0か1では解釈しないようです（引数自体を分ければ機能する）。

▼ここから追記：2024年7月19日
大変うっかりしていましたが、条件判定を外に出して作業列を作っているのだから、SUM関数やPRODUCT関数を使わなくても、そのままOR関数やAND関数を使えば良い話でした。FILTER関数には通常それらを入れないという思い込みが、うっかりを発動させていたようです。反省。
SUM関数やPRODUCT関数でももちろん動きますので、内容はこのままにしておきます。
▲ここまで追記：2024年7月19日

これで準備が整いました。後は、抽出したい範囲を指定して、第2引数には、追加した作業列を参照するようにすれば完成です。下のほうにFILTER関数を入れてあります。

数式は、

=FILTER(sushi_tabeta[[食べた人]:[金額]],sushi_tabeta[OR条件])

こうです。第2引数には、追加した作業列のOR条件列を参照させてあります。これであれば、条件が増えても作業列を増やせば良く、FILTER関数の中に条件を追加する必要はありません。

条件を動的に変化させる

今は、条件に直接、まぐろや3などを入れました。テーブルなので、変更する場合には一箇所を変えれば、列全体が上書きされますが、数式を書き換える必要があるのは面白く無いですね。であれば、この条件部自体も、別の所を参照させれば良さそうです。やってみましょう。

一番上に、テーブルを追加しました。列名を見れば判るように、各条件を入力するようになっています。ネタは入力規則のリストです。そして、寿司食べた表（テーブル）の条件判定を、ネタ判定などの列名に変更してあります。AND条件とOR条件はそのままです。そして、

ネタ判定
皿数判定
金額判定

これらの列の数式は、直接ネタ名などを入れるのでは無く、いま追加した条件テーブルを参照させます。条件テーブル名はconditionsとしてあります。

このようにすれば、メインのテーブルの数式を触る必要はありません。条件テーブル（conditions）の内容を変更すれば、それに合わせてメインのテーブルも変化し、結果的に、FILTER関数によって返るものも連動して変化します。

テーブルとFILTER関数の連動
※noteには、かなり品質を落とさないとgifは上げられないようです

要件に合わせて

作業列を増やす場合は、元の表が拡張されますし、FILTER関数に入れる条件を追加する場合は、可読性が損なわれます。どちらを選ぶかは要件次第ですので、両方のやりかたを知っておくと、色々の情況に対応出来ると思われます。
私が実務で作成する際は、迷わず作業列追加を選びます。メンテナンス性や、他者との共有可能性を考えると、数式はなるべくシンプルにしておきたいからです。また、表はあくまでデータとして使用し、それ自体を印刷等に使う事はほとんどありませんので、作業列を追加するデメリットは大きく無いのです。数式では構造参照を用いていますから、テーブルの拡張に対しては柔軟で頑健です。列名等の名前の定義をきっちりしておけば、そうそう崩れはしません。

関数の構造と配列への意識

ここまで見て来たように、FILTER関数の第2引数が、単なる条件なのでは無く、列方向か行方向の可視/不可視状態を決めるための配列であるのを把握する事によって、その関数の挙動について見通しが良くなります。恐らく、FILTER関数の第2引数に条件を入れるものだと認識する人は、SQLのWHERE句のように捉えているのだと思いますが、実際の引数が真偽値または数値の配列であるのを知れば、私が何度か説明したような、列か行の可視状態を司るスイッチのように思い浮かべる事が出来るはずです。そうすれば、そのスイッチ群をどのように制御して所望のフィルターを得られるのかと、より詳細にしくみを考えられるわけです。

また、配列という、順序をもった値の集まりが様々に使われているのを理解するのが重要です。プログラミングでは、配列は当たり前のように使うものですが、Excelなど表計算アプリケーションのワークシート関数をメインで使っていると、なかなか配列のような構造を意識する機会はありません。特に、Office 2021辺りでスピル機能が実装され喧伝されているので、その機能と配列が密接に関連しているのを理解しておくのが望ましいでしょう。

追記：2024年7月14日

FILTER関数の第2引数、可視条件を設定する配列部分の条件判定について、更に詳しく説明した記事を書きました。

参考資料

この記事が気に入ったらサポートをしてみませんか？