一计数与求和于一身的SUMPRODUCT函数 _知识经验

1为什么要使用SUMPRODUCT函数？
不论是COUNTIF函数、COUNTIFS函数，还是SUMIF函数、SUMIFS函数，在进行判断时，工作表必须存在这样的判断区域，否则这几个函数是不能使用的。但是，在实际工作中，原始数据往往是各种各样的，条件隐含在数据中的情况比比皆是。
下图就是这样的一种情况。工作表左边的4列是原始数据，右侧是需要做的汇总报表，按照产品类别和月份进行汇总。

文章插图
在这个表单数据中，并没有单独的一列来保存产品类别，也没有单独的一列来保存月份（尽管名义上A列是日期，但不是真正的日期），因此，如果要使用SUMIFS函数来求和的话，就必须把C列产品编码的左两位所代表的产品类别分离出来单独保存在一列里，把A列数据中间两位数字取出来转换为月份单独保存另外一列里，这样的操作显然违背了高效数据分析的基本原则。那么，我们能不能在不使用辅助列的情况下，使用一个综合公式来解决呢？答案是肯定的，就是使用SUMPRODUCT函数。
2、SUMPRODUCT函数基本原理
SUMPRODUCT的功能是对多个数组的各个对应的元素进行相乘，然后再把这些乘积相加，语法如下：
=SUMPRODUCT(数组1，数组2，数组3，……)
顾名思义，从函数名字上理解，SUMPRODUCR= SUM + PRODUCT 。
在使用这个函数时，要牢记以下重要的两点：
各个数组必须具有相同的维数。
非数值型的数组元素是作为0处理的。例如，逻辑值TRUE和FALSE都被处理成0了，为了把TRUE还原成数字1，把FALSE还原为数字0，可以把它们都乘以1：TRUE*1，FALSE*1）。
下面我们结合2个示例，来解释SUMPRODUCT函数的基本原理和用法。
示例1
下图是一个各个产品销售单价、销售量和折扣率的数据，现在要求计算所有产品的销售总额、折扣额、销售净额。

文章插图
对于这样的问题，很多人会采用这样的做法：在数据区域的右侧插入2个辅助列，分别计算出每个产品的销售额和折扣额，再使用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)
【一计数与求和于一身的SUMPRODUCT函数】示例2
下图是一个评分表，有5个评价指标，每个指标的权重是不同的，现在要计算每个人的评分，而这些评分就是每个指标分数与指标权重相乘相加的合计数，也就是数学中的，那么，计算公式如下。
=SUMPRODUCT($B$2:$F$2,B5:F5)

文章插图
SUMPRODUCT可以替代COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函数，其原理就是使用条件表达式构建只有数字0和1组成的数组，然后将这个数组中的所有数字1和0相加，就是满足条件的单元格个数；将这些只有数字0和1的数组与实际求和区域的每个单元格数据相乘相加，就得到满足条件的合计数。