QUERY 函式大解析（五）：PIVOT 給你一雙透視眼

所以 PIVOT 是什麼？

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

語法

=QUERY(資料, "SELECT 聚集函數(##) PIVOT ####")

=QUERY(資料, "SELECT SUM(B) PIVOT A")

=QUERY(A:F, "SELECT AVG(E) PIVOT F")

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

=TRANSPOSE(QUERY(A:F, "SELECT AVG(E) PIVOT F"))

對聚集函數透視！

接下來就是本篇的重點：對聚集函數（Sum、Avg、Count、Max、Min）做透視。其實語法也很單純，就是要記得把 PIVOT 放在 GROUP BY 後面就是了。

=QUERY(A:F,"SELECT 首欄 ##, AGG(##), ... GROUP BY ## PIVOT ###")

（AGG(##) 代表聚集函數，請自由代換；# 代表欄位字母）

這邊欄位字母變多了，該要填什麼才好呢？先釐清一下各個語法的欄位意義到底是什麼吧。假如手上有一份資料長這樣：

你可以用班級或是性別來作為切入視角，去透視期末考總分的不同樣貌。那麼：

• 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。填這個會顯示出 GROUP BY 分組的標題和分組內容，如果不寫的話，就會沒有標題。
• AGG(##)：你要執行聚集函數（SUM、AVG、COUNT、MAX、MIN）。你的資料的 C 欄有某考試的總分，那你就可以用 AVG (C) 取得 C 欄的平均，也就是某考試的平均分數。
• GROUP BY ##：你希望你的聚集函數結果以「##」為分組、且組別會出現在「同一欄」上的意思。我想要看到 C 欄某考試的全校平均分數、還要看到各個班（B 欄）的班平均，那就會用 GROUP BY B 來分組。 順帶一提，第一個 ## 的部分也會填 B 喔！也就是 B, AVG (C) GROUP BY B。
• PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為視角切入來查詢資訊，且這視角的名稱會出現在「同一列」上。再接著上面的例子，我想要知道各個班級內男女之間的平均總分數，所以我的角度就是 D 欄的性別了，所以我就會寫 PIVOT D。

簡單說，GROUP BY 的資料會出現在同一欄上、PIVOT 則是同一列上。

我們回到這篇文章開頭的例子，再附上要查詢的 QUERY 的資料：

假設我們今天要找的是，各公司裡各部門的平均薪資。如果你覺得有點難判斷要下什麼欄位字母，我們按照剛剛的思路走：

• 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。 ➡️ 嗯，還不知道，先空著。
• AGG(##)：執行聚集函數。 ➡️ 我要找平均薪資，所以就是 AVG(E)。
• GROUP BY ##：聚集函數結果以「##」為分組，組別在同一欄。 ➡️ 我想要以公司作為分組，所以是 GROUP BY F。 ➡️ 所以首欄就是 F。
• PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為角度切入來查詢資訊，且這視角會出現在「同一列」上。 ➡️ 我要進一步用部門來切入取得資訊，所以是 PIVOT B。

用神秘拼裝魔法湊起來就會得到：

=QUERY(A:F,"SELECT F, AVG(E) GROUP BY F PIVOT B")

結果如下：

搞定！⎝( OωO)⎠

試試看第二個角度，用年齡區間來切入查詢資訊吧：

要找的是，各個公司裡各個年齡區間的平均薪資：

• 首欄 ##：GROUP BY 的標題欄位，GROUP BY 寫什麼欄位這邊就寫什麼。 ➡️ 嗯，還不知道，還是先空著。
• AGG(##)：執行總和、平均、含有資料的儲存格個數、最大值、最小值的欄位。 ➡️ 我要找平均薪資，所以就是 AVG(E)。
• GROUP BY ##：聚集函數結果以「##」為分組，組別在同一欄。 ➡️ 我想要以公司作為分組，所以是 GROUP BY F。 ➡️ 所以首欄就是 F。
• PIVOT ##：在 GROUP BY 的組別裡面，再用「##」為角度切入來查詢資訊，且這視角會出現在「同一列」上。 ➡️ 我要進一步用年齡層區間來切入取得資訊，所以是 PIVOT D。

拼拼裝裝就會得到這個語法：

=QUERY(A:F,"SELECT F, AVG(E) GROUP BY F PIVOT D")

結果就會是！

---

最後小叮嚀

如果你喜歡這次的文章，歡迎你透過這些方法支持我：

・按下愛心、按下儲存

・留言告訴我你的想法

・加入喜特先生的官方沙龍，即時看到我發布的教學

・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案

・追蹤喜特先生的 Facebook

・按這邊小額贊助我的創作！