2014年4月27日 星期日

Excel-處理變動的儲存格範圍(OFFSET,INDIRECT)

有網友想要詢問:在 Excel 的工作表中處理資料時,能夠使用公式來處理變動的儲存格範圍。例如下圖中的各欄資料可能會有所增/刪,該如何處理呢?

一般當你計算加總時,例如儲存格F2:=SUM(A2:A18)

當你在Data1的欄位資料中刪除或是插入一筆資料時,公式=SUM(A2:A18)會跟著調整,可是如果你新增的資料是在最後一筆以外的位置,則 SUM 公式中的儲存格範圍,不會自動調整,該如何使用公式讓他可以自動調整呢?

你可以試試 OFFSET 函數和 INDIRECT 函數,假設每欄資料不會超過999列:

(1) 儲存格F2:=SUM(OFFSET(A2,0,ROW(1:1)-1,COUNTA(A2:A999),))

COUNTA(A1:A999):計算 Data1 中含有數字的儲存格個數。

透過 OFFSET 函數配合 COUNTA(A2:A999) 的結果,即可達到變動儲存格範圍。

(2) 儲存格F2:=SUM(INDIRECT("A2:A"&COUNTA(A2:A999)+1))

使用 INDIRECT 函數配合 COUNTA(A2:A999) 的結果,即可達到變動儲存格範圍。

 

【自行練習】

修改公式,求出 Data2 和 Data3 的總和!

沒有留言:

張貼留言

好康東東