有網友問到:在 Excel 中,如何能在增刪欄/列時,不會改變公式中的儲存格範圍?

大家都知道 Excel 中的公式可以在你增刪欄/列時,會自動幫你相對調整公式而自動擴大或縮小儲存格範圍。所以也可能發生公式中的某些儲存格(例如下圖中的第1欄)被刪除後,傳回 #REF! 錯誤訊息。

或是在下圖中的例子,儲存格B1:=SUM(A4:A21),用以計算儲存格A4:A21的總和。因為當你在第4列中插入一列時,公式會自動調整為儲存格B1:=SUM(A5:A22)。

如果當第4列插入一列的資料時,你希望公式仍計算儲存格A4:A21的和,該如何設計公式?

要處理這樣的問題,必須將儲存格範圍以另一種型式呈現,提供以下二種方式:

(1) =SUM(INDIRECT("A4:A21"))

將儲存格範圍以字串("A4:A21")表示,再以 INDIRECT 函數轉換為儲存格範圍。

(2)=SUM(OFFSET($A$1,3,0,18,1))

透過 OFFSET 函數,指定起始儲存格和列數,由系統產生儲存格範圍。

如此,公式中便不會產生因為增刪欄/列而改變儲存格範圍的狀況。

arrow
arrow
    全站熱搜

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