為什麼我超推薦學習 Google Sheet Query Function (函數)?
- 學習 Query,可以減少公式的行數,省時又方便
- QUERY 可以完全取代 UNIQUE 函數 + SUMIF/COUNTIF 函數
- QUERY 甚至也可以完全取代 Pivot Table
- Google Sheet Query 是 SQL 的入門版,學會 Query,SQL 就先會一半
- 如果可以在 Google Sheet 裡面就練習 SQL 的思維方式,對於資料處理會有很大的進階
- 報表的公式有修改,就要重新下拉,超麻煩!有 Google Sheet Query 後,不用再下拉公式
- 如果你認識 Google Sheet Query 或是 Google Sheet 裡面的 Arrayformula,他可以完全改變你對 Google Sheet 的想法!如果你也有遇到報表的數字新增,公式就要不斷下拉,那 Query 可以協助不用再來下拉公式
什麼是 Google Sheet Query Function (函數)?
- 可以引用資料來源
- 可以 aggregate data, rearrange data, create new data, filter data, sort data
- Query 裡面包含 10 幾種函數可以寫,就像在 Google Sheet 裡面寫 SQL 一樣,更方便了
Google Sheet Query 有哪些函數可以寫?
Google Sheet Query 公式的組成
- 資料範圍:在下面的範例以 data_range 顯示,以 ‘ ‘ 單引號選取資料範圍
- 接著就是下 Query 的語法:在下面的範例以 google_sheet_query 顯示,以 “ “ 雙引號將公式包起來
= QUERY('data_range'!A:A, "google_sheet_query")
Google Sheet Query 支援哪些資料格式?
- String 文字格式:以 ‘ ‘ 單引號的方式顯示為文字
- Numbers 數字格式:直接輸入數值就可以了
- Time/Date 時間或是日期格式:可以將時間轉為數值,或是以 date’2020-01-01′ 或是 TEXT(N12,”yyyy-mm-dd”) 的形式處理
如果想要看更多實例轉換,可以參考:Google Sheet Query Where 條件教學
4 大基本用法:SELECT, WHERE, GROUP BY, ORDER BY
- SELECT
- select A,E:直接將要選取的欄位打上來
=query('RAW Data'!A:P,"select A,E")
- select *:選取資料範圍中,全部的欄位
=query('RAW Data'!A:P,"select *")
- WHERE
- select A,E Where E <= 20:選取 A, E 兩欄,並且當 E 欄 ≤ 20,如果你需要的 Where 條件很複雜,可以到 Google Sheet Query Where 條件教學 這邊看更多 Where 條件設置的進階用法
=query('RAW Data'!A:P,"select A,E Where E <= 20")
- GROUP BY
- 只要是你想要做敘述性統計,例如+-×÷,或是平均 avg()、最小 min()、最大 max(),最後都要用 GROUP BY 這個函數做「聚合」,需要聚合的資料就是除了敘述性統計以外的其他行
- 以下的範例:我選取 B, C, K 這三行,然後把 L 加總,把 P 平均,所以要對 B, C, K 這三行進行聚合,也就是 GROUP BY
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K")
- ORDER BY
- ORDER BY 就是排序,看我們想要依照哪一行做排列
- ASC 就是從小到大,DESC 就是從大到小
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) asc")
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) desc")
樞紐分析:Pivot (資料透視表)
- 如果想要在 Google Sheet 裡面做樞紐分析表,Query 也可以做到:樞紐分析會分成三個部分:列、欄,還有樞紐裡面的值
- 舉例:如果樞紐分析裡的列是 C 欄,欄是 G 欄,樞紐分析的值是 L (加總),那 Query 裡的樞紐分析就是:SELECT C,sum(L) GROUP BY C PIVOT G
=QUERY('RAW Data'!A:P,"select C,sum(L) GROUP BY C PIVOT G")
回傳限制:LIMIT、OFFSET
- LIMIT
- LIMIT 是用來控制資料的行數,如果只想回傳 10 行,可以 LIMIT 10
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) desc limit 10")
- OFFSET
- 跳過資料範圍裡的 N 行之後,再執行 Google Sheet Query 函數,所以如果我寫 OFFSET 10,那就會跳過除了表頭以外的 10 行,然後回傳。所以前 10 行的值都不會顯示
=QUERY('RAW Data'!A:P,"select * Offset 10")
調整格式:LABEL, FORMAT
- LABEL
- LABEL 可以幫 Query 後的結果,重新取表頭名稱,如果你的 RAW DATA 是英文表頭,那 QUERY 輸出之後,可以換成中文表頭,以方便閱讀囉!
=QUERY('RAW Data'!A:P,"select A, B label A '姓名', B '國家'")
- FORMAT
- FORMAT 可以用來調整 Query 之後的結果,是以怎麼樣的「格式」呈現
- FORMAT 最常用來格式化日期、時間、數值這類的呈現格式
- 日期:FORMAT C ‘DD-MM-YYYY’,或是: FORMAT C ‘YYYY’、FORMAT C ‘MM’、FORMAT C ‘DD’
- 時間:FORMAT G ‘HH’,或是 FORMAT G ‘MM:SS’
- 數值:FORMAT E ‘##,##0.00’
=query('RAW Data'!A:P,"select K format K 'dd-mm-yyyy'")
=query('RAW Data'!A:P,"select K format K 'HH:MM:SS'")
=query('RAW Data'!A:P,"select L format L '##,##0.00'")
Google Sheet Query 函數先後順序
- 如果你也有疑問,那哪些函數要先寫,哪些要後寫?以下是 Google Sheet Query 函數在寫的時候的先後順序:SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT
- 排序必須照著這個寫!以免函數不能運轉喔!
- 這個順序主要是數據在整理跟運算的時候的先後處理順序,這裡來解釋一下數據處理的順序:選取資料 (SELECT)、篩選條件 (WHERE)、聚合 (GROUP BY)、樞紐 (PIVOT),接著排列結果的順序 (ORDER BY)、只回傳某幾行或是從某幾行開始才回傳 (LIMIT & OFFSET),接著格式化表頭及格式化值 (LABEL & FORMAT)
總結
Google Sheet Query 整體的用法就分為這 9 個函數語法 (9 useful google sheet query functions),我覺得最值得深學的就是 WHERE 這個語法,你可以到 Google Sheet Query Where 條件教學 這邊,看更多教學
如何練習 Google Sheet Query 函數?
- 複製一份我的 Google Sheet 教學文件
- 在我整理好的幾個工作表中,練習這 9 個語法
更多學習資源推薦
- Google 官方教學:Getting Started with Google Sheets
- Lisa 的超白話實用教學:Lisa 的 Google Sheet 公式教學
- Google Data Analytics Professional Certificate:Google 認證的資料分析師證照課程有幫助嗎? 誰最適合上?可以參考我的學習心得,證照班適合想要全方位學習數據分析工具跟技巧的人!
如何使用這個網站?我的數據分析文章導覽!裡面也會分享更多實用 Google Sheet 教學:本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學
Pingback: [數據分析#1] Google Sheet Query 教學 - Lisa Wu 財富自由之路
Pingback: [數據分析#0] 數據分析文章導覽 - Lisa Wu 財富自由之路
Pingback: [數據分析#38] Google 試算表:一次學完 12 個基礎功能,一起早點下班! - Lisa Wu 財富自由之路