excelperfect

本文主要探討什么時(shí)候使用SUMPRODUCT函數(shù)更有效，而什么時(shí)候應(yīng)該使用SUMIFS函數(shù)代替SUMPRODUCT函數(shù)。

下面是關(guān)于SUMPRODUCT函數(shù)使用的一些重要說(shuō)明：

1. SUMPRODUCT函數(shù)接受兩個(gè)或多個(gè)相同維數(shù)大小的數(shù)組作為參數(shù)，首先將數(shù)組相乘，然后將結(jié)果相加。

2. SUMPRODUCT函數(shù)接受的數(shù)組參數(shù)數(shù)量范圍為1至255個(gè)，這些參數(shù)分別命名為array1、array2，等等，數(shù)組必須具有相同的大?。ɡ?×3和1×3，2×5和2×5，等等）。

3. SUMPRODUCT函數(shù)將非數(shù)字?jǐn)?shù)據(jù)視為0。

4. SUMPRODUCT函數(shù)的數(shù)組參數(shù)可以處理數(shù)組操作以及由數(shù)組操作生成的結(jié)果數(shù)組，而無(wú)需按Ctrl+Shift+Enter鍵。

5. 如果需要相乘的數(shù)組的維數(shù)不同，那么可以使用乘法運(yùn)算符并將相乘的數(shù)組放在單個(gè)數(shù)組參數(shù)中。

6. 當(dāng)需要將數(shù)組操作的結(jié)果相加時(shí)，可以在參數(shù)array1中包含單個(gè)數(shù)組操作。此時(shí)，僅使用了SUMPRODUCT函數(shù)的SUM部分。

7. 在Excel 2003或更早版本中，SUMPRODUCT函數(shù)可用于的多條件求和和計(jì)數(shù)。

8. 由于SUMPRODUCT函數(shù)將非數(shù)字?jǐn)?shù)據(jù)視為0，因此如果要在數(shù)組計(jì)算中使用TRUE和FALSE值，則必須將它們轉(zhuǎn)換成1和0。可使用任何的數(shù)學(xué)運(yùn)算來(lái)進(jìn)行轉(zhuǎn)換，但使用雙減號(hào)通常是最快的計(jì)算方法。

9. 在Excel 2007及后續(xù)版本中，如果要進(jìn)行多條件計(jì)數(shù)或求和，首先考慮是否可以使用SUMIFS函數(shù)或COUNTIFS函數(shù)，因?yàn)樗鼈兊挠?jì)算速度更快。

10. SUMPRODUCT函數(shù)可用于處理工作簿引用，以替代SUMIF函數(shù)、COUNTIF函數(shù)、SUMIFS函數(shù)和COUNTIFS函數(shù)。

11. SUMPRODUCT函數(shù)可用于處理SUMIF函數(shù)、COUNTIF函數(shù)、SUMIFS函數(shù)和COUNTIFS函數(shù)的單元格區(qū)域（range）參數(shù)無(wú)法處理的數(shù)組計(jì)算。

示例：相同大小的兩個(gè)或多個(gè)數(shù)組相乘，然后相加

SUMPRODUCT函數(shù)的基本用法是在其中輸入以逗號(hào)分隔開(kāi)的幾個(gè)大小相同的單元格區(qū)域。SUMPRODUCT函數(shù)將相應(yīng)的單元格相乘，然后將結(jié)果相加。如下圖1所示，簡(jiǎn)潔的公式求出了兩組單元格區(qū)域中相應(yīng)單元格相乘并將乘積相加的結(jié)果。

圖1

下圖2展示了SUMPRODUCT函數(shù)與直接使用乘法運(yùn)算符的SUM函數(shù)相比的優(yōu)勢(shì)。SUMPRODUCT函數(shù)不需要Ctrl+Shift+Enter，并且將0、空單元格和文本視為數(shù)字0；而SUM函數(shù)的數(shù)組公式結(jié)果是錯(cuò)誤值#VALUE!，因?yàn)閿?shù)字和文本不能直接相乘。

圖2

下圖3至圖5展示了SUMPRODUCT函數(shù)基本用法的3個(gè)示例。

圖3：根據(jù)權(quán)重計(jì)算成績(jī)

圖4：根據(jù)可能性預(yù)測(cè)訂單數(shù)

圖5：4個(gè)單元格區(qū)域相乘后的結(jié)果相加得到總壓力

注意，如果SUMPRODUCT函數(shù)接受兩個(gè)單元格作為其參數(shù)，但這兩個(gè)單元格都為空，則結(jié)果是錯(cuò)誤值，如下圖6所示。

圖6

示例：三個(gè)具有不同大小的單元格區(qū)域相乘，然后相加

如下圖7所示，基于經(jīng)濟(jì)狀態(tài)的概率（單元格區(qū)域B3:B5）、每支股票的權(quán)重（單元格區(qū)域C1:D1）以及單支股票收益估計(jì)來(lái)估算持有股票的預(yù)期收益，使用數(shù)組相乘操作來(lái)創(chuàng)建作為SUMPRODUCT函數(shù)參數(shù)的結(jié)果。

圖7

注意，雖然示例公式使用數(shù)組相乘作為SUMPRODUCT函數(shù)的參數(shù)array1的值，但是由于相乘操作不能處理文件，因此要注意用于相乘的數(shù)組中不能含有文本值，否則公式會(huì)導(dǎo)致錯(cuò)誤#VALUE!。

示例：將數(shù)組運(yùn)算得到的結(jié)果數(shù)組相加（僅利用SUM部分）

如下圖8所示，在ROUND函數(shù)中進(jìn)行數(shù)組運(yùn)算，然后使用SUMPRODUCT函數(shù)計(jì)算總和。注意，可以使用SUM函數(shù)，但需要按Ctrl+Shift+Enter輸入數(shù)組公式，因此SUMPRODUCT函數(shù)更簡(jiǎn)單些。

圖8

什么時(shí)候使用SUMPRODUCT函數(shù)進(jìn)行多條件計(jì)數(shù)或求和

在Excel中，除SUMPRODUCT函數(shù)外，COUNTIFS函數(shù)、SUMIFS函數(shù)、DCOUNT函數(shù)和DSUM函數(shù)都可以進(jìn)行多條件計(jì)數(shù)或求和，并且比SUMPRODUCT函數(shù)更有效率。但為什么還要使用SUMPRODUCT函數(shù)呢？下面是一些理由。

1. 在Excel 2003及以前的版本中，沒(méi)有COUNTIFS函數(shù)和SUMIFS函數(shù)。

2. 在Excel 2003及以前的版本中，不總是可能去使用D-函數(shù)，因?yàn)樗鼈冃枰线m的數(shù)據(jù)集，并且難以將公式復(fù)制到其它單元格。

3. 在使用Excel 2007及以后的版本時(shí)，可能會(huì)碰到在Excel 2007發(fā)布以前已經(jīng)創(chuàng)建的帶有SUMPRODUCT函數(shù)的公式的工作表。

4. SUMPRODUCT函數(shù)能夠進(jìn)行COUNTIFS函數(shù)和SUMIFS函數(shù)無(wú)法進(jìn)行的一些多條件計(jì)算。

如下圖9所示，要求使用公式求出員工Kip花在Project 2項(xiàng)目上的次數(shù)和時(shí)間和。示例中，使用了更有效率的COUNTIFS函數(shù)和SUMIFS函數(shù)。

在Excel 2007及以后的版本中，COUNTIFS函數(shù)和SUMIFS函數(shù)提供了以下優(yōu)勢(shì)：

1. 與SUMPRODUCT函數(shù)或等效的D-函數(shù)相比，使用COUNTIFS函數(shù)和SUMIFS函數(shù)的公式計(jì)算速度更快。對(duì)于大數(shù)據(jù)集來(lái)說(shuō)，它們能夠明顯地縮短計(jì)算時(shí)間。

2. 不像D-函數(shù)，在數(shù)據(jù)集或判斷條件區(qū)域中，它們不需要的字段名。

3. 不像D-函數(shù)，使用它們的公式很容易被復(fù)制到其他單元格。

圖9

下圖10展示了使用DCOUNT函數(shù)和DSUM函數(shù)獲得次數(shù)和求和的示例。如果使用的是Excel 2003或以前的版本，在數(shù)據(jù)集和條件區(qū)域中帶有字段名的合適的數(shù)據(jù)集，不需要復(fù)制公式到其它單元格，那么使用D-函數(shù)更有效率，公式的計(jì)算時(shí)間比SUMPRODUCT函數(shù)更快。此外，D-函數(shù)的公式比等價(jià)的SUMPRODUCT函數(shù)的公式更簡(jiǎn)潔，尤其是具有多個(gè)條件時(shí)。

圖10

下圖11展示了使用SUMPRODUCT函數(shù)獲得次數(shù)和求和的示例。如果使用的是Excel 2003或以后的版本，在數(shù)據(jù)集或條件區(qū)域中沒(méi)有字段名，SUMPRODUCT函數(shù)能夠進(jìn)行運(yùn)算，但D-函數(shù)不能。

圖11

當(dāng)使用Excel 2003及以前版本時(shí)，下圖12展示使用SUMPRODUCT函數(shù)比D-函數(shù)更有優(yōu)勢(shì)：可以復(fù)制公式。示例中，添加了兩個(gè)條件并創(chuàng)建了交叉表，在單元格F3中創(chuàng)建公式后，向右向下復(fù)制到單元格區(qū)域F3:G5。

圖12

使用雙減號(hào)將TRUE和FALSE轉(zhuǎn)換成1和0

首先，注意下面兩個(gè)問(wèn)題：

1. SUMPRODUCT函數(shù)將非數(shù)字?jǐn)?shù)據(jù)視為0，它不認(rèn)識(shí)TRUE和FALSE。

2. 任何數(shù)學(xué)運(yùn)算將TRUE和FALSE轉(zhuǎn)換為1和0。

如下圖13所示，A2:A5=C2生成一個(gè)由邏輯值組成的數(shù)組，而SUMPRODUCT函數(shù)將邏輯值視為0，因此結(jié)果為0，而實(shí)際應(yīng)該是2。

圖13

通過(guò)對(duì)邏輯值執(zhí)行任何數(shù)學(xué)運(yùn)算將TRUE和FALSE轉(zhuǎn)換為1和0，如下圖14所示。

圖14

下圖15展示在SUMPRODUCT函數(shù)公式中如何使用不同的數(shù)學(xué)運(yùn)算來(lái)統(tǒng)計(jì)列A中“Kip”的數(shù)量。

圖15

下面詳細(xì)給出了公式[1]的運(yùn)算過(guò)程，讓我們理解雙減號(hào)的工作原理。

=SUMPRODUCT(--(A2:A5=C2))

轉(zhuǎn)換為：

=SUMPRODUCT(--({TRUE;FALSE;TRUE;FALSE}))

轉(zhuǎn)換為：

=SUMPRODUCT(-({-1;0;-1;0}))

轉(zhuǎn)換為：

=SUMPRODUCT({1;0;1;0})

得到結(jié)果：

2

據(jù)測(cè)試，使用雙減號(hào)比其它方法速度更快。

關(guān)于SUMIFS函數(shù)、DSUM函數(shù)和SUMPRODUCT函數(shù)中使用比較運(yùn)算符的語(yǔ)法差異

在使用比較運(yùn)算符時(shí)，SUMIFS函數(shù)、DSUM函數(shù)和SUMPRODUCT函數(shù)有不同的要求，如下圖16至圖19所示。

圖16：SUMIFS函數(shù)接受包含比較運(yùn)算符和要處理的數(shù)值的單元格作為條件。本示例中，要求在兩個(gè)日期之間的條件，單元格A2中包含大于等于某日期的條件，單元格B2中包含小于等于某日期的條件。

圖17：SUMIFS函數(shù)接受在公式中連接在一起的條件，示例在公式中將比較運(yùn)算符（加上雙引號(hào)）和單元格中的值相連接。

圖18：DSUM函數(shù)要求在單元格中放置比較運(yùn)算符，沒(méi)有SUMIFS函數(shù)那么靈活。

圖19：SUMPRODUCT函數(shù)要求將比較運(yùn)算符直接放在數(shù)組和條件之間，以創(chuàng)建數(shù)組操作。這也是其運(yùn)算時(shí)間較長(zhǎng)的原因。

什么時(shí)候使用SUMPRODUCT函數(shù)是最好的

類(lèi)似于SUMIFS函數(shù)、SUMIF函數(shù)、COUNTIFS函數(shù)等都包含一個(gè)參數(shù)range或一個(gè)含有條件值的單元格區(qū)域的參數(shù)criteria_range。參數(shù)range和criteria_range在任何情況下都不能處理數(shù)組。當(dāng)使用工作簿引用，然后關(guān)閉這個(gè)含有外部數(shù)據(jù)的工作簿時(shí)，該工作簿引用將轉(zhuǎn)換為數(shù)組并導(dǎo)致該函數(shù)顯示#VALUE！錯(cuò)誤。而SUMPRODUCT函數(shù)則不會(huì)受到影響。因此，如果公式中含有對(duì)外部工作簿的數(shù)據(jù)引用或者遇到上述函數(shù)不能處理數(shù)組的情形時(shí)，則最好使用SUMPRODUCT函數(shù)。

示例：計(jì)算兩個(gè)日期之間有多少個(gè)星期五是13號(hào)

如下圖20所示，使用SUMPRODUCT函數(shù)的公式求出在兩個(gè)日期之間有多少個(gè)星期五是13號(hào)。公式利用了前面介紹的ROW函數(shù)生成連續(xù)數(shù)字的技巧，這些數(shù)字都代表日期的序號(hào)，再使用TEXT函數(shù)設(shè)置其日期格式并與指定格式的日期比較，求出該日期的數(shù)量。

圖20

如果使用COUNTIF函數(shù)的公式：

=COUNTIF(TEXT(ROW(INDIRECT(B2&':'&B3)),'dddd')='Friday 13',TRUE)

Excel會(huì)彈出如下圖21所示的警告消息。

圖21

這個(gè)消息并沒(méi)有指出公式存在的問(wèn)題，其問(wèn)題是：COUNTIF函數(shù)中的參數(shù)range不能處理數(shù)組或數(shù)組操作。在COUNTIF函數(shù)、SUMIF函數(shù)、AVERAGEIF函數(shù)、SUMIFS函數(shù)、COUNTIFS函數(shù)和AVERAGEIFS函數(shù)中，參數(shù)range和參數(shù)criteria_range不能夠處理數(shù)組。這種情形下，使用SUMPRODUCT函數(shù)。

不能夠處理數(shù)組（數(shù)組運(yùn)算、數(shù)組常量、通過(guò)工作簿引用創(chuàng)建的數(shù)組）的函數(shù)參數(shù)：

1. VLOOKUP函數(shù)中的參數(shù)lookup_value。

2. HLOOKUP函數(shù)中的參數(shù)lookup_value。

3. SUMIF函數(shù)中的參數(shù)range。

4. COUNTIF函數(shù)中的參數(shù)range。

5. AVERAGEIF函數(shù)中的參數(shù)range。

6. SUMIFS函數(shù)中的參數(shù)criteria_range。

7. COUNTIFS函數(shù)中的參數(shù)criteria_range。

8. AVERAGEIFS函數(shù)中的參數(shù)criteria_range。

SUMPRODUCT函數(shù)參數(shù)里的IF函數(shù)

在前面的系列文章的講解中，我們講過(guò)一條規(guī)則：如果在IF函數(shù)中有數(shù)組運(yùn)算，那么無(wú)論IF函數(shù)位于什么函數(shù)參數(shù)中，公式都需要按Ctrl+Shift+Enter鍵。如下圖22所示，在單元格A5中沒(méi)有按Ctrl+Shift+Enter鍵，結(jié)果是錯(cuò)誤值#VALUE!。此外，為了避免潛在的歧義，在這種情形下可以使用單元格A10和A11中的公式。

圖22

SUMPRODUCT函數(shù)特性小結(jié)

下面是SUMPRODUCT函數(shù)的一些重要特性：

1. 能夠?qū)ο嗤笮〉臄?shù)組先相乘再相加。

2. 能夠?qū)?shù)組運(yùn)算的結(jié)果相加。（具有不同大小的數(shù)組，可能使用乘法運(yùn)算和單個(gè)數(shù)組參數(shù)來(lái)得到結(jié)果）

3. 可以處理工作簿引用，而COUNTIF函數(shù)和COUNTIFS函數(shù)則不能。

4. 能夠處理數(shù)組，而諸如COUNTIF和COUNTIFS函數(shù)中的參數(shù)range和criteria_range則不能處理數(shù)組。

5. 如果使用的是Excel 2007或以后的版本，那么對(duì)于多條件計(jì)算來(lái)說(shuō)，使用COUNTIF、COUNTIFS及其它類(lèi)似函數(shù)會(huì)比SUMPRODUCT函數(shù)更有效率。

6. 如果要進(jìn)行多條件計(jì)算且不需要復(fù)制公式，那么使用D-函數(shù)可能比SUMPRODUCT函數(shù)更有效率。

7. 在SUMPRODUCT函數(shù)中使用IF函數(shù)的公式，必須按Ctrl+Shift+Enter鍵。為了避免誤解，最好考慮使用其它公式。 

《Ctrl+Shift+Enter：MasteringExcel Array Formulas》學(xué)習(xí)筆記

完美Excel

歡迎到知識(shí)星球：完美Excel社群，進(jìn)行技術(shù)交流和提問(wèn)，獲取更多電子資料。

完美Excel社群2020.9.13動(dòng)態(tài)

#電子書(shū)# 數(shù)學(xué)01：小平邦彥高中數(shù)學(xué)教材

#電子書(shū)# 數(shù)學(xué)02：MIT線性代數(shù)筆記

#電子書(shū)# 數(shù)學(xué)03：幾何變換