Excel中Sumproduct函數的強大你是否知道？

一、

Sumproduct函數是Excel中的幾大神器之一，它兼具條件求和及計數兩大功能。用它可以達到事半功倍的效果。Sumproduct(array1,array2,array3,...)即在給定的幾組數組（array）中，將數組間相對應的元素相乘，並返回乘積之和。

Sumproduct函數有幾個特點：

1.它支持數組間運算。

2.它會自動將非數值型的數組元素作為0處理。

3.數組參數必須有相同的高度，否則返回錯誤值。

二、

小編通過實例來演示下，小夥伴們對該函數的含義就更清晰了。產品、單價、銷售數量要求出一月份的銷售總額。

三、

這就是Sumproduct函數的一個簡單的運用。它的運算過程是：B3：B6和C3：C6兩個區域數組間的元素對應相乘再求和。展示開來就是：B3*C3+B4*C4....+B6*C6。

四、

上面介紹的是Sumproduct函數的簡單應用，接下來小編用另一個實例來演示它的強大。這組數據中，求出男、女各有多少高級工程師和中級工程師。公式在數據統計區域中的F2單元格編輯如下公式：=SUMPRODUCT(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))相同的原理，向下複製到F3（公式中$E2改成$E3），向右複製到G2（公式中$F1改成$G1）,G3（公式中$E2改成$E3及$F1改成$G1）。

五、公式分解及分析

1、$B$2:$B$21=$E2在計算過程中，條件1是一個數組，返回多值，寫成公式如下：=$B$2:$B$21=$E2具體操作：選中20個連續的單元格，輸入上述公式後，按Ctrl+Shift+回車鍵確認，

返回10個邏輯判斷值——true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true；。這20個邏輯判斷值構成一個新的由true主false組成的數組1。

2、$C$2:$C$21=F$1與條件1相同，是一個數組，返回多值，寫成公式如下：=$C$2:$C$21=F$1具體操作：同樣的方法，選中對應的20連續單元格，輸入上述公式，按Ctrl+Shift+回車鍵確認，返回10個邏輯判斷值——true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true。這20個邏輯判斷值構成另一個新的由true主false組成的數組2。

3、($B$2:$B$21=$E2)*($C$2:$C$21=F$1)由新構成的數組1乘以數組2，即：

=($B$2:$B$21=$E2)*($C$2:$C$21=F$1)

={數組1*數組2}

={ true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true }*{ true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true }

={true*true;true*false;false*true;false*false;true*true;false*true;true*false;false*false;false*true;true*false;false*false;true*true;false*false;true*false;true*true;true*false;false*false;true*true;false*false;true*true;}

={1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1}

其中,邏輯值TRU*與**LSE參與計算時:

true=1，false=0，true*true=1，true*false=false*true=0，false*false=0

因此{數組1*數組2}={1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1}由1和0構成了一個新的數組3。

4、=Sumproduct(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))

函數Sumproduct對新的數組3中的所有數據求和。即：

=Sumproduct(($B$2:$B$21=$E2)*($C$2:$C$21=F$1))=Sumproduct({數組1*數組2})

=Sumproduct({數組3})

=Sumproduct({1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1})

=6

六、

這裡需要說明的是，公式編輯按照函數Sumproduct的一般格式，可以編輯如下等效的公式：

=Sumproduct(($B$2:$B$21=$E2)*1，($C$2:$C$21=F$1)*1)

函數Sumproduct的作用是對數組($B$2:$B$21=$E2)與數組($C$2:$C$21=F$1))計算其乘積的和，即：

=Sumproduct(($B$2:$B$21=$E2)*1，($C$2:$C$21=F$1)*1)

=Sumproduct({ true；true；false；false；true；false；true；false；false；true；false；true；false；true；true；true；false；true；false；true }*1，{ true；false；true；false；true；true；false；false；true；false；false；true；false；false；true；false；false；true；false；true}*1)

=Sumproduct({1;1;0;0;1;0;1;0;0;1;0;1;0;1;1;1;0;1;0;1},{1;0;1;0;1;1;0;0;1;0;0;1;0;0;1;0;0;1;0;1})

=Sumproduct({1*1,1*0,0*1,0*0,1*1,0*1,1*0,0*0,0*1,1*0,0*0,1*1,0*0,1*0,1*1,1*0,0*0,1*1,0*0,1*1})

=Sumproduct({1;0;0;0;1;0;0;0;0;0;0;1;0;0;1;0;0;1;0;1})

=6

注意：true*1=1，false*1=1*false=0，true*0=0*true=0 。數組中用分號分隔，表示數組是一列數組，分號相當於換行。兩個數組相乘是同一行的對應兩個數相乘。

推薦閱讀：