SUMPRODUCT函数是Excel中的数学函数,也是一个“神函数”。之所以称之为“神”,是因为它求和、计数、多权重统计、排名,都能完成。 函数解释基本语法为: SUMPRODUCT(array1,[array2], [array3], ...) SUMPRODUCT 函数语法具有下列参数: Array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。 Array2, array3,...:可选。 2 到 255 个数组参数,其相应元素需要进行相乘并求和。 特别注意: 数组参数必须具有相同的维数。否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值#REF!。 1、基本用法SUMPRODUCT函数最基本的用法是: 数组间对应的元素相乘,并返回乘积之和。 如下图: 公式:=SUMPRODUCT(B2:B9,C2:C9) 该公式的含义是: B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8+B9*C9 2、单条件求和如下图,计算女员工业绩得分和: 公式: =SUMPRODUCT((B2:B11='女')*C2:C11) 其中: B2:B11='女': 将B2:B11内每个单元格值与“女”比较,凡是性别为“女”的是TRUE,否则是FALSE,结果返回一组逻辑值: {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;} (B2:B11='女')*C2:C11: 将上述逻辑数组内的值与对应的C2:C11的数值相乘。 3、多条件求和如下图,计算女员工业绩得分高于15的得分和: 公式: =SUMPRODUCT((B2:B11='女')*(C2:C11>15),C2:C11) 多条件求和的通用写法是: =SUMPRODUCT((条件一)*(条件二)*……*(条件N),求和范围) 4、模糊条件求和如下图,计算销售部门女员工业绩得分和: 销售部门不止一个,要查找所有的销售部门,就要按照关键字“销售”查找,就属于模糊查找。 公式: =SUMPRODUCT(ISNUMBER(FIND('销售',A2:A11))*(C2:C11='女'),D2:D11) 其中: FIND('销售',A2:A11): 在A2:A11各单元格值中查找'销售',如果能查到,返回'销售'在单元格值中位置,如果差不到,返回错误值#VALUE!。 本部分的结果是: {#VALUE!;1;1;1;#VALUE!;1;#VALUE!;#VALUE!;1;#VALUE! } ISNUMBER(FIND('销售',A2:A11)): 判断上述数值中各值是不是数字,如果是,返回TRUE,否则返回FALSE,所以,本部分公式的结果是: {FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE} 5、单条件计数计算女员工人数: 公式: =SUMPRODUCT(N(B2:B11='女')) N函数: 语法:N(VALUE); 功能:将不是数值的值转换为数值形式; 不同参数VALUE,对应的返回值: 本示例中,N(B2:B11='女'),是将等于女的值TRUE返回1,不等于女的值FALSE返回0。 6、多条件计数计算女员工业绩得分高于15的人数 公式: =SUMPRODUCT((B2:B11='女')*(C2:C11>15)) 7、模糊条件计数计算销售部门女员工人数 公式: =SUMPRODUCT(ISNUMBER(FIND('销售',A2:A11))*(C2:C11='女')) 8、按月份统计数据要求: 按月份统计销售总额 公式为: =SUMPRODUCT((MONTH($A$2:$A$13)=D2)*($B$2:$B$13)) 9、跨列统计要求: 统计三个仓库的销售总量与库存总量 公式为: =SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3) (此公式中一定要注意相对引用于绝度引用的使用) 10、多权重统计要求: 根据分项得分与权重比例计算总分 公式为: =SUMPRODUCT(B$2:D$2,B3:D3) 11、二维区域统计要求: 统计各销售部门各商品的销售总额 公式为: =SUMPRODUCT(($B$2:$B$13=$E2)*($A$2:$A$13=F$1)*$C$2:$C$13) 12、不间断排名用RANK函数排名,如果有数值相同的情况,会出现名次间断现象,用SUMPRODUCT函数,很好的避免这种名次的间断。 如下图: C6单元格公式为: =SUMPRODUCT(($B$2:$B$7>=B6)/COUNTIF($B$2:$B$7,$B$2:$B$7)) ($B$2:$B$7>=B6),返回值是: {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE} 即:{1;1;1;1;1;0} COUNTIF($B$2:$B$7,$B$2:$B$7),返回值是: {1;1;2;2;1;1} SUMPRODUCT(($B$2:$B$7>=B6)/COUNTIF($B$2:$B$7,$B$2:$B$7)) 即是:SUMPRODUCT({1;1;0.5;0.5;1;0}),即得名次4。 |
|