sumproduct函数竟然不支持通配符.docxVIP

sumproduct函数竟然不支持通配符 前言SUMPRODUCT函数全部隐藏 ZECEXCEXCELEXCEL 帮助中是这么解释的：说明在给定 的几组数组中，将数组间对应的元素相乘， 并返回乘积之和。 语法 SUMPRODUCT(array1, [array2], [array3],...)SUMPRODUCT 函数语法具有下列参数 (参数: 为操作、事件、方法、属性、函数或过程提供信息的值。 )： Array 1 必需。其相应元素需要进行相乘并求和的第一个数 组参数。 Array2, array3,... 可选。2至(J 255个数组参数， 其相应元素需要进行相乘并求和。说明 数组参数必须具有 相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE! o 函数SUMPRODUCT将非数值型的数组元素 作为0处理。1 SUMPRODUCT 运行机理 大家是否看懂了呢，特别是说明中的两条：第一：所有数组 参数的维数必须相同，即所有参数的行和列必须具有相同的 个数第二：所有数组中非数值型元素作为 0处理，包括逻辑 值TRUE和FALSE,全部认为是0.下面看一下例子 如果我们用SUMPRODUCT来计算字段什字段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)=$A1 0))或 =SUMPRODUCT(($B$2:$B$7=$B10)*1 ,(Y EAR(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=,*,amp;$B10amp；5YEA R(C$2:C$7)=$A10))把原来的$B$2:$B$7=$B10变成$B$2:$B$7=*amp;$B1 R(C$2:C$7)=$A10)) 把原来的$B$2:$B$7=$B10变成 $B$2:$B$7=*amp;$B10amp;* 相当于把原来的 “开工” 变为吩开工1 但结果为0,显然不对，说明SUMPRODUCT 并不支持通配符， 但COUNTIF和sumif都是支持通配符的, 这点显然不给力，微软的工程师是不是要改善一下用户体验 呢？ 如果不支持通配符，还有一种方法就是用查找的方式在 B 列区域中查找包含“开工”的值，如果返回数字，说明包含 “开工”，否则返回错误值，可以用 ISNUMBER来判断是否 是数字。正确结果如下： =SUMPRODUCT(ISNUMBER(FIND($B10,$B$2:$B$7))*( YE AR(C$2:C$7)=$A10)) 总结 SUMPRODUCT函数可以统计多条件计数和求和多条件统 计时，需要将逻辑值转数值用 ISNUMBER(FIND())组合函数 可以代替通配符解决包含问题 END