Googleスプレッドシート FILTER関数 超応用例 -2（あるあるな例 多数）
先週に続いてGoogleスプレッドシートの FILTER関数について掘り下げていきます。
前回は基本動作、Query関数やLOOKUP系関数との使い分け、Excel と GoogleスプレッドシートのFILTER関数の違い、そして IMPORTRANGE関数と組み合わせた活用例などを書きました。
今回は あるあるネタの チョイ応用問題を中心に、FILTER関数の活用パターンをお題形式で紹介していきたいと思います。
超応用例までは・・・今回も辿り着きません！次回こそは。
FILTER関数 チョイ応用例
超応用例の前に FILTER関数のチョイ応用例（よく使いそうなケース）を見ていきましょう！
と色々書いていたら、また超応用例までたどりつけず・・・。FILTER関数は奥が深いんで書くこと増えちゃいます。
世の中のどのくらいの人が、今回のお題が簡単と感じるのかわかりませんが、自力で行けいそうな人は各お題の回答を見る前に挑戦してみてください。
Q1. 〇歳以上 〇歳未満をFILTER関数で取得する（片方が空欄の場合も対応）
ありがちな条件の一つですね。C列 年齢で 例えば 15歳以上、30歳未満だけを抽出したいといった場合、どのような式になるでしょうか？
条件部分はセル参照としましょう。
J1セルに 15歳以上、K1セルに 30歳未満 と表示させていますが、どちらも中身は数値のみ（15,30）で、 セルの表示形式 >カスタム数値形式で 成形しています。
J1セルは カスタム数値形式で 0歳以上
K1セルは カスタム数値形式で 0歳未満
このように設定しました。
こうすると J1に 15と数値を入れれば、見た目上は 「15歳以上」と表示されますが、数式上の扱いは 15という数値になり便利ですね。
もちろん 関数で 15歳以上 という文字列から数値だけ取り出すこともできますが、シートをいじれるなら 表示形式で対処した方がよいです。
この条件で H1にどのように FILTER関数を 書いたらよいでしょうか？
ただ、これだけだとチョイ応用ともいえない基本問題です。
追加要件として、「 J1セル、K1セル どちらか 片方、もしくは両方が空欄でも機能すること」を加えます。
つまり、たとえば J1が空欄でK1のみ数値が入ってるなら 30歳未満を全員、逆に J1のみ数値が入っていたら 15歳以上全員、そして両方空欄なら 全員（※年齢未記入はない）を表示するといします。
これでチョイ応用になりました。考えてみましょう！
↓↓↓回答
A1a. 〇歳以上 〇歳未満をFILTERで取得する
=FILTER(A3:F,C3:C>=J1,C3:C<MIN(K1,500))
シンプルに書くと C列の年齢に対して 不等号を使った条件をカンマ区切りで 2つ記述して AND条件とする上記のような式になります。
J1、K1が空白時の対応ですが、J1は 〇歳以上の条件数値なので、空白の場合は 0 扱いで 0歳以上となり 全年齢が条件を満たすので、特に気にする必要はありません。そのままで良いです。
一方 K1側が 空欄だった時は 0歳未満という扱いになってしまうので一工夫必要です。それが
MIN(K1,500)
この部分ですね。500は人間の年齢の上限としてありえない数値なら1000でも300でもなんでもよいです。
もちろんこの部分を IF関数で分岐してもいいんですが、よりシンプルな記述を目指すとMIN関数を活用するのがベストかと思います。
通常 MIN関数は最小値を見つける以外だと、K1が 500以上なら 500に丸めるといったキャップをかぶせるケースで使うことが多いんですが、今回は MAXやMIN関数の 空白を無視するという特性を応用しています。
つまり、K1が空白の時 MIN関数上は K1は 0としては使われず無視されるので、500という数値が返されます。当然500歳以上の人間はいないので、上限なしと同じ扱いになります。
A1b. 〇歳以上 〇歳未満をFILTERで取得する（ISBETWEENを使った別解）
ちょっと変化をつけた書き方の別解を紹介しましょう。
=FILTER(A3:F,ISBETWEEN(C3:C,J1,MIN(K1,500),,false))
こんな感じで 数値が 上限と下限の範囲内か否かを判別できる ISBETWEEN関数を使って条件記述を1つにまとめる方法もあります。
ISBETWEEN(比較する値, 最小値, 最大値, 最小値を含む, 最大値を含む)
※第3引数、第4引数は 省略時は TRUE（含む）
今回の場合は 上限側 だけ 〇歳未満 と 最大値を含まない処理なので、第4引数に FALSEを指定しています。
ISBETWEEN関数は Excelにはない関数で、結構知らない人も多いので紹介してみました。
上記のケースだと普通に書くより長くなってあまり価値を感じませんが、 たとえば 元データが配列(array)だった場合は、C3:C の部分を INDEX(array,,3) や CHOOSECOLS(array,3) と関数で条件列を取得する必要があります。
こんなケースだと、条件記述が1回で済む ISBETWEENを使う書き方がシンプルで良いかもしれません。
Q2. FILTER関数 複数のセル参照のAND条件の式を セルが 空欄だった時も機能するようにしたい
続いて先ほどの「条件に使用しているセルが空欄だった時」の対処の応用です。
=FILTER(A3:F,E3:E=L1,F3:F=M1)
前回登場した上記のような 式で、性別と血液型を セル参照で AND条件で絞り込むケースですが、これが 片方が空欄でも機能するようにするにはどうすればよいでしょうか？
もちろんL1が空欄の時も同様に、血液型だけで条件絞り込みが出来る必要があります。
当然このままの式では、M1の血液型の条件を空欄にした場合、エラーとなります。求めているのは血液型が空欄なら 性別が「男」を全て抽出したいのですが・・・。
これもあるあるなチョイ応用問題ですね。考えてみましょう！
↓↓↓回答
A2. FILTER関数 複数のセル参照 の AND条件で 空欄だった時に 機能するようにする
=FILTER(A3:F,E3:E=IF(L1="",E3:E,L1),F3:F=IF(M1="",F3:F,M1))
幾つか方法は考えられますが、上記のようなイコールの右側をIFで分岐させる方法がシンプルでよさそうです。
たとえば L1が空白の時には 第2引数は E3:E=E3:E という配列式になります。当然ですがこれは全て TRUE の縦1列の配列を返しますので、結果として もう片方の条件のみでの絞り込みとなります。
ポイントは 条件が空欄だった時、対象範囲と同じサイズ（行数）の TRUE（もしくはTRUE扱い）の配列を返す式を作るってことです。
上の式以外だと、SEQUENCE関数を使ったり、ROW関数を使う方法もアリですね。
Q3. リストに一致する データを抽出したい
抽出リストが別にあって、それと特定の列が一致する（重複する）データのみを取得したいというケースです。
同じくA3:F のデータ範囲から A列の名前が リスト H3:H13の いずれかに一致するデータを抽出するにはどうすればよいでしょうか？
これも良く見かけるので知ってる人は知ってそうですが。まずは考えてみましょう！
↓↓↓回答
A3a. リストに一致するデータを抽出する
=FILTER(A3:F,COUNTIF(H3:H13,A3:A))
この COUNTIFの式はよく見るなと思ったら、重複に色付けする時に 条件付き書式で カスタム数式に使う式の変化形ですね。
COUNTIF(H3:H13,A3:A)
COUNTIF(範囲, 条件)
ポイントは COUNTIF関数で配列処理をする際に、上の構文（引数の順番）に対して
本来の検索条件である H3:H13を COUTIFの 検索される 範囲に入れる
逆に本来の検索範囲である A3:Aを COUNTIFの 条件に入れる
つまり COUNTIF(A3:A,H3:H13) とするのではなく、
COUNTIF(H3:H13,A3:A) と 感覚的に引数を逆にするって点でしょうか。
得られる結果が A3:A の行分必要なので、A3:A側を条件とする必要がある為です。慣れないと 逆に書いちゃうことも多いんで気をつけましょう。
また、0以外の数値は TRUE、0がFALSEとなるので、わざわざ
COUNTIF(H3:H13,A3:A)>0
と書かなくても機能するってのもポイントです。
A3b. リストに一致しないデータを抽出する
逆に一致しないを抽出する場合は 1つも見つからない、つまりCOUNTIFの結果が 0となる 行に絞ればよいので
=FILTER(A3:F,COUNTIF(H3:H13,A3:A)=0)
となります。
Q4. 厳密な一致でFILTER関数を使いたい
XLOOKUPの回でも触れましたが、Googleスプレッドシートの一致判定は非常にゆるゆるです。
英字、漢字、かな(カナ) の区別はできますが、ひらがな・カタカナは区別されず、さらに 半角ｶﾀｶﾅ と 全角カタカナ、半角小文字英字 と 全角大文字英字 も同じと見なされます。
つまり 普通にFILTE関数でイコールで条件式を組んだ際、本当は平仮名の「おとこ」だけ抽出したいのに！って困る時があります。
では、FILTER関数の条件を 厳密な一致 としたい場合はどうすればよいでしょうか？
割と簡単ですし、前のnoteでも触れてるお題ですが 考えてみましょう！
↓↓↓回答
A4. 厳密な一致でFILTER関数を使う
=FILTER(A3:F, EXACT(E3:E,L1))
厳密な一致といえば、EXACT関数です。
EXACT関数で厳密な一致にする方法については、XLOOKUPの回でも触れているので細かい説明は割愛します。
結構深い！ FILTER関数で 含む を扱う
厳密一致に続いては「含む」で絞り込みたいってお題です。
「含む」条件は 需要が多いんで 使ったことある、もしくは 簡単だよーって人も多いかもしれませんが 結構奥が深いんです。丁寧めに書いておきます。
Q5. FILTER関数 あるワードを含むで抽出したい
一致ではなく「含む」で検索したい。
これもあるあるですね。
たとえば H1 に入れた文字（山）を名前（A列）に含む人を抽出したい場合、どうすればよいでしょうか？
↓↓↓回答
A5a. FILTER関数 あるワードを含む で抽出する REGEXMATCHを使う
「含む」と聞くと、COUNTIFやXMARCH で使えるワイルドカード * の使用を思いつくい人も多いかと思います。しかし、これは使えなくはないんですが、式が煩雑になるのでお勧めしません。
今回のようなFILTER関数の処理で「含む」を条件とする場合は、テキスト系関数による判定がおススメです。まず思いつくのが 正規表現関数の REGEXMATCHを使う方法です。
=FILTER(A3:F, REGEXMATCH(A3:A,H1))
正規表現はよくわからなくても、REGEXMATCHは 特定のワードを含むかどうか？を TRUE / FALSE で返してくれる という挙動だけでも覚えておきましょう。
ただし、この場合 正規表現上で特別な意味を持つ メタ文字 たとえば . ドット や() 半角カッコ を扱う場合は注意が必要です。また H1の検索ワードに 数値を入れた場合も エラーになります。※REGEX系は数値を扱えない（文字列扱いの数字は扱える）
名前の行の条件で 数値を扱うことはないでしょうが、例えば住所に3を含むデータを抽出したいといったケースでは注意が必要です。
数値だとダメなんですが、上の画像だと 検索値 3 を入れたO3セル の表示形式を 「書式なしテキスト」にすると 機能しているのがわかりますね。
"含む"条件で数字を扱う場合は、あらかじめ 検索値を入れるセルの表示形式を「書式なしテキスト」にしておくか、強制的に数値をテキストに変換する TO_TEXT関数を組み合わせて
=FILTER(A3:F, REGEXMATCH(A3:A,TO_TEXT(H1)))
このようにしておきましょう。
TO_TEXT関数は、数値を文字列として扱ったり 日付 や 表示形式で加工した数値などを セルに表示されたまま 文字列として取り出せる GAS の getDisplayvalue() みたいな関数です。
こちらの TO_TEXTをFILTER関数と組み合わせたチョイ応用例は、下の Q6 に記載しています。
A5b. FILTER関数 あるワードを含む で抽出する FINDを使う
FILTER関数の「含む」条件で使える関数としては、他に 昔ながらというか EXCELでもおなじみの FINDやSEARCHもあります。
実は REGEXMATCHよりも、メタ文字や数値を文字を意識せず使えるこれらの方が使いやすかったりもします。
=FILTER(A3:F, FIND(H1,A3:A))
注意点としては 検索ワード H1 と 検索対象 A3:A の引数の順番が REGEXMATCHとは 逆になっている点でしょうか。
ちなみに 関数名のFIND を SEARCH に変えても ほぼ同じ動きをします。この2つの関数は ほぼ同じ動きをするんですが、ちょっとだけ挙動が違います。でも、今回は単に含まれるか？の判定に使いたいだけなので、あまり気にせず FIND関数 を使うこととしましょう。
一応 FINDとSEARCHの違いは以下にまとめておきます。
（余談）FIND関数 と SEARCH関数はちょっとだけ違う
ほぼ同じような使い勝手のFIND関数とSERACH関数ですが、ちょとだけ挙動が違います。
この挙動の違いは ExcelもGoogleスプレッドシートも一緒のようです。
FIND関数
FIND(検索文字列, 検索対象のテキスト, [開始位置])
・大文字・小文字を区別する
・ワイルドカードは使えない
SEARCH関数
SEARCH(検索文字列, 検索対象のテキスト, [開始位置])
・大文字・小文字を区別しない
・ワイルドカードがつかえる
【ポイント】FILTE関数で 含む条件に FIND関数を使う際の注意点
FIND関数は数値やメタ文字を気にせず使えるんですが、REGEXMATCHと同じく こちらにも注意点があります。
FILTER関数で得られる結果は一緒にみえますが、中の挙動を見ると
REGEXMATCH関数
キーワードを含む TRUE
キーワードを含まない FALSE
に対して
FIND関数
キーワードを含む キーワードの見つかった位置の数値
キーワードを含まない #VALUE!
となっています。これがどんな影響があるか？
前回の Excelと Googleスプレッドシートの FILTER関数の違い の紹介で少しだけ触れましたが、Googleスプレッドシートは FILTER関数の条件部分で結果の一部にエラーがあっても TRUE（または 0以外の数値）だけを 抽出します。
つまり GoogleスプレッドシートのFILTER関数は エラーに寛容な処理であると言えます。
一方 Excel側のFILTER関数は エラーの扱いが違います。
=FILTER(A3:F100,FIND(H1,A3:A100),"")
Googleスプレッドシートで動いた FILTER関数+FIND関数の式を EXCEL（オンライン）用に置き換えて使っても、このようにエラーとなります。
これは Excel側のFILTER関数が エラーに厳しく、条件式で一つでもエラーがあったら 全体が エラーとなってしまう為です。
これを回避するには IFERROR でエラー時にFALSEを返すか、FINDの見つかった時は数値を返すという特性をふまえて ISNUMBER関数で括るといった方法を用います。
=FILTER(A3:F100,ISNUMBER(FIND(H1,A3:A100)),"")
Excel側の話だったら、
Googleスプレッドシートで使うなら気にしなくていーじゃん！
って思うかもしれませんが、実は OR条件 での絞り込みで 演算子 + を使う時に影響があります。
=FILTER(A3:F, FIND(H1,A3:A)+(E3:E=L1))
一見問題なさそうな 「名前に 山」を含む、または 「性別が 女」を抽出とする上の式ですが、「性別が 女」の方が1つも抽出されていません。
これは、FINDを使ったことで 山 を名前に含まない場合 エラーとなってしまう為、いくら 性別が女でも
エラー + TRUE(1) = エラー
となってしまい、FILTER関数で抽出対象外となってしまうからです。
これを先ほどの Excelの時と同じく FINDにISNUMBER関数を組み合わせることで 他の条件と OR で合わせた「含む」条件を FINDで使うことができます。
=FILTER(A3:F, ISNUMBER(FIND(H1,A3:A))+(E3:E=L1))
長くなりましたが、たまに 挙動を確認せず使ってるとハマるポイントです。
以下FILTERの「含む」条件利用の まとめです。
■FILTER関数で 含む 条件を使う方法
1. REGEXMATCH関数を使う
・結果がTRUE / FALSE で返るのでそのまま使える
・メタ文字 を扱う場合は注意。（エスケープで対応可能）
・数値を扱う時は注意（TO_TEXT関数と組み合わせ利用推奨）
2. FIND関数を使う
・結果は 含む時は 数値、含まない時は エラー #VALUE! を返す
・数値、メタ文字も気にせず扱える
・Googleスプレッドシートでは 見つからなかった時のエラーは、
OR条件利用の際に影響がある （ISNUMBER関数と組み合わせ推奨）
ちなみに「含まない」を条件とする場合は、NOT関数を使って 条件の結果を反転させましょう。※その際もエラーの対処は必要です。
Q6. 【含む条件 もうチョイ応用問題】FILTER関数で 〇年生まれや〇月生まれを抽出したい
では、含む条件の もうチョイ応用例です。
上のように 生年月日 D3:Dを条件列として K1 に入れた「〇〇〇〇年」や「〇月」、数字の「〇〇〇〇」と入れた際に、その年や月で絞り込むにはどんな式を組めばよいでしょうか？
いままでの流れを踏まえれば、自力で解けるお題かと思います。
考えてみましょう！
↓↓↓回答
A6. FILTER関数で 〇年生まれや〇月生まれを抽出する
順を追って解説します。
上のように 生年月日 を条件セルK1を 1984 として 1984年生まれを抽出したい場合、普通に REGEXMATCHを使った「含む」条件の式では N/Aエラーとなります。
これは、日付データ の中身はシリアル値なので
セル上の見た目 1984年10月20日
↓
実際の中身 30975 （シリアル値）
となっているのが理由です。
通常は 日付データから 〇年で抽出したいという場合は
=FILTER(A3:F,YEAR(D3:D)=K1)
として、YEAR関数で D列の日付データから 年の数値を取り出して 判定するのが一般的です。
でも、これだと K1を使って 〇月生まれを抽出したい 時に対応できません。IFで分岐させるのも面倒そうです。
こんな時に TO_TEXT関数を 活用できます。K1に4桁の数値が入ることも考慮して、D3:D、K1 両方に TO_TEXTを適用させます。
=FILTER(A3:F,REGEXMATCH(TO_TEXT(D3:D),TO_TEXT(K1)))
または
=FILTER(A3:F,FIND(TO_TEXT(K1),TO_TEXT(D3:D)))
このようにTO_TEXTをかますことで 日付データを文字列として扱うことが出来るようになり、〇年や〇月（生まれ）が 「含む」条件を扱える FINDやREGEXMATCHで抽出できるようになります。
注意点としては、この表だと 「1月」 とキーワードを 入れた際に、11月生まれも抽出してしまう点でしょうか。
これは「年1月」と入れるか、そもそもの生年月日データを「1984年1月5日」ではなく「1984年01月05日」と表示させるようにして、「01月」とキーワードを入れる運用にすることで解決できます。
FILTER関数 もうチョイ 応用例
含む条件の抽出の解説でだいぶ 長くなってしまいまいしたが、最後にもう少し歯ごたえのある・あるあるお題を1つだけやっておきましょう。
現場ではよくある 凶悪な表から FILTER抽出したいというお題です。
Q7. 2行で1データの 表から 条件に合致した データ（各2行分ずつ）を抽出したい
このような 2行で1データという非常に困りもののデータから A列 漢字の名前に E1のキーワード(中) を含む 人のデータを抽出したい、というお題です。
FILTER関数で どうやって2行分を抽出するか？
少し 難しくする為に 行の最後（お尻）を指定しない、つまり 第1引数の範囲は A3:C という条件を加えてみましょう。
少し難しいですが、自信のある人は自力で作成してみましょう！
↓↓↓回答
A7a. 2行で1データの 表から 条件に合致した データ（各2行分ずつ）を抽出する OFFSETでズラす
=FILTER(A3:C,REGEXMATCH(A3:A,E1)
+REGEXMATCH(OFFSET(A3:A,-1,0,ROWS(A3:A)),E1))
最もシンプルに書くとこんな感じ。
OR条件（+の後）の式は、OFFSETを使って一つ上の行が E1（中）を含むかを判別しています。
OFFSET(A3:A,-1,0,ROWS(A3:A))
この式で取得できるのは 以下のようなイメージ。
A列または B列 が 「中」を含む行 を抽出すれば 連続する2行がヒットすることがわかりますね？
ポイントは OFFSETの第3引数の ROWS(A3:A)
過去の noteでも書きましたが、お尻が指定されていない A3:A のような範囲をOFFSETする時、単に OFFSET(A3:A,-1,0) と1行上にしてまうと 縦サイズ（高さ）が1つ増えてしまいます。
バグというわけではないんでしょうが、お尻を決めないことで OFFSETした時にサイズが変わってしまうようです。これを第3引数の ROWS(A3:A) で固定化しているわけです。
ちなみに お尻を指定できるならもっとシンプルで、
=FILTER(A3:C100,REGEXMATCH(A3:A100,E1)
+REGEXMATCH(A2:A99,E1))
こんな感じに1つズラした範囲を指定するだけです。
A7b. 2行で1データの 表から 条件に合致した データ（各2行分ずつ）を抽出する 配列やOFFSET出来ない場合
しかし、お尻を指定しない範囲に適用できる OFFSETですが、これが使えないケースもあります。
OFFSETすると シートからはみ出る
対象が範囲ではなく配列である
以下のような シートを飛び出すような OFFSETは出来ませんし
そもそも セル範囲ではない IMPORTRANGE で取得したデータなど、 配列を扱う場合はOFFSET自体が使えません。
こんな時はどうするか？
配列の先頭に 空白を1つ追加し、最後の要素を1つ削る方法があります。
=LET(array,{A3:C},col,CHOOSECOLS(array,1),
FILTER(array, REGEXMATCH(col,E1)
+REGEXMATCH(ARRAY_CONSTRAIN({"";col},ROWS(A3:A),1),E1)))
ARRAY_CONSTRAIN({"";col},ROWS(A3:A),1)
まず {"";col} で、縦1列の配列の先頭に 空白を一つ追加します。
Googleスプレッドシートではお馴染みの、中カッコを使った 配列結合ですね。
ただ、これだと 長さが1つ増えてしまうので 配列の最後を減らしてサイズを元データと同じにする必要があります。
この 最後の要素を削るのに使っている関数が ARRAY_CONSTRAIN です。
EXCELだったら DROP関数を使いたい ところですが、Googleスプレッドシートには無いので 配列を縮小させる こちらの関数を活用しています。
ちなみに、これはFILTER活用とはズレるんで 割愛しますが、この手の処理の解決方法として TOROW（またはTOCOL）とWRAPROWSを駆使して、一回1レコード1行の正しい形にしてからFILTERして結果を再度2行データに折り替えす式でやる方法もあります。
変則的なデータに対しても、工夫すれば FILTER関数を使えることが理解できたでしょうか？
いよいよ 次回こそは FILTER関数 超応用例
今回も長くなってしまったので、超応用例（フリーワード複数検索）にたどり着く前に終わりとなってしまいました。。
ただ、今回の チョイ応用例で、様々な関数の知識と理解があれば FILTER関数は無限の可能性があるってことが理解できたんじゃないでしょうか？
次回の FILTER関数 と 最新の LAMBDAヘルパー関数の組み合わせで、超応用例をどう解決するか？
その答えは 来週 FILTER関数シリーズ最終回で！
この記事が気に入ったらサポートをしてみませんか？