2011年10月7日 星期五

Excel-數列中重覆者加總時只計算一次(COUNTIF+陣列公式)

在 Excel 中的一個資料表(參考下圖),如果計算B欄中的數值總和,但是重覆者只能計算一次,該如何處理?

求得B欄中每個數字重覆的次數,輸入公式:

儲存格C2:=COUNTIF($B$2:$B$16,B2)

(1) 計算未重覆者之和

儲存格F2:=SUMIF(C2:C16,1,B2:B16)

根據C欄的重覆次數,利用SUMIF函數求得總和。

或是以陣列公式來計算:

儲存格F2:={SUM(IF(COUNTIF(B2:B16,B2:B16)=1,B2:B16))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(2) 計算重覆者之和

儲存格F3:=SUMIF(C2:C16,">1",B2:B16)

或是以陣列公式來計算:

儲存格F3:{=SUM(IF(COUNTIF(B2:B16,B2:B16)>1,B2:B16))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

(3) 每數只計算1次之和

儲存格F4:{=SUM(B2:B16/COUNTIF(B2:B16,B2:B16))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

其原理是將數列的陣列除以每個數重覆次數的陣列,再將結果的陣列加總即可。

沒有留言:

張貼留言

好康東東