きり丸の技術日記

技術検証したり、資格等をここに残していきます。

トップ > 技術 > Google スプレッドシートでフィルタした結果を元に操作する（SUBTOTALとFILTER）

Google スプレッドシートでフィルタした結果を元に操作する（SUBTOTALとFILTER）

技術

Google スプレッドシートで管理していた障害管理表を元に、フィルタ機能で自分が担当した障害をフィルタしていました。そのまま原因分析をしようとしたところ、自分の担当した件数以上にデータを取得してしまいました。

画面のフィルタを活かす、またはフィルタと同等の機能を使えるようにするのが、この記事の目的です。

環境

  • Google スプレッドシート

仕様

2021年10月1日から2021年10月31日までの東京の平均気温が載っているCSVを元にする。土日の平均気温を調べたい。

次の画像はゴールを指定しています。画像では曜日のB列で「値でフィルタ」をして「土」・「日」をフィルタした結果を表示しているため、土日しか表示されていませんが、平日のデータも存在します。土日の平均気温は18.07℃、10月平均気温は18.17℃です。18.07℃を求められることがゴールです。

f:id:nainaistar:20211118225558p:plain

フィルタした結果を元に計算したい

SUBTOTAL関数を使用することで、画面上でフィルタしている状態の平均や合計を簡単に計算できます。

第一引数を変更することで、最小値、最大値等を求めることもできます。

# 平均を求めるには1を第一引数とする。
# 第二引数には範囲を指定します。
=SUBTOTAL(1,$C$4:$C$34)

f:id:nainaistar:20211118225558p:plain

SUBTOTAL関数の詳細は公式ページを参照してください。

  • 1 - AVERAGE
  • 2 - COUNT
  • 3 - COUNTA
  • 4 - MAX
  • 5 - MIN
  • 6 - PRODUCT
  • 7 - STDEV
  • 8 - STDEVP
  • 9 - SUM
  • 10 - VAR
  • 11 - VARP

また、1桁のコードの先頭に10を追加するか、2桁のコードの先頭に1を追加すると非表示の値を無視できるそうです。101であれば非表示の値を無視したうえでAVERAGE, 110であれば非表示の値を無視したうえでVARの関数で処理します。

フィルタした結果を元に処理したい

集合関数（AVERAGE、SUM、MAX等々）を使用したい場合はSUBTOTAL関数を使用すれば、画面上でフィルタした結果を使用できます。

それ以外の操作をしたい場合、FILTER関数を使用するとフィルタした結果を元に処理できます。ただし、FILTER関数では画面上でフィルタした結果を使用することはできません。画面上で使用しているフィルタと同等の条件をFILTER関数ですべて指定する必要があります。

FILTER関数の第一引数に、フィルタする前の範囲を指定します。第二引数以降にフィルタする条件を指定します。第二引数、第三引数以降はそれぞれの条件をANDで結合してフィルタします。もしOR条件でフィルタしたい場合は、条件を+で結合するとOR条件でフィルタできます。

# AND条件でフィルタしたい場合、第二引数以降にパラメータを指定する
=FILTER(範囲, A条件, B条件)

# OR条件でフィルタしたい場合、+で結合します
=FILTER(範囲, (A条件)+(B条件))

また、列数は一致している必要はありませんが、行数は一致している必要がありますので注意してください。

今回のユースケースの場合、B列が「土」・「日」のレコードのC列の平均気温を取得したいので、FILTER関数でフィルタした後に集合関数のAVERAGEを設定しています。

=AVERAGE(FILTER($B$4:$C$34, ($B$4:$B$34="土")+($B$4:$B$34="日")))

f:id:nainaistar:20211118225558p:plain

終わりに

フィルタした結果を別シートにコピー&ペーストして、必要なレコードを抽出してもよかったのですが、毎回この手作業やりたくなかったので方法を知ることができてよかったです。

今後もSUBTOTAL、 FILTER関数を使って、余計な条件を省いた結果を容易に分析したいです。

…まぁ、本格的に分析したければ、SpreadSheetではなくBIツールを使った方がいいとは理解しています。とりあえず、さくっと確認したいときには使える関数です。

この記事がお役に立ちましたら、各種SNSでのシェアや、今後も情報発信しますのでフォローよろしくお願いします。

参考情報

f:id:nainaistar:20211118225731p:plain