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

1、SUMPRODUCT的實質(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ù)量）中的資料對應相乘後再求和。

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

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

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

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

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

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

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

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

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

意義的改變有何影響呢？

我們還是透過範例來看：

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

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

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

5、SUMPRODUCT用乘號（*）的要點

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

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

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

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

#我們把SUMPRODUCT函數(shù)逗號、乘號前後的資料用A、 B來代替，表達為SUMPRODUCT（A，B）和SUMPRODUCT（A*B）。當為逗號時，A、B必須同時都是數(shù)值或數(shù)組，不能一個是數(shù)值，一個是數(shù)組；當為乘號時，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）

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

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

7、看懂這些SUMPRODUCT公式嗎？

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

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

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

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

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

8、了解一點邏輯值和陣列

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

相關(guān)學習推薦：excel教學

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