【QUERY関数】group by句とavg関数で列の値の平均値を求める

QUERY関数11アイキャッチ

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

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

前回の記事では、group by句とsum関数を使用して、列の値の合計値を算出する方法を紹介しました。

【QUERY関数】group by句とsum関数で列の値の合計値を求める
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第十回目は、group by句とsum関数で項目毎の値の合計値を求める方法です。label句を使用して集計結果の列名を変更することもできます。

さて、合計値ときたら…次に求めたいのは「平均値」ですよね!

今回の記事では、この2点を紹介します。

  • 平均値を求めるavg関数の使い方
  • セルの書式設定をするformat句で小数点の端数処理をする方法

それでは備品購入リストのサンプルを用いて、見ていきましょう!

query11-1

スポンサーリンク

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

avg関数は、指定列の値の平均値を求める関数です。

avg(列)

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

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

query11-2

G2セルにD列の平均値が算出されました。

ご覧のとおり、小数点以下まで計算・表示されています。次の項で、format句を使用して整数部のみの表示にする方法を紹介します。

query11-3

format句でセルの書式設定をする

format句とは、セル(列)に書式設定をするための句です。

format 列 表示形式

この記事ではformat句を使用した「数字」の書式設定を紹介しますが、format句では日付の書式設定もできるので、興味のある方はこちらの公式ドキュメント(英語版)も併せてご覧ください。

小数点以下を四捨五入して整数表示にする

数字3桁ごとにカンマで区切り、小数点以下を四捨五入して整数部のみを表示させる記述です。表示形式はシングルクォートで囲みます。

=query(A:E,"select avg(D) format avg(D) '#,##0'",1)

query11-4

このformat句では、スプレッドシート上の表示が変わるだけで、avg関数の算出結果は変わりません。

query11-5

 

表示形式が ‘#,##0’ だと、下4桁しか表示されないように見えてしまいますが、5桁以上の数字でも大丈夫です。

「QUERY関数で小数点を丸めて整数表示にする時は、format句で ‘#,##0’ と記述する」と覚えておきましょう!

query11-9

小数点以下の指定の桁数まで表示させる

format句では、ピリオドのあとにゼロを並べることで、小数点以下の桁数を指定できます。小数点2桁まで表示(小数点第3位を四捨五入)させる書き方がこちらです。

=query(A:E,"select avg(D) format avg(D) '#,##0.00'",1)

query11-6

なお、この場合も、スプレッドシート上の表示桁数が変わるのみで、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)

query11-7

B列「区分」ごとの平均値を算出できました。

QUERY関数の第1引数に列全体を指定しているため、空白行も集計されています。

where 列 is not null

これも前回記事と同様に、where句を使用して、null(空白)でない行(なんらかのデータが存在する行)のみを集計対象にすればOKです。

=query(A:E,"select B,avg(D) where B is not null group by B format avg(D) '#,##0'",1)

query11-8

まとめ

今回の記事では、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'
  1. select句とavg関数で集計する列を指定する
  2. where句で空白行を除外する
  3. group by句で列をグループ化する
  4. format句で列の書式設定をする

 

    group by句で使用できる集計関数はまだまだあります。次回は、列の最大値・最小値を求めるmax関数とmin関数を紹介します。

    どうぞお楽しみに!

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