2015年8月13日 星期四

Excel-公式中不因增刪欄列而改變儲存格範圍(INDIRECT,OFFSET)

有網友問到:在 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 函數,指定起始儲存格和列數,由系統產生儲存格範圍。
如此,公式中便不會產生因為增刪欄/列而改變儲存格範圍的狀況。

沒有留言:

張貼留言

好康東東