「找出高於平均金額的訂單」有兩個步驟：先算全部有效金額的平均值，再拿每筆訂單跟它比較。結果應保留訂單編號、門市與原始金額，方便回頭處理個別訂單。
這個需求是在篩原始明細。它與「各門市加總後，只保留總額達標的門市」不同，也不是在試算表的查詢文字裡使用 SQL 子查詢或 HAVING。
準備可核對的資料
在空白工作表的 A1:C5 輸入：
| 訂單 | 門市 | 金額 |
|---|---|---|
| Q01 | 信義 | 10 |
| Q02 | 板橋 | 20 |
| Q03 | 信義 | 30 |
| Q04 | 中和 | 40 |
A6:C20 保持空白。每筆訂單的三欄都要填完整；訂單編號與門市用文字，金額用真正的數值。範圍中的金額空白表示未提供金額，不參與平均與明細結果，也不當成 0；要讓免費訂單參與平均，請明確填入 0。
平均金額是 25，所以嚴格「高於平均」只應留下 Q03 與 Q04，不能只看到兩個金額而遺失訂單資料。
把平均值接進 QUERY
在 E1:G1 分別填入「訂單、門市、金額」。清空 E2:G20，再在 E2 輸入：
先檢查金額，再以平均值篩選原始訂單
=IF(
COUNTA(A2:C20)=0,
"沒有資料",
IF(
COUNT(C2:C20)=0,
"沒有可計算金額",
IF(
COUNT(C2:C20)<>COUNTA(C2:C20),
"請先把金額轉成數值",
LET(
threshold, AVERAGE(C2:C20),
IFNA(
QUERY(
A2:C20,
"select A, B, C where C > " & threshold,
0
),
"沒有高於平均的明細"
)
)
)
)
)結果會放在 E2:G3：
| 訂單 | 門市 | 金額 |
|---|---|---|
| Q03 | 信義 | 30 |
| Q04 | 中和 | 40 |
LET 把平均值命名為 threshold。& 將數字接到查詢文字後面，形成 select A, B, C where C > 25；QUERY 收到的是已經算好的數字條件，沒有在 where 裡執行 avg(C)。
第三個參數 0 代表輸入範圍 A2:C20 沒有標題列。E1:G1 是自己寫的表頭，不在查詢結果裡。若範圍改成包含 A1:C20，也要同步修改標題列參數，不能只改參照。
為什麼先檢查金額型別？
Google 官方說明，QUERY 的同一欄混合不同型別時，會以多數型別決定欄位型別，少數型別視為空值。看起來是數字的文字，也可能因此消失。QUERY 官方說明
這份公式遇到非空白金額中有文字，會要求先轉成數值；全表空白顯示「沒有資料」，有訂單但沒有任何數值金額顯示「沒有可計算金額」。這些訊息優先於查詢結果。它不替你補齊缺少的訂單編號、辨識重複訂單或修正錯誤值；正式使用前仍要檢查資料品質。
如果平均值由 25 變成小數，仍應以實際平均值比較，不要為了顯示好看先四捨五入。
大於與大於等於，差在哪裡？
把資料改成只有 10、20、30 三筆，平均是 20：
| 條件 | 留下的金額 |
|---|---|
>，高於平均 | 30 |
>=，高於或等於平均 | 20、30 |
需求寫「高於」就用 >；寫「不低於」則用 >=。若所有金額都是 20，嚴格大於平均沒有符合資料，應顯示「沒有高於平均的明細」。
Google 的查詢語言允許的子句與聚集函數位置，請以官方查詢語言參考為準。若想篩選門市加總後的結果，需要先建立彙總表，再對彙總表查詢；請接著看 QUERY 資料分析。
自己練習與排錯
- 把
C2:C4改成 10.5、20.25、30.75，清空A5:C5。平均是 20.5，應只留下 Q03 的 30.75。 - 改成 10、20、30，再把條件
>改成>=，確認結果多了 Q02；完成後還原>。 - 在
C3輸入前置單引號的'20，確認顯示轉數值訊息。把它重新輸入為數值 20，再還原本篇四筆原始資料。
遇到 #REF!，先清空結果要展開的 E2:G20，也檢查合併儲存格。遇到解析錯誤，檢查雙引號、& 與最後的 0；不要將查詢文字裡的 A 改成輸出欄 E，查詢欄位指的是輸入資料。
本文改寫自方格子的動態平均門檻案例，改用自製訂單資料，並分清原始明細與彙總篩選。若條件來自多份選項清單，可接著看 QUERY 動態多選。
接下來可以
相關文章
查看所有文章QUERY 基礎：select、where、order by、limit 四句就能查資料
從一張訂單表開始，學 QUERY 的查詢語言：select 選欄位、where 設條件、order by 排序、limit 取前幾筆，以及欄位代號 A、B 與 Col1 的差別、文字條件的引號和 headers 參數。
用 QUERY 做分組彙總與月報：group by、pivot、label、format
用一張訂單表，一步步用 QUERY 的 group by 算品項加總、筆數、平均，用 pivot 做交叉表，用 label 和 format 整理欄位標題與數字格式，最後組成一份會自動更新的月報。
QUERY 少了幾列、日期比不出來、括號算錯的原因與修法
整理 QUERY 不報錯卻給錯結果的情況：一欄混了文字、日期條件與儲存格拼接、文字與數字的引號、and 與 or 的括號、彙總不能放進 where、插欄後的欄位字母、標題列參數猜錯。
留言討論
共 0 則留言