學不完．教不停．用不盡

網友問到 Excel 的問題：參考下圖，如何在G欄和H欄之間插入一欄時，不會改變公式中相對的儲存格位置？

下圖中，目前起始值有6個月的目內容，之後會不斷的插入一個月，報表會不斷的往右延伸。例如：在插入七月的資料後，近6月的平均自動計算2～7月的平均；而前1月數值，也自動對應到新插入的H欄內容。

【公式設計與解析】

這個題目，很適合使用 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，而其他定數的部分都不會被改變。