Googleスプレッドシート FILTER関数 超応用例 -1（XLOOKUP,QUERYとの使い分け importrange組み合わせ）
LETや LAMBDAヘルパー関数（BYROW、BYCOL、REDUCE）など新関数の登場で 各段に活用の幅が広がったのが FILTER関数です。
Googleスプレッドシートでは当たり前に使われている（と思うんだけど、使えてない人もいるんですよね） FILTER関数ですが、Excelでは買い切り版のバージョンによって対応していないものも多いので、まだまだ使ってない人も多いみたい。
MOTTAINAI！
一度 FILTER関数を使ったら、これなしじゃ生きていけません。
NO FILTER NO LIFE
それくらい便利です。
というわけで、今までの noteでも何度も登場してますが、改めて 最強のデータ抽出関数 FILTER関数を紹介します。
超応用例までは・・・今回は辿り着きません！
前回の記事（GASネタ）
Googleスプレッドシート FILTER関数 の基本
FILTER(範囲, 条件1, [条件2, ...])
基本を説明するだけでも、かなりのボリュームなんで本当はすっ飛ばしたいんですがｗ 一応ポイントを押さえておきましょう。
公式ページ
FILTER関数とは？
FILTER関数は セル範囲（または配列）を対象として、ある列（または行）が特定の条件を満たすデータに絞り込み 抽出する関数です。
上の式で 条件1,条件2… となっている通り、複雑な条件や複数の条件でデータを絞り込め、条件を満たすデータを全てまるっと取得できるのが魅力です。
こんな感じで 特定条件で絞り込みたい時、まさにフィルタ機能での絞り込みと同じようなことがしたい時に使えます。
「条件」の部分は、基本的には true / false を返す式である必要があります。
ちなみに この条件式 E3:E=L1 はどういった挙動をしているのか？
後ほど 再度触れますが、FILTER内では 自動で配列処理がされています。なので、動きを見るには条件式にArrayfomrulaをつけて
=ARRAYFORMULA(E3:E=L1)
として対象データの 右側の列に 出力してみるとよいです。
上の画像のように E3:E のデータが L1（男）と一致する場合 TRUEを返し、それ以外は FALSEとなっています。
ここでTRUEとなった行だけが抽出対象となっているのがわかりますね。
なお、FILTER関数での処理には絶対参照を使う必要はありません。
過去noteでも書いた内容ですが、条件部分については 以下がポイントです。
■Googleスプレッドシートの FILTER関数の条件の記述ルール
・条件は 対象範囲（配列）と 同じ行数の 1列の配列 であればよい
（または結果として 同じ行数の1列の配列を返す式）
・抽出対象となる条件
真偽値 TRUE 、もしくは 0以外の数値（これは TRUE扱い）
※上記以外（FALSE、0、文字列、空白 は除外対象）
→ 抽出対象結果 と同じ行の 対象範囲（配列） が返される
・FILTER関数の エラーあるある
- 条件配列の行数が 対象範囲の行数と 違っている
- 条件配列が 1列ではなく 複数列になっている
- 条件配列が 配列ではなく 1つの値を返している
列方向の絞り込みも出来る
フィルタ機能は 縦方向（行）の絞り込みしかできませんが、FILTER関数は 横方向（列）の絞り込みも出来ます。
行方向の絞り込みか？列方向の絞り込みか？は、特に引数で切り替えるとかではなく、自動判別となっています。
条件式が 対象範囲の 縦サイズ（高さ）と同じサイズの1列なら 縦方向の絞り込み、対象範囲の 横サイズ（幅）と同じサイズの1行なら 横方向の絞り込みとなります。
FILTERを入れ子にすることで 縦・横 両方の絞り込みも可能。
FILTER関数で AND検索・OR条件を使う場合
AND条件は簡単です。そのままカンマ区切りで条件式を並べればよいだけ。
=FILTER(A3:F,E3:E=L1,F3:F=M1)
↑ の式は、
E3:E=L1 性別が男（L1の値）
かつ （AND）
F3:F=M1 血液型が B（M1の値）
で絞り込む式となっています。これで 対象データから B型の男性を抽出しています。
では、OR条件の場合はどうなるのか？
ちなみに、AND条件の方も違う書き方ができますます。
OR条件とセットで覚えておくと便利です。
AND条件・OR条件を式にするのに、そのまま AND関数や OR関数を使いたいところですが、残念ながら 配列に対しては AND関数・OR関数は使えません。
FILTER内は配列処理なので、AND関数・OR関数は正常に機能しないってことです。
そこで true が1 （0以外の数値は true）、false は 0 という性質を利用して、以下のように演算子（ANDは * 乗算、OR は + 加算）に置き換えることで、FILTER関数の条件を 「かつ」や「または」とすることができます。
■FILTER関数 複数条件 （AND,OR）での絞り込み
- AND式、OR式は 配列では使えない
- 四則演算 で TRUEは 1、FLASEは 0 と扱われることを活用する
※ 1以上も全てTRUEの扱い
- AND条件 (条件1) * (条件2) ・・・ 両方TRUEで TRUE
どれか1つが0(FALSE)だと 0をかけるので 全体が 0(FALSE)となる。
- OR条件 (条件1) + (条件2) ・・・ どちらか TRUEで TRUE
どれか1つが 1(TRUE) なら 全体としては 1以上(TRUE)になる
↓以下はExcelの解説ですが、Googleスプレッドシートでも同様の理解でOKです。
というわけで、FILTER関数での記述は
AND条件
=FILTER( A3:F, (E3:E=L1)*(F3:F=M1) )
OR条件
=FILTER( A3:F, (E3:E=L1)+(F3:F=M1) )
このようになります。
これを複数組み合わせて複雑な条件処理もできますが、カッコが複雑になるので注意が必要です。
Excelの FILTER関数との違い
=FILTER(array,include,[if_empty])
ExcelのFILTER関数 と GoogleスプレッドシートのFILTER関数は、式の構成に微妙に違いがあります。そのせいで移行時にエラーになることも。
=FILTER(A3:F,E3:E=L1,F3:F=M1)
=FILTER(A:F,(E:E=L1)*(F:F=M1),"")
同じ例で式を書くと違いがわかります。
ExcelのFILTER関数は、条件は 第2引数にしか入れられず、カンマ区切りで複数羅列して AND検索はできません。先ほど記載した演算子を活用する必要があります。
また、第3引数に 1つも条件に一致する項目が見つからなかった時の返り値を設定可能です。これは "" として空文字を返すことが多いです。省略した場合、見つからない時はエラーになります。
こちらの「見つからない時の返り値」は GoogleスプレッドシートのFILTER関数にはありません。同じことをやる場合、IFERROでFILTERを括って対応します。
その他、以下は FILTER関数というよりは Excelの特性なんですが、
A3:A のような開始行だけ指定してお尻を指定しない 記述はできない
A:A とするか A3:A100のように両方行数値を入れるかどちらかになります。
Googleスプレッドシートで慣れてると、これ地味に不便なんですよね。
FILTERで抽出された結果の空白セルは 0表示
そして、FILTER関数でも当然空白セルは出力すると 0になるのは変わらずです。これもGoogleスプレッドシートだと気にしない点なので結構ストレス。
上のように 表示形式を 日付としていると 空白が 0扱いで 1900年1月0日 となって紛らわしいですね。
また細かい点ですが、条件式の中で一部エラーが出る場合の挙動が Googleスプレッドシートと ExcelのFITLER関数では違います。
Excleの方はエラーに厳しい感じ。詳細は過去noteを参照ください。
そもそも FILTER関数は Googleスプレッドシートが先行していたので、EXCEL側が合わせればよいと思うんですが、合えて微妙に変えてくるってのは MS側の陰湿な嫌がらせでしょうかｗ
FILTER関数 はArrayformulaいらず
先ほど記載した通り FILTER関数内の処理は 自動で配列処理となります。
それは 第2引数以降の条件部分に限らず、第1引数の 出力する配列に対しても適用されます。
つまり、FILTER関数だけで 絞り込むついでに結果を加工して出力することが出来るってことです。
=FILTER(IF(A2:F2="年齢",A3:F&"歳",A3:F),(E3:E=L1)*(F3:F=M1))
たとえば、このような式でA3:Fを出力する際にタイトル行 A2:F2が "年齢"の列だけは 後ろに "歳" をつけて出力する、といったことが可能です。
他にも URLが別のある列にあるデータで、FILTER関数で絞り込みついでにハイパーリンクにして出力なんて使い方もアリですね。
まさに Arrayformulaいらず！
FILTE関数内で Arrayformulaを使ったり、FILTER関数をArrayformulaでくくるなんてことをしている人は、無駄なんでArrayformulaはずしましょう。
この出力結果も配列処理できるという特性を理解していれば、先ほどのExcelのFILTER関数で結果の空白箇所が 0になってしまう問題も、IF関数で空白を空文字とする配列処理で回避できます。
=FILTER(IF(A:F="","",A:F),(E:E=L1)*(F:F=M1),"")
その他、基本的なFILTER関数の説明については、より丁寧に書かれている「いきなり答える備忘録」さんが参考になります。
FILTER関数と 他の関数の使い分けを考える
とても便利なFILTER関数ですし mir的には普通に使えるものって認識なんで、つい 過去noteでも 説明なしに使っちゃったりしますが、まだまだ世間的には認知不足だったりします。
Excelでは先行で利用できていた & 対応バージョンが多い XLOOKUP関数や、Googleスプレッドシートならではで SQL利用者にもとっつきやすい Query関数の方がメジャー（聞いたことある人が多い）なのかもしれません。
上のグラフで FILTER関数の方が多いことはわかりますが、利用者の多いEXCELとGoogleスプレッドシートの両方で使えるFILTER関数に対して、Googleスプレッドシートでのみ利用可能な Query関数はかなり健闘してるんじゃないでしょうか。
というか、Googleスプレッドシート上で使いたい人の検索数としては FILTER関数より多そうです。Query関数は大人気ですね。
XLOOKUP、Query どちらも最強関数であることは間違いありません。
でも、FILTER関数を含め それぞれ適するケースがあります。
FILTER関数との使い分けを考えてみましょう。
LOOKUP系（XLOOKUP、VLOOKUP）と FILTER関数の使い分け
XLOOKUPを代表格とする LOOKUP系関数は
一致を条件として（近似値一致やワイルドカードの場合もある）
単一の値（行または列）を取得する
という処理であるのに対して、
FILTER関数は
条件式をもちいて
条件を満たす行（または列）を全て取得する
という、いわゆるスピル（式を入れたセル以外にも結果が展開される）を前提とした関数です。
ざっくり言うと、
XLOOKUP関数
・一致を条件としてヒットしたもの1つだけを取得したい
・縦並びの各々のキーワードに合致した結果を取得したい（Arrayformula）
FILTER関数
・複数または複雑な条件でデータを絞り込みたい
・条件に合致したものを全て取得したい
こんな感じの使い分けでしょうか。
基本的に複数データを返す（スピる）関数であるFILTERは Arrayformulaと組み合わせて使えません。
上の青字のような検索したいキーが複数あって、それぞれの結果を 複数列で取得したい場合、これを 一つの式で処理するには、
Arrayformula + VLOOKUP
を使うことになります。
なんでArrayformula + XLOOKUPじゃダメなのか？は過去noteを参照
Query関数とFILTER関数の使い分け
Googleスプレッドシート と Excelで 多くの関数が輸出入され、どちらにも存在する関数がだいぶ増えました。
それでも Googleスプレッドシート独自の関数として、今でもExcel側には輸入されていない最強関数が2つあります。
それが
最強集計関数の QUERY関数
正規表現を扱える REGEX系関数 3兄弟（REGEXMATCH,REGEXEXTRACT,REGEXREPLACE)
です。
でも、QUERY関数は確かに最強ですが、それは絞り込みだけじゃなく、グループ化、ピボット、集計、並べ替え、件数制限、列指定、ラベル変更 と 複数魔法（処理）同時発動が使えるチート関数ってのが大きいです。
■QUERY関数を使うべきシーン
1. 絞り込みと合わせて order by 句で 並び替えを行いたい
2. group by や pivot 句で 集計を行いたい
3. 配列 を対象として 絞り込む際に FILTERより簡潔に記述できそう
Query関数を使うべきシーンとしては、1,2のような単純な絞り込みじゃないケース。
もちろん状況によるので、1に関しては FILTER + SORTでやった方がよい場合もありますし、2に関しても関数ではなくピボットテーブルで処理した方が良い場合もあります。
3 に関しては Queryで配列を対象とした際は Col1,Col2 といった列番号で指定できるのが強みでしたが、今は LET関数や CHOOSECOLSといった新関数をFILTERと組み合わせて簡潔に記述できるようになったんで、この理由でQuery関数を選択する必要性は低下してるかも。
「条件による抽出・絞り込み」に特化した利用では、専用関数である FILTER関数の方が Queryより 上 なのです！
また条件による抽出のついでに加工して出力したい、って時もFILTER関数の方が簡潔に書けることが多いです。
Query 推し、Query至上主義みたいな人も多いですし解説サイトも多いんですが、あえて mir的には FILTER関数から入ることをお勧めしたいかなと。
FILTER関数 と IMPORTRANGEの組み合わせ
先ほどの Query関数を使うべきケースの 「3. 配列 を対象として 絞り込む際に FILTERより簡潔に記述できそう」 の代表例が、 importrange関数を使って他のスプレッドシートのデータを扱う時です。
importrange は 他のスプレッドシートファイル（Excelでいう他のブック）を参照する際に利用する関数で、Googleスプレッドシートをデータベース的に扱う際には抑えておくべき関数です。
IMPORTRANGE(スプレッドシートの URL, 範囲の文字列)
FILTER関数とIMPORTRANGE関数 を組みわせる
基本のAND検索の際に登場した以下の式、
=FILTER(A3:F,E3:E=L1,F3:F=M1)
これが 他のスプレッドシートの シート2に元データがあって、
さらに 作業シートや作業列が使えないという 縛りがあったら、
importrangeを 直接 組み合わせた式にする必要があります。
=FILTER(importrange("シートID","シート2!A3:F"),
importrange("シートID","シート2!E3:E")=E1,
importrange("シートID","シート2!F3:F")=F1))
こんな感じの煩雑な式になってしまいます。
シートIDは40字以上で結構長いので、これも直接入れると3回登場するんで120文字・・・、かなりの長さになりますね。スプレッドシートURLを使ったらとんでもない長さですｗ
Query関数とIMPORTRANGE関数 を組みわせる
これをQuery式で書くと、ぐっと簡潔になります。
=QUERY(importrange("シートID","シート2!A3:F"),
"where Col5 ='"&E1&"' and Col6 = '"&F1&"'")
QUERY関数は 対象が配列（直接のセル範囲参照ではない）の場合、列の指定を Col1,Col2… と書けるのが強みですね。
ただし、シンプルですが 条件をセル参照にした際の ダブルクォート、クォート、&、スペース、文字の大文字・小文字（一部）、これらが厳密にチェックされるので正しく書かないとエラーから抜け出せません。
&"' and ← ここのスペース忘れて
&"'and と書いてエラーになることも多い
ちょっと慣れが必要ですね。
ちなみに FILTER側に合わせてますが QUERY関数の場合はタイトル行含めて取得した方がよいです。
便利で人気なのもよくわかります。
新関数登場前は、importrangeと組み合わせたデータの 条件絞り込み、もしくは範囲指定なしで取得してからの 空白行削除は、Query一択でした。
FILTER関数とIMPORTRANGE関数 と LET関数 を組みわせる
それでは FILTER関数の方の記述を簡略化する為に、 新関数の LETを組み合わせたらどうでしょうか？
=LET(data,importrange("シートID","シート2!A3:F"),
FILTER(data,index(data,,5)=E1,index(data,,6)=F1))
最初に importrangeで取得した 別スプレッドっシートの シート2の A3:Fの範囲を LETで dataと置くことで、FILTER内の記述が簡潔になりました。
Query関数の方が式の文字数は少ないですが、差は10文字程度です。
慣れによってはこっちもアリでしょう。もちろん 複雑な条件での抽出であれば FILTER+LETの方が記述が短くなることもあります。
さらに 最初のFILTER式では importrangeが3つあったので、3回外部アクセスが走っていましたが、LETで1つにまとめたことで importrangeの外部アクセスが1回になり処理が軽くなっています。（たぶん）
LET関数は 過去noteで 以下の基本編に加え、応用編2回の全3回に渡って取り上げています。
FILTERで項目行（タイトル行）を含めて抽出する
とはいえ、QUERY関数はタイトル行を含め取得できるという便利機能があります。
=QUERY(importrange("スプレッドシートID","シート2!A2:F"),
"where Col5 ='"&E1&"' and Col6 = '"&F1&"'",1)
引数の一番最後、ここが見出しの指定になります。省略しても自動で見出し行と判断してくれることが多いです。
残念ながら FILTER関数には タイトル行を指定する引数はありません。
では、どう対応するか？
【タイトル行】分離して最後に再結合する方法
まず 思いつくのは、タイトル行とそれ以降のデータ行を分離して、最後にFILTERの結果と連結させる方法
タイトル行は簡単です。INDEXやCHOOSEROWSを使って取り出せます。
=LET(data,importrange("シートID","シート2!A2:F"),
↓タイトル行
INDEX(data,1,)
または
CHOOSEROWS(data,1)
ただ、タイトル行より下のデータ行のみを取得するのが面倒です。
Excelだったら DROPで DROP(data,1) で1行目だけ落とせて簡単なんですが・・・。 DROP関数が輸入されなかったのが悔やまれます。
Googleスプレッドシートでも 1行目を捨てたデータを取得する方法はありますが、式が長くなるので意味がありません。
極論を言えば、条件によっては絞り込みで勝手にタイトル行は除外されるんで、そのまま使ってもアリかと思います。
=LET(data,importrange("スプレッドシートID","シート2!A2:F"),
title,INDEX(data,1,),
VSTACK(title,FILTER(data,index(data,,5)=E1,index(data,,6)=F1)))
連結は 中カッコとセミコロン {title ;FITER(… } でも良いですが、FILTERの結果が1件もヒットしなかった場合にエラーとなった際に、タイトル行 とエラーでは 結合面のサイズ違いで 全体がエラーとなってしまいます。
その点、VSTACKによる縦連結なら 結合面のサイズ違いが発生しても タイトル行の表示は保持されるので、こっちを使いました。
【タイトル行】FILTERの条件で処理する方法
FILTERのOR条件に タイトル行を取得する条件を入れ込む方法もあります。
これは2つアプローチがあります。
=LET(data,importrange("スプレッドシートID","シート2!A2:F"),
FILTER(data,(index(data,,5)=E1)*(index(data,,6)=F1)
+(index(data,,6)="血液型")))
簡単なのはタイトル行の文字を一致条件に加える方法です。
(index(data,,5)=E1)*(index(data,,6)=F1)+(index(data,,6)="血液型")
「5列めがE1と一致する かつ 6列目がF1と一致する」
または
「6列目が "血液型" に一致する]
という条件で絞り込むことで、タイトル行を含めています。
「または」条件を絡めるので、 カンマ区切りで条件を羅列する方法は使えなくなるので演算子を使います。ここで少し複雑化しちゃいますね。
もう一つは バーチャルな行番号を条件に使う方法。
ROW関数を使いたいところですが、importrangeで取得したデータは セル範囲ではなく配列なので 行や列といったセル位置の情報を持ちません。（OFFSETなども使えません）
よって、バーチャルな行番号を生成する必要があります。
importrangeで取得した data（タイトル行を含む） の行数は、ROWS(data) で取得できます。
これを SEQUENCE関数と組み合わせ
SEQUENCE(ROWS(data))
とすると、1からdataの最終行までの連番が生成されます。
これを条件として使います。
タイトル行は先頭なので 1ですね。
=LET(data,importrange("シートID","シート2!A2:F"),
FILTER(data,(index(data,,5)=E1)*(index(data,,6)=F1)
+(SEQUENCE(ROWS(data))=1)))
FILTER関数の良い点は、このように 行数（サイズ）さえ揃っている配列を用意すれば条件に使えるという点です。
こっちの方が汎用性あっていいかも。
でもQUERY関数に比べると、ぐっと手間が増えるんで タイトル行を入れたい場合は無理にFILTER関数じゃなくてもいいですね。
FILTER関数で QUERYの Selectみたいに 結果列を絞り込みたい
importrangeで取得したデータを条件で絞り込んだうえで、
1列目（氏名）、5列目（性別）、3列目（年齢）
だけを、この順番で使いたい。
こんな要望もQUERY関数なら SELECT句で簡単です。
=QUERY(importrange("シートID","シート2!A2:F"),
"select Col1,Col5,Col3 where Col5 ='"&E1&"' and Col6 = '"&F1&"'",1)
でも、これも新関数 CHOOSECOLSを FILTER関数に組み入れることで対応できます。
=LET(data,importrange("シートID","シート2!A2:F"),
FILTER(choosecols(data,1,5,3),
(index(data,,5)=E1)*(index(data,,6)=F1)
+(SEQUENCE(ROWS(data))=1)))
タイトル行を含める式から 変更点は 太字部分だけなんで 簡単ですね。
chooseclosが活用できるケースです。
importrange関数との組み合わせに限らず、これ以上（並べ替え、グループ化、クロス集計化）を求めるなら、もうFILTER関数ではなく QUERY関数に切り替えるべきです。
一方、条件が複雑で QUERY関数の Where句で記述するのが無理ゲーって時には、上記のFILTER + LET が活用できるかもしれません。
両方使いこなせれば便利ってことと、FILTER関数は 他の関数の知識があればあるほど、条件部分の式が柔軟に作成できるってことを覚えておきましょう！
FILTER関数 超応用例 のゴール
今回は長くなってしまったので、超応用例にたどり着く前に終わりとなります。
ただ「超応用例ってなに？」って気になる人の為に、上の画像をあげておきます。
わかりやすいように同じシートに出力しています。
・対象データの範囲はA:H
・J1に入れた スペース区切りの複数ワードで 絞り込み検索ができること
（複数ワードは何個までという指定はなし）
・区切りのスペースは 半角、全角どちらにも対応すること
・ワードを「含む」で検索できること
・列を指定せず 検索できること（どこかのセルに含んでる行を抽出）
・タイトル行も出力すること
・タイトル行の上にヒット件数を表示すること
・一つの式で処理すること
FILTER関数を他の関数（新関数フル活用）と組み合わせて、これにチャレンジしてみよっていうお題です。
来週続きを書きますので、その前に興味ある方は 自力でチャンレジしてみてください。
いいなと思ったら応援しよう！
