2013年4月19日 星期五

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

有人想要在一個 Excel 的資料表中(如下圖左),計算最近幾天的平均值,但是這個報表是動態的,因為每隔 2 天會新增一個日期,而且要從第 1 筆資料的位置來新增(如下圖右),計算公式要如何撰寫?

原本計算平均是件簡單的事,但是由於資料會不斷的新增,所以公式中的儲存格位址會因為資料表新增一列而自動被調整,因此要使用間接方式來表達儲存格才能正確運算。

 

儲存格B2:=AVERAGE(INDIRECT("B7:B" & (7+ROW(1:1)*5)-1))

複製儲存格B2,貼至儲存格B2:B4。

"B7:B" & (7+ROW(1:1)*5)-1):找出最近 5 天的儲存格位址範圍字串,當向下複製時會自動調整為最近 10 天和最近 15 天的儲存格位址範圍字串。

接著以 INDIRECT 函數將上式的儲存格位址範圍字串轉換為真實的儲存格位址。這個位址不會因為新增一列而被 Excel 自動改變公中的位址。再以 AVERAGE 函數計算平均。

使用這種做法,可以不受新增/移除儲存格的影響。

沒有留言:

張貼留言

檢視其他文章

好康東東