Sumproduct，可以將其分為兩個部分，Sum和Product，Sum是求和，Product是乘積，綜合到一起，就是對各個數(shù)據(jù)參數(shù)先乘積，再返回乘積之和。

一、Sumproduct——常規(guī)用法

功能：返回相應的數(shù)據(jù)或區(qū)域乘積的和。

語法結(jié)構：=Sumproduct(數(shù)據(jù)1,[數(shù)據(jù)2],[數(shù)據(jù)3]……)。

目的：計算各類商品的總采購額。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(E3:E12,F3:F12)。

解讀：

Sumproduct函數(shù)的功能是返回相應的數(shù)據(jù)或區(qū)域乘積的和，公式=SUMPRODUCT(E3:E12,F3:F12)中，數(shù)據(jù)區(qū)域有E3:E12和F3:F12兩個，所以計算過程為：E3*F3+E4*F4+E5*F5+……+E11*F11+E12*F12，對應數(shù)據(jù)元素先乘積，后求和，得到最終的總采購額。

二、Sumproduct——單條件求和

目的：按【部門】統(tǒng)計總采購額。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)。

解讀：

公式=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)中，有三個數(shù)據(jù)區(qū)域，分別為D3:D12=I3、E3:E12、F3:F12，當D3:D12區(qū)域中的值和I3單元格中的值相等時，返回1，否則返回0，然后三個數(shù)據(jù)對應元素先乘積，再契合，從而計算得到相應部門的總采購額。

三、Sumproduct——多條件求和

目的：相應【部門】采購相應【商品】的總采購額。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((B3:B12=J3)*(D3:D12=I3)*E3:E12*F3:F12)。

解讀：

計算過程同“單條件求和”類似，只是增加了一個判斷條件而已。

四、Sumproduct——單條件計數(shù)

目的：統(tǒng)計商品總采購【數(shù)量】。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((B3:B12=I3)*(E3:E12))。

解讀：

公式=SUMPRODUCT((B3:B12=I3)*(E3:E12))中，有兩個數(shù)據(jù)區(qū)域，一個為B3:B12=I3，另一個為E3:E12，第一個數(shù)據(jù)區(qū)域中當B3:B12=I3這個條件成立時，返回1，否則返回0，所以第一個數(shù)據(jù)區(qū)域?qū)脑刂禐閧1，0，0，0，0，0，0，0，0，0}，與第二個數(shù)據(jù)元素的值先乘積，再求和，從而得到計數(shù)的結(jié)果。

五、Sumproduct——多條件計數(shù)

目的：對相應【采購部門】采購的【商品】進行計數(shù)統(tǒng)計。

方法：

在目標單元格中輸入公式：=SUMPRODUCT((D3:D12=I3)*(B3:B12=J3)*E3:E12)。

解讀：

具體的計算過程請參閱“單條件計數(shù)”的解讀部門。

六、Sumproduct——中國式排名

目的：對單個【商品】的【采購額】進行排序。

方法：

在目標單元格中輸入公式：=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1。

解讀：

1、從結(jié)果中可以看出，公式=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1的排序結(jié)果降序，如果要按升序排序，則只需將公式中的$G$3:$G$12>G7更改為>G3>$G$3:$G$12即可。

2、公式整體上可以看成3部分組成，一部分為：$G$3:$G$12>G3，另一部分為：COUNTIF($G$3:$G$12,$G$3:$G$12），最后一部分為附加值+1。計算時，首先判斷$G$3:$G$12>G3是否成立，以H3單元格為例，則返回{0,1,1,1,1,1,1,0,1,0}，而COUNTIF($G$3:$G$12,$G$3:$G$12）統(tǒng)計的是$G$3:$G$12區(qū)域中的每個值在$G$3:$G$12區(qū)域中的出現(xiàn)次數(shù)，即返回{1,1,1,1,1,1,1,1,1,1}，對應的數(shù)據(jù)元素先進行除法（/）計算，返回{0,1,1,1,1,1,1,0,1,0},并返回給Sumproduct作為參數(shù)，而Sumpoduct中，當只有一個數(shù)據(jù)區(qū)域時，對該區(qū)域的值進行求和運算，即0+1+1+1+1+1+1+0+1+0，最后等于7，加上輔助值1，得到想要的結(jié)果8。

轉(zhuǎn)自https://me.mbd.baidu.com/r/GH4ne1MRj2?f=cp&u=41b49a65f43c2520

如有侵權將刪除