一起聊聊Excel的SUMPRODUCT函數
相關學習推薦：
今天給大家分享一個很常用也很實用的函數：SUMPRODUCT。眾所周知，條件求和與計數是表格使用者最經常碰到的兩個問題，而該函數不但集合了條件求和與計數兩大功能於一身；還可用於複雜場景下的排名處理，甚至聽說有人靠它一個函數就打下了Excel半壁江山……於是便不可不學了。
基礎語法
先來看基礎語法，SUMPRODUCT的官方語法說明是在給定的幾組陣列（array）中，將陣列間對應的元素相乘，並返回乘積之和。語法格式如下：
=SUMPRODUCT（array1,array2,array3, …）
——SUM是求和的意思，PRODUCT是相乘的意思，引數之間相乘之後再求和，你看，SUMPRODUCT確實人如其名了。
看我手，歪、圖、斯瑞……總結起來，SUMPRODUCT函數具有以下三個特點：
1> 它本身預設執行陣列運算。
2> 它會將引數中非數值型的陣列元素作為0處理。
3> 引數必須有相同的尺寸，否則返回錯誤值。
特點解析
看完了SUMPRODUCT的簡歷，想必很多朋友是霧裡看花，僅僅對它有個模糊的認知，它的這些特點是啥意思？它到底能夠勝任什麼樣子的工作？其實並不瞭然。
打個響指，我舉幾個例子。
如上圖所示的資料表，C列是商品單價，D列是銷售數量，現在需要在C9單元格計算銷售總額。
C9輸入以下公式，即可得出結果11620.60
=SUMPRODUCT(C3:C7, D3:D7)
這便是一個簡單的SUMPRODUCT函數了。它的運算過程是：C3:C7和D3:D7兩個區域陣列內的元素分別相乘，也就是C3*D3,C4*D4,C5*D5……直至C7*D7
等於先將每個商品的銷售金額計算出來，最後彙總求和。
由於SUMPRODUCT函數第一個特點，本身是支援陣列間運算的，所以雖然該公式執行了多項運算，但並不需要按陣列三鍵<Ctrl+Shift+Enter>結束公式輸入。
有的朋友說啦，公式也可以寫成這樣：
=SUMPRODUCT(C3:C7*D3:D7)
或者使用以下陣列公式，也是可以的。
=SUM(C3:C7*D3:D7)
那麼這三個公式之間有什麼區別呢？
首先，大部分情況下，SUMPRODUCT函數都不需要陣列三鍵結束公式輸入即可執行陣列運算，而SUM函數是需要的。
其次，就要說到SUMPRODUCT函數另一個非常重要的特點了。
……
我們將上面的表稍做改動，將「鋼筆」的銷售數量更改為：暫未統計。同樣需要在C9單元格計算銷售總額。
這時候，如果使用公式：
=SUMPRODUCT(C3:C7*D3:D7)
或者陣列公式：
=SUM(C3:C7*D3:D7)
都將返回錯誤值#VALUE!
返回錯誤值的原因在於D4單元格「暫未統計」為文字值，文字值是無法直接參與數學運算的，於是C4*D4返回錯誤值#VALUE!，進而造成整個公式的結果返回錯誤值。
而使用以下公式就沒有這方面的困擾，會直接返回正確結果：
=SUMPRODUCT(C3:C7,D3:D7)
這便是SUMPRODUCT函數的第二個特點：將非數值型的陣列元素作為0處理。
以該範例來說，D4單元格的值「暫未統計」為文字，並非數值，SUMPRODUCT將其主動視為零，於是C4*D4，結果亦為零，其餘陣列元素照常計算，得出11385.60的結果。
需要特別說明的是，SUMPRODUCT將非數值型的陣列元素作為0處理，所謂的非數值型陣列元素，包含邏輯值、文字，但並不包含錯誤值，如果陣列元素中包含錯誤值，該公式亦返回錯誤值，比如該範例的第一條公式。
……
說完了SUMPRODUCT函數的兩個特點，我們就再來聊聊它的第三個特點：陣列引數必須有相同的尺寸，否則返回錯誤值。
我們依然用上述圖片的例題為例，繼續計算商品的銷售總額。如果我們在C9輸入公式：
=SUMPRODUCT(C3:C7,D3:D6)
結果會是怎麼樣的呢？
錯誤值：#VALUE!
為什麼？
細心的你肯定已經注意到了，兩個區域陣列，C3:C7明顯顯比D3:D6多了一個元素，C3和D3結對子，C4和D4結對子……那麼C7和誰結對子呢？女人們都嫁了，結果剩下一個光棍，這日子沒法過了！一個蘿蔔一個坑，只有蘿蔔沒有坑，這不是要蘿蔔死嗎？
——於是SUMPRODUCT就不高興了，它給你一個錯誤值#VALUE!，明確告訴你，和諧時代幸福歲月，日子不能這麼過。
這就是SUMPRODUCT函數的第三個特點：陣列引數必須有相同的尺寸，否則返回錯誤值。
下面是一道練習題，你看看，能用SUMPRODUCT函數做出來嗎？
案例拓展
假設下面這張圖，是某個公司工資發放的部分記錄表（資料純屬虛擬，如有雷同，那是穿越）。A列是工資發放的時間，B列是員工所屬的部門，C列是員工姓名，D列是相關員工領取的工資金額。
——那麼，問題和廣告都來了：
1
員工西門慶領取了幾次工資？
這是一個單條件計數的問題,通常我們使用COUNTIF函數,但如果使用SUMPRODUCT函數，一般寫成這樣：
=SUMPRODUCT((C2:C13="西門慶")*1)
先判斷C2:C13的值是否等於」西門慶」，相等則返回TRUE，不等則返回FALSE，由此建立一個有邏輯值構成的記憶體陣列。
上文已經說過，SUMPRODUCT有一個特性，它會將非數值型的陣列元素作為0處理，邏輯值自然是屬於非數值型的陣列元素，為了避免SUMPRODUCT函數把邏輯值視為0，造成統計錯誤，我們使用*1的方式，把邏輯值轉化為數值，TRUE轉化為1，FALSE轉化為0，最後統計求和。
2
員工西門慶領取了多少工資？
這是一個單條件求和的問題,通常我們使用SUMIF函數,如果使用SUMPRODUCT函數，我們可以寫成這樣：
=SUMPRODUCT((C2:C13="西門慶")*D2:D13)
依然首先判斷C2:C13的值是否等於」西門慶」，得到邏輯值FALSE或TRUE，再和D2:D13的值對應相乘。TRUE乘以數值，得到數值本身。FALSE乘以數值返回0。最後統計求和得出結果。
看完了上面兩個問題，有些朋友可能會在心裡想，貌似SUMPRODUCT能幹的事，SUMIF和COUNTIF也能做到，而且做的更好，那麼還要SUMPRODUCT幹啥嘞？
鄉親們吶，話不能這麼說，SUMPRODUCT可是上得廳堂下得廚房，對工作環境不挑不揀，它對引數型別沒有啥特別要求，COUNTIF和SUMIF就不同了，他倆要求個別引數，必須是區域（Range型），不支援陣列，比如下面這兩個問題，COUNTIF和SUMIF就要繞了。
3
二月份外交部發放了幾次工資？總額是多少？
第1個問題，二月份外交部發放了幾次工資？
這是一個多條件計數的問題。
第一個條件，發放工資的時間必須是二月份；第二個條件，發放工資的部門必須是外交部。
如果使用多條件計數函數COUNTIFS，判斷髮放工資的時間是否屬於六月份，會簡單問題複雜化。而使用SUMPRODUCT函數，咱們可以把公式寫成這樣：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))
……
第2個問題，統計二月份外交部發放了多少工資？
這是一個常見的多條件求和問題。
如果使用SUMIFS函數，判斷髮放工資的時間是否屬於六月份，也會簡單問題複雜化。
SUMPRODUCT躍然而至：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)
或者：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)
打個響指，關於這兩個形式的SUMPRODUCT函數的區別，咱們上文已有詳細說明——你還記得嗎？
上面這個公式可以說是SUMPRODUCT多條件求和的典型用法啦，可以歸納為:
=SUMPRODUCT((條件一)*(條件二)……,求和區域)
4
二月份外交部和步兵部合計發放了多少工資？
解決了上面的問題，相信大家已經曉得如何計算二月份外交部發放多少工資了，那麼二月份外交部和步兵部合計發了多少工資，又當怎麼計算呢
我們經常見有些性格樸素的表親們把公式寫成這樣：
=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)
這些表親們估計心想，不就是計算兩個部門嗎？甭說兩個，二十個咱也能算，一個加一個，一直加到二十個，世上無難事，只怕有心人嘛，一磚加一磚，長城就建成了，一泡加一泡，長江就奔流了……
呃……公式寫的那麼長，先不談計算速度，首先它累手啊，萬一寫錯了，又要修改，那也是麻煩他媽哭麻煩——麻煩死了。
其實我們可以寫成這樣：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)5
排名應用
認識了SUMPRODUCT函數在條件計數和求和方面的用法，最後，咱們再來看一個它在排名上的使用方法。
如上圖所示，某個月某個公司某些人領了某些工資，然後呢，他們想看看自己的工資，在部門內的排名情況，比如說步兵部的魯智深都是老員工了，非常想知道自個工資在各自部門排幾號。
當然啦，不排不知道，一排就傻掉。
SUMPRODUCT是這麼解決這個問題的，D2輸入公式向下複製：
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1
（思考，為什麼公式的最後+1，而不是直接寫成如下：）
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))
結束語
嘮嘮叨叨說了這麼多，眼睛都說痠麻了，是到了該結束的時候啦。
最後，請思考兩個小問題：
第1個問題：下面SUMPRODUCT函數有幾個引數？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="財務部")*D2:D13)
下面這個SUMPRODUCT函數又有幾個引數？
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="財務部")，D2:D13)
第二個問題：
SUMPRODUCT為什麼有時候比SUMIF/COUNTIF計算速度慢？
相關學習推薦：
以上就是一起聊聊Excel的SUMPRODUCT函數的詳細內容，更多請關注TW511.COM其它相關文章！
