這篇文章為大家帶來了關於excel的相關知識，其中主要介紹了SUMPRODUCT函數的相關問題，該函數不僅集合了條件求和與計數兩大功能於一身，還可用於複雜場景下的排名處理，下面一起來看一下，希望對大家有幫助。
相關學習推薦：excel教學
#今天要跟大家分享一個很常用也很實用的函數：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函數第一個特點，本身是支援數組間運算的，所以雖然公式執行了多項運算，但並不需要按數組三鍵結束公式輸入。
有的朋友說啦，公式也可以寫成這樣：
=SUMPRODUCT(C3:C7*D3:D7)
登入後複製
登入後複製
或使用以下陣列公式，也是可以的。
那麼這三個公式之間有什麼差別呢？
首先，大部分情況下，SUMPRODUCT函數都不需要陣列三鍵結束公式輸入即可執行陣列運算，而SUM函數是需要的。
其次，就要說到SUMPRODUCT函數另一個非常重要的特點了。
……
我們將上面的表格稍做改動，將「鋼筆」的銷售數量改為：暫未統計。同樣需要在C9單元格計算銷售總額。
這時候，如果使用公式：
=SUMPRODUCT(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教程
以上是一起聊聊Excel的SUMPRODUCT函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！
