違う世界線へ！ Googleスプレッドシートのフィルタビュー（フィルタ表示） が超絶便利 3（関数と合わせたら本気出す）
2024/06/01 追記
当 noteは フィルタビューが フィルタ表示という名前だった時に書いたものです。2024年5月、仕様変更および名称変更があり、現在は一部画面構成など変更されている点があります。
変更点については、以下の noteでまとめております。
Googleスプレッドシートの 「フィルタ表示」 シリーズの3回目、今回が一応シリーズ最後・・・と思った書いてたんですが
書いてたら長くなってしまって今回で終わらず。もう1週つづきます！
普通の フィルタ とは 一味違う フィルタ表示。
前回は 閲覧権限や 保護と組み合わせたフィルタ表示の活用と Excelのシートビューとの違いを検証しました。
今回はフィルタ表示と関数を組み合わせた 超絶便利な 活用方法を紹介していきます。
「フィルタ表示」でカスタム数式を使って本気出す！
フィルタ表示 だけではなく、通常のフィルタでも使える機能なんですが、Googleスプレッドシートは カスタム数式で フィルタ（絞り込み）をかけることが出来ます。
言い換えれば、フィルタ条件にシート関数を使うことが出来るんです。
Excelでは フィルタの絞り込みに 関数（数式）は使えない
Excelのフィルタも多機能ではありますが、残念ながら Excelでは フィルタ条件に 関数（数式）は使えません。
一応、複雑な条件でのフィルタは、ユーザー設定のフィルタを使って対応する方法があります。
上のようなダイアログが表示されるので、 AND条件 や OR条件、等しいや 含む、以上などの条件 を組み合わせたり、 ?や*などワイルドカードを使って条件セットすることが出来ます。
これでも対処できない条件で絞り込みたい場合は、条件フラグ用の作業列を使って そこに 関数・数式を入れて対処する方法が一般的です。
たとえば 取り込んだデータが 年齢 が 〇〇歳 と文字列になっていて、かつ諸事情で 歳を 削除して 数値を 表示形式で「歳」をつけるといった対応ができない時、Excelの場合は作業列で対応することになります。
H列を作業列として使うとしたら、 H3に
=SUBSTITUTE(C3:C502,"歳","")*1
と入れると H列には C列の年齢を数値化したデータが出力されます。
SUBSTITUTEは ExcelにもGoogleスプレッドシートにもある 元祖 置換関数ですね。
※テーブル化している場合は =SUBSTITUTE(C3,"歳","")*1 をH3に入れると自動展開されます
年齢が 数値化された H列であれば、
数値フィルタ ＞ 指定の範囲内
として 20～29 とすることで、20代に絞り込みすることが出来ます。
わかりやすいんですが、どうしても作業列が使えませんって時は困りますよね。
一方 Googleスプレッドシートのフィルタなら、カスタム数式で、この作業列で使ったような 関数、数式を フィルタ条件に使うことで、 作業列いらずで処理出来ます。
これが超絶便利なんです！！
フィルタ表示のカスタム数式は 条件付き書式のと一緒
そもそも
フィルタのカスタム数式ってどう使うの？？
って不安を感じる人もいるでしょうが、「条件付き書式」でカスタム数式を使ったことがあるなら、まったく問題ありません。
フィルタ と 条件付き書式のカスタム数式、どちらも 基本は一緒です。
条件付き書式については、カレンダー回で詳しく触れています。
そこで書いた 条件付き書式でカスタム数式を作成するポイントが ↓
・その範囲の開始セル（左上）で動く式を作る （範囲内で自動でスピる）
・式は TRUE,FALSEを返す形にする （TRUEの時に書式適用）
・条件セルではなく 書式設定するセル（色付けセル）の視点で式を作る
・条件セルと色付けセルがイコールでない場合は 絶対参照を利用
・一部の関数は 動かないので注意
・エラーが見つけにくので複雑な式はセルで作成・確認してからコピペ
これを踏まえて フィルタ（フィルタ表示）でカスタム数式を作成するポイントをまとめました。
■フィルタ（フィルタ表示）で カスタム数式を作成するポイント
・その列 の開始セル（一番上）で動く式を作る （範囲内で自動でスピる）
・式は TRUE,FALSEを返す形にする （TRUEのデータを抽出）
・条件でセル参照を使う場合は、必要に応じて 絶対参照を利用
・一部の関数は 動かないので注意
・エラーが見つけにくので複雑な式はセルで作成・確認してからコピペ
こんな感じでしょうか。
実際の例を見た方が理解しやすいと思いますんで、サンプル多めでいきましょう。
フィルタ表示でのカスタム数式 基本動作
まずは簡単な条件の式で カスタム数式の基本動作をみていきます。
フィルタ表示:基本のカスタム数式を理解する
超基本的な フィルタ表示での カスタム数式の例です。
性別（E列）が男であるデータに絞り込みたい場合のカスタム数式は
=E3="男"
このようになります。
これはポイントを踏まえて、以下のように作成しています。
・その列 の開始セル（一番上）で動く式を作る （範囲内で自動でスピる）
→ E列が対象なので 一番上の E3で動く式を作る
・式は TRUE,FALSEを返す形にする （TRUEのデータを抽出）
→ =E3="男"
この 男 という条件をセル参照（J1）にする場合
=E3=J1
このようにしてしまうと ↓ のように 正しく抽出されません。
これは
・条件でセル参照を使う場合は、必要に応じて 絶対参照を利用
が出来ていない為で、 条件部分を 普通に J1 としてしまうと
=E4=J2
=E5=J3
=E6=J4
.
.
.
といったように、E列に連動して 条件参照のJ2も下に相対参照してしまう為です。
J3以下は空白なので 性別が空白のセルで絞り込もうとしているわけですね。
正しく抽出する為には 条件である J1セルの行を 絶対参照として
=E3=J$1
このようにする必要があります。
ま、これはわざわざカスタム数式を使う必要もない条件ですが、基本の動作を理解する例として紹介しました。
ちなみにセル参照を 単に 一致や含むという条件で使うなら、カスタム数式ではなく 条件でフィルタ内の 「次を含むテキスト」や「完全一致するテキスト」を利用できます。
条件ボックスに セル参照の場合は =を頭に付けてセルを記載すればよいのですが、カスタム数式の時と同じく 絶対参照で記載する点には注意が必要です。
フィルタ表示は 条件で参照しているセルを編集しても即反映ではない
絞り込み条件が セル参照の場合、つい 条件セルを書き換えれば連動してフィルタが再計算されることを期待してしまいますが、残念ながら 条件セルを書き替えただけでは フィルタ表示の内容は変わりません。
条件セルと即連動するのは FILTER関数だけです。フィルタ表示の場合は 更新（画面の開きなおし）が必要になります。
ちなみに 画面更新で 最新に更新されるのは「フィルタ表示」の特性であって、通常のフィルタの場合は 画面更新をしても連動しません。
これは初回にも書きましたが、通常のフィルタは 一度フィルタを解除して再度フィルタをかけないと最新情報に更新されないのです。
これはさすがに面倒ですね。
フィルタ表示のカスタム数式は 別シート参照で INDIRECT不要
それでは、この条件部分が 他のシートのセルを参照する場合はどうなるでしょうか？
条件付き書式のカスタム数式の場合は、他のシートを参照する場合は特殊で INDIRECTを使う必要がありました。
しかし フィルタ表示のカスタム数式は、INDIRECTなしで普通に他シートの参照が可能です。
たとえば 別のシート2 A1 セルが 男となっていた場合、
=E3='シート2'!A$1
E列でこのようにカスタム数式をセットすることで、男だけに絞り込みが出来ます。
他のシートのセルも フィルタ条件に使えるなんて、超絶便利ですね。
じゃあ 別スプレッドシート（別ブック）からも参照できるのか？
他のスプレッドシートを参照する importrangeをカスタム数式に入れて他のスプレッドシートを条件に使うフィルタを検証してみましたが、こちらは機能しませんでした。
どうやら import系の関数は、条件付き書式のカスタム数式と同じく フィルタのカスタム数式でも使えないようです。
※ただし シート内に カスタム数式で利用している import系の関数を出力したセルがある場合は動く。でもこれだったら出力したセルを参照すればいいんで意味がないｗ
カスタム数式を使えば 複数列 での絞り込みも 1つの条件で可能だし OR条件もいける
少し応用例を見ていきましょう。
複数条件 + フィルタをかける列とは別の列を条件 にした場合です。
フィルタ（フィルタ表示）でカスタム数式を使うことで、通常なら複数列にそれぞれフィルタ条件をセットする必要があるケースも、1つの列に条件を入れて絞り込むことが出来ます。
上の画像は A列のフィルタに カスタム数式を入れて絞り込んでいますが、
=AND(E3="男",F3="A",REGEXMATCH(G3,"東京都"))
式の中身は このようになっています。
REGEXMATCH は 正規表現を扱う関数ですが 普通に「含む」という条件判定にも使えます。（FILTERの回で少し解説しています）
これは、
性別(E列) が男で
血液型(F列) がAで
住所(G列) に東京都を 含む
という条件の式です。
これを 関係のない A列に入れて カスタム数式で フィルタすることが出来るわけです。
ANDは 普通に 各列で条件設定すればよい話ですが、これを応用することで 通常のフィルタ条件では 難しい 各列の OR条件での絞り込みも 可能となります。
OR条件でのフィルタは、あまり使わないと思いますが。。
ちなみに FILTER関数での配列処理における AND条件、OR条件は AND関数やOR関数は使えず、
AND条件は 演算子 *（乗算）
OR条件は 演算子 +（加算）
に置き換えて式にする必要がありましたが、フィルタ表示 や 条件付き書式の場合は、その点は気にする必要がありません。
これは カスタム数式での配列処理が Arrayformula的ではなく BYROWやMAPなどの挙動に近い為と思われます。
便利だけど、計算の負荷は大きめなのかも。
文字列化している年齢を数値化して 20代で絞り込む
基本動作の確認の最後は、Excelの例では 作業列を使った年齢の数値化絞り込みを Googleスプレッドシートのフィルタ表示で、カスタム数式を使ってやってみましょう。
=SUBSTITUTE(C3,"歳","")*1
まず"歳"がついて文字列化している 年齢を数値データとするには 上記のようにすれば良いですね。 C3以降は自動で相対処理されます。
これが 20以上、29以下とすればよいので 普通に書くと
=AND(SUBSTITUTE(C3,"歳","")*1>=20,SUBSTITUTE(C3,"歳","")*1<=29)
ちと冗長でカッコ悪いので、LETを使ってまとめると
=LET(x,SUBSTITUTE(C3,"歳","")*1,AND(x>=20,x<=29))
こんな感じ。カスタム数式内でも LETは使えるみたいです。
新関数 LETがよくわからないよーって人は 過去noteを参照ください。
でも今回のケースは FILTER関数の時にも登場した、ある数字からある数字の間にあるかどうかを判定する関数 ISBETWEENを使うのがベストかなと。
=ISBETWEEN(SUBSTITUTE(C3,"歳","")*1,20,29)
もう少し複雑なケースでのフィルタ表示 ＋ カスタム数式の例を見ていきましょう。
フィルタ表示でのカスタム数式 応用例
ちょうど FILTER関数の回で使った「あるある事例」が幾つかあるので、これを フィルタ表示のカスタム数式に置き換えてみましょう。
〇歳以上 〇歳未満を セル参照 ＆片方が空欄の場合も対応させる
↑ まずは、この例ですね。先ほどの基本動作の最後にやった年齢式をアレンジして ↓ こんな式になります
=ISBETWEEN(SUBSTITUTE(C3,"歳","")*1,J$1,MIN(K$1,500),,false)
FILTERの時と同じように 更新後は セルの数値を年齢条件として絞り込みができているのがわかりますね。
上の場合は 上限のK1セルが空欄なので 30歳以上を全て抽出となっています。
※式の解説は FILTER関数 超応用例2 を参照ください。
別シートのリストと一致したデータだけを フィルタ表示で絞り込む
FILTER関数の時にやった リストと一致するデータの抽出をフィルタ表示でやってみましょう。
仮に 別シート（シート2）の A列に抽出したいデータがあった場合
=COUNTIF('シート2'!A:A,A3)
カスタム数式で これだけです。簡単ですね。
条件である シート2の A列の方は 本来は絶対参照にすべきですが、A:Aと列まるごと指定なので このまま使えます。仮に開始位置を 2行目からとする場合は
=COUNTIF('シート2'!A$2:A,A3)
といった具合に 絶対参照にする必要があります。
また FITER関数の時にもやりましたが、シート関数では 数値は 0以外は TRUE扱いとなるので COUNTIFの結果がそのまま 使えます。
2行で1データ をフィルタ表示で 絞り込む
フィルタ表示のカスタム数式を扱う上で、これはちょっと 悩ましい題材かも。
このように A2:C にある 2行で1データの リストから E1セルに入った文字を氏名に含む データを抽出したいというお題です。
=FILTER(A3:C,REGEXMATCH(A3:A,E1)
+REGEXMATCH(OFFSET(A3:A,-1,0,ROWS(A3:A)),E1))
FILTER関数の時は OFFSETでズラして サイズ調整したデータを REGEXMATCHで含む判定 を配列処理 という式を作りました。
今回はこれをフィルタ表示で実現したいのですが、カスタム数式の場合は勝手に相対参照で順に処理してくるので、
=REGEXMATCH(A3,E$1)+REGEXMATCH(A2,E$1)
このように 自分または1つ上のセルが E1の文字列を含んでいる を条件に記述できます。
でも残念ながら、こちらはうまく機能せず正しい 結果が得られません。
これは A2という フィルタをかけているセルが式の範囲に含まれている為です。
ちなみに 1つ上ではなく 1つ下を条件にすれば A2 セルを巻き込まないので問題なく作動します。
REGEXMATCH(A3,E$1)+REGEXMATCH(A4,E$1)
でも、やりたいことは 漢字氏名に E1の文字列 "中" を含む データ（2行1組）の絞り込み。つまり1つ上のセルを参照したい。でも1つ上を参照しちゃうとA2 という条件を入れるセルを巻き込んじゃう。。
じゃあどうするか？
式はそのまま
=REGEXMATCH(A3,E$1)+REGEXMATCH(A2,E$1)
これを使います。
で、これをなんと 別の列（B列）のフィルタに入れちゃいます。
そうすると、正しく動作するんですね～。変な感じです。
〇回以上登場する人だけを表示
カスタム数式を使うと フィルタ表示で 重複データを条件にすることができます。
重複は簡単なので、少しひねって 漢字氏名A列と 生年月日 D列が両方一致する人を同一人物と判定して、3回以上登場する人だけを表示してみましょう。
作業列を使う場合は、
=COUNTIFS($A$3:$A,A3,$D$3:$D,D3)
として 下にフィルした列を 3以上という条件でフィルタすればよいですね。
複数条件の一致 をカウントするのは COUNTIFS が便利です。
で、カスタム数式でフィルタする場合も、ほぼこの式がそのまま使えます。
=COUNTIFS($A$3:$A,A3,$D$3:$D,D3)>=3
これを条件にすれば
条件を満たすデータだけに絞り込めました。
FILTER関数の超応用例 フリーワード検索を カスタム数式で フィルタ表示に使う
フィルタ表示のカスタム数式応用例の最後は、FILTER関数の超応用例で登場した列指定なしのフリーワード検索に挑戦してみましょう。
K列にスペース区切りで入力した 複数ワードを 列指定せず、どこかの列に含む データに絞り込みたいって処理です。
ちなみに FILTER関数の超応用例で お題とした際は、列指定なしのフリーワード検索に加え
・タイトル行も出力すること
・タイトル行の上にヒット件数を表示すること
といった追加要件もあったので式が長くなりましたが、フィルタ表示でカスタム数式として使う場合は、絞り込みの部分だけなんで、コツさえつかんでしまえば割と簡単です。
↓ こんな式になります。
=ARRAYFORMULA(LET(keywords,IFERROR(SPLIT(K$1," "),"*"),
PRODUCT(COUNTIF(TO_TEXT(A3:I3),"*"&keywords&"*"))))
ポイントとしては 横方向の配列処理を動かすのに ARRAYFORMULAが必要って点でしょうか。
あとは FILTER関数で使った式とほぼ同じです。
もちろん 検索ワードを変更してから 一度更新をする必要がありますが、更新後は AB型で 東京在住の 女 に絞り込まれているのがわかります。
ちなみに条件にヒットした件数は 右下の表示で確認可能。
複雑なフィルタこそ フィルタ表示で保存・共有を
こんな複雑な式、自分じゃ作れないよー。フィルタで使えたとしても、解除したら 二度と戻せなそう。
こんな風に思うかもしれません。だから カスタム数式を使うような 複雑なフィルタ条件こそ、フィルタ表示 と組み合わせるべきなのです！
フィルタ表示であれば、カスタム数式を使った条件も 名前を付けて保存ができますし、関数の得意な人に作ってもらった複雑な条件のフィルタ表示が共有されて誰でも使うことができます。
フィルタ表示 ＋ カスタム数式 の超絶便利さは伝わったでしょうか？
違う世界線なら フィルタ解除 → 作業 → フィルタ戻すは出来るのか？
フィルタ表示が便利といっても、なかなか 古典的な Excel脳 の社員には浸透せず、普通のフィルタ で複雑な絞り込み（もしくは 複数の列で絞り込み）をしている共有シートがどうしても発生します。
これをやられちゃうと、若い社員が ちょっと非表示のデータを編集したいといっても、気軽にフィルタ解除するのが難しかったりするんですよね。
一度フィルタを解除しちゃうと二度と 同じ状態にできないんじゃないか？
こんな時でも大丈夫です。通常フィルタの一時解除に使えるテクニックが2つあります。
テクニック1. 複数タブ作業と 戻るボタンの利用 でフィルタを一時解除
1つ目は、複数タブ作業と 戻るボタンの利用です。
仮に 閲覧だけであれば、フィルタ解除して 全件表示で データ確認した後、「戻る」ボタンで先ほどセットしてたフィルタを復元という方法が使えます。
でも、見るだけなら いいですが、 フィルタを解除したあと 特定のセルを色付けしたり、編集したり 作業してから フィルタを戻したい。
こんな時はどうすればよいでしょうか？
戻るボタンを何度か押せば、当然フィルタは復元されますが、フィルタ解除後に行った編集作業も元に戻って、全てなかったことになってしまいます。これでは意味がありません。
こんな時に使えるのが Chromeの別タブで もう一つ同じスプレッドシートを開く二刀流 作業です。
手順としては
同じスプレッドシートを Chromeの別タブを使い 2つひらく
A.フィルタ解除専用画面でフィルタを解除
別タブで開いている B.作業用専用画面もフィルタが解除される
色付けや 編集などの作業を B.作業専用画面で行う
作業が終わったら再び A.フィルタ解除専用画面を開き
A.フィルタ解除専用画面で 「戻る」ボタンを押す
A.フィルタ解除専用画面で 実行した フィルタ解除だけ戻る
フィルタは元に戻り、作業した内容は保持される
こんな感じ。
スプレッドシートの「戻る」ボタンは 開いている画面単位（Chromeのタブ単位）での作業を戻すので、同じアカウントでも別タブで開けば それぞれのタブで実施した作業だけ戻せるっていうウルテクです。
まぁ、普通にフィルタ解除して作業だったら 2番目の方法を使うことをお勧めしますが、これが フィルタじゃなくて 手動で非表示になっている列や行を再表示させて作業をするケースだったら。
さぁ非表示を戻そうとした時に「あれ？どっからどこまでが非表示だったっけ？」 なんて経験がある人には 使えるスプシハックじゃないでしょうか。
テクニック2. フィルタ表示で フィルタを一時解除
もう一つは当然フィルタ表示を使う方法。
フィルタ表示シリーズの1回目の 超絶便利な理由には入れませんでしたが、フィルタ表示には、通常のフィルタを解除して表示できるという メリットもあります。
方法は？といっても、新規にフィルタ表示を発動するだけ。フィルタ表示の初期画面が、普通のフィルタ解除状態（全件表示）なんです。
とりあえず新規フィルタ表示作成 → なにもせず → 「全件表示」といった名前で 保存としておけば、通常のフィルタを解除することなく フィルタ表示（異世界）で、全件表示 した状態で作業することができます。
さらに その状態で作業をして、フィルタ表示をオフにすれば 作業は残り、フィルタは元通り。
異世界で修行をして得たスキルが現実世界でも使えて無双するって感じですねｗ
こんな使い方もできるわけです。
別タブ + 戻るボタンのテクって タイムリープっぽくない？
ちなみに、なぜここで１番の方法 別タブ+戻るボタンのマイナーテクニックを紹介したか？というと、これが フィルタ表示シリーズのタイトル分に「違う世界線へ！」というワードを入れた理由の１つだからです。
もちろん １番の理由は 同じシートでも 開いている各々が 違う表示ができる「フィルタ表示」という機能が、まさに 違う世界線 って感じだからですが、この 別タブ + 戻る ボタンも タイムリープ系の漫画とかラノベっぽいなって思いませんか？
過去に戻って（フィルタを解除して）、過去を改ざん（フィルタで非表示になったセルを編集）して、で再び今の世界（フィルタが元通りの世界）に戻ってくる。都合よく一部だけ戻せる（過去を修正できる）んですよね。
東リベや GIGANT など、見る人によっては矛盾が気になる、ややご都合主義な タイムリープ漫画みたいな感じで面白いなと。（個人的には 漫画なんでエンタメとして楽しければ 細かい矛盾は別にいいじゃないって思いますが）
一つのアカウント（自分）がスプレッドシートを扱う際に、別タブで複数開くことで、それぞれ違う世界線が生まれ、各々のシートで行った作業だけ戻せるってのは、不思議な感覚です。
余談ですが、この 「戻る」と「進む」（戻したのをやっぱなかったことに）という動作ですが、 mirは結構よく使うので ショートカットで利用しています。
戻る Ctrl + Z
進む Ctrl + Y
Googleスプレッドシートはもちろん、Excelや その他表計算以外のアプリでも結構一緒なんで、汎用性のあるショートカットです。
WebアプリやGoogleフォームなどで、テキスト入力 を戻す時にも使えるんで便利ですね。
まだ フィルタ表示は終わらない
フィルタ表示 + カスタム数式 をたっぷりやった上に、フィルタ解除を戻すテクニックなんて ネタを書いていたら、またまたかなりの長さになってしまったので、今回はここまでにしたいと思います。
先週予告した SUBTOTALを 組み合わせたネタをまだやってない！
というわけで、次回こそ最後にするつもりで、もう1回フィルタ表示ネタで書かせていただきます。
SUBTOTALという関数と組み合わせると、何が超絶便利なのか！？
フィルタ表示を 違う世界線 と呼ぶ ３番目の理由がそこにある！！