データを集計するときにCOUTIFS()やSUMIFS()などを使う方が多いかと思いますが、実はそれ全部QUERY関数でできます。
フィルタリングやソートした状態でデータを出力できるので、データを集計したり分析したりする人の作業をかなり軽減できる関数なんじゃないかなと思います。
当記事では、
「QUERY関数ので色んな形の集計値を取得したい」
「group by句の使い方を知りたい」
という方向けに、
- QUERY関数の基礎知識
- QUERY関数における集計値の種類
- group by句の使い方
- group by句の活用例
について解説します。
QUERY関数の基礎知識
まずはQUERY関数の基礎知識からお話します。
QUERY関数は表計算ソフトの中でGoogleスプレッドシートでしか使うことのできない「特別な関数」です。
QUERY関数は以下の公式で構成されています。
=QUERY（範囲,"select 出力データ列 where 条件式 group by グルーピング列 order by ソート条件 limit 出力データ数 offset 開始行 label 列名")
QUERY関数はDB操作に使われるSQL文をベースに作られています。
QUERY関数を使えばデータのフィルタリングやソート、グルーピングなどが驚くほど簡単にできるようになります。
この記事ではQUERY関数のgroup by句の使い方について解説するので、
「まずはQUERY関数全般について知りたい」
という方は、QUERY関数の基礎から解説している「QUERY関数の基礎から応用まで！使い方を徹底解説【完全版】」から先にお読みください。
QUERY関数における集計値の種類
QUERY関数のgroup by句では、以下5つの集計値を取得することができます。
| 演算子 | 集計データ |
|---|---|
| count() | データの数を集計する |
| sum() | 合計値を集計する |
| avg() | 平均値を集計する |
| max() | 最大値を集計する |
| min() | 最小値を集計する |
基本的な統計量ばかりなので、これまでもCOUNTIFS()とかSUMIFS()とかを使って算出していた方も多いでしょう。
QUERY関数のgroup by句を使えばこれらの値が一気に算出できるようになります（しかもソートされた状態で）
group by句の使い方・活用例
それでは、先ほど紹介した5つの集計値それぞれの使い方と活用例について、以下のデータセットを例に解説します。
個数：count()の使い方・活用例
count()関数は、グループごとのデータの個数をカウントしてくれる関数です。
以下のように記述すると、日ごとの販売数を集計してくれます。
=QUERY(A1:D14,"select B,count(B) group by B")
計算結果
さらにこれを販売数の多い順に並び替えると、以下のようになります。
=QUERY(A1:D14,"select B,count(B) group by B order by count(B) desc")
個数を集計した上で並び替えてくれるところが、countifs()関数よりも使い勝手が良さそうですね。
合計値：sum()の使い方・活用例
sum()関数は、グループごとのデータの合計値を集計してくれる関数です。
以下のように記述すると、日ごとの売上合計値と販売数を集計してくれます。
=QUERY(A1:D14,"select A,sum(C),count(B) group by A")
このように、「日づけ」というグループにして、売上合計値と販売数を同時に集計できるのが、QUERY関数の強みです。
平均値：avg()の使い方・活用例
avg()関数は、グループごとのデータの平均値を集計してくれる関数です。
以下のように記述すると、日ごとの売上合計値と販売数、単価（合計値/販売数=1商品あたりの売上平均値）を集計してくれます。
=QUERY(A1:D14,"select A,sum(C),count(B),avg(C) group by A")
ここまでデータが見える化されてくると、「売上には商品単価が重要」という事実が見えやすくなってきますね。
このように隠れた事実を発見する際に、統計量の集計はとても便利です。
最大値：max()の使い方・活用例
max()関数は、グループごとのデータの最大値を集計してくれる関数です。
以下のように記述すると、商品ごとの最高単価を集計してくれます。
=QUERY(A1:D14,"select B,max(C) group by B")
最小値：min()の使い方・活用例
min()関数は、グループごとのデータの最小値を集計してくれる関数です。
以下のように記述すると、商品ごとの最低単価を集計してくれます。
=QUERY(A1:D14,"select B,min(C) group by B")
max()とmin()はセグメントごとの最大・最小に価値がある場合などに使用すると良いでしょう。
まとめ
今回はQUERY関数のgroup by句の使い方について解説しました。
group by句を理解すると、QUERY関数をかなり自由に使いこなせるようになると思います。
QUERY関数はとっても便利な関数なので、ぜひ使い方をマスターして、業務の効率化を目指しましょう。
では、また。
QUERY関数の解説本【kindle unlimitedで無料】
