提到求和，大家想到的肯定是Sum、Sumif、Sumifs等函數(shù)，而計數(shù)則想到的肯定是Count、Counta、Countif、Countifs等函數(shù)，排名則用Rank函數(shù)，但是，在Excel中，有一個函數(shù)它既能求和、計數(shù)、還會權(quán)重計算和排名等功能，它就是Sumproduct函數(shù)。

一、Sumproduct函數(shù)簡介。

功能：返回相應(yīng)的數(shù)組區(qū)域乘積的和。

語法：=Sumproduct(數(shù)組或單元格引用1，數(shù)組或單元格引用2……數(shù)組或單元格引用N）。

注意事項：

1、如果SUMPRODUCT函數(shù)具有多個參數(shù)數(shù)組，這些數(shù)組之間必需具有相同的維數(shù)，否則SUMPRODUCT將返回#VALUE!錯誤值REF！

2、函數(shù)Sumproduct將非數(shù)據(jù)類型的元素作為0處理。

示例：

方法：

在目標單元格中輸入公式：=SUMPRODUCT(C3:C9,D3:D9)。

解讀：

公式=SUMPRODUCT(C3:C9,D3:D9)可以理解為：C3*D3+C4*D4+……C9*D9，暨對應(yīng)的同一行單元格乘積的和。

二、Sumproduct：求和。

1、單條件求和。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((E3:E9=H3)*C3:C9)、=SUMPRODUCT((E3:E9=H3)*C3:C9*D3:D9)。

解讀：

1、首先判斷E3:E9=H3條件是否成立，如果成立，則返回True，暨1，否則返回False,，暨0。

2、以“上海”地區(qū)的為例：公式=SUMPRODUCT((E3:E9=H3)*C3:C9)的計算過程就是：1*66+0*88+0*67+0*56+0*33+1*57+0*20=123。公式：=SUMPRODUCT((E3:E9=H3)*C3:C9*D3:D9)的計算過程就是：1*66*39+0*88*58+0*67*61+0*56*53+0*33*42+1*57*84+0*20*82=7362。

2、多條件求和。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((E3:E9=H3)*(C3:C9>=50),C3:C9)、=SUMPRODUCT((E3:E9=H3)*(C3:C9>=50),C3:C9*D3:D9)。

解讀：

1、其實計算過程和單條件的類似，只是多了一個條件判斷而已，具體請參閱單條件的計算過程。

2、為了是公式更具有條理性，我們可以將條件用*（乘號）連接在一起，將數(shù)值用*（乘號）連接在一起，條件和數(shù)值之間用，（逗號）分隔，但，（逗號）的作用還是乘。

3、連接符，（逗號）和*（乘號）的區(qū)別在于：如果數(shù)據(jù)源中包含文本，必須用，（逗號）連接，而不能用*（乘號）連接。故*（乘號）只能用于連接數(shù)據(jù)類型的值。

3、隔列求和。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(($C$3:$J$3=K$3)*$C4:$J4)。

解讀：

1、隔列求和也很好理解，需要注意的就是相對引用和絕對引用的使用，如條件區(qū)域C3:J3不變，隨意采用絕對引用，而條件值的列會發(fā)生變化，隨意采用相對和絕對引用相結(jié)合的方式。

2、數(shù)據(jù)區(qū)域也是采用絕對和相對引用相結(jié)合的方式。

三、Sumproduct：計數(shù)。

1、單條件計數(shù)。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(N(E3:E9=H3))。

解讀：

1、公式中用到了Excel中最短函數(shù)N，其主要作用為將非數(shù)值的值轉(zhuǎn)換為數(shù)值。

2、首先判斷E3:E9=H3是否成立，如果成立，返回True，否則返回False，然后用N函數(shù)將對應(yīng)的值轉(zhuǎn)換為1和0。

3、當Sumproduct函數(shù)只有一個數(shù)據(jù)區(qū)域時，對區(qū)域中的值進行求和處理。以“上海”為例：判斷和轉(zhuǎn)換后的結(jié)果為{1;0;0;0;0;1;0},求和的結(jié)果為2。達到了計數(shù)的目的。

2、多條件計數(shù)。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(N(E3:E9=H3)*(C3:C9>=50))。

解讀：

1、以“上海”為例，公式可以理解為：=Sumproduct（{1;0;0;0;0;1;0},{1;0;0;0;0;1;0}），所以返回的結(jié)果為2。

四、Sumproduct：多權(quán)重計算。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(C$3:E$3,C4:E4)。

解讀：

1、從表中我們可以看出要計算考生的最終成績，其中體能占20%，筆試占30%，面試占50%。

2、各項所占的比是固定不變的，所以行絕對引用，不可以隨著單元格的變動而變動，而每個人的成績要隨著人員的變化而變化，所以才用相對引用的形式。

3、如果增加“名次”列，則可以快速的對考生的成績進行排序。排序請繼續(xù)閱讀。

五、Sumproduct：中國式排名。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(($F$4:$F$10>F4)/COUNTIF($F$4:$F$10,$F$4:$F$10))+1。

解讀：

1、首先判斷$F$4:$F$10>F4條件是否成立，如果成立，則返回True，暨1，否則返回False，暨0。

2、=COUNTIF($F$4:$F$10,$F$4:$F$10)的作用就是形成一個7個元素為1的數(shù)組。

3、第一步和第二步對應(yīng)的數(shù)組元素進行除法運算，形成一個新的數(shù)組，而Sumproduct函數(shù)當數(shù)組區(qū)域為單一區(qū)域時，對區(qū)域中的值進行求和運算。

4、公式中的1為附加值。

5、以93.8分的名次為例，公式=SUMPRODUCT(($F$4:$F$10>F4)/COUNTIF($F$4:$F$10,$F$4:$F$10))就轉(zhuǎn)化為=Sumproduct（{0;0;0;1;0;0;0}/{1;1;1;1;1;1;1}）,然后對應(yīng)位置的數(shù)組相除得到新的數(shù)組=Sumproduct({0;0;0;1;0;0;0})，對其進行求和：0+0+0+1+0+0+0=1，再加上附加值1，最終結(jié)果為2。

結(jié)束語：

本文主要學(xué)習了萬能函數(shù)Sumproduct，其不僅能夠求和、計數(shù)、還能夠計算權(quán)重和排名，功能非常的強大，這難倒是要搶“飯碗”的節(jié)奏？……

各位親在學(xué)習的過程中如果有不明白的地方，歡迎在留言區(qū)留言討論哦！