Excel函數公式：會求和、計數、權重計算、排名的SUMPRODUCT函數

07-21

Excel函數公式5天前5評論關注

Excel中，我們經常會求和，計數，權重計算，排名等，聽起來相當的複雜，也要用到很多函數公式，其實，Excel中有一個功能非常強大的函數，它就是SUMPRODUCT函數，既能求和、計數、權重計算，還能排名等。

一、SUMPRODUCT語法結構。

功能：將數組之間對應的元素相乘，並返回乘積之和。

語法結構：SUMPRODUCT(array1,[array2],[array3]……）。

參數：

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

2、array2、array3：可選。其相應元素需要進行相乘並求和。

注意事項：

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

2、函數SUMPODUCT將非數值型的數組元素作為0來處理。

示例：

目的：返回產品的銷量總額。

方法：

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

解讀：

函數SUMPRODUCT的計算過程是：50*66+25*56+500*67+5*56+98*33+2500*57+1800*20。

二、SUMPRODUCT:單條件求和。

目的：計算出相應的地區的銷量總和和銷售額總和。

方法：

在目標單元格中輸入公式：

1、=SUMPRODUCT((E3:E9=H3)*D3:D9)、

2、=SUMPRODUCT((E3:E9=H3)*(C3:C9)*(D3:D9))。

解讀：

1、如果E3:E9區域中的單元格中的值和H3相等，則返回TRUE，暨1。如果不相等，則返回FALSE，暨0。

2、以「北京」地區為例：公式：=SUMPRODUCT((E3:E9=H3)*D3:D9)就是1*66+0*56+0*67+0*56+0*33+0*57+0*20=66。

3、以「北京」地區為例：公式：=SUMPRODUCT((E3:E9=H3)*(C3:C9)*(D3:D9))就是1*50*66+0*25*56+0*500*67+0*5*56+0*98*33+0*2500*57+0*1800*20=3300。

三、SUMPRODUCT：多條件求和。

目的：計算相應地區銷量大於50的銷量總額。

方法：

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

解讀：

1、首先進行條件判斷，如果相等或大於50，則返回TRUE，暨1。如果不相等或小於等於50，則返回FALSE，暨0。

2、然後對應的數組元素之間相乘，並返回成績之和。

四、SUMPRODUCT:單條件計數。

目的：計算銷往相應地區的產品數。

方法：

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

解讀：

1、函數N的主要作用是將不是數值的值轉換為數值形式。

2、對應的返回值請參閱下圖。

五、SUMPRODUCT：多條件計數。

目的：查詢銷售到相應地區的相應產品數。

方法：

在對應的目標單元格中輸入公式：=SUMPRODUCT(N(E3:E9=H3)*N(B3:B9=I3))。

解讀：

1、利用函數N將對判斷的結果轉換為數值，然後再求對應的乘積之和。

六、SUMPRODUCT:跨列統計。

目的：統計出計劃和銷量之和。

方法：

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

解讀：

1、首先判斷$C$3:$J$3區域中的值和K$3是否相等，如果相等返回TRUE，如果不相等，則返回FALSE。

2、然後和C4:J4區域中的值進行相乘並返回乘積之和。

3、特別注意的事：絕對引用和相對引用的實用，固定不變的範圍絕對引用，變化的相對引用。

七、SUMPRODUCT：多權重計算。

目的：根據分值比例算出最後得分。

方法：

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

八、SUMPRODUCT：中國式排名。

目的：對「最後得分」進行排名。

方法：

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