Googleスプレッドシート QUERY関数 超応用例 20（唯一無二の format）
Googleスプレッドシートの最強集計関数 QUERY関数シリーズを再開します。今回は遂に第20回です。
👇これまでのQUERY関数シリーズは 無料マガジンにまとめています。QUERY関数を宇宙（どこ）までも極めたい人は必読です！
先週のnoteは バイブコーディングで GASを使ってGoogleスプレッドシートにストップウォッチサイドバー機能を実装する方法を紹介しました。
QUERY関数の表示形式を制御する format句
今回はクエリ文の
第10の句 format
について学んでいきます。
$$
\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は、GoogleスプレッドシートのQUERY関数で使用しても効果が無いのでQUERY関数で利用できる最後の句が、この format です。
いままでシリーズの中で何度もお伝えしましたが、クエリ句は記述順が厳密（表の順番通りでないとエラーになる）です。
format句が最後の句ということは、format句はクエリ文の一番最後に記述するということです。記述順に注意しましょう。
さらに 他のQUERY関数を解説するサイトでは、format句の解説はさらっと流していることが多いんですが、実はこのformat句はExcelやGoogleスプレッドシートの他の関数では真似が出来ない 超希少句（レア）な機能を持っています。
唯一無二とタイトルに入れているのはそれ故にです。
表示形式を制御する QUERY関数の format の使い方
format はフォーマットという名前の通り、出力するデータの表示形式を制御する為の句です。
format句に関してはクエリ言語リファレンスの解説は、GoogleスプレッドシートのQUERY関数には適していません。
是非このnoteで理解を深めてください。
=QUERY(表_1[#ALL],"format Col2 'yyyy年mm月dd日'")
まずは基本の使い方から
format 列指定 '表示形式'
format に続けて半角スペースを挟んで 列指定、半角スペース、そして ' シングルクォートで括って表示形式を指定することで、QUERY関数の指定した列の出力結果の表示形式を変えることが出来ます。
👆の例は 2列目の日付の表示形式を
元データ yyyy/mm/dd
だったのを
出力 yyyy年mm月dd日
に変更しています。
これって
TEXT(数値, 表示形式)
TEXT関数 とほぼ同じですよね。
TEXT関数が使える人ならすんなり理解できると思います。
表示形式の指定方法は、カスタム数値形式の公式ガイドを参考に。
format を複数列に使う
label句や order by句 と同じように format句も 複数の列に適用する際は , カンマで区切って記述します。
=QUERY(表_1[#ALL],"format Col1 '000',Col2 'yyyy年mm月dd日'")
👆たとえばこちらの式では
Col1 '000', 1列目No の数値を000の3桁0埋め表記、
Col2 'yyyy年mm月dd日' 2列目の日付を 年月日 表示
に変更しています。
format句の特徴を理解する
ここからが本番です。
format句を極める為に ディープな5つの特徴を理解していきましょう。
format句は 表示形式を制御できる（TEXT関数とは違う）
format句よりもセルの表示形式の設定が優先される（自動で使おう）
format句は 数値型にしか使えない（@で文字列の表示形式指定は不可）
format句は [条件]表示形式 が使える
format句は 実際の値を変更できるわけではない（中身は変わらない）
1. format句は 表示形式を制御できる（TEXT関数とは違う）
format句の構文自体は TEXT関数と近いんですが、実はやってることの根本的な部分が違います。
TEXT関数は「指定した表示形式に成形したテキストを出力する」関数です。
上の例で言えば、A2セルの 2025/12/1 という日付は TEXT関数では指定した yyyy年mm月dd日 という表示形式を適用した 2025年12月01日 という 文字列を返しています。（結果が左寄せになっているのがわかりますね）
つまり結果は日付型ではないので演算子や日付型に使える関数は使えず、式で参照した A2セルの元データとも一致しないってことです。
一方 QUERY関数のformat句は、中身はそのままで表示形式だけを変える仕様となっています。
※ISDATE関数は日付と見なせる文字列もTRUEを返します。真に日付型であるか？を判定できる関数は 隠し関数の ISDATE_STRICTとなります
日付型はそのまま維持し、A2セルとも一致します。
つまりセルの表示形式と同じ効果を関数で実現できるってことです！
QUERY関数のformat句を解説しているサイトの多くが「特定の列の値を表示する形式（フォーマット）を指定することが出来る」とさらっと書いていますが、
よーく考えてください
関数で表示形式を指定出来るって驚きじゃないですか！？
このように表示形式を自由にコントロールできる関数は、ExcelにもGoogleスプレッドシートにも他に存在しません。
QUERY関数のformat句が唯一無二、表示形式を制御できる関数と言えます。
2.format句よりもセルの表示形式の設定が優先される（自動で使おう）
format句はセルの表示形式を設定した時と、同じ効果があることがわかりました。
では、format句と表示形式がバッティングした場合、つまり両方が同じセルに設定された場合はどうなるか？
これは 表示形式の設定がformat句よりも優先されます。
👆の画像は
=QUERY($A$1:$B$10,"format Col2 '0個'")
という式で、2列目の数量の数値に'個'をつけてるんですが、E2:E10のセル範囲に事前に メニューから 表示形式 ＞ 数値 ＞ カスタム数値形式 で
0ぴょん
という数字の後ろに"ぴょん"を付ける設定だった場合は、formatで指定している"個"ではなく、"ぴょん"が優先されているのがわかりますね。
表示形式の指定を解除（自動に変更）すると、format句で指定した 個 に変わります。
つまり、format句を活用したい場合は セルの表示形式は「自動」としておく必要があるってことです。
format句で指定してるのに表示形式が変わらない場合は、セルに表示形式が設定されていないか？を確認しましょう。
3. format句は 数値型にしか使えない（@による文字列の表示形式指定は不可）
TEXT関数やカスタム表示形式では @ を使うことで 文字列に対しても表示形式を指定することが出来ます。
=ARRAYFORMULA(TEXT(A2:A10,"@ 様"))
よくあるのが、"@ 様" のように 名前の後ろに 様 をつける、なんて使い方ですね。
しかしQUERY関数の format句では、文字列型の表示形式指定が出来ません。
=QUERY(A1:B10,"format Col1 '@様',Col2 '0個'")
✖ '＠様' は機能しない
エラーにはなりませんが、文字列型である1列目に対してformat句で
Col1 '@様'
と指定しても表示形式が変わりません。
format句が使えるのは 数値および日付、日時、時刻 の型だけです。
4. format句は [条件]表示形式 が使える
ExcelやGoogleスプレッドシートに詳しい人なら、format句の表示形式の指定が TEXT関数やカスタム数値形式と同じような感じってことは、正の数や負の数で表示形式を切り替える記述も使えるのでは？ となりますよね。
これはQUERY関数で使えます！
=QUERY(A1:B10,"format Col2 '0個;▲0個;'")
👆こちらの式は
0個;▲0個;
正の数の時の表示形式 ・・・ 0個
負の数の時の表示形式 ・・・ ▲0個
0（ゼロ）の時の表示形式 ・・・ （空白）
と指定したケースです。
3つの形式を ; （セミコロン）で区切って記述します。
TEXT関数やカスタム数値形式だと、さらに ４つ目にテキストの場合の表示形式が指定できますが、QUERY関数のformat句では使いません。（そもそも型が混在できない & テキストは表示形式を制御できない）
もちろん空白に見えるセルの中身は 0です。表示が空白となっているだけです。
これがQUERY関数で出来ちゃうってことです。
ちなみに表示形式だと色指定も可能なんですが、さすがにformat句では色は反映されません。
で、これを応用したのが [条件] 表示形式 なんですが、これもformat句で使えます！
=QUERY(A1:B10,"format Col2 '[>10]多い;[>0]少ない;在庫なし'")
'[>10]多い;[>0]少ない;在庫なし'
このように指定することで、
10より大きい数値は 多い
0より大きい数値は 少ない （10より大きいの条件を満たさず）
それ以外 在庫なし （上の2つの条件を満たさない）
と表示させてます。
この書き方は、
✅指定できる条件は2つまで
✅指定した数値と比較演算子を使ったシンプルな条件のみ
※一致の時は [=数値] とする
✅条件は左から順に評価していく
（1番目に [>10] としたら 2番目に[>20] としても意味はない）
✅最後にそれ以外の表示形式を必ず指定
というルールがあるので、あまり複雑な分岐はできません。
[条件]表示形式 の書き方のルールに準拠していない場合は
このように #VALUE!エラーや 表示形式の指定がないと ##### が返ります。
ちなみに 条件は1つで良い場合は、マッチしないものはそれ以外として2種類の表示形式を設定することも可能です。
この [条件]表示形式 は、以前ランキンググラフを作る noteでも紹介しました。
制限はあるもののセルにカスタム数値形式を指定するのと違って、QUERY関数のformat句であれば 比較に使用する数値を セル参照にしたり、数式で生成することが出来ます。これが凄い！
これを使ったQUERY関数曲芸（超応用例）は次回紹介します！
5. format句は 実際の値を変更できるわけではない（中身は変わらない）
これは当たり前なんですが、formatで表示を変えてるとついつい見えているものが正だと勘違いしてしまいます。
最初に言った通り、TEXT関数と違って format句は 表示だけを変えるもので、中身は変わっていません。
だから、右側の出力データを FILTER関数で "多い"を条件にしても
1件もヒットしませんし、
=QUERY(D2:E11,"select Col2,count(Col1) group by Col2")
QUERY関数でグループ集計しても、同じ「在庫なし」表示でも中身はバラバラの数字なので、2列目がユニーク化されません。
中身はそのままで数値の表示形式だけを変えたい
▶ QUERY関数の format
数値の表示形式を変えた結果を別の関数で扱いたい
▶ 元データをTEXT関数で処理
ケースによって、format句と TEXT関数とを使い分けが必要ってことです。
👆TEXT関数を使って数量に応じて3グループ化、それをQUERY関数でグループ集計
関数で表示形式を制御できる ▶ QUERY関数の使い方が変わる
QUERY関数のformat句を使えば、表示形式も制御可能です。そのため、フォーマット要件がある場合は、他の関数の代わりに「あえてQUERY関数を採用する」のも有効な手段です。
たとえば
こんな感じでB2:B5の合計を D2に入れたい。ただし、〇個と数字の後ろに「個」をつけて出力したい。といった場合は、
もちろん通常は =SUM(B2:B5) をD2に入れて、D2セルを選択した状態でメニューから
表示形式 ＞ 数値 ＞カスタム数値形式
と進んで 0個 として。と伝えるのが基本です。
でも QUERY関数を使った
=QUERY(B2:B5,"select sum(Col1) label sum(Col1) '' format sum(Col1) '0個'")
👆こんな式や
SUM(B2:B5)を残しつつ、表示形式の設定部分だけをQUERY関数で処理する
=QUERY(SUM(B2:B5),"format Col1 '0個'")
👆こんな式を用意すれば、「これをD2セルに入れて」で済むわけです。
表示形式が設定できる関数と捉えると、QUERY関数の活用シーンが一気に広がりますね！
QUERY関数 format句 基本問題～ちょい応用に挑戦
それでは format句を使ったお題に挑戦してみましょう。
基本問題と書いてますが、他のサイトだと応用クラスです。これまでの句も使いますので復習にも最適。
データは前に使った
日付 営業担当 商品 売上金額
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これを販売実績というテーブルにして使いましょう。
Q1. 年・月で 売上合計をピボット集計したい（成形あり）
販売実績のデータを 縦に月ごと、横に年ごと に売上金額の合計をピボット集計をしたい。
ただし画像のように
見出しは 月、〇年 売上合計
月は 〇月
売上合計金額は ¥をつけて3桁区切り
と表示させたい。QUERY関数でどのような式を組めばよいか？
もちろん シートの表示形式は一切変えず、式はF1セルに1つだけ使ってです。
これはベースは QUERY関数シリーズ15の Q1. 年・月で 売上合計をピボット集計したい がベースのお題です。
ピボット集計部分を忘れちゃってる人は、👆リンク先の過去noteの確認を。
考えてみましょう！
↓↓
回答はここから。
↓↓
A1. 年・月で 売上合計をピボット集計する（成形あり）
回答です。
=QUERY(販売実績[#ALL],"select month(Col1)+1,sum(Col4)
group by month(Col1)+1 pivot year(Col1)
label month(Col1)+1 '月',sum(Col4) '年 売上合計'
format month(Col1)+1 '0月',sum(Col4) '¥#,##0'")出来たでしょうか？
=QUERY(販売実績[#ALL],"select month(Col1)+1,sum(Col4)
group by month(Col1)+1 pivot year(Col1)
ここまでは 過去のお題の回答と一緒の式です。
QUERY関数のスカラー関数 month は0スタートなんで +1する必要があります。
【注意】QUERY関数の スカラー関数 month() は 0はじまりでひと月ズレる
見出し部分の成形が
label month(Col1)+1 '月',sum(Col4) '年 売上合計'
この部分。
で、今回学んだ format句を使って 〇月と 売上金額の ¥つき3桁区切り の表示形式の指定が
format month(Col1)+1 '0月',sum(Col4) '¥#,##0'
この部分です。
記述順にも気を付けましょう。
Q2. 年・月で 売上合計をピボット集計した上で 前年比の差分と割合も表に入れたい（成形あり）
同じデータを使ってもう1問挑戦してみましょう。
先ほどの年と月の売上金額のピボット集計に、さらに前年との差額、そして前年比の割合も合わせて出力したい。
差額は見出しを前年比差額とし、0より大きい場合は +をつけ、0より小さいマイナスの場合は ▲を付ける。0の場合（前年同額）は空白とする。
その上で ¥マーク付きの3桁区切りとする。
前年比割合は 小数点2桁の%（パーセント）表示とする。ただし100%より大きい場合は ⇧、小さい場合は ⇩ をつける。0%（前年同額）の場合は何もつけない。
これを1つの式、表示形式の設定なしで実現したい場合は、どのような式を組めばよいでしょうか？
これもQ1と同じく QUERY関数シリーズ15の Q2. 年・月で 売上合計をピボット集計した上で 前年比の差分と割合も表に入れたい がベースとなっています。
考えてみましょう！
↓↓
回答はここから。
↓↓
A2. 年・月で 売上合計をピボット集計した上で 前年比の差分と割合も表に入れたい（成形あり）
回答です。
=LET(
x,QUERY(販売実績[#ALL],"select month(Col1)+1,sum(Col4)
group by month(Col1)+1 pivot year(Col1)
label month(Col1)+1 '月',sum(Col4) '年 売上合計'
format month(Col1)+1 '0月',sum(Col4) '¥#,##0'"
),
QUERY(x,"select Col1,Col2,Col3,Col3-Col2,Col3/Col2
label Col3-Col2 '前年比差額',Col3/Col2 '前年比割合'
format Col3-Col2 '+¥#,##0;▲¥#,##0;',
Col3/Col2 '[>1]⇧0.00%;[<1]⇩0.00%;0.00%'"
)
)QUERY関数を2段階で利用します。
とりあえず前年比差額と前年比割合以外はQ1と一緒の式なんで、Q1 の回答のQUERY関数の結果を LET関数で xと置きましょう。
その上で
QUERY(x,"select Col1, Col2, Col3, Col3-Col2, Col3/Col2
Col3-Col2 が前年からの差額
Col3/Col2 が前年と比較した割合
です。ここまでは良いですね。
見出し部分の
label Col3-Col2 '前年比差額',Col3/Col2 '前年比割合'
ここも大丈夫ですね。
で、今回学んだ format句を使う
format Col3-Col2 '+¥#,##0;▲¥#,##0;',
Col3/Col2 '[>1]⇧0.00%;[<1]⇩0.00%;0.00%'
ここがポイントです。
Col3-Col2 '+¥#,##0;▲¥#,##0;'
差額は 正の数;負の数;0の時 の表示形式分岐
Col3/Col2 '[>1]⇧0.00%;[<1]⇩0.00%;0.00%'
割合は [条件1]表示形式1;[条件2]表示形式2;その他の表示形式 この条件分岐としています。
100%は中身は1なので、「より大きい」は [>1]、「より小さい」は [<1]と記述します。
理解できたでしょうか。
QUERY関数 format句 超応用に挑戦
最後にもう1問、正統派な超応用例に挑戦しましょう。
Q3. 毎日の売上データから曜日別の売上合計を集計して、一番売上が多い曜日を可視化したい
左側の日付、店舗、売上額のデータから QUERY関数で右のような 曜日ごとの売上金額の合計を集計したい。
集計表の 見出しは「曜日」「売上」として、曜日は 月曜日～日曜日と月曜スタートの順で表記し、売上金額合計は3桁区切りのカンマを入れたものとする
まずは、ここまで実現してみましょう！
余裕のある人はさらに「売り上げが最も大きい曜日には 👑マークを表示させる」これも追加条件に加えて式を作ってみましょう。※売り上げが最も大きい曜日が2つ以上ある場合は考慮しないものとします。
式はE1セルに1つだけ入れるとして、使える関数はLET関数とQUERY関数のみで他のシート関数を使わない、第1引数で指定する範囲は A:C とするを条件とします。
データは以下を利用ください。※今回はテーブルにはしない
日付 店舗 売上額
2025/10/01 A 756123
2025/10/01 B 321456
2025/10/01 C 987654
2025/10/02 A 456789
2025/10/02 B 123456
2025/10/02 C 876543
2025/10/03 A 654321
2025/10/03 B 234567
2025/10/03 C 998877
2025/10/04 A 789012
2025/10/04 B 345678
2025/10/04 C 567890
2025/10/05 A 112233
2025/10/05 B 887766
2025/10/05 C 445566
2025/10/06 A 901234
2025/10/06 B 567890
2025/10/06 C 234567
2025/10/07 A 765432
2025/10/07 B 345678
2025/10/07 C 890123
2025/10/08 A 456789
2025/10/08 B 100000
2025/10/08 C 999999
2025/10/09 A 678901
2025/10/09 B 210987
2025/10/09 C 765432
2025/10/10 A 321098
2025/10/10 B 876543
2025/10/10 C 543210
2025/10/11 A 987654
2025/10/11 B 654321
2025/10/11 C 123456
2025/10/12 A 789012
2025/10/12 B 345678
2025/10/12 C 876543
2025/10/13 A 456789
2025/10/13 B 100000
2025/10/13 C 999999
2025/10/14 A 678901
2025/10/14 B 210987
2025/10/14 C 765432
2025/10/15 A 321098
2025/10/15 B 876543
2025/10/15 C 543210
2025/10/16 A 987654
2025/10/16 B 654321
2025/10/16 C 123456
2025/10/17 A 789012
2025/10/17 B 345678
2025/10/17 C 876543
2025/10/18 A 456789
2025/10/18 B 100000
2025/10/18 C 999999
2025/10/19 A 678901
2025/10/19 B 210987
2025/10/19 C 765432
2025/10/20 A 321098
2025/10/20 B 876543
2025/10/20 C 543210
2025/10/21 A 987654
2025/10/21 B 654321
2025/10/21 C 123456
2025/10/22 A 789012
2025/10/22 B 345678
2025/10/22 C 876543
2025/10/23 A 456789
2025/10/23 B 100000
2025/10/23 C 999999
2025/10/24 A 678901
2025/10/24 B 210987
2025/10/24 C 765432
2025/10/25 A 321098
2025/10/25 B 876543
2025/10/25 C 543210
2025/10/26 A 987654
2025/10/26 B 654321
2025/10/26 C 123456
2025/10/27 A 789012
2025/10/27 B 345678
2025/10/27 C 876543
2025/10/28 A 456789
2025/10/28 B 100000
2025/10/28 C 999999
2025/10/29 A 678901
2025/10/29 B 210987
2025/10/29 C 765432
2025/10/30 A 321098
2025/10/30 B 876543
2025/10/30 C 543210
2025/10/31 A 987654
2025/10/31 B 654321
2025/10/31 C 123456チャレンジしてみましょう！
↓↓
回答はここから。
↓↓
A3. 毎日の売上データから曜日別の売上合計を集計したい（👑なし）
回答です。まずはシンプルな曜日の集計から
=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) '売上'
format dayOfWeek(Col1) 'dddd',sum(Col3) '#,##0'"
)これはQUERY関数一発で可能です。
まず曜日ごとに集計するので スカラー関数の dayOfWeek() を使います。
第1引数の範囲が A:C なので空白除去をする為に where句も組み合わせて、group by で曜日ごとに集計する式なんで
=QUERY(A:C,"select dayOfWeek(Col1),sum(Col3)
where Col1 is not null group by dayOfWeek(Col1)
ここまでは大丈夫ですね？
これに見出し部分の成形
label dayOfWeek(Col1) '曜日',sum(Col3) '売上'
データの表示形式指定
format dayOfWeek(Col1) 'dddd',sum(Col3) '#,##0'"
を加えると
このようになります。
表示形式で曜日にするには、Excelと違って Googleスプレッドシートの場合は
短い曜日 月,火,水 ・・・ ddd
長い曜日 月曜日,火曜日,水曜日 ・・・ dddd
となります。
でも、これだと一番小さい数値の1の日曜日が一番上ですね。これを月曜日スタートとするにはどうすればよいか？
それが
order by (dayOfWeek(Col1)+5)%7
この部分です。
以前 order by 句の超応用例のお題で、演算子%（剰余）を使って 月を4月始まりにするテクニックを紹介しましたが、曜日も同じように %を使ってスタートを調整できます。
月曜日スタートにする為には、1～7の数字の 2を一番小さくして1を一番大きい数に加工すればよいので、最終的に7で割ることを考えると
割った時に余が0となる、つまり 2に5を加算して 7とすれば良さそうです。
この曜日の数値に5を足して7で割った余りは
(dayOfWeek(Col1)+5)%7
で取得できます。
これは並び替えにだけ使えばよいので、selectやgorup by は dayOfWeek(Col1)のままで、並び替えの order by句だけ
order by (dayOfWeek(Col1)+5)%7
とすればOK。
これで月曜スタートに加工ができました。
A3. 毎日の売上データから曜日別の売上合計を集計したい（👑あり）
それでは、ここから売り上げがもっとも大きい曜日に👑をつける方法を考えていきましょう。
つまり、目視でもわかりますが 一番売り上げが大きい金曜日を取得できれば良いわけです。
もちろんMAX関数を使ったり、XLOOKUP関数を使えば可能ですが、LET関数とQUERY関数だけを使ってという縛りがあります。
どうすればよいか？
ここでは QUERY関数の order by 、limit、そして第3引数を使います。
まず先ほどのQUERY関数の結果を LET関数で xと置いて、これを使った
QUERY(x,"select Col1 order by Col2 desc")
という式を考えます。
必要になるのは曜日の列だけ、かつ売り上げが一番大きい金曜日が取得したいので、売上の列 Col2 を降順で並び替えます。
ただ、ここで見出しが邪魔になるので、まだ学んでないんですが見出し0指定で見出しもデータに含めてしまいます。
分かりやすいように2列目も出力しておくと
このように曜日も売上もどちらも数値列（曜日は中身は数値）なので、見出しの文字列は型違いとして空白になり、かつ2列目の売上で降順にすると空白は最も小さいと判定されるので、一番下にきます。
これで取得したい金曜日が一番上にきましたね。
あとは1列目だけにして出力する行数を制限する limit句で1とすれば
QUERY(x,"select Col1 order by Col2 desc limit 1",0)
売上が最大の曜日が出力できました。
これは見た目は「金曜日」ですが、中身は5という数値なので、format句の [条件]表示形式 で利用することができます。
QUERY(x,"format Col1 '[="&y&"]👑dddd;dddd'")
これで正解でもいいんですが、どうせ最後に成形するなら label句とformat句 そしてorder by句も 最後に持ってきた方が Col1,Col2で列指定が出来てすっきりします。
というわけで、
=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'")
)最終回答はQUERY関数3発のこんな式 になります。
QUERY関数にかなり精通していないと書けない式です！
ここまでたどり着けた人はお疲れさまでした～。
QUERY関数 format句 次回は「え！？こんなことも出来るの？」を紹介
今回はQUERY関数の format句 を学びました。集大成って感じの超応用例も登場したんで、お題にチャレンジして理解が出来た方は QUERY関数の自信がついたんじゃないでしょうか？
次回もう1回 QUERY関数の format句を取り上げます。
まず今回の超応用例の問3の👑ありで、もっとも売り上げが大きい曜日が複数あるケースにも対応したい場合どうするか？にチャレンジしてみましょう。
さらに スプレッドシートでは不可能と思われた、あんなことやこんなことが出来ちゃう！ QUERY関数曲芸（超応用例）を見ていきたいと思います！！
いいなと思ったら応援しよう！
チップ大歓迎です。やる気がアップしますｗ