QUERY 函式大解析（四）：進階 SELECT、GROUP BY

更新於 2024/04/06發佈於 2021/08/01閱讀時間約 9 分鐘

今天要介紹的是進階的 SELECT 功能，可以即時對 QUERY 的結果運算，迅速取得數值的平均、總和、最大值、最小值和數量，省去拉資料透視表（pivot table）的麻煩！

如果還沒看過 QUERY 是什麼，還不知道 QUERY 強大的威力在哪的話，我在方格子上寫了一系列使用 QUERY 的教學文章，也歡迎你來看看：

先來複習一下 SELECT 的語法：

=QUERY(資料, "SELECT A, B, C, D... ")

在 SELECT 後面要寫上想要回傳的欄位字母，如 A、B、C、D 等。你也可以視狀況安排順序，也可以是 B、A、D、C，看你需求而定。

也順便複習一下 WHERE：

=QUERY(資料, "SELECT A, B, C, D WHERE B = '台灣'")

WHERE 會寫在 SELECT 後面，限縮回傳結果，只回傳符合條件的資料。這個例子就是只有當資料的 B 欄是「台灣」的時候，回傳欄 A、欄 B、欄 C、欄 D 的意思。

我把這次示範的 Google 試算表放在這邊，歡迎你複製一份，跟著一起做唷！

進階 SELECT：聚集函數

來瞧瞧我們的本日主軸！我們有 SUM()、AVG()、COUNT()、MAX()、MIN() 這五個功能可以用。這些指令後面都跟著一對小括號 ()，我們要在括號裡面填入想要做運算的欄位字母，像這樣：

=QUERY(資料, "SELECT SUM(A)...")
=QUERY(資料, "SELECT AVG(B)...")
=QUERY(資料, "SELECT COUNT(C)...")
=QUERY(資料, "SELECT MAX(D)...")
=QUERY(資料, "SELECT MIN(D)...")

來快速了解一下這些函式的定義：

SUM()：回傳指定欄位內所有數字的總和。
AVG()：回傳指定欄位內所有數字的平均值。
COUNT()：回傳指定欄位內所有含有資料的儲存格數量。
MAX()：回傳指定欄位內最大值的資料。
MIN()：回傳指定欄位內最小值的資料。

上面這五個東西，我們在這篇文章就叫姑且叫做「聚集函數（aggregate function）」！來看看範例：

上圖是某三個小吃店賣的菜跟價格。我們可以用上面提到的這些語法來求得整張表價格（Ｃ欄）的總和、平均、資料數、最貴的價格和最便宜的價格。用 QUERY 來呈現就是：

=QUERY(A:C, "SELECT SUM(C)")
=QUERY(A:C, "SELECT AVG(C)")
=QUERY(A:C, "SELECT COUNT(C)")
=QUERY(A:C, "SELECT MAX(C)")
=QUERY(A:C, "SELECT MIN(C)")

就會得到以下結果：

...欸不是啊，這個我不用 QUERY 也做得出來吧！

沒錯，我也這麼覺得（笑）這些交給一般的 SUM、AVERAGE、COUNT、MAX、MIN 就可以了。但是！如果今天想要快速查詢這些東西的時候：

「各家店賣的項目」的價格總和、價格平均、數量、價格最大值、價格最小值
「各個品項」的價格總和、價格平均、數量、價格最大值、價格最小值

我們在 Excel 可能會用到樞紐分析表、 Google Spreadsheet 的話會用到資料透視表，指定欄位跟價格。效果會像這樣：

雖然這樣也可以，但是比起資料透視表那樣滑鼠猛點、設定東西，其實還有更進階、更帥氣、更便捷的方法！

隆重介紹 ––––

GROUP BY...

顧名思義，GROUP BY... 就是「把資料依～分組」的意思。在上面的資料透視表裡面，我們把資料按照小吃店分組，列出各個小吃店的項目總額加總起來；換作 GROUP BY 的想法，你可以看成「GROUP BY 小吃店」，也就是「GROUP BY A 欄」。那麼，在 QUERY 的語法裡，我們會這樣寫：

=QUERY(A:C, "SELECT A, SUM(C) GROUP BY A")

翻譯成中文就是「輸出 A 欄、C 欄的總和，並且把結果以 A 來分組」。就會得到以下結果：

只寫一行指令！把製作資料透視表的時間都省起來了！帥氣！

至於執行結果的排序是由 GROUP BY 的欄位來決定的，預設是遞增排序唷！但可惜目前 QUERY 不太認識中文，沒辦法完全以我們習慣的注音符號排序、拼音排序、筆劃等等方式排序。但如果你的資料是英文或數字的話，應該會好好從 A 到 Z、或是由小到大的方式排好。

那我們這次來試試看如果用「品項」（B 欄）分組。我想要求的是依照品項分組、得到各品項的平均價格，就會是：

=QUERY(A:C, "SELECT B, AVG(C) GROUP BY B")

注意，因為我想要顯示各個品項 B 欄的結果，這邊 SELECT 已經不會是 A 欄的小吃店，而該改成 B 欄的品項了！那麼我們就會得到：

太棒了，搞定！你也當然可以對搜尋結果做 WHERE 的篩選，以及 GROUP BY 的排序。我們再看看幾個例子：

=QUERY(A:C, "SELECT A, SUM(C) WHERE A = '甲' GROUP BY A")

選擇 A 欄、C 欄的總和，只出現「甲」店小吃店的結果，並且以小吃店店家做分類：

=QUERY(A:C, 
      "SELECT B, AVG(C)
       WHERE B = '炒飯' OR B = '炒麵'
       GROUP BY B")

選擇 B 欄、C 欄的平均，只出現品項是炒飯或炒麵的結果，並且以品項來做分類：

最後的美中不足：沒有 HAVING！

雖然有 WHERE 可以限制某欄位的特定條件，回傳篩選過後的結果，但如果我想要限制某個聚集函數（剛剛看到的 SUM()、AVG()、COUNT()、MAX()、MIN()），例如「只回傳價格平均超過 70 塊的品項」、「只回傳價格最大值小於 60 塊的小吃店」，這樣的特定條件，有辦法做到嗎？

嗚，不行 (ﾉω･､)...

沒錯，截至目前為止（2021 年 8 月），Google 的 Query 目前還不支援聚合函數的條件查詢（這就是 SQL 的 HAVING）！目前在江湖廣為人知的變通方法就是巢狀 QUERY：先用一層 QUERY 包覆起來，對第一次的 QUERY 進行搜尋，並用 WHERE 限制條件。

上個範例好了！我們想要找的資訊是：

小吃店各品項的平均價格
並以小吃店作為分組
只回傳平均價格大於 60 的小吃店（←聚合函數的條件查詢）

截至前兩點，我們可以用剛剛學到的 AVG() 和 GROUP BY 來寫出：

=QUERY(A:C, "SELECT A, AVG(C) GROUP BY A")

就會得到：

但如果我們只要回傳平均價格大於 60 的店，就得用第二個 QUERY 包在外面，用 WHERE 回傳「avg 價格 > 60」的方式來篩選出最後結果。大致上是這個概念（外包的 QUERY 以粗體顯示）：

=QUERY
　　(QUERY
　　　　(A:C, "SELECT A, AVG(C) GROUP BY A"),
　　"SELECT * WHERE Col2 > 60")

結果放這邊：

由於內層 QUERY 的結果欄沒辦法以欄位字母顯示，只能用 Col2（代表第二欄）這個關鍵字進行搜尋。是不是有點小麻煩？我也覺得！還是希望 Google 可以早日推出 HAVING，節省節省大家時間 ԅ(º﹃ºԅ)

資料透視表（Pivot table）或許也很方便，但如果要爬取大量的資料、或在 Google 試算表上面已經有太多的資料透視表正在使用中了，再加上一個表都有可能成為負擔。這時，用 QUERY 做進階 SELECT 和 GROUP BY 不僅有幾乎相同的功效，執行效率也更好。

如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！

想要看更多文章，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！

我是喜特先生，Mr. Sheet，我們下個教學見！

喜特先生官方沙龍Google 試算表教學站🛠️｜函式用法｜

喜特先生官方沙龍

14.0K會員

147內容數

簡潔，快速，有效，讓你的日常生活、工作生產力大提升！＿＿＿快按「加入」，馬上追蹤所有喜特先生的更新，有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱，還有 Google 試算表疑難雜症的解題分享唷！💪

留言0

查看全部

發表第一個留言支持創作者！

喜特先生官方沙龍的其他內容

QUERY 函式大解析（三）：ORDER BY、LIMIT、OFFSET、LABEL

除了 SELECT、WHERE 之外，今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家，讓你的 QUERY 更強大！

#教學 #喜特先生 #Spreadsheets

QUERY 函式大解析（二）：WHERE

這是 QUERY 函式大解析系列文章的第二篇！我們要用 WHERE 語法來指定搜尋條件，要 QUERY 只回傳符合條件的資料。

#教學 #喜特先生 #Spreadsheets

QUERY 函式大解析（一）：基本原理與 SELECT

QUERY，一個真的要隆重介紹的函式，真的很好用。一起從頭開始學這個強力的函式吧！

#教學 #喜特先生 #Spreadsheets

QUERY 函式大解析（三）：ORDER BY、LIMIT、OFFSET、LABEL

除了 SELECT、WHERE 之外，今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家，讓你的 QUERY 更強大！

#教學 #喜特先生 #Spreadsheets

QUERY 函式大解析（二）：WHERE

這是 QUERY 函式大解析系列文章的第二篇！我們要用 WHERE 語法來指定搜尋條件，要 QUERY 只回傳符合條件的資料。

#教學 #喜特先生 #Spreadsheets

QUERY 函式大解析（一）：基本原理與 SELECT

QUERY，一個真的要隆重介紹的函式，真的很好用。一起從頭開始學這個強力的函式吧！

#教學 #喜特先生 #Spreadsheets

你可能也想看

Google News 追蹤

VK科技閱讀時間

2024/12/17

美股定期定額從國泰世華 CUBE App 輕鬆開始

*合作聲明與警語：本文係由國泰世華銀行邀稿。證券服務係由國泰世華銀行辦理共同行銷證券經紀開戶業務，定期定額(股)服務由國泰綜合證券提供。剛出社會的時候，很常在各種 Podcast 或 YouTube 甚至是在朋友間聊天，都會聽到各種市場動態、理財話題，像是：聯準會降息或是近期哪些科

#國泰世華 #美股 #定期定額

效率基地

2024/04/24

#12 從0到1的POWER QUERY | 條件資料行

條件資料行是POWER QUERY裡面一個可以設定指定條件，進而判斷符合條件的資料產生指定的內容，就跟EXCEL中的IF函數很像。但是POWER QUERY的條件資料行，他是利用對話式的視窗，所以不需要自己動手寫函數，就可以順利達到相同的功能。今天就用1個資料3個題目來學習這個功能

#EXCEL #效率職人 #POWERQUERY

效率基地

2024/03/06

POWER QUERY實戰 | PQ資料清理 ※ 樞紐資料分析 | 粉絲解題系列

日前在LINE社群，有網友提出一個問題，要把資料進行分析，用日期來計算出將對應的資料。原始資料，密密麻麻的數據，都看不清楚了放大一點點要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。有興趣可以下載試著挑戰看看:檔案下載作法有很多種，當然也可以用函數處

#粉絲PQ #PQ教學 #EXCEL

效率基地

2023/10/10

EXCEL如何將相同分類的內容合併到同一個儲存格內(函數法、POWER QUERY法)

網友提問的一個問題，這個問題其實也蠻常見，資料中相同日期的項目全部合併到同一個儲存格中，來簡化資料的顯示。這邊分享三種作法，通用的函數法、365函數法、POWER QUERY法 💡通用函數法通用版本的函數需要輔助欄才可以達成這個需求輔助欄C2=VLOOKUP(A2,A2:B10,

#EXCEL #效率職人 #粉絲PQ

Shang的前端開發筆記

2023/09/20

Media query最簡單的基礎-跟著KP用21天征服Responsive Layout-W3

Media query可以很複雜，但是這裡KP只談最簡單、最實用的基礎。

#CSS

效率基地

2023/06/28

使用POWER QUERY輕鬆擷取儲存格中的數字

粉絲提問需求儲存格中這一段字串:baseccy=usd,baseccyamoun=1300,type=short} 要如何把字串中1300的數字給抓出來，其實方式蠻多的，今天來介紹3種方式第一種剖析法: 先將資料選取出來之後利用資料剖析，因為資料的規則數字是在=之後與逗號之前，所以可以先將逗號剖

#EXCEL #PQ粉絲 #POWERQUERY

各種生活事的沙龍

2023/02/03

KOREAN AIR 大韓航空貴賓室 ICN JFK Lounge

不得不說仁川機場的機場本體建築設計很有設計感，大韓航空跟韓亞航的主場都在仁川機場，尤其是大韓航空KOREAN AIR，在其官網上寫著韓國第一的航空公司，多麼自信的口號，讓人想像著充滿設計感的韓國第一的航空公司貴賓室應該有一些驚艷旅客的設計吧。實際上，到了貴賓室的感覺是驚嚇，因為好像來到東南亞的某個不