學不完．教不停．用不盡

1. 不因在第一列插入資料改變公式

在 Excel 的公式中，當你在插入一列（欄）後，Excel 會自動幫你調整相對的公式內容。

雖然方便實用，但有時你就是不想讓它自動調整。

【例】如果儲存格F5公式：=(B3-B6)/(A3-A6)

當在第3列新增一列時，公式會自動被修正為：=(B4-B7)/(A4-A7)

這是錯誤的結果。因為第 3 列裡預備要輸入資料，公式應為：=(B3-B7)/(A3-A7)

該如何處理？

公式修正：=(INDIRECT("B3")-B7)/(INDIRECT("A3")-A7)

INDIRECT：文字串所指定的參照位址。

INDIRECT("B3")將文字「B3」轉換為儲存格B3位址。

轉成字串就不會因插入動作而自動改變參照位址。

輸入資料後，結果是正確的。

不論插入幾列，都是會保持儲存格B3不變。

2. 不因在最後一列新增資料而改變公式

如果是在最後一個儲存格之後再新增資料，如何不修公式？

儲存格F2：=(OFFSET(B3,COUNT(B3:B1000)-1,0)-B3)/(OFFSET(A3,COUNT
(A3:A1000)-1,0)-A3)

(1) COUNT(B3:B1000)

利用 COUNT 函數計算儲存格B3:B1000中已輸入幾筆資料。

(2) OFFSET(B3,COUNT(B3:B1000)-1,0)

透過 OFFSET 函數，以儲存格B3為起點，取出相對第(1)式傳回值所對應的儲存格位址。

【延伸閱讀】

參考：本部落格中其他關於 Excel OFFSET 函數的應用

參考：本部落格中其他關於 Excel INDIRECT 函數的應用

參考：本部落格中其他關於 Excel COUNT 函數的應用