2011年11月2日 星期三

Excel-插入一列後自動調整公式

在 Excel 中的一個常見資料表(如下圖),如果在12列和13列之間插入一列輸入資料,平均值會變成如何?

【原始內容】

儲存格B13:AVERAGE(B2:B12)

儲存格C13:AVERAGE(C2:C12)

儲存格D13:AVERAGE(D2:D12)

儲存格E13:AVERAGE(E2:E12)

【插入一列】

當插入一列(例如下圖之列13)後,當你輸入資料時,按下 Enter 鍵後,Excel 會自動調整公式為:

儲存格B14:AVERAGE(B2:B13)

當其他欄尚未輸入資料時,並不會調整公式。(例如儲存格B14、C14、D14)

【不變的公式】

如果想要輸入一個不會隨著列的增加而改變的公式,該如何處理?

儲存格B14:=AVERAGE(OFFSET(B2,,,ROW()-2,))

在 OFFSET 函數中使用 ROW() 找到目前的列號,減掉 2 可得儲存格B2到平均值公式的前一格共有多少列。

OFFSET(B2,,,ROW()-2,) 可得一個變動的儲存格範圍,如此變可以不因插入列而變動公式。

儲存格C14:=AVERAGE(OFFSET(C2,,,ROW()-2,))

儲存格D14:=AVERAGE(OFFSET(D2,,,ROW()-2,))

儲存格E14:=AVERAGE(OFFSET(E2,,,ROW()-2,))

【補充說明】

詳細函數說明,請參考微軟網站:

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

沒有留言:

張貼留言

好康東東