用 Google Sheets Query 函式製作訂飲料表單

近來，閒暇無事翻閱 Google Sheets 函式列表 的時候， 赫然發現一個有趣的函式 - QUERY 。

相信有使用資料庫經驗的人都會直覺想到 SQL 。

沒錯， Google Sheets 的 QUERY 函式提供透過 SQL 語法查詢資料的功能。

不過， Google Sheets 的 QUERY 的語法並不是完整的 SQL ，所以使用上有些出入，甚至功能並非想像中的強大，例如幾個不同的地方：

1. 沒有 FROM ，所以不需要寫 FROM <table>
2. 沒有 AS ，因此使用 AS 會造成錯誤
3. 資料型態只有 string , number , boolean , date , datetime , timeofday 與 null

其他詳見 Language Syntax 。

即使如此， QUERY 對於簡單的應用依然十分強大，所以本篇用 Google Sheets 中的 Query 函式試著製作一個訂飲料的表格作為範例，幫忙快速彙整訂飲料外送所需要的金額與品項統整。

飲料表格 #

ㄧ般訂飲料需要的資訊通常會有品項、大中小杯、冰塊、甜度，因為是訂購飲料的表單，所以我們需要額外加上單價、數量、訂購人、小計、總計等資訊方便統計，所以表格可能會長得像以下這樣：

小計的部分就可以用簡單用公式 =IF(E2*F2=0, "", E2*F2) ，簡單來說 E 欄乘以 F 欄就是小計，為了表單美觀，此處額外多了一個 IF 函式，判斷當 E 欄乘以 F 欄是 0 的時候，就顯示空的格子，如果不是 0 就會顯示 E 欄乘以 F 欄的結果。

計算完小計之後，可以進一步算出總計，也就是整個 H 欄加總，公式為 =sum(H2:H) 。

雖然已經算出總金額，但最麻煩的其實是訂飲料時需要把相同的品項、大中小杯、甜度、冰塊都相同的飲料數量加總在一起匯總，這樣才能有效地跟店員溝通。

這時候， QUERY 主角登場。

首先，利用 SQL 語法中的 SELECT 篩選出品項、大中小杯、甜度、冰塊與總杯數，所以語法會是 SELECT A,B,C,D,SUM(F) ，其中的 SUM(F) 指的是將同一群組的 F 欄加總。

篩選資料做好之後，還需要告訴 Google Sheets 如何進行分組，分組的條件為相同的品項、大中小杯、甜度、冰塊就是同一組，所以進一步利用 GROUP BY 語法幫忙把相同的品項、大中小杯、甜度、冰塊分成一組，也就是 GROUP BY A, B, C, D 。

不過有些人可能會故意空一列填寫表格，導致有些品項被 QUERY 函式認為空的品項也是 1 種品項，所以額外再加上 WHERE A IS NOT NULL 排除空一列填寫表格的情況。

所以最後公式會變成 =QUERY(A2:F1000, "select A,B,C,D,sum(F) where A is not null group by A,B,C,D", -1) ，輸入完之後，就可以看到自動統計完的結果啦！

以下是 Google Sheets 的範例，可以自行複製使用：

https://docs.google.com/spreadsheets/d/1ARi1mdeu4jAbOgsdqq3xEz2_NT-INN1W7vd1JVaXCLM/edit?usp=sharing

References #

https://support.google.com/docs/table/25273?hl=en

https://support.google.com/docs/answer/3093343

google sheets excel 試算表