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

【スプレッドシート】Query関数のpivotの使い方と例4つ

Googleスプレッドシートには標準でピボット機能がありますが、実はQuery関数のpivot節を使えば同じ出力を行えます。

このページでは、Query関数のpivotの基本的な使い方から、4つの集計関数ごとのスプレッドシート操作例を解説していきます。ぜひ参考にして、情報の整理されたグラフや表の作成にお役立てください。

Query関数のpivotの基本的な使い方

GoogleスプレッドシートQuery関数のpivotを使う際の基本形を紹介します。次のpivotを使ったQuery関数は「列Aを縦軸、列Bのユニークな値を横軸にして、列Aを集計した値を表示する」という指示になります。

pivotだけでなく「group by」も出てきますが、このあとに簡単に説明します。Query関数内だけで使用できる集計関数の種類は次の通り。

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

より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、A列の個数を集計した表を出力する」という指示を書いたものが次のQuery関数です。

下の例では、「日毎のカテゴリ別の投稿数」を表示しています。

集計関数countを使ってpivot関数の使い方

group by を簡単解説

先程のpivotを使ったQuery関数は、もともと「group by」による出力を、最後「pivot」によって変化させているものです。

もしpivotがなければ、次のようにgroup by によって、日付の出現回数がカウントされることになります。下の例では、「日毎の投稿数」を表示しています。

集計関数countを使ってgroup byの使い方

ここにpivotの指定が加わることで、「日付の投稿数を、さらにカテゴリごとに分けて出力」をしていました。

Query関数のpivotの使い方例

スプレッドシートQuery関数のpivotを使用する上で抑えておきたいのが集計関数。

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

それぞれの集計関数とpivotの使い方の例を解説していきます。

①count関数を使ってpivot例

こちらは先程の「pivotの使い方」で説明したので解説は省略します。

集計関数countを使ってpivot関数の使い方

②avg関数を使ってpivot例

avg関数はaverageの略で「平均」を集計する関数です。Query関数の中では次のようにして使います。

より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの平均値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。

下記のデータを踏まえると「日毎のカテゴリ別の平均PV数を表示せよ」という意味になります。

集計関数avgを使ってpivot関数の使い方

③max関数を使ってpivot例

max関数はmaximumの略で「最大値」を集計する関数です。Query関数の中では次のようにして使います。

より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの最大値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。

下記のデータを踏まえると「日毎のカテゴリ別の最大PV数を表示せよ」という意味になります。

集計関数maxを使ってpivot関数の使い方

④min関数を使ってpivot例

min関数はminimumの略で「最小値」を集計する関数です。Query関数の中では次のようにして使います。

より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの最小値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。

下記のデータを踏まえると「日毎のカテゴリ別の最小PV数を表示せよ」という意味になります。

集計関数minを使ってpivot関数の使い方

⑤sum関数を使ってpivot例

sum関数は「合計値」を集計する関数です。Query関数の中では次のようにして使います。

より具体的に「A11:D17の範囲の行の中で、A列を縦軸、C列を横軸にして、Dの合計値を表示せよ」を指示する、containsを使ったquery関数は次のようになります。

下記のデータを踏まえると「日毎のカテゴリ別の合計PV数を表示せよ」という意味になります。

集計関数sumを使ってpivot関数の使い方

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")

次の記事も読むと、未来のユーザーニーズが見えてきます

icon-check-circle Google検索の変遷から見えてくる「Googleがキュレーション化する日」
あなたの課題はなんですか?
【SEO】順位が上がらない。アクセスが集まらない。
SEOで重要なことは、ユーザーが求めている体験は何かを、感覚ではなく理論で落とし込み、どのページでも上位表示を狙える体制をつくることです。順位が上がらない、アクセスが増えないことでお悩みの方はお悩みの方は、立ち上げから1,500万PVにまで成長させた私のSEO経験がお役に立てるかもしれません。お気軽にご相談ください。
【アフィリエイト】成果が発生しない。リンクをクリックされない。
アフィリエイトで重要なことは、「何を伝えるか」ではなく「どういう気持にさせるか」「何を伝えないか」です。アフィリエイトの成果を加速させたい方は加速させたい方は、4年間積んだアフィリエイト経験が役に立つかもしれません。お気軽にご連絡ください。
【サービス登録率】登録まで行かない。登録率が改善しない。
登録率改善で重要なことは、ユーザーの思考回路を順を追って仮説として1つずつ検証していくことです。1%→3%に登録率を改善させた経験をノウハウ化して共有いたします。
【広告運用】CPAが下がらない。毎回数値がブレる。
広告運用で重要なことは、「これが刺さるだろう」と再現性の無い施策をうつのではなく、頻繁にクリエイティブを変更しても、同じ効果が維持できる検証をし続けることです。転職広告のCPA20,000円→8,000円にした経験で、お役に立てることがあるかもしれません。気軽にご連絡ください。
Twitterをフォロー 30分相談 コンサル相談 匿名質問
(2018年3月12日現在、1件コンサルの空きがあります)