【QUERY関数】group by句で複数の列をグループ化して集計する

QUERY関数13アイキャッチ

こんにちは!もり(@moripro3)です。

GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています!

前回の記事では、group by句とmax,min関数を使用して、列の最大値と最小値を算出する方法を紹介しました。

【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第十二回目は、group by句とmax関数, min関数で列の最大値・最小値を求める方法です。数値データだけでなく、日付・文字列データの比較も可能です。

直近数回の記事で、列の値をグループ化して集計するためのgroup by句を使ってきました。

  • (例)列xをグループ化し、項目ごとの「合計値」を求める

これまでの記事で紹介してきたのは、1つの列のみをグループ化する使い方でしたが、実務で大量のデータを扱う場合、複数の列をキーにして集計することがありますよね。

そこで今回は、group by句で複数の列をグループ化して集計する方法を紹介します。

それではみていきましょう!

スポンサーリンク

(前回までのおさらい)列をグループ化するgroup by句

group by句とは、複数行にわたる値を集計するための句です。

group by句で指定する列は、このようにselect句で指定されているか、

select 列 group by 列

もしくは、select句で、集計関数によって集計されている必要があります。

select 集計関数(列) group by 列

前回までのおさらいもかねて、40人の生徒表をサンプルに使用して、列の項目ごとの人数を集計してみます。
query13-1

1つの列でグループ化する

group by句を使用して、列ごとの人数をカウントしてみましょう。

  • 性別ごとの人数
  • 血液型ごとの人数

まずは性別ごとの人数を求めてみます。B列「性別」でグループ化して人数をカウントする記述です。

=query(A:C,"select B,count(B) where B is not null group by B",1)

query13-2-2

 

つぎに、血液型ごとの人数を求めます。C列「血液型」でグループ化して人数をカウントする記述です。

=query(A:C,"select C,count(C) where C is not null group by C",1)

query13-3

 

このように、1つの列のみをグループ化する記述の場合、「性別」または「血液型」のいずれかしか求めることができません。

group by句で2つの列を指定することで、「性別・血液型別の人数」を集計することが可能です。次の項で、グループ化の考え方とクエリの記述方法を紹介します。

group by句で複数列をグループ化する

group by句は、複数の列をグループ化して集計することが可能です。select句とgroup by句それぞれに、カンマ区切りで列を列挙します。

select 列1,列2,列3 … group by 列1,列2,列3 …

さきほどの40人の生徒表を使用して「性別・血液型別」の人数を集計してみます。まずは、40人の生徒を図でイメージしましょう。性別は、男性=青・女性=ピンクで、血液型は記載のとおりです。

query13-4

グループ化の順序は自由に指定することが可能です。この40人を、2通りの方法で集計してみます。

  • パターン1.性別 → 血液型
  • パターン2.血液型 → 性別

パターン1.性別→血液型でグループ化

まずは、「性別 → 血液型」ごとに人数を集計するパターンです。select句で1~3の順番で指定、group by句で1,2の順番で指定します。

  1. B列(性別)
  2. C列(血液型)
  3. C列(血液型)をcount関数で集計

※select句で指定する列の順番(B,C)と、group by句で指定する列の順番(B,C)は一致させます。

=query(A:C,"select B,C,count(C) where C is not null group by B,C",1)

query13-5

【考え方】
下記の2ステップで、40人の生徒を分割しています。

  1. 「性別」でグループ化する(男, 女)
  2. 1のグループを、さらに「血液型」でグループ化する(AB, A, B, O)

query13-11

パターン2.血液型→性別でグループ化

つぎに、「血液型 → 性別」ごとに人数を集計するパターンです。select句で1~3の順番で指定、group by句で1,2の順番で指定します。

  1. C列(血液型)
  2. B列(性別)
  3. B列(性別)をcount関数で集計

※select句で指定する列の順番(C,B)と、group by句で指定する列の順番(C,B)は一致させます。

=query(A:C,"select C,B,count(B) where B is not null group by C,B",1)

query13-7

【考え方】
下記の2ステップで、40人の生徒を分割しています。

  1. 「血液型」でグループ化する(AB, A, B, O)
  2. 1のグループを、さらに「性別」でグループ化する(男・女)

query13-12

グループ化する順序の指定

2つの集計パターンを紹介しました。

  • 性別 → 血液型
  • 血液型 → 性別

グループ化の順序が異なっても、集計結果は同じになります。それでは、どのようにグループ化の順序を指定すればよいか迷いますね。

オススメは、大カテゴリ → 小カテゴリと、項目数の少ない列を優先してグループ化する方法です。

  • 「性別」は2種類(男, 女) ← 大カテゴリ
  • 「血液型」は4種類(AB, A, B, O) ← 小カテゴリ

40人の生徒表の場合、性別の方が種類が少ないので、性別 → 血液型の順序が良いでしょう。

ただ、必ずしもという訳ではないので、業務要件や、集計目的に応じて、列の優先順位を考えてみてください。

まとめ

今回の記事では、group by句で複数の列をグループ化して集計する方法を紹介しました。

スプレッドシートのQUERY関数は、SQLをベースにした難易度の高い関数です。その分、ひとつの関数で、行の抽出・ソート・セルの書式設定・グループ集計などが一気にできる強みがあります。

QUERY関数で自由自在にデータ集計ができるようになれば、事務職としての戦闘力はトップレベル、一目置かれること間違いなし!

シリーズ全体を通して、「クエリとは何か?」から、一つずつ積み上げてきました。
QUERY関数を一から勉強したい!QUERY関数を使うメリットは?そもそも「クエリ」って何?など、基礎から学びたい方は、ぜひシリーズ初回の記事からご覧ください。

または、下記の連載目次から、必要な記事のみを復習してみてくださいね!

連載目次:GoogleスプレッドシートQUERY関数をマスターしよう

スプレッドシートのQUERY関数を使って、データ抽出・集計を効率化する方法を紹介しています。

  1. スプレッドシートのQUERY関数を使う最初の一歩!クエリを理解する
  2. QUERY関数の基本!別シートのデータからselect句で列を取得する方法
  3. 【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
  4. 【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する
  5. 【QUERY関数】where句で日付データを条件にして行を抽出する
  6. 【QUERY関数】where句で時刻データを条件にして行を抽出する
  7. 【QUERY関数】where句でand,orを使用して複数条件を指定する
  8. 【QUERY関数】order by句で抽出結果を昇順・降順ソートする
  9. 【QUERY関数】group by句とcount関数で列のデータ数をカウントする
  10. 【QUERY関数】group by句とsum関数で列の値の合計値を求める
  11. 【QUERY関数】group by句とavg関数で列の値の平均値を求める
  12. 【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
  13. 【QUERY関数】group by句で複数の列をグループ化して集計する
タイトルとURLをコピーしました