Googleスプレッドシート QUERY関数 超応用例 16（group by と pivot 超応用）
Googleスプレッドシートの最強集計関数 QUERY関数について書いたnoteの第16回です。
👇これまでのQUERY関数シリーズは マガジンにまとめています。QUERY関数に 沼りたい人は必読です！
前回はgroup by句、pivot句の応用的な使い方を学びました。
今回はお題中心、ボスラッシュのような超応用例が連発です！
QUERY関数のピボット集計で 合計行・合計列を出力するテクニックも紹介しています。
QUERY関数の結果を他の関数と組み合わせる超応用例
前回も登場した QUERY関数の結果をさらに他の関数で処理する超応用例にチャレンジしてみましょう。
元データは シリーズ14で使ったものと同じで、以下のサンプルデータをA1セルに貼り付けて、販売実績miniという名前のテーブルに変換して利用ください。
営業担当 商品 売上金額
田中 B ¥1,200,000
田中 C ¥2,500,000
山田 A ¥1,600,000
佐藤 B ¥900,000
山田 C ¥700,000
佐藤 B ¥1,800,000
山田 A ¥1,500,000
佐藤 A ¥2,000,000
田中 B ¥2,200,000
田中 C ¥600,000
佐藤 C ¥800,000
山田 A ¥3,000,000Q1. QUERY関数の集計表の空白を 0埋めしたい
まずは シリーズ14で少し触れた ピボット集計表の 0埋めに挑戦してみましょう。
元データの 販売管理表mini から右のようなピボット集計を生成したいのですが、売上金額を空欄のところを 0としたい場合、どのような式を組めばよいでしょうか？
QUERY関数に他の関数を組み合わせる 基本のお題です。（超応用例ではありません）
考えてみましょう！
↓↓
回答はここから。
↓↓
A1. QUERY関数の集計表の空白を 0埋めする
回答です。
=LET(x,QUERY(販売実績mini[#ALL],"select Col2,sum(Col3)
group by Col2 pivot Col1"),
ARRAYFORMULA(IFERROR(--x,x)))
式の最後の部分 IFERROR(--x,x) は、丁寧に書く場合は
IF(x="",0,x)
こうなります。考え方としては
「QUERY関数の結果に対して、空白だったら 0、それ以外ならそのままの値を返す」
という処理です。
👆まず普通に ピボット集計をするQUERY関数の式は大丈夫ですね。
この結果に対して処理を行うのですが、ここで必要になるのが
このQUERY関数の結果を変数化して再利用できる LET関数
そして結果の配列に対して処理を行う ARRAYFORMULA関数
この2つです。
どうしても、「空白だったら 0、それ以外ならそのままの値を返す」の部分で、QUERY関数の結果を2回記述する必要があるので、ここでLET関数が必須となります。（以前はQUERY関数のまったく同じ長い式を2回記述していました）
TEXT関数で 表示形式を "0;;0;@" とすることで、2回記述しない方法もあるんですが、
TEXT関数を使うと数値が全て文字列になってしまうのでおススメしません。
というわけで
=LET(x,QUERY(販売実績mini[#ALL],"select Col2,sum(Col3)
group by Col2 pivot Col1")
LET関数で QUERY関数の結果を xと置いてから
ARRAYFORMULA(--x)
ARRAYFORMULAで全体に対して --（マイナス2回）で 空白を 0に変換、数値は 変わらずそのままの数値が返り、
文字列である見出し部分だけ #VALUE!エラーとなっちゃうんで、
ARRAYFORMULA(IFERROR(--x,x))
これをIFERRORでエラーだったらそのまま元の値 x を返すとしています。
QUERY関数と他の関数を組み合わせる処理では、LET関数でQUERY関数の結果を変数化する流れが基本です。
是非使えるようになりましょう！
Q2. QUERY関数で 集計関数を2行表示とした集計表を作成したい
QUERY関数シリーズの14で少し触れた、QUERY関数単体では生成できない表にチャレンジしてみましょう。
このような 担当毎、商品毎の 売上金額の最小値、最大値を2行で集計する表を生成したい場合、どのような式を組めば良いでしょうか？
なお、見出し行は1行とし、担当と商品をカンマ区切りするとします。
これ group byで作った表を TRANSPOSEしてコネコネする方法もあるんですが、今回は group by句 と TRANSPOSE関数を 使わない方法でチャレンジしてみましょう！
↓↓
回答はここから。
↓↓
A2. QUERY関数で 集計関数を2行表示とした集計表を作成する
回答です。
=LET(
x,QUERY(販売実績mini[#ALL],"select min(Col3) pivot Col1,Col2"),
y,QUERY(販売実績mini[#ALL],"select max(Col3) pivot Col1,Col2"),
{{"";"min";"max"},UNIQUE({x;y})}
)解説していきましょう。
QUERY関数のpivot句では 集計を2行とした表は生成できないので、minとmax それぞれQUERY関数で集計したもの生成し、
それぞれ LET関数で、x、y と変数化します。
これを中カッコで縦連結して
重複する見出し行をUNIQUE関数で一意化（上の見出しが残る）
この左側に列見出しを 式の中で作成して
{{"";"min";"max"},UNIQUE({x;y})}
横連結すれば完成です。
UNIQUE関数を使わず y の方を INDEX(y,2) で見出し行を除いたデータ部分だけとして連結でもOKです。
A2b. QUERY関数で 集計関数を2行表示とした集計表を作成する（似たような式をLET & LAMBDAで 式内名前付き関数化）
もう1段階ハイレベルな回答があります。
今回の式の中の
x,QUERY(販売実績mini[#ALL],"select min(Col3) pivot Col1,Col2"),
y,QUERY(販売実績mini[#ALL],"select max(Col3) pivot Col1,Col2"),
minとmax部分だけが違うのに、同じような式が 2回登場するのは煩雑ですよね。
これをスッキリ記述できるのが、LET関数+LAMBDA関数による 式内名前付き関数化 です。
=LET(
f,LAMBDA(v,{{"";v},QUERY(販売実績mini[#ALL],"select "&v&"(Col3) pivot Col1,Col2")}),
UNIQUE({f("min");f("max")})
)QUERY関数の min、maxと 左側の列見出しで利用する min、maxをまとめて生成する関数を
f,LAMBDA(v,{{"";v},QUERY(販売実績mini[#ALL],"select "&v&"(Col3) pivot Col1,Col2")})
この式は v という1つの変数をとる LAMBDA関数を LET関数の 数式変数化で、名前付き関数 f と定義しています。 （ファンクションの f です）
これによって f("min")、f("max") と記述するだけで QUERY関数 +左側に 見出し列 を生成しています。
あとは これを縦連結してUNIQUEで一意化すれば、よりシンプルな式（といっても可読性は下がるかもですが・・）で、同じ結果を得ることが出来るわけです。
UNIQUE({f("min");f("max")})
LET関数+LAMBDA関数による 式内名前付き関数化は、どこかのタイミングでnoteで解説を書きたいと思います。
Q3. 集計の種類をプルダウンで可変にして自由に切り替えたい
他の関数と組み合わせる超応用例の最後は、集計の種類をプルダウンで可変にするお題にチャレンジしてみましょう。
👆 のように I3セルに 集計関数を選択できる複数選択可能なプルダウンを設定しています。
販売実績miniのテーブルを対象に営業担当ごとの プルダウンで選択した集計を表に生成するには、どのような式を組めばよいでしょうか？
なお、プルダウンが未選択（I3セルが空白）の時は「集計を選択してください」という文字を表示するものとします。
複数選択プルダウンの設定や中身については、過去noteを参照ください。
これは、どちらかというとQUERY関数内部で他の関数を組み合わせる方がメインですね。
考えてみましょう！
↓↓
回答はここから。
↓↓
A3. 集計の種類をプルダウンで可変にして自由に切り替える
回答です。
幾つか処理方法はあるんですが、2パターンほど用意しました。
ポイントは
クエリ文の中身は全て文字列。だから必要な文字列を数式で生成すれば自由に処理を切り替え出来る
という点です。
この部分が理解で出来ていれば、select句やwhere句の時と同じように、他のセルを参照して集計関数を可変にするイメージができると思います。
=ARRAYFORMULA(IFERROR(
QUERY(販売実績mini[#ALL],"select Col1,"&
JOIN(",",SPLIT(I3,", ")&"(Col3)")&" group by Col1"
),"集計を選択してください"
))1つは複数選択プルダウンを SPLIT関数で分割して、それぞれの選択した集計関数に &"(Col3)" で(Col3) の文字を後ろにつけてから、JOIN関数で カンマ区切りで連結する処理です。
select句の超応用例でも登場したパターンですね。
注意点として
SPLIT(I3,", ")&"(Col3)"
👆この部分の処理が配列処理となる為、ARRAYFORMULA関数が必要となります。
プルダウンでなにも選択しなかった時はエラーとなるので、
IFERROR関数で "集計を選択してください" を返すように設定すれば完成です。
もう1つの方法は 分割や配列処理を使わないので、より短い記述とできます。
=IFERROR(
QUERY(販売実績mini[#ALL],"select Col1,"&
REGEXREPLACE(I3,"(,|$)","(Col3)$1")&" group by Col1"
),"集計を選択してください"
)ポイントは正規表現が使えて変換した値をキャプチャグループとして変換後にも使える REGEXREPLACE関数です。
REGEXREPLACE(I3,"(,|$)","(Col3)$1")
👆 この式は I3セル（プルダウン）内の
(,|$) ・・・・ ,（カンマ）または文末を検索して キャプチャ（後で $1で呼び出して利用できるように保存）して
(Col3)$1 ・・・ 文字列 (Col3) と $1（検索で保存した文字）に置換
つまり
, ▶ (Col3),
$(文末) ▶ (Col3)
この置換を1回の記述で処理しています。
これによって、
プルダウンの値をクエリ内で使える記述に変換しているわけです。
選択なしの時の処理は、IFERRORが一番シンプルかなと思います。
QUERY関数と他の関数を組み合わせた超応用例3問、いかがだったでしょうか？
QUERY関数 group by句、pivot句の苦手な処理を突破する超応用例
かなり万能なQUERY関数ですが、苦手とする集計や他の方法で処理した方がよいケースがあります。
QUERY関数の group by句・pivot句で 何が出来ないのか？を理解しましょう。
✅group by句は 集計関数以外のグループ集計が出来ない
group by句による集計は、クエリ文で利用できる集計関数を使った集計しか出来ません。
つまり対応出来る集計は、avg() 平均値、count() 個数、max() 最大値、min() 最小値、sum() 合計 の5つだけです。
たとえば
月ごとに 販売された商品が何種類だったか？（ユニークな個数）を集計したい
や
月ごとに販売された商品を文字列としてカンマ区切りで連結したい
これらは、グループ毎の集計っぽいからQUERY関数で出来そう！？と思うかもしれませんが、対応する集計関数がクエリ文に存在しない為、QUERY関数では基本的には対処出来ません。
特に文字列を連結するような集計もどきの処理は、QUERY関数の苦手とするところです。
Q4. QUERY関数で 年・月ごとに販売した商品をカンマ区切りでクロス集計したい
QUERY関数では基本的には対処できない、苦手と書きましたが、前述したように「他のシート関数」と組み合わせることで、この弱点を突破することができます。
特にLET、LAMBDA他 新関数登場で、現在はだいぶ楽に記述できるようになりました。
というわけで超応用例のこちらのお題にチャレンジしてみましょう！
販売実績テーブルのデータをもとに、横に年、縦に月で その年月に売れた商品（3列目）のユニークな値を カンマ区切りで👆のように集計表にしたい。
どのような式を組めばよいか？
データは前回と同じ 👇を 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考えてみましょう！
↓↓
回答はここから。
↓↓
A4. QUERY関数で 年・月ごとに販売した商品をカンマ区切りでクロス集計する
回答です。
=LET(
x,QUERY(販売実績[#ALL],"select year(Col1),month(Col1)+1,max(Col3)
group by year(Col1),month(Col1)+1 pivot upper(Col3)"),
y,BYROW(x,LAMBDA(r,
{CHOOSECOLS(r,1,2),TEXTJOIN(",",TRUE,CHOOSECOLS(r,SEQUENCE(COLUMNS(x)-2,1,3)))})),
QUERY(y,"select Col2,min(Col3) group by Col2 pivot Col1")
)「うげーやっぱ長くて複雑」と感じるか「あれ、意外と短くて処理は3ステップ？」と感じるか、そこは人それぞれかと思います。
まず、第一ステップがQUERY関数によるピボット集計です。
=QUERY(販売実績[#ALL],"select year(Col1),month(Col1)+1,max(Col3) group by year(Col1),month(Col1)+1 pivot upper(Col3)")
ここでのポイントは max(Col3) と pivot upper(Col3) を組み合わせることで、縦方向のグループ 年・月毎に売れた商品が行で集約できるという点です。
文字列にも使える集計関数、min(),max()はここで活躍します！
あとは 最終的な集計で使う1,2列目はそのままで、3,4,5列目をカンマ区切りで1列にすればよいんですが、ここは行毎に処理が出来る BYROWを使うのが簡単です。
ステップ1のQUERY関数の結果をLET関数で xと置いて
BYROW(x,LAMBDA(r,{CHOOSECOLS(r,1,2),
TEXTJOIN(",",TRUE,CHOOSECOLS(r,SEQUENCE(COLUMNS(x)-2,1,3)))}))
行毎に 1,2行目は CHOOSECOLS(r,1,2) で取得してそのまま利用。
3列目以降は 全てを CHOOSECOLS(r,SEQUENCE(COLUMNS(x)-2,1,3)) で取得して、
TEXTJOINでカンマ区切りで連結。最後に中カッコで横に連結とします。
ExcelのTAKE関数やDROP関数が輸入されてれば、この部分がもうちょい楽に書けるんですが、無いものは諦めてCHOOSECOLS関数とSEQUENCE関数で代用しています。
この段階での3列目の見出しは変な状態ですが、これは最後のQUERY関数のピボット集計で消えるので気にしなくてOK。
このBYROW関数の結果を yと置いて、最後にもう一発QUERY関数で
QUERY(y,"select Col2,min(Col3) group by Col2 pivot Col1")
既にグループ化されている表を min(Col3) で文字列はそのままに、最終形の文字列ピボット集計表としています。
なかなかの難易度だったんじゃないでしょうか？
ただQUERY関数使うよりも
=ARRAYFORMULA(LET(
日付,A2:A54,商品,C2:C54,
y,TOROW(UNIQUE(YEAR(日付))),m,UNIQUE(month(日付)),
集計,MAP(IF(m,y),IF(y,m),LAMBDA(_v1,_v2,
IFERROR(TEXTJOIN(",",TRUE,FILTER(商品,YEAR(日付)=_v1,month(日付)=_v2))))),
{{"",y};{m,集計}}
))年、月をUNIQUEにした上で、MAP 内でFILTER+TEXTJOINで該当する商品を取得して、最後に連結した方が 式は短いんですよね。。
そもそも以前書きましたが、この手の処理は ピボットテーブルを使えば圧倒的に簡単です。
というわけで、文字列をピボット集計する処理は、QUERY関数でも頑張れば出来るけど他の方法がおススメってことです。
✅group by句、pivot句では グループ毎の最大の行を抜き出すといったことは出来ない
もう1つQUERY関数では難しい処理がこれです。
=QUERY(販売実績[#ALL],"select Col2,max(Col4) group by Col2")
この式で 営業担当（Col2）ごとの 売上（Col4)の最大値は集計できます。
しかし、そのデータ（行）の 日付、商品を合わせて出力したい！といったことは出来ません。
これは 重複がない（各最大値のデータが1つのみ）場合は、SORTN関数であれば一撃で処理できるお題です。
他にもFILTER関数やピボットテーブルを使う方法もありますが、QUERY関数には向いてない処理と言えるでしょう。
Q5. QUERY関数で 担当ごとの売上最大のデータ（行）を出力したい
しかしQUERY関数には向いてない処理でも、出来るか？出来ないか？で言えば、他の関数と組み合わせてQUERY関数で対応することは可能です！（たぶんこんなことをやるのは、mirのnoteだけですｗ）
これをお題としてみましょう。データはお題4と同じ 販売実績を利用ください。
考えてみましょう！
↓↓
回答はここから。
↓↓
A5. QUERY関数で 担当ごとの売上最大のデータ（行）を出力する
回答です。
=LET(
x,QUERY(販売実績[#ALL],"select max(Col4) pivot Col2"),
QUERY(販売実績[#ALL],"where "&
JOIN(" or ",BYCOL(x,LAMBDA(c,"Col2='"&INDEX(c,1)&"' and Col4="&INDEX(c,2))))
)
)QUERY関数を2段階で処理します。
流れとしては
担当毎の最大売上の集計表を QUERY関数で生成
▼
この担当と最大売上の組み合わせのいずれかに合致する行を 2回目のQUERY関数の where句で抽出
となります。
まず 初手の 「担当毎の最大売上の集計表を QUERY関数で生成」ですが、group by句で作ると
見出し行が後々の処理で邪魔になるんで pivot句で生成します。
これを xと置いて where句で使う クエリ文を
JOIN(" or ",BYCOL(x,LAMBDA(c,"Col2='"&INDEX(c,1)&"' and Col4="&INDEX(c,2)))
このように BYCOL関数 で列毎に処理して 最後に JOIN関数で " or "(orの前後に半角スペースが入った文字列） で連結して生成します。
QUERY(販売実績[#ALL],"where "&JOIN(" or ",BYCOL(x,LAMBDA(c,"Col2='"&INDEX(c,1)&"' andCol4="&INDEX(c,2)))))
where句内で 数式で生成した and, or を組み合わせたクエリ文により
担当が 佐藤 かつ（ and ） 売上金額が ¥2,600,000 または（ or ）
担当が 山田 かつ（ and ） 売上金額が ¥3,000,000 または（ or ）
担当が 田中 かつ（ and ） 売上金額が ¥2,900,000
に該当する行
という条件でフィルタすることで、
QUERY関数で 各担当の最大の売上金額とその時の日付、商品のデータを取得するという処理を実現しています。
さすがに SORTN関数に比べると式が複雑で手間がかかりますが、こちらは最大値が2つ以上あっても
しっかり取得できるのが利点です。
ま、これもQUERY関数を使うより FILTER関数かピボットテーブルを使った方が簡単なんですけどね。。
ピボットテーブルを使ったこのテクニックは、そのうちnoteを書きたいと思います。
group by句、pivot句と 総合計、グループ計
最後に QUERY関数で集計行・集計列を生成したいというお題です。
需要は高いんですが、残念ながら QUERY関数の group by句 や pivot句を使った集計では、「基本的には」総合計や グループ小計を出力することが出来ません。
✅group by句や pivot句は、総合計やグループ計が出力できない
例えば 👆 こんな表や
👆こんな表は、QUERY関数で一発で出力するのは無理ってことです。
ただし「基本的には」と書いた通り、QUERY関数のポテンシャルを最大限に引き出した超応用式を作れば、
ほぼQUERY関数だけで 画像のような グループ系や総合計を含む集計表を出力することが可能です。
ちなみに上の2つの画像の表は、どちらも LETやLAMBDAなど新関数を使っていません！
✅QUERY関数の代替案（ピボットテーブル）
とは言え、総合計やグループ系を出力したい場合は、基本的にはQUERY関数では無く
このようにピボットテーブルを利用した方が、圧倒的に簡単です。
ピボットテーブルは使い方次第では、QUERY関数以上に自由度が高く、便利な機能です。（ただし使いこなす為には関数の知識が必要です）
✅QUERY関数の代替案（Excelの GROUPBY関数、PIVOTBY関数）
無料で使えるなら Googleスプレッドシートにこだわらない！って人なら、無料のWeb版Excel（Excelオンライン）を使う方法もあります。
無料とはいえ Web版Excelは最新関数を利用できるので、今回のようなグループ計や総合計を含めた集計表の生成なら
PIVOTBY関数を使えば
かなり短い式で瞬殺できちゃいます。
Excelに慣れている人であれば、シート関数とは違う独特なクエリ文を記述するGoogleスプレッドシートのQUERY関数に比べ、引数は非常に多いですがシート関数の延長線上にある Excelの GROUPBY関数、PIVOTBY関数は 扱いやすいと感じると思います。
もちろん QUERY関数の利点もありますが、この比較は別のタイミングで書きたいと思います。
Q6. QUERY関数で総合計入りグループ集計表 と 縦横の合計入りピボット集計表を作成したい
それでは最後のお題、超応用例にチャレンジしてみましょう！
QUERY関数一発では生成は無理、ピボットテーブルを使った方がいいとは書きましたが、工夫すれば ほぼ QUERY関数だけで 👆のような
一番下に合計行を加えたグループ集計表
縦・横の合計行・合計列を表示したピボット集計表
は作成可能です。
この画像と同じ表2つの生成にチャレンジしてみましょう！（色や表示形式は気にせずでOK）
なお、グループ小計を入れた表は 今後登場する別の句が必要となる為、今回は対象外としています。
難しそうに見えますが、LETやLAMBDAなど新関数を使わずに実現できます！ まだ登場していない句を使う必要もありません。
これまでのQUERY関数の知識を総動員それば可能です！！
チャレンジしてみましょう。
↓↓
回答はここから。
↓↓
A6a. QUERY関数で総合計入りグループ集計表を作成する
まずは 左側 合計行入り グループ集計表を生成する式の回答です。
いきなり回答ではなく考え方を順を追って説明しましょう。
まず、出力したい表を実現可能なパーツに分解して考えます。👆の表であれば
この2つですね。
①は ちょうど今学んでる group by句を使ったグループ集計なので
①普通の group by による グループ集計 部分
=QUERY(販売実績[#ALL],"select Col2,Col3,sum(Col4)
group by Col2,Col3")
ここは問題ないですね。
②の方ですが
グループを全て足した合計 = 元データの4列目の合計
なので SUM関数を使ってもいいんですが、ここはQUERY関数で
=QUERY(販売実績[#ALL],"select sum(Col4)")
とグループ無し集計をします。
ただ、これだと ①の3列データとドッキングする際のサイズが揃ってないので合計の前に2つ列を追加します。
=QUERY(販売実績[#ALL],"select '合計',1/0,sum(Col4)")
1つは 合計 という文字列、もう一つのセルは空白としたいので
select '合計',1/0,sum(Col4)
と、select句の 値直接指定、そして QUERY関数のselect句で 空白を生成する 1/0エラー法を使います。
これらは select句の回でどちらも登場しましたね！
あとは見出し無しの2行目だけを使いたいので、ここはINDEX関数で取得しちゃいましょう。（QUERY関数でやる方法もあります。今後登場します。）
=INDEX(QUERY(販売実績[#ALL],"select '合計',1/0,sum(Col4)"),2)
👆手順をまとめると、こんな流れです。
合計行のパーツが用意出来ました。
あとは縦に連結するだけですが、結合面が一緒なので新関数のVSTACKを使うまでもなく、ここは { ; } 中カッコセミコロン でよいですね。
というわけで 合計行入りグループ集計の式の回答は
={
QUERY(販売実績[#ALL],"select Col2,Col3,sum(Col4)
group by Col2,Col3");
INDEX(QUERY(販売実績[#ALL],"select '合計',1/0,sum(Col4)"),2)
}コチラです。
A6b. QUERY関数で縦横合計入りピボット集計表を作成する
複雑そうに見えますが、実はこれも先ほどと一緒です。
パーツに分けて、それぞれのパーツをQUERY関数で生成して中カッコ連結という手順。
今回は4つのパーツに分割します。
①group by + pivot の縦横ピボット集計
=QUERY(販売実績[#ALL],"select Col2,sum(Col4)
group by Col2 pivot Col3")
これは基本のピボット集計なんで大丈夫ですね。
②group by で select 指定なしの縦グループ集計
②の右側に表示させたい集計列ですが、営業担当（Col2）毎の売上合計を縦に出力すれば良いので 普通に group by句で
=QUERY(販売実績[#ALL],"select Col2,sum(Col4) group by Col2")
として INDEXで必要な2列目だけを取得でもいいんですが、前回
group by句で指定した列は select句で指定する必要はありません。（エラーになりません）
ってのを学びましたね。
今回は 集計部分だけ使いたいので最初から Col2を selectで指定せず
=QUERY(販売実績[#ALL],"select sum(Col4) group by Col2")
とすれば INDEXも不要で非常にシンプルです！
③pivot 句による横グループ集計
③のパーツは 横方向に 商品毎の合計を集計すれば良いので pivot句ですね。
=QUERY(販売実績[#ALL],"select sum(Col4) pivot Col3")
と考えますが、①とドッキングする際に 一番左の見出し列部分が必要となるので、ここもselect 句で直接 '合計’という値を入れて調整します。
2行目だけ取得するのでここはINDEX関数で。
=INDEX(QUERY(販売実績[#ALL],"select '合計',sum(Col4) pivot Col3"),2)
④グループ化無しの Col4の合計
最後の④はぶっちゃけ この右下スミを埋める 4列目全体の売上の合計なんで
SUM(販売実績[売上金額])
でもシンプルでいいんですが、なんとなくここだけ違う範囲を取得するのが嫌なのと、せっかくここまで全てのパーツをQUERY関数で生成してきたんだし 揃えたい！って気持ちがあるんですよねｗ
というわけで QUERY関数で 合計を取得して INDEXした
=INDEX(QUERY(販売実績[#ALL],"select sum(Col4)"),2)
こちらの式で用意しました。
最後は戦隊モノの巨大ロボ合体のように 中カッコで 超電磁合体すれば
={
QUERY(販売実績[#ALL],"select Col2,sum(Col4) group by Col2 pivot Col3"),
QUERY(販売実績[#ALL],"select sum(Col4) group by Col2");
INDEX(QUERY(販売実績[#ALL],"select '合計',sum(Col4) pivot Col3"),2),
INDEX(QUERY(販売実績[#ALL],"select sum(Col4)"),2)
}縦横合計入りのピボット集計表の完成です！！
ちなみに中カッコ の連結は ,カンマによる 横が優先となるので、先に横を連結してから縦連結 する 👆ような書き方であれば 中カッコをネストする必要はありません。
この式はきちんとフレキシブルに参照するテーブルのデータが増えた時に拡張してくれます。
第4の営業担当 花園、第4の商品 Zが 登場すると、縦横の合計入り ピボット集計表が自動拡張しているのがわかりますね！
あまり美しくはないですが、指定するデータ範囲、グループ化、ピボットかする列、集計関数を 集約した汎用的な式にすると
=LET(
x,販売実績[#ALL],g,"Col2",p,"Col3",agg,"sum(Col4)",
q,LAMBDA(クエリ,r,c,INDEX(QUERY(x,クエリ),r,c)),
{ q(JOIN(" ","select",g,",",agg,"group by",g,"pivot",p),,),
q(JOIN(" ","select",g,",",agg,"group by",g),,2);
q(JOIN(" ","select","'合計'",",",agg,"pivot",p),2,),
q(JOIN(" ","select",agg),2,)
}
)こんな感じになります。
x,販売実績[#ALL],g,"Col2",p,"Col3",agg,"sum(Col4)",
この部分の各項目を指定するだけで、自由に集計行・集計列入りのQUERY関数によるピボット集計が出来ます！
group by句、pivot句 を極める！
QUERY関数の最重要句である group by、pivot をフル活用した超応用例お題 6連発 いかがだったでしょうか？
3回にわたって group by、pivot の 基本 、 他のサイトでは触れない特殊な仕様や使い方、そして超応用例を学びました。
お題を自力で解けた方、解説を読んで理解できた方は、QUERY関数による集計 、group by句、そしてpivot句 を極めてきている！と言ってよいでしょう。
次は並び替えを司る order by句に入りますが、また3週ほど他のネタを挟んでからQUERY関数シリーズの続きを書きたいと思います。
