みなさんこんにちは！
もり（@moripro3）です。
GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています！
前回の記事では、order by句を使用して抽出結果をソートする方法を紹介しました。
さて、今回から数回にわたって、値の集計をするgroup by句を紹介していきます。
これまで紹介してきたselect句・where句はデータを抽出するための句でした。QUERY関数のメリットは、データを抽出することだけでなく、抽出データの集計までを一気にできることです。
少しずつレベルがあがっていきますが、ゆっくりみていきましょう！
group by句と集計関数とは
QUERY関数は、クエリで抽出した結果を、グループ化して集計することが可能です。そのためには、下記の２つを理解しておく必要があります。
- データをグループ化するための「group by句」
- データを処理するための「集計関数」
group by句とは
group by句とは、複数行にわたる値を集計するための句です。group by句で指定する列の、値の組み合わせごとに単一行が作成されます。
group by句で指定するすべての列は、select句で集計関数によって集計されている必要があります。文字だけで見てもむずかしいので、のちほど、実際の集計例で詳しく説明します。
ここでは、「group by句は集計関数と一緒に使用する」と理解しておきましょう。
集計関数とは
集計関数とは、指定の列の値に対して処理を実行する関数です。
集計関数には下記の5種類があります。この記事では、列の要素数を集計するcount関数を紹介します。
| 関数名 | 説明 |
|---|---|
| count() | 列の要素数を返す |
| sum() | 列の値の合計値を返す |
| avg() | 列の値の平均値を返す |
| max() | 列の最大値を返す |
| min() | 列の最小値を返す |
いずれも、使い方はスプレッドシートの関数とほぼ同じです。ただ、この記事で紹介している関数は、スプレッドシートの関数ではなく、Google Visualization APIのクエリ言語の集計関数です。
それでは、備品購入リストのサンプルを用いて実際の集計例をみていきましょう！
データの個数をカウントするcount関数
count関数は、指定列の要素数を集計する関数です。
まずは最も簡単な使い方を紹介します。備品購入リストB列「区分」の要素数をカウントする方法です。要素数とは「データの数」のことです。
全14行からヘッダーの1行をのぞくと、データの数は13個なので、count関数の結果は「13」が返ります。
=query(A:E,"select count(B)",1)
項目ごとのデータ個数をカウントする
それではここからが本題です。group by句と併用して、項目毎の個数を集計します。group by句でB列をグループ化します。
=query(A:E,"select count(B) group by B",1)
項目毎のデータ個数を算出できましたが、それぞれ何の項目かわからないので、項目名も表示させたいですよね。そこで、select句でB列を指定します。
=query(A:E,"select B,count(B) group by B",1)
ここまでで、QUERY関数を使用して項目毎のデータ個数の集計ができました。
ただ、第1引数の「データ範囲」に列全体（A列~E列）を指定しているため、空白行もカウントされています。これを解決しましょう。
空白行は集計対象外とする
この問題は、前回の記事でお伝えしたとおり「データが存在する行のみを集計対象とする」ことで解決します。
B列がnull（空白）でない行、つまり「B列になんらかのデータがある行」を集計対象にします。where句は、group by句よりも前に記述します。
=query(A:E,"select B,count(B) where B is not null group by B",1)
QUERY関数とCOUNTIF関数の違い
上記の集計結果をみて、スプレッドシートのCOUNTIF関数を思い浮かべた方もいるでしょう。
G列に項目名を入力してH列に関数を組めば、QUERY関数と同様の結果が得られますし、記述もシンプルです。
『それじゃあ、わざわざ長くて難しいQUERY関数を使うメリットってあるの…？』って思いますよね。大丈夫です、あります。
QUERY関数で集計するメリット
集計元のデータ（A列~E列）に行追加した場合を考えてみましょう。15行目に新規項目「その他」が発生しました。
COUNTIF関数は「項目名ありき」の集計方法です。集計条件（項目名）を人が設定するため、元データに新規項目が発生した場合、人が手作業でその項目を追加する必要があります。
下記の場合だと、G4セルに「その他」という項目名を追加し、H4セルにCOUNTIF関数を入力すれば集計ができますが、それではちょっと面倒ですね。
「元データの合計値」と「集計結果の合計値」が一致せず、原因を調べたら集計漏れの項目があった…なんて経験ありませんか？
それに対して、QUERY関数は項目名を意識する必要がありません。B列をグループ化して集計してね、という指定なので、新規項目も自動で集計されます。便利ですね。
このように、QUERY関数で集計するメリットは、「集計する項目名を意識しなくてよいため、集計漏れが発生しない」ことです。
order by句と組み合わせて並び替え
group by句では、ソート順を指定しない場合、グループ化された列によって自動的にソートされます。B列でグループ化した場合、B列の名称順でソートされます。
自分でソート順を決めたい場合、前回の記事で紹介したorder by句で並び替えができます。
B列の個数が大きい順で並び替えたい場合、order by句には、Bではなく、count(B)を指定するのがポイントです。
=query(A:E,"select B,count(B) where B is not null group by B order by count(B) desc",1)
まとめ
今回の記事では、group by句と集計関数を使用して、列をグループ化してデータの個数を集計する方法を紹介しました。
- group by句で列をグループ化する方法
- count関数でデータ個数をカウントする方法
- COUNTIF関数との違い
COUNTIF関数に比べると、QUERY関数は記述が長くて難しく感じるかもしれませんが、データの集計漏れが発生しないという利点がありますので、データ集計をする方はぜひ覚えておきましょう！
さて次回は、値の合計値を求めるsum関数を紹介します！
連載目次：GoogleスプレッドシートQUERY関数をマスターしよう
スプレッドシートのQUERY関数を使って、データ抽出・集計を効率化する方法を紹介しています。
- スプレッドシートのQUERY関数を使う最初の一歩！クエリを理解する
- QUERY関数の基本！別シートのデータからselect句で列を取得する方法
- 【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
- 【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する
- 【QUERY関数】where句で日付データを条件にして行を抽出する
- 【QUERY関数】where句で時刻データを条件にして行を抽出する
- 【QUERY関数】where句でand,orを使用して複数条件を指定する
- 【QUERY関数】order by句で抽出結果を昇順・降順ソートする
- 【QUERY関数】group by句とcount関数で列のデータ数をカウントする
- 【QUERY関数】group by句とsum関数で列の値の合計値を求める
- 【QUERY関数】group by句とavg関数で列の値の平均値を求める
- 【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
- 【QUERY関数】group by句で複数の列をグループ化して集計する
