用 QUERY 做分組彙總與月報：group by、pivot、label、format

月底要交一份月報：每個月賣了幾杯、收了多少錢、平均每筆多少，還要知道每個品項在外送和店內各賣多少。這篇用 QUERY 的 group by、pivot、label、format 把這份報表做出來。

select、where、order by 的基本寫法在 QUERY 基礎 講過了，這篇不重講。用到的資料是同一張，放在 A1:E13：

日期	品項	杯數	金額	通路
2026/03/03	蘋果紅茶	12	540	店內
2026/03/05	青茶	7	245	外送
2026/03/09	蘋果紅茶	5	235	外送
2026/03/14	烏龍奶茶	9	441	店內
2026/03/21	青茶	14	476	店內
2026/04/02	蘋果紅茶	8	376	店內
2026/04/06	烏龍奶茶	6	306	外送
2026/04/11	青茶	11	396	店內
2026/04/18	蘋果紅茶	13	598	外送
2026/04/27	烏龍奶茶	4	204	店內
2026/05/04	青茶	10	360	外送
2026/05/16	蘋果紅茶	7	329	店內

後面每一個示範公式都輸入 G1，取代上一個示範；先保持 G:K 空白，讓查詢結果能向右、向下展開。原始資料 A:E 不要覆寫。

最後想要的成品，長得像這樣：

先算每個品項賣多少

如果手算「蘋果紅茶」的杯數：12 + 5 + 8 + 13 + 7 = 45。三種品項各算一次，資料多了就很煩。這時候用 group by，把相同品項的列歸成一組，再對每一組做加總：

來看看結果：

蘋果紅茶是 45，跟手算一樣。第二欄的標題是 QUERY 自己取的，格式是「函數名 欄位名」，所以是 sum 杯數。

這邊有兩件事要記得。第一，group by 規定 select 裡的每一欄，不是被 group by 分組的欄，就是被 sum、count、avg、max、min 這類彙總函數包住的欄。寫 select B, C, sum(D) group by B 會出錯，因為每個品項有好幾個不同的杯數，QUERY 不知道要顯示哪一個。第二，彙總函數不能放進 where；想篩選彙總後的結果，要把這個 QUERY 的結果當成資料，再包一層 QUERY 去篩。

分組後的排列順序，中文是照字碼（Unicode）的順序，不是筆畫也不是注音，所以上面的表是烏龍奶茶、蘋果紅茶、青茶。要筆畫排序得用 SORT，QUERY 沒有這個行為。我個人比較喜歡直接用 order by 指定成「賣最多的排前面」，順序就不用靠運氣。

一次算加總、筆數、平均

彙總函數可以同時寫好幾個，再用 order by 排序、label 改標題：

新增的是查詢尾端的 label 子句；完整公式仍可從上方複製，以下分行看每個欄位如何改名：

label B '品項',
  sum(C) '杯數',
  count(D) '筆數',
  sum(D) '金額',
  avg(D) '平均每筆'

label 的寫法是「欄位 '新標題'」，用逗號隔開好幾組。label 要放在 order by 的後面。標題我都會用 label 寫死，因為預設的 sum 杯數 要是之後資料欄位改名，標題也會跟著變，下游如果有公式在參照標題文字就會壞掉。

用 format 整理數字

金額 2078 沒有千分位，平均 415.6 小數位數也不一致。format 用 ICU 的格式碼，放在最後面：

這次保留前一步的選欄、分組、排序與標題，只在最後新增這兩項格式：

format sum(D) '#,##0',
  avg(D) '#,##0.00'

sum(D) 對應金額，#,##0 加千分位且不留小數；avg(D) 對應平均每筆，#,##0.00 固定顯示兩位小數。format 只改顯示格式，不改彙總的數值。

子句的順序要照這個來：select、where、group by、pivot、order by、limit、offset、label、format。format 在最後，label 在它前面。

用 pivot 做交叉表

想知道每個品項在外送和店內各賣多少杯，group by 加上 pivot，會把 pivot 指定欄位裡的每一種值各自變成一欄：

青茶外送是 7 + 10 = 17，店內是 14 + 11 = 25，跟手算對得上。如果某個品項在某個通路完全沒有訂單，那一格會是空白。

pivot 的欄位不能再出現在 select 或 group by 裡。另外，pivot 產生的欄位標題是自動生成的，不太好用 label 控制，所以如果最後要拿去給別的公式引用，我會傾向用前面的 group by，不用 pivot。

做互動式的探索，想隨時拖拉換維度，用 資料透視表 比較順手。資料透視表在資料變動後會自己更新，QUERY 的結果也是即時重算，兩者都不用手動重整。

組成月報

這一小節先使用同一年的資料（本例都是 2026 年）；只有月份的分組會合併不同年份的同月，跨年版本在下方。

月份要從日期欄算出來。month(A) 從 0 開始算，1 月是 0、3 月是 2，所以要加 1。把月份當成分組欄位，再放上彙總：

3 月的杯數 12 + 7 + 5 + 9 + 14 = 47，金額 540 + 245 + 235 + 441 + 476 = 1,937。

想只看外送，加 where，它要放在 group by 的前面：

之後新增資料怎麼辦

持續新增訂單時，把來源範圍改成 A1:E，並且同時依年份和月份分組，避免把 2026 年 3 月與 2027 年 3 月加在一起。跨年版本如下，仍貼在 G1：

跨年版本會多一欄「年份」，結果需留空 G:L 六欄。關鍵差異拆開看：

select year(A), month(A)+1, …
where A is not null
group by year(A), month(A)+1
order by year(A), month(A)+1

year(A) 讓每一年的同月分開；where A is not null 排除開放範圍裡尚未填日期的列；最後的 1 明確指定一列表頭。例如再新增一筆 2027/03/03、蘋果紅茶、1 杯、45 元、店內，會新增「2027／3」的一列，原本「2026／3」仍是 47 杯、1,937 元。

選用 QUERY 而不是 SUMIFS，是因為年月組合會從資料產生，不用事先列出每一個月。

where 裡有 and、or 混用的條件，要記得加括號，不然 and 會先於 or 計算；日期條件、標題列判斷這些也都有幾個容易踩的地方，寫完先用幾筆小資料對一下手算結果，會比較安心。

再往下鑽：每月 5 日、晚間時段與件數

方格子的日期時間擷取可以接在月報後面，回答「活動當天／晚間到底賣多少」。另開空白練習表，從 A1 貼下列資料；一列是一筆合成訂單，件數都有效：

日期時間	品項	件數
=DATE(2026,12,5)+TIME(20,59,59)	紅茶	2
=DATE(2026,12,5)+TIME(21,0,0)	紅茶	3
=DATE(2027,1,5)+TIME(23,59,59)	青茶	4
=DATE(2027,1,6)+TIME(0,0,0)	紅茶	5
=DATE(2027,1,5)+TIME(22,30,0)	紅茶	6

A 欄由 DATE 加 TIME 建立真正的日期時間，不是看起來像日期的文字。在空白 E1 輸入：

dayOfWeek 的 1 是星期日，和 WEEKDAY 可選的編碼方式不能混用；month 從 0 開始，這裡已加 1。這些函式讀取表內日期時間，不會替匯入的 UTC 字串自動完成台北時區換算；來源時區與試算表設定仍應先確認。

21 點至 23 點：sum 是件數，count 是筆數

在 M1 輸入：

20:59:59 不納入，21:00:00 與 23:59:59 納入，隔天 00:00:00 不納入。條件用 and，不是 or；大於等於 21 或小於等於 23 幾乎會把全天都放進來。

sum(C) 把 3 與 6 合成紅茶 9 件，count(C) 是這兩筆有效件數的筆數。本例一列一筆且件數完整，才能把它標成訂單筆數；真實資料若一張訂單拆成多列、件數缺漏，就要改用可靠的訂單 ID／去重規則。不能把 count 的結果直接稱為售出件數。

每月 5 日另放 Q1，同時保留年份：

結果為 2026／12：5 件、2 筆；2027／1：10 件、2 筆。這題沒有夜間條件，所以 12 月 5 日 20:59:59 的 2 件也會納入；日期與時段兩種篩選不要混成同一個問題。

SELECT 裡跨欄相加：空值會改變答案

方格子的 SELECT 四則運算適合處理手上已有的橫向月報。這不表示原始交易資料都應改成一月、二月、三月三欄；持續累積交易仍可保留前面的長表，再產生展示用橫表。

在同一張練習表的 A14 貼上，甲的第二筆二月真的留空：

門市	一月	二月	三月
甲	10	20	30
甲	5		15
乙	1	2	3

F14 計算每一列的第一季：

結果是三列：甲／60、甲／空值、乙／6。第二筆不是 20：B+C+D 遇到空值，並不等於試算表的 SUM 自動略過空格。

J14 改成先分組、再相加：

結果是乙／6、甲／80。甲的三欄各自加總為 15、20、45，再相加為 80；這和逐列計算後忽略空結果的 60 是兩回事。這裡沒有指定排序，展示順序不要當固定契約，正式報表應另加 order by。

**哪個數字合理，取決於空值代表什麼。**如果只是還沒回報二月，80 是目前已知數字的合計，不代表第一季資料已完整；若業務明訂沒有交易就是 0，應先檢查後補成 0，別靠換一個算式默默改掉缺漏意義。label 必須對 sum(B)+sum(C)+sum(D) 整段命名，不能只改其中一欄。

除以零也可能安靜地變空值

在 A23:B25 放「分子／分母」表頭，下兩列為 10／2、10／0；E23 輸入：

結果為 5、空值，沒有跳出 #DIV/0!。因此不能只靠外層 IFERROR 找出分母零；需要另列分母檢查、待補原因或排除筆數，避免把空結果看成正常的 0。

以上日期時間與橫表算術另放獨立練習表；下面的通路別題目回到開頭 12 筆訂單的 A1:E13。

練習

結果是兩列：店內 7 筆、2762，外送 5 筆、1744。

同一份彙總也可以用 資料透視表 拖拉做做看，比較一下哪一種順手。

接下來可看 QUERY 進階陷阱，檢查日期、空值與混合型別；想回顧選欄與篩選，則回到 QUERY 基礎。