Googleスプレッドシート QUERY関数 超応用例 14（group by と pivot で集計）

Googleスプレッドシートの最強集計関数 QUERY関数について書いたnoteの第14回です。

👇これまでのQUERY関数シリーズは マガジンにまとめています。QUERY関数をハイレベル（廃レベル）に使いこなしたい人は必読です！

今回はクエリ文の select, where に続く第3の句  group by句、そして対になる第4の句 pivot句 をセットで学んでいきましょう。

$$
\begin{array}{lll}
\text{No}&\text{句}&\text{使用量}\\ \hline
\text{1}&\text{select}&\text{出力する列を指定 （類似関数 CHOOSECOLS）}\\ \hline
\text{\text2}&\text{where}&\text{条件でデータをフィルタ （類似関数 FILTER）}\\ \hline
\textbf{3}&\textbf{group by}&\textbf{行方向（縦）にグループ化（類似関数 UNIQUE）}\\ \hline
\textbf{4}&\textbf{pivot}&\textbf{列方向（横）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{5}&\text{order by}&\text{データを並び替え（類似関数 SORT）}\\ \hline
\text{6}&\text{skipping}&\text{〇行おきにデータを出力する}\\ \hline
\text{7}&\text{limit}&\text{〇行目までデータを出力}\\ \hline
\text{8}&\text{offset}&\text{〇行目からデータを出力}\\ \hline
\text{9}&\text{label}&\text{列のラベル名を変更する}\\ \hline
\text{10}&\text{format}&\text{特定の列の値を書式設定}\\ \hline
\text{11}&\text{options}&\text{追加オプションを設定。（使わない）}\\ \hline
\end{array}
$$

いよいよQUERY関数の真骨頂、集計に入っていきます！今回は基礎編です。

前回のnoteはQuickTips GoogleスプレッドシートをGASで操作する時に便利なメソッド offset() について書きました。

group by句、pivot句の理解

まずは group by句、pivot句 が、それぞれどのようなもので、何が便利なのかを理解しましょう。

実データを操作しながら理解していくのがおススメです。

以下のサンプルデータをA1セルに貼り付けて、販売実績miniという名前のテーブルに変換して利用ください。

営業担当	商品	売上金額
田中	B	¥1,200,000
田中	C	¥2,500,000
山田	A	¥1,600,000
佐藤	B	¥900,000
山田	C	¥700,000
佐藤	B	¥1,800,000
山田	A	¥1,500,000
佐藤	A	¥2,000,000
田中	B	¥2,200,000
田中	C	¥600,000
佐藤	C	¥800,000
山田	A	¥3,000,000

テーブル化することで 表の見出しを含めた範囲をテーブル名を使って

販売実績mini[#ALL]

このように構造化参照で取得することが出来ます。

基本解説の QUERY関数の第1引数は、基本的には 👆を使います。

Googleスプレッドシートのテーブル機能について知りたい方は、以下のマガジンにまとめているので、👇を参照ください。

group by句、pivot句は 集計表を生成する為の句

QUERY関数の group by句、pivot句は、クエリ言語の集計関数と組み合わせて 集計表を生成する為の句です。

これまで登場した select句、where句 は、

このように類似するシート関数がありましたが、group by句 や pivot句を単体で代用するシート関数は存在しません。

UNIQUE関数とSORT関数、これに集計関数の処理（COUNTIFS関数やSUMIFS関数）を組み合わせた複雑な処理をする句と言えます。

Googleスプレッドシートだと 機能の  ピボットテーブルが一番近いでしょう。

Excelだと新関数の GROUPBY関数、PIVOTBY関数が、QUERY関数のgroup by句、pivot句と近いイメージです。（もしかすると GoogleスプレッドシートのQUERY関数うらやますぃーって声が多くて導入されたのかもｗ）

group by句：指定した列の要素を「縦にグループ化」して集計する為の句

group by は 指定した列を「縦に」ユニーク（一意）にした上で、集計関数と組み合わせることで、そのグループ（項目）ごとに集計（合計や個数、平均値など）する為の句です。

「グループ化」の部分は、UNIQUE関数のような処理だと思ってください。

さらに言えば、ユニーク化と合わせて昇順で並び替えも行うので、SORT関数的な一面もあります。

たとえば👇は、

👆この式の group by Col2 で2列目（商品）を

この3つにグループ化（UNIQUE）して、値を昇順に「縦方向」に並び替え（SORT）して、sum(Col3) で 3列目（売上金額）を 商品毎に 合計（SUMIF）したグループ集計表を出力しています。

この縦方向のグループ集計が出来るのが group by句です！

pivot句：指定した列の要素を「横にグループ化」して集計する為の句

一方 pivot句は 指定した列を「横に」ユニーク（一意）にした上で、集計関数と組み合わせることで、そのグループ（項目）ごとに 集計（合計や個数、平均値など）する為の句です。

ちなみに1列目の 営業担当 を pivotでグループ化して集計する

👆この式ですが、見出し部分の結果が

佐藤 ▶ 山田 ▶ 田中

となっています。

これが昇順？と疑問を持つかもしれませんが、この日本語（漢字）の昇順の並び順がイメージと違う問題は、QUERY関数の仕様です。

こちらについては order by句の回で取り上げます。現段階では仕様だと思っておいてください。

細かい点は後で触れますが、ざっくりと以下のようにまとめられます。

group by + pivot でピボット集計（クロス集計）ができる

pivot句は単体でも利用できますが、

pivot句は group by句と組み合わせて、縦横のピボット集計表（クロス集計表）を一撃で生成することで真価を発揮します。

👆 たとえばコチラの式では

販売実績mini[#ALL] に対して、

group by Col2　・・・　縦方向に 2列目（商品）をグループ化

pivot Col1　・・・　横方向に1列目（営業担当）をグループ化

した上で、

sum(Col3)　・・・　 3列目の「売上金額」の合計

を集計しています。

ピボット集計することで、たとえば 田中が売った B商品の売上合計が サクっと 3,400,000 であることが確認できます。

このように 元データを

• グループ化（縦方向・横方向）

• 自動並び替え（昇順）

• 集計（合計や個数、平均、最大値・最小値）

した集計表を QUERY関数だけで簡単に生成することが出来る。

これがQUERY関数が チート関数と言われる所以であり、この処理の要（かなめ）となっているのが group by句、pivot句 ってわけです。

group by句・pivot句 を利用する際のルール（お作法）

QUERY関数で group by句、pivot句 を使う際には幾つかの守るべきルールがあります。

1. 必ず集計関数がセットで必要となる

2. pivot句は 集計対象の列と同じ列を指定できない

3. group by句で指定した列は通常はselect句で指定する、
   pivot句は select句での指定が不要

4. where → groupu by → pivot 記述の順番に注意

5. 複数の列を指定する際は カンマで区切る（gropu by句）
   複数の列を指定する際は カンマで区切り（pivot句）

6. 複数の集計関数をセットで利用できる

このルールに則ってクエリ文を記述しないとエラーになったり、見づらい表になってしまうので注意が必要です。

それぞれ見ていきましょう。

1．必ず集計関数が必要となる

group by句 や pivot句は、単体では使用出来ません。必ず集計関数（集計結果）とセットで利用する必要があります。

つまり select句で 集計関数の指定が必須ってことです。

クエリ文の中で使える集計関数は、select句の回で紹介した

この5つです。

select句合わせ技1. 集計関数で列を集計

集計関数を指定せず group by句 や pivot句を 利用すると、 #VALUE! エラーが返ります。

ちなみにエラーメッセージの

 この中で登場する AGGは、Aggregate（集計）を短縮したもので 集計関数を意味しています。

一方、集計関数を select句で指定しても、必ずしもgorup by句や pivot句は必要ではありません。

group byやpivot句無しで集計関数を利用した場合は、全体を一つのグループと見なし、

count(Col2) で 2列目全体の値の入っているセルの個数
sum(Col3) で 3列目全体の数値の合計

を返します。

集計関数を単体で使う方法については、QUERY関数超応用例シリーズ5でも触れています。

select句合わせ技1. 集計関数で列を集計

2. pivot句は 集計対象の列と同じ列を指定できない

集計関数を利用する列は、group by で指定した 列と同じ列でもOKです。

たとえば上の式は、列2（商品）だけに着目して

select Col2,count(Col2) group by Col2

で、countと組み合わせて2列目の商品のグループ毎の個数（登場回数）を集計しています。

この1つの列をグループ集計するQUERY関数の式は結構使います。

しかし、pivot句の場合は

このように 集計関数と同じ 2列目 （Col2）を pivotで指定することが出来ません。

AGG_IN_SELECT_NO_PIVOT

集計関数で使った列はPIVOTで指定出来ないってことです。

👆ほぼ同じことをしたい似たような式なのに、上はエラーで下は機能するのは、これが理由です。

Q1. 横方向に 2列目（商品）毎の個数を集計するにはどうすればよいか？

じゃあ 👆 の画像のような 2列目（商品）毎の個数を 横に集計した表は、QUERY関数では生成できないのか？

いいえ、ちょっと工夫すれば出来ます！

これを1つ目のお題とします。画像の右側の集計表を生成する式を考えてみましょう！

↓↓
回答はここから。
↓↓

A1. 横方向に 2列目（商品）毎の個数を集計する

回答です。

Col2がそのまま使えないなら、Col2を影響がない形で加工したものを pivot句で使えばよいってことです！

ここで使えるのが select句の回で学んだ、同じ列が1回しか指定できない制限を回避する対処法です。

QUERY関数で同じ列（文字列型）を複数回 指定したい

対象の列が文字列型で、日本語もしくは アルファベットが大文字（または小文字）に統一されていれば、スカラー関数の upper() もしくは lower()を使って

upper(Col2)

とすれば、集計関数で使った Col2とは別モノとして pivot句で指定が出来て、かつ元々商品はアルファベットの大文字なので 影響無しってことです。

group by句の集計をTRANSPOSEで縦横変換って方法を考えた人がいるかもしれませんが、

生成された見出しが左側に残ってしまうので、これを外す手間がかかります。（やり方はあります）

同じ列を使ってエラーが出た時の対処法（スカラー関数で加工）は、知っておくと便利です！是非習得しましょう。

3. group by句で指定した列は通常はselect句で指定する、pivot句はselect句での指定が不要

group by句で指定した列は、select句で必ずしも指定する必要はありません。（エラーになりません）

ただ、これだと

👆この合計は「なんの?」ってなりますよね。

というわけで group by句で指定した列は select句でも指定して

何のグループの集計か？を明示するのが一般的です。

※group by句で指定した列を selectで指定しないテクニックは超応用例で使うことがあります

逆に 集計を行う場合は、select句で列を指定した場合は、必ず group by でその列をグループ化しなければなりません。こちらは必要条件ってことです。

一方、pivotで指定した列はselectでは指定しません。

pivot句の場合は selectで同じ列を指定しなくても、集計した値に対応するグループ化した列の値が、自動で見出し行として横に展開されます。

混乱しやすいんですが、group by句と pivotの大きな違いの一つです。

なお、pivot句を利用した時に集計関数以外の 列を select句で指定した場合は、

グループ化しないとエラーとなります。

当然 select句で 指定した列を グループ化すれば、group by と pivotの両方を使っているので

ピボット集計となります。

QUERY関数で「集計」を行う（集計関数を使う）時の 列の select ルールをまとめると

となります。

4. where → groupu by → pivot 記述の順番に注意

これはQUERY関数シリーズの中で 何度も触れていますが、第2引数のクエリ文の中で登場する「句は」 記述する順番が決まっています。

QUERY関数超応用例3 クエリ文は 言語句の順番が大事

group by と pivot を両方使ってピボット集計をする際は、必ず

group by が先で pivot が後

です。

これを逆に書くと #VALUE!エラーとなります。

同様に where句で併用してフィルタしてから集計する場合も、select句の後ろ、group by句の 前に where句を記述する必要があります。

エラーメッセージで Encountered となるケースの大半が、この句の記述順のミスです。

5a. 複数の列を指定する際は カンマで区切る（group by句）

group by句や pivot句で グループ化出来る列は、1つだけではありません。

必要に応じて2列、3列と複数の列をグループ化することが可能です。

複数列をグループ化する際は、select句と同じように カンマで区切って記述します。

グループ化の優先順位は group by句で指定した順になります。

よって select句の並び順と group by句の並び順を 揃えなくてもエラーにはなりませんが、基本的には 

複数列を group by句で指定した場合は、それと同じ順番で select句でも指定する

という理解でOKです。

この複数列のグループ化に関して、たまに、👇のように

なんて質問を見かけますが、これは無理です。

QUERY関数は存在しない組み合わせのグループは出力できません。

後述しますが、2列の組み合わせならこの存在しなく組み合わせも網羅できる ピボット集計がおススメです。

5b. 複数の列を指定する際は カンマで区切る（pivot句）

一方 pivot句で複数列を指定した時は、挙動が少々違います。

まず、pivot句で複数選択した列は選択した順に、元データに存在するパターンがグループ化（一意化）され、 カンマ切りで連結されて見出し1行に集約されます。

複数列をpivotで指定すると、どんどん 横に長くなっていくってことです。

これは

QUERY関数の出力は、必ず見出し行は「文字列型」の1行データとなる（もしくは見出し行無しとなる） 仕様 

である為です。

また、pivot句で複数列を選択した場合も

存在しない組み合わせのグループは出力されません。

【2列グループ化】ピボット集計のススメ

このように2つの列のグループ集計をgroup byのみ、pivotのみで処理すると、少々見づらい集計表になることがあるのと、存在しない組み合わせが表示されないといったデメリットがあります。

これを解決するのが、group by + pivot を組み合わせた ピボット集計です。

このようにQUERY関数で ピボット集計することで、 山田, B や 田中, A が元データには存在しない（実績がない）ことが明示されます。

ただし、残念ながら 0が入るわけではありません。

他の関数を組み合わせて 0を入れるのは少し難しいので、次回以降にお題で取り上げるとして、簡単にわかりやすくするだけなら 条件付き書式で空白セルをグレーにするのが良いでしょう。

6. 複数の集計関数をセットで利用できる

group by句、pivot句の 集計では、2つ以上の複数の集計関数を同時に利用することも可能です。

👆上の式では、営業担当、商品の順でグループ化して、グループ毎の商品の売上の 最小値 と 最大値 の両方を集計しています。

これは select句で指定した順になっているので、非常にわかりやすいですね。

一方 pivot句で 複数の集計関数を使った場合は、非常に見づらくなります。

👇このようにカンマ区切りで グループ化された値の見出し行に、さらに 半角スペースを挟んで集計関数も見出しに表示されるようになり、

集計結果は ひたすら横に長く表示されて、かなり見づらい表に・・・。

group by句を使わない pivotのみの集計は 見出し1行、集計 1行

となる仕様なんです。

さらに

グループ化の優先順位は select句で指定している 集計関数が優先度が一番上になってしまう為、

今回の場合だと、まず min（最小値）の各グループの値、その後で max(最大値）の各グループの値、という表示順となります。

これは残念ながらコントロールは出来ません。だから、

グループ毎の集計値（max,min）を並べて交互に表示させたい！とか、

👆 このように集計関数を2行に表示したい！といったことは、

QUERY関数単体では対応できないってことです。

※これらはQUERY関数に他の関数を組み合わせて生成することが可能です。次回以降にお題で触れたいと思います。

ピボット集計で2つの集計関数を使った場合も同様に、

列は 集計関数が優先でグルーピングされる為、ちょっと見づらい表になってしまいます。

QUERY関数で 複数の集計関数を使う場合は、group by句のみで記述するのがおススメです。

group by句、pivot句の使い方、記述のルールの基本は理解出来たでしょうか？

group by句・pivot句 ちょい応用例

最後に学んだことをベースに ちょい応用例のお題にチャレンジして、今回は終わりとしましょう。

Q2. 第1引数を A:Cで指定して ピボット集計したい

データは 冒頭のサンプル 販売実績mini と一緒ですが、実務ではテーブルを使わず 範囲を A:C のように指定するケースも多いです。

この範囲 A:C をQUERY関数の第1引数 として、右のような 縦を営業担当、横を商品として 売上の平均値をピボット集計したい場合、どのような式を記述すればよいでしょうか？

考えてみましょう！

↓↓
回答はここから。
↓↓

A2 .第1引数を A:Cで指定して ピボット集計する

回答です。

これは簡単でしたかね？

もし、見出しの次に 👇このような 空白行・空白列が出来てしまった場合は

where句での空白除去の記述が漏れています。

group by句や pivot句は 空白も一つのグループとして集約してしまいます。これはUNIQUE関数の仕様と一緒ですね。

空白はそのまま 1つのユニークな値として出力される

さらにQUERY関数内の並び替えルールでは、空白（Null）は昇順でもっとも前（上）にくる仕様となっています。これはSORT関数とは違うところ。

空白と空文字の SORT関数での扱いの違い

つまり where句で空白を除外しておかないと、集計行（集計列）の先頭に余計な空白が表示されてしまうってことです。

空白除去は where句の回で学んだ

where Col1 is not null

一択ですね。

並び順ルールに従って、これを select句と group by句の間に記述します。 集計関数は 平均値なので avg()を使えばOK。

ここでつまずいた人は、where句の回を見直しましょう！

where句で空白データを除外する

Q3. 1列の数値データの数字ごとの個数（登場回数）を横に集計したい

A1:A18 に見出し行と1～5までの数字がランダムに入っています。

これを右側のように 各数字毎の個数（登場回数）を横方向に集計したい場合、QUERY関数でどのような式を組めばよいでしょうか？

データは以下をA1に貼って利用ください。

数字
1
2
3
5
4
2
4
1
3
5
3
4
2
4
1
1
3

考えてみましょう！

↓↓
回答はここから。
↓↓

A3. 1列の数値データの数字ごとの個数（登場回数）を横に集計する

回答です。

ポイントは pivot Col1 + 0 　の +0 です。

これは お題1の pivot句は集計関数で使った列を指定できない縛りを回避する方法の別パターンですね。

そのまま

=QUERY(A1:A18,"select count(Col1) pivot Col1")

と書くとエラーになってしまうので、

Col1を影響がない形で加工したいのですが、数値なので upper()やlower()といったスカラー関数は使えません。

ここで使えるのが算術演算子です。

数値が変わらなければよいので、 +0 とすればOKです。

Q4 .複数行・複数列に散らばった値の個数を集計したい

最後のお題です。👆の左側のような日付ごと、役割ごとの当番表があった場合、各人が何回役割をこなしているか？（値がそれぞれ何個あるか？）をQUERY関数で集計するにはどのような式を書けばよいでしょうか？

つまり、右側の集計表を生成したいってことです。

データは以下をA1セルにコピペしてご利用ください。

	役割A	役割B	役割C	役割D	役割E
8/1	山田	佐藤		佐藤	田中
8/2		山田		田中	
8/3			山田		
8/4	山田	田中		佐藤	
8/5					佐藤
8/6		田中	田中		
8/7		山田	佐藤		佐藤
8/8	山田				
8/9		田中		山田	
8/10		山田			
8/11	佐藤				
8/12		佐藤	佐藤	田中	田中
8/13			佐藤	佐藤	
8/14	山田		山田		

今回は 日付の列や役割の行を気にする必要はありません。

考えてみましょう！

↓↓
回答はここから。
↓↓

A4. 複数行・複数列に散らばった値の個数を集計する

回答です。

まず今回は1列目、1行目の 行・列の見出しは無視してOK。対象となる範囲は B2:F15だけです。

これを QUERY関数で扱えるように 1列のデータにします。

ここで使えるのが TOCOL関数。合わせて第2引数を1として、空白除去もしちゃうとよいでしょう。

ここでの並び順は気にする必要はありません。

あとはこの生成された1列データを対象に グループ化して個数をカウントすれば良いので、

"select Col1,count(Col1) group by Col1"

とすれば良いですね！

これ、新関数 TOCOLの登場前はどうしてたか？というと、少し式が長くなりますが

👆こんな式で対応してました。

FLATTEN関数で1列にした上で、where Col1 is not null で空白除去します。

この集計処理が作業セル無しで一発で出来るんですから、QUERY関数は当時は今以上にチート扱いでした。

このように一見 QUERY関数を使うのが難しそうなデータであっても「各グループ毎の個数を集計する」という最終的にQUERY関数で処理できそうな出力結果を作るケースでは、

どう加工してQUERY関数に渡せばよいか？
QUERY関数とどの関数を組み合わせればよいか？

を考えるのがポイントです！

次回 group by句、pivot句の応用へ

今回は QUERY関数の group by句、pivot句の 基本を学びました。

お題の中で特に説明なくしれっと、

pivot upper(Col2)

や

pivot Col1+0

といった記述を使ってますが、group by句や pivot句は 単純な列指定だけではなく、スカラー関数や算術演算子と組み合わせて使えます。

次回はこの辺りの解説を含めた応用的な使い方、そして超応用例を学んでいきたいと思います！