違う世界線へ！ Googleスプレッドシートのフィルタビュー（フィルタ表示） が超絶便利 4（SUBTOTALに祝福を）
2024/06/01 追記
当 noteは フィルタビューが フィルタ表示という名前だった時に書いたものです。2024年5月、仕様変更および名称変更があり、現在は一部画面構成など変更されている点があります。
変更点については、以下の noteでまとめております。
Googleスプレッドシートの 「フィルタ表示」 シリーズの4回目、今回で本当にフィルタ表示は 最後です。結構引っ張りましたｗ
そして、このフィルタ表示シリーズやってる間に フィルタ表示のアイコンが変わっちゃったｗ
フィルタの右の▼から開かないと使えなかったフィルタ表示ですが、新しいメニューバーでは独立したアイコンになりました。
Googleとしてもフィルタ表示を推していきたいっていう、推し活の一環ってことでしょうかｗ
独立アイコン化で 認知度アップして、活用が進むとよいなと。
前回の note は 「カスタム数式」を使った複雑な条件で絞り込む フィルタ表示を紹介。↓
今回はフィルタ表示とSUBTOTAL関数 を組み合わせた 超絶便利な 活用方法を紹介していきます。
トリックスターな関数 SUBTOTAL
SUBTOTAL関数で集計してる表をみると、お！少し表計算をたしなんでる人だなって感じますｗ（あくまで主観ですが）
SUBTOTAL関数は 他の関数とはちょっと違う独特の挙動で、将棋でたとえると 桂馬みたいな関数です。
つまり、使いこなせるとカッコいい！ってことです。
=SUBTOTAL(関数コード, 範囲1, [範囲2, ...])
関数コード
1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP
第1引数の関数コードでモードチェンジすることで、SUM関数だけでなく、MAXやMIN、AVERAGEやCOUNTA と同じように使えます。
これだけなら「普通にSUMやCOUNTAを使うのと一緒じゃん」ってなりますが、SUBTOTALならではの特徴が２つあります。
SUBTOTALでの集計はSUBTOTALの結果を除外する
SUBTOTALは 非表示セルを除外する （可視セルのみ集計する）
少し解説しておきましょう。
1. SUBTOTALでの集計はSUBTOTALの結果を除外する
シート関数の代表というか、初めの一歩といえる SUM関数ですが、以下のような 途中に小計（部門毎の合計）のある表だと、全合計を算出する際に 面倒だったりします。
このSUMを 全てSUBTOTAL の関数コード9に置き換えると
総合計の C13セルの式は
=SUBTOTAL(9,C3:C12)
このように 小計セルを含めて 範囲指定することができます。SUBTOTALでの集計は SUBTOTAL の結果であるC6,C9,C12 は無視されるってことです。
SUBTOTAL（小計）というだけあって、まさに小計算出の為の関数って感じですね。
2. SUBTOTALは 非表示セルを除外する （可視セルのみ集計する）
もう一つが今回のフィルタ表示に関連する特徴です。
SUBTOTALは フィルタで 非表示となったセルを集計から除外する（計算しない）という特徴があります。
上の画像だとフィルタで A列の日付を 6/7で絞り込んでいますが、その際 SUM関数を使った合計値 は 126のままフィルタ前と変化がありません。
一方、SUBTOTALを使った合計値は 126→ 33と フィルタで絞り込まれた結果（表示されているセル）だけの合計に 変化しています。
フィルタで絞り込んだデータだけ集計したいって時は SUBTOTAL
これは使えますね！！
関数コードを10xで 手動非表示や閉じたグループも除外できる
Googleスプレッドシートの公式の SUBTOTAの説明が少しわかりづらいんですが、関数コード の第1引数を例えば SUMを意味する 9 を 109 とすると、少し挙動が変わります。
フィルタによる絞り込みの場合は、違いはありません。表示セルのみを集計した同じ結果が返ります。
違いが出るのは フィルタではなく 手動での行の非表示の時です。
上のように10-18行を 非表示した際、関数コード 9のSUBTOALは変化がありませんが、109とした SUBTOTALは 非表示になったセルを計算から除外しています。
行グループの時も 同じく、109指定のSUBTOTAL なら 行グループが閉じられたら、非表示になったセルを計算から除外しています。
行グループで活用できるのは便利ですね。
非表示除外は行方向のみ。列方向には使えず
SUBTOTALは、非表示除外の対象となるのは 行のみってのに注意です。
残念ながら列方向の 手動非表示や グループ化して閉じて非表示になったセルは除外されません。（列方向のフィルターはありません）
列方向は対象外ってことですね。これは ExcelのSUBTOTAL関数も同じ仕様です。
公式の関数の説明は Excelの方が、「101-111」だと手動非表示に対応できる点や「非表示にした行」が対象である点など 明確に記載されてますね。
じゃあ、列方向の非表示を除外は出来ないのか？
ちなみに Excelには SUBTOTALの強化版といえる AGGREGATE関数 が存在します。
でも、このAGGREGATE関数も 行方向を前提とした集計関数なので、横（列方向）の非表示除外に対応しているわけではありません。
そもそも AGGREGATE関数は、昔の日本メーカーのボタンが多すぎるテレビリモコンって感じで、色々出来るけどわかりづらいんですよね。
普通に使うなら SUBTOTALで十分です。
さらに言えば AGGREGATE関数は、Googleスプレッドシートに 輸入されてない関数です。（2023年6月現在）
というわけで、残念ながら列方向の非表示を判定して集計する式は これといえるものが存在しません。
そもそもシート関数では厳しい気がします。。良い方法を編み出したら ドヤ顔で note書きたいと思いますｗ
SUBTOTAL関数についてのより詳しい説明は、ちょうど 窓の杜でも最近特集されたんで、そちらを参照ください。
フィルタ表示と SUBTOTALで違う世界線へ
このSUBTOTALとフィルタ表示を組み合わせると なにが超絶凄いのか？
まさに 違う世界線が生成出来るのが、この組み合わせなんです！
SUBTOTALを 使ってユーザー毎に違う結果を返す
上の画像を見てください。
同じタイミングで同じシートを開いているのに、 C2 セルの計算結果が違っています。
=SUBTOTAL(109,C5:C25)
式はこれだけ。
フィルタ表示でも SUBTOTALは、その画面で表示されているセルだけが合計の計算対象となるので、ユーザー毎（開ているフィルタ表示毎）に C2セルに違う結果が表示されるのです。
非常に特殊なケースですが、こんなことが起こります。
保護 + フィルタ表示 + SUBTOTAL で 閲覧のみでも 計算できる
これが活用できそうなのが、フィルタ表示シリーズ2回目の 保護との組み合わせで紹介した 閲覧のみリンク共有で ユーザー（閲覧者）に利用させる方法です。
Googleアカウントなしで 閲覧のみのユーザーでも、表を自由に絞り込みできて、さらに その絞り込みに応じた合計値を確認できます。
余談ですが、↑ このデータは Google BARDに 「サイゼリヤの代表的メニューと金額を 表形式で30品ほどください 」と依頼して得られた回答を 新たに実装された スプレドシートへエクスポート したものです。
ユーザー毎に 表示が変わるのは、SUBTOTALの入ったセルの結果だけではなく、その結果を使った 計算式や その結果を条件とした条件付き書式も ユーザー毎（開いているフィルタ表示毎）に 変わります。
不思議ですねー。
さらにこのフィルタ表示の SUBTOTALは、他のシートに式を入れた場合も 開いているシート毎に 違う結果を出力できます。
それぞれのウィンドで 元になる 予算表のシートは 違うフィルタ表示を適用しているんですが、フィルタ表示「異世界」ではない（黒い縁取りになってない）シートの計算結果が 違うってのは、さすがに混乱しますね。。
まどマギのような、因果律に逆らって 宇宙のルールが書き換えられた感がありますｗ
なんかもっと色々出来そうなんで、この応用ネタは 思いついたら書きたいなと。
フィルタ後の 可視セルだけ SUMIFしたい
フィルタ表示 + SUBTOTAL が便利なのはわかりましたが、上のようなケースの時はどうすればよいでしょうか？
フィルタで絞り込んだデータを、さらに 各営業課ごとに 売上金額を集計したいってケースです。
普通だったら SUMIFやSUMIFSを使うところですが、可視セルのみ集計対象というのがネックですね。
SUBTOTALには 他の条件を付加できないし・・・。
一番簡単なのは 作業列を用意して 行毎にSUBTOTAL
一番簡単なのは、行毎にSUBTOTALしちゃう方法です。
仮にE列を作業列とした場合、E16に
=SUBTOTAL(9,C7)
このようにして、下にフィルします。
これによって、E列 はフィルタで絞り込まれたあと
表示されいている 行 → C列の 売上を返す
表示されていない 行 → 0を返す
フィルタで非表示になった中身は直接は見れないので、他のセルに式で出力させてみましょう。
「6月」というフィルタ表示で 絞り込んだ中身を見ると、フィルタで表示されていない 5月の日付の行は、E列の数字が 0になっているのがわかりますね。
あとは、この E列を合計列として SUMIFで C2セルに
=SUMIF($B$7:$B$27,B2,$E$7:$E$27)
このように式を入れればOK。
とはいえ、作業列は最後の手段ですね。
一つの式で一発で出来る方法はないものか？
実は LAMBDA登場前は Googleスプレッドシートでは、この計算を 作業列無しでやる方法はありませんでした。
Excelは INDIRECTを使って SUBTOTALを配列処理できた
Googleスプレッドシートでは 出来ないと書いたのは、Excelでは前から 実現できたからで 意外にもスピル機能以前の Excelでも対応できたんです。
作業列不要で、C列に入れるのは
=SUMPRODUCT(($B$7:$B$27=B2)*
SUBTOTAL(9,INDIRECT("C"&ROW($C$7:$C$27))))
条件に一致した合計を求める部分は SUMIFではなく 式内で配列処理が出来るSUMPRODUCTを使っています。
新関数の登場で影は薄くなりましたが、SUMPRODUCTは やはり頼れるいぶし銀、転スラだと ハクロウ的 ポジション ですね。
それにしても後ろの部分
SUBTOTAL(9,INDIRECT("C"&ROW($C$7:$C$27))))
無理やり INDIRECT を 絡めることで セル毎にSUBTOTALを計算させる（さっきの作業列の動き）を実現していると思いますが・・・、いやー先人の知恵には本当に恐れ入ります。
ちなみに INDIRECTの挙動が Excelとは違う為、この式は Googleスプレッドシートでは動きません。
LAMBDAヘルパー関数で フィルタした後の SUMIFも 1行数式で出来る
先ほど書いた通り、Googleスプレッドシートでは BYROWやMAPといった LAMBDAヘルパー関数が登場したことで、ようやく この フィルタ後の 可視セルを対象とした SUMIF処理が１つの式で出来るようになりました。
C2に入れる式がこれです。下にフィルコピー。
=SUMPRODUCT($B$7:$B$27=B2,MAP($C$7:$C$27,LAMBDA(v,SUBTOTAL(9,v))))
ポイントとしては、
MAP($C$7:$C$27,LAMBDA(v,SUBTOTAL(9,v)))
の部分ですね。これが 先ほどの作業列と同じ1つ1つ SUBTOTALをした 配列を返す 式になります。MAPは BYROWでも問題ないです。
ちなみに SUMIFは 第3引数の合計範囲は セル範囲しか指定できません。
つまり今回のような 計算で返す配列を合計する場合はSUMIFが使えないので、やはりSUMPRODUCTを使うことになります。
ちなみに、下にフィルコピーじゃなくて 1つの式でやりたいって場合は、もう一つMAPで 計算を回せばよいです。
LETで MAP + SUBTOTALの計算結果 の配列を 変数化しておくとよいでしょう。
=LET(array,B7:C27,keys,B2:B4,
sumcol,MAP(index(array,,2),LAMBDA(v,SUBTOTAL(9,v))),
MAP(keys,LAMBDA(v,SUMPRODUCT(index(array,,1)=v,sumcol))))
でも、これやるなら Query関数で処理した方がいいですよね。
フィルタ後の 可視セルだけ QUERY集計したい
SUBTOTALとLAMBDAヘルパー関数を組み合わせた結果配列を、さらにQUERY関数と組み合わせて 可変集計することも可能です。
フィルタ表示に連動する 可変Query集計を作ってみる
カスタム数式を使った フィルタ条件、これにフィルタ表示連動の可変QUERY関数、SUMとSUBTOTALの合計を比較し フィルタ表示状態かを判定する関数、そして フィルタ表示のリンク機能、グラフを組みわせみました。
こんな感じで ダッシュボード風にも出来るんです。
A3セルに入れる式は
=LET(array,A7:C,
IFERROR(
HSTACK(
IF(
SUM(INDEX(array,,3))=SUBTOTAL(9,INDEX(array,,3)),,
TEXT(SUBTOTAL(5,INDEX(array,,1)),"M月")
),
TRANSPOSE(
QUERY({array,BYROW(INDEX(array,,3),LAMBDA(r,SUBTOTAL(9,r)))},
"select Col2,sum(Col4)
where Col2 is not null
group by Col2
label sum(Col4) ''"
)
)
)
,)
)こんな感じ、ちょっと欲張りすぎましたかね。
QUERY関数部分の解説
QUERY({array,BYROW(INDEX(array,,3),LAMBDA(r,SUBTOTAL(9,r)))},
"select Col2,sum(Col4) where Col2 is not null
group by Col2 label sum(Col4) ''" )
QUERY関数の集計はこの部分。
INDEX(array,,3) 、つまりこの場合はLET関数で変数化した array （A7:C)の3列目である C列を BYROW + SUBTOTAL でフィルタ後の非表示を 0とした 配列を生成。
↓ LET関数についてはコチラ
これを array （A7:C) と中カッコで 横連結したものを対象配列として、Query関数で グループ集計しています。
group by Col2 の部分で、Col2 つまり 2列目である B列の営業課で グループ化して、sum(Col4) で BYROW + SUBTOTAL の結果を グループ毎に集計しています。
グループ化の集計で 自動的にタイトル行が生成されてしまうので、
label sum(Col4) ''
これでタイトル行無しで結果を返しています。
さらに、これだと縦に長いんで TRANSPOSEで縦横変換。
QUERY関数は難しいと感じるかもしれませんが、とりあえずは こんな処理もできるだなーくらいの理解で良いです。
QUERY以外の部分の説明 と 環境設定
さらにフィルタ表示を判別して 〇月で絞り込んでるのを数式で表示させているのが
IF(SUM(INDEX(array,,3))=SUBTOTAL(9,INDEX(array,,3)),,
TEXT(SUBTOTAL(5,INDEX(array,,1)),"M月") )
この部分です。
SUMで合計した値とSUBTOTALで合計した値が一致してたら フィルタしてないと判断し空欄、違う場合はフィルタ表示中と判断し、SUBTOTALの 関数コード 5 （MIN) で取得した日付を TEXT関数で 〇月に変換としています。
これを HSTACKで横連結し、縦サイズのズレで発生する エラーセルを IFERRORで空欄にしています。
↓ HSTACK関数についてはコチラ
ちなみにフィルタの カスタム数式はこんな感じ。 A1:C1は 表示形式を 0月とした数値です。
=MONTH($A7)=A$1
Excelのフィルタだと 〇月っていう絞り込みが簡単に出来るんですが、Googleスプレッドシートの場合は カスタム数式を使って一工夫する必要があります。
こういうとこが、Googleスプレッドシートが なんでも関数でやらせようとする！関数に詳しくないと使いづらい！ハードルが高い！って声がでる要因なんですよね。。
フィルタ表示と SUBTOTAL、QUERY関数を組み合わせた 超応用例の紹介でした。
フィルタ表示関連の 残りのネタ
今回でフィルタ表示は最後なので、紹介できなかった関連ネタを最後に紹介しておきます。
フィルタ表示と 印刷・PDF化・コピーペースト
フィルタ表示状態の画面を印刷したり PDFとしてダウンロードしたりは普通にできます。
フィルタ表示状態のセル範囲をコピーして、他のシートに貼り付けた場合はきちんと可視セルのみが 貼り付け出来ます。
また、フィルタ表示状態のセル範囲に 貼り付けをした場合は、可視セルのみに貼り付けされ 非表示になっている行へは貼り付けされません。
これは Deleteによる削除も同様です。フィルタで絞り込んだデータにだけ一気に作業できるんで便利ですね。
フィルタ表示とスライサー
Googleスプレッドシートには フィルタ機能を より使いやすくした スライサーという機能があります。
スライサーも 便利ですし、UIって点では わかりやすくていいんですが、残念ながら フィルタ表示と 合わせて使えません。
フィルタ表示が スライサーで制御できたら 良かったんですけどね。
フィルタ表示と グラフ
基本的には グラフは フィルタによって非表示となったデータは含まれないので、自動でフィルタ表示の結果にグラフも連動します。
フィルタ表示（フィルタ）と連動させたくない！（非表示セルもグラフに反映させたい）という場合は、グラフエディタの「非表示のデータやフィルタ処理されたデータを含める」というチェックボックスで設定ができます。
フィルタ表示とGAS
この便利なフィルタ表示、GASから制御できたら便利だなと思うんですが、残念ながら フィルタ表示を扱えるメソッドは用意されてません。
↓通常のフィルタはGASから扱える
そもそも、フィルタ表示 自体が サーバー側ではなく、クライアント側で 生成する 異世界なんで、GASからの操作は無理なのかなと思ったら、
↑こちらを見つけて、 GASからは無理だけど、GoogleSheets APIを使うことで 各 フィルタ表示 の設定を JSON オブジェクトとして取得することは出来るってのがわかりました。
Gmail系もそうなんですが、GASから直接操作できないものも APIからいけたり、処理速度がそっちの方が早かったりってあるんですよね。。
コードを一部拝借して 以下のようにしてログ出力してみると
filterViews.forEach(filterView => {
console.log(JSON.stringify(filterView));
})
フィルタ表示のURLを作成するのに必要な fvid（フィルタビューID） や タイトル、フィルタ条件が取得できているのがわかりますね。
新規に フィルタ表示を作成が出来るかはわかりませんが、とりあえずフィルタ表示が保存されていれば、GAS から Sheets APIをたたくことで
このハイパーリンクの部分は 自動生成できそうです。
さすがに今回はやりませんがｗ
フィルタ表示をまずは使ってみる
全４回にわたって フィルタ表示を紹介してきました。
普通のフィルタで色々不便を感じている場合は フィルタ表示を使ってみるところから 始めてみましょう。
後半のカスタム数式や SUBTOTALとの組み合わせは、ちょっとハードルが高いと思う人もいるかもしれませんが、普通にフィルタ表示を使うだけなら簡単です。
そして 誰とも共有してない自分用スプレッドシートでも、フィルタ表示は活用できます。
フィルタ設定を複数保存できて、簡単に解除できて、更新時に再計算される。これだけでも超絶便利。
フィルタ表示の扉をあけて 違う世界線へ行ってみましょう！！
次回からは、また 単発の関数ネタあたりをやろうかなと。
最近、色々 GoogleWorkspace関連やBARD関連のアップデートニュースも多いんで、その辺りの情報を挟んでいくかも。
いいなと思ったら応援しよう！
