2015年6月18日 星期四

表格摘要(SUMPRODUCT)

有網友要將在 Excel 中的一個資料表(如下圖左),摘要成下圖右的各種表示方式,該如何處理?
這類問題,感覺上好像是被問過最多次的問題,不過也是日常生活和工作中最被需要的技巧。但是對於初學者往往不知從何下手,可能也不了解只要使用 SUMPRODUCT 函數就能解決。

【函數設計】
先提一下 SUMPRODUCT 函數,參考微軟網站的說明:
SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。
語法:SUMPRODUCT(array1, [array2], [array3], ...)
array1:要求對應元素乘積和的第一個陣列引數。
array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。
各陣列必須有相同的維度(相同的列數,相同的欄數),否則會傳回錯誤值#VALUE!,並且會將所有非數值資料的陣列元素當成0來處理。

【公式設計】
(1) 儲存格E2:=SUMPRODUCT(($A$2:$A$24=D2)*1)
其中運算式「*1」,可以將 $A$2:$A$24=D2 的傳回值 TRUE/FALSE 陣列藉由運算過程轉換為 1/0 陣列。
複製儲存格E2,貼至儲存格E2:E6。

(2) 儲存格G2:=SUMPRODUCT(($B$2:$B$24=F2)*1)
複製儲存格G2,貼至儲存格G2:G7。

(3) 儲存格E11:=SUMPRODUCT(($A$2:$A$24=E$10)*($B$2:$B$24=$D11))
其中運算式「*」,可以視為將將 $A$2:$A$24=E$10 和 $B$2:$B$24=$D11 兩個運算執行邏輯 AND 運算,並將傳回值 TRUE/FALSE 陣列藉由運算過程轉換為 1/0 陣列。
複製儲存格E11,貼至儲存格E11:I16。(特別注意公式中的 E$10 和 $D11 的位址參照方式)

(4) 儲存格E20:=SUMPRODUCT(($A$2:$A$24=$D20)*($B$2:$B$24=E$19))
複製儲存格E20,貼至儲存格E20:J24。(特別注意公式中的 $D20 和 E$19 的位址參照方式)

沒有留言:

張貼留言

檢視其他文章

好康東東