違う世界線へ！ Googleスプレッドシートのフィルタ表示が超絶便利 4（SUBTOTALに祝福を）

mir

2023年6月11日 11:14

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

※よく使う 3と9くらいは覚えておいてもよいかも

第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)

※109はこのケースは9でもよい

式はこれだけ。

フィルタ表示でも 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))))

C2にこれを入れるだけ

でも、これやるなら 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関数も取り上げないと

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

5月データに絞り込みのカスタム数式

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関連のアップデートニュースも多いんで、その辺りの情報を挟んでいくかも。

この記事が気に入ったらサポートをしてみませんか？

違う世界線へ！ Googleスプレッドシートのフィルタ表示 が超絶便利 4（SUBTOTALに祝福を）

トリックスターな関数 SUBTOTAL

1. SUBTOTALでの集計はSUBTOTALの結果を除外する

2. SUBTOTALは 非表示セルを除外する （可視セルのみ集計する）

関数コードを10xで 手動非表示や閉じたグループも除外できる

非表示除外は行方向のみ。列方向には使えず

フィルタ表示と SUBTOTALで違う世界線へ

SUBTOTALを 使ってユーザー毎に違う結果を返す

保護 + フィルタ表示 + SUBTOTAL で 閲覧のみでも 計算できる

フィルタ後の 可視セルだけ SUMIFしたい

一番簡単なのは 作業列を用意して 行毎にSUBTOTAL