函數篇：被遺忘的SUMPRODUCT

隨著SUMIFS、COUNTIFS等一些函數的出現，函數SUMPRODUCT漸漸被遺忘，由於前者的運行速度比他快而被慢慢淡出了excel接觸者的視線。然並卵，我想說函數SUMPRODUCT的強大功能足以讓函數SUMIFS、COUNTIFS等失業，而數組公式函數SUMPRODUCT能取代函數SUM並且不需要按<Ctrl Shift Enter>三鍵結束。

一、函數語法解析

1、函數定義：

在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。

2、語法格式：

SUMPRODUCT(array1,[array2],[array3],...)

SUMPRODUCT(數組區域1，數組區域2，數組區域3，……)

3、參數說明：

array1：必需。其相應元素需要進行相乘並求和的第一個數組參數。

array2，array3，...：可選。2到255個數組參數，其相應元素需要進行相乘並求和。

二、擴展語法

1、多條件計數語法：

SUMPRODUCT((條件1區域="條件1")*(條件2區域="條件2")*(……))

2、多條件求和語法：

①、SUMPRODUCT((條件1區域="條件1")*(條件2區域="條件2")*(……)*求和區域)

②、SUMPRODUCT((條件1區域="條件1")*(條件2區域="條件2")*(……),求和區域)

3、求和語法中求和區域前使用「,」與「*」的區別：

多條件求和時，當求和區域中沒有文本時「,」與「*」可以通用；

當求和區域中有文本時使用「*」會返回錯誤值#VALUE!，故而只能使用「,」。

三、函數示例

1、基礎應用

▲

01

文本數字求和

輸入公式：=SUMPRODUCT(--(B3:B9))

解析：該公式中兩個負號，一個負號是把文本型數字轉換成負數值,另一個負號是把負數值轉換成需要的正數。

當然轉換的方法有很多種，可以用「 0」、「-0」、「*1」、「/1」、「^1」等等，關鍵看你自己怎麼用。

有人覺得用函數SUMPRODUCT字元太長了，用函數SUM明顯字元少很多，該題中用函數SUM代替函數SUMPRODUCT，那就必須按Ctrl Shift Enter三鍵結束。

▲

02

忽略文本求和

輸入公式：=SUMPRODUCT(A15:A22)

▲

03

有文本的數組相乘

輸入公式：

=SUMPRODUCT(C29:C34,D29:D34)

解析：函數SUMPRODUCT將非數值型的數組元素作為0處理。

如果該題中用單一的函數SUM來解決，按三鍵結束是小事，會出現錯誤值#VALUE!。

函數SUM的參數為不能轉換成數字的文本,將會顯示錯誤。

函數SUM的解決方法：=SUM(IFERROR(C29:C34*D29:D34,""))，按三鍵結束。

▲

04

單條件計數數

輸入公式：=SUMPRODUCT(--(B41:D47>=80))

解析：(B41:D47>=80)部分判斷條件是否成立，成立返回TRUE，不成立返回FALSE，

發生運算時TRUE相當於1，FALSE相當於0，

然後用函數SUMPRODUCT或函數SUM求和，

注意使用函數SUM時一定要按三鍵結束。

同樣的可以用「 0」、「-0」、「*1」、「/1」、「^1」等等來轉換。

▲

05

單條件求和

輸入公式：

=SUMPRODUCT((B54:D60>=80)*B54:D60)

解析：(B54:D60>=80)部分判斷條件是否成立，成立返回TRUE，不成立返回FALSE，

再*B54:D60得到的就是滿足條件的，

最後用函數SUMPRODUCT或函數SUM求和，

注意使用函數SUM時一定要按三鍵結束。

得到邏輯值TRUE和FALSE後直接*B54:D60就已發生運算，如果像上題一樣用「 0」、「-0」、「*1」、「/1」、「^1」等等來轉換雖不影響結果，但明顯是多此一舉。

2、進階應用

▲

01

單條件數組相乘

輸入公式：=SUMPRODUCT((A3:A8="燒烤")*C3:C8*D3:D8)

或者：=SUMPRODUCT((A3:A8="燒烤")*C3:C8,D3:D8)

▲

02

多條件計數

輸入公式：=SUMPRODUCT((B15:B21="女")*(C15:C21>=60))

▲

03

多條件求和

輸入公式：=SUMPRODUCT((B28:B34="女")*(C28:C34>=60)*C28:C34)

或者：=SUMPRODUCT((B28:B34="女")*(C28:C34>=60),C28:C34)

▲

04

有條件的區間計數

輸入公式：=SUMPRODUCT((B41:B47="女")*(C41:C47>=60)*(C41:C47<=80))

▲

05

有條件的區間求和

輸入公式：=SUMPRODUCT((B54:B60="女")*(C54:C60>=60)*(C54:C60<=80)*C54:C60)

或者：=SUMPRODUCT((B54:B60="女")*(C54:C60>=60)*(C54:C60<=80),C54:C60)

上述示例可以參照前面說的擴展語法格式來，也可以用函數SUMIFS、COUNTIFS來完成，關於這兩個函數的用法不再贅述，也可以用函數SUM代替函數SUMPRODUCT，需要注意的是要按三鍵結束。

3、組合應用

▲

01

隔列求和

輸入公式：

=SUMPRODUCT((MOD(COLUMN(B:I),2)=0)*B3:I3)

▲

02

零鈔的計算

註：第一行中的「元」是通過設置單元格格式自定義加上去的，若是直接輸入的「元」就要先提取數字或把「元」替換掉。

在C15單元格輸入公式：

=INT(($B15-SUMPRODUCT($A$14:B$14,$A15:B15) 1%%)/C$14)，向右向下填充。

思路：當前面額張數=(工資-(已計算面額*已計算面額張數))/當前面額

解析：SUMPRODUCT($A$14:B$14,$A15:B15)部分算出了左側單元格中已經計算張數的金額，用B列的工資減去這個金額，然後除以當前的面額，再用INT函數取整就可以得到對應面額的鈔票數量。

留個問題：為什麼要加1%%呢？加1%、1%%%等可不可以呢？不加又可不可以呢？

▲

03

多條件求不重複的個數

輸入公式：

=SUMPRODUCT(1/COUNTIFS(A30:A38,A30:A38,B30:B38,B30:B38))

▲

04

中式排名

輸入公式：

=SUMPRODUCT((C$45:C$50>C45)*(1/(COUNTIF(C$45:C$50,C$45:C$50)))) 1

▲

05

包含求和

①、求姓名中包含6，性別為男的銷售額之和

輸入公式：

=SUMPRODUCT(ISNUMBER(FIND("6",A57:A62))*(B57:B62="男")*C57:C62)

或者：=SUMPRODUCT(ISNUMBER(FIND("6",A57:A62))*(B57:B62="男"),C57:C62)

解析：函數SUMPRODUCT不能使用通配符，故而與函數FIND組合實現包含求和功能。

函數SUMIFS通配符解法：=SUMIFS(C57:C62,A57:A62,"*6*",B57:B62,"男")

②、求姓名中有波形符~的銷售額之和

輸入公式：

=SUMPRODUCT(ISNUMBER(FIND("~",A57:A62))*C57:C62)

解析：函數SUMPRODUCT不能使用通配符，故而與函數FIND組合實現包含求和功能。

函數SUMIF通配符解法：

=SUMIF(A57:A62,"*~~*",C57:C62)

四、注意事項

①、數組參數必須具有相同的維數。否則，函數SUMPRODUCT將返回#VALUE!錯誤值。

②、函數SUMPRODUCT將非數值型的數組元素作為0處理。

③、函數SUMPRODUCT不能使用通配符，但可以與函數FIND組合實現包含功能。

④、函數SUMPRODUCT是不用按<Ctrl Shift Enter>三鍵結束的數組運算函數。

作者：仰望星空