前言SUMPRODUCT 函数 全部隐藏 ZECEXCEXCELEXCEL帮助中是这么解释的： 说明 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 语法 SUMPRODUCT(array1, [array2], [array3],...) SUMPRODUCT 函数语法具有下列参数 （参数：为操作、事件、方法、属性、函数或过程提供信息的值。）： Array1 必需。其相应元素需要进行相乘并求和的第一个数组参数。 Array2, array3,... 可选。2到 255 个数组参数，其相应元素需要进行相乘并求和。 说明 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。 1 SUMPRODUCT运行机理大家是否看懂了呢，特别是说明中的两条： 第一：所有数组参数的维数必须相同，即所有参数的行和列必须具有相同的个数 第二：所有数组中非数值型元素作为0处理，包括逻辑值TRUE和FALSE,全部认为是0. 下面看一下例子 如果我们用SUMPRODUCT来计算字段1*字段2的值，再相加起来结果如下：=SUMPRODUCT(B20:B24,C20:C24)的结果为78，就是把TRUE和FALSE两个逻辑值统统看成0的结果。 如果我们不用SUMPRODUCT，借助辅助列，来计算是多少呢？ 答案是82，两个结果迥然不同，原因是直接相乘是，TRUE会被强制转换成1，FALSE会被强制转化成0。
那么，如何让SUMPRODUCT得出正确的值呢： 这个需要转换一下：让TRUE和FALSE转换成1或0 可以这样写： =SUMPRODUCT(--B20:B24,--C20:C24) 通过负负得正将TRUE和FALSE 转换成1,0
也可以这样写： =SUMPRODUCT(B20:B24*C20:C24) 通过数组相乘将TRUE和FALSE 转换成1,0
也可以这样： =SUMPRODUCT(B20:B24*1,C20:C24*1) 通过分别*1，将TRUE和FALSE强制转换成1和0
通过上面的论证我们知道，在多条件统计时，将涉及到逻辑值的问题，不能直接将参数用“,”隔开，需要将逻辑值提前转换成1和0，才能得出正确的结果，否则结果会为0，转换的方式如上面三种方式。
2 示例如：计算2017年开工的计划节点数量，公式为 =SUMPRODUCT(--($B$2:$B$7=$B10),--(YEAR(C$2:C$7)=$A10)) 或 =SUMPRODUCT(($B$2:$B$7=$B10)*(YEAR(C$2:C$7)=$A10)) 或 =SUMPRODUCT(($B$2:$B$7=$B10)*1,(YEAR(C$2:C$7)=$A10)*1)
但是直接用 =SUMPRODUCT($B$2:$B$7=$B10,YEAR(C$2:C$7)=$A10) 结果将是0值。 3 通配符问题
增加一下难度：把第一个“开工”增加“（仪式）”，那么如何统计所有含有“开工”的2017年的计划节点呢？ 我试着使用通配符的写法 =SUMPRODUCT(($B$2:$B$7='*'&$B10&'*')*(YEAR(C$2:C$7)=$A10))
把原来的 $B$2:$B$7=$B10 变成 $B$2:$B$7='*'&$B10&'*'， 相当于把原来的“开工”变为“*开工*”
但结果为0，显然不对，说明SUMPRODUCT并不支持通配符，但COUNTIF和sumif都是支持通配符的，这点显然不给力，微软的工程师是不是要改善一下用户体验呢？
如果不支持通配符，还有一种方法就是用查找的方式在B列区域中查找包含“开工”的值，如果返回数字，说明包含“开工”，否则返回错误值，可以用ISNUMBER来判断是否是数字。 正确结果如下： =SUMPRODUCT(ISNUMBER(FIND($B10,$B$2:$B$7))*(YEAR(C$2:C$7)=$A10))
总结SUMPRODUCT函数可以统计多条件计数和求和 多条件统计时，需要将逻辑值转数值 用ISNUMBER(FIND())组合函数可以代替通配符解决包含问题
END |
|
|
