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

Google Workspace

2019.05.10

こんにちは！もり（@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句
1. 1つの列でグループ化する
group by句で複数列をグループ化する
まとめ
連載目次：GoogleスプレッドシートQUERY関数をマスターしよう

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

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

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

select 列 group by 列

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

select 集計関数(列) group by 列

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

B列（性別）
C列（血液型）
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)

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

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

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

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

C列（血液型）
B列（性別）
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)

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

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

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

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

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

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

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

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

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

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

まとめ

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

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

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

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

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

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

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

タイトルとURLをコピーしました