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 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/index-函數-a5dcf0dd-996d-40a4-a822-b56b061328bd

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

https://support.microsoft.com/zh-tw/office/indirect-函數-474b3a3a-8a26-4f44-b491-92b6306fa261

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

https://support.microsoft.com/zh-tw/office/large-函數-3af0af19-1190-42bb-bb8b-01672ec00a64

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

https://support.microsoft.com/zh-tw/office/row-函數-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d

 

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

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