|
函數(shù)SUMPRODUCT是一個集計數(shù)與求和于一身的函數(shù)，數(shù)組公式中函數(shù)SUMPRODUCT能取代函數(shù)SUM且不用按<Ctrl+Shift+Enter>三鍵結(jié)束。 函數(shù)定義： 在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。 語法格式： SUMPRODUCT(array1, [array2], [array3], ...) array1：必需。其相應(yīng)元素需要進(jìn)行相乘并求和的第一個數(shù)組參數(shù)。 array2, array3,...：可選。2 到 255 個數(shù)組參數(shù)，其相應(yīng)元素需要進(jìn)行相乘并求和。 1、忽略文本求和 輸入公式： =SUMPRODUCT(A2:A10) 函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。 公式就是SUMPRODUCT({1;0;2;0;3;0;0;0;5})，即返回11 2、文本型數(shù)值求和 輸入公式： =SUMPRODUCT(B2:B9*1) B2:B9單元格數(shù)值是文本型，*1是將文本型數(shù)值轉(zhuǎn)換成數(shù)值型 可以用“--”、“+0”、“-0”、 “/1”、“^1”等等來轉(zhuǎn)換 3、單條件計數(shù) 求業(yè)績大于等于5000的個數(shù)： 輸入公式： =SUMPRODUCT(N(B2:B9>=5000)) B2:B9>=5000部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE} 條件成立返回邏輯值TRUE，條件不成立返回邏輯值FALSE 函數(shù)N：將不是數(shù)值形式的值轉(zhuǎn)換為數(shù)值形式。日期轉(zhuǎn)換為序列值，TRUE轉(zhuǎn)換成1，其他值轉(zhuǎn)換成0。 同樣的，也可以用“--”、“+0”、“-0”、 “/1”、“^1”、“*1”等等來轉(zhuǎn)換 N(B2:B9>=5000)部分返回{0;1;1;0;0;1;1;0} 用函數(shù)SUMPRODUCT求和返回4 4、單條件求和 求業(yè)績大于等于5000的和： 輸入公式： =SUMPRODUCT((B2:B9>=5000)*B2:B9) (B2:B9>=5000)部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE} 發(fā)生四則運(yùn)算時，TRUE相當(dāng)于1，F(xiàn)ALSE相當(dāng)于0 (B2:B9>=5000)*B2:B9部分返回{0;7800;6521;0;0;16660;8054;0} 用函數(shù)SUMPRODUCT求和返回39035 5、多條件計數(shù) 求銷售一部業(yè)績大于等于5000的個數(shù)： 輸入公式： =SUMPRODUCT((B2:B9='銷售一部')*(C2:C9>=5000)) 多條件計數(shù)語法： SUMPRODUCT((條件區(qū)域1=條件1)* (條件區(qū)域2=條件2)* (條件區(qū)域N=條件N)) 6、多條件求和 求銷售一部業(yè)績大于等于5000的和： 輸入公式： =SUMPRODUCT((B2:B9='銷售一部')*(C2:C9>=5000)*C2:C9) 多條件求和語法： SUMPRODUCT((條件區(qū)域1=條件1)* (條件區(qū)域2=條件2)* (條件區(qū)域N=條件N)*求和區(qū)域) 或者： SUMPRODUCT((條件區(qū)域1=條件1)* (條件區(qū)域2=條件2)* (條件區(qū)域N=條件N),求和區(qū)域) 該題也可以用公式： =SUMPRODUCT((B2:B9='銷售一部')*(C2:C9>=5000),C2:C9) 多條件求和語法中求和條件前使用“*”與“,”的區(qū)別： 當(dāng)求和區(qū)域中沒有文本時，“*”與“,”可以互相通用； 當(dāng)求和區(qū)域中有文本時使用“,”，若使用“*”會返回錯誤值#VALUE! 7、單條件計算不重復(fù)個數(shù) 求業(yè)務(wù)員不重復(fù)的個數(shù)： 輸入公式： =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) COUNTIF(A2:A9,A2:A9)部分統(tǒng)計區(qū)域A2:A9中每個單元格內(nèi)容在該區(qū)域中出現(xiàn)的次數(shù)，返回{2;1;2;3;1;1;3;3} 1/{2;1;2;3;1;1;3;3}是將重復(fù)數(shù)據(jù)算作1，比如出現(xiàn)2次的是1/2，出現(xiàn)3次的是1/3， 1/COUNTIF(A2:A9,A2:A9)部分返回 {0.5;1;0.5;0.333333333333333;1;1;0.333333333333333;0.333333333333333} 用函數(shù)SUMPRODUCT求和返回5 8、區(qū)間求和 求業(yè)績大于等于5000、小于15000的和： 輸入公式： =SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000)*B2:B9) 或者： =SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000),B2:B9) 9、隔列求和 求入庫、出庫合計：
輸入公式： =SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)，右拉下拉填充。 10、數(shù)組相乘 求總提成：
輸入公式： =SUMPRODUCT(B2:B9*C2:C9) B2:B9*C2:C9返回每個業(yè)績*對應(yīng)提成比例，即返回{45.06;390;326.05;136.89;2001.5;1466;402.7;138.75} 然后用函數(shù)SUMPRODUCT求和 11、有條件的數(shù)組相乘 求“銷售一部”的總提成：
輸入公式： =SUMPRODUCT((A2:A9='銷售一部')*C2:C9*D2:D9) (A2:A9='銷售一部')條件成立返回TRUE，條件不成立返回FALSE，即{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE} 然后*C2:C9返回的是銷售一部的業(yè)績，即{1502;7800;0;0;20015;14660;0;4625} 再*D2:D9返回的就是銷售一部對應(yīng)的提成，即{45.06;390;0;0;2001.5;1466;0;138.75} 最后用函數(shù)SUMPRODUCT求和 12、帶'~'求和 求業(yè)務(wù)員中有帶“~”的業(yè)績和：
輸入公式： =SUMPRODUCT(ISNUMBER(FIND('~',B2:B9))*C2:C9) 函數(shù)SUMPRODUCT不能使用通配符，但可以與函數(shù)FIND相結(jié)合完成對應(yīng)功能 FIND('~',B2:B9)部分找到“~”在各業(yè)務(wù)員字符串中的位置，沒有“~”返回錯誤值#VALUE!，該部分返回{2;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!} 函數(shù)ISNUMBER：判斷一個值是否為數(shù)值，返回TRUE或FALSE。 ISNUMBER(FIND('~',B2:B9))部分返回{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} 再*C2:C9返回帶“~”的各業(yè)務(wù)員的業(yè)績{1502;0;6521;0;0;14660;0;0} 最后用函數(shù)SUMPRODUCT求和。 推薦學(xué)習(xí)★★★★★ |
|
|
