【Excel函數教程】SUMPRODUCT函數的應用

　

 提示：點擊上方"excel教程"↑免費訂閱

　第一部分：SUMPRODUCT函數用法介紹

　　SUMPRODUCT是什麼?其實結合英語就能很好的理解SUMPRODUCT函數，sum是和，product是積，結合起來就是乘積之和。

　　Excel中SUMPRODUCT函數是一個數組類型的函數。很多時候可以用SUMPRODUCT函數取代SUM函數的數組公式，就不需要按三鍵結束。

　　SUMPRODUCT函數能夠計算多個區域的數值相乘後之和。SUMPRODUCT函數的用法就是在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。

　　SUMPRODUCT函數的語法：SUMPRODUCT(array1,array2,array3, ...)

　　其中Array1, array2, array3, ... 為 2 到 30 個數組，其相應元素需要進行相乘並求和。

　　SUMPRODUCT函數使用需要注意三點：
　　第一，數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 將返回錯誤值 #VALUE!。
　　第二，函數 SUMPRODUCT 將非數值型的數組元素作為 0 處理。
　　第三，如果是一個數組，那麼就是對這個數組的求和。

　　我們先通過一個簡單的工作表數據來認識SUMPRODUCT函數。

　　第一，輸入公式：=SUMPRODUCT(A2:B4*C2:D4)，就是將A2:B4和C2:D4兩個區域的所有元素對應相乘，然後把乘積相加，即3*2+4*7+8*6+6*7+1*5+9*3，得到結果為156。

　　第二，輸入公式：=SUMPRODUCT(A2:B4)，得到結果為31。根據上面的要點介紹，如果是一個數組，那麼就是對這個數組的求和，因此就是對{3,4;8,6;1,9}這個區域求和。

　　提示：兩個數組相乘是同一行的對應兩個數相乘。數組數據用大括號{}括起來，行數據之間用分號";"分隔，如果是同一行的數據，用逗號","分隔。

　　第二部分：SUMPRODUCT函數應用案例介紹

　　下圖所示的是咱們IT部落窩6群(群號：92798512)的隨機抽查的人員資料表。為了演示方便，只是截取了部分數據。

　　通過上面的數據，我們結合SUMPRODUCT函數的用法來完成以下應用案例。

　　第一部分，SUMPRODUCT函數在計數中的應用。

　　SUMPRODUCT函數用於多條件計數，計算符合2個及以上條件的數據個數。有一個經典公式計數：SUMPRODUCT((條件1)*(條件2)*(條件3)*...)

　　第一，統計C列性別列中女性有幾個人。

　　此題為單條件求和。首先要知道條件是什麼，(C4:C33="女")區域中等於女的，這部分就是條件。

　　如果直接輸入=SUMPRODUCT(C4:C33="女")，得到結果為0。第一部分用法介紹裡介紹：函數 SUMPRODUCT 將非數值型的數組元素作為 0 處理，C4:C33="女"，按F9鍵得到執行結果是true、false形式的邏輯值，所以等於0。

　　那如何把邏輯值轉換為數值呢，就要讓邏輯值參加運算，可以用--，*1，+0等等。比如，=TRUE*1，結果為1。=FALSE*1，結果為0。因此在(C4:C33="女")外面加上--就可以了。

　　公式為：=SUMPRODUCT(--(C4:C33="女"))，結果為9人。

　　第二，求E列潛水天數大於15天的男性有幾人。

　　此題有兩個條件：第一，大於15天，用E4:E33>15表示。第二，男性，用C4:C33="男"表示。

　　套用SUMPRODUCT((條件1)*(條件2)*(條件3)*...)，得到公式：=SUMPRODUCT((E4:E33>15)*(C4:C33="男"))，結果為8人。

　　第三，統計2月份發言的男性有幾人。

　　D列最後發言時間有1月和2月的份。統計2月份，需要用到month函數來求月份。比如A1單元格：2011-2-25，A2單元格輸入公式：=MONTH(A1)，返回2。

　　還是套用SUMPRODUCT((條件1)*(條件2)*(條件3)*...)，得到公式：=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33="男"))，結果為16人。

　　第四，統計不包括笑看今朝的男性有多少人。

　　不包括，就是不等於，是<>表示。

　　公式為：=SUMPRODUCT((A4:A33<>"笑看今朝")*(C4:C33="男"))，結果為20人。

　　第五，統計有幾個人的潛水天數是不一樣的。

　　比如，E列潛水天數為6的有6次，為5的有2次。

　　如何讓每個數字只出現一次呢?計數用countif函數。如何讓每個數隻計算一次呢，可以使用1/countif。比如5出現兩次，就是兩個1/2，最後匯總就得到1。最後再套用公式sumproduct(1/countif(區域，區域))。

　　公式為：=SUMPRODUCT(1/COUNTIF(E4:E33,E4:E33)) ，結果為14人。

　　第二部分，SUMPRODUCT函數在求和中的應用。

　　用函數SUMPRODUCT求和，函數需要的參數一個是進行判斷的條件，另一個是用來求和的數據區域。

　　SUMPRODUCT函數求和應用有一個經典的套用格式：SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區域)

　　第一，女性潛水總天數計算。

　　套用格式，得到公式：=SUMPRODUCT((C4:C33="女")*E4:E33)，結果為134。

　　第二，潛水時間大於15天的男性的潛水天數計算。

　　套用格式，得到公式：=SUMPRODUCT((E4:E33>15)*(C4:C33="男")*E4:E33)，結果為242。

　　第三，2月份發言的男性的潛水天數計算。

　　套用格式，得到公式：=SUMPRODUCT((MONTH(D4:D33)=2)*(C4:C33="男")*E4:E33)，結果為159。

　　第四，QQ號首位是8的人的潛水天數計算。

　　首先用left函數提取首位是8的，因為left提取的是文本，加""就成了文本。然後套用格式，得到公式：=SUMPRODUCT((LEFT(B4:B33)="8")*E4:E33)，結果為77。

　　第五，姓名字符數為2，不包括「月亮」的人的潛水天數計算。

　　公式為：=SUMPRODUCT((LEN(A4:A33)=2)*(A4:A33<>"月亮")*E4:E33)，得到結果：92。

　　第六，「笑看今朝」和 「冷逸」的潛水天數計算。

　　公式為：=SUMPRODUCT((A4:A33={"笑看今朝","冷逸"})*E4:E33)，得到結果13。

　　公式中兩個條件，可以這樣寫：{"笑看今朝","冷逸"}。

　　第三部分，SUMPRODUCT函數在查找及排名中的應用。

　　如下圖所示，需要求出姓名列的潛水天數已經排名情況。

　　在以前的講座詳細介紹了vlookup函數實現查找的方法，在本講座中用sumproduct函數取代。利用單條件求和的特點來查找。根據前面的介紹，套用格式=SUMPRODUCT((姓名=I26)*天數)，得到公式：=SUMPRODUCT(($A$4:$A$33=I26)*$E$4:$E$33)，然後下拉即可完成。

　　在以往排名我們使用rank函數，不過我們也可使用SUMPRODUCT函數來完成。在K26輸入公式：=SUMPRODUCT(--($J$26:$J$31>J26))+1,下拉。思路就是如果區域中有幾個大於本身，目標值就是大於的所有數+1。