2016年5月22日 星期日

Excel-依組別在最後一筆資料計算小計(SUMIF,SUMPRODUCT)

有網友想要利用 Excel 在一個已經依照組別排列的清單中,自動計算小計,該如何處理?而小計只出現在該組的最後一筆位置。
觀察下圖中,各個組別最後一筆會有對應的一筆小計。
Excel-依組別在最後一筆資料計算小計(SUMIF,SUMPRODUCT)

【公式設計與解析】
(1) 使用 SUMPRODUCT 函數
透過 SUMPRODUCT 函數運算,當資料內容變動時,小計可以隨之變動。
儲存格D2:=IF(A2<>A3,SUMPRODUCT(($A$2:A2=A2)*$C$2:C2),"")
複製儲存格D2,貼至儲存格D2:D26。
SUMPRODUCT(($A$2:A2=A2)*$C$2:C2):計算從A欄的第一筆資料(組別)和所在儲存格符者,計算對應C欄(銷售量)的和,此為該組的小計。
然後,在 IF 函數中判斷 A2<>A3 是否成立,若成立表示為儲存格A2和儲存格A3為不同組別,則顯示小計算結果,若不成立,則顯示空白。

(2) 使用 SUMIF 函數
除了使用 SUMPRODUCT 函數,你也可以使用 SUMIF 函數,可以得到相同的結果。
儲存格D2:=IF(A2<>A3,SUMIF($A$2:A2,A2,$C$2:C2),"")

(3) 利用小計工具
因為資料已經依組別排序完成,所以你也可以使用 Excel 提供的小計工具,手動得到的結果(如下圖)。使用小計時的設定:
分組小計欄位:組別
使用函數:加總
新增小計位置:銷售量
image

沒有留言:

張貼留言

好康東東