【スプレッドシート】QUERY関数group by句の使い方を集計関数別に解説

本記事では、スプレッドシートのQUERY関数で使えるgroup by句の使い方を解説していきます。

データを集計するのに便利なオプションですので、ぜひマスターしましょう。

QUERY関数のそのほかの使い方についてはこちらの記事で解説しています。

気になった方はチェックしてみてください。

QUERY関数のgroup by句とは

QUERY関数のgroup by句は、指定した列をグループ化し集計することができるオプションです。

「グループ化」がどういうことかと言うと、重複を排除してユニークな列に変換すると思ってもらえれば良いかと思います。

group by句の使い方は以下の通りです。

=QUERY(範囲,”select 列,集計関数(列) group by 列”,1)

集計関数とは

上記の使い方の中に、集計関数というものが入っています。

集計関数とは、指定した列のデータに対していくつかの方法で集計してくれる関数です。

集計関数には以下の5つの種類があり、それぞれ役割が違います。

関数名	説明	対応している形式	返ってくる形式
count()	データの個数を返す	全て（数値・文字列）	数値
sum()	データの合計値を返す	数値	数値
avg()	データの平均値を返す	数値	数値
max()	データの最大値を返す	全て（数値・文字列）	指定列の形式と同様
min()	データの最小値を返す	全て（数値・文字列）	指定列の形式と同様

group by句と集計関数は基本的にセットで使用すると覚えておきましょう。

group byで指定する列はselectで集計関数によって必ず指定する

「group by句でグループ化する列」と「select句の集計関数の列」は揃える必要があるので注意してください。

それでは、group by句の使い方を集計関数別に解説していきます。

【count関数】データの個数をカウント

まずはcount関数を用いたgroup by句の使い方です。

count関数ではデータの値をカウントすることができます。

以下は、実際の関数とその結果です。

=QUERY(B2:D12,”select C,count(C) group by C”,1)

上記の関数は、group by Cでグループ化したC列の値をcount(C)でカウントしています。

先述したように、グループ化とは重複を排除してユニークな値にすることです。

ですので、C列のユニークな値（今回で言うと「男性」「女性」）でそれぞれカウントしたことになります。

空白行以外を対象にする方法

出力結果を見ると、先頭に「0」という結果が表示されています。

これは空白行も一緒に集計しているため表示されているので、空白行以外で集計するよう修正してみましょう。

空白行を除外するにはwhere句で以下のように指定します。

where 列 is not null

これで「男性」と「女性」の個数のみ表示させることができました。

条件に一致したデータをカウントするCOUNTIF関数との違い

今回、男性・女性それぞれの数をカウントしました。

このようにデータをカウントする関数にCOUNTIF関数があります。

以下の画像がQUERY関数のgroup by句を使った集計とCOUNTIF関数を使った結果です。

このように出力結果は全く同じになります。

違いとしては、条件が増えたら自動で集計されるかどうかです。

QUERY関数のgroup by句は条件が増えても自動で集計されますが、COUNTIF関数は手動で条件を追加していかなければいけません。

【sum関数】データの合計値を求める

次にsum関数です。

sum関数はグループ化した列の合計値を求めることができます。

それでは実際の関数と出力結果をみていきましょう。

=QUERY(B2:D12,”select C,sum(D) where C is not null group by C”,1)

count関数で解説したように空白行は除外しています

C列の性別をグループ化し、グループ化したC列とその条件に一致したD列の合計を出力しています。

集計した見出しをlabel句で変更する

上記の結果の画像を見てみると、合計点数の見出しが「sum 点数」となっています。

集計関数を使うと「集計関数名 + 元の見出し」になってしまうので、label句を使って変更してみましょう。

label 列 見出し

=QUERY(B2:D12,”select C,sum(D) where C is not null group by C label sum(D) ‘合計点数'”,1)

集計関数の列の見出しを変更する場合は、label句に集計関数を指定するようにしましょう。

order byで昇順・降順に並び替え

現在の結果は合計点数が大きい順に並んでいますが、小さい順に並び替えたいと思います。

QUERY関数で並び替えを行うにはorder by句を使います。

order by 列 ソート方法

=QUERY(B2:D12,”select C,sum(D) where C is not null group by C order by sum(D) asc label sum(D) ‘合計点数'”,1)

こちらもlabel句で見出しを変更した時のように、列の指定は集計関数をそのまま指定しましょう。

【avg関数】データの平均値を求める

sum関数は合計値を求める関数でしたが、avg関数は平均値を求める関数です。

以下は実際の関数と出力結果です。

=QUERY(B2:D12,”select C,avg(D) where C is not null group by C”,1)

平均点を算出できましたが、デフォルトだと小数点以下まで表示されてしまいます。

このような場合はQUERY関数のformat句を使うことで表示形式を変更することが可能です。

format句で小数点第2位までに変更する

QUERY関数のformat句はセルの表示形式を変更できるオプションで、以下のように指定します。

format 列 表示形式

今回は小数点第2位まで変更してみます。

=QUERY(B2:D12,”select C,avg(D) where C is not null group by C format avg(D) ‘0.00’”,1)

正しく小数点第2位までの表示に変更されていますね。

【max・min関数】データの最大値・最小値を求める

最後は最大値を表示できるmax関数と、最小値を表示できるmin関数のご紹介です。

まずはそれぞれ解説していきます。

max関数とmin関数の使い方はどちらも同じです。

最大値・最小値のどちらも表示する

max関数とmin関数を別々で使用しましたが、併用することで最大値と最小値をまとめて表示させることが可能です。

=QUERY(B2:D12,”select C,max(D),min(D) where C is not null group by C”,1)

これまでご紹介したlabel句を使えば、それぞれの見出しを変更することもできます。

複数列をグループ化して集計する方法

これまでは、1つの列をグループ化してデータを集計する方法を解説してきましたが、複数の列をグループ化することも可能です。

①クラス→性別の順でグループ化した場合

複数の列をグループ化して集計する方法は以下の通りです。

=QUERY(B2:E12,”select D,C,avg(E) group by D,C format avg(E) ‘0.0’”,1)

ポイントはselect句で指定する列の順番と、group by句で指定する列の順番です。

この2つは揃えるようにしましょう。

②性別→クラスの順でグループ化した場合

複数の列をグループ化する際、指定する順番を変更すれば表示結果も変わります。

①ではクラス→性別の順番で指定しましたが、これを逆にしてみましょう。

=QUERY(B2:E12,”select C,D,avg(E) group by C,D format avg(E) ‘0.0’”,1)

性別→クラスの順に変更すると上記のような表示になります。

あくまで表示が変わるだけで集計関数で指定した集計結果が変わることはないので、どの順番で指定した方がみやすいかで判断すると良いでしょう。

まとめ

今回はスプレッドシートのQUERY関数でgroup by句を使って集計する方法をご紹介しました。

集計関数別に解説したので長くなりましたが、使い方を覚えておけば基本的な集計は簡単にできるようになります。

ぜひ、group by句を使ってみてください。