みなさんこんにちは！
もり（@moripro3）です。
GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています！
前回の記事では、group by句とcount関数を使用して、列の要素数をカウントする方法を紹介しました。
スプレッドシートのCOUNTIF関数で集計するよりも、QUERY関数で集計したほうが「項目名を設定する必要がなく、項目の集計漏れが発生しない」というメリットがあります。
引き続き、値の集計をするgroup by句を紹介します。
「group by句は集計関数と一緒に使用する」というルールを前回お伝えしましたね。今回は、集計関数のsum関数を使用して、値の合計値を求める方法を紹介します。
スプレッドシートのSUMIF(S)関数もありますが、count関数と同じように、QUERY関数を使用したほうが集計漏れが発生しないのでオススメですよ。
それではみていきましょう！
前回のおさらい：group by句と集計関数とは
QUERY関数は、クエリで抽出した結果を、グループ化して集計することが可能です。そのためには、下記の２つを理解しておく必要があります。
- データをグループ化するための「group by句」
- データを処理するための「集計関数」
group by句とは、複数行にわたる値を集計するための句です。
group by句で指定するすべての列は、select句で集計関数によって集計されている必要があります。
集計関数とは、指定の列の値に対して処理を実行する関数です。count, sum, avg, max, minの5種類があります。
今回は、合計値を求めるsum関数を紹介します。備品購入リストのサンプルを用いて、単価の合計値を求めていきます。
列の値の合計値を求めるsum関数
sum関数は、指定列の値の合計を求める関数です。
まずは最も簡単な使い方です。D列「単価」の合計値を求めます。
=query(A:E,"select sum(D)",1)
合計値50,300を求めることができました。
項目ごとの合計値を求める
続いて、E列「購入者」ごとの合計金額を算出してみます。select句でE列を指定し、かつ、group by句でE列をグループ化します。
=query(A:E,"select E,sum(D) group by E",1)
E列購入者ごとの合計金額を算出できました。
QUERY関数の第1引数に列全体を指定してるため、空白行も集計されています。前回同様に「データが存在する行のみを集計対象とする」ことで解決します。
where句を使用して、E列がnull（空白）でない行、つまり、なんらかのデータが存在する行のみを集計対象にすればOKですね。
=query(A:E,"select E,sum(D) where E is not null group by E",1)
label句で項目名を設定する
集計関数を使用すると、集計結果の項目名が「関数名+列名」となります。
たとえば、D列「単価」の合計値をsum関数で求める場合、「sum 単価」と自動で設定されます。この項目名を変更する方法を紹介します。
label句を使用すると、抽出結果の列に任意の名前をつけることができます。
ポイントは2つです。
- ラベル名はシングルクォートで囲んで文字列とする
- 「D列の合計値」にラベルを付けるので、label D ではなく、label sum(D) と記述する
D列の合計値に「合計額」というラベルを付ける書き方がこちらです。
=query(A:E,"select sum(D) label sum(D) '合計額'",1)
それでは、このlabel句を使用して、購入者ごとの合計金額を求めたクエリにラベルを付けてみます。
label句は、QUERY関数の第2引数の最後に記述します。
=query(A:E,"select E,sum(D) where E is not null group by E label sum(D) '合計額'",1)
「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) '合計額'
- select句で集計する列を指定する
- where句で空白行を除外する
- group by句で列をグループ化する
- label句で項目名を変更する
さて次回は、平均値を求めるavg関数を紹介します！
連載目次：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句で複数の列をグループ化して集計する
