FILTER関数とスピルの仕組み
よくすすめられる関数
Excelの最近の関数で、便利なものやおすすめのものはあるか、という話で、FILTER関数が紹介される事があります。今回は、その関数について説明します。
注意事項
FILTER関数ならびに、一緒に説明するスピル機能は、2020年頃に追加されたものですので、使用バージョンに注意ください。
シートのフィルター機能
説明に使うのは、回転寿司を食べた記録です。
この種の表で、表を絞り込みたい場合があります。たとえば、一皿150円の寿司を食べた記録を絞り込んで表示したい、といった具合です。
その時、単にそれを表示したいのであれば、表にフィルターをつけて、単価の所のボタンを押して150で絞り込めば良いです。表の中を選択した状態で、リボンのデータタブ→フィルターを押せば有効にできます。
フィルターを有効にしたら、絞り込む条件を指定したい所のボタンを押して、条件を指定します。ここでは、単価が3種類あるので、そこで150のみチェックを入れて絞り込みましょう。
これで、単価が150円のもののみが表示されるように絞り込まれました。表を絞り込む機能をフィルターと言います。
FILTER関数
フィルターをかけて別表に表示させたい
単に、着目する表を絞り込んで表示させたいのであれば、いま説明したフィルター機能で充分に事足ります。しかし、要件によっては、元の表はそのままで、別の所に絞り込んだ表を作って表示させたい場合もあります。その時、あらかじめ元の表にフィルターをかけて、表示されている部分をコピーして、別の所に貼り付ける、というやりかたもできますが、いちいちフィルターをかけてコピーペーストし、必要に応じて元の表のフィルターを解除するなどの必要があり、処理が煩雑になってしまいます。これを関数で実現しようというのが、FILTER関数です。
FILTER関数の構造
今回は、いきなり実際に書いたものを示します。左側に元の表、右側に、FILTER関数を使用した別表を作りました。
一見すると、元の表にフィルターをかけたものをコピーし、右側に値を貼り付けたように思えます。では実際に、書いた数式を示します。
=FILTER(A2:E13,C2:C13=150)上記が実際の数式です。最初の材料のA2:E13が何を表しているのかは、すぐに解りますね。元の表です。次の材料を見ます。C2:C13=150となっています。これは何でしょうか。C2:C13は複数のセルが属する範囲ですが、それと150を等号で結んでいます。よく解りませんね…。ここで、Excelが示す、関数の構造を見てみましょう。
範囲と配列
最初の配列は、
フィルターする範囲または配列です
とあります。要するに、複数のものの集まりを意味します。いまで言えば、元の表を構成するセルを複数集めたものが範囲ですし、もっと抽象的に、集めるものの性質を特に問わず、似たようなものを集めてきてそれに番号をつけて管理する、というようなものが配列です。範囲に属するセルは1つでも構いません。その場合は、大きさが1の範囲であると表現できますから。
含む？
1つ目の材料は、直感的に把握しやすいです。フィルターをかけようとしているのですから、フィルターをかけたい表を入れれば良いのはすぐに解りますし、表がセルの集合であるのも明らかです。では、改めて2つ目の材料に着目します。
含む
含む…？ 何を言っているのか解りません。何が何を含むのでしょうか。これではさっぱりなので、説明文を見ましょう。
Bool値の配列で、TRUEの場合は保持する行または列を表します
…ますます意味が解りませんね。果たしてこの文を初見で理解できる人がいるのでしょうか。
しかもです。この材料としていま入れたのは
C2:C13=150これです。という事は、C2:C13=150が含むであり、Bool値の配列で、TRUEの場合は保持する行または列を表しているのを意味していると言えます。…何を意味しているのですか？
数式から外に出す
このままでは埒が明きませんから、違うアプローチを取りましょう。
以前のルックアップ系の関数の説明でも書いたように、数式は、複数の数式や関数の組み合わせで構成できます。数式や関数の中に数式や関数を入れるのを入れ子と表現するのでした。いま見ているFILTER関数は、1つ目の材料はシンプルな範囲ですが、2つ目の材料はC2:C13=150であり、これは等号がついていて、それ自体が数式になっています。つまり、材料として数式を投入している訳です。であれば、これを外に出してやれば、FILTER関数が実際に何を材料として必要としているかが解るというものです。やってみましょう。C2:C13=150を取り出し、元の表の隣に入れます。
謎の数式
実際に入れてみたのが下の図です。
着目する数式はC2:C13=150ですが、実際にセルに入れる場合は、最初に=をつけます。これは、頭に半角イコールを入れることで、それ以降が数式であるのを示す意味があります。それを入れた所、TRUEとFALSEが複数表示されました。これは何でしょうか。
真偽判定
いまは範囲と数値をイコールで結んでいるので、もっと単純にしてみます。
上図は、数式に
=1=1
を入れてあります。結果はTRUEです。TRUEは真である事、つまり、正しいとか合っているのような意味を表します。物事の真偽を明らかにする、といった具合で日常でも使われます。数式ボックスに入れる最初の=は、そこより後が数式であるのを示すために書くので、実際の数式は1=1です。これは、通常の算数や数学で使うやりかたとちょっと違いますね。そりゃあ、1は1だろうとなります。
実は、プログラミングや表計算アプリケーションの数式では、このような書きかたをした場合、
その数式の真偽が返ってくる
という機能を持つ場合があります。いまで言うと、
1=1は真か偽か
の結果がセルに表示されるという寸法です。もちろん1は1なので、1=1は正しいです。だから、真を表すTRUEが表示されている訳です。これを別の数式にしてみましょう。
今度は、数式に
=1=2と入れました。1と2は当然違います。だから、1=2は合っていますか、と問われたら、それは偽です。そして、セルに表示されるのは、偽を表すFALSEになるという訳ですね。文字列でも一緒です。
これは、1=2や"まぐろ"="まぐろ"といった数式が評価されて、その結果がセルに表示されているのを意味しているのです。
スピル
ここで、謎の数式に戻りましょう。
既に私たちは、シンプルな等式を数式ボックスに投入した場合、その評価された結果がセルに表示される事を知っています。謎の数式はC2:C13=150なので、そのしくみを使っているのは一緒です。ただしこれは、
左辺が（大きさ2以上の）範囲
となっているのが違います。ちょっと考えると、複数のセルがあるのに、それと1つの数値が一致する事などあるはずが無いではありませんか。であれば、FALSEが返ってくるのが当たり前のように思います。けれども実際は、
複数のTRUEとFALSEが複数のセルに表示されている
のです。これはつまり、
評価の対象が複数のセルからなる範囲であれば、その各セルごとに式が表示される
のを意味します。いまで言えば、式の左辺は、表の内の単価のデータの縦並び、つまり290,210,150,290,150…からなるセル範囲です。ですから、それをそれぞれ150と比較し、
290=150→FALSE
210=150→FALSE
150=150→TRUE
290=150→FALSE
150=150→TRUE
このように表示されて行ったのです。
通常の数式であれば、1対1の評価で、その結果が1つのセルに返されます。しかしいまのように、複数のセル範囲との比較の場合、
結果が複数セルに表示される
のです。ですから、謎の数式を実際に入れたのはF2セルなのに、F3以降も結果が表示されるのです。試しに1つ下のF3セルの数式を見てみると…
グレーアウトしています。そのグレーアウトされた数式は、1つ上のF2セルに入れた謎の数式です。いや、既に謎ではありませんね。
そしてこのように、
1つの数式の結果が複数のセルに表示される
機能を、スピルと言います。先に、似たようなものを集めて管理するようなものを配列と言うと説明しましたが、いま見ている数式は、複数セルと数値との比較をしているものだから、結果が配列になっています。ですから、その配列が複数の隣り合ったセルに表示されていく、というしくみです。
ここでスピル（spill）を辞書で調べてみましょう。
こぼす、流す、投げ出す、振り落とす、漏らす、ばらす、言いふらす
これを見れば解りやすいですね。つまりスピルとは、
数式の結果が配列になって、それが複数のセルにこぼれる
ような機能なのです。
ちなみに、スピルされたセルに入っている、グレーアウトされ変更できない数式の事をゴースト化されたものと言います。なんだか物騒な名前であります。
ふたたび、FILTER関数の構造
ここで再度、FILTER関数に投入する材料の2つ目に着目しましょう。
改めて見ると、最初に見た時よりも、内容が解ってきたかも知れません。まず、配列が解ります。ものの集まり、リストです。次はBoolです。これは、プログラミング言語などで、真（TRUE）か偽（FALSE)となるデータの事を表します。だんだん解ってきました。これをまとめれば、要するに、含むなる材料は、
TRUEとFALSEからなる配列（集まり）
を示すものだったのです。ここまでを踏まえれば、FILTER関数に投入した謎の数式C2:C13=150は、TRUEとFALSEの配列を返すものであった事が納得できます。
なぜBoolの配列が材料なのか
ここまでで、FILTER関数の2つ目の材料が配列であるのが解りました。では、なぜそのような材料を入れなくてはならないのかを考えましょう。
先ほど見た材料の説明を詳しく検討します。Bool値からなる配列で、
TRUEの場合は保持する
と書いてあります。保持って何でしょうね。
材料に範囲を入れてみる
FILTER関数には、C2:C13=150を入れました。これは配列を返すので、結果としてFILTER関数に配列が投入されるしくみです。そうであるなら、材料を外に出しておいて、FILTER関数からはそこを見に行かせるようにしても、同じように機能するはずです。やってみましょう。
先ほどは、謎の数式のしくみを知るためにスピルをさせましたが、今度は、TRUEとFALSEをそのまま直接入れています。そして、このBool値が入った範囲をFILTER関数に投入します。
いかがでしょうか。例の謎の数式を入れるのでは無く、TRUEとFALSEの集まりを示すセル範囲をFILTER関数に投入する事によって、全く同じ結果が得られています。つまり、FILTER関数で鍵を握るのは、TRUEとFALSEのリストであると推測できます。
Bool値を直接変更してみる
TRUEとFALSEの集まり、つまりBool値のリストがFILTER関数の機能の鍵を握っていると考えられます。であるなら、それが格納されているF2からF13を触ってみて、FILTER関数の結果がどうなるかを見てみましょう。
試しに、F2のFALSEをTRUEにしてみました。するとどうでしょう。右側のフィルターした部分に、Aさんが食べた うにのデータが表示されているではありませんか。もう少し探ってみます。
F4のTRUEをFALSEに変更しました。すると何と、Aさんが食べたぶりが、右の表で隠れてしまったのです。
表示と非表示を制御するスイッチ
ここまで来れば明らかです。
つまり、材料の説明で記載されている、
TRUEの場合は保持する
とは、
配列の値がTRUEであれば、その番号の横並びは表示する
のを表していたのです。言い換えれば、FILTER関数に投入する、Bool値の配列は、表の横並びを表示させるかどうか、つまり
フィルターのオン・オフのスイッチ
のようなものであった訳です。これを把握すれば、謎の数式C2:C13=150は、スイッチの集まりを作る数式であった事が解ります。要するに、単価の集まりC2:C13を150と比較して、セルに入っているのが150ならTRUEを、それ以外ならFALSEを与え、その全体を配列というリストに返していたのです。そうであれば、スイッチ群を直接操作する事で、自由に表示・非表示を操れるというものです。
ここまでを把握すれば、FILTER関数の基本構造は押さえられたと言って良いでしょう。何という事はありません。要するにFILTER関数とは、
フィルターする範囲
表示と非表示を指示するスイッチ群
これを投入して、元の範囲を絞り込んでいるに過ぎないのです。FILTER関数の説明が解りにくくなるのは、こういう基本構造の説明をせずにいきなり、配列を返すような数式を材料にして、入れ子の数式を構成して説明するからなのでしょう。
実は見出しも
FILTER関数の鍵になるのは第2材料、つまりフィルターのオン・オフを表す配列で、それを作るのに、謎のスピル数式が入れられていました。そしてスピルとは、数式の結果が配列になって、それが隣接のセルにこぼれていく機能を指しました。
実は、フィルターをかけた右側の表、データの所だけで無く、見出しの部分もスピルでできています。
ご覧ください。右側の表の見出しの一番左端、数式がA1:E1と、複数セルからなる範囲です。この結果は複数の値からなるリスト、つまり配列ですから、右側にスピルしています。ですから、I1セルの数式を見ると
ゴースト化されています。なかなか便利ですね。ただ、気をつける必要があります。無理やりゴーストを上書きしたりすると…
H1の結果がこぼれるのを、隣のセルの数式が邪魔をしてしまいます。その結果、
#スピル !なる#SPILLエラーが発生するのです。つまり、スピルされる先のセルは、空白でなくてはなりません。
複数条件によるフィルター
ここまでで、FILTER関数の基本的なしくみは把握できました。では、次には応用を考えてみましょう。
条件を増やす
いまの例でのフィルターは、単価が150円の皿の横並びを表示させ、それ以外は非表示にするものでした。だから、謎の数式C2:C13=150を入れて、単価が150であるものをTRUEにしたスイッチ群という材料を作って投入しました。
しかし、表をフィルターする際は、条件が1つだけとは限りません。たとえば、
150円の皿を3皿分食べた
データだけを表示させるには、どうすれば良いでしょうか。
かつ条件
まず、前のほうでやったように、FILTER関数に入れる2つ目の材料、つまりスイッチ群を外に出す事を考えます。外の範囲に記入して、そこを見に行かせる（参照）のです。
既に謎は解けたので、謎の数式は見出しを変え、フィルターの条件であると明示してあります。それは、単価が150円であるというものでした。いま、
皿数が3である
なる条件を加えるのを考えます。条件の数式を加えましょう。
要領は同じです。皿数が入っているのはD2:D13の範囲ですから、それと3をイコールで結べば、その評価結果がTRUEとFALSEの集まりである配列として返って、下のセルにこぼれて行く（スピル）流れです。これで条件は揃いました。後は、これを組み合わせて、FILTER関数に投入する材料であるスイッチ群を作るのを考えます。
ルックアップ系関数の説明で紹介したように、TRUEとFALSEは、1と0で代替できます。であれば、FILTER関数に投入するスイッチ群を、1と0で構成された配列にしても、同じように機能するでしょうか。やってみましょう。
上手く行っていますね。つまり、TRUEは1、FALSEは0で表現されます。そしていまは、両方が1の時のみ表示させる、つまりスイッチをオンにしたいのです。これは見かたを変えると、
どちらかが0であればスイッチを0にする
とも言えます。ある数値の集まりについて、1つでも0が入っていれば結果を0にしたい訳です。であれば、
条件をかける
ようにすれば、
1×1=1
1×0=0
0×1=0
0×0=0
となって、見事、両方が1の時にだけスイッチが1になるようにできます。ゼロに何をかけてもゼロだからです。数式にしましょう。
ちゃんと、TRUEとTRUEをかけた時だけ1になり、それ以外は0になっています。後は、これを関数の材料に投入すれば良いです。
上手く行きましたね。
材料を直接入れる
これで、複数条件でのフィルターのしくみが解りました。数式を入れ子にすると煩雑になるので、いったん材料を外に出して段階を分けて、最終的にFILTER関数に投入する材料を作り上げました。このように、データとしては不要であるけれども、処理をする際に一時的にデータを保持しておくような縦並びを作業列と言います。いまの例では、作業列を3列用意した訳ですね。
作業列は便利ですし、数式を複雑にするくらいなら作業列に分けたほうが読みやすいので、私は積極的に使います。けれども、慣れてくれば、ある程度はまとめて直接材料に入れるのも検討して良いでしょう。いまの例では、
単価が150である条件を判定
皿数が3である条件を判定
2つの条件をかけてスイッチを作成
この三段階ですので、それをまとめてみましょう。1と2はスピルで
C2:C13=150
D2:D13=3
このように既に書いています。後は、これをかけたものを配列として得られれば良いでしょう。であれば、H2に入れた通常の掛け算を、スピルになるようにしてみましょう。
見事に成功しました。これで成功するのが解りましたので次は、範囲同士をかけるのでは無く、
C2:C13=150
D2:D13=3
この2つを直接かけましょう。2つの作業列は消しておきます。
あれ、上手く行きませんね…下まで見ると、全部FALSEになっています。数式は
=C2:C13=150*D2:D13=3このようです。いま目的としているのは、2つの配列を求め、それに属する数値をおのおのかけてを新しい配列に格納してFILTER関数に投入する事ですが、これが上手く行っていないのです。ですから、それぞれの配列を算出する数式を、カッコで分けて明示します。
=(C2:C13=150)*(D2:D13=3)
見事に成功しました。このように、数式の構成によっては、上手くアプリケーション側が解釈してくれない場合がありますので、カッコでまとめて評価の順序を明示すると成功に繋がります。
いよいよ最終段階です。この数式を直接、FILTER関数に投入しましょう。
長い旅路でした。ついに完成です。最終的な数式は
=FILTER(A2:E13,(C2:C13=150)*(D2:D13=3))こうです。数式そのもの長さはそれほどではありません。しかしながら、これまで長く長く説明してきたように、この数式の構成には、FILTER関数に投入する材料の内容、それを作り出すための数式評価、配列の考え、複数の条件を組み合わせる数式の作成、といった、かなり複雑な背景が隠されていたのです。
説明の段階
ここで、数式や関数を実装したMicrosoftによる、FILTER関数の説明を見てみましょう。
次の例では、数式 =FILTER(A5:D20,C5:C20=H2,"") を 使用して、セル H2 で選択されている Apple のすべてのレコードを返し、リンゴがない場合は空の文字列 ("") を返します。
最初から、謎数式C5:C20=H2を入れて説明しています。これは、FILTER関数の2つ目の材料の役割および、《範囲》対《1つの値》による配列の生成、という両方のしくみを知らなければ意味が解らない記述です。もちろん、それをきちんと把握しなくても、やろうとしている事は解っているから、そこから何となくは掴めますが、私がこの記事で示したように、直接TRUEとFALSEをセルに入れて、その範囲をFILTER関数に参照させてフィルターをかける、などという挙動は思いつきにくい事でしょう。
ちなみに、3つ目の材料に""が指定されていますが、引用文にもあるようにこれは、見つからない場合に何を返すかという材料です。オプション、つまり、投入しなくても良い材料です。基本のしくみが解ればすぐに把握できるものなので、省略しました。
Microsoftのページでは、複数条件のフィルターの説明が続きます。
この例の =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"") は、乗算演算子 (*) を使用して、配列範囲 (A5:D20) から、"りんご" が含まれていて、かつ、地域が "東" であるすべての値を返します。
おそらく、私がこの記事で説明した内容を踏まえて読めば、上の引用文が何を言っているかは解ると思います。しかし、そうで無いのにいきなりこの文を示されたとして、すぐに理解できるでしょうか。先述したように、この数式は、2箇所の範囲に属する値のリストという配列との評価をおこなって、更にその結果同士をかけて最終的な材料の配列を作っています。リンク先の説明では、それをすぐに示している訳です。こういうのは暗記的に覚えればそれなりに使えるでしょうけれども、想定外の挙動を示したりした際には、きちんと関数の構造と材料とを把握していないと、修正や応用は容易ではありません。
重要なのは、着目している数式や関数が、
何を必要とし、何をしようとしているのか
を正確に把握する事だと考えます。
余談と応用
基本的な所を把握したかた向けです。
OR条件
メインで紹介したのはAND条件でした。乗算演算子で実現しています。作業列を使って確認したように、1と0の乗算なので、結果は1か0です。
いっぽう、OR条件は加算演算子で実現できますが、論理演算では無く単純な数の加算なので、結果は1または0ではありません。条件が2つあって、両方がTRUEなら、1+1で当然2になります。それでも問題無く動きます。
この振る舞いから解るのは、FILTER関数の第2引数である配列は、ブーリアンからなるリストで無くても良いという事です。つまり、
非表示行をFALSE（0）で示せば充分
と言えます。TRUEで保持を定義するのでは無く、FALSEで非保持を定義するのでしょう。
ですから、Excelが示すリファレンスは正確ではありません。そもそも、引数名を含む（英語だとinclude）などと書いている時点で察せますが。
Excelで表示される説明は
Bool値の配列で、TRUEの場合は保持する行または列を表します
これで全く意味不明ですが、Microsoftページだと、
高さまたは幅が array と同じブール値の配列
このようになっていて、より具体的です。どちらも解りやすくは無いですが。
追記
上の記述について、Excelでは、FALSEは0でそれ以外の数値はTRUEとなる、という話があります。実際にFILTER関数に与える配列の要素は、500であろうが-1000であろうが可視判定されます。
実装としてそうなっている事情は解りますが、それを踏まえて、引数をブール値の配列と表現するのは、やはり正確では無いと思われます。ブーリアンの配列として評価可能な数値配列、とでもすべきではないでしょうか（文字列があるとエラーになるので）。
空白処理
ルックアップ系関数と同じで、返される所に空白があれば、0が表示されます。
処理としては、FILTER関数の第1引数に渡す範囲（値の配列）をIF関数で包んで、空白の場合に空白を返すようにしておく、というのがありますが、まあ冗長ですね。
もう1つは、ルックアップ系での定番、後ろに&""をつけるやりかた。
短く書けますが、文字列化するので、数値が文字列扱いとなって左寄せされます。書式も数値用のが設定できないから不便ですね。
実は…
ここまで長く説明しておいて何ですが、実は私、FILTER関数を、ほぼ使いません。というか、スピル自体を滅多に使いません。と言うのは、表は原則テーブル化して、数式は構造化参照で作るからです。テーブル内でスピルは効きませんから当然、使う頻度も少なくなります。スピル系関数が便利なのは言うまでも無い事ですが、その利便性と引き換えにしてでもテーブルの性質を重視して使っています。
参考資料
この記事が気に入ったらサポートをしてみませんか？