【Googleスプレッドシート】QUERY関数のGROUP BY!カテゴリ別の集計

大量のデータをカテゴリごとに集計したい場面はよくあります。QUERY関数のGROUP BY句を使えば、カテゴリ別の合計や平均を一つの式で取得できます。ただし、構文のルールを間違えるとエラーになりがちです。この記事では、QUERY関数のGROUP BY句を使ってカテゴリ別に集計する方法を、具体例を交えてわかりやすく解説します。

QUERY関数とGROUP BY句の仕組み

QUERY関数は、GoogleスプレッドシートにSQLライクなクエリを実行させる関数です。データ範囲を指定し、SELECT句で取得する列、FROM句で範囲(省略可)、WHERE句で条件、GROUP BY句でグループ化を指定します。GROUP BY句を使うと、指定した列の値ごとにデータがグループ化され、各グループに対してSUMやAVGなどの集計関数が適用されます。これにより、カテゴリ別の売上合計や平均単価などを一つの式で求められます。

カテゴリ別に集計する具体的な手順

ここでは、売上データをもとに商品カテゴリ別の合計金額を求める手順を説明します。データはA列にカテゴリ、B列に金額が入力されているとします。

1. 基本の式を入力する
   集計結果を表示したいセルに次の式を入力します。=QUERY(A:B, “SELECT A, SUM(B) GROUP BY A”, 1)。第1引数はデータ範囲、第2引数はクエリ文字列、第3引数はヘッダー行数です。
2. クエリ文字列の書き方を確認する
   SELECT句にはグループ化する列と集計関数を指定します。ここでは列A(カテゴリ)とSUM(B)(合計金額)を選択し、GROUP BY Aでグループ化します。列はラベルでも参照できますが、列名にスペースがある場合はバッククォートで囲みます。
3. 複数の集計値を同時に取得する
   カテゴリごとの平均や件数も欲しい場合は、SELECT句にAVG(B), COUNT(B)などを追加します。式は=QUERY(A:B, “SELECT A, SUM(B), AVG(B), COUNT(B) GROUP BY A”, 1)のようになります。
4. 条件を追加して絞り込む
   WHERE句を使えば、特定の条件を満たすデータのみを集計できます。例えば、金額が1000以上のデータに絞るには、=QUERY(A:B, “SELECT A, SUM(B) WHERE B>=1000 GROUP BY A”, 1)とします。
5. 結果を並び替える
   ORDER BY句を使って集計結果を並び替えられます。例えば、合計金額の降順に並べるには、=QUERY(A:B, “SELECT A, SUM(B) GROUP BY A ORDER BY SUM(B) DESC”, 1)とします。

実践例:売上データをカテゴリ別に集計する

実際のシートで試してみましょう。A1セルからC100セルに「カテゴリ」「商品名」「金額」のデータがある場合、カテゴリ別の合計金額を求める式は=QUERY(A1:C100, “SELECT A, SUM(C) GROUP BY A”, 1)です。結果は自動的にカテゴリ名と合計額の2列で表示されます。見出し行がデータに含まれている場合は第3引数を1以上に設定してください。

GROUP BY使用時の注意点

QUERY関数のGROUP BY句を使う際には、いくつかのルールを守る必要があります。間違えるとエラーになるので確認しておきましょう。

SELECT句にGROUP BYで指定した列以外の集計されていない列を含めるとエラー

SELECT句に指定できる列は、GROUP BYで指定した列と、集計関数で包んだ列だけです。例えば、=QUERY(A:C, “SELECT A, B, SUM(C) GROUP BY A”, 1)のように、B列(集計なし)を含めるとエラーになります。B列を表示したい場合は、B列もGROUP BYに含めるか、集計関数(MINやMAXなど)で包む必要があります。

列ラベルにスペースがあるときはバッククォートで囲む

列の見出しに「商品カテゴリ」のようにスペースが含まれる場合、クエリ内ではバッククォート(`)で囲みます。例えば、=QUERY(A:B, “SELECT `商品カテゴリ`, SUM(`金額`) GROUP BY `商品カテゴリ`”, 1)と書きます。そうしないと構文エラーになります。

第3引数のヘッダー行数がずれるとラベルがおかしくなる

第3引数に指定するヘッダー行数は、実際のデータの見出し行数を正確に設定します。例えば、データの1行目が見出しなら1、2行目が見出しなら2とします。間違えると、見出し行がデータとして扱われたり、結果のラベルがずれたりします。

GROUP BYで指定した列のデータ型に注意

日付や時刻の列をGROUP BYで使う場合、日付のシリアル値でグループ化されるため、見た目上同じ日でも異なるグループになることがあります。日付ごとに集計したい場合は、QUERY内で日付をテキストに変換するなどの工夫が必要です。

QUERYのGROUP BYと他の集計方法の比較

まとめ

この記事では、QUERY関数のGROUP BY句を使ったカテゴリ別集計の方法を解説しました。基本的な構文を覚えれば、SUMやAVGなどの集計を一つの式で効率的に行えます。さらに、WHERE句やORDER BY句を組み合わせることで、より高度なデータ分析が可能です。まずは簡単な例から試して、実際のデータで活用してみてください。