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

延續這兩篇:

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

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

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

Excel-動態計算最近日期大於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筆來計算平均。

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

 

【參考資料】

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

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

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

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

 

學不完.教不停.用不盡文章列表

arrow
arrow

    vincent 發表在 痞客邦 留言(5) 人氣()