網友提問:(參考下圖)如何在 Excel 工作表中不斷增加資料的清單中,計算最近 5, 10 , 15 天的數值平均值?

資料每天都在增加,如何計算距離現在最近的幾筆資料的平均?

Excel-動態計算最近幾天的平均(OFFSET)

 

【公式設計與解析】

假設B欄資料最多不超過儲存格B7:B500。

儲存格B2:=AVERAGE(OFFSET($B$7,COUNT($B$7:$B$500)-1,0,-5,1))

(1) COUNT($B$7:$B$54)

利用 COUNT 函數計算B欄中不斷增加的資料目前有幾筆數字。

(2) OFFSET($B$7,COUNT($B$7:$B$54)-1,0,-5,1)

將第(1)式傳回資料數值共有幾筆,代入 OFFSET 函數。

OFFSET($B$7,COUNT($B$7:$B$54)-1,0)指向B欄最後一個儲存格。

OFFSET($B$7,COUNT($B$7:$B$54)-1,0,-5,1)藉由參數「-5」,表示由下而上 5 筆資料。(本例為儲存格B50:B54)

(3) AVERAGE(OFFSET($B$7,COUNT($B$7:$B$54)-1,0,-5,1))

最後利用 AVERAGE 函數將第(2)式傳回的儲存格範圍予以平均,即為所求。

 

同理:

儲存格B3:=AVERAGE(OFFSET($B$7,COUNT($B$7:$B$500)-1,0,-10,1))

只要設定參數「-10」。

儲存格B4:=AVERAGE(OFFSET($B$7,COUNT($B$7:$B$500)-1,0,-15,1))

只要設定參數「-15」。

另一個類以的例子,請參考:https://isvincent.pixnet.net/blog/post/38975811

文章標籤

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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