スプレッドシートの使い方

【スプレッドシート】QUERY関数のgroup byと集計関数の使い方

スプレッドシートの便利なQUERY関数では、「group by」オプションを使うことで、列の値をグループ化して、グループ内の最大値や合計値を求めることができます。

この記事では、便利なgroup byオプションの使い方、使用用途に合わせた例を紹介していきます。

group byの基本の使い方

QUERY関数内で使用するオプション「group by」は、基本的に次のような表記で使います。

=QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")

「group by」オプションを使う際には、集計関数が必要です。これは「group by」でグルーピングした列に対して、どう処理するのか命令を出します。集計関数には次のようなものがあります。

集計関数意味使用例
avg()平均avg(列)
count()個数count(列)
max()最大値max(列)
min()最小値min(列)
sum()合計sum(列)

代表的な例

「goup by」オプションを使った代表例を紹介します。

次のQUERY関数では、同じカテゴリどうしをグルーピングして、PVを合計する例です。

=QUERY(A11:D17,"select C, sum(D) group by C",true)

query関数でgroup byの基本的な使い方を解説

count()でのgroup by の使い方例

count関数を使用することで、グルーピングした中での個数を算出することができます。

次の例では、同じ日付(B列)どうしをグルーピングして、各グループのデータ個数を表しています。

=QUERY(B11:E17,"select B, count(B) group by B",true)

avg()でのgroup byの使い方例

avg関数を使用することで、グルーピングした中での平均値を算出することができます。

次の例では、同じ日付(B列)どうしをグルーピングして、各グループの中でのPV(E列)の平均値を出しています。

=QUERY(B11:E17,"select B, avg(E) group by B",true)

max()でのgroup by の使い方例

max関数を使用することで、グルーピングした中での最大値を算出することができます。

次の例では、同じ日付(B列)どうしをグルーピングして、各グループの中でのPV(E列)の最大値を出しています。

=QUERY(B11:E17,"select B, max(E) group by B",true)

min()でのgroup by の使い方例

min関数を使用することで、グルーピングした中での最小値を算出することができます。

次の例では、同じ日付(B列)どうしをグルーピングして、各グループの中でのPV(E列)の最小値を出しています。

=QUERY(B11:E17,"select B, min(E) group by B",true)

sum()でのgroup by の使い方例

sum関数を使用することで、グルーピングした中での合計値を算出することができます。

次の例では、同じ日付(B列)どうしをグルーピングして、PV(E列)の合計を出しています。

=QUERY(B11:E17,"select B, sum(E) group by B",true)

【応用編】Group by と Where と Order by を使う方法

=QUERY(B4:C,"select B, sum(C) where C > 19 group by B order by sum(C) desc",false)
QUERY関数の使い方」まとめ
QUERY関数の使い方使用頻度使用例
オプションOrder byで昇順・降順を指定する★★QUERY("範囲" "order by 列 asc")
オプションgroup byで同じ値をグルーピングして集計する★★★QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")
オプションpivotを使って、group byをさらにグルーピングして表示するQUERY(範囲, "select 列A, 集計関数(列A) where group by 列A pivot 列B")
オプションlabelを使って、出力される列のラベルを変更するQUERY(範囲, "label 列 '名前' ")
オプションformatを使って、出力される値の表示形式を変更するQUERY(範囲, "format 列 '表示形式' ")
オプションlimitを使って、表示する行数に制限をかけるQUERY(範囲, "limit 数")
オプションoffsetを使って表示データを上からスキップする方法QUERY(範囲, "offset 数")
オプションoptionsを使う
演算子containsを使って、指定文字列を含む行を表示する★★QUERY(範囲, "where 列 contains '検索文字列' "
演算子starts withを使って、行の先頭文字列に条件を指定するQUERY(範囲, "where 列 starts with '検索文字列' "
演算子ends withを使って、行の後尾文字列に条件を指定するQUERY(範囲, "where 列 ends with '検索文字列' "
演算子likeで複雑な条件を指定するQUERY(範囲,"where 列 like '検索文字列' ")
演算子★★★QUERY(範囲,"WHERE 列 matches '正規表現' ")
テクニックwhereの条件で空白を指定・除外する★★QUERY(範囲, "where 列 is not null")
テクニックQUERY関数にSUM関数を使う★★SUM(QUERY(範囲, "where 条件")
テクニックwhereの条件に日付を指定する★★★QUERY(範囲,"where 列 = date '"& TEXT(セル,"YYYY-MM-DD") &"'")
テクニックQUERY関数内でimportrange関数を使って、別シートを参照する★★★QUERY(importrange(シートID,参照範囲), "where 条件")
テクニック複数範囲・複数シートを結合する★★QUERY({IMPORTRANGE(シートURL,シート範囲);IMPORTRANGE(シートURL,シート範囲)},"WHERE 条件")
テクニックwhereの条件を複数指定する★★★QUERY("範囲", "where 条件A or 条件B and 条件C")