【QUERY関数】group by句とsum関数で列の値の合計値を求める

QUERY関数10アイキャッチ

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

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

前回の記事では、group by句とcount関数を使用して、列の要素数をカウントする方法を紹介しました。

スプレッドシートのCOUNTIF関数で集計するよりも、QUERY関数で集計したほうが「項目名を設定する必要がなく、項目の集計漏れが発生しない」というメリットがあります。

【QUERY関数】group by句とcount関数で列のデータ数をカウントする
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第九回目は、group by句を使用して値を集計する方法の紹介です。group by句は集計関数と併せて使用します。count関数データの個数を数えます。

引き続き、値の集計をするgroup by句を紹介します。

「group by句は集計関数と一緒に使用する」というルールを前回お伝えしましたね。今回は、集計関数のsum関数を使用して、値の合計値を求める方法を紹介します。

スプレッドシートのSUMIF(S)関数もありますが、count関数と同じように、QUERY関数を使用したほうが集計漏れが発生しないのでオススメですよ。

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

スポンサーリンク

前回のおさらい:group by句と集計関数とは

QUERY関数は、クエリで抽出した結果を、グループ化して集計することが可能です。そのためには、下記の2つを理解しておく必要があります。

  • データをグループ化するための「group by句」
  • データを処理するための「集計関数」

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

group by 列

group by句で指定するすべての列は、select句で集計関数によって集計されている必要があります。

集計関数とは、指定の列の値に対して処理を実行する関数です。count, sum, avg, max, minの5種類があります。

    集計関数(列)

     

    今回は、合計値を求めるsum関数を紹介します。備品購入リストのサンプルを用いて、単価の合計値を求めていきます。

    query9-1

    列の値の合計値を求めるsum関数

    sum関数は、指定列の値の合計を求める関数です。

    sum(列)

    まずは最も簡単な使い方です。D列「単価」の合計値を求めます。

    =query(A:E,"select sum(D)",1)

    query10-1

    合計値50,300を求めることができました。

    項目ごとの合計値を求める

    続いて、E列「購入者」ごとの合計金額を算出してみます。select句でE列を指定し、かつ、group by句でE列をグループ化します。

    =query(A:E,"select E,sum(D) group by E",1)

    query10-2

    E列購入者ごとの合計金額を算出できました。

    QUERY関数の第1引数に列全体を指定してるため、空白行も集計されています。前回同様に「データが存在する行のみを集計対象とする」ことで解決します。

    where 列 is not null

    where句を使用して、E列がnull(空白)でない行、つまり、なんらかのデータが存在する行のみを集計対象にすればOKですね。

    =query(A:E,"select E,sum(D) where E is not null group by E",1)

    query10-3

    label句で項目名を設定する

    集計関数を使用すると、集計結果の項目名が「関数名+列名」となります。

    たとえば、D列「単価」の合計値をsum関数で求める場合、「sum 単価」と自動で設定されます。この項目名を変更する方法を紹介します。

    query10-4

     

    label句を使用すると、抽出結果の列に任意の名前をつけることができます。

    label 列 ラベル名

    ポイントは2つです。

    • ラベル名はシングルクォートで囲んで文字列とする
    • 「D列の合計値」にラベルを付けるので、label D ではなく、label sum(D) と記述する

    D列の合計値に「合計額」というラベルを付ける書き方がこちらです。

    =query(A:E,"select sum(D) label sum(D) '合計額'",1)

    query10-5

     

    それでは、このlabel句を使用して、購入者ごとの合計金額を求めたクエリにラベルを付けてみます。

    label句は、QUERY関数の第2引数の最後に記述します。

    =query(A:E,"select E,sum(D) where E is not null group by E label sum(D) '合計額'",1)

    query10-6

    「sum 単価」の表記(H1セル)が、label句で指定した「合計額」に変わりましたね。

    まとめ

    今回の記事では、group by句とsum関数を使用して、列をグループ化して値の合計値を求める方法を紹介しました。

    記述が長くなると難しく感じますが、select, where, group by, label それぞれの句の役割をきちんと理解し、順番につなげていけば大丈夫です。

    この記事では、下記の1~4の順番でクエリを組み立てています。もう一度復習もかねてご確認ください。

    select E,sum(D) where E is not null group by E label sum(D) '合計額'
    1. select句で集計する列を指定する
    2. where句で空白行を除外する
    3. group by句で列をグループ化する
    4. label句で項目名を変更する

       

      さて次回は、平均値を求めるavg関数を紹介します!

      【QUERY関数】group by句とavg関数で列の値の平均値を求める
      GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第十一回目は、group by句とavg関数で項目毎の値の平均値を求める方法です。format句で書式設定をして小数点以下の桁数を処理する方法も紹介しています。

      連載目次: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をコピーしました