excel中的sumproduct函數太強大了!一個頂多個,不服不行

在excel中，對於sumproduct函數的說明是返回相應的數組或者區域乘積的和。看上去就是一組數據和另一組數據先分別相乘然後求和嘛，其實sumprodct函數的用法不只是看上去這麼簡單，它兼有sum函數、product函數、sumif函數、sumifs函數、countif函數、countis等函數的功能。本文就詳細給大家介紹一下sumproduct函數的用法吧。

一、基本用法。對於sumproduct函數，公式參數特別簡單，即=SUMPRODUCT（數組1,數組2,數組3, ……），每個數組之間用逗號隔開，表示數組之間先相乘再求和。

如下圖所示，在E2單元格中輸入函數=SUMPRODUCT(C2:C21,D2:D21)，計算過程為188*5+232*7+292*4+……224*2=23308，直接就求出來總銷售額，而不用求出每個地區每個產品的銷售額再求和。

在F2中輸入函數=SUMPRODUCT(F9:F28)，因為只有一組數據，所以返回的結果就是對這組數據求和，相當於sum函數。

在G2單元格輸入函數=SUMPRODUCT(E4,F4)，則表示E4單元格的數值乘以F4單元格的數值。相當於product函數。

所以我們可以看出來，這個函數隻要有逗號，那麼就是逗號隔開的區域相乘，且逗號兩邊區域的單元格個數必須相同。

二、條件求和。

在下圖中的E2單元格中輸入公式=SUMPRODUCT((B2:B21=B2)*C2:C21)，就會算出A01產品的銷量合計（圖中綠色部分），這個公式中仍然只有一組參數，B2:B21=B2是在B列內容中判斷條件是否是A01，返回的結果是TRUE;FALSE;FALSE……，(B2:B21=B2)*C2:C21表示邏輯值與銷售數量相乘，返回{188;0;0;0;283;0;0;0;327;0;0;0;288;0;0;0;211;0;0;0}，可以看到FALSE與數值相乘返回的是0，最後的sumproduct函數僅表示求和，因為只有一個參數。

在F2單元格中輸入公式=SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21)，就會算出A01產品中銷量大於200的合計數，對於這種多條件求和，其實原理和單條件求和一樣，條件之間用乘號隔開即可。

在G2單元格中輸入公式=SUMPRODUCT((B2:B21=B2)*C2:C21,D2:D21)，算出的是A01產品的銷售額。首先看到有一個逗號了，說明這裡的sumproduct函數的參數就有兩個，然後(B2:B21=B2)*C2:C21表示A01產品的數量（不是A01返回的數量是0），加上逗號後面D2:D21，表示的單價先相乘再求和，最後就算出A01產品的銷售額了。

看完這三個公式，A01產品中銷量大於200的銷售額的公式也應該明白了，即SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21,D2:D21)，和你想的一樣嗎？

三、條件計數。

在下圖中E2單元格輸入公式=SUMPRODUCT((B2:B21=B2)*1)後，可以統計出A01產品的數量。(B2:B21=B2)表示在產品型號中條件是A01，計算結果是返回的是TRUE;FALSE;FALSE……FALSE，但是此處為什麼要在後面乘以1呢？因為sumproduct是對數值計算，而(B2:B21=B2)返回的結果是文本。而true*1=1，false*1=0，所以(B2:B21=B2)*1計算結果是1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0，進行求和就會算出5。

在F2單元格中輸入公式=SUMPRODUCT((B2:B21=B2)*1+(B2:B21=B3)*1)，會統計出A01和B02產品的合計個數，因為求合計個數是或的關係，兩個條件滿足一個即可，所以兩個條件之間用加號連接。

在G2單元格中輸入公式=SUMPRODUCT((C2:C21>200)*1*(C2:C21<400)*1)，會計算出銷售數量大於200且小於400的個數，此處表示且的關係，兩個條件都要滿足，所以條件之間用乘號連接。

圖三

四、模糊條件求和。

如下圖所示，如果想求出北方地區（東北、華北、西北）A01產品的銷售額，那麼在E2單元格中輸入公式=SUMPRODUCT(--(ISNUMBER(FIND("北",A2:A21)))*(--(B2:B21=B2))*C2:C21,D2:D21)即可。FIND("北",A2:A21)表示查找「北」在單元格中的位置，如果能找到，返回字符的位置，找不到返回#VALUE!。ISNUMBER(FIND("北",A2:A21)）表示如果find函數結果是數值，isnumber返回true，否則返回false。而isnumber函數前加--表示減負數（作用和上面乘以1相同），最終把true返回到1，false返回0。--(B2:B21=B2)判斷產品是否為A01，如果是返回1，否則返回0。此時就相當於多條件求和，逗號前麵條件和數量相乘，逗號後面為單價，最終符合條件的值相乘並求和。

圖四

五、中國式排名。

excel中的排名函數rank函數返回的是西方國家慣用的排名方式，而我們中國式排名如果有並列名次，不會佔用下面的名次，下面是sumproduct函數與rank函數排名結果對比。

利用sumprodunct排名時，在E2單元格輸入公式=SUMPRODUCT(($C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))+1即可。這個函數比較難理解。對於E2單元格，COUNTIF($C$2:$C$21,$C$2:$C$21)函數表示條件計數，如果有重複值，則返回重複的個數，此處返回的結果是1;1;2;1;1;2;1;1;1;1;1;1;1;1;2;1;2;1;1;1，而用1/COUNTIF($C$2:$C$21,$C$2:$C$21)表示相同的數字只統計一次（因為每個重複的數字都被平均了）。返回結果為1;1;0.5;1;1;0.5;1;1;1;1;1;1;1;1;0.5;1;0.5;1;1;1，然後（$C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21)的返回結果為0;1;0.5;1;1;0.5;1;1;1;1;0;0;1;0;0.5;0;0.5;0;0;1，其中$C$2:$C$21>C2採用的是相對引用，所以向下填充會返回不同的結果。直接決定了計算的相對名次。然後再用sumproduct函數對上面返回結果求和，最後+1對結果修正。

圖五

這就是sumproduct函數的常見用法，如果本文的用法理解並熟練運用，那麼有一些其他的用法自然就會了，比如隔列求和，組內排序等。最後提醒的是，注意函數中逗號，乘號，加號的運用，函數中的參數應為數值格式，且各個參數區域中的單元格個數相同。