(網友提問)在 Excel 中要計算符合條件的加總可以使用 SUMIF 函數,例如:

(參考下圖)

儲存格F2:=SUMIF($B$2:$B$19,E2,$C$2:$C$19)

儲存格F3:=SUMIF($B$2:$B$19,E3,$C$2:$C$19)

但是當資料是複製而來,當貼上的資料範圍超過原來公式裡的儲存格範圍,如何可以不改公式而正確的計算結果。

Excel-解決複製而來的資料而需要修改公式

【公式設計與解析】

如果儲存格範圍會變動,則需要藉助 OFFSET 函數和 COUNTA 函數來求得動態範圍。

儲存格F2:=SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$199),1),E2,OFFSET
($C$2,0,0,COUNTA($B$2:$B$199),1))

(1) COUNTA($B$2:$B$199)

利用 COUNTA 函數求取一個儲存格範圍內的文字(非空白)數量,其中參數 199,只是一個很大的數字。

(2) OFFSET($B$2,0,0,COUNTA($B$2:$B$199),1)

將第(1)式代入 OFFSET 函數,可以求得含有文字的儲存格範圍。

同理:

儲存格F3:=SUMIF(OFFSET($B$2,0,0,COUNTA($B$2:$B$199),1),E3,OFFSET
($C$2,0,0,COUNTA($B$2:$B$199),1))

 

【另解】

如果使用 SUMIF($B$2:$B$19,E2,$C$2:$C$19) 時,能將儲存格範圍盡量放大一些,如此貼上新資料時,也不需要改公式。

文章標籤
創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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