網友問到 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,而其他定數的部分都不會被改變。

arrow
arrow
    文章標籤
    Excel OFFSET
    全站熱搜

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