一起聊聊Excel的SUMPRODUCT函數(shù)（Excel的sumproduct）

手機(jī)如何做表格：點(diǎn)擊查看

今天給大家分享一個(gè)很常用也很實(shí)用的函數(shù)：SUMPRODUCT。眾所周知，條件求和與計(jì)數(shù)是表格使用者最經(jīng)常碰到的兩個(gè)問(wèn)題，而該函數(shù)不但集合了條件求和與計(jì)數(shù)兩大功能于一身；還可用于復(fù)雜場(chǎng)景下的排名處理，甚至聽(tīng)說(shuō)有人靠它一個(gè)函數(shù)就打下了Excel半壁江山……于是便不可不學(xué)了。

先來(lái)看基礎(chǔ)語(yǔ)法，SUMPRODUCT的官方語(yǔ)法說(shuō)明是在給定的幾組數(shù)組（array）中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。語(yǔ)法格式如下：

——SUM是求和的意思，PRODUCT是相乘的意思，參數(shù)之間相乘之后再求和，你看，SUMPRODUCT確實(shí)人如其名了。

看我手，歪、圖、斯瑞……總結(jié)起來(lái)，SUMPRODUCT函數(shù)具有以下三個(gè)特點(diǎn)：

1> 它本身默認(rèn)執(zhí)行數(shù)組運(yùn)算。

2> 它會(huì)將參數(shù)中非數(shù)值型的數(shù)組元素作為0處理。

3> 參數(shù)必須有相同的尺寸，否則返回錯(cuò)誤值。

看完了SUMPRODUCT的簡(jiǎn)歷，想必很多朋友是霧里看花，僅僅對(duì)它有個(gè)模糊的認(rèn)知，它的這些特點(diǎn)是啥意思？它到底能夠勝任什么樣子的工作？其實(shí)并不了然。

打個(gè)響指，我舉幾個(gè)例子。

如上圖所示的數(shù)據(jù)表，C列是商品單價(jià)，D列是銷售數(shù)量，現(xiàn)在需要在C9單元格計(jì)算銷售總額。

C9輸入以下公式，即可得出結(jié)果11620.60

這便是一個(gè)簡(jiǎn)單的SUMPRODUCT函數(shù)了。它的運(yùn)算過(guò)程是：C3:C7和D3:D7兩個(gè)區(qū)域數(shù)組內(nèi)的元素分別相乘，也就是C3*D3,C4*D4,C5*D5……直至C7*D7

等于先將每個(gè)商品的銷售金額計(jì)算出來(lái)，最后匯總求和。

由于SUMPRODUCT函數(shù)第一個(gè)特點(diǎn)，本身是支持?jǐn)?shù)組間運(yùn)算的，所以雖然該公式執(zhí)行了多項(xiàng)運(yùn)算，但并不需要按數(shù)組三鍵結(jié)束公式輸入。

有的朋友說(shuō)啦，公式也可以寫(xiě)成這樣：

或者使用以下數(shù)組公式，也是可以的。

那么這三個(gè)公式之間有什么區(qū)別呢？

首先，大部分情況下，SUMPRODUCT函數(shù)都不需要數(shù)組三鍵結(jié)束公式輸入即可執(zhí)行數(shù)組運(yùn)算，而SUM函數(shù)是需要的。

其次，就要說(shuō)到SUMPRODUCT函數(shù)另一個(gè)非常重要的特點(diǎn)了。

……

我們將上面的表稍做改動(dòng)，將“鋼筆”的銷售數(shù)量更改為：暫未統(tǒng)計(jì)。同樣需要在C9單元格計(jì)算銷售總額。

這時(shí)候，如果使用公式：

或者數(shù)組公式：

都將返回錯(cuò)誤值#VALUE!

返回錯(cuò)誤值的原因在于D4單元格“暫未統(tǒng)計(jì)”為文本值，文本值是無(wú)法直接參與數(shù)學(xué)運(yùn)算的，于是C4*D4返回錯(cuò)誤值#VALUE!，進(jìn)而造成整個(gè)公式的結(jié)果返回錯(cuò)誤值。

而使用以下公式就沒(méi)有這方面的困擾，會(huì)直接返回正確結(jié)果：

=SUMPRODUCT(C3:C7,D3:D7)

這便是SUMPRODUCT函數(shù)的第二個(gè)特點(diǎn)：將非數(shù)值型的數(shù)組元素作為0處理。

以該示例來(lái)說(shuō)，D4單元格的值“暫未統(tǒng)計(jì)”為文本，并非數(shù)值，SUMPRODUCT將其主動(dòng)視為零，于是C4*D4，結(jié)果亦為零，其余數(shù)組元素照常計(jì)算，得出11385.60的結(jié)果。

需要特別說(shuō)明的是，SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理，所謂的非數(shù)值型數(shù)組元素，包含邏輯值、文本，但并不包含錯(cuò)誤值，如果數(shù)組元素中包含錯(cuò)誤值，該公式亦返回錯(cuò)誤值，比如該示例的第一條公式。

……

說(shuō)完了SUMPRODUCT函數(shù)的兩個(gè)特點(diǎn)，我們就再來(lái)聊聊它的第三個(gè)特點(diǎn)：數(shù)組參數(shù)必須有相同的尺寸，否則返回錯(cuò)誤值。

我們依然用上述圖片的例題為例，繼續(xù)計(jì)算商品的銷售總額。如果我們?cè)贑9輸入公式：

結(jié)果會(huì)是怎么樣的呢？

錯(cuò)誤值：#VALUE!

為什么？

細(xì)心的你肯定已經(jīng)注意到了，兩個(gè)區(qū)域數(shù)組，C3:C7明顯顯比D3:D6多了一個(gè)元素，C3和D3結(jié)對(duì)子，C4和D4結(jié)對(duì)子……那么C7和誰(shuí)結(jié)對(duì)子呢？女人們都嫁了，結(jié)果剩下一個(gè)光棍，這日子沒(méi)法過(guò)了！一個(gè)蘿卜一個(gè)坑，只有蘿卜沒(méi)有坑，這不是要蘿卜死嗎？

——于是SUMPRODUCT就不高興了，它給你一個(gè)錯(cuò)誤值#VALUE!，明確告訴你，和諧時(shí)代幸福歲月，日子不能這么過(guò)。

這就是SUMPRODUCT函數(shù)的第三個(gè)特點(diǎn)：數(shù)組參數(shù)必須有相同的尺寸，否則返回錯(cuò)誤值。

下面是一道練習(xí)題，你看看，能用SUMPRODUCT函數(shù)做出來(lái)嗎？

假設(shè)下面這張圖，是某個(gè)公司工資發(fā)放的部分記錄表（數(shù)據(jù)純屬虛擬，如有雷同，那是穿越）。A列是工資發(fā)放的時(shí)間，B列是員工所屬的部門(mén)，C列是員工姓名，D列是相關(guān)員工領(lǐng)取的工資金額。

——那么，問(wèn)題和廣告都來(lái)了：

1

員工西門(mén)慶領(lǐng)取了幾次工資？

這是一個(gè)單條件計(jì)數(shù)的問(wèn)題,通常我們使用COUNTIF函數(shù),但如果使用SUMPRODUCT函數(shù)，一般寫(xiě)成這樣：

先判斷C2:C13的值是否等于”西門(mén)慶”，相等則返回TRUE，不等則返回FALSE，由此建立一個(gè)有邏輯值構(gòu)成的內(nèi)存數(shù)組。

上文已經(jīng)說(shuō)過(guò)，SUMPRODUCT有一個(gè)特性，它會(huì)將非數(shù)值型的數(shù)組元素作為0處理，邏輯值自然是屬于非數(shù)值型的數(shù)組元素，為了避免SUMPRODUCT函數(shù)把邏輯值視為0，造成統(tǒng)計(jì)錯(cuò)誤，我們使用*1的方式，把邏輯值轉(zhuǎn)化為數(shù)值，TRUE轉(zhuǎn)化為1，F(xiàn)ALSE轉(zhuǎn)化為0，最后統(tǒng)計(jì)求和。

2

員工西門(mén)慶領(lǐng)取了多少工資？

這是一個(gè)單條件求和的問(wèn)題,通常我們使用SUMIF函數(shù),如果使用SUMPRODUCT函數(shù)，我們可以寫(xiě)成這樣：

依然首先判斷C2:C13的值是否等于”西門(mén)慶”，得到邏輯值FALSE或TRUE，再和D2:D13的值對(duì)應(yīng)相乘。TRUE乘以數(shù)值，得到數(shù)值本身。FALSE乘以數(shù)值返回0。最后統(tǒng)計(jì)求和得出結(jié)果。

看完了上面兩個(gè)問(wèn)題，有些朋友可能會(huì)在心里想，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么還要SUMPRODUCT干啥嘞？

鄉(xiāng)親們吶，話不能這么說(shuō)，SUMPRODUCT可是上得廳堂下得廚房，對(duì)工作環(huán)境不挑不揀，它對(duì)參數(shù)類型沒(méi)有啥特別要求，COUNTIF和SUMIF就不同了，他倆要求個(gè)別參數(shù)，必須是區(qū)域（Range型），不支持?jǐn)?shù)組，比如下面這兩個(gè)問(wèn)題，COUNTIF和SUMIF就要繞了。

3

二月份外交部發(fā)放了幾次工資？總額是多少？

第1個(gè)問(wèn)題，二月份外交部發(fā)放了幾次工資？

這是一個(gè)多條件計(jì)數(shù)的問(wèn)題。

第一個(gè)條件，發(fā)放工資的時(shí)間必須是二月份；第二個(gè)條件，發(fā)放工資的部門(mén)必須是外交部。

如果使用多條件計(jì)數(shù)函數(shù)COUNTIFS，判斷發(fā)放工資的時(shí)間是否屬于六月份，會(huì)簡(jiǎn)單問(wèn)題復(fù)雜化。而使用SUMPRODUCT函數(shù)，咱們可以把公式寫(xiě)成這樣：

……

第2個(gè)問(wèn)題，統(tǒng)計(jì)二月份外交部發(fā)放了多少工資？

這是一個(gè)常見(jiàn)的多條件求和問(wèn)題。

如果使用SUMIFS函數(shù)，判斷發(fā)放工資的時(shí)間是否屬于六月份，也會(huì)簡(jiǎn)單問(wèn)題復(fù)雜化。

SUMPRODUCT躍然而至：

或者：

打個(gè)響指，關(guān)于這兩個(gè)形式的SUMPRODUCT函數(shù)的區(qū)別，咱們上文已有詳細(xì)說(shuō)明——你還記得嗎？

上面這個(gè)公式可以說(shuō)是SUMPRODUCT多條件求和的典型用法啦，可以歸納為:

4

二月份外交部和步兵部合計(jì)發(fā)放了多少工資？

解決了上面的問(wèn)題，相信大家已經(jīng)曉得如何計(jì)算二月份外交部發(fā)放多少工資了，那么二月份外交部和步兵部合計(jì)發(fā)了多少工資，又當(dāng)怎么計(jì)算呢

我們經(jīng)常見(jiàn)有些性格樸素的表親們把公式寫(xiě)成這樣：

這些表親們估計(jì)心想，不就是計(jì)算兩個(gè)部門(mén)嗎？甭說(shuō)兩個(gè)，二十個(gè)咱也能算，一個(gè)加一個(gè)，一直加到二十個(gè)，世上無(wú)難事，只怕有心人嘛，一磚加一磚，長(zhǎng)城就建成了，一泡加一泡，長(zhǎng)江就奔流了……

呃……公式寫(xiě)的那么長(zhǎng)，先不談?dòng)?jì)算速度，首先它累手啊，萬(wàn)一寫(xiě)錯(cuò)了，又要修改，那也是麻煩他媽哭麻煩——麻煩死了。

其實(shí)我們可以寫(xiě)成這樣：

5

排名應(yīng)用

認(rèn)識(shí)了SUMPRODUCT函數(shù)在條件計(jì)數(shù)和求和方面的用法，最后，咱們?cè)賮?lái)看一個(gè)它在排名上的使用方法。

如上圖所示，某個(gè)月某個(gè)公司某些人領(lǐng)了某些工資，然后呢，他們想看看自己的工資，在部門(mén)內(nèi)的排名情況，比如說(shuō)步兵部的魯智深都是老員工了，非常想知道自個(gè)工資在各自部門(mén)排幾號(hào)。

當(dāng)然啦，不排不知道，一排就傻掉。

SUMPRODUCT是這么解決這個(gè)問(wèn)題的，D2輸入公式向下復(fù)制：

（思考，為什么公式的最后+1，而不是直接寫(xiě)成如下：）

嘮嘮叨叨說(shuō)了這么多，眼睛都說(shuō)酸麻了，是到了該結(jié)束的時(shí)候啦。

最后，請(qǐng)思考兩個(gè)小問(wèn)題：

第1個(gè)問(wèn)題：下面SUMPRODUCT函數(shù)有幾個(gè)參數(shù)？

下面這個(gè)SUMPRODUCT函數(shù)又有幾個(gè)參數(shù)？

第二個(gè)問(wèn)題：

SUMPRODUCT為什么有時(shí)候比SUMIF/COUNTIF計(jì)算速度慢？

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。