Googleスプレッドシートのデータ抽出に便利なQUERY関数について、前回はWHERE句を使用して抽出条件を指定する方法をご紹介しました。
QUERY関数No2
- WHERE句の使い方
- 複数条件の設定
- 文字列の比較演算子
今回は列の値を項目化してデータ集計できる「GROUP-BY」句と「PIVOT」句について解説します。2つのクエリを組み合わせてクロス集計する方法も解説しているので、ぜひ参考にしてみてください。
スプレッドシートのQUERY関数の使い方
QUERY関数ではGoogle Visualization API のクエリ言語を使用することで、様々な方法でデータ抽出が可能です。QUERY関数の要素と、クエリの種類についておさらいしましょう。
QUERY関数とは？
QUERY関数は対象の「データ」範囲に、指定した「データ操作(クエリ)」を行います。
クエリはデータに対して実行する「処理・命令」のようなイメージです。
クエリにはそれぞれ異なる機能を持つ10種類の句が用意されており、各クエリや関数を組み合わせることで様々な方法でデータ抽出することができます。
QUERY関数の構成要素：(データ, クエリ, [見出し])
QUERY関数のクエリの種類
クエリの種類には以下のようなものがありますが、今回はGROUP-BY句とPIVOT句について詳しく解説します。2つはよく組み合わせて使われるクエリなので、まとめて理解しておきましょう。
- select ：特定の列を指定した順序で抽出します。
- where ：条件を満たすセルだけを抽出します。
- group by： 列内の同じ要素の値を集計します。(ピックアップ)
- pivot ：group byで抽出したデータに分類要素を追加します。(ピックアップ)
- order by 行を列の値で並べ替えます。
- limit ：抽出する行数を指定します。
- offset：指定した数の最初の行をスキップします。
- label：列ラベルを設定します。
- format：特定の列の値を書式設定します。
- options ：追加オプションを設定します(no_formatまたはno_values)
QUERY関数のGROUP BY句の使い方
GROUP-BY句の基本的な使い方や、空白行を含まない抽出方法について解説します。GROUP-BY句が理解できたら、PIVOT句を組み合わせて高度な集計にもチャレンジしてみましょう。
GROUP BY句の基本的な使い方
GROUP-BY句は対象データを指定した列の同じ値ごとにグループ化し、集計関数の結果を返します。
例えば「=QUERY(A:D,”SELECT B, sum(D) GROUP BY B”)」のように、グループ化の対象に列(B)、SUM関数の対象に列(D)を指定してみましょう。
sum( )：合計、avg( )：平均値、count( )：要素数、max( )：最大値、min( )：最小値
B列が果物と野菜にグループ化され、各グループごとのD列の合計が抽出できました。
GROUP-BY句を使用する場合、SELECT句にはグループ化した列を指定します。
GROUP-BY：=QUERY(データ範囲,”SELECT 列, 集計関数(列) GROUP BY 列“)
GROUP BY句に複数の集計関数を指定する方法
GROUP-BY句を使えば1つの行項目に対して、複数の集計結果を求めることも可能です。
例えば「=QUERY(A:D,”SELECT B, sum(D),count(C) GROUP BY B”)」のように、グループ化したB列に対しSUM(D)とCOUNT(C)の2つを指定してみましょう。
QUERY(データ範囲,”SELECT 列, sum(列),count(列) GROUP BY 列“)
B列がグループ化された行項目(果物・野菜)に対し、D列の合計とC列の要素数が抽出できました。
なお抽出先の2行目には、空白の項目が抽出されています。空白を除外するにはデータ範囲をA1:D7のように限定するか、演算子(is not null)を使用します。
GROUP BY句の抽出結果から空白を削除する方法
抽出結果から空白を除外するには、WHERE句に「is not null」演算子を指定します。
クエリについて軽くおさらいすると、WHERE句は条件を設定するクエリです。
「=QUERY(A:D,”SELECT B, sum(D) WHERE D is not null GROUP BY B”)」のように、WHERE句を集計関数の後ろにセットしましょう。
WHERE句についての詳しい解説＞＞QUERY関数にWHERE句で抽出条件を指定する方法
抽出結果から、D列が空白の行が除外されました。
空白の行だけを抽出したい場合は、WHERE句に「is null」を指定します。
QUERY(データ範囲,”SELECT 列, sum(列) WHERE 列 is not null GROUP BY 列“)
GROUP BY句で複数の項目をグループ化する方法
GROUP-BY句には複数の列を指定して、行項目をさらに詳細に分けることも可能です。
例えば「GROUP BY A,B」のようにA列・B列の2つをグループ化すると、それぞれの組み合わせが一意の項目になるようにグループ化されます。
QUERY(データ範囲,”SELECT 列1,列2, sum(列) GROUP BY 列1,列2“)
GROUP-BY句に複数の列を指定した場合、SELECT句にもそれぞれの列を指定します。
グループ化してもSELECT句で抽出しなければ、例のように結果には含まれません。
SELECT句についての詳しい解説＞＞QUERY関数の基本解説【SELECT句】
QUERY関数のPIVOT句の使い方
PIVOT句は個別の値を新しい列に変換することで、ピボットテーブルのようなクロス集計が可能です。
例えば「=QUERY(A1:D7,”SELECT B, sum(D) GROUP BY B PIVOT C”)」のように、PIVOT句で列項目にC列のユニーク値(一意の値)を追加してみましょう。
クロス集計：複数の項目(例えばカテゴリーは果物で商品名はリンゴ)をまとめる集計方法
グループ化したD列の行項目に加え、列項目にC列のユニーク値が割り当てられました。
集計関数にはsumをセットしているので、各カテゴリーごとに商品別の価格が合計されています。
=QUERY(データ範囲,”SELECT 列, sum(列) GROUP BY 列 PIVOT 列“)
PIVOT句はよくGROUP-BY句と組み合わせて使用されますが、単体でも使用可能です。
例えば「=QUERY(A:D,”SELECT sum(D) PIVOT B”)」のようにGROUP-BYを含まない式を指定すると、列項目だけの1行の集計結果が抽出されます。
PIVOT：=QUERY(データ範囲,”SELECT sum(列) PIVOT 列“)
QUERY関数No4
- ORDER BY句
- LIMIT句
- OFFSET句
