みなさんこんにちは！
もり（@moripro3）です。
GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています！
前回の記事では、group by句とsum関数を使用して、列の値の合計値を算出する方法を紹介しました。
さて、合計値ときたら…次に求めたいのは「平均値」ですよね！
今回の記事では、この2点を紹介します。
- 平均値を求めるavg関数の使い方
- セルの書式設定をするformat句で小数点の端数処理をする方法
それでは備品購入リストのサンプルを用いて、見ていきましょう！
列の値の合計値を求めるavg関数
avg関数は、指定列の値の平均値を求める関数です。
まずは最も簡単な使い方です。D列「単価」の平均値を求めます。
=query(A:E,"select avg(D)",1)
G2セルにD列の平均値が算出されました。
ご覧のとおり、小数点以下まで計算・表示されています。次の項で、format句を使用して整数部のみの表示にする方法を紹介します。
format句でセルの書式設定をする
format句とは、セル（列）に書式設定をするための句です。
この記事ではformat句を使用した「数字」の書式設定を紹介しますが、format句では日付の書式設定もできるので、興味のある方はこちらの公式ドキュメント（英語版）も併せてご覧ください。
小数点以下を四捨五入して整数表示にする
数字3桁ごとにカンマで区切り、小数点以下を四捨五入して整数部のみを表示させる記述です。表示形式はシングルクォートで囲みます。
=query(A:E,"select avg(D) format avg(D) '#,##0'",1)
このformat句では、スプレッドシート上の表示が変わるだけで、avg関数の算出結果は変わりません。
表示形式が ‘#,##0’ だと、下4桁しか表示されないように見えてしまいますが、5桁以上の数字でも大丈夫です。
「QUERY関数で小数点を丸めて整数表示にする時は、format句で ‘#,##0’ と記述する」と覚えておきましょう！
小数点以下の指定の桁数まで表示させる
format句では、ピリオドのあとにゼロを並べることで、小数点以下の桁数を指定できます。小数点2桁まで表示（小数点第3位を四捨五入）させる書き方がこちらです。
=query(A:E,"select avg(D) format avg(D) '#,##0.00'",1)
なお、この場合も、スプレッドシート上の表示桁数が変わるのみで、avg関数の算出値自体が変わるわけではありません。
項目ごとの平均値を求める
続いて、group by句を使用して、B列「区分」ごとの平均値を算出してみます。group by句の使い方は、これまで紹介してきたcount関数・sum関数と同じです。
select句でB列を指定し、かつ、group by句でB列をグループ化します。group by句は、format句よりも前に書きます。
=query(A:E,"select B,avg(D) group by B format avg(D) '#,##0'",1)
B列「区分」ごとの平均値を算出できました。
QUERY関数の第1引数に列全体を指定しているため、空白行も集計されています。
これも前回記事と同様に、where句を使用して、null（空白）でない行（なんらかのデータが存在する行）のみを集計対象にすればOKです。
=query(A:E,"select B,avg(D) where B is not null group by B format avg(D) '#,##0'",1)
まとめ
今回の記事では、group by句とavg関数で項目毎の平均値を求める方法・format句を使用してセルの書式設定をする方法を紹介しました。
format句はクエリの最後に記述するのがポイントです。select, where, group by, format それぞれの句の役割と、記述する順番をもう一度おさらいしておきましょう。
select B,avg(D) where B is not null group by B format avg(D) '#,##0'
- select句とavg関数で集計する列を指定する
- where句で空白行を除外する
- group by句で列をグループ化する
- format句で列の書式設定をする
group by句で使用できる集計関数はまだまだあります。次回は、列の最大値・最小値を求めるmax関数とmin関数を紹介します。
どうぞお楽しみに！
連載目次：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句で複数の列をグループ化して集計する
