小編有話說：小編一邊為大家推送教程，一邊和大家一起學(xué)習(xí)，看到作者這篇文章的時(shí)候，真覺得excel的學(xué)習(xí)之路還有還很長(zhǎng)呀。sumproduct函數(shù)可以得到兩列數(shù)據(jù)的乘積之和，但是在運(yùn)用函數(shù)的過程中有非常多需要注意的地方，特別是運(yùn)用上邏輯值和數(shù)組之后，下面我們一起來看看作者的講解吧。

1.

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

要說SUMPRODUCT函數(shù)的話，真的非常簡(jiǎn)單，就是得到兩列數(shù)據(jù)的乘積之和，我們用一個(gè)簡(jiǎn)單的例子來說明函數(shù)的基本功能：

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

在這個(gè)公式里，使用了兩個(gè)參數(shù)，分別是單價(jià)區(qū)域（B2:B7）和數(shù)量區(qū)域（C2:C7），函數(shù)的作用就是將第一參數(shù)（單價(jià)）與第二參數(shù)（數(shù)量）中的數(shù)據(jù)對(duì)應(yīng)相乘后再求和。

2.

大多數(shù)錯(cuò)誤的原因

很多朋友在使用這個(gè)函數(shù)的時(shí)候，經(jīng)常會(huì)得到錯(cuò)誤值，大多數(shù)是因?yàn)閰^(qū)域大小選擇不一致，例如下面這種情況，第一個(gè)參數(shù)有7個(gè)單元格而第二個(gè)參數(shù)只有6個(gè)單元格：

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

3.

另一種常見寫法，逗號(hào)變乘號(hào)（*）

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

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

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

4.

逗號(hào)和乘號(hào)（*）的差別

雖然只是將第一個(gè)公式里的逗號(hào)變成了乘號(hào)（*），但是公式的意義發(fā)生了變化。第一個(gè)公式（SUMPRODUCT(B2:B7,C2:C7)）有兩個(gè)參數(shù)，而第二個(gè)公式（B2:B7*C2:C7）是一個(gè)參數(shù)。（判斷有幾個(gè)參數(shù)要看是不是有逗號(hào)去分隔開。）第一個(gè)公式中，兩個(gè)區(qū)域相乘這一步是由函數(shù)來完成的，函數(shù)做了兩件事，先讓兩個(gè)區(qū)域的數(shù)據(jù)對(duì)應(yīng)相乘，再把乘積相加。在第二個(gè)公式中，兩個(gè)區(qū)域相乘是由數(shù)組計(jì)算來完成的，函數(shù)只做了一件事，就是把乘積值相加。

意義的變化有何影響呢？

我們還是通過例子來看：

在上圖這個(gè)公式中用的是逗號(hào)（，），有兩個(gè)獨(dú)立的參數(shù)。SUMPRODUCT函數(shù)首先讓兩組數(shù)據(jù)對(duì)應(yīng)相乘，相乘的時(shí)候會(huì)檢查數(shù)據(jù)并把非數(shù)值型數(shù)據(jù)作為0處理，然后在把乘積相加。因此，B1“單價(jià)”和C1“數(shù)量”會(huì)當(dāng)成0來處理，公式可以得到正確結(jié)果。

當(dāng)我們把逗號(hào)換成*號(hào)后，公式結(jié)果錯(cuò)誤。為什么呢？SUMPRODUCT函數(shù)這時(shí)只負(fù)責(zé)把乘積相加。參數(shù)B2:B7*C2:C7是數(shù)組乘法運(yùn)算，因?yàn)橛?jì)算的區(qū)域中包含了文字（文字是不能進(jìn)行乘法運(yùn)算的），所以在這個(gè)數(shù)組的計(jì)算結(jié)果里就有錯(cuò)誤值了。選中公式中的“B2:B7*C2:C7”按F9可以查看B2:B7*C2:C7的運(yùn)算結(jié)果：

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

5.

SUMPRODUCT用乘號(hào)（*）的要點(diǎn)

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

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

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

6.

其實(shí)乘號(hào)還帶來了更大優(yōu)勢(shì)

我們把SUMPRODUCT函數(shù)逗號(hào)、乘號(hào)前后的數(shù)據(jù)用A、B來代替，表達(dá)為SUMPRODUCT（A，B）和SUMPRODUCT（A*B）。當(dāng)為逗號(hào)時(shí)，A、B必須同時(shí)都是數(shù)值或者數(shù)組，不能一個(gè)是數(shù)值，一個(gè)是數(shù)組；當(dāng)為乘號(hào)時(shí)，A、B可以同時(shí)都是數(shù)值或者數(shù)組，也可以一個(gè)是數(shù)值一個(gè)是數(shù)組。

也就是：

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

7.

看得懂這些SUMPRODUCT公式嗎？

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

這實(shí)際上是一個(gè)條件計(jì)數(shù)的問題，再看這個(gè)：

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

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

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

8.

了解一點(diǎn)邏輯值和數(shù)組

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

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

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

通過*1運(yùn)算之后，TRUE都變成了1，而FALSE都變成了0。不要問我為什么，Excel就是這么規(guī)定的，邏輯值與數(shù)字的對(duì)應(yīng)關(guān)系就是這個(gè)。

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

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

9.

不論SUMPRODUCT公式多復(fù)雜，全看懂！

咱們?cè)賮砜纯吹诙€(gè)公式：=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="襯衫")*J2:J22)。

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

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

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

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

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

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

兩個(gè)數(shù)組（A1:A9和B1:B9）相乘時(shí)，是第一組數(shù)與第二組數(shù)對(duì)應(yīng)位置的數(shù)據(jù)相乘。

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

其實(shí)數(shù)組的計(jì)算原則還有更加復(fù)雜的情況，有興趣的朋友可以看看相關(guān)的資料，這里了解簡(jiǎn)單原理即可。再來看看第三個(gè)公式：=SUMPRODUCT((H2:H22=H2)*(G2:G22={"毛衣","襯衫"})*L2:L22)。這個(gè)公式看上去和前面兩個(gè)有點(diǎn)區(qū)別，(G2:G22={"毛衣","襯衫"})這部分用到了常量數(shù)組，其實(shí)這個(gè)公式本來應(yīng)該是這樣寫的：=SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="襯衫")*L2:L22)。

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

通過今天的講解，我們讀懂了常見的SUMPRODUCT公式，解決了SUMPRODUCT函數(shù)運(yùn)用乘號(hào)的困惑，掌握了SUMPRODUCT函數(shù)更多的應(yīng)用。同時(shí)，我們也了解到邏輯值和數(shù)組的一些基礎(chǔ)知識(shí)——這對(duì)我們理解其他的復(fù)雜公式有幫助。關(guān)于邏輯值和數(shù)組，還有很多很多其他的應(yīng)用，有興趣的朋友可以留言，我們會(huì)根據(jù)大家的意愿來分享相關(guān)的文章。

本文配套的練習(xí)課件請(qǐng)加入QQ群：316492581下載。

如果您因工作所需使用到Excel，不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。

相關(guān)推薦：