2015年10月3日 星期六

Excel-最近幾筆和最近幾天的每日與分組平均(OFFSET,SUMPRODUCT)

常有人會問到在 Excel 中以日期為主的每日或分組的數值計算,例如:計算最近幾筆和最近幾天的每日平均,或是計算幾筆和幾天的日期區間的分組平均。本篇就來看看這樣的差異,如何處理?
1. 計算最近幾筆和最近幾天的每日平均
參考下圖,要計算以 8 天為單位來計算最近幾筆和最近幾天的每日平均。
Excel-最近幾筆和最近幾天的每日與分組平均(OFFSET,SUMPRODUCT)
(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 天為單位來計算幾筆和幾天的日期區間的分組平均。
Excel-最近幾筆和最近幾天的每日與分組平均(OFFSET,SUMPRODUCT)
(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)))

沒有留言:

張貼留言

檢視其他文章

好康東東