QUERY 函式大解析（七）：簡易日期時間擷取術

(包起來就好)

• 我有個資料叫 apple，用「大寫濾鏡」就可以把 apple 轉成 APPLE。
• 我有個資料叫 TEA，用「小寫濾鏡」就可以把 TEA 變成 tea。
• 我有個資料叫 2022/01/23，用「年份濾鏡」就可以把 2022/01/23 變成 2022。

濾鏡(A)
濾鏡(B)

濾鏡(A), 濾鏡(B), 濾鏡(C)...
SUM(A), AVG(B), COUNT(C)...

日期

• year()：回傳日期的年份。

在 A 欄的某資料是 2022/01/23
year(A) --> 2022

• month()：回傳日期的月份。
  要注意的是，這裡的月份是以 0 為基底，要記得加 1 才會顯示正確的月份。舉例：

在 A 欄的某資料是 2022/01/23
month(A) --> 0
month(A) + 1 --> 1

在 B 欄的某資料是 2022/02/23
month(B) --> 1
month(B) + 1 --> 2

在 C 欄的某資料是 2022/10/23
month(C) --> 9
month(C) + 1 --> 10

• day()：回傳日期的日子。

在 A 欄的某資料是 2022/01/23
day(A) --> 23

• quarter()：回傳日期的季別。1 就是第一季、2 就是第二季，以此類推。

在 A 欄的某資料是 2022/01/23
quarter(A) --> 1

在 B 欄的某資料是 2022/04/23
quarter(B) --> 2

• dayOfWeek()：回傳日期的星期。要注意 QUERY 是以星期天當做一個星期裡的第一天，所以 1 是星期天、2 是星期一、3 是星期二，以此類推。

在 A 欄的某資料是 2022/01/23（這天是星期天）
dayOfWeek(A) --> 1

在 B 欄的某資料是 2022/02/23（這天是星期三）
dayOfWeek(B) --> 4

時間

• hour()：回傳時間的小時。

在 A 欄的某資料是 14:25:19
hour(A) --> 14

• minute()：回傳時間的分。

在 A 欄的某資料是 14:25:19
minute(A) --> 25

• second()：回傳時間的秒。

在 A 欄的某資料是 14:25:19
second(A) --> 19

• millisecond()：回傳時間的毫秒。

在 A 欄的某資料是 14:25:19.487
millisecond(A) --> 487

練習

• 想知道每個月的總銷售額。
• 每個月的五號是全館九折的活動日，想知道各品項的總銷售額。
• 每天晚上 9 點 00 分到 11 點 59 分有會員點數加倍活動，想知道各品項的總銷售件數。
• 想知道星期一到星期日裡，各品項的平均銷售額。

想知道每個月的總銷售額

=QUERY('訂單資料'!A:I,
"SELECT Month(A) + 1, SUM(H)
 WHERE I = '已發貨'
 GROUP BY Month(A) + 1")

每月 5 號各品項的總銷售件數

=QUERY('訂單資料'!A:I,
"SELECT E, COUNT(C)
WHERE Day(A) = 5 and I = '已發貨'
GROUP BY E")

每天晚上 9 點 00 分到 11 點 59 分各品項的總銷售件數

... WHERE (Hour(B) >= 21 and Hour(B) <= 23)
and I = '已發貨'

... WHERE (Hour(B) = 21 or Hour(B) = 22 or Hour(B) = 23)
and I = '已發貨'

星期一到星期日各品項的平均銷售額

• 各品項的平均銷售額
• 星期一到星期日

=QUERY('訂單資料'!A:I,
 "SELECT E, AVG(H)
  WHERE I = '已發貨'
  GROUP BY E
  PIVOT dayOfWeek(A)")

如果你喜歡這次的文章，歡迎你透過這些方法支持我：
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍，即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作！