Googleスプレッドシート QUERY関数 超応用例 21（formatでコレも出来る！）

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

前回の続きで format句を深堀していきます。今回はQUERY関数のformat曲芸というか、本来のQUERY関数の使い方ではないけど「え、こんなことができるの！？」という超応用例も登場します。

Excelの条件付き書式では、条件に応じて「表示形式」を分岐できます。しかし、Googleスプレッドシートではこれが出来ない。じゃあGASしかないのか？

これがQUERY関数 format句で解決できるかもしれません！！

👇先週のnote。今回はコチラの続きです。

👇これまでのQUERY関数シリーズは 無料マガジンにまとめています。QUERY関数における天上天下唯我独尊（ザ・ワン）を目指したい人は必読です！

format句でこれも出来る！？

超応用例の前に、前回触れていなかった format句ならこれも出来るというネタと特殊文字のエスケープに触れておきましょう。

QUERY関数の format句は * による セル埋めが出来る

Excelにもある特殊な表示形式なので、詳しい人はご存じかと思いますが、表示形式のカスタム数式では

このように記述すると * の直後の1文字をひたすら繰り返して、セル幅いっぱいに埋めることが出来ます。

これがQUERY関数でも使えます！

画像は

と式を入れることで、まず 0.00 で1列目の数値を小数点2桁表示とした上で、その前（左側）を '#' をセル幅いっぱいに繰り返して埋めています。

'0.00*#' と *#を後ろにつければ、👆このように小数点2桁表示の数値の後ろに 見た目上スペースを空けて # をひたすら繰り返し、数値を左詰めのように表示します。

列の幅を変えると連動して #（繰り返し文字）の数が増減します。面白いですね。

ただし 中身の #は実は1個で、列の幅に応じて #の繰り返し数が変動するのは、あくまでも表示（見た目上）だけです。

たとえば 

このようにした場合は、

このように判定され セルいっぱい0埋めされます。

ちなみに '*0' だけだと

このように参照している数字を無視してひたすら 0でセルを埋めるだけの表示になっちゃいます。

そして、この特殊仕様は TEXT関数では使用できません。

これが出来る関数は QUERY関数（format句）だけです。

中間で使うと👇こんなこともｗ

この辺りの特殊な表示形式の解説は、以前noteでも触れています。

特殊なカスタム数値形式の場合

面白い仕様ですが、実務で活用できるかは微妙。。

format句のメタ文字エスケープは　\（バックスラッシュ）

format句内で、表示形式を指定する際のメタ文字（特殊文字）のエスケープについて書いておきましょう。

たとえば 👆 のように 数値の後ろに 単位 m（メートル）をつけて表示したいなと

'0m'

とすると、0の後ろに謎の数値が表示されてしまいうまくいきません。

これは m が 表示形式においては日付の 月（month)を表すメタ文字である為です。

※ : と組み合わせた時は、同じmでも時間（minutes)という扱いになります

それ以外にも y（年）や、d（日）、0、*、( )など全てメタ文字となります。

では今回のように m をそのまま文字として使いたい場合はどうすればよいか？

一応 TEXT関数だと

こんな感じで

"0""m"""　

として対処する方法もあります。

QUERY関数でも

'0""m""'

このようにシングルクォート内の 文字列リテラルで ""ダブルクォート2回で括ることで出来るんですが、ちょっとわかりにくいですよね。

QUERY関数でもTEXT関数でも使える メタ文字のエスケープ表記 \（バックスラッシュ）を覚えておきましょう。

メタ文字の頭に \ を付けることで直後の文字をそのまま扱います。メタ文字以外に付けてもエラーになることはありません。

ちなみに Excelだと エスケープは \ バックススラッシュではなく、! エクスクラメーションマークなので注意が必要です。

それでは応用例に入っていきましょう！

format句で [条件]表示形式 が使えないケースの対応方法

前回のnoteでは、 format句で 以下のように記述することで、

対象は数値に限定されますが、 条件によって表示形式を分岐できることを学びました。

しかし、この方法だと指定できる条件は2つまでですし、そもそも 表示形式を設定する数値以外を条件とすることが出来ません。

では、3つ以上の条件を設定したい場合や、表示形式を設定する数値以外を条件としたい場合はどうすれば良いでしょうか？

お題形式で理解を深めてみましょう。

Q1. 毎日の売上データから曜日別の売上合計を集計して、一番売上が多い曜日を可視化したい（一番売り上げが大きい曜日に 👑をつけて表示させたい）

前回のお題3の👑ありは、売上が一番大きい曜日の重複はないものとして

=LET(
  x,QUERY(A:C,"select dayOfWeek(Col1),sum(Col3) 
  where Col1 is not null group by dayOfWeek(Col1)"),
  y,QUERY(x,"select Col1 order by Col2 desc limit 1",0),
  QUERY(x,"order by (Col1+5)%7 label Col1 '曜日',Col2 '売上' 
    format Col1 '[="&y&"]👑dddd;dddd',Col2 '#,##0'")
)

このような式で対応しました。

では、売上が一番大きい曜日が2つ以上（3つや4つあるケースも含む）の場合は、全ての一番売上が大きい曜日に(表示だけ）👑をつけたい場合、どのような式を組めばよいでしょうか？

今回はQUERY関数以外の関数もフル活用してOKです。

データは 水曜日と金曜日が最大売上曜日となるように調整した

日付	店舗	売上額	利益額
2025/10/01	A	787298	160000
2025/10/01	B	321456	80000
2025/10/01	C	987654	250000
2025/10/02	A	456789	110000
2025/10/02	B	123456	30000
2025/10/02	C	876543	200000
2025/10/03	A	654321	150000
2025/10/03	B	234567	70000
2025/10/03	C	998877	280000
2025/10/04	A	789012	190000
2025/10/04	B	345678	90000
2025/10/04	C	567890	140000
2025/10/05	A	112233	25000
2025/10/05	B	887766	210000
2025/10/05	C	445566	100000
2025/10/06	A	901234	220000
2025/10/06	B	567890	130000
2025/10/06	C	234567	60000
2025/10/07	A	765432	180000
2025/10/07	B	345678	85000
2025/10/07	C	890123	260000
2025/10/08	A	456789	100000
2025/10/08	B	100000	20000
2025/10/08	C	999999	290000
2025/10/09	A	678901	170000
2025/10/09	B	210987	50000
2025/10/09	C	765432	190000
2025/10/10	A	321098	75000
2025/10/10	B	876543	240000
2025/10/10	C	543210	120000
2025/10/11	A	987654	270000
2025/10/11	B	654321	150000
2025/10/11	C	123456	35000
2025/10/12	A	789012	180000
2025/10/12	B	345678	80000
2025/10/12	C	876543	200000
2025/10/13	A	456789	110000
2025/10/13	B	100000	25000
2025/10/13	C	999999	280000
2025/10/14	A	678901	160000
2025/10/14	B	210987	55000
2025/10/14	C	765432	170000
2025/10/15	A	321098	70000
2025/10/15	B	876543	230000
2025/10/15	C	543210	130000
2025/10/16	A	987654	260000
2025/10/16	B	654321	140000
2025/10/16	C	123456	30000
2025/10/17	A	789012	170000
2025/10/17	B	345678	90000
2025/10/17	C	876543	210000
2025/10/18	A	456789	120000
2025/10/18	B	100000	28000
2025/10/18	C	999999	270000
2025/10/19	A	678901	180000
2025/10/19	B	210987	60000
2025/10/19	C	765432	160000
2025/10/20	A	321098	80000
2025/10/20	B	876543	250000
2025/10/20	C	543210	140000
2025/10/21	A	987654	280000
2025/10/21	B	654321	160000
2025/10/21	C	123456	33000
2025/10/22	A	789012	190000
2025/10/22	B	345678	85000
2025/10/22	C	876543	220000
2025/10/23	A	456789	130000
2025/10/23	B	100000	27000
2025/10/23	C	999999	260000
2025/10/24	A	678901	170000
2025/10/24	B	210987	58000
2025/10/24	C	765432	180000
2025/10/25	A	321098	72000
2025/10/25	B	876543	240000
2025/10/25	C	543210	135000
2025/10/26	A	987654	290000
2025/10/26	B	654321	155000
2025/10/26	C	123456	31000
2025/10/27	A	789012	185000
2025/10/27	B	345678	95000
2025/10/27	C	876543	230000
2025/10/28	A	456789	125000
2025/10/28	B	100000	29000
2025/10/28	C	999999	250000
2025/10/29	A	678901	165000
2025/10/29	B	210987	62000
2025/10/29	C	765432	175000
2025/10/30	A	321098	78000
2025/10/30	B	876543	260000
2025/10/30	C	543210	145000
2025/10/31	A	987654	275000
2025/10/31	B	654321	165000
2025/10/31	C	123456	34000

こちらを使うものとします。

前回同様に、月曜を1番上とする、金額は 3桁カンマ区切りを条件とします。

考えてみましょう！

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

A1. 毎日の売上データから曜日別の売上合計を集計して、一番売上が多い曜日を可視化する（一番売り上げが大きい曜日に 👑をつけて表示させる）

回答です。

=LET(
  x,QUERY(A:C,"select dayOfWeek(Col1),sum(Col3) where Col1 is not null 
  group by dayOfWeek(Col1) order by (dayOfWeek(Col1)+5)%7 
  label dayOfWeek(Col1) '曜日',sum(Col3) '売上'"),
  BYROW(x,LAMBDA(r,
    QUERY(r,"format Col1 '"&IF(INDEX(r,,2)=MAX(INDEX(x,,2)),"👑",)&"dddd',Col2 '#,##0'")
  ))
)

解説していきましょう。

まず 

=LET(
  x,QUERY(A:C,"select dayOfWeek(Col1),sum(Col3) where Col1 is not null 
  group by dayOfWeek(Col1) order by (dayOfWeek(Col1)+5)%7"),x
)

とした場合、曜日別の売上の最大値（金額）は、

MAX(INDEX(x,,2))

2列目の最大値ってことなんで、これで取得できます。

これを使った条件分岐なので、IF関数を使って売上額が 最大値と一致したときは 👑をつけるといった方法が思いつくかと思います。

ただ、👑付パターンのQUERY関数と👑なしパターンのQUERY関数を用意してIF関数で分岐しても

このように表示形式はリセットされてしまいます。

これは前に label句の時に少し触れましたが、ARRAYFORMULAと組み合わせてIF関数を使った場合は、表示形式がリセットされてしまう仕様だからです。

IFNAやIFERROR、配列に対して IF関数を使うと 表示形式（日付表示）が解除されシリアル値に戻る

では、どうすればよいか？

ここは LAMBDAヘルパー関数の出番となります。

まず x（1回目のQUERY関数）の段階で label句で見出しの成形までは完了しておきます。

ここから行単位で処理が出来る BYROW関数を使います。

  BYROW(x,LAMBDA(r,
    QUERY(r,"format Col1 '"&IF(INDEX(r,,2)=MAX(INDEX(x,,2)),"👑",)&"dddd',Col2 '#,##0'")
  ))

この式のポイントは

QUERY(r,"format Col1 '"&IF(INDEX(r,,2)=MAX(INDEX(x,,2)),"👑",)&"dddd',Col2 '#,##0'")

ここです。

1行毎にQUERY関数を使うことで、クエリ内でIF関数を使って

週ごとの売上金額合計 INDEX(r,,2) が、最大値 MAX(INDEX(x,,2)) であれば 👑つき、falseなら なしと、1行毎に表示形式を適用しています。

1回目のQUERY関数の段階で先に label句で見出しを成形したのは、行毎にQUERY関数を回した場合は、見出し行もデータ扱いとなる為、 label句が使えないからです。

• QUERY関数で見出し行は必ず文字列となる

• 文字列は format句の影響を受けない

という仕様を利用しています。

式は短くなりましたが、なかなか難しいですよね。

とりあえず、3つ以上の条件や 表示形式を適用する数値以外を条件とした format句による 表示形式の分岐は

BYROW関数 ＋ QUERY関数　（行単位でQUERY関数）
MAP関数 ＋ QUERY関数　（要素単位でQUERY関数）

このような組み合わせで実現できる！ってことです。

超応用例 QUERY関数format曲芸の世界

ここからは本来のQUERY関数の使い方とは違う、format句で表示形式を操作する為だけのQUERY関数 超応用例（というかQUERY曲芸）を見ていきましょう。

実務に使えるケースもあるかと思います。

Q2. 21日スタート、翌20日までの1ヶ月カレンダーで 21日と1日だけ m月d日表示にしたい

21日スタート、20日締めで、終了日の月で 〇月度とする会社があったとします。（11/21～12/20 は12月度）

B1セルで年、B2セルで月度を指定した時に自動でA4:B に見出し付で 日にち、曜日を下に展開する式を組みたい場合、どのような数式を作ればよいでしょうか？

ただし、

✅式はA4に1つだけ入れる
✅21日と1日だけ 月も入れた m月d日とし、それ以外は d日と表示したい
✅上記は中身を文字列として変えるのではなく、表示形式だけを変える
✅手動で表示形式をいちいち設定しないものとする

これらを条件とします。

これが厄介なのは1日、つまり表示形式を変えるセルの位置が変動するって点です。

このように

10月度　10月1日は 15行目
11月度　11月1日は 16行目
12月度　12月1日は 15行目

と m月d日 表示としたいセル位置が変動します。

これは Excelだったら、条件付き書式で 表示形式を適用させて対応するかと思います。

条件付き書式で表示形式を制御できないGoogleスプレッドシートだと、スプレッドシートに自信のある人でもほとんどが 出来ない、またはGASを使うしかないと回答するケースでしょう。

しかし先週からの流れでQUERY関数の format句を学んできた皆さんなら、「このケースはQUERY関数でいけるんじゃないか？」と、なんとなくイメージ出来てますよね！

これは 数式だけで出来ます！

考えてみましょう！！

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

A2. 21日スタート、翌20日までの1ヶ月カレンダーで 21日と1日だけ m月d日表示にする

回答です。

これはMAPやBYROWは不要で、QUERY関数一発で出来ます。

=LET(
  sd,DATE(B1,B2-1,21),
  eom,DATE(B1,B2,0),
  QUERY(
    SEQUENCE(DAY(eom),1,sd),
    "select Col1,Col1+0 
    label Col1 '日付',Col1+0 '曜日' 
    format Col1 '[="&sd&"]m月d日;[="&eom+1&"]m月d日;d日',Col1+0 'ddd'"
  )
)

解説していきましょう。

まずB1とB2で指定した年、月度の  21日から20日までの日付連番をSEQUENCE関数で生成します。

この連番が幾つまで必要か？（30なのか？31なのか？または28なのか？）は、開始月の月末が何日か？で決まります。

というわけで、LET関数を使ってまず

開始日　 sd,DATE(B1,B2-1,21)
開始月の月末　eom,DATE(B1,B2,0)　※ EOMONTH(sd,0) でもよい

とします。

B2セルの月度は 終わりの方の月なので、開始日の月は B2-1 ですね。

DATE関数は、DATE(年, 月, 日) とそれぞれ数値で指定するんですが、たとえば 月や日 が 0だったり、マイナスだったり 月が12を超える 13や14だったりしても、自動で年や月を調整して 正しい 年、月、日の日付を返してくれます。

開始日と開始日の月の月末

翌月の21日から20日までの日付の数は、開始月の月末の日の数字なので  DAY(eom) で取得できます。

これらを使って日付連番を SEQUENCE関数で生成する式が

SEQUENCE(DAY(eom),1,sd)

これです。

指定した年、月度の21日～翌20日までの1ヶ月間の日付が出力出来ました。これがQUERY関数の第1引数になります。

今回は日付だけでなく 隣に曜日も出力するので、select句で もう1回これを選びたいところですが、

QUERY関数の select句では、同じ列を2回選択出来ません。

この生成された連番は QUERY関数から見て 日付型ではなく、シリアル値（数値型）になっているので、2つ目の Col1を Col1+0 とすることで回避しましょう。

QUERY関数で同じ列（数値型）を複数回 指定したい

とりあえず見出し部分 label と曜日部分の format は、今まで学んだクエリ文を使って成形できますね。一旦日付も全て d日 表示にしておきましょう。

=LET(sd,DATE(B1,B2-1,21),eom,DATE(B1,B2,0),QUERY(SEQUENCE(DAY(eom),1,sd),
"select Col1,Col1+0 label Col1 '日付',Col1+0 '曜日' format Col1 'd日', Col1+0 'ddd'"))

まずは、ここまで出来ればOKです。

あとは21日と1日だけ 表示を m月d日とすればよいので、 [条件]表示形式を使います。

21日は開始日 なんで変数 sd ですね。

1日の方は 前日が 前の月の末（eom）なので、 eom + 1 と出来ます。

これらと一致した時だけ m月d日として、それ以外は d日としたいので

このようになります。

=LET(
  sd,DATE(B1,B2-1,21),
  eom,DATE(B1,B2,0),
  QUERY(
    SEQUENCE(DAY(eom),1,sd),
    "select Col1,Col1+0 
    label Col1 '日付',Col1+0 '曜日' 
    format Col1 '[="&sd&"]m月d日;[="&eom+1&"]m月d日;d日',Col1+0 'ddd'"
  )
)

LAMBDA系もARRAYFORMULAも使わずに実現できました。

Googleスプレッドシートでは出来ないと思われていた、変数を条件とする表示形式の分岐が QUERY関数を使えば対応できる！ということです。

※直入力ではなく数式による出力かつ数値（日付含む）に限ります

いきなりコレが登場したらお手上げでしょうが、今までの流れを踏まえて自力で解けた人もいたんじゃないでしょうか。

Q3. 割り切れる場合は整数をそれ以外（小数）の場合は、最大で小数点第2位までを表示したい

もう1問挑戦してみましょう。

このようにA列（割られる数）、B列（割る数）の割り算の計算結果をC列に出力しています。

表示形式を自動とした場合は、割り切れる場合 や小数点第1位の時はいいのですが、割り切れない数は 33333…のようになってしまいます。

これを

整数の時は 整数
小数点第1位で割り切れる場合は 小数点第1位まで
小数点第2位以上となる場合は、四捨五入で 小数点第2位まで

表示したい場合、どのようにすればよいでしょうか？というお題です。

つまり、中身はC列のまま表示だけを D列のようにしたいって要望です。

もちろん個々のセルに違う表示形式を設定しているわけではありません。

A列、B列の数値が変わって計算結果が変われば 表示形式も連動します。

ちなみに セルの表示形式で対応しようとすると

小数点第2位までの表示 0.00 とした場合は

2　→　2.00
0.4　→　0.40

と割り切れる数も小数点第2位まで表示されてしまいます。 

これを今まで学んだQUERY関数 format句のテクニックを使って実現しようというお題です。

まずは1セルだけでＯＫです。それが出来た方はスピル式を考えてみましょう！

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

A3. 割り切れる場合は整数をそれ以外（小数）の場合は、最大で小数点第2位までを表示する

回答です。まずは1セル単位の式を

今回のようなケースは 対象の数値そのものを 比較演算子（イコールや不等号）で評価して 表示形式を分岐させることが出来ないので、

[条件1]表示形式1;[条件2]表示形式2;その他の時の表示形式

この記述が使えません。ここは愚直にIFで表示形式を分岐させます。

IF(INT(A2/B2)=A2/B2,"0","0.##")

A2/B2 の計算結果を INTで小数点以下を切り落とした（整数にした）ものと A2/B2そのものが一致すれば 整数なので '0' という表示形式で、一致しない場合は 小数点以下が発生するので '0.##' で 最大第2位までの表示でとします。

実はカスタム数値形式で 0.## とすれば、#は存在する時だけ数字を表示するメタ文字なんで、いい線までいくんです。

小数点1位までの時は 0.4となり、小数点2位以上の時は 2.3333 → 2.33となります。

しかし どうしても .（ドット）は消えない為、整数の時に

2　→　2.

と余計な . が残ってしまいます。

つまり整数の時だけ、IF文で QUERY関数の format句で表示形式 0に分岐させれば解決ってわけです。

A3. （スピル式）割り切れる場合は整数をそれ以外（小数）の場合は、最大で小数点第2位までを表示する

これを 1つの数式（スピル式）とした回答が

=MAP(A2:A5,B2:B5,LAMBDA(a,b,
  QUERY(a/b,"format Col1 '"&IF(INT(a/b)=a/b,"0","0.##")&"'")
))

こちらです。 ARRAYFORMULAをつけて

=ARRAYFORMULA(MAP(A2:A5/B2:B5,LAMBDA(v,
  QUERY(v,"format Col1 '"&IF(INT(v)=v,"0","0.##")&"'")
)))

こうしてもOK。

Q1でも解説しましたが、ARRAYFORMUAL+ IFで処理しようとすると、QUERYでせっかく設定した表示形式がリセットされてしまいます。

QUERY関数のformat句で設定した表示形式を保つために 1セルに適用した式をそのまま活かす形で、MAPを使って 1つずつQUERY関数を適用するという式を組む必要があります。

MAPは同じサイズの配列を複数引数とすることが出来るのが魅力です。

format曲芸の世界、いかがだったでしょうか？

他にもネタあるんですが、それは別の時に書きたいと思います。

超応用例 QUERY関数 format句 表示形式を極める

最後に高難度の超応用例にチャレンジしてみましょう。表示形式を意識して式を作る問題です。

Q4. 返却月ごとに未返却の商品が幾つあるか集計したい

A:C列に 貸出日、商品、返却 という商品貸し出しの管理データがあります。

貸出日の12日後が返却期限となりますが、返却列が済となっていない商品は、2025年12月時点で、まだ返却されていない商品です。

また貸出日が空欄の行があり、これは貸出日が記載漏れで不明となっています。この場合返却が済でなければ、 2025年12月末を返却期限とすることにします。

商品の列に漏れはありません。返却日は全て2025年になっているとします。

返却期限の月ごとに 未返却の商品が幾つあるかを集計した右のような表を作りたい場合、どのような式を組めばよいでしょうか？

もちろん 式を入れるのは E1セルのみで、QUERY関数をメインで使って集計するものとします。

データは ↓を 利用ください。（テーブル化は無しとします）

貸出日	商品	返却
2025/01/06	C	済
2025/01/12	A	
2025/01/20	H	済
2025/01/28	F	済
2025/02/03	B	済
2025/02/14	I	済
	E	
2025/02/25	A	済
2025/03/05	J	済
2025/03/11	D	
	G	済
2025/03/27	C	
2025/04/02	H	済
2025/04/10	B	済
2025/04/15	F	
2025/04/24	E	済
2025/05/06	I	済
2025/05/13	A	済
2025/05/22	D	済
	J	済
2025/06/04	G	
2025/06/11	C	済
2025/06/19	E	済
2025/06/26	H	
	B	
	F	済
2025/07/22	A	
2025/07/30	I	済
2025/08/05	D	済
2025/08/12	J	済
	G	済
2025/08/26	C	済
2025/09/02	E	
2025/09/10	H	済

可能であれば、LET関数で A:Cを dataとして 他はセル参照をせず式を組んでみましょう。

考えてみましょう！

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

A4. 返却月ごとに未返却の商品が幾つあるか集計する

順に解説をして最後に回答の式とします。

このお題のポイントは2点です

1. A列の 貸出日の日付を 空白は 2025/12/31に 日付が入っているセルは 12日後の日付にするのをどう取得するか？

2. シリアル値化した日付データをQUERY関数でどう対処するか？

まず1つ目 12日後の取得ですが、やや乱暴ですが

こうする方法もあります。

ただ、これだと空白セルにすべて2025/12/31（のシリアル値）が入ってしまうので、QUERY関数をかけた際に本来、テキスト型の列だった 2列目、3列目が

シートの最終行まで埋まった 46022 という数値型に乗っ取られて、テキストがマイノリティで除外（空）となってしまいます。

これではQUERY関数で集計が出来ません。

IFをネストしたり、IFS関数で細かく条件を分岐してもいいんですが、ちょっと面倒ですよね。

というわけで、初手で日付の空白を埋めたり 日付を +12で返却日変換するのではなく、先にQUERY関数で 必要なデータだけに絞り込むと考えてみましょう。

この時点では まだ日付は返却日になっていないので、集計は我慢です。

このように商品（2列目）が空ではなく返却（3列目）が空の貸出日（1列目）だけを QUERY関数で抽出した結果を xと置けば

IFERROR(IF(x="",--"2025/12/31",x+12),x)

このように、だいぶすっきりと 12日後の返却日変換と 空白の12/31化ができます。

ただ この結果をQUERY関数で月ごとにグループ集計しようとすると

このように計算結果がシリアル値（数値型）である為、日付型に対して使えるmonth() が使えずエラーとなってしまいます。

これがQUERY関数の厄介な点です。QUERY関数内で日付型として扱う為には日付と判定できる表示形式となっている必要があります。

シリアル値のままでは、QUERY関数では日付として扱うことが出来ません。

というわけで発想を変えて

QUERY関数で出力した対象データを 直接month関数で月の数値化 & 空白を 12として、QUERY関数の外で月に変換しちゃいましょう。

ついでに IFERRORを外したんで 見出しが  #VALUE! になってますが、どうせ最後にQUERY関数で成形するので問題なし。このままこれを第1引数としてQUERY関数で突っ走ります。

QUERY関数で、グループ集計、ラベル加工、表示形式加工をして完成です。

こちらが回答

=ARRAYFORMULA(LET(
  data,A:C,x,QUERY(data,"select Col1 where Col2 is not null and Col3 is null"),
  QUERY(IF(x="",12,MONTH(x+12)),"select Col1,count(Col1) group by Col1 
  label Col1 '返却予定月',count(Col1) '未返却商品数' format Col1 '0月'")))

たぶんかなり短い方かと思いますが、シリアル値化した日付データの対応でQUERY関数から逃げてしまった感があり、若干モヤっとしますね。

A4.【別解】 返却月ごとに未返却の商品が幾つあるか集計する

QUERY関数でシリアル値を日付として扱えない問題を解決する回答が 👇こちらです。

=ARRAYFORMULA(LET(data,A:C,
x,QUERY(TO_PURE_NUMBER(data),"select Col1+12,Col2 where Col2 is not null and Col3 is null
 format Col1+12 'yyyy/mm/dd'",1),
 y,QUERY(x,"select month(Col1)+1,count(Col2) group by month(Col1)+1"),
 QUERY(IF(y="",12,y),"order by Col1 label Col1 '返却予定月',Col2 '未返却商品数' format Col1 '0月'")
))

式は長くなっていますが、QUERY関数の特性を活かした式になっています。

こちらのポイントは3つ。

1. あえて日付をシリアル値（数値）としてQUERY関数で扱うことで、空白や見出しを除いた加算ができる

2. シリアル値（数値型）を format句で 日付型に戻せば、その結果をQUERY関数で日付として扱える

3. 空白は集計後、最後に差し替えて成形

です。

まず貸出日の列を数値としてQUERY関数で扱う為に、TO_PURE_NUMBER関数で 

という処理をします。

これを他の関数で処理すると 空白が0になったり、テキストがエラーになってしまいます。

TO_PURE_NUMBERはExcelにはない、Googleスプレッドシート 独自の関数で、型を意識する必要があるQUERY関数や表示形式の影響を受ける関数と組み合わせることで力を発揮します。

これで1列目は日付型ではなく数値型となったので、QUERY関数で 1列目を +12すると

QUERY関数の算術演算子は数値型に対してしか機能しない（かつ見出しはそのまま）となる為、見出しや空白セルに影響を与えず 日付のシリアル値だけが +12されます。

さらに、ここでwhere句で 商品（2列目）が空ではなく返却（3列目）が空の 1，2列目に絞り込んでおきます。※今回は2列目も残す理由は後で登場します

加えて 次の段階ですぐに日付として月でグループ集計できるように、シリアル値を日付表示にもどしておきましょう。

ここもポイントです。

=ARRAYFORMULA(LET(data,A:C,QUERY(TO_PURE_NUMBER(data),
"select Col1+12,Col2 where Col2 is not null and Col3 is null format Col1+12 'yyyy/mm/dd'")))

これを xと置いて、再度QUERY関数でグループ集計をかけます。

QUERY関数で空白を count すると 0となってしまう為、count(Col2)としています。 ここで使う為に2列目を残しました。

QUERY(x,"select month(Col1)+1,count(Col2) group by month(Col1)+1")

これを yと置いて、IF関数で空白セルを12に変換、QUERY関数で昇順並び替え、見出し、表示形式を整えて完成です。

内部処理で 👇こんなことをやってます。

型を切り替えてQUERY関数で処理するテクニックを紹介しました。

日付データも関数で処理してると内部でシリアル値になってしまうことが多々あります。

長く複雑な式を書いていている時に、なぜかQUERY関数で日付としての処理（年、月のグループ集計、ピボット集計）が出来ない原因はこのシリアル値化であることが多いです。

そんな時は一度 QUERY関数で format Col1 'yyyy/mm/dd' で日付表示（出力はしない）としたものを 再度QUERY関数で処理するとよいでしょう。

LET関数と組み合わせれば割とすっきり記述できます。

QUERY関数 クエリ句（完全制覇）

$$
\begin{array}{lll}
\text{No}&\text{句}&\text{使用量}\\ \hline
\text{1}&\text{select}&\text{出力する列を指定 （類似関数 CHOOSECOLS）}\\ \hline
\text{\text2}&\text{where}&\text{条件でデータをフィルタ （類似関数 FILTER）}\\ \hline
\text{3}&\text{group by}&\text{行方向（縦）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{4}&\text{pivot}&\text{列方向（横）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{5}&\text{order by}&\text{データを並び替え（類似関数 SORT）}\\ \hline
\text{6}&\text{skipping}&\text{〇行おきにデータを出力する}\\ \hline
\text{7}&\text{limit}&\text{〇行目までデータを出力}\\ \hline
\text{8}&\text{offset}&\text{〇行目からデータを出力}\\ \hline
\text{9}&\text{label}&\text{列のラベル名を変更する}\\ \hline
\textbf{10}&\textbf{format}&\textbf{特定の列の値を書式設定}\\ \hline
\text{11}&\text{options}&\text{追加オプションを設定。（使わない）}\\ \hline
\end{array}
$$

最後11番目の句 optionsは QUERY関数では使用しない句なので、全10のクエリ句の説明は今回で終了です。

隠れ句のskippingを含めた個々の句やスカラー関数、集計関数、演算子をここまでディープに解説したサイトは、他にはないでしょうｗ

残すは 第3引数の [見出し] のみ。

実はこの第3引数も特殊仕様があって、最後のQUERY関数 曲芸をお見せしたいと思います。

次回 QUERY関数シリーズ クライマックス！（最後とは言ってない）