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

Posted on  Oct 23, 2018  in  Google 試算表  by  Amo Chen  ‐ 2 min read

近來,閒暇無事翻閱 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 , timeofdaynull

其他詳見 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

對抗久坐職業傷害

研究指出每天增加 2 小時坐著的時間,會增加大腸癌、心臟疾病、肺癌的風險,也造成肩頸、腰背疼痛等常見問題。

然而對抗這些問題,卻只需要工作時定期休息跟伸展身體即可!

你想輕鬆改變現狀嗎?試試看我們的 PomodoRoll 番茄鐘吧! PomodoRoll 番茄鐘會根據你所設定的專注時間,定期建議你 1 項辦公族適用的伸展運動,幫助你打敗久坐所帶來的傷害!

贊助我們的創作

看完這篇文章了嗎? 休息一下,喝杯咖啡吧!

如果你覺得 MyApollo 有讓你獲得實用的資訊,希望能看到更多的技術分享,邀請你贊助我們一杯咖啡,讓我們有更多的動力與精力繼續提供高品質的文章,感謝你的支持!