加了*的 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函數的應用。你即將在下面看到的都是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函數更多的應用。同時，我們也了解到邏輯值和數組的一些基礎知識——這對我們理解其他的複雜公式有幫助。關於邏輯值和數組，還有很多很多其他的應用，有興趣的朋友可以留言，我們會根據大家的意願來分享相關的文章。

作者：老菜鳥

來源：部落窩教育

推薦閱讀：