Excel函數學習之無所不能的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函數的應用。你即將在下面看到的都是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=H2)*(G2:G22={"毛衣","襯衫"})*L2:L22)。這個公式看上去和前面兩個有點區別，(G2:G22={"毛衣","襯衫"})這部分用到了常數陣列，其實這個公式本來應該是這樣寫的：=SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="襯衫")*L2:L22)。

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

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

相關學習推薦：

以上就是Excel函數學習之無所不能的SUMPRODUCT！的詳細內容，更多請關注TW511.COM其它相關文章！