Googleスプレッドシート QUERY関数 超応用例 15（group by と pivot 応用編）

Googleスプレッドシートの最強集計関数 QUERY関数について書いたnoteの第15回です。

👇これまでのQUERY関数シリーズは マガジンにまとめています。QUERY関数をハイレベル（廃レベル）に使いこなしたい人は必読です！

前回はgroup by句、pivot句の基礎を学びました。

今回は応用編に入っていきましょう！

group by句、pivot句 の応用を学ぶ

今回は group by句、pivot句の 応用的な使い方を学んでいきます。

前回同様、サンプルデータを元に操作しながら学ぶのがおススメです。

今回は1列日付データを増やした、以下のサンプルデータをA1セルに貼り付けて、販売実績という名前のテーブルに変換して利用ください。

日付	営業担当	商品	売上金額
2024/01/05	山田	A	¥1,500,000
2024/01/12	田中	B	¥2,200,000
2024/01/18	佐藤	C	¥800,000
2025/01/25	山田	B	¥3,000,000
2025/01/30	田中	A	¥1,200,000
2024/02/03	佐藤	B	¥1,800,000
2024/02/10	山田	C	¥700,000
2024/02/15	田中	A	¥2,500,000
2025/02/20	佐藤	B	¥900,000
2025/02/28	山田	A	¥1,600,000
2025/03/05	田中	C	¥600,000
2024/03/12	佐藤	A	¥2,000,000
2024/03/18	山田	B	¥1,100,000
2024/03/25	田中	C	¥2,800,000
2025/03/30	佐藤	B	¥750,000
2024/04/03	山田	A	¥1,900,000
2025/04/10	田中	B	¥950,000
2024/04/15	佐藤	C	¥2,400,000
2025/04/20	山田	B	¥650,000
2025/04/28	田中	A	¥1,700,000
2025/05/05	佐藤	B	¥1,000,000
2025/05/12	山田	C	¥2,100,000
2024/05/18	田中	A	¥780,000
2025/05/25	佐藤	B	¥2,600,000
2024/05/30	山田	C	¥550,000
2024/06/03	田中	A	¥1,400,000
2025/06/10	佐藤	B	¥850,000
2024/06/15	山田	C	¥2,300,000
2025/06/20	田中	B	¥600,000
2024/06/28	佐藤	A	¥1,850,000
2024/07/05	山田	B	¥900,000
2025/07/12	田中	C	¥2,700,000
2024/07/18	佐藤	A	¥500,000
2025/07/25	山田	B	¥1,550,000
2025/07/30	田中	C	¥1,250,000
2024/08/03	佐藤	A	¥2,050,000
2024/08/10	山田	B	¥700,000
2025/08/15	田中	C	¥2,900,000
2024/08/20	佐藤	B	¥600,000
2024/08/28	山田	A	¥1,750,000
2025/09/05	田中	B	¥1,150,000
2024/09/12	佐藤	C	¥2,250,000
2024/09/18	山田	B	¥800,000
2024/09/25	田中	A	¥2,650,000
2025/09/30	佐藤	C	¥900,000
2025/10/03	山田	A	¥1,950,000
2024/10/10	田中	B	¥750,000
2025/10/15	佐藤	C	¥2,550,000
2024/11/13	山田	B	¥580,000
2024/11/21	田中	A	¥1,650,000
2024/12/12	山田	B	¥940,000
2024/12/17	佐藤	C	¥1,230,000
2024/12/27	田中	A	¥1,600,000

👆もともと2024年のデータだったものを無理に 2025年のデータも加えたんで、若干データの並びが変ですが気にしないでください。

テーブル化して全データを 構造化参照 販売実績[#ALL] で取得して,
 QUERY関数の第1引数として使っていきましょう！

group by句、pivot句とスカラー関数

group by句 や pivot句は スカラー関数と組み合わせて、加工した結果をグループ集計することが可能です。

前回、お題の中で変化球的な使い方で登場した スカラー関数との組み合わせですが、改めてどんな時に活用できるのか？を見ていきましょう。

✅group by句、pivot句は スカラー関数と組み合わせて 大文字・小文字の表記ブレを吸収して集計が出来る

QUERY関数の group by句やpivot句のグループ化（一意化）は、GoogleスプレッドシートのUNIQUE関数と同等レベルの厳密さとなっています。

※「Googleスプレッドシートの」と書いたのは、ExcelのUNIQUE関数は一致判定がイコール一致よりも緩い為です

Excel / Googleスプレッドシート UNIQUE関数の違い

QUERY関数の group by句、pivot句は、半角・全角、小文字・大文字、ひらがな・カタカナ をきっちり区別してグループ化をします。

※数値と文字列の数字の区別は、そもそもQUERY関数が1つの列に型が混在できない仕様なので対象外

もし、これらの表記のゆらぎを 吸収した上で グループ集計をしたい場合、QUERY関数の内部処理だけで 大文字・小文字 を区別しない集計が可能です。

スカラー関数の upper() もしくは lower() を group by や pivotと組み合わせることで、上の画像のように 大文字・小文字が混在するテキストデータを 大文字（小文字）に揃ええてグループ化することが出来ます。

upper()、lower()については、select句の回でも触れています。

select句合わせ技2. スカラー関数で列を加工

一方で、これ以外の 全角・半角や ひらがな・カタカナ の表記ブレに関しては QUERY関数ではどうにもなりません。

QUERYに渡す前に第1引数の段階でデータをクレンジングしておく必要があります。

✅group by句、pivot句は スカラー関数と組み合わせて 年・月など 期間で 集計が出来る

group by句やpivot句は、 日付を加工するスカラー関数 を組み合わせることで、日付データを 年、月などの期間でグループ化して集計が出来ます。

これは利用頻度の高い集計パターンです。

👆上の画像の式は、販売実績テーブル 1列目の日付データを対象に

select句、gropu by句で year(Col1) と指定することで、年毎の 売上合計を集計しています。

同じく pivot句でも

このように pivot month(Col1)+1 と指定することで、月ごとの 売り上げの平均（avg）を横にグループ集計出来ます。

月を +1としている理由は、QUERY関数シリーズの12でやりましたね！

【注意】QUERY関数の スカラー関数 month() は 0はじまりでひと月ズレる

日付、日時、時刻を操作するスカラー関数について詳しく知りたい方は、以下のnoteを参照ください。

ただし月だけで集計した場合、今回のような元データの 販売実績テーブルは、2024年と2025年の年をまたぐデータが含まれていると

👆このように、たとえば1月の平均値は 2024年1月と2025年1月の両方のデータの avg（平均値）を算出している状態となります。

年をまたいだデータを月だけでグループ集計する場合は注意しましょう。

Q1. 年・月で 売上合計をピボット集計したい

このような複数年にわたるデータを集計する場合は、年と月でピボット集計するのが良いです。

これをお題としてみましょう。

👆上のように1列めの日付データをもとに、縦方向に月、横方向に年をグループ化して4列目の売上金額の合計をピボット集計したい場合、QUERY関数でどのような式をつくれば良いでしょうか？

基本問題なので簡単ですね。考えてみましょう！

↓↓
回答はここから。
↓↓

A1. 年・月で 売上合計をピボット集計する

回答です。

これは大丈夫ですね？

考え方としては、こんな流れです。

Q2. 年・月で 売上合計をピボット集計した上で 前年比の差分と割合も表に入れたい

それでは、この流れで応用問題にも挑戦してみましょう。

QUERY関数で生成した 年毎、月毎の売上集計表に、👆画像のように

前年比（差分）　月ごとの 2025年売上合計 - 2024年売上合計
前年比（割合）　月ごとの  2025年売上合計 / 2024年売上合計

を入れたい場合、QUERY関数でどのような式を記述すればよいでしょうか？

まだ見出しを加工する label句を学んでないので、見出し修正は無しとします。またパーセント表示などの表示形式はシートの設定で対応するものとします。

難しく考えすぎないでくださいね。チャレンジしてみましょう！

↓↓
回答はここから。
↓↓

A2. 年・月で 売上合計をピボット集計した上で 前年比の差分と割合も表に入れる

回答です。

=QUERY(
  QUERY(販売実績[#ALL],"select month(Col1)+1,sum(Col4)
   group by month(Col1)+1 pivot year(Col1)"),
   "select Col1,Col2,Col3,Col3-Col2,Col3/Col2"
)

これはQUERY関数一発とはいきません。

QUERY関数の結果をさらにQUERY関数で集計する、ダブルQUERY式で対処します。

1回目のQUERY式の出力結果が

月ごとの 2024年売上合計 ・・・ Col2
月ごとの 2025年売上合計 ・・・ Col3

となっているので、

前年比（差分）　Col3 - Col3
前年比（割合）　Col3 / Col2

で表すことが出来ます。

あとは、これを既存の列の後ろに select句で指定してあげればOK。

QUERY関数の結果をさらにQUERY関数や他のシート関数で加工するテクニックは少し難しいかもしれませんが、非常に便利ですし必要になるケースが結構あります。頑張ってマスターしましょう！

group by句、pivot句と 算術演算子

group by句 や pivot句は 算術演算子と組み合わせて、数値を加工した結果でグループ集計することも可能です。

これを理解するとQUERY関数の解像度が一気に上がります。

ただし独特な仕様があるのでエラーが発生しやく注意点が必要です。見ていきましょう！

✅group by句、pivot句は 算術演算子と組み合わせて数値を加工して集計が出来る

たとえばお題1の回答の

👆 この式。

group by month(Col1)+1

この部分でスカラー関数に加え、 +1 という演算子を使った計算をした上でグループ化しているのがわかりますね。

同じく前回の お題3では

=QUERY(A1:A18,"select count(Col1) pivot Col1+0")

このように pivto Col1 + 0 という式で、グループ化する数値に影響が出ないように加工しました。

group by句で算出演算子を活用するケースは少し複雑なものが多いので、超応用例として触れていきたいと思いますが、ポイントは

この2つの活用です。

Q3. 偶数月と奇数月 それぞれの売上の平均値を集計したい

算術演算子を組み合わせたグループ集計を理解する為に、シンプルなお題にチャレンジしてみましょう。

販売実績データ の日付を 偶数月と奇数月、それぞれの売上平均を集計したい場合、QUERY関数でどのような式を組めばよいでしょうか？

ただし、結果の集計表は 偶数月は 0、奇数月は 1の表示で良いものとします。（format句を学ぶ前なので）

考えてみましょう！

↓↓
回答はここから。
↓↓

A3. 偶数月と奇数月 それぞれの売上の平均値を集計する

回答です。

👆こんな式になります。

クエリ文の中では数値の 奇数、偶数を判別するような関数は使えませんが、

月 を month(Col1) + 1 で取得した上で、これを  2で割った余りを

(month(Col1)+1)%2

で計算してグループ化することで、

2で割った余りが 0の時・・・偶数月
2で割った余りが 1の時・・・奇数月

としてグループ化出来ます。

小学校算数の範囲ですが、ポイントは

(month(Col1)+1)

このようにカッコで括って先に +1を計算処理してから

%2（2で割った余を求める）

とすることです。

これによって 全ての日付データ（月の数値1～12）が 0か1に分類されるので、

後は group by でグループ化して avg()で 集計すればOK。

ちなみに 今後登場する label句、format句を応用すると

👆 このように表示を成形した 表を QUERY関数 一発で出力することができます。

✅【注意】group by句、pivot句で使う 算術演算子の特殊な仕様

group by句 や pivot句 で使う 算術演算子には、2つ特殊な仕様があります。

1. group by句やpivot句でグループ化した列は、select句でさらに算術演算子で処理できる

2. group by句やpivot句の算術演算子は 同じ数値が2回以上登場できない

よくわからないと思うので実例で解説していきます。

特殊仕様1. group by句やpivot句でグループ化した列は、select句でさらに算術演算子で処理できる

👆こちらを見てください。

先ほどの お題3の回答の式と ほぼ同じですが、

group by (month(Col1)+1)%2

に対して select句で指定しているのは

select (month(Col1)+1)%2*10

と、group by で指定した  (month(Col1)+1)%2 を10倍した式が入っています。

つまり、group by句で演算子で加工したグループを select句で出力する際に 算術演算子を追加して、もう一加工できるってことです。

これは逆は成り立ちません。

このように group by 句で  (month(Col1)+1)%2*10 としたのに、select句では  (month(Col1)+1)%2 としてしまうと 集計関数でもgroup by句で指定した列でもない 列がselect で選択されたことになり エラーとなります。

これが、

の意味です。

特殊仕様2. group by句やpivot句の算術演算子は 同じ数値が2回以上登場できない

2番目の

はさらに特殊です。

👆の画像では 一番上の式は 正しく出力されているのに、ほぼ同じ下の2つの式は #N/Aエラーとなっています。

これは一番上の式は登場する数字が +1 %2 +3 と重複していないのに対して、

2番目の式は +1 %2 +1 で  「1」を2回使用
3番目の式は +1 %2 -2 で  「2」を2回使用

と数値が重複使用となっている為です。

演算子による式が複雑だとか長いとかではなく、

group by句や pivot句の 中で演算子を使う場合は、同じ数値や同じスカラー関数が2回登場できない縛りがあるんです。

かなり特殊ですね。。

では、どうしても (month(Col1)+1)%2 をさらに -2 としたい場合はどうすればよいか？

 (month(Col1)+1)%2 - 2 の 2を まだ登場していない他の数字で置き換えた

5 - 3　や　8/4

👆ならアリってことです。完全に数字クイズの世界ですねｗ

QUERY関数のスカラー関数、算術演算子をフル活用して集計する超応用例

最後に スカラー関数 と算術演算子、そして2つの特殊な仕様の理解を総動員する超応用例に挑戦しましょう！

ほとんどの人が「QUERY関数だけでは処理できないでしょ」と思うお題を、スカラー関数と算術演算子をフル活用して QUERY関数だけで実現します。

Q4. QUERY関数で 年と 上期、下期でピボット集計したい

シンプルなようで結構難しいお題です。

販売実績データの1列目日付を

1～6月を上期として 数字の 1
7～12月を下期として 数字の 2

として売上金額の合計を、画像右のような

（上）　年、半期 で売上金額をグループ集計
（下）　縦に年、横に半期で 売上金額をピボット集計

これらの表を作りたい時、QUERY関数でどのような式を組めばよいでしょうか？

条件としてQUERY関数の式は

=QUERY(販売実績[#ALL],"select

で始まるもとのします。

この縛りがあると超応用例なんで、どうしても難しい場合は違うやり方でもOKです。

考えてみましょう！

↓↓
回答はここから。
↓↓

A4. QUERY関数で 上期、下期集計する

回答です。

どうしても =QUERY(販売実績[#ALL],"select で始まる式が作れなかった人は

=ARRAYFORMULA(QUERY(
  {{"半期"; IF(month(販売実績[日付])>6,2,1)},販売実績[#ALL]},
  "select year(Col2),Col1,sum(Col5) group by year(Col2),Col1"
))

もしくは

=ARRAYFORMULA(QUERY(
  {IFERROR((month(A:A)>6)+1,A1),A:D},
  "select year(Col2),sum(Col5) where Col2 is not null
   group by year(Col2) pivot Col1"
))

こんな式を作ったんじゃないでしょうか？

QUERY関数の第1引数の段階で 日付を 上期1、下期2に変換したものを 元データと横連結してから集計する方法で、もちろんこれでもOKです。

では、=QUERY(販売実績[#ALL],"select で始まる式はどう作るか？

その回答がコチラです。

クエリ文の中で日付データを 上期、下期に分類するやり方は大きく2つ方法があるので、上と下でそれぞれのやり方を使ってみました。

=QUERY(販売実績[#ALL],"select year(Col1),(quarter(Col1)+quarter(toDate(Col1))%2)/2,
sum(Col4) group by year(Col1), quarter(Col1)+quarter(toDate(Col1))%2")

=QUERY(販売実績[#ALL],
  "select year(Col1),(quarter(Col1)+quarter(toDate(Col1))%2)/2,sum(Col4)
   group by year(Col1), (quarter(Col1)+quarter(toDate(Col1))%2)"
)

どちらも割と短い式だとは思いますが、ぱっと見では理解が難しいですよね。

解説していきます。

 まずQUERY関数のクエリ文の中で使えるスカラー関数には、日付を上期、下期に加工するものがありません。

ここはまず一番近い日付から四半期の数値に加工するスカラー関数 quarter() を使って

日付を1～4の四半期の数値に変換します。

ここから

に変換したいのですが、シート関数だったら IFを使う方法以外に

QUOTIENT関数 や CEILING関数 (または INT関数）を使う方法が思いつきますが、クエリ文にはこれらに該当する関数がありません。

ここが少し難しいのですが、これをクエリ文の算術演算子、そしてスカラー関数で代替する方法が2つあります。

🎯QUERY関数内で QUOTIENT関数を代替する

まず1つ目は QUOTIENTを代替する方法として、%（剰余演算子）を利用する方法です。

これは前に QUERY関数シリーズ 第5回、select句の超応用例で一度お題にしてますね。

Q1. QUERY関数の select で演算子を使って シート関数の QUOTIENTと同じ割った時の整数部分のみを取得したい

再度、算数のおさらいをすると 👆を今回のケースにあてはめると

QUOTIENT( quarter(Col1) - 1, 2 ) / 2 + 1

を実現したいので

となります。

このように selet句で確認すると 1と2に分類することが出来ました。

この考え方が QUOTIENT関数を QUERY関数のクエリ文の中で代替する方法となります。

🎯QUERY関数内で CEILING関数やINT関数を代替する

もう1つは スカラー関数 toDate() を使う方法です。

QUERY関数シリーズの日付・日時・時刻の回では、

日付型　▶dateDiff()▶　数値型　▶toDate()▶　日付型

という処理が登場しました。

今回は toDate() の特性である

スカラー関数 toDate()の使い方

を使って、

数値型　▶toDate()▶　日付型　▶day()またはdateDiff()▶　数値型

という処理をします。

今回の場合 quarter(Col1)/ 2 とすると

となりますが、これだと 0を1日（ついたち）、1増えるごとに1日（24時間）増えると考えた時に 0.9999までは 1日ですが、1となると 2日目の 0:00 となってしまうので、

(quarter(Col1)-1)/ 2 として

とします。これを*24*60*60*1000 でエポックミリ秒変換をしてから toDate() すると

このようになりました。

この後の　日付型　▶　数値型　の変換は基本的には

dateDiff( 日付型の列 , date '1970-1-1')

このように dateDiff() を使うのですが、今回のように1～31であれば日付部分を取り出す

day(日付型の列)

が簡単です。

これがQUERY関数内で CEILING関数やINT関数を代替する方法です。

⚠️group by句、pivot句 でのNAエラー回避

2つの代替テクニックを解説しましたが、これを group by句や pivot句で使う場合は、前述した通り注意が必要です。

「group by句やpivot句の算術演算子は 同じ数値が2回以上登場できない」

という制限に今回作成した式だと、色を付けた箇所が抵触する為、#N/Aエラーになってしまいます。

60 が2回登場する下のtoDate()を使う下の式は簡単ですね。
 / 2 *24*60*60*1000 の部分を計算しちゃえばよいだけです。

=QUERY(販売実績[#ALL],
  "select year(Col1),sum(Col4) group by year(Col1)
   pivot day(toDate((quarter(Col1)-1)*43200000))"
)

回答の式の 43200000 は、こういう意味だったわけです。

一方上の式は

quater(Col1) が2回
1が 3回
2が 2回

と重複が多いので大変です。まずは式を整理して

さらに 2で割る今回のケースに限りますが、

+ 1 - (quarter(Col1) - 1 )%2 の部分は 

quarter(Col1) が1の時　1
quarter(Col1) が2の時　0
quarter(Col1) が3の時　1
quarter(Col1) が4の時　0

となるので、これは + quarter(Col1)%2 と置き換えできます。

つまり

となります。

残っている 2の重複は (quarter(Col1) + quarter(Col1)%2) / ( 3 - 1 )

としてもよいんですが、これは上で学んだ特殊仕様1の

1. group by句やpivot句でグループ化した列は、select句でさらに算術演算子で処理できる

を利用して /2 は select句の方にだけつけることで 回避しましょう。

また quarter(Col1) の重複は、 日付型の Col1に影響を与えない スカラー関数 toDate() を使って

(quarter(Col1) + quarter(toDate(Col1))%2) / 2

とすることで重複利用エラーを回避します。

もともと 日付型である Col1 は toDate(Col1) としても中身は一緒であることを利用しています。

=QUERY(販売実績[#ALL],
  "select year(Col1),(quarter(Col1)+quarter(toDate(Col1))%2)/2,sum(Col4)
   group by year(Col1),quarter(Col1)+quarter(toDate(Col1))%2"
)

回答の2つの式の意味は、理解出来ましたでしょうか。

この超応用例テクニックが使えると、たとえば 販売実績テーブルの売上データを 500,000（50万）単位で価格帯をグルーピングして、各営業担当の販売実績が どの価格帯が何回あったかを集計

こんな表をQUER関数一発で実現できます！

これは他の句も使用しているので、もう少し先の回で取り上げたいと思います。

group by句、pivot句 次回は お題中心、超応用例 チャレンジへ！

今回は QUERY関数の group by句、pivot句の 応用的な使い方を学びました。

QUERY関数では対応が難しそうな、数値列に対する QUOTIENT関数や CEILING関数、INT関数的な処理を理解できた方は、確実にレベルアップしています。

次回は、超応用例のお題ラッシュ！

今回のお題でも少し登場したQUERY関数の結果をさらにQUERY関数や他のシート関数で処理する超応用例、そしてQUERY関数で 集計行・集計列を入れた集計表を生成する超応用例について学んでいきましょう！