掌握用 QUERY 按最高數字排序是提升 Google Sheets 資料彙整與報表自動化能力的關鍵。當你需要根據某個聚合值(例如出席次數、銷售總和或平均值)將結果由高到低排列時,正確使用 QUERY 語句的 order by 子句可以省去手動排序與多重公式的麻煩。以下從基本語法到進階情境全面說明,並提供實務可直接套用的範例與排錯建議。 首先回顧 QUERY 的基本結構以理解排序位置。QUERY 函數的常見呼叫方式是 =QUERY(範圍, "查詢字串", 標頭列數)。在查詢字串中你可以使用 Select、Where、Group by、Order by、Label 等關鍵字。
當你在做群組彙總時,例如使用 count()、sum()、avg() 等聚合函數,若要依聚合結果排序,需要在查詢字串的結尾加入 order by 聚合函數 並指定 desc(由高到低)或 asc(由低到高)。 以一個實務案例說明。原始使用者想要將出席次數由高到低排序,原始 QUERY 如下: =query(Attendence!A:G, "Select A, B, C, E,count(G) where D <>'' and G ='Attended' group by A, B, C, E label count(G) 'Count of sessions attended'", 1) 如果要把結果以「出席次數」由多至少排序,只要在 group by 後加入 order by count(G) desc,就能達成。修改後的語句如下: =query(Attendence!A:G, "Select A, B, C, E,count(G) where D <>'' and G ='Attended' group by A, B, C, E order by count(G) desc, A asc, B asc label count(G) 'Count of sessions attended'", 1) 這段查詢先以 count(G) desc 將彙總數從大到小排列,接著用 A asc、B asc 做第二、第三排序依據,這在出現相同次數的情況下可以按姓名順序穩定排列。order by 可以接受多個排序欄位,排序順序由左至右優先級逐步套用,並且每個欄位都可以指定 asc 或 desc。 在 GROUP BY 與 ORDER BY 的使用上有幾點重要原則必須注意。
任何出現在 Select 中但不是聚合函數的欄位,都必須出現在 group by 中,否則會出現錯誤。此外在 order by 中若要以聚合結果排序,需要直接使用聚合函數(例如 count(G)、sum(C)、avg(D))作為排序項目,不能使用在 Label 中指定的別名來排序(也就是不能用別名代替聚合函數)。若想用別名排序,可改用欄位位置或在外層再做一次 QUERY 或 SORT。 處理數值與文本的差異也是常見問題來源。例如若要依某欄位的數值大小排序,但該欄為文字型態(例如匯入 CSV 時數字被當成字串),排序結果會變成字典序而非數值大小。常見處理方法是在原始範圍或查詢前先將欄位轉為數字。
可以使用 ArrayFormula 和 N、VALUE 或乘以 1 的技巧將文本轉為數值,例如在資料範圍外建立輔助欄以 =ArrayFormula( VALUE(原欄位範圍) ),或在 QUERY 範圍中加入計算式以強制轉換。當然若來源是 IMPORTRANGE,也要注意在 IMPORTRANGE 之後執行轉換或在外層 QUERY 處理轉換。 若想只取得最高的前 N 筆資料,可以考慮以 Query 結合 limit,或使用 SORTN 函數。以 Query 寫法為例,在原始查詢後加上 limit 10 就能取得前十名: =query(Attendence!A:G, "Select A, B, C, E,count(G) where D <>'' and G ='Attended' group by A, B, C, E order by count(G) desc label count(G) 'Count of sessions attended'", 1) 再外層套一個 QUERY 或直接在同一條查詢字串後面接 limit,例如 ... order by count(G) desc limit 10。若你需要在遇到相同次數時取出更多策略(例如分群依照其他欄位決定),那就應在 order by 中補上後續排序欄位以確定排序結果為期望的穩定順序。 在實務資料上常見的另一個情況是使用 IMPORTRANGE 從不同表單拉入資料後再使用 QUERY。
IMPORTRANGE 在 QUERY 中使用時語句通常像這樣: =QUERY(IMPORTRANGE("試算表ID", "工作表名稱!A:G"), "select Col1, Col2, count(Col7) where Col4 <> '' and Col7 = 'Attended' group by Col1, Col2 order by count(Col7) desc", 1) 在這種情形中,經常需要注意 IMPORTRANGE 的授權步驟,第一次使用會跳出需要按一下允許存取的提示,否則 QUERY 會回傳錯誤或空白。當使用 Col1 等列號寫法時,請留意標頭列參數的設定。若標頭列數設定不正確,Query 會把標頭當成資料影響結果或群組計算。 針對資料中出現空白或 Null 的處理也很重要。若在 WHERE 條件中想排除空白,常見語法是 where D <> '' 或 where D is not null。但在有些情境中空白與空字串處理會不同,尤其是合併資料來源時,建議先檢查來源資料是否包含只由空格組成的值,可用 trim() 或在原欄位上先做清理。
遇到 Query 回傳錯誤的常見訊息之一是『Column X does not exist』或『Unable to parse query string for Function QUERY parameter 2: ...』。這通常來自於查詢字串的語法錯誤、引號不配對或 group by/ select 欄位不符合規則。當你在公式中要放雙引號做字串時,Google Sheets 公式本身也使用雙引號包裹整個查詢字串,所以字串內若需含有單引號(例如篩選 G = 'Attended'),那麼在查詢字串內使用單引號是最佳做法,避免在公式層面需要額外跳脫。若需要在字串內使用雙引號可以使用 CHAR(34) 或是用兩層替代,但通常使用單引號就足夠了。 對於想要把聚合欄位放到特定位置並以該欄位排序的需求,常見做法是直接在 Select 中指定聚合函數的位置,例如 Select A, B, count(G) ... 並在 order by 中使用 count(G) desc。若你想將聚合欄位放在視覺上更靠左或靠右,可以調整 Select 欄位順序。
例如若希望出席次數放在最左方,Select count(G), A, B ... 再在 order by 使用 count(G) desc。注意在 group by 中依然要列出非聚合欄位,且 group by 的順序並不影響排序,排序由 order by 決定。 有時候你可能會想用別名來讓表頭更具可讀性,例如 label count(G) '出席次數'。Label 子句只影響輸出欄位的標籤,不會影響排序。若你嘗試使用 label 定義的名稱在 order by 中排序,會發現無效或發生錯誤。正確做法是繼續在 order by 中使用聚合函數本身,或在外層再做一次 SELECT/SORT 並使用別名進行排序。
效能面向也是設計大型報表時不能忽視的。當資料集非常大時,複雜的 Where 條件、跨工作表的 IMPORTRANGE 或過多的聚合運算會使 QUERY 變慢。若遇到性能問題,可採取的方法包括先在來源表做預處理(例如減少不必要欄位、用輔助欄先計算是否符合篩選條件),或把 IMPORTRANGE 的資料暫存在一個靜態工作表中以避免每次重算都去拉外部資料。另一個方式是將 Query 拆成多層:內層先做必要的篩選與轉換,外層再做群組與排序,這樣有時可以讓引擎更有效率地處理運算。 在多語系與地區設定下排序也可能有微妙差異,例如字母排序可能會受語言環境影響。如果排序結果看起來不合預期,檢查試算表的地區設定與文字格式化設定是個好習慣。
日期欄位在 Query 中若不被當作日期處理,也會導致排序異常,確保日期欄位為正確的日期格式或在查詢前將其轉成可排序的數值時間戳。 最後總結實用範例重點:若你的目標是依某個聚合數值由最高到最低排序,最直接的做法是在查詢字串加上 order by 聚合函數 desc。若需要穩定的次級排序,在主排序後追加欄位並指定 asc 或 desc。若要限制結果數量,使用 limit。若使用 IMPORTRANGE,先確保授權與標頭列設定正確。遇到資料型態問題時先將文字型數字轉為數值。
善用 label 改善欄位名稱,但別試圖用 label 名在 order by 中排序。透過上述原則與範例,你可以把 QUERY 變成強大的資料彙整與排序工具,使報表自動化更可靠且可讀性更高。祝你在 Google Sheets 的資料處理與排序工作上更得心應手。 。
