從 Excel 的時代學會用公式處理 Raw data 後，還不知道 google sheet 有多神，自從學會用 google sheet Query 之後，一個公式就可以做完資料處理．完全不用再用有格式限制的 pivot 或是一格一格寫 sumifs, countifs 這種多條件下的數學計算，Query 學會之後，資料處理的速度可以加速非常多，本篇有附上測試資料在 google sheet 上，歡迎大家取用，一起來看看 Google sheet Query 教學吧！

Google sheet Query 重點整理

• Google sheet Query：選取資料範圍，學會 Select(選取欄位), Where(篩選), group by(聚合), order by(排序)
• 知道什麼情境下 pivot table 不好用

Google sheet Query 實際操作

假設我有一份資料，裡面包含 LionDelivery 這家公司，一月到三月在台灣各城市所有外送員上線的時數、完成的訂單數、所花的時間跟訂單評分，LionDelivery – Operation RAW Data，之後的公式學習可以用這份原始資料做練習。

Google sheet query 的公式組成

Query 由兩段指令組合，第一段指令可以指定要分析的資料範圍，第二段指令可以指定要選取哪些欄位來做資料分析。第二段指令又分為：Select(選取欄位), Where(篩選), group by(聚合), order by(排序)，跟 SQL 的用法很相似！接著我們立刻來實作一下。

Google sheet query 公式細節&實作

1. 如何快速統計一月各城市的營運指標 (外送員上線的時數、完成的訂單數、所花的時間跟訂單評分) 請打開範例一

=QUERY('RAW Data'!A:P,
"select B, C, K, sum(L), sum(M), sum(N),avg(O),avg(P) 
where K= date '" &TEXT("2020-01-01","yyyy-mm-dd")&
"' group by B, C, K order by sum(M) desc")
 

Google sheet query 指令一

• ‘RAW Data’!A:P：第一段指令要指定準備分析的資料範圍，也就是 RAW Data 中的 A 到 P 欄
• Tips: 這邊如果你想要引用其他張 google sheet 的 table，可以使用 importrange 來引用

Google sheet query 指令二

• 第二段指令就是比較複雜的資料分群統計，我們分成幾個部分講解

Google sheet query: Select

• Select (選取欄位)：select B, C, K, sum(L), sum(M), sum(N),avg(O),avg(P)，請把原始資料中要聚合的類別選取出來，今天我們要統計「台灣」、「各城市」、「一月」的敘述統計指標，也就是 RAW Data 中 B, C, K 欄
• 接著我們要做「敘述性統計」加總外送員上線的時數、加總訂單數、加總完成的訂單數、平均訂單評分、平均一單的外送時間，也就是 sum(L), sum(M), sum(N), avg(O), avg(P) 這些欄位

Google sheet query: Where

• Where(篩選)：RAW Data 現在包含一到三月的數字，如果要縮限範圍只要分析一月的話，可以在 where 篩選日期，where K = date ‘”&TEXT(“2020-01-01″,”yyyy-mm-dd”)&”‘，需要代換的地方就是日期的部分
• 大家也可以篩選城市或是其他月份的時間玩玩看，如果要篩選城市可以用 where C = ‘Taipei’，字串用 ‘ ‘ 包起來

#引用數值

• 引用數值是最簡單的，不用考慮引號，直接 E = 引用數值就可以

--引用數值
=query('RAW Data'!A:P,"select A,E Where E <= 20")
--引用儲存格裡的數值
=query('RAW Data'!A:P,"select A,E Where E <=" &D12)
 

#引用文字

• 使用單引號前後包住文字：C = ‘文字’ 

--引用文字
=QUERY('RAW Data'!A:P,"select C,sum(L) where C='Taipei' or C='Taichung' group by C") 
--引用儲存格裡的文字
=QUERY('RAW Data'!A:P,"select C,sum(L) where C='" & X11 &"' group by C")
 

#引用日期

• Where 中要篩選日期有兩種方法，第一種是轉成數值 (推薦這個方法，因為很簡單)，第二種方法是轉成日期格式
  • 第一種：直接 Q <= 43862 (日期本來就可以自動轉成數值)，請在格式中選擇
  • 第二種：date’2020-01-01′ 或是 TEXT(N12,”yyyy-mm-dd”)

--將日期轉成數值
=query('RAW Data'!A:Q,"select A,Q Where Q <= 43862")
=query('RAW Data'!A:Q,"select A,Q Where Q <=" &J12)
--將日期轉成日期格式
=QUERY('RAW Data'!A:P,"select A,E where K= date'2020-01-01'")
=QUERY('RAW Data'!A:P,"select A,E where K= date'" & TEXT(N12,"yyyy-mm-dd") & "'") 
 

#用 Where 篩選多條件分享

再 SQL 語法裡有一種 in ( )，裡面可以快速寫入很多個條件，例如說台灣所有的城市，我們會用 in (‘Taipei’, ‘Taichung’…..)，那在 google sheet query 裡有三種方法可以做多條件篩選，給大家參考

1. 如果有好幾個條件想要同時篩選，可以用 OR 或是 AND 組起來
2. 用 MATCHES ‘(條件1|條件2|條件3)’，這個方法類似 in ( )
3. 用 TEXTJOIN 小工具，把 or 和所有條件快速組起來

#用 Where 篩選多條件 – or/and

第一種方法：如果有好幾個條件想要同時篩選，可以用 or 或是 and 組起來：

=QUERY('RAW Data'!A:P,"select C,sum(L) 
where C='Taipei' or C='Taichung' group by C") 
 

• 如果想要 Taipei 或是 Taichung 的資料，就用 or 連接
  • 用 or 把所有條件組起來，如果文字要用單引號包起來

=QUERY('RAW Data'!A:P,"select C,sum(L) 
where C='Taipei' and D='M' and E=50 group by C")
 

• 如果想要同時符合三個條件，例如說 Taipei 同時又是男性 (M)，而且年齡 50 歲，那就用 and 連接，寫法和 or 類似

#用 Where 篩選多條件 – MATCHES ‘(條件1|條件2|條件3)’

第二種方法：如果你同時想篩選好多條件，例如說你想要台北、台中、高雄，或是更多城市的資料，但你不想要一個一個用 or 寫，那你可以試試看用 matches 來生成自動化的條件組合

--MATCHES '(條件1|條件2|條件3)'
--原來的樣子
=QUERY('RAW Data'!A:P,"select C,sum(L) 
where C matches'(Taipei|Taichung)'group by C")
--以 matches 做變化：有點難！建議大家到示範裡觀看
=query('RAW Data'!A:Q,"select A, E where " &  "E matches'(" &textjoin("|",1,AH12:AH) &")'")
 

• MATCHES ‘(條件1|條件2|條件3)’
  • 條件不論文字不需要包單引號，中間用 | 隔開

#用 Where 篩選多條件 – TEXTJOIN​

第三種方法：假如你有 100 個人名要篩選，那你應該不會想要慢慢組條件，這時候 Textjoin 小道具就可以幫我們自動生成 or 小組

--textjoin("' or C='", 1, 多條件)
--原來的樣子
=query('RAW Data'!A:Q,"select C,sum(L) where C='Taipei' or C='Taichung' or C='Taoyuang' group by C")
--以 textjoin 做變化
=query('RAW Data'!A:Q,"select C, sum(L) where " & "C='" & textjoin("' or C='",1,AA12:AA) &"'" & "group by C")
 

• Textjoin 可以將你設定的所有條件組成一連串的 or
  • 第一步：先把所有條件列在 google sheet 裡面其中一行，範例中列在 AA12:AA
  • 第二步自動生成很多 or + 條件連在一起：=”C='” & textjoin(“‘ or C='”,1,AA12:AA) &”‘”
  • 如果想看這些函數如何組合，可以參考 Google Sheet 教學，或是 影片 教學，這裡有詳細說明每一步是如何生成的

# 模糊比對 like ‘%A%’

如果你想進行模糊比對，例如說文字只要包含 Taipei 就可以，那可以用 where 欄位 like ‘%Taipei%’ 來進行模糊比對搜尋結果

=QUERY('RAW Data'!A:P,
"select A,C 
where C like '%Taipei%'") 

Google sheet query: Group by

• Group by(聚合)：group by B, C, K，如果在選取欄位時有做「敘述性統計」加總、平均這類的運算，就要把一開始分群的類別用 group by 做聚合

Google sheet query: Order by

• Order by(排序)：order by sum(M) desc，資料按照你想要的欄位做排序，再指定要升冪或是降冪，範例中我用訂單數做降冪排列，可以看到台北訂單最多，台中墊底

Google sheet Query 練習與使用場景

為什麼專業分析師不喜歡用 pivot table？

範例一跟練習一比較類似，這些屬於 pivot table 就可以處理的。不過很多時候，資料處理是一層一層的，RAW Data 初步作成我們想要的分群之後，我們會再把這份 Data 加工做成 dashboard 或是表格，所以像是練習二中，pivot table 就不會是我們想要的資料形式，他太多空格了，以至於不能再去做下一層的資料聚合。

樞紐分析表太多空格，不能做下一層的資料處理

這種資料格式可以畫圖、匯入 Data Base、做下一層資料聚合

Google sheet Query Challenge

• 練習一：試著分析每個月份各城市不同外送方式的人數及成效，這個練習跟範例一比較類似，試著練習看看 Query
• 練習二：試著對一月有跑該平台的外送員做分群，再做成效分析，這個就是平時我們在做資料處理的時候很常用的「資料分群」，把特性相似的一群使用者聚合在一起後再做更深度的分析。這個練習中，我把外送員依照這些特性：「城市」、「年齡」、「種族」、「外送方式」做統計，通常數字出來之後，會再依據分析目的：下招募廣告時，應該以哪些使用者輪廓為目標？去寫結論或是再繼續深挖資料。

總結

這邊總結了 Google Sheet Query 最重要的用法跟細節，如果你想要更全面的了解，可以到這篇文章：Google Sheet Query 9 大函數語法 (9 useful google sheet query functions)

如何練習 Google Sheet Query 函數？

1. 複製一份我的範例出來
2. 練習所有的函數跟 2 道練習題

更多學習資源推薦

• Google 官方教學：Getting Started with Google Sheets
• Lisa 的超白話實用教學：Lisa 的 Google Sheet 公式教學
• Google Data Analytics Professional Certificate：Google 認證的資料分析師證照課程有幫助嗎？ 誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！

如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 Google Sheet 教學：本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學