學不完．教不停．用不盡

延續這兩篇：

Excel-動態計算最近n天且小於m筆的平均

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

如果要動態計算最近日期大於M的N筆數值平均，該如何處理？

【設計與解析】

定義名稱：DATA，參照位址：儲存格B4:B400。

儲存格D6：=AVERAGE(INDEX(DATA,LARGE(IF(DATA>$D$4,ROW(DATA),""),ROW(INDIRECT("1:"&E4)))-3))

這是陣列公式，Excel 2021 以前版本者，要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

儲存格D6：{=AVERAGE(INDEX(DATA,LARGE(IF(DATA>$D$4,ROW(DATA),""),ROW(INDIRECT("1:"&E4)))-3))}

(1) ROW(INDIRECT("1:"&E4))=ROW(1:10)

儲存格E4為指定的筆數(N)，指定1:N筆

(2) LARGE(IF(DATA>$D$4,ROW(DATA),""),第(1)式))

傳回大於M的最近N筆的列號

=INDEX(DATA,第(2)式-3)

根據列號找出對應的數值

=AVERAGE(INDEX(DATA,第(2)式-3))

計算找到的數值加以平均

當在最末位置增加一筆時，公式自動判斷並取最近日期大於500的10筆來計算平均。

【參考資料】

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

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

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

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