學不完．教不停．用不盡

常有人會問到在 Excel 中以日期為主的每日或分組的數值計算，例如：計算最近幾筆和最近幾天的每日平均，或是計算幾筆和幾天的日期區間的分組平均。本篇就來看看這樣的差異，如何處理？

1. 計算最近幾筆和最近幾天的每日平均

參考下圖，要計算以 8 天為單位來計算最近幾筆和最近幾天的每日平均。

(1) 計算最近 8 筆的每日平均

儲存格D9：=AVERAGE(C2:C9)

複製儲存格D9，貼至儲存格D9:D28。

(2) 計算最近 8 天的每日平均

儲存格E2：
=SUMPRODUCT(($B$2:B2>B2-8)*$C$2:C2)/SUMPRODUCT(--($B$2:B2>B2-8))

SUMPRODUCT(($B$2:B2>B2-8)*$C$2:C2)：計算合於最近 8 天的數值總合。

SUMPRODUCT(--($B$2:B2>B2-8))：計算合於最近 8 天的日期個數。

以上二相除可得平均數。

複製儲存格E2，貼至儲存格E2:E28。

2. 計算幾筆和幾天的日期區間的分組平均

參考下圖，要計算以 5 天為單位來計算幾筆和幾天的日期區間的分組平均。

(1) 計算每 5 筆日期區間的分組平均

儲存格F2：=AVERAGE(OFFSET($C$2,(ROW(1:1)-1)*5,0,5,1))

ROW(1:1)=1 在向下複製公式後，會得到 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

OFFSET($C$2,(ROW(1:1)-1)*5：利用 OFFSET 函數找出每 5 筆的儲存格範圍。

複製儲存格F2，貼至儲存格F2:F7。

(2) 計算每 5 天的日期區間的分組平均

先將儲存格B2:B31定義名稱：日期、儲存格C2:CB31定義名稱：數值。

第一式：=SUMPRODUCT((日期>=($B$2+(ROW(1:1)-1)*5))*(日期<=
($B$2+(ROW(1:1)-1)*5+4)))

ROW(1:1)-1)*5：在向下複製公式時，可以傳回 0, 5, 10, 15, 20, 25, …。

ROW(1:1)-1)*5+4：在向下複製公式時，可以傳回 4, 9, 14, 19, 24, 29, …。

(日期>=($B$2+(ROW(1:1)-1)*5))*(日期<=($B$2+(ROW(1:1)-1)*5+4))：用以找出合於最近 5 日的儲存格陣列個數，其中「*」運算子相當於執行邏輯 AND 運算。運算時 TRUE／FASLE 陣列會轉換為 1／0 陣列。

第二式：=SUMPRODUCT((日期>=($B$2+(ROW(1:1)-1)*5))*(日期<=
($B$2+(ROW(1:1)-1)*5+4))*數值)

原理仿第一式，在 SUMPRODUCT 函數中多加了「*數值」，即可取得合於日期條件的數值總和。

儲存格F10：=第二式/第一式，即可求得平均數。

複製儲存格F10，貼至儲存格F10:F17。

註：完整公式

儲存格F10：=SUMPRODUCT((日期>=($B$2+(ROW(1:1)-1)*5))*(日期<=
($B$2+(ROW(1:1)-1)*5+4))*數值)/SUMPRODUCT((日期>=($B$2+
(ROW(1:1)-1)*5))*(日期<=($B$2+(ROW(1:1)-1)*5+4)))