學不完．教不停．用不盡

網友根據這篇：Excel-動態計算最近幾天的平均(OFFSET)

問到：如果要動態計算最近n天且小於m筆的平均該如何設計？

【設計與解析】

選取儲存格A4:A39，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

1. 最近m筆(m=10)

儲存格D6：=AVERAGE(OFFSET($B$4,COUNT(日期)-1,0,-1*D4,1))

因為日期是由遠而近(由上而下)排列，所以：

(1) COUNT(日期)

計算全部日期的數量。

(2) OFFSET($B$4,COUNT(日期)-1,0,-1*D4,1)

利用「-1*D4」，取得日期陣列的最後m筆。

2. 最近n天且小於m筆(n=30、m=10)

儲存格D26：=AVERAGE(OFFSET($B$4,COUNT(日期)-1,0,-1*MIN(D24,COUNTIF(日期,">="&(TODAY()-E24))),1))

利用和1.相同的原理。

MIN(D24,COUNTIF(日期,">="&(TODAY()-E24))，其中：

最近m天：=儲存格D24

小於n筆：=COUNTIF(日期,">="&(TODAY()-E24)

取儲存格D24和COUNTIF(日期,">="&(TODAY()-E24)的最小值。

【延伸閱讀】

​ Excel-動態計算最近日期大於M的N筆數值平均

​ Excel-動態計算最近N天且數值大於M的平均

【參考資料】

​ OFFSET 函數參考微軟提供的說明網頁：OFFSET 函數

​ COUNTIF 函數參考微軟提供的說明網頁：COUNTIF 函數