函數太多太難學？那就顯學一個萬能的吧！

06-10

很多朋友覺得Excel函數太多了，而且每種都有不同的應用條件，根本記不過來。

如果你也有同樣的感受，不想學習太多的函數公式，那麼也一定要學習的函數就是-SUMPRODUCT。

無論是條件求和、條件計數、加權平均，這些常用的統計計算它都能做得到！還在為函數太多記不住發愁嗎？快來學習它吧~~更多Excel學習和問題請加群：289393114、570064677

今日大綱：

① SUMPRODUCT基礎應用

② SUMPRODUCT多條件計數

③ SUMPRODUCT多條件求和

④ 計算加權平均值(權重已知)

⑤ 計算加權平均值(權重未知)

⑥ 交叉查詢

⑦ 分組排名

SUMPRODUCT函數的工作原理為：

在給定的幾組數組中，將數組間對應的元素先相乘(PRODUCT)，後相加(SUM)。但是就憑這個相乘、相加的計算，就能實現很多功能。

語法

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

其中：

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

array2, array3,... 為可選。可以是 2 到 255 個數組參數，其相應元素需要進行相乘並求和。更多Excel學習和問題請加群：289393114、570064677

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

基礎應用

SUMPRODUCT函數的最典型應用：

下面例子中有單價和數量，我們要求所有產品的總價。

其中A12單元格中的公式為：

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的作用，就像我在旁邊藍色虛框中標出的一樣，先將B3到B9中的每一個數，分別與C3到C9中的數相乘。然後再將這些乘積進行加和，就得到了我們要的結果。更多Excel學習和問題請加群：289393114、570064677

多條件計數

求成績大於80分的女生的數量：

我們一共有兩個條件：

① 成績，也就是D列的數，要大於80；

② 性別，也就是C列，為「女」。

D13單元格中的公式為：

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80))

公式中，

$C$3:$C$11=$C$3代表，C3到C11區域等於C3，也就是「女」。符合條件的，會返回「TRUE」（相當於1），不符合條件的，返回「FALSE」（相當於0）.

$D$3:$D$11>80，同理，這幾個單元格「成績」大於80的，返回「TRUE」（1），不符合條件的，返回「FALSE」（0）。

然後兩個數組分別相乘。更多Excel學習和問題請加群：289393114、570064677

1*0=0

0*0=0

1*1=1

就得到下面第3列的結果。

然後在將這一列結果進行相加，就得到2.

（此計算過程，下面幾個例子很相似，就不再贅述）

多條件求和

下面求和的例子，跟計數的區別就在於，最後多乘一個數組。

D13單元格中的公式為：

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80)*($D$3:$D$11))

也就是將剛才的列再與「成績」那一列，D3：D11進行相乘，然後在進行加和，便可得到。更多Excel學習和問題請加群：289393114、570064677

加權平均值(權重已知)

加權平均值的用法也很常見。例如期末成績由下面3部分構成，每一部分的權重分別為20%，30%，50%。

E4單元格中公式為：

=SUMPRODUCT(B4:D4,B$3:D$3)

與之前例子不同的是，這次是不同行之間先相乘，然後再把列進行相加。

B$3:D$3用了「混合引用」，目的是為了保證公式向下複製時，這一行數據的引用不會發生變化。（對混合引用不了解的戳這裡）

加權平均值(權重未知)

這裡的權重未知，指的是沒有一個明確的百分比，只有一個絕對的數量。

這樣的情況也很好處理，我們只需用跟上個例子一樣的過程，讓「數量」和「成本」分別相乘後再相加，然後在除以「數量」的和即可。

圖中A9單元格中的公式為：

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

交叉查詢

交叉查詢用在這裡可以說又是一神作了。

通常我們熟悉的解決方法是用INDEX+MATCH函數來完成，這種方法我們之前也寫過教程了（戳這裡查看這個教程）

但是，萬能的SUMPRODUCT也一樣能行。

下圖中，我們要求「超市3」「火龍果」的價格。

D13單元格中的公式為：

=SUMPRODUCT(($B$4:$B$9=B13)*($C$3:$G$3=C13)*($C$4:$G$9))

前兩段分開看很好理解，就是判斷是否符合條件，符合的返回"TRUE",不符合的返回"FALSE"。

但是，它倆分別是一個列數組和一個行數組，相乘後得到的是一個二維的矩陣。更多Excel學習和問題請加群：289393114、570064677

($B$4:$B$9=B13)*($C$3:$G$3=C13)

如下圖：

然後，再把($C$4:$G$9)這個區域的每一個單元格，分別與上面的結果相乘。

最後在將所有的數相加，就得到9.8.

分組排名

下面這個例子，"小組"這一列中有兩個不同的值，「一組」和「二組」。現在我們要針對「銷售額」按不同的組進行排名。

D3單元格中公式為：

=SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10>C3))+1

($B$3:$B$10=B3)不解釋了

($C$3:$C$10>C3)就是拿所有的「銷售額」跟C3（這裡是相對引用，向下複製時會變成C4，C5，……）比，比它大的會返回TRUE。

但是這樣一來排第一的數，是沒有比它大的，會返回0。所以我們在公式的最後面有個「+1」，這就變成我們想要的「排名」的樣子了。