計數(shù)與求和于一身的SUMPRODUCT函數(shù)(一)

計數(shù)與求和于一身的SUMPRODUCT函數(shù)(一)

1 ?為什么要使用SUMPRODUCT函數(shù)？

不論是COUNTIF函數(shù)、COUNTIFS函數(shù)，還是SUMIF函數(shù)、SUMIFS函數(shù)，在進行判斷時，工作表必須存在這樣的判斷區(qū)域，否則這幾個函數(shù)是不能使用的。但是，在實際工作中，原始數(shù)據(jù)往往是各種各樣的，條件隱含在數(shù)據(jù)中的情況比比皆是。

下圖就是這樣的一種情況。工作表左邊的4列是原始數(shù)據(jù)，右側(cè)是需要做的匯總報表，按照產(chǎn)品類別和月份進行匯總。

在這個表單數(shù)據(jù)中，并沒有單獨的一列來保存產(chǎn)品類別，也沒有單獨的一列來保存月份（盡管名義上A列是日期，但不是真正的日期），因此，如果要使用SUMIFS函數(shù)來求和的話，就必須把C列產(chǎn)品編碼的左兩位所代表的產(chǎn)品類別分離出來單獨保存在一列里，把A列數(shù)據(jù)中間兩位數(shù)字取出來轉(zhuǎn)換為月份單獨保存另外一列里，這樣的操作顯然違背了高效數(shù)據(jù)分析的基本原則。那么，我們能不能在不使用輔助列的情況下，使用一個綜合公式來解決呢？答案是肯定的，就是使用SUMPRODUCT函數(shù)。

2、SUMPRODUCT函數(shù)基本原理

SUMPRODUCT的功能是對多個數(shù)組的各個對應(yīng)的元素進行相乘，然后再把這些乘積相加，語法如下：

=SUMPRODUCT(數(shù)組1，數(shù)組2，數(shù)組3，……)

顧名思義，從函數(shù)名字上理解，SUMPRODUCR= SUM + PRODUCT。

在使用這個函數(shù)時，要牢記以下重要的兩點：

各個數(shù)組必須具有相同的維數(shù)。

非數(shù)值型的數(shù)組元素是作為0處理的。例如，邏輯值TRUE和FALSE都被處理成0了，為了把TRUE還原成數(shù)字1，把FALSE還原為數(shù)字0，可以把它們都乘以1：TRUE*1，F(xiàn)ALSE*1）。

下面我們結(jié)合2個示例，來解釋SUMPRODUCT函數(shù)的基本原理和用法。

示例1

下圖是一個各個產(chǎn)品銷售單價、銷售量和折扣率的數(shù)據(jù)，現(xiàn)在要求計算所有產(chǎn)品的銷售總額、折扣額、銷售凈額。

對于這樣的問題，很多人會采用這樣的做法：在數(shù)據(jù)區(qū)域的右側(cè)插入2個輔助列，分別計算出每個產(chǎn)品的銷售額和折扣額，再使用SUM函數(shù)求和。

這里，每個產(chǎn)品的銷售額就是每個產(chǎn)品單價和銷售量相乘的結(jié)果，也就是B列的單價與C列銷售量相乘；每個產(chǎn)品的折扣額就是每個產(chǎn)品單價、銷售量和折扣率相乘的結(jié)果，也就是B列的單價與C列銷售量以及D列折扣率相乘的結(jié)果。

這種先把幾列（或者幾行）數(shù)據(jù)分別相乘，然后再把這些乘積相加的計算問題，Excel給我們提供了一個非常有用的函數(shù)：SUMPRODUCT函數(shù)。

在這個例子中，利用SUMPRODUCT函數(shù)計算所有產(chǎn)品的銷售總額、折扣額、銷售凈額的公式分別如下：

銷售總額：

=SUMPRODUCT(B2:B9,C2:C9)

折扣額：

=SUMPRODUCT(B2:B9,C2:C9,D2:D9)

銷售凈額：

=SUMPRODUCT(B2:B9,C2:C9,1-D2:D9)

示例2

下圖是一個評分表，有5個評價指標(biāo)，每個指標(biāo)的權(quán)重是不同的，現(xiàn)在要計算每個人的評分，而這些評分就是每個指標(biāo)分?jǐn)?shù)與指標(biāo)權(quán)重相乘相加的合計數(shù)，也就是數(shù)學(xué)中的，那么，計算公式如下。

=SUMPRODUCT($B$2:$F$2,B5:F5)

SUMPRODUCT可以替代COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函數(shù)，其原理就是使用條件表達式構(gòu)建只有數(shù)字0和1組成的數(shù)組，然后將這個數(shù)組中的所有數(shù)字1和0相加，就是滿足條件的單元格個數(shù)；將這些只有數(shù)字0和1的數(shù)組與實際求和區(qū)域的每個單元格數(shù)據(jù)相乘相加，就得到滿足條件的合計數(shù)。

但是，條件表達式的結(jié)果是兩個邏輯值TRUE和FALSE，而SUMPRODUCT會把這兩個邏輯值都當(dāng)做0處理，因此，需要將條件表達式乘以數(shù)字1，或者除以數(shù)字1，或者輸入兩個負(fù)號，以使其轉(zhuǎn)換為數(shù)字1和0。

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

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