數(shù)學函數(shù)SUMPRODUCT應(yīng)用實例.doc

對于sumproduct使用過程中*1的解釋說明sumproduct函數(shù)，逗號分割的各個參數(shù)必須為數(shù)字型數(shù)據(jù)，如果是判斷的結(jié)果邏輯值，就要乘1轉(zhuǎn)換為數(shù)字。如果不用逗號，直接用*號連接，就相當于乘法運算，就不必添加*1。數(shù)學函數(shù)SUMPRODUCT應(yīng)用實例一、基本功能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ù)52 3 4 12 105 5 6 5 209 7 8 #N/A 307 2 7 9 KL1 6 2 8 2基本計算區(qū)域計算要求：計算A、B、C三列對應(yīng)數(shù)據(jù)乘積的和。公式：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)計算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6即三個區(qū)域A2:A6,B2:B6,C2:C6同行數(shù)據(jù)積的和。返回值788。數(shù)組計算要求：把上面數(shù)據(jù)表中的三個區(qū)域A2:A6,B2:B6,C2:C6數(shù)據(jù)按一個區(qū)域一個數(shù)組，計算對應(yīng)數(shù)組積的和。把A2:A6,B2:B6,C2:C6分別作為一個數(shù)組,即A2:A6表示為數(shù)組-2;5;9;7;1B2:B6表示為數(shù)組-3;5;7;2;6C2: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ù)，用逗號,分隔?？赡艹霈F(xiàn)的錯誤編輯公式時，引用的數(shù)據(jù)區(qū)域大小不一致導(dǎo)致計算錯誤，返回值為#VALUE！。示例：在上面的數(shù)據(jù)表中，計算A列與B列數(shù)據(jù)區(qū)域積的和。公式：=SUMPRODUCT(A2:A6,B2:B5)或=SUMPRODUCT(A2:A6,B2:B8)都會返回錯誤值#VALUE！。所以在用SUMPRODUCT函數(shù)時，引用的數(shù)據(jù)區(qū)域大小要一致。數(shù)據(jù)區(qū)域中有錯誤值時，計算出現(xiàn)錯誤值。示例：在上面的數(shù)據(jù)表中，計算數(shù)據(jù)區(qū)域A2：A6與D2：D6對應(yīng)積的和。公式：=SUMPRODUCT(A2:A6,D2:D6)因為D2：D6中有錯誤值#N/A，所以公式返回值為錯誤值#N/A。數(shù)據(jù)區(qū)域引用不能整列引用。示例：計算上面數(shù)據(jù)表中A2：A6和B2：B6區(qū)域?qū)?yīng)數(shù)據(jù)積的和，正確公式為=SUMPRODUCT(A2:A6,B2:B6)則返回正確的計算值94。如果用公式=SUMPRODUCT(A:A,B:B)則返回錯誤值#NUM！。數(shù)據(jù)區(qū)域有文本，計算中系統(tǒng)默認文本值為0。示例：在上面數(shù)據(jù)表中，計算A2：A6和E2：E6區(qū)域中對應(yīng)數(shù)據(jù)積的和。公式=SUMPRODUCT(A2:A6,E2:E6)返回值是392，其中E5是文本KL，則A5*E5=0。二、用于多條件計數(shù)用數(shù)學函數(shù)SUMPRODUCT計算符合2個及以上條件的數(shù)據(jù)個數(shù)數(shù)據(jù)表如下數(shù)據(jù)表所示：ABC1姓名性別職稱2A男中一3B女中二4C女中一5D男中一6E女中一7F男中二8G女中二9H男中一10I男中一11J女中一統(tǒng)計表E列 F列 G列中一 中二男女要求：統(tǒng)計上面數(shù)據(jù)表中男、女性中分別是中一、中二的人數(shù)。如下圖片，A1：C11數(shù)據(jù)區(qū)域，在E1：G3區(qū)域統(tǒng)計男、女中中一和中二的人數(shù)是多少。計算結(jié)果公式在數(shù)據(jù)統(tǒng)計區(qū)域中的F2單元格編輯如下公式：=SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1)向下復(fù)制到F3，向右復(fù)制到G3。公式分解及分析條件1$B$2:$B$11=$E2在計算過程中，條件1是一個數(shù)組，返回多值，寫成公式如下：=$B$2:$B$11=$E$2具體操作：選中10個連續(xù)的單元格，輸入上述公式后，按Ctrl+Shift+回車鍵確認，返回10個邏輯判斷值TRUE；FALSE；FALSE；TRUE；FALSE；TRUE；FALSE；TRUE；TRUE；FALSE。這10個邏輯判斷值構(gòu)成一個新的由TRUE主FALSE組成的數(shù)組1。條件2$C$2:$C$11=F$1與條件1相同，是一個數(shù)組，返回多值，寫成公式如下：=$C$2:$C$11=F$1具體操作：同樣的方法，選中對應(yīng)的10連續(xù)單元格，輸入上述公式，按Ctrl+Shift+回車鍵確認，返回10個邏輯判斷值TRUE；FALSE；TRUE；TRUE；TRUE；FALSE； FALSE；TRUE； TRUE；TRUE。這10個邏輯判斷值構(gòu)成另一個新的由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其中,邏輯值TRUE與FALSE參與計算時:TRUE=1，F(xiàn)ALSE=0，TRUE*TRUE=1，TRUE*FALSE=FALSE*TRUE=0，F(xiàn)ALSE*FALSE=0因此數(shù)組1*數(shù)組2=1;0;0;1;0;0;0;1;1;0由1和0構(gòu)成了一個新的數(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)計算其乘積的和，即：=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ù)組，分號相當于換行。兩個數(shù)組相乘是同一行的對應(yīng)兩個數(shù)相乘。三、用于多條件求和。對于計算符合某一個條件的數(shù)據(jù)求和，可以用SUMIF函數(shù)來解決。如果要計算符合2個以上條件的數(shù)據(jù)求和，用SUMIF函數(shù)就不能夠完成了。這就可以用函數(shù)SUMPRODUCT。用函數(shù)SUMPRODUCT計算符合多條件的數(shù)據(jù)和，基本格式是：SUMPRODUCT（條件1*條件2*，求和數(shù)據(jù)區(qū)域）數(shù)據(jù)表ABCD1姓名性別職稱課時2A男中一153B女中二164C女中一145D男中一136E女中一187F男中二158G女中二169H男中一1410I男中一1711J女中一18要求：計算男、女分別是中一或中二的總課時數(shù)。統(tǒng)計表F列 G列 H列 中一 中二男女在G2中編輯公式=SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)回車確認后向下向右復(fù)制公式到H3單元格。公式釋義：性別區(qū)域$B$2:$B$11中滿足條件男和職稱區(qū)域$C$2:$C$11中滿足條件中一的數(shù)據(jù)，通過判斷計算后由1和0組成一個新的數(shù)據(jù)區(qū)域，這個新的數(shù)據(jù)區(qū)域再和課時區(qū)域$D$2:$D$11中的對應(yīng)數(shù)據(jù)相乘后求和。公式對比：到此，對函數(shù)SUMPRODUCT用來計數(shù)和求和，試作一對比計數(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)不難看出，求和公式在原來的計數(shù)公式中，在相同判斷條件下，增加了一個求和的數(shù)據(jù)區(qū)域。也就是說，用函數(shù)SUMPRODUCT求和，函數(shù)需要的參數(shù)一個是進行判斷的條件，另一個是用來求和的數(shù)據(jù)區(qū)域。四、用于排名次如下A列數(shù)據(jù)：A列 B列數(shù)據(jù) 名次56658965567890526090需要排出區(qū)域A2：A11中10個數(shù)據(jù)的名次。在B2中編輯公式：=SUMPRODUCT(A2$A$2:$A$11)*1)+1向下復(fù)制到單元格B11。公式釋義：用A2到$A$2:$A$11(用絕對引用$保證公式在向下復(fù)制時整個數(shù)據(jù)區(qū)域不發(fā)生變化)中進行比較,當A2$A$2:$A$11成立時,則返回TRUE;如果A2$A$2:$A$11不成立就返回FALSE。所以數(shù)組公式=A2$A$2:$A$11返回一個由TRUE和FALSE構(gòu)成的邏輯數(shù)組。把數(shù)組公式=A2$A$2:$A$11構(gòu)成的邏輯數(shù)組乘1，得到一個由0和1構(gòu)成的新數(shù)組。SUMPRODUCT再對由0和1構(gòu)成的新數(shù)組求和，表示在數(shù)據(jù)區(qū)域$A$2:$A$11中比A2大的數(shù)據(jù)個數(shù)。所以A2在數(shù)據(jù)區(qū)域$A$2:$A$11內(nèi)排列的位次應(yīng)該是比A2大的數(shù)據(jù)個數(shù)+1，即公式=數(shù)據(jù)區(qū)域$A$2:$A$11內(nèi)比A2大的個數(shù)+1=SUMPRODUCT(A2$A$2:$A$11)*1)+1公式向下復(fù)制，則依次對$A$2:$A$11中每一個數(shù)據(jù)重復(fù)進行上述判斷求和，從而排出數(shù)據(jù)區(qū)域$A$2:$A$11中每一個數(shù)據(jù)的位次。很顯然，對于數(shù)據(jù)區(qū)域$A$2:$A$11中相同的數(shù)，判斷和計算結(jié)果是相同的，也就是排出來的位次相同。所以用SUMPRODUCT函數(shù)排出來的名次，與直接用RANK函數(shù)排出來的名次是一樣的，有重復(fù)名次，但最大位次數(shù)不超過數(shù)據(jù)區(qū)域$A$2:$A$11中的總數(shù)據(jù)個數(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 865 5 589 3 365 5 656 8 978 4 490 1 152 10 1060 7 790 1 2綜上所述，對于多條件的計數(shù)或者求和，可以用數(shù)學函數(shù)SUMPRODUCT來比較方便的解決。在使用函數(shù)時，進行數(shù)據(jù)引用的單元格區(qū)域或數(shù)組應(yīng)該大小一致，不能采取整列引用（形如A：A）。如果跨表使用函數(shù)SUMPRODUCT，與其它函數(shù)跨表引用數(shù)據(jù)一樣，數(shù)據(jù)區(qū)域前面應(yīng)該標明工作表名稱。color=#EE1D24,strength=3);計數(shù)公式中最關(guān)鍵的