Googleスプレッドシートには標準でピボット機能がありますが、実はQuery関数のpivot節を使えば同じ出力を行えます。
このページでは、Query関数のpivotの基本的な使い方から、4つの集計関数ごとのスプレッドシート操作例を解説していきます。ぜひ参考にして、情報の整理されたグラフや表の作成にお役立てください。
Query関数のpivotの基本的な使い方
GoogleスプレッドシートQuery関数のpivotを使う際の基本形を紹介します。次のpivotを使ったQuery関数は「列Aを縦軸、列Bのユニークな値を横軸にして、列Aを集計した値を表示する」という指示になります。
=QUERY(範囲, "select 列A, 集計関数(列A) group by 列A pivot 列B")
pivotだけでなく「group by」も出てきますが、このあとに簡単に説明します。Query関数内だけで使用できる集計関数の種類は次の通り。
[table id=32 /]より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、A列の個数を集計した表を出力する」という指示を書いたものが次のQuery関数です。
=QUERY(A11:D17, "select A, count(A) group by A pivot C",true)
下の例では、「日毎のカテゴリ別の投稿数」を表示しています。
group by を簡単解説
先程のpivotを使ったQuery関数は、もともと「group by」による出力を、最後「pivot」によって変化させているものです。
もしpivotがなければ、次のようにgroup by によって、日付の出現回数がカウントされることになります。下の例では、「日毎の投稿数」を表示しています。
ここにpivotの指定が加わることで、「日付の投稿数を、さらにカテゴリごとに分けて出力」をしていました。
Query関数のpivotの使い方例
スプレッドシートQuery関数のpivotを使用する上で抑えておきたいのが集計関数。
[table id=32 /]それぞれの集計関数とpivotの使い方の例を解説していきます。
①count関数を使ってpivot例
こちらは先程の「pivotの使い方」で説明したので解説は省略します。
=QUERY(範囲, "select 列A, 集計関数(列A) group by 列A pivot 列B")
=QUERY(A11:D17, "select A, count(A) group by A pivot C",true)
②avg関数を使ってpivot例
avg関数はaverageの略で「平均」を集計する関数です。Query関数の中では次のようにして使います。
=QUERY(範囲, "select 列A, avg(列B) group by 列A pivot 列C")
より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの平均値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。
=QUERY(A11:D17, "select A, avg(D) group by A pivot C",true)
下記のデータを踏まえると「日毎のカテゴリ別の平均PV数を表示せよ」という意味になります。
③max関数を使ってpivot例
max関数はmaximumの略で「最大値」を集計する関数です。Query関数の中では次のようにして使います。
=QUERY(範囲, "select 列A, max(列B) group by 列A pivot 列C")
より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの最大値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。
=QUERY(A11:D17, "select A, max(D) group by A pivot C",true)
下記のデータを踏まえると「日毎のカテゴリ別の最大PV数を表示せよ」という意味になります。
④min関数を使ってpivot例
min関数はminimumの略で「最小値」を集計する関数です。Query関数の中では次のようにして使います。
=QUERY(範囲, "select 列A, min(列B) group by 列A pivot 列C")
より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの最小値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。
=QUERY(A11:D17, "select A, min(D) group by A pivot C",true)
下記のデータを踏まえると「日毎のカテゴリ別の最小PV数を表示せよ」という意味になります。
⑤sum関数を使ってpivot例
sum関数は「合計値」を集計する関数です。Query関数の中では次のようにして使います。
=QUERY(範囲, "select 列A, sum(列B) group by 列A pivot 列C")
より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの合計値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。
=QUERY(A11:D17, "select A, sum(D) group by A pivot C",true)
下記のデータを踏まえると「日毎のカテゴリ別の合計PV数を表示せよ」という意味になります。
その他のpivotの使い方応用編
質問がありましたので、こちらの記事で略説します。
pivotの列を任意の順番にする
解説:QUERY関数の出力をさらにQUERY関数で「select Col番号」を使って、順番を任意の順番にします。ただし、任意って結構無理やりなので、新しいデータが追加されて、新しいカテゴリが生まれると、順番の指定が狂います。(順番の対応表使って、それをColで指定もできますが、さすがに面倒)
▼応用前
▼応用後
就活→転職だったのが、転職→就活の順番になりました。
[table id=31 /]Google検索の変遷から見えてくる「Googleがキュレーション化する日」
[/su_note]