2017年7月12日 星期三

Excel-插入一欄後不改變公式中相對的儲存格位置(OFFSET)

網友問到 Excel 的問題:參考下圖,如何在G欄和H欄之間插入一欄時,不會改變公式中相對的儲存格位置?
下圖中,目前起始值有6個月的目內容,之後會不斷的插入一個月,報表會不斷的往右延伸。例如:在插入七月的資料後,近6月的平均自動計算2~7月的平均;而前1月數值,也自動對應到新插入的H欄內容。
Excel-插入一欄後不改變公式中相對的儲存格位置(OFFSET)
【公式設計與解析】
這個題目,很適合使用 OFFSET 函數來設計。
1. 儲存格I2:=AVERAGE(OFFSET($I2,0,-2,1,-6))
複製儲存格I2,貼至儲存格I2:I6。
OFFSET($I2,0,-2,1,-6) 公式中的參數「-2、-6」分別對應圖中的「-2、-6」。
在 OFFSET 函數中以儲存格I2起始,-2 表示往左相對 2 欄/-6 表示往左寬度 6 欄的位置。
因為插入一欄後,原公式會自動變為:AVERAGE(OFFSET($J2,0,-2,1,-6))
其中儲存格I2會自動變為儲存格J2,而其他定數的部分都不會被改變。

2. 儲存格L2:=OFFSET($L2,0,-5)
複製儲存格L2,貼至儲存格L2:L6。
OFFSET($L2,0,-5) 公式中的參數「-5」對應圖中的「-5」。
在 OFFSET 函數中以儲存格L2起始,-5 表示往左相對 5 欄的位置。
因為插入一欄後,原公式會自動變為:=OFFSET($M2,0,-5)
其中儲存格L2會自動變為儲存格M2,而其他定數的部分都不會被改變。

沒有留言:

張貼留言

好康東東