Excel函數學習之無所不能的SUMPRODUCT！

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

1、SUMPRODUCT的實質

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

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

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

2、大多數錯誤的原因

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

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

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

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

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

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

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

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

意義的改變有何影響呢？

我們還是透過範例來看：

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

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

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

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

以上內容所要表達的意思有兩點：

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

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

6、其實乘號還帶來了更大優勢

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

也就是：

逗號,		乘號*
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函數的應用。你即將在下面看到的都是SUMPRODUCT函數用乘號的應用。

7、看懂這些SUMPRODUCT公式嗎？

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

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

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

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

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

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

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

如果有興趣的話可以逐一對比銷售員和結果值，會發現張三對應的都是TRUE。

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

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

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

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

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

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

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

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

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

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

到現在我們已經了解到數組計算的兩個原則，現舉個簡單的例子來說明：

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

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

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

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

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

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

相關學習推薦：excel教學

以上是Excel函數學習之無所不能的SUMPRODUCT！的詳細內容。更多資訊請關注PHP中文網其他相關文章！