贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 訪客
  • A B C D E F G H I J K L M N
    1 S0 A B C D S0 A B C D 1 2 3
    2 1 2 3 S1 1 2 3 4 A B C
    3 S1 1 2 3
    4 4 A B C

    抱歉,手機排版有點困難

    老師請問要怎麼把資料(A1:F4),變成以S0為開頭,如(G1)開始這樣排序完成??
    或是如何把儲存格的每列欄數固定,變成一列只有4欄,讓(E1)移至(A2),後面的欄數都往後移一格??
    資料共有300多列
    再麻煩老師傳授一下技巧,謝謝了
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼