【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列「単価」の合計値を求めます。

    query10-1

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

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

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

    query10-2

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

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

    where 列 is not null

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

    query10-3

    label句で項目名を設定する

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

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

    query10-4

     

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

    label 列 ラベル名

    ポイントは2つです。

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

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

    query10-5

     

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

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

    query10-6

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

    まとめ

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

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

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

    1. select句で集計する列を指定する
    2. where句で空白行を除外する
    3. group by句で列をグループ化する
    4. label句で項目名を変更する

      次回も引き続きgroup by句でデータの集計をする方法を紹介します。どうぞお楽しみに!

      連載目次: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関数で列のデータ数をカウントする

        投稿者プロフィール

      もり
      もりシステムエンジニア・Webライター
      「ラクするために全力を尽くす」をモットーに日々勉強。退屈なことはプログラミングで片づけよう。
      事務作業をとことんラクにできるITネタを発信していきます。

      お気軽にフォローしてくださいね!

      もりの詳しいプロフィールはこちら
      ▽▽▽

      コメント

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