Googleスプレッドシート QUERY関数超応用例 23（QUERY関数超応用テクニックまとめ）

mir

2025年12月21日 10:17

Googleスプレッドシートの最強集計関数 QUERY関数シリーズの第23回です。

かなり長いことやりましたが、一応先週の第3引数で QUERY関数の構文について全て解説したことになります。

というわけで今回で QUERY関数超応用例シリーズは一旦終了となります。

👇先週のnoteでは QUERY関数の第3引数「見出し」について書きました。

今回は先週の続き、QUERY関数の第3引数「見出し」テクニックを使った超応用例と、全体振り返りをお届けします。

👇これまでのQUERY関数シリーズは無料マガジンにまとめています。

集計するためには手段を選んでいられませんというQUERY関数で下剋上したい人は必読です。

QUERY関数第3引数（見出し）を使った超応用例にチャレンジ

QUERY関数の第3引数（見出し）の2つの応用技（曲芸）

応用技1. クエリスマッシュ
　QUERY関数の第3引数 9^9指定で、全てのデータを見出し扱いにすることで、スペース区切りの文字列連結（JOIN関数処理）が列毎に出来るぞ！

応用技2. 見出しDROP
　QUERY関数の第3引数 0指定に offset句を組み合わせることで、見出し部分もデータとして扱い、見出しを除去したデータ部分だけを取得するぞ！

これらを使った超応用例 3つにチャレンジしてみましょう！

この2つだけでなく、これまで学んだQUERY関数のテクニックをフル活用するような最終ダンジョンレベルのお題続出です！

Q1. カテゴリ別にアイテムをカンマ区切りで集約したい

画像左のように、アイテムとカテゴリという2列のデータを右のようにカテゴリ毎にアイテムをカンマ区切りで集約した表を作りたい。

D2セルにどんな式をいれればよいでしょうか？（範囲はA:Bで指定とする）
アイテムには重複は無いものとします。

LAMBDAヘルパー関数を使えば簡単なお題なんですが、今回もQUERY関数を軸に式を組むという条件でチャレンジしてみましょう！

データは👇コチラを利用ください。

アイテム	カテゴリ
りんご	フルーツ
オレンジ	フルーツ
カリフラワー	野菜
薔薇	花
チューリップ	花
ステーキ	肉
ニンジン	野菜
豚バラ	肉

考えてみましょう！

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

A1. カテゴリ別にアイテムをカンマ区切りで集約する

回答です。他の解法もあるので、後ほど紹介します。

=LET(
  x,QUERY(A:B,"select Col2,'_',min(Col1) group by Col2 pivot upper(Col1)",1),
  y,QUERY(x,"offset 2",0),
  z,TRANSPOSE(QUERY(TRANSPOSE(y),,9^9)),
  ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(z,"_"))," ",","))
)

式を見るとわかりますが、まず xを作って、xからyを作り、yからzを作り、最後zを仕上げるという 4段階で形態を変化させていく処理です。

解説していきましょう。

まず第1形態、 QUERY関数でピボット集計します。

QUERY(A:B,"select Col2,'_',min(Col1) group by Col2 pivot upper(Col1)",1)

この最初の式のポイントは5つ

select 句で後で区切り文字として利用する '_' を仕込む
where句による空白除去を省略。式を短く
集計関数は文字列に対してmin(max)を使う
pivot句の列指定に upper（lower）を組み合わせてエラーを回避
第3引数を1指定して見出しが1行あることを明示

1つ目はピボット集計とセットで

select句で値を指定した時の挙動

を使って後でカテゴリだけSPLIT関数で分割する為の区切り文字 '_' を2列目に仕込みます。（ポイント1）

さらに通常はピボット集計の段階で、where Col1 is not null で空白除去するのですが、無駄に発生する空白行、空白列は後の処理で簡単に消せるので、この段階では放置して where句を省略することで式を短くしています。（ポイント2）

※もちろんここで where句で空白除去してもOKです

そしてCol2（カテゴリ）を縦軸、Col1（アイテム）を横軸としてピボット集計するんですが、この時集計関数を count(Col1) で数値とするのではなく、

min(Col1) もしくは max(Col1) で アイテムの値をそのまま表示させます。（ポイント3）

しかし、普通に pivot Col1 としてしまうと 👆このよう

AGG_IN_SELECT_NO_PIVOT

というエラーが出てしまいます。

pivot句は集計対象の列と同じ列を指定できない

そこで、日本語のテキスト型に使えて値に影響を与えないスカラー関数 upper（または lower）を使って

pivot upper(Col1)

とします。（ポイント4）

最後に第3引数ですが、今回はこれを省略すると見出しもデータも全てテキスト型である為、正しく見出し判定できません。

👆このように見出し部分もデータとして集計されてしまうので、第3引数を 1と指定します。（ポイント5）

これを LET関数で xと置いて、第2形態へ進みます。

第2形態は、先週登場したQUERY関数第3引数テクニックの 「見出しDROP」を使います。

QUERY(x,"offset 2",0)

今回は1行ではなく、offset 2 として空白行を含めた不要な2行を除去します。

この結果を yと置いて、さらに第3形態へ。

TRANSPOSEで y を縦横変換してから、もう1つのQUERY関数第3引数テクニック「クエリスマッシュ」で列毎に連結。再度 TRANSPOSEで向きを戻します。

TRANSPOSE(QUERY(TRANSPOSE(y),,9^9))

これを zと置いて、最後の仕上げ、第4形態（完成形）へ進みます。

まず ARRAYFORMULA + SPLIT で1列目のカテゴリとアイテムを分割この時仕込んだ _ で分割するんですが、

クエリスマッシュの文字列結合は、空白セルを無視せずスペース結合している為、

各データの前後に余計なスペースがあったり、スペースが連続している箇所が出来ちゃってます。

これを綺麗にする為にTRIM関数を使うことは、前回学びましたね。

クエリスマッシュ + TRIM関数

最後に SUBSTITUTE関数で残った単語間のスペースを ,(カンマ）に置換

これで完成です。

A1（別解）. カテゴリ別にアイテムをカンマ区切りで集約する

別解です。

=LET(
  x,QUERY(A:B,"select Col1,min(Col1) where Col1 is not null 
    group by Col1 pivot Col2 label min(Col1) '_'",1),
  y,QUERY(TRANSPOSE(QUERY(x,,9^9)),"offset 1"),
  ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(y,"_"))," ",","))
)

ほぼ一緒なんですが、初手が違います。

=QUERY(A:B,"select Col1,min(Col1) where Col1 is not null
group by Col1 pivot Col2 label min(Col1) '_'",1)

ピボット集計の縦・横を逆にして、label句を使ってカテゴリの後ろに _ を仕込む式としています。

pivot 句と組み合わせて labelを使う

またこちらは後の処理の都合上、 where Col1 is not null で空白除去をしています。

これを xと置いて

QUERY(TRANSPOSE(QUERY(x,,9^9)),"offset 1")

クエリスマッシュ、TRANSPOSE、見出しDROPで成形したものを yとします。

この後の流れはほぼ一緒なんで割愛。

1形態分減りましたが、式の文字数的には最初の式の方が短いです。

これらのQUERY関数クエリスマッシュ、見出しDROPを使った式は、LET関数無しで書くことが出来るので、LET や LAMBDAの登場前は、圧倒的な存在感（しかも知る人ぞ知るテクニック）だったんですが、

今だったらカテゴリをUNIQUEにしてTOCOLで空白除去してから、MAPでカテゴリの要素1つ1つで FILTERしてJOINした方が圧倒的に簡単なんですよね。。

=LET(x,TOCOL(UNIQUE(B2:B),1),MAP(x,LAMBDA(v,{v,JOIN(",",FILTER(A:A,B:B=v))})))

でも古臭い レガシーテクニックと思わずに、QUERY関数マスターを目指すなら是非使えるようにしておきましょう！（役に立つかはわかりませんが）

次のお題も難易度高めです。

Q2. QUERY関数で指定した年月の日別売上レポートのテキストを生成したい

左のリスト表から、 F1セルの日付の年・月の売上データを集計した。

例えば F1セルが 2024/12/14 であれば、表から2024年12月のデータを日付別に売上金額を合計、さらに2024年12月の売上合計を

【日別売上レポート】
12/12 の売上は 940,000円です
12/17 の売上は 1,230,000円です
12/27 の売上は 1,600,000円です
2024年12月合計: 78,980,000円

このようなレポートとして出力したい。

※タイトル、日付別の売上を書いたテキスト、年月の合計を上記のように改行で連結した一つのテキストとする。
　日付は年不要で mm/dd形式、合計は yyyy年mm月 とする
　金額は3桁区切りで最後に円を付ける
　テキスト内にスペースが混じっていても特に気にする必要はない

どのような数式を組めばよいでしょうか？

データは 👇こちらを使って範囲指定は A:Dとしてください。

日付	営業担当	商品	売上金額
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

まずは関数に制限なしで解いてみましょう。F1の日付の年月が存在しなった場合のエラー処理は不要とします。

割と簡単に出来たよーという人は、LAMBDA不使用でQUERY関数をメインで使った解法にチャレンジしてみましょう！

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

A2. QUERY関数で指定した年月の日別売上レポートのテキストを生成する（QUERY関数不使用）

回答です。まずはQUERY関数を使わない解法から。

=LET(
  ym,TEXT(F1,"yyyy年mm月"),
  data,FILTER(A:D,TEXT(A:A,"yyyy年mm月")=ym),
  a,INDEX(data,,1),d,INDEX(data,,4),
  x,MAP(UNIQUE(a),
    LAMBDA(v,TEXT(v,"mm/dd")&"の売上は "&TEXT(SUMPRODUCT((a=v)*d),"#,##0円です"))
  ),
  y,ym&" 合計: "&TEXT(SUM(d),"#,##0円"),
  TEXTJOIN(CHAR(10),TRUE,"【日別売上レポート】",x,y)
)

こちらは今回は解説は割愛します。

A2. QUERY関数で指定した年月の日別売上レポートのテキストを生成する（QUERY関数をメイン使用）

=LET(
  ym,F1,
  x,QUERY(A:D,"select Col1,sum(Col4) 
    where Col1 contains "&TEXT(ym,"'yyyy-m-'")&
    " group by Col1 
    label Col1 '【日別売上レポート】',sum(Col4) '' 
    format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です'"
  ),
  y,TRANSPOSE(QUERY(x,"select sum(Col2) label sum(Col2) "&
    TEXT(ym,"'yyyy年mm月 合計:'")&
    " format sum(Col2) '#,##0円'"
    )),
  JOIN(CHAR(10),QUERY(TRANSPOSE({x;y}),,2))
)

こちらがQUERY関数で処理する式です。解説していきます！

まず F1セルを ym と置きます。

QUERY関数で ymの日付と同じ年月でフィルタした上で、日にち毎の売上合計をグループ集計します。

QUERY(A:D,"select Col1,sum(Col4) where Col1 contains "&TEXT(ym,"'yyyy-m-'")& " group by Col1 ")

where句で日付で絞り込む方法は幾つかあるんですが、ここは最もシンプルに記述できる

contains 'yyyy-m-'
または
starts with 'yyyy-m-'

を使うとよいでしょう。

以前QUERY関数で like を使って年月でフィルタする方法を紹介しましたが、

今回のような日付データと同じ年月でフィルタとする場合は、contains や starts with を使うのがおススメです。

ここのポイントは、

contains "&TEXT(ym,"'yyyy-m-'")

TEXT関数内に 'シングルクォートを含めてすっきり記述
月は mm ではなく m と1桁表記
m- と最後に -を付ける

この3点です。

まず、" 'yyyy-m-' " このようにTEXT関数内で前後の'シングルクォートを付けてしまうことで、記述をすっきりさせています。

セルの日付をwhere句の条件にする

そして2つ目が m と1桁表記にしている点です。

QUERY関数の日付データの中身（文字列としたもの）は、

yyyy-m-d というハイフン区切り、年、日は0埋めなしのデータです。

日付・日時・時刻を文字列化した時の中身

月を 2桁表記にしてしまうと正しく検索できず、1桁月の時に何も出力されません。

また3つ目のポイントお尻の - がない場合、F1セルが 1月だった時に 11月と12月のデータも拾ってしまいます。

つまり、高度な文字列比較演算子を使ってセルの日付の年月でデータを絞り込む場合は、

TEXT(日付の入ったセル,"'yyyy-m-'")

このように加工するべきってことです。

さらにこの第一段階でlabel句、format句を使って見出しと表示形式を整えつつ、日付と金額の間やお尻の文字も仕込んでしまいましょう。

=LET(
  ym,F1,
  QUERY(A:D,"select Col1,sum(Col4) 
    where Col1 contains "&TEXT(ym,"'yyyy-m-'")&
    " group by Col1 
    label Col1 '【日別売上レポート】',sum(Col4) '' 
    format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です'"
  )
)

label Col1 '【日別売上レポート】',sum(Col4) ''

label句で 1列目の見出しを後で使う見出し '【日別売上レポート】' に差し替え、合計列の方は '' 指定で見出しを空白にします。

format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です'

format句で日付と金額を成形、合わせて「の売上は」と「です」も表示形式で入れ込んでしまいます。

この結果 2列複数行の配列を LET関数で xと置きましょう。

残るパーツは最後の 合計行の箇所ですが、ここを作る式が

TRANSPOSE(QUERY(x,"select sum(Col2) label sum(Col2) "&
TEXT(ym,"'yyyy年mm月合計:'")&" format sum(Col2) '#,##0円'"))

この部分です。

見た目で混乱するかもしれませんが、赤枠の xのデータ部分は 1列目は日付型、2列目は数値型です。

だから

QUERY(x,"select sum(Col2)")

これで売上金額の合計を出力することが可能です。

先ほどと同じように label句、format句を使ってこの結果をゴールに近づけるように加工します。

QUERY(x,"select sum(Col2)
label sum(Col2) "&TEXT(ym,"'yyyy年mm月合計:'")&
" format sum(Col2) '#,##0円'")

label sum(Col2) "&TEXT(ym,"'yyyy年mm月合計:'")

" format sum(Col2) '#,##0円'"

これをTRANSPOSEで縦横変換し 2列1行のデータにしたものを yと置きます。

最後に仕上げです。

QUERY(TRANSPOSE({x;y}),,2)

同じ列数なので、中カッコで {x;y} として縦に連結、これをTRANSPOSEで向きを変えて

クエリスマッシュで列毎に連結。

ここは 2行のデータであるとわかっているので、第3引数は 2でOK。

この時点で format句で変えた表示形式が、その見た目のまま文字列として確定されます。

最後に JOIN関数で改行 CHAR(10) 区切りで連結すれば完成です。

JOIN(CHAR(10),QUERY(TRANSPOSE({x;y}),,2))

ここは再度TRANSPOSE関数で向きを変える必要はありませんし、特に空白無視を意識する必要のない1行データなので、TEXTJOINを使う必要もありません。

=LET(
  ym,F1,
  x,QUERY(A:D,"select Col1,sum(Col4) 
    where Col1 contains "&TEXT(ym,"'yyyy-mm'")&
    " group by Col1 
    label Col1 '【日別売上レポート】',sum(Col4) '' 
    format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です'"
  ),
  y,TRANSPOSE(QUERY(x,"select sum(Col2) label sum(Col2) "&
    TEXT(ym,"'yyyy年mm月 合計:'")&
    " format sum(Col2) '#,##0円'"
    )),
  JOIN(CHAR(10),QUERY(TRANSPOSE({x;y}),,2))
)

なかなかの難易度でしたね。理解出来たでしょうか！

【注意】QUERY関数を出力せずにMAPやBYROWなどで処理すると表示形式が解除される

行毎、列毎といった処理は、現在はLAMBDAヘルパー関数の BYROWやBYCOLを使うのが主流ではありますが、QUERY関数の format句を使って表示形式を設定したデータを扱う場合は注意が必要です。

クエリスマッシュというテクニックを知らなかった場合、上のように先ほどの式の xの段階から BYROW関数で行毎の JOIN関数で連結できないかなと考えるかもしれません。

しかし、

QUERY関数を出力せずにMAPやBYROWなどで処理すると表示形式が解除される

という仕様になっている為、これはうまくいきません。

上の画像は一度セルに出力した結果をBYROWで処理しているから、表示形式を保持したまま連結されていますが、式内で続けて BYROW関数を使ってしまうと

=LET(
  ym,F1,
  x,QUERY(A:D,"select Col1,sum(Col4) 
    where Col1 contains "&TEXT(ym,"'yyyy-m-'")&
    " group by Col1 
    label Col1 '【日別売上レポート】',sum(Col4) '' 
    format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です'"
  ),
  BYROW(x,LAMBDA(r,JOIN(,r)))
)

このように format句で設定した表示形式が全て解除されてしまいます。

これはJOIN関数の問題ではなく、MAPやBYROW、BYCOLといったLAMBDAヘルパー関数とQUERY関数のformat句の相性の問題と思われます。

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

A2:A10の見出し付の日付データを何も処理せずMAP関数で出力した時、QUERY関数で出力した時、MAP関数+QUERY関数で出力した時を比較した画像です。

① MAP関数で出力・・・日付の表示形式を保持
② QUERY関数で出力・・・日付の表示形式を保持
③ MAP+QUERY関数・・・表示形式が解除されシリアル値で出力

このような違いがあるのがわかりますね。

当然 QUERY関数内で format句で成形した場合も、

=QUERY(A2:A10,"format Col1 'yyyy年mm月dd日'")

=MAP(QUERY(A2:A10,"format Col1 'yyyy年mm月dd日'"),LAMBDA(v,v))

とMAP関数で、直接 QUERY関数を出力せずに読み込んでしまうと表示形式がリセットされシリアルナンバーで出力されてしまいます。

一方、一度QUERY関数を出力したセル範囲 C2:C10をMAP関数で参照して同じ処理をした場合は

=MAP(C2:C10,LAMBDA(v,v))

表示形式を保った形で出力されます。

つまり今回のケースを一度QUERY関数で集計してから BYROW関数で処理しようとすると

このように、QUERY関数では formatは使わずに、BYROW関数内で 1列目、2列目それぞれ TEXT関数で整える必要があるってことです。

QUERY関数と LAMBDAヘルパー関数の組み合わせは表示形式が解除されるのには注意しましょう。

というわけで、表示形式を維持したまま列毎に結合できるクエリスマッシュは、BYROWやBYCOLが登場した現在においても、優位性があるってことです。

QUERY関数の format句は唯一、表示形式で改行が扱える

さて、上のお題実は頑張れば、LET関数、TEXT関数、JOIN関数、CHAR関数を省略して、QUERY関数とTRANSPOSE関数だけで記述ができます。

その際ポイントとなるのが

QUERY関数の format句は表示形式で改行を扱える
format句で成形した表示形式はクエリスマッシュでテキスト化（確定）できる

この2点です。

Excelでは Ctrl+J を使って 表示形式に改行を設定出来るんですが、

Googleスプレッドシートではこのテクニックが使えません。

これは、検索と置換 の回で、検索した文字列を「改行」に置換するテクニックを紹介した際にも触れました。

Googleスプレッドシートでは出来ない改行への置換

その為、Googleスプレッドシートは表示形式で改行が使えないと認識されているんですが、これを突破する唯一の方法が QUERY関数のformat句です！

=QUERY(B2:B5,"format Col1 'yyyy"&CHAR(10)&"mm"&CHAR(10)&"dd'")

このように直接入力するセルではありませんが、QUERY関数の format句内で CHAR(10)を使うことで、中身は日付データですが、見た目上は年、月、日で改行された表示にすることが出来ます。

さらに式の可読性が下がるので注意が必要ですが、リテラル内に直接改行を記述 することで CHAR(10)を使わず、これを実現することも可能です。

=QUERY(B2:B5,"format Col1
'yyyy
mm
dd'")

この仕様を使って JOIN関数とCHAR関数を置き換え、そして format句の表示形式をクエリスマッシュで値化するテクニックで TEXT関数を代替して、さらに LET関数をカットすれば、

やたら長くで読みづらい式ではありますが

=QUERY(QUERY(
  {
  TRANSPOSE(QUERY(TRANSPOSE(QUERY(
  QUERY(A:D,"select Col1,sum(Col4) where Col1 starts with '"&
  QUERY(QUERY(F1,"format Col1 'yyyy-m-'"),,1)&
  "' group by Col1 format Col1 'mm/ddの売上は',sum(Col4) '#,##0円です
'"),"offset 1",0)),,9^9));
  TRANSPOSE(QUERY(
    QUERY(A:D,"select "&F1&",sum(Col4) where Col1 starts with '"&
    QUERY(QUERY(F1,"format Col1 'yyyy-m-'"),,1)&
    "' format "&F1&" 'yyyy年mm月',sum(Col4) '合計: #,##0円'"),
    "offset 1",0))
  },"label Col1 '【日別売上レポート】
'",0),,9^9)

11個のQUERY関数と 2個のTRANSPOSE関数だけで、このお題の処理が実現できます！

ま、参考程度に。

Q3. QUERY関数のピボット集計でグループ計、総合計を出力したい

QUERY関数シリーズ最後のお題です。

2024年の売上データである左の表を右のように営業担当ごと、商品ごとの売上金額を上期、下期でピボット集計したい。

さらに営業担当毎の計と全体の総合計を出力したい。

この時 F1セルにどのような式を入れればよいでしょうか？

なお 売上金額は 3桁区切りとします。

これが ARRAYFORMULA関数や LAMBDA関数、VSTACK関数、HSTACK関数などを使わず QUERY関数をベースに実現できます。

※LET関数は使った方が分かりやすいのでOKとします。

データはお題2と同じものを使用し範囲 A:Dとし指定します。

QUERY関数の集大成チャレンジしてみましょう！（自信の無い人も出来るところまでやってみましょう！！）

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

A3. QUERY関数のピボット集計でグループ計、総合計を出力する

回答です。

=LET(
  x,QUERY(A:D,"select Col2,Col3,sum(Col4) where Col1 is not null 
    group by Col2,Col3 pivot quarter(Col1)+quarter(toDate(Col1))%2"),
  y,TRANSPOSE(QUERY(TRANSPOSE(QUERY(
    QUERY(x,"select Col1,'計',1/0,sum(Col3),sum(Col4) group by Col1"),"offset 1",0)),,2)),
  z,QUERY(QUERY(y,"select '合計',1/0,sum(Col3),sum(Col4)"),"offset 1",0),
  QUERY({QUERY({x;y},"order by Col1");z},"label Col3 '上期',Col4 '下期' format Col3 '#,##0',Col4 '#,##0'")
)

QUERY関数を8回使う超絶式ですね。

解説していきます！

処理の流れとしては、

x ・・・担当毎、商品毎の売上合計
y ・・・担当毎の売上合計
z ・・・全体の合計

を用意して、最後に連結、並び替え、成形

となります。

このお題のベースは QUERY関数シリーズの 第16回で登場した超応用例のさらに応用です。

まず、元データを where句で is not null で空白行を除いた上で、

where句で空白データを除外する

営業担当 Col2、商品 Col3 をグループ化、さらに日付 Col1 を四半期にしたもので売上金額の合計をピボット集計します。

=LET(
  x,QUERY(A:D,"select Col2,Col3,sum(Col4) where Col1 is not null 
    group by Col2,Col3 pivot quarter(Col1)"),
  x
)

ここのポイントは grop by 句と pivot 句を使ったピボット集計

そして日付データを四半期の数値に変換する スカラー関数 quarter( ) です。

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

この四半期の数値（1～4）を上期、下期の2つに分類にする為に使うのが QUERY関数で使える隠れ算術演算子の %（剰余 mod）です。

$$
\begin{array}{lll}
\text{No}&\text{演算子}&\text{解説}\\ \hline
\text{1}&\text{+}&\text{加算（足し算）}\\ \hline
\text{2}&\text{-}&\text{減算（引き算）}\\ \hline
\text{3}&\text{*}&\text{乗算（掛け算）}\\ \hline
\text{4}&\text{/}&\text{除算（割り算）}\\ \hline
\text{5}&\text{\%}&\text{剰余（余り）　類似シート関数： MOD}\\ \hline
\end{array}
$$

しかし、ここを

pivot quarter(Col1)+quarter(Col1)%2

としてしまうと、 #N/Aエラーとなってしまいます。

エラーメッセージが出ないので原因がわかりづらいんですが、これは

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

という仕様の為です。

QUERY関数は select 句でも同じ列を2回選択できなかったり、知らないとハマるトラップが満載です。

QUERY関数で同じ列（文字列型）を複数回指定する

今回の場合は quarter(Col1) が2回登場しているのがエラーの原因です。

というわけで、このエラーを回避する為に日付データに使えるスカラー関数 toDate() を使って

日時や数値を日付に変換する toDate()

quarter(toDate(Col1)) とすることで、Col1の日付データに影響を与えずに quarter(Col1) とは別の値と認識させることでエラーを回避しています。

同じ数値や列指定によるエラーを回避する方法としては、

✅数値型の場合
Col1+0 や Col1-0 、Col1+0+0 など

✅テキスト型の列（全て大文字、全て小文字、または日本語）
upper(Col1) や lower(Col1)

✅日付型
toDate(Col1)

型別にこれらのテクニックがあります。

これを使ってピボット集計すると

=LET(
  x,QUERY(A:D,"select Col2,Col3,sum(Col4) where Col1 is not null 
    group by Col2,Col3 pivot quarter(Col1)+quarter(toDate(Col1))%2"),
  x
)

上期、下期の見出し部分が 2、4 となっていますが、ここは後で成形するのでこのままでOK。まずは第1段階の集計ができました。

この結果を LET関数で xと置きます。

次にこの xを使って 営業担当ごとのグループ集計を用意します。

QUERY(x,"select Col1,sum(Col3),sum(Col4) group by Col1")

ただ、これを後々の並び替えを考慮して、かつ列数を xと合わせる為に

QUERY(x,"select Col1,'計',1/0,sum(Col3),sum(Col4) group by Col1")

このように 2列目に '計' というテキスト、3列目に空白を挿入し

このような5列データを生成します。

1/0を使って空白列を生成するテクニックは第5回に登場しましたね。

select句で空白列を生成する

先頭の見出し行は不要なので

offset句の回と先週の見出しの回で登場した「見出しDROP」で見出し1行を除去します。

offsetの活用シーン（offsetで見出しを除いたデータ部分だけを出力する）

さらに、このデータの1列目、2列目を連結して xと同じ 4列データとする為に、まず TRANSPOSEで縦横変換してから

QUERY関数の第3引数を 2 と指定、 部分クエリスマッシュで 1、2行目を見出しとして列毎に結合します。

これをもう一度 TRANSPOSE関数で縦横を戻したデータ（xと同じ4列）を y と置きます。

TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY(x,"select Col1,'計',1/0,sum(Col3),sum(Col4) group by Col1"),"offset 1",0)),,2))

これが 営業担当毎のグループ計のパーツ yとなります。

さらに、この yを使って総合計の行を生成しましょう。

QUERY(y,"select '合計',1/0,sum(Col3),sum(Col4)")

2列目は Col2をそのまま使ってもいいんですが、その場合 group by Col2 の記述が必要になるので、1/0 指定で短くしています。

これも見出しが不要なので 見出しDROPしておきましょう。

QUERY(QUERY(y,"select '合計',1/0,sum(Col3),sum(Col4)"),"offset 1",0)

この総合計パートを zと置きます。

全てのパーツが準備できました。最後の仕上げです！

まず全部を連結ではなく、 {x;y}でピボット集計の結果 xと営業担当毎のグループ計の y だけを縦に連結します。

これを orde by句で昇順に並び替えをします。（ asc は省略可）

すると各営業担当の行の一番下に営業担当名に " 計" を連結したグループ集計のデータがきます。

yの手順で出てきた '計' のテキスト連結は、この並び替えで使う為の仕込みだったわけです。

この結果を一番下に表示させる総合計 zと再度中カッコで縦に連結

{QUERY({x;y},"order by Col1");z}

これを最後のQUERY関数で 見出しと表示形式を成形します。

QUERY({QUERY({x;y},"order by Col1");z},"label Col3 '上期',Col4 '下期' format Col3 '#,##0',Col4 '#,##0'")

見出しを成形する label句

label Col3 '上期',Col4 '下期'

そして表示形式を成形する format句

format Col3 '#,##0',Col4 '#,##0'

をそれぞれ記述。

これで QUERY関数を使ったグループ計、総合計入りのピボット集計が完成しました。

QUERY関数の集大成超応用例のお題、突破できましたでしょうか？

QUERY関数超応用例シリーズ 1～22まとめ

最後にここまでのシリーズを NotebookLMのインフォグラフィック機能で生成した要約画像で振り返りたいと思います。

1. QUERY関数全体感をまずは把握して使ってみる

https://note.com/mir4545/n/nafb3d0ed2ce2?magazine_key=mbc1301a1a9d3

2.第1引数データの挙動を理解

https://note.com/mir4545/n/n66df53031b5a?magazine_key=mbc1301a1a9d3

3. 第2引数クエリ文の概要を理解しよう

https://note.com/mir4545/n/n3c11f3e701b5?magazine_key=mbc1301a1a9d3

4. QUERY関数曲芸「文字列の計算式」計算する

https://note.com/mir4545/n/ndf386529b04d?magazine_key=mbc1301a1a9d3

5-6.QUERY関数 select句基本、応用

https://note.com/mir4545/n/n79e0f780d1ed?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n1c9ee24e2a7d?magazine_key=mbc1301a1a9d3

7-8.QUERY関数 where句をマスターする

https://note.com/mir4545/n/n7e7943715f50?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n8369b1de6b4c?magazine_key=mbc1301a1a9d3

9-10. where句の like mathes（正規表現）を理解する

https://note.com/mir4545/n/nd7ce76b12de6?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n268990e2a9ee?magazine_key=mbc1301a1a9d3

11-13. QUERY関数の日付、日時、時刻を完全攻略

https://note.com/mir4545/n/n2f76352d069f?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n341c43df9f74?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n58e974d137b1?magazine_key=mbc1301a1a9d3

14-16. group by と pivot で集計を極める

https://note.com/mir4545/n/n2022e2660bf8?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n96097d9ed36e?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n6f1b9a0a8c05?magazine_key=mbc1301a1a9d3

17. order by で並び替え（sortとは違うのだよ）

https://note.com/mir4545/n/nbb4f7d13c3ff?magazine_key=mbc1301a1a9d3

18. skipping、limit、offset で出力結果をコントロール

https://note.com/mir4545/n/nbc20deaf633a?magazine_key=mbc1301a1a9d3

19. 見出しを制御する label句

https://note.com/mir4545/n/nd0535b0ba974?magazine_key=mbc1301a1a9d3

20-21. 唯一無二のformat句

https://note.com/mir4545/n/n74804fb996ca?magazine_key=mbc1301a1a9d3
https://note.com/mir4545/n/n8f8bb0e3976c?magazine_key=mbc1301a1a9d3

22. 第3引数見出しを理解（クエリスマッシュ、見出しDROP）

https://note.com/mir4545/n/n7608918b0c2c?magazine_key=mbc1301a1a9d3

20万字以上（データや式を含む）の大長編シリーズとなりましたが、最後までお付き合いいただいた読者の皆様、ありがとうございました！！

QUERY関数超応用例シリーズ第一部完

冒頭に今回で QUERY関数シリーズは「一旦終了です」と書いた通り、全て出し切った、やり切ったというわけではありません。

IMPORTRANGE関数や複数シート、Googleフォームと組み合わせた実践応用例の紹介や Excelの GROUPBY関数、PIVOTBY関数との比較検証、その他小ネタなどなど、まだ書きたいネタが残ってます。

いずれ再開する予定なので、完結ではなく 第一部完と受け止めてください。

でも、しばらくはQUERY関数は食傷気味かもｗ

次回、2025年最後の更新は軽めの QuickTipsを予定。

といっても、QUERY関数の本編では触れられなかった関連ネタを早速取り上げようかなと・・・

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ

Googleスプレッドシート QUERY関数 超応用例 23（QUERY関数超応用テクニックまとめ）

QUERY関数 第3引数（見出し）を使った超応用例にチャレンジ

Q1. カテゴリ別に アイテムをカンマ区切りで集約したい

A1. カテゴリ別に アイテムをカンマ区切りで集約する

A1（別解）. カテゴリ別に アイテムをカンマ区切りで集約する

Q2. QUERY関数で 指定した年月の 日別売上レポートのテキストを生成したい

A2. QUERY関数で 指定した年月の 日別売上レポートのテキストを生成する（QUERY関数不使用）

A2. QUERY関数で 指定した年月の 日別売上レポートのテキストを生成する（QUERY関数をメイン使用）

【注意】QUERY関数を 出力せずにMAPやBYROWなど で処理すると表示形式が解除される

QUERY関数の format句は 唯一、表示形式で改行が扱える

Q3. QUERY関数のピボット集計でグループ計、総合計を出力したい

A3. QUERY関数のピボット集計でグループ計、総合計を出力する

QUERY関数 超応用例シリーズ 1～22まとめ

1. QUERY関数 全体感をまずは把握して使ってみる

2.第1引数 データの挙動を理解

3. 第2引数 クエリ文の概要を理解しよう

4. QUERY関数曲芸「文字列の計算式」計算する

5-6.QUERY関数 select句 基本、応用

7-8.QUERY関数 where句をマスターする

9-10. where句 の like mathes（正規表現）を理解する

11-13. QUERY関数の日付、日時、時刻を完全攻略

14-16. group by と pivot で集計を極める

17. order by で並び替え（sortとは違うのだよ）

18. skipping、limit、offset で出力結果をコントロール

19. 見出しを制御する label句

20-21. 唯一無二のformat句

22. 第3引数 見出しを理解（クエリスマッシュ、見出しDROP）

QUERY関数 超応用例シリーズ 第一部 完

いいなと思ったら応援しよう！

Googleスプレッドシート QUERY関数超応用例 23（QUERY関数超応用テクニックまとめ）

QUERY関数第3引数（見出し）を使った超応用例にチャレンジ

Q1. カテゴリ別にアイテムをカンマ区切りで集約したい

A1. カテゴリ別にアイテムをカンマ区切りで集約する

A1（別解）. カテゴリ別にアイテムをカンマ区切りで集約する

Q2. QUERY関数で指定した年月の日別売上レポートのテキストを生成したい

A2. QUERY関数で指定した年月の日別売上レポートのテキストを生成する（QUERY関数不使用）

A2. QUERY関数で指定した年月の日別売上レポートのテキストを生成する（QUERY関数をメイン使用）

【注意】QUERY関数を出力せずにMAPやBYROWなどで処理すると表示形式が解除される

QUERY関数の format句は唯一、表示形式で改行が扱える

QUERY関数超応用例シリーズ 1～22まとめ

1. QUERY関数全体感をまずは把握して使ってみる

2.第1引数データの挙動を理解

3. 第2引数クエリ文の概要を理解しよう

5-6.QUERY関数 select句基本、応用

9-10. where句の like mathes（正規表現）を理解する

22. 第3引数見出しを理解（クエリスマッシュ、見出しDROP）

QUERY関数超応用例シリーズ第一部完