SUMPRODUCT取代SUM數(shù)組公式

關于用SUMPRODUCT取代SUM數(shù)組公式的想法,,,,,'/dispbbs.asp?BoardID=3&ID=37924&replyID=&skin=0

,,,,,,

大家用過數(shù)組公式的都可能知道，對于多條件求和或計數(shù)，我們一般都采用這種數(shù)組公式來計算。如：,,,,,

對于1個明細數(shù)據(jù)(見例)，我們需要求該班男、女生各科成績大于各科平均分的人數(shù)，這個問題不難，大家一般都會用sum()來多條件求值就行了(注：數(shù)組公式)：,,,,,

=SUM(($C$3:$C$428=$K3)*(D$3:D$428>=AVERAGE(D$3:D$428))),,,,,

,,,,,,

確實這是解決問題的一種方法，但往往對于初用數(shù)組公式的朋友常常會遺忘(或不知道)用(Ctrl+shift+Enter)組合鍵來結束編輯(論壇中常常有類似的提問)。,,,,,

,,,,,,

出于這個原因，我想到能否用SUMPRODUCT()函數(shù)來代替常用SUM()多條件求和公式呢？結果答案是肯定的。,,,,,

把上面的公式稍做修改，請見：,,,,,

,,,,,,

"=SUMPRODUCT(($C$3:$C$428=$K3)*1,(D$3:D$428>=AVERAGE(D$3:D$428))*1)",,,,,

而且直接用Enter來結束，函數(shù)仍然可以正確計算結果。,,,,,

,,,,,,

原因在于：SUMPRODUCT()函數(shù)本身就支持數(shù)組間運算(相同尺寸的數(shù)組相乘后再加總)，因此我們毋須用數(shù)組公式組合鍵來結束。,,,,,

另：提醒大家一點，如果大家試圖將公式改為：,,,,,

,,,,,,

"=SUMPRODUCT(($C$3:$C$428=$K3),(D$3:D$428>=AVERAGE(D$3:D$428)))",,,,,

公式不能得出正確結果。,,,,,

,,,,,,

如果多個條件這樣并列寫入，系統(tǒng)默認是用AND關系(即相乘)來運算，但是SUMPRODUCT不支持邏輯值數(shù)組常量間相乘(也算是小遺憾吧：))，因此請一定要將邏輯值轉換成數(shù)值才行。,,,,,

大家可能覺得麻煩，因此我建議使用縮寫公式，實際上是將多個數(shù)組參數(shù)變?yōu)橐粋€數(shù)組，也就可以避免不同維數(shù)數(shù)組間相乘而帶來的錯誤麻煩了。,,,,,

,,,,,,

=SUMPRODUCT(($C$3:$C$428=$K3)*(D$3:D$428>=AVERAGE(D$3:D$428))),,,,,

同理：我們如果需要求出所有男生中總分大于平均總分的所有總分，我們也可以用公式：,,,,,

"=SUMPRODUCT(($C$3:$C$428=$K13)*(I$3:I$428>=AVERAGE(I$3:I$428)),I$3:I$428)",,,,,

,,,,,,

如果數(shù)組尺寸（即行列數(shù)）相同，建議使用這種方法，如果直接相乘出現(xiàn)錯誤，可能是I列出現(xiàn)非數(shù)值字符，用逗號分隔后，系統(tǒng)可以自動忽略非數(shù)值型數(shù)據(jù)的。,,,,,

,,,,,,

對于條件求和的應用，大家可以結合下面的實例(又是借來的實例)來理解(但對于使用該函數(shù)，運算速度是否有提高，還待各位朋友驗證)，希望對大家有幫助，謝謝!,,,,,

點擊瀏覽該文件,,,,,

,,,,,,

,,,,,,

最后再提醒大家一點：并不是所有用SUM