今天,盧子來一場求和函數對比的文章,對於普通人看完你就明白哪個函數更適合你?
用過數組公式的都知道,對於多條件求和或計數,我們一般都採用SUM函數的數組公式來計算。根據學部跟性別兩個條件統計總成績。
這時會採用SUM函數的數組公式進行解決。
=SUM(($E$2:$E$16=G2)*($C$2:$C$16=H2)*$D$2:$D$16)
確實這是解決問題的一種方法,但往往對於初用數組公式的朋友,常常會遺忘用Ctrl+Shift+Enter組合鍵來結束,而用Enter鍵直接結束,導致出錯。
出於這個原因,能否用SUMPRODUCT函數來代替常用SUM函數的多條件求和公式呢?結果答案是肯定的。
把上面的公式稍做修改即可。
=SUMPRODUCT(($E$2:$E$16=G2)*($C$2:$C$16=H2)*$D$2:$D$16)
可以直接用Enter來結束,函數仍然可以正確計算結果。
原因在於:SUMPRODUCT函數本身就支持數組間運算(相同尺寸的數組相乘後再加總),因此我們不需要用數組公式組合鍵來結束。
對於多條件求和,最後一個*也可以用,代替。
=SUMPRODUCT(($E$2:$E$16=G2)*($C$2:$C$16=H2),$D$2:$D$16)
但是並不是所有情況下都可以用,代替*,如現在只是根據學部統計成績。
=SUMPRODUCT(($E$2:$E$16=G2)*$D$2:$D$16)
用,代替*,會得到錯誤的結果。
如果多個條件這樣並列寫入,系統默認是用相乘來運算,但是SUMPRODUCT函數不支持邏輯值數組常量間相乘,因此請一定要將邏輯值轉換成數值才行。
將邏輯值轉換成數值可以用--、*1、+0等等。
=SUMPRODUCT(--($E$2:$E$16=G2),$D$2:$D$16)
,代替*有兩個好處:
①當數據源出現文本值的時候當做0處理,進行運算就不會出錯,而用*就會出錯。
②運算速度更快。
也就是說,在大多數情況下用SUMPRODUCT函數可以取代SUM函數的數組公式,無需按三鍵結束這一點有極大的優勢。
當然了,如果只是普通的條件求和,用SUMIFS函數是最理想的。
函數語法:
=SUMIFS(求和區域,條件區域1,條件1,求和區域2,條件2)
剛剛的多條件求和,根據學部和性別,獲取總成績。
=SUMIFS(D:D,E:E,G2,C:C,H2)
成績含有文本的情況下,SUMIFS函數也可以輕鬆解決。
=SUMIFS(D:D,E:E,G2)
在處理常規的條件求和,SUMIFS函數無疑是最好的,公式簡潔,運算效率最高,絕對的王者。