QUERY 函式大解析(五):PIVOT 給你一雙透視眼

閱讀時間約 9 分鐘
終於邁入了 QUERY 系列的第五篇文章了!到這你可以說是很厲害的 QUERY 大師,能把一大筆資料玩弄在手掌心了。這次要介紹的是 QUERY 系列文的最後一個大語法:PIVOT,讓你可以更進一步切入分析 SELECT 出來的資訊。
在開始之前,我會建議你先看過前一篇 QUERY 函式大解析(四):進階 SELECT、GROUP BY,了解聚集函數(Sum、Avg、Count、Max、Min)和 GROUP BY 的使用規則再回來試做,會更有概念。
如果還沒看過 QUERY 是什麼,還不知道 QUERY 強大的威力在哪的話,我在方格子上寫了一系列使用 QUERY 的教學文章,也歡迎你來看看:

所以 PIVOT 是什麼?

PIVOT 原意是「扭轉」、「樞紐」、「中樞」的意思,你也可以看成一雙透視眼鏡,可以透過 PIVOT 看到聚集 SELECT 內的細部資訊,並且可以指定用某欄位來切割視角
PIVOT 的應用範圍也很廣,可以用它來做圖表、快速取得聚集資訊、效果跟資料透視表(pivot table)很接近!
這邊放上這次範例出現的試算表給大家複製,歡迎取用~
例如說我現在有這樣的一筆員工資料:
如果今天我想要知道 F 欄的所有公司裡,所有員工的平均薪資的話,我可以做:
=QUERY(A:F, "SELECT F, AVG(E) GROUP BY F")
我就會得到:
但如果今天還想再從部門、年齡區間切入,取得平均薪資(avg 薪水)的資訊,還可以怎麼做呢?這就是 PIVOT 可以進一步做的事了。
像是這是以部門的視角切入透視,得到的資訊:
再來,以年齡區間的視角切入透視,得到的的資訊:
是不是很像我們熟悉的資料透視表(pivot table)?馬上來看看怎麼做吧!

語法

PIVOT 的語法其實很單純,如下:
=QUERY(資料, "SELECT 聚集函數(##) PIVOT ####")
## 代表欄位字母。所以如果你想要以A欄的視角,透視 B 欄的總和,就會是:
=QUERY(資料, "SELECT SUM(B) PIVOT A")

以我們剛剛的員工資料表為例:
如果想知道各個公司的平均薪資,那就是:AVG(薪水)、PIVOT 公司。也就是:
=QUERY(A:F, "SELECT AVG(E) PIVOT F")
執行結果就會是:
其實執行結果跟 GROUP BY 差不多,只是表格方向有一點不太一樣就是了。跟 GROUP BY 的語法和執行結果比較一下:
=QUERY(A:F, "SELECT F, AVG(E) GROUP BY F")
其實計算結果相同,都 OK 的!如果你用了 PIVOT,比較喜歡把公司資訊放在一欄、平均薪水放在另一欄,你可以在 QUERY 前面都加一個 TRANSPOSE 函式,把行轉換成欄、欄轉換成行:
=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")
結果就會是!

最後小叮嚀

我們當然可以用其他的語法跟 PIVOT 的搜尋結果互動,例如再進一步用 WHERE 去限縮條件、用 LIMIT 指定輸出行數、用 ORDER BY、用 OFFSET 都可以。但是記得,順序要寫對!Google QUERY 對於語法的順序比較嚴格,必須要按照下面的順序來寫:
SELECT / WHERE / GROUP BY / PIVOT / ORDER BY / LIMIT / OFFSET / LABEL
如果不照上面的順序寫的話,QUERY 會直接跑不出結果!
以上就是 PIVOT 的教學了。QUERY 已經幾乎被我們看完囉!要下字母的時候往往會猶豫一下,但是對自己有耐心、照著思路慢慢想的話,應該都會沒問題的!
直接做資料透視表(Pivot Table)也是個好方法,但表太多的話,也會讓整個工作表的載入效率變低,這時候 PIVOT 就是個很方便的選擇!(灬ºωº灬)

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!
我是喜特先生,Mr. Sheet,我們下個教學見!
12.5K會員
147內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
發表第一個留言支持創作者!
喜特先生官方沙龍 的其他內容
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
除了 SELECT、WHERE 之外,今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家,讓你的 QUERY 更強大!
這是 QUERY 函式大解析系列文章的第二篇!我們要用 WHERE 語法來指定搜尋條件,要 QUERY 只回傳符合條件的資料。
QUERY,一個真的要隆重介紹的函式,真的很好用。一起從頭開始學這個強力的函式吧!
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
除了 SELECT、WHERE 之外,今天再介紹 ORDER BY、LIMIT、OFFSET 和 LABEL 給大家,讓你的 QUERY 更強大!
這是 QUERY 函式大解析系列文章的第二篇!我們要用 WHERE 語法來指定搜尋條件,要 QUERY 只回傳符合條件的資料。
QUERY,一個真的要隆重介紹的函式,真的很好用。一起從頭開始學這個強力的函式吧!
你可能也想看
Google News 追蹤
Thumbnail
這個秋,Chill 嗨嗨!穿搭美美去賞楓,裝備款款去露營⋯⋯你的秋天怎麼過?秋日 To Do List 等你分享! 秋季全站徵文,我們準備了五個創作主題,參賽還有機會獲得「火烤兩用鍋」,一起來看看如何參加吧~
Thumbnail
11/20日NVDA即將公布最新一期的財報, 今天Sell Side的分析師, 開始調高目標價, 市場的股價也開始反應, 未來一週NVDA將重新回到美股市場的焦點, 今天我們要分析NVDA Sell Side怎麼看待這次NVDA的財報預測, 以及實際上Buy Side的倉位及操作, 從
Thumbnail
Hi 大家好,我是Ethan😊 相近大家都知道保濕是皮膚保養中最基本,也是最重要的一步。無論是在畫室裡長時間對著畫布,還是在旅途中面對各種氣候變化,保持皮膚的水分平衡對我來說至關重要。保濕化妝水不僅能迅速為皮膚補水,還能提升後續保養品的吸收效率。 曾經,我的保養程序簡單到只包括清潔和隨意上乳液
Thumbnail
條件資料行是POWER QUERY裡面一個可以設定指定條件,進而判斷符合條件的資料產生指定的內容,就跟EXCEL中的IF函數很像。 但是POWER QUERY的條件資料行,他是利用對話式的視窗,所以不需要自己動手寫函數,就可以順利達到相同的功能。 今天就用1個資料3個題目來學習這個功能
Thumbnail
日前在LINE社群,有網友提出一個問題,要把資料進行分析,用日期來計算出將對應的資料。 原始資料,密密麻麻的數據,都看不清楚了 放大一點點 要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。 有興趣可以下載試著挑戰看看:檔案下載 作法有很多種,當然也可以用函數處
Thumbnail
網友提問的一個問題,這個問題其實也蠻常見,資料中相同日期的項目全部合併到同一個儲存格中,來簡化資料的顯示。 這邊分享三種作法,通用的函數法、365函數法、POWER QUERY法 💡通用函數法 通用版本的函數需要輔助欄才可以達成這個需求 輔助欄C2=VLOOKUP(A2,A2:B10,
Media query可以很複雜,但是這裡KP只談最簡單、最實用的基礎。
Thumbnail
粉絲提問需求 儲存格中這一段字串:baseccy=usd,baseccyamoun=1300,type=short} 要如何把字串中1300的數字給抓出來,其實方式蠻多的,今天來介紹3種方式 第一種剖析法: 先將資料選取出來之後利用資料剖析,因為資料的規則數字是在=之後與逗號之前,所以可以先將逗號剖
Thumbnail
不得不說仁川機場的機場本體建築設計很有設計感,大韓航空跟韓亞航的主場都在仁川機場,尤其是大韓航空KOREAN AIR,在其官網上寫著韓國第一的航空公司,多麼自信的口號,讓人想像著充滿設計感的韓國第一的航空公司貴賓室應該有一些驚艷旅客的設計吧。實際上,到了貴賓室的感覺是驚嚇,因為好像來到東南亞的某個不
Thumbnail
如果你正在學習SQL,這邊介紹一個方便簡易的方式,不須透過安裝傳統的MySQL,只要利用Google提供的BigQuery開源示範dataset就可以實作練習,另一方面,如果你想應用自己手邊上的資料,也能簡單的使用匯入功能(如何匯入看這邊)。 輸入名稱「data-to-insights」。
Thumbnail
在【刁民國】一定要買房,不然就會變成「社會邊緣倫」,與財富重分配絕緣,而且你一定要買「會賺錢」的房子,那種拿來【遮風避雨】的房子,求求你千萬不要買,不如租房子,剩下錢出國旅遊,充實自己總比住在【財務地獄】好。 在這個【刁民寶島】,拿房地產來抵抗通貨膨脹?~就你腦袋進水信這套~有錢人當然為了賺大錢。
Thumbnail
又到了愉悅的蕾蕾週五影院 而且是連續假期的週五,真是太令人開心了👏🎉 週五電影-旅遊篇暫時告一段落 上週看完金鐘獎,決定要來開啟台灣篇 好好的介紹一下台灣優秀的影劇們 (Netflix前三都是韓片有點Sad🥲)   第一部就獻給本次得獎的大債時代。 Netflix 上面有哦  
Thumbnail
※劇透 這篇文章盡可能解析女主角昭容王妃、男主角哲宗皇帝以及男神張奉煥三人的角色關係與變化,內容較多還請大家耐心看完。情感的導師、扶持的戰友、陪伴的摯友、曖昧的情人、牽掛的靈魂,這些身分都是在《哲仁王后》中一位身兼多職的角色,沒錯,就是....
Thumbnail
這個秋,Chill 嗨嗨!穿搭美美去賞楓,裝備款款去露營⋯⋯你的秋天怎麼過?秋日 To Do List 等你分享! 秋季全站徵文,我們準備了五個創作主題,參賽還有機會獲得「火烤兩用鍋」,一起來看看如何參加吧~
Thumbnail
11/20日NVDA即將公布最新一期的財報, 今天Sell Side的分析師, 開始調高目標價, 市場的股價也開始反應, 未來一週NVDA將重新回到美股市場的焦點, 今天我們要分析NVDA Sell Side怎麼看待這次NVDA的財報預測, 以及實際上Buy Side的倉位及操作, 從
Thumbnail
Hi 大家好,我是Ethan😊 相近大家都知道保濕是皮膚保養中最基本,也是最重要的一步。無論是在畫室裡長時間對著畫布,還是在旅途中面對各種氣候變化,保持皮膚的水分平衡對我來說至關重要。保濕化妝水不僅能迅速為皮膚補水,還能提升後續保養品的吸收效率。 曾經,我的保養程序簡單到只包括清潔和隨意上乳液
Thumbnail
條件資料行是POWER QUERY裡面一個可以設定指定條件,進而判斷符合條件的資料產生指定的內容,就跟EXCEL中的IF函數很像。 但是POWER QUERY的條件資料行,他是利用對話式的視窗,所以不需要自己動手寫函數,就可以順利達到相同的功能。 今天就用1個資料3個題目來學習這個功能
Thumbnail
日前在LINE社群,有網友提出一個問題,要把資料進行分析,用日期來計算出將對應的資料。 原始資料,密密麻麻的數據,都看不清楚了 放大一點點 要把這些資料不同『料號』的各種『狀態』依據『日期』進行分析。 有興趣可以下載試著挑戰看看:檔案下載 作法有很多種,當然也可以用函數處
Thumbnail
網友提問的一個問題,這個問題其實也蠻常見,資料中相同日期的項目全部合併到同一個儲存格中,來簡化資料的顯示。 這邊分享三種作法,通用的函數法、365函數法、POWER QUERY法 💡通用函數法 通用版本的函數需要輔助欄才可以達成這個需求 輔助欄C2=VLOOKUP(A2,A2:B10,
Media query可以很複雜,但是這裡KP只談最簡單、最實用的基礎。
Thumbnail
粉絲提問需求 儲存格中這一段字串:baseccy=usd,baseccyamoun=1300,type=short} 要如何把字串中1300的數字給抓出來,其實方式蠻多的,今天來介紹3種方式 第一種剖析法: 先將資料選取出來之後利用資料剖析,因為資料的規則數字是在=之後與逗號之前,所以可以先將逗號剖
Thumbnail
不得不說仁川機場的機場本體建築設計很有設計感,大韓航空跟韓亞航的主場都在仁川機場,尤其是大韓航空KOREAN AIR,在其官網上寫著韓國第一的航空公司,多麼自信的口號,讓人想像著充滿設計感的韓國第一的航空公司貴賓室應該有一些驚艷旅客的設計吧。實際上,到了貴賓室的感覺是驚嚇,因為好像來到東南亞的某個不
Thumbnail
如果你正在學習SQL,這邊介紹一個方便簡易的方式,不須透過安裝傳統的MySQL,只要利用Google提供的BigQuery開源示範dataset就可以實作練習,另一方面,如果你想應用自己手邊上的資料,也能簡單的使用匯入功能(如何匯入看這邊)。 輸入名稱「data-to-insights」。
Thumbnail
在【刁民國】一定要買房,不然就會變成「社會邊緣倫」,與財富重分配絕緣,而且你一定要買「會賺錢」的房子,那種拿來【遮風避雨】的房子,求求你千萬不要買,不如租房子,剩下錢出國旅遊,充實自己總比住在【財務地獄】好。 在這個【刁民寶島】,拿房地產來抵抗通貨膨脹?~就你腦袋進水信這套~有錢人當然為了賺大錢。
Thumbnail
又到了愉悅的蕾蕾週五影院 而且是連續假期的週五,真是太令人開心了👏🎉 週五電影-旅遊篇暫時告一段落 上週看完金鐘獎,決定要來開啟台灣篇 好好的介紹一下台灣優秀的影劇們 (Netflix前三都是韓片有點Sad🥲)   第一部就獻給本次得獎的大債時代。 Netflix 上面有哦  
Thumbnail
※劇透 這篇文章盡可能解析女主角昭容王妃、男主角哲宗皇帝以及男神張奉煥三人的角色關係與變化,內容較多還請大家耐心看完。情感的導師、扶持的戰友、陪伴的摯友、曖昧的情人、牽掛的靈魂,這些身分都是在《哲仁王后》中一位身兼多職的角色,沒錯,就是....