2016年10月6日 星期四

Excel-計算多欄多列交集的小計(SUM,IFERROR,陣列公式)

網友根據前一篇文章:Excel-計算多欄多列交集的小計(SMPRODUCT),如下圖,如果想要列入小計的欄列交集處包含了文字(例如:A),則原公式會發生錯誤,該如何調整?
例如:人員『寅』和項目『乙』資料的交集是『A,A,59,A,79,A,A,78,21,69』,其中包含了 3 個A是非數,該如何計算數字和?
Excel-計算多欄多列交集的小計(SUM,IFERROR,陣列公式)


【公式設計與解析】
儲存格M3:{=SUM(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
原先的公式:
儲存格M3:=SUMPRODUCT(B2:J18*(B1:J1=M2)*(A2:A18=M1))
當公式中執行『*』運算時,會因文字(A)傳回 #VALUE,而發生錯誤。
新的公式改用陣列公式,並且利用 IFERROR 函數將錯誤訊息轉換為『0』,再自行利用SUM 函數予以加總,即為所求。
如果你將 SUM 函數以 SUMPRODUCT 函數置換結果會一樣。
儲存格M3:{=SUMPRODUCT(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

沒有留言:

張貼留言

檢視其他文章

好康東東