计数和计数相加的SUMPRODUCT函数(1)

1为什么要使用SUMPRODUCT函数?

无论是COUNTIF函数、COUNTIFS函数、SUMIF函数还是SUMIFS函数,在进行判断时,工作表都必须有这样的判断区域,否则这些函数都无法使用。但在实际工作中,原始数据往往是各种各样的,数据中隐含的条件无处不在。

下图就是这样的情况。工作表左边四栏是原始数据,右边是要做的汇总报表,按产品类别和月份汇总。

计数与求和于一身的SUMPRODUCT函数(一)

在这个表单数据中,没有单独的列来存储产品类别和月份(虽然A列名义上是日期,但不是实际日期)。所以如果要用SUMIFS函数求和,就必须把C列产品代码左两位表示的产品类别分开单独存放在一列,把A列数据的中间两位取出换算成月份单独保存在另一列。这样的操作显然违反了高效数据。那么,可以不用辅助柱,用综合公式求解吗?答案是肯定的,就是要用SUMPRODUCT函数。

2.和积函数的基本原理

SUMPRODUCT的作用是将多个数组的对应元素相乘,然后将这些乘积相加。语法如下:

=SUMPRODUCT(数组1,数组2,数组3,…)

顾名思义,从函数名来看,SUMPRODUCR= SUM+PRODUCT。

使用此功能时,请记住以下两点:

每个数组必须具有相同的维度。

非数字数组元素被视为0。例如,逻辑值TRUE和FALSE都被视为0。为了将TRUE还原为数字1,FALSE还原为数字0,可以将它们乘以1: true * 1,FALSE*1)。

下面结合两个例子来说明SUMPRODUCT函数的基本原理和用法。

示例1

下图是各产品的销售单价、销量、折扣率等数据。现在需要计算所有产品的总销售额、折扣额和净销售额。

计数与求和于一身的SUMPRODUCT函数(一)

对于这样的问题,很多人会采用这样的做法:在数据区右侧插入两个辅助列,分别计算每个产品的销售金额和折扣金额,然后用SUM函数进行求和。

这里每个产品的销量是每个产品的单价和销量相乘的结果,即B列的单价乘以C列的销量;每个产品的折扣金额是每个产品的单价、销量和折扣率相乘的结果,即B列的单价、C列的销量和d列的折扣率。

这种计算问题是将几列(或几行)数据分别相乘,然后将这些乘积相加。Excel为我们提供了一个非常有用的函数:SUMPRODUCT函数。

在此示例中,使用SUMPRODUCT函数计算所有产品的总销售额、折扣额和净销售额的公式如下:

总销售额:

=SUMPRODUCT(B2:B9,C2:C9)

折扣金额:

=SUMPRODUCT(B2:B9,C2:C9,D2:D9)

净销售额:

=SUMPRODUCT(B2:B9,C2:C9,1-D2:D9)

示例2

下图是一个评分表,有五个评价指标,每个指标的权重不同。现在,我们要计算每个人的评分,而这些评分就是每个指标得分的总数乘以指标权重,也就是数学中的。然后,计算公式如下。

=SUMPRODUCT($B$2:$F$2,B5:F5)

计数与求和于一身的SUMPRODUCT函数(一)

SUMPRODUCT可以代替COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函数。它的原理是用条件表达式构建一个只由数字0和1组成的数组,然后将这个数组中所有的数字1和0相加,就是满足条件的单元格个数;将这些只有数字0和1的数组与实际求和区域中每个单元格的数据相乘并相加,得到满足条件的总数。

但是,条件表达式的结果是两个逻辑值,TRUE和FALSE,SUMPRODUCT会将这两个逻辑值都视为0。所以需要将条件表达式乘以数字1,或者除以数字1,或者输入两个负号,转换成数字1和0。