いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)QUERY関数でピボットテーブルを作る

 GoogleスプレッドシートのQUERY関数を使ってピボットテーブル(クロス集計表)を作成する方法についてです。
 メニュー操作で作成するピボットテーブルも便利ですが、こちらの方法でも手軽にピボットテーブルを作れます。

  • PIVOT句を使うことで、横方向の集計軸を設けて集計することができます。
  • GROUP BY句とPIVOT句を併用することで一般的なピボットテーブル(クロス集計表)を作成することができます。

手順

 QUERY関数を使ったピボットテーブル(クロス集計表)の例です。
 B~D列には店舗別・部門別の売上が記録されています。 
 これに対しF2セルに次のような式を入力するだけで、店舗別(縦軸)・部門別(横軸)の売上合計を出力するピボットテーブルができています。

f:id:accs2014:20210109160812p:plain:w700

 F2セル

=QUERY(B2:D12,"SELECT B,SUM(D) GROUP BY B PIVOT C",1)

 「SELECT ~ SUM(D)」により売上を集計するよう指定し、さらに「GROUP BY B」により縦軸とする列(店舗)と、「PIVOT C」により横軸にする列(部門)を指定しています。
 慣れないとSELECTの直後に「B」が必要で「C」が不要なところが気になるかもしれません。

 

f:id:accs2014:20210109160818p:plain:right:w500

 ちなみに、こちらはPIVOTなしでGROUP BYだけを用いた集計です。
 縦軸(店舗)を集計軸として売上が集計されるのがわかります。

 F2セル

=QUERY(B2:D12,"SELECT B,SUM(D) GROUP BY B",1)

 

f:id:accs2014:20210109160821p:plain:right:w600

 こちらはGROUP BYなしでPIVOTだけを用いた集計です。
 横軸(部門)を集計軸として売上が集計されています。
 これらの機能を組み合わせた結果として上記のようなピボットテーブルになっていることがわかります。

 F2セル

=QUERY(B2:D12,"SELECT SUM(D) PIVOT C",1)

 
 ところで、それぞれの軸に表示される項目は昇順での表示となります。縦軸の項目の降順での並べ替えは、PIVOTの後にORDER BY ~ DESCを置くことで可能です。
 しかし横軸については適当な方法がなく、QUERY関数内では解決できない模様です。

f:id:accs2014:20210109160826p:plain:w700

 F2セル

=QUERY(B2:D12,"SELECT B,SUM(D) GROUP BY B PIVOT C ORDER BY B DESC",1)

 
 さて、こちらのデータではB~E列に店舗別・部門別・月別の売上が記録されています。
 また、G2セルに入力した式により、店舗別部門別(縦軸)・月別(横軸)の売上を表示するピボットテーブルを表示しています。

f:id:accs2014:20210109160830p:plain:w750

 G2セル

=QUERY(B2:E12,"SELECT B,C,SUM(E) GROUP BY B,C PIVOT D",1)

 「GROUP BY B,C」により、店舗と部門の2つの項目を縦軸とする集計を行っています。
 なお、店舗・部門・月の組み合わせごとにデータが1つずつしかないので、ここではSUM関数の代わりにMAXやMINでも同じ結果になります。ただし「SELECT B,C,E」としてしまってはダメで、適当な集計関数を使う必要があります。

 
 さらにこちらでは、店舗別(縦軸)・月別部門別(横軸)の売上を表示しています。

f:id:accs2014:20210109160835p:plain:w780

 G2セル

=QUERY(B2:E12,"SELECT B,SUM(E) GROUP BY B PIVOT D,C",1)

 「PIVOT D,C」により、月と部門の2つの項目を横軸とする集計を行っています。
 ただし見出しが2行になることはなく、1つの行にまとめて表示されます。