sumproduct函数竟然不支持通配符

前言

SUMPRODUCT 函数

EXCEL帮助中是这么解释的：

说明

在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

语法

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