1．函數(shù)SUMPRODUCT的功能
返回相應(yīng)的區(qū)域或數(shù)組乘積的和。
2．基本格式
SUMPRODUCT（數(shù)據(jù)1，數(shù)據(jù)2，……，數(shù)據(jù)30）
3．示例
數(shù)據(jù)表
A列 B列 C列 D列 E列
數(shù)據(jù)1數(shù)據(jù)2數(shù)據(jù)3數(shù)據(jù)4數(shù)據(jù)5
2 3 4 12 10
5 5 6 5 20
9 7 8 #N/A 30
7 2 7 9 KL
1 6 2 8 2
⑴基本計(jì)算
①區(qū)域計(jì)算
要求：計(jì)算A、B、C三列對應(yīng)數(shù)據(jù)乘積的和。
公式：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)
計(jì)算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6
即三個(gè)區(qū)域A2:A6,B2:B6,C2:C6同行數(shù)據(jù)積的和。
返回值788。
②數(shù)組計(jì)算
要求：把上面數(shù)據(jù)表中的三個(gè)區(qū)域A2:A6,B2:B6,C2:C6數(shù)據(jù)按一個(gè)區(qū)域一個(gè)數(shù)組，計(jì)算對應(yīng)數(shù)組積的和。
把A2:A6,B2:B6,C2:C6分別作為一個(gè)數(shù)組,即
A2:A6表示為數(shù)組-{2;5;9;7;1}
B2:B6表示為數(shù)組-{3;5;7;2;6}
C2:C6表示為數(shù)組-{4;6;8;7;2}
公式：
=SUMPRODUCT({2;5;9;7;1},{3;5;7;2;6},{4;6;8;7;2})=788
注意：
數(shù)組數(shù)據(jù)用大括號{}括起來。
行數(shù)據(jù)之間用分號"；"分隔，如果是同一行的數(shù)據(jù)，用逗號","分隔。
⑵可能出現(xiàn)的錯(cuò)誤
①編輯公式時(shí)，引用的數(shù)據(jù)區(qū)域大小不一致導(dǎo)致計(jì)算錯(cuò)誤，返回值為#VALUE！。
示例：
在上面的數(shù)據(jù)表中，計(jì)算A列與B列數(shù)據(jù)區(qū)域積的和。
公式：
=SUMPRODUCT(A2:A6,B2:B5)
或
=SUMPRODUCT(A2:A6,B2:B8)
都會返回錯(cuò)誤值#VALUE！。
所以在用SUMPRODUCT函數(shù)時(shí)，引用的數(shù)據(jù)區(qū)域大小要一致。
②數(shù)據(jù)區(qū)域中有錯(cuò)誤值時(shí)，計(jì)算出現(xiàn)錯(cuò)誤值。
示例：
在上面的數(shù)據(jù)表中，計(jì)算數(shù)據(jù)區(qū)域A2：A6與D2：D6對應(yīng)積的和。
公式：=SUMPRODUCT(A2:A6,D2:D6)
因?yàn)镈2：D6中有錯(cuò)誤值#N/A，所以公式返回值為錯(cuò)誤值#N/A。
③數(shù)據(jù)區(qū)域引用不能整列引用。
示例：
計(jì)算上面數(shù)據(jù)表中A2：A6和B2：B6區(qū)域?qū)?yīng)數(shù)據(jù)積的和，正確公式為
=SUMPRODUCT(A2:A6,B2:B6)則返回正確的計(jì)算值94。如果用公式=SUMPRODUCT(A:A,B:B)則返回錯(cuò)誤值#NUM！。
④數(shù)據(jù)區(qū)域有文本，計(jì)算中系統(tǒng)默認(rèn)文本值為0。
示例：
在上面數(shù)據(jù)表中，計(jì)算A2：A6和E2：E6區(qū)域中對應(yīng)數(shù)據(jù)積的和。
公式=SUMPRODUCT(A2:A6,E2:E6)返回值是392，其中E5是文本KL，則A5*E5=0。
二、用于多條件計(jì)數(shù)
用數(shù)學(xué)函數(shù)SUMOPRODUCT計(jì)算符合2個(gè)及以上條件的數(shù)據(jù)個(gè)數(shù)
⑴數(shù)據(jù)表
如下數(shù)據(jù)表所示：
A列 B列 C列
姓名性別職稱
A 男中一
B 女中二
C 女中一
D 男中一
E 女中一
F 男中二
G 女中二
H 男中一
I 男中一
J 女中一
統(tǒng)計(jì)表
E列 F列 G列
中一 中二
男
女
要求：
統(tǒng)計(jì)上面數(shù)據(jù)表中男、女性中分別是中一、中二的人數(shù)。
如下圖片，A1：C11數(shù)據(jù)區(qū)域，在E1：G3區(qū)域統(tǒng)計(jì)男、女中中一和中二的人數(shù)是多少。
⑵公式
在數(shù)據(jù)統(tǒng)計(jì)區(qū)域中的F2單元格編輯如下公式：
=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
向下復(fù)制到F3，向右復(fù)制到G3。
⑶公式分解及分析
①條件1——$B$2:$B$11=$E2
在計(jì)算過程中，條件1是一個(gè)數(shù)組，返回多值，寫成公式如下：
=$B$2:$B$11=$E2
具體操作：
選中10個(gè)連續(xù)的單元格，輸入上述公式后，按Ctrl+Shift+回車鍵確認(rèn)，返回10個(gè)邏輯判斷值——TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。
這10個(gè)邏輯判斷值構(gòu)成一個(gè)新的由TRUE主FALSE組成的數(shù)組1。
②條件2——$C$2:$C$11=F$1
與條件1相同，是一個(gè)數(shù)組，返回多值，寫成公式如下：
=$C$2:$C$11=F$1
具體操作：
同樣的方法，選中對應(yīng)的10連續(xù)單元格，輸入上述公式，按Ctrl+Shift+回車鍵確認(rèn)，返回10個(gè)邏輯判斷值——TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE。
這10個(gè)邏輯判斷值構(gòu)成另一個(gè)新的由TRUE主FALSE組成的數(shù)組2。
③($B$2:$B$11=$E2)*($C$2:$C$11=F$1)
由新構(gòu)成的數(shù)組1乘以數(shù)組2，即：
=($B$2:$B$11=$E2)*($C$2:$C$11=F$1)
={數(shù)組1*數(shù)組2}
={ TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE }
*{ TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE }
={TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;TRUE*TRUE;FALSE*TRUE;TRUE*FALSE; FALSE*FALSE;TRUE*TRUE;TRUE*TRUE; FALSE*TRUE}
={1;0;0;1;0;0;0;1;1;0}
其中,邏輯值TRU*與**LSE參與計(jì)算時(shí):
TRUE=1
FALSE=0
TRUE*TRUE=1
TRUE*FALSE=FALSE*TRUE=0
FALSE*FALSE=0
因此
{數(shù)組1*數(shù)組2}={1;0;0;1;0;0;0;1;1;0}
由1和0構(gòu)成了一個(gè)新的數(shù)組3。
④SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
函數(shù)SUMPRODUCT對新的數(shù)組3中的所有數(shù)據(jù)求和。即：
=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
=SUMPRODUCT({數(shù)組1*數(shù)組2})
=SUMPRODUCT({數(shù)組3})
=SUMPRODUCT({1;0;0;1;0;0;0;1;1;0})=4
這里需要說明的是，公式編輯按照函數(shù)SUMPRODUCT的一般格式，可以編輯如下等效的公式：
=SUMPRODUCT(($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1)
函數(shù)SUMPRODUCT的作用是對數(shù)組($B$2:$B$11=$E2)與數(shù)組($C$2:$C$11=F$1))計(jì)算其乘積的和，即：
=SUMPRODUCT(($B$2:$B$11=$E2)*1，($C$2:$C$11=F$1)*1)
=SUMPRODUCT({ TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE }*1，{ TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE }*1)
=SUMPRODUCT({1;0;0;1;0;1;0;1;1;0},{1;0;1;1;1;0;0;1;1;1})
=SUMPRODUCT({1*1;0*0;0*1;1*1;0*1;1*0;0*0;1*1;1*1,0*1})
=SUMPRODUCT({1;0;0;1;0;0;0;1;1,0})=4
注意：
TRUE*1=1，F(xiàn)ALSE*1=1*FALSE=0，TRUE*0=0*TRUE=0 。
數(shù)組中用分號分隔，表示數(shù)組是一列數(shù)組，分號相當(dāng)于換行。兩個(gè)數(shù)組相乘是同一行的對應(yīng)兩個(gè)數(shù)相乘。
三、用于多條件求和
對于計(jì)算符合某一個(gè)條件的數(shù)據(jù)求和，可以用SUMIF函數(shù)來解決。如果要計(jì)算符合2個(gè)以上條件的數(shù)據(jù)求和，用SUMIF函數(shù)就不能夠完成了。這就可以用函數(shù)SUMPRODUCT。
用函數(shù)SUMPRODUCT計(jì)算符合多條件的數(shù)據(jù)和，其基本格式是：
SUMPRODUCT（條件1*條件2*……，求和數(shù)據(jù)區(qū)域）
數(shù)據(jù)表
A列B列C列D列
姓名性別職稱課時(shí)
A男中一15
B女中二16
C女中一14
D男中一13
E女中一18
F男中二15
G女中二16
H男中一14
I男中一17
J女中一18
要求：
計(jì)算男、女分別是中一或中二的總課時(shí)數(shù)。
統(tǒng)計(jì)表
F列 G列 H列
中一 中二
男
女
在G2中編輯公式
=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)
回車確認(rèn)后向下向右復(fù)制公式到H3單元格。
公式釋義：
性別區(qū)域$B$2:$B$11中滿足條件男和職稱區(qū)域$C$2:$C$11中滿足條件中一的數(shù)據(jù)，通過判斷計(jì)算后由1和0組成一個(gè)新的數(shù)據(jù)區(qū)域，這個(gè)新的數(shù)據(jù)區(qū)域再和課時(shí)區(qū)域$D$2:$D$11中的對應(yīng)數(shù)據(jù)相乘后求和。
公式對比：
到此，對函數(shù)SUMPRODUCT用來計(jì)數(shù)和求和，試作一對比
計(jì)數(shù)公式
=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))
求和公式
=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)
不難看出，求和公式在原來的計(jì)數(shù)公式中，在相同判斷條件下，增加了一個(gè)求和的數(shù)據(jù)區(qū)域。
也就是說，用函數(shù)SUMPRODUCT求和，函數(shù)需要的參數(shù)一個(gè)是進(jìn)行判斷的條件，另一個(gè)是用來求和的數(shù)據(jù)區(qū)域。
四、用于排名次
如下A列數(shù)據(jù)：
A列 B列
數(shù)據(jù) 名次
56
65
89
65
56
78
90
52
60
90
需要排出區(qū)域A2：A11中10個(gè)數(shù)據(jù)的名次。
在B2中編輯公式：
= SUMPRODUCT((A2<$A$2:$A$11)*1)+1
向下復(fù)制到單元格B11。
公式釋義：
用A2到$A$2:$A$11(用絕對引用$保證公式在向下復(fù)制時(shí)整個(gè)數(shù)據(jù)區(qū)域不發(fā)生變化)中進(jìn)行比較,當(dāng)A2<$A$2:$A$11成立時(shí),則返回TRUE;如果A2<$A$2:$A$11不成立就返回FALSE。所以數(shù)組公式=A2<$A$2:$A$11返回一個(gè)由TRUE和FALSE構(gòu)成的邏輯數(shù)組。
把數(shù)組公式=A2<$A$2:$A$11構(gòu)成的邏輯數(shù)組乘1，得到一個(gè)由0和1構(gòu)成的新數(shù)組。
SUMPRODUCT再對由0和1構(gòu)成的新數(shù)組求和，表示在數(shù)據(jù)區(qū)域$A$2:$A$11中比A2大的數(shù)據(jù)個(gè)數(shù)。
所以A2在數(shù)據(jù)區(qū)域$A$2:$A$11內(nèi)排列的位次應(yīng)該是比A2大的數(shù)據(jù)個(gè)數(shù)+1，即公式
=數(shù)據(jù)區(qū)域$A$2:$A$11內(nèi)比A2大的個(gè)數(shù)+1
=SUMPRODUCT((A2<$A$2:$A$11)*1)+1
公式向下復(fù)制，則依次對$A$2:$A$11中每一個(gè)數(shù)據(jù)重復(fù)進(jìn)行上述判斷求和，從而排出數(shù)據(jù)區(qū)域$A$2:$A$11中每一個(gè)數(shù)據(jù)的位次。
很顯然，對于數(shù)據(jù)區(qū)域$A$2:$A$11中相同的數(shù)，判斷和計(jì)算結(jié)果是相同的，也就是排出來的位次相同。所以用SUMPRODUCT函數(shù)排出來的名次，與直接用RANK函數(shù)排出來的名次是一樣的，有重復(fù)名次，但最大位次數(shù)不超過數(shù)據(jù)區(qū)域$A$2:$A$11中的總數(shù)據(jù)個(gè)數(shù)。
如果希望排出的位次沒有重復(fù)，而數(shù)據(jù)區(qū)域$A$2:$A$11中相同數(shù)據(jù)的位次按數(shù)據(jù)出現(xiàn)的先后順序排位，可以用下面的公式：
=SUMPRODUCT((A2<$A$2:$A$11)*1)+COUNTIF($A$2:A2,A2)
公式向下復(fù)制即可。
兩種排名對比如下表所示：
A列 B列 C列
數(shù)據(jù) 名次重復(fù)順序名次
56 8 8
65 5 5
89 3 3
65 5 6
56 8 9
78 4 4
90 1 1
52 10 10
60 7 7
90 1 2
綜上所述，對于多條件的計(jì)數(shù)或者求和，可以用數(shù)學(xué)函數(shù)SUMPRODUCT來比較方便的解決。在使用函數(shù)時(shí)，進(jìn)行數(shù)據(jù)引用的單元格區(qū)域或數(shù)組應(yīng)該大小一致，不能采取整列引用（形如A：A）。如果跨表使用函數(shù)SUMPRODUCT，與其它函數(shù)跨表引用數(shù)據(jù)一樣，數(shù)據(jù)區(qū)域前面應(yīng)該標(biāo)明工作表名稱。
[color=#EE1D24,strength=3);]計(jì)數(shù)公式中最關(guān)鍵的是確定計(jì)數(shù)的判斷條件。
[color=#EE1D24,strength=3);]求和公式在原來的計(jì)數(shù)公式中，在相同判斷條件下增加了一個(gè)求和的數(shù)據(jù)區(qū)域。[color=#EE1D24,strength=3);]用函數(shù)SUMPRODUCT求和，函數(shù)需要的參數(shù)一個(gè)是進(jìn)行判斷的條件，另一個(gè)是用來求和的數(shù)據(jù)區(qū)域。
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點(diǎn)擊舉報(bào)。
