在之前的文章《Excel函數(shù)學(xué)習(xí)之神奇的AGGREGATE，竟可一個抵19個！在》中，我們了解了一個強(qiáng)大的統(tǒng)計函數(shù)。而今天我們來聊聊SUMPRODUCT函數(shù)，sumproduct函數(shù)可以得到兩列資料的乘積之和，但是在運(yùn)用函數(shù)的過程中有非常多需要注意的地方，特別是運(yùn)用上邏輯值和數(shù)組之後，下面我們一起來看看講解！

1、SUMPRODUCT的實(shí)質(zhì)

要說SUMPRODUCT函數(shù)的話，真的非常簡單，就是得到兩列資料的乘積總和，我們用一個簡單的例子來說明函數(shù)的基本功能：

上圖是一個非常簡單的表格，要算出總價一般都是將單價*數(shù)量算出來再求和，結(jié)果如D8所示。如果使用了SUMPRODUCT函數(shù)的話，就可以直接利用單價和數(shù)量來計算總價，公式1為：=SUMPRODUCT(B2:B7,C2:C7)，結(jié)果如D9所示。

在這個公式裡，使用了兩個參數(shù)，分別是單價區(qū)域（B2:B7）和數(shù)量區(qū)域（C2:C7），函數(shù)的作用就是將第一參數(shù)（單價）與第二參數(shù)（數(shù)量）中的資料對應(yīng)相乘後再求和。

2、大多數(shù)錯誤的原因

很多朋友在使用這個函數(shù)的時候，常常會得到錯誤值，大多數(shù)是因為區(qū)域大小選擇不一致，例如下面這種情況，第一個參數(shù)有7個單元格而第二個參數(shù)只有6個單元格：

##使用SUMPRODUCT函數(shù)必須確保每個參數(shù)的區(qū)域大小相同，但許多朋友沒有註意到這一點(diǎn)。

3、另一種常見寫法，逗號變乘號（*）

就這個例子來說，還有一種寫法更為常見，公式是這樣的：=SUMPRODUCT(B2:B7*C2:C7)

可以看到其計算結(jié)果與=SUMPRODUCT(B2:B7,C2: C7)是一致的。

一致的結(jié)果導(dǎo)致了許多朋友都百思不得其解的一個問題：二者有何差別？

4、逗號和乘號（*）的差別

雖然只是將第一個公式裡的逗號變成了乘號（*），但是公式的意義改變了。第一個公式（SUMPRODUCT(B2:B7,C2:C7)）有兩個參數(shù)，而第二個公式（B2:B7*C2:C7）則是參數(shù)。 （判斷有幾個參數(shù)要看是不是有逗號去分隔開。）第一個公式中，兩個區(qū)域相乘這一步是由函數(shù)來完成的，函數(shù)做了兩件事，先讓兩個區(qū)域的資料對應(yīng)相乘，再把積相加。在第二個公式中，兩個區(qū)域相乘是由陣列計算來完成的，函數(shù)只做了一件事，就是把乘積值相加。

意義的改變有何影響呢？

我們還是透過範(fàn)例來看：

在上圖這個公式中用的是逗號（，），有兩個獨(dú)立的參數(shù)。 SUMPRODUCT函數(shù)先讓兩組資料對應(yīng)相乘，相乘的時候會檢查資料並把非數(shù)值型資料當(dāng)作0處理，然後在把乘積相加。因此，B1「單價」和C1「數(shù)量」會當(dāng)成0來處理，公式可以得到正確結(jié)果。

當(dāng)我們把逗號換成*號後，公式結(jié)果錯誤。為什麼呢？ SUMPRODUCT函數(shù)這時只負(fù)責(zé)把乘積相加。參數(shù)B2:B7*C2:C7是陣列乘法運(yùn)算，因為計算的區(qū)域包含了文字（文字是不能進(jìn)行乘法運(yùn)算的），所以在這個陣列的計算結(jié)果裡面就有錯誤值了。選取公式中的「B2:B7*C2:C7」按F9可以查看B2:B7*C2:C7的運(yùn)算結(jié)果：

可以看到第一個（單價*數(shù)量）運(yùn)算結(jié)果就是錯誤值。接下來SUMPRODUCT對包含了錯誤值的資料進(jìn)行求和，結(jié)果肯定就是錯誤了。

5、SUMPRODUCT用乘號（*）的要點(diǎn)

以上內(nèi)容所要表達(dá)的意思有兩點(diǎn)：

第一，使用逗號和使用*號有時候結(jié)果相同，但是意義完全不一樣，希望大家可以理解。

第二，SUMPRODUCT函數(shù)使用乘號（*）必須注意兩點(diǎn)：第一，不能存在無法計算的內(nèi)容，如文字；第二，如果是兩組或多組數(shù)組相乘的話，數(shù)據(jù)區(qū)域大小一致。用逗號則只需要保證資料區(qū)域大小一致即可。

6、其實(shí)乘號還帶來了更大優(yōu)勢

#我們把SUMPRODUCT函數(shù)逗號、乘號前後的資料用A、 B來代替，表達(dá)為SUMPRODUCT（A，B）和SUMPRODUCT（A*B）。當(dāng)為逗號時，A、B必須同時都是數(shù)值或數(shù)組，不能一個是數(shù)值，一個是數(shù)組；當(dāng)為乘號時，A、B可以同時都是數(shù)值或是數(shù)組，也可以一個是數(shù)值一個是數(shù)組。

也就是：

逗號,		乘號*
SUMPRODUCT（C1,B1）			√
SUMPRODUCT（C1*B1）	√ ################SUMPRODUCT######（C1:C9,D1:D9）### ###############√######	求和（C1:C9*D1:D9）	√
##求和（C1:D9,F1:G9）	##√	SUM乘積（C1:D9*F1:G9）	##√
#SUMProduct（C1:C9, D1）	##×	#SUMProduct （C1:C9* D1）	√
求和（C1:D9,F1）	# ×	求和（C1:D9*F1）

########################################## ## ## #√####################

因此，用乘號擴(kuò)大了SUMPRODUCT函數(shù)的應(yīng)用。你即將在下面看到的都是SUMPRODUCT函數(shù)用乘號的應(yīng)用。

7、看懂這些SUMPRODUCT公式嗎？

如果明白了上面的內(nèi)容，表示對於這個函數(shù)的基本用法是沒問題了。可是很可能很多SUMPRODUCT公式你還是看不懂，比如這種：

#這其實(shí)是一個條件數(shù)的問題，再看這個：

這是一個多條件求和的問題，還有這個：

#在這個表裡，幾乎各種統(tǒng)計問題都可以用SUMPRODUCT函數(shù)去解決，不再一一舉例。光是上面列出的三個公式，都能看懂的朋友可能就不多了。

為什麼已經(jīng)理解了SUMPRODUCT函數(shù)的用法，卻還看不懂這些公式呢，更別說自己去用公式來解決問題了。原因就是你還不了解這兩個知識點(diǎn)：邏輯值和陣列。

8、了解一點(diǎn)邏輯值和陣列

#先來說說邏輯值，邏輯值只有兩個，就是TRUE和FALSE。當(dāng)我們在公式裡進(jìn)行某種比較或判斷的時候，就會產(chǎn)生邏輯值，以SUMPRODUCT((I2:I22="張三")*1) 為例，其中的(I2:I22="張三" )就是一個判斷。通常情況下，我們是以單元格去做判斷，I2 ="張三"的意思就是判斷I2單元格內(nèi)容是否為"張三"，如果是則得到TRUE，反之得到FALSE。當(dāng)我們使用了一個區(qū)域去判斷的時候，就會得到一組數(shù)據(jù)，這其實(shí)就是一個陣列了。例如I2:I22="張三"就會得到一組邏輯值，可以用F9來看看計算結(jié)果：

如果有興趣的話可以逐一對比銷售員和結(jié)果值，會發(fā)現(xiàn)張三對應(yīng)的都是TRUE。

因為邏輯值無法直接進(jìn)行求和，必須轉(zhuǎn)換為數(shù)字才行，轉(zhuǎn)換的方法就是對邏輯值進(jìn)行加減乘除之一的任何運(yùn)算即可。在這個公式中，*1就是這個作用，可以看看效果：

透過*1運(yùn)算之後，TRUE都變成了1，而FALSE都變成了0。不要問我為什麼，Excel就是這麼規(guī)定的，邏輯值跟數(shù)字的對應(yīng)關(guān)係就是這個。

好了，我們先得到一組邏輯值，然後透過*1變成一組數(shù)字，再進(jìn)行求和，就達(dá)到了按條件計數(shù)的目的。你現(xiàn)在是否已經(jīng)理解了=SUMPRODUCT((I2:I22="張三")*1)這個公式呢？

現(xiàn)在我們了解到邏輯值，也明白了陣列運(yùn)算的第一個原則：當(dāng)一組數(shù)與一個數(shù)進(jìn)行計算時，是這組數(shù)中的每個資料分別與這一個數(shù)進(jìn)行計算。剛才的公式中就是這樣計算的。

9、不論SUMPRODUCT公式多複雜，全看懂！

咱們再來看看第二個公式：=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*( G2:G22="襯衫")*J2:J22)。

不要看這個公式長，利用我們剛才學(xué)到的知識來破解它的話其實(shí)很簡單。公式還是只有一個參數(shù)，只不過這個參數(shù)是由四個陣列構(gòu)成的，其中的三組都是邏輯值，分別是(MONTH(F2:F22)=3)、(H2:H22="二分店")和(G2:G22="襯衫")。這三組邏輯值完成三個判斷，分別對應(yīng)了三個條件：月份=3、店面=二分店和名稱=襯衫。具體內(nèi)容如圖所示：

看起來密密麻麻的，但是經(jīng)過了乘法運(yùn)算以後，就變成了一堆1和0，結(jié)果是這樣的：

乘積結(jié)果只有兩個1，其實(shí)對應(yīng)了三月份二分店襯衫這兩條數(shù)據(jù)。此時的公式變成了一組邏輯值（已經(jīng)是0和1了）與一組資料（數(shù)量）相乘，再由函數(shù)完成求和。

在這個例子中，需要注意數(shù)組計算的第二個原則：當(dāng)多個（含兩個）數(shù)組計算時（本例是四個數(shù)組），數(shù)組中對應(yīng)位置的資料進(jìn)行計算，要求數(shù)組中包含的資料個數(shù)相同。

到現(xiàn)在我們已經(jīng)了解到數(shù)組計算的兩個原則，現(xiàn)舉個簡單的例子來說明：

一個數(shù)組（A1:A9）與一個數(shù)據(jù)（B1）相乘時，是這組數(shù)分別與這個資料相乘；

#兩個陣列（A1:A9和B1:B9）相乘時，是第一組數(shù)與第二組數(shù)對應(yīng)位置的資料相乘。

現(xiàn)在回頭來看這個公式=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="襯衫")* J2:J22)，應(yīng)該沒那麼頭痛了吧。

其實(shí)陣列的計算原則還有更複雜的情況，有興趣的朋友可以看看相關(guān)的資料，這裡了解簡單原理即可。再來看看第三個公式：=SUMPRODUCT((H2:H22=H??2)*(G2:G22={"毛衣","襯衫"})*L2:L22)。這個公式看上去跟前面兩個有點(diǎn)差別，(G2:G22={"毛衣","襯衫"})這部分用到了常數(shù)數(shù)組，其實(shí)這個公式本來應(yīng)該是這樣寫的：=SUMPRODUCT((H2:H22=H??2)*(G2:G22="毛衣")*L2:L22) SUMPRODUCT((H2:H22=H??2)*(G2:G22="襯衫")*L2:L22 )。

這個公式用到了兩個SUMPRODUCT函數(shù)，第一個是計算總店毛衣的銷售額，第二個是計算總店襯衫的銷售額，分別看應(yīng)該每個都能理解。兩個SUMPRODUCT函數(shù)除了"毛衣"和"襯衫"這裡不同，其他的完全一樣，遇到這種情況，就可以用常數(shù)數(shù)組把兩個內(nèi)容放到一起，使公式變得簡潔。

透過今天的講解，我們讀懂了常見的SUMPRODUCT公式，解決了SUMPRODUCT函數(shù)運(yùn)用乘號的困惑，掌握了SUMPRODUCT函數(shù)更多的應(yīng)用。同時，我們也了解到邏輯值和陣列的一些基礎(chǔ)知識——這對我們理解其他的複雜公式有幫助。

相關(guān)學(xué)習(xí)推薦：excel教學(xué)

以上是Excel函數(shù)學(xué)習(xí)之無所不能的SUMPRODUCT！的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！