2015年9月9日 星期三

Excel-在多組資料清單中將符合條件者加總(SUMPRODUCT,SUMIF,陣列公式)

有網友想要在 Excel 中如下圖左的資料清單,將不同類別的數量予以小計,該如何處理?在下圖左中,如果三組「類別、數量」放在同一欄中,不管使用 SUMPRODUCT 函數或是 SUMIF 函數等,都可以一個公式就可以解決,但是分成多組就不易處理了。

【公式設計與解析】
方式1:使用 SUMPRODUCT 函數
(1) SUMPRODUCT(($A$2:$A$16=H2)*$B$2:$B$16)
(2) SUMPRODUCT(($C$2:$C$16=H2)*$D$2:$D$16)
(3) SUMPRODUCT(($E$2:$E$16=H2)*$F$2:$F$16)
儲存格I2:=(1)+(2)+(3)
複製儲存格I2,貼至儲存格I2:I8。

方式2:使用 SUMPRODUCT 函數
(1) ($A$2:$A$16=H2)*$B$2:$B$16
(2) ($C$2:$C$16=H2)*$D$2:$D$16
(3) ($E$2:$E$16=H2)*$F$2:$F$16
儲存格I2:SUMPRODUCT((1)+(2)+(3))
複製儲存格I2,貼至儲存格I2:I8。
方法2的寫法相對方式1的寫法,可以縮短公式長度。

方式3:使用 SUMIF 函數
(1) SUMIF($A$2:$A$16,H2,$B$2:$B$16)
(2) SUMIF($C$2:$C$16,H2,$D$2:$D$16)
(3) SUMIF($E$2:$E$16,H2,$F$2:$F$16)
儲存格I2:=(1)+(2)+(3)
複製儲存格I2,貼至儲存格I2:I8。

方式4:使用 SUM+IF 函數+陣列公式
(1) IF($A$2:$A$16=H2,$B$2:$B$16,0)
(2) IF($C$2:$C$16=H2,$D$2:$D$16,0)
(3) IF($E$2:$E$16=H2,$F$2:$F$16,0)
儲存格I2:{=SUM((1),(2),(3))}
複製儲存格I2,貼至儲存格I2:I8。
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
方法4和方法3的公式是相同概念的。

沒有留言:

張貼留言

檢視其他文章

好康東東