Excel函数学习之无所不能的SUMPRODUCT!

时间:2023-04-08
阅读:

1、SUMPRODUCT的实质

要说SUMPRODUCT函数的话,真的非常简单,就是得到两列数据的乘积之和,我们用一个简单的例子来说明函数的基本功能:

Excel函数学习之无所不能的SUMPRODUCT!

上图是一个非常简单的表格,要算出总价一般都是将单价*数量算出来再求和,结果如D8所示。如果使用了SUMPRODUCT函数的话,就可以直接利用单价和数量计算出总价,公式1为:=SUMPRODUCT(B2:B7,C2:C7),结果如D9所示。

Excel函数学习之无所不能的SUMPRODUCT!

在这个公式里,使用了两个参数,分别是单价区域(B2:B7)和数量区域(C2:C7),函数的作用就是将第一参数(单价)与第二参数(数量)中的数据对应相乘后再求和。

2、大多数错误的原因

很多朋友在使用这个函数的时候,经常会得到错误值,大多数是因为区域大小选择不一致,例如下面这种情况,第一个参数有7个单元格而第二个参数只有6个单元格:

Excel函数学习之无所不能的SUMPRODUCT!

使用SUMPRODUCT函数必须要确保每个参数的区域大小相同,但很多朋友没有注意到这一点。

3、另一种常见写法,逗号变乘号(*)

就这个例子来说,还有一种写法更为常见,公式是这样的:=SUMPRODUCT(B2:B7*C2:C7)

Excel函数学习之无所不能的SUMPRODUCT!

可以看到其计算结果与=SUMPRODUCT(B2:B7,C2:C7)是一致的。

一致的结果导致了很多朋友都百思不得其解的一个问题:二者有何差别?

4、逗号和乘号(*)的差别

虽然只是将第一个公式里的逗号变成了乘号(*),但是公式的意义发生了变化。第一个公式(SUMPRODUCT(B2:B7,C2:C7))有两个参数,而第二个公式(B2:B7*C2:C7)是一个参数。(判断有几个参数要看是不是有逗号去分隔开。)第一个公式中,两个区域相乘这一步是由函数来完成的,函数做了两件事,先让两个区域的数据对应相乘,再把乘积相加。在第二个公式中,两个区域相乘是由数组计算来完成的,函数只做了一件事,就是把乘积值相加。

意义的变化有何影响呢?

我们还是通过例子来看:

Excel函数学习之无所不能的SUMPRODUCT!

在上图这个公式中用的是逗号(,),有两个独立的参数。SUMPRODUCT函数首先让两组数据对应相乘,相乘的时候会检查数据并把非数值型数据作为0处理,然后在把乘积相加。因此,B1“单价”和C1“数量”会当成0来处理,公式可以得到正确结果。

Excel函数学习之无所不能的SUMPRODUCT!

当我们把逗号换成*号后,公式结果错误。为什么呢?SUMPRODUCT函数这时只负责把乘积相加。参数B2:B7*C2:C7是数组乘法运算,因为计算的区域中包含了文字(文字是不能进行乘法运算的),所以在这个数组的计算结果里就有错误值了。选中公式中的“B2:B7*C2:C7”按F9可以查看B2:B7*C2:C7的运算结果:

Excel函数学习之无所不能的SUMPRODUCT!

可以看到第一个(单价*数量)运算结果就是错误值。接下来SUMPRODUCT对包含了错误值的数据进行求和,结果肯定就是错误了。

5、SUMPRODUCT用乘号(*)的要点

以上内容所要表达的意思有两点:

第一,使用逗号和使用*号有时候结果相同,但是意义完全不一样,希望大家可以理解。

第二,SUMPRODUCT函数使用乘号(*)必须要注意两点:第一,不能存在无法计算的内容,如文字;第二,如果是两组或多组数组相乘的话,数据区域大小一致。用逗号则只需要保证数据区域大小一致即可。

6、其实乘号还带来了更大优势

我们把SUMPRODUCT函数逗号、乘号前后的数据用A、B来代替,表达为SUMPRODUCT(A,B)和SUMPRODUCT(A*B)。当为逗号时,A、B必须同时都是数值或者数组,不能一个是数值,一个是数组;当为乘号时,A、B可以同时都是数值或者数组,也可以一个是数值一个是数组。

也就是:

逗号,

乘号*

SUMPRODUCT(C1,B1)

SUMPRODUCT(C1*B1)

SUMPRODUCT(C1:C9,D1:D9)

SUMPRODUCT(C1:C9*D1:D9)

SUMPRODUCT(C1:D9,F1:G9)

SUMPRODUCT(C1:D9*F1:G9)

SUMPRODUCT(C1:C9,D1)

×

SUMPRODUCT(C1:C9*D1)

SUMPRODUCT(C1:D9,F1)

×

SUMPRODUCT(C1:D9*F1)

因此,用乘号扩大了SUMPRODUCT函数的应用。你即将在下面看到的都是SUMPRODUCT函数用乘号的应用。

7、看得懂这些SUMPRODUCT公式吗?

如果明白了上面的内容,说明对于这个函数的基本用法是没问题了。可是很可能很多SUMPRODUCT公式你还是看不懂,比如这种:

Excel函数学习之无所不能的SUMPRODUCT!

这实际上是一个条件计数的问题,再看这个:

Excel函数学习之无所不能的SUMPRODUCT!

这是一个多条件求和的问题,还有这个:

Excel函数学习之无所不能的SUMPRODUCT!

在这个表里,几乎各种统计问题都可以用SUMPRODUCT函数去解决,不再一一举例。仅仅是上面列出的三个公式,都能看明白的朋友可能就不多了。

为什么已经理解了SUMPRODUCT函数的用法,却还看不懂这些公式呢,更别说自己去用公式来解决问题了。原因就是你还不了解这两个知识点:逻辑值和数组。

8、了解一点逻辑值和数组

先来说说逻辑值,逻辑值只有两个,就是TRUE和FALSE。当我们在公式里进行某种比较或者判断的时候,就会产生逻辑值,以SUMPRODUCT((I2:I22="张三")*1) 为例,其中的(I2:I22="张三")就是一个判断。通常情况下,我们是以单元格去做判断,I2 ="张三"的意思就是判断I2单元格内容是否为"张三",如果是则得到TRUE,反之得到FALSE。当我们使用了一个区域去判断的时候,就会得到一组数据,这其实就是一个数组了。例如I2:I22="张三"就会得到一组逻辑值,可以用F9来看看计算结果:

Excel函数学习之无所不能的SUMPRODUCT!

如果有兴趣的话可以逐一对比销售员和结果值,会发现张三对应的都是TRUE。

因为逻辑值无法直接进行求和,必须转换为数字才行,转换的方法就是对逻辑值进行加减乘除之一的任何运算即可。在这个公式中,*1就是这个作用,可以看看效果:

Excel函数学习之无所不能的SUMPRODUCT!

通过*1运算之后,TRUE都变成了1,而FALSE都变成了0。不要问我为什么,Excel就是这么规定的,逻辑值与数字的对应关系就是这个。

好了,我们首先得到一组逻辑值,然后通过*1变成一组数字,再进行求和,就达到了按条件计数的目的。你现在是否已经理解了=SUMPRODUCT((I2:I22="张三")*1)这个公式呢?

现在我们了解到逻辑值,也明白了数组运算的第一个原则:当一组数与一个数进行计算时,是这组数中的每个数据分别与这一个数进行计算。刚才的公式中就是这样计算的。

9、不论SUMPRODUCT公式多复杂,全看懂!

咱们再来看看第二个公式:=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22)

不要看这个公式长,利用我们刚才学到的知识来破解它的话其实很简单。公式还是只有一个参数,只不过这个参数是由四个数组构成的,其中的三组都是逻辑值,分别是(MONTH(F2:F22)=3)、(H2:H22="二分店")和(G2:G22="衬衫")。这三组逻辑值完成三个判断,分别对应了三个条件:月份=3、店面=二分店和名称=衬衫。具体内容如图所示:

Excel函数学习之无所不能的SUMPRODUCT!

看起来密密麻麻的,但是经过了乘法运算以后,就变成了一堆1和0,结果是这样的:

Excel函数学习之无所不能的SUMPRODUCT!

乘积结果只有两个1,其实就对应了三月份二分店衬衫这两条数据。此时的公式变成了一组逻辑值(已经是0和1了)与一组数据(数量)相乘,再由函数完成求和。

在这个例子中,需要注意数组计算的第二个原则:当多个(含两个)数组计算时(本例是四个数组),数组中对应位置的数据进行计算,要求数组中包含的数据个数相同。

到现在我们已经了解到数组计算的两个原则,现举个简单的例子来说明:

Excel函数学习之无所不能的SUMPRODUCT!

一个数组(A1:A9)与一个数据(B1)相乘时,是这组数分别与这个数据相乘;

Excel函数学习之无所不能的SUMPRODUCT!

两个数组(A1:A9和B1:B9)相乘时,是第一组数与第二组数对应位置的数据相乘。

现在回头来看这个公式=SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22),应该没那么头疼了吧。

其实数组的计算原则还有更加复杂的情况,有兴趣的朋友可以看看相关的资料,这里了解简单原理即可。再来看看第三个公式:=SUMPRODUCT((H2:H22=H2)*(G2:G22={"毛衣","衬衫"})*L2:L22)。这个公式看上去和前面两个有点区别,(G2:G22={"毛衣","衬衫"})这部分用到了常量数组,其实这个公式本来应该是这样写的:=SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="衬衫")*L2:L22)

Excel函数学习之无所不能的SUMPRODUCT!

这个公式用到了两个SUMPRODUCT函数,第一个是计算总店毛衣的销售额,第二个是计算总店衬衫的销售额,分别看应该每个都能理解。两个SUMPRODUCT函数除了"毛衣"和"衬衫"这里不同,其他的完全一样,遇到这种情况,就可以用常量数组把两个内容放到一起,使公式变得简洁。

通过今天的讲解,我们读懂了常见的SUMPRODUCT公式,解决了SUMPRODUCT函数运用乘号的困惑,掌握了SUMPRODUCT函数更多的应用。同时,我们也了解到逻辑值和数组的一些基础知识——这对我们理解其他的复杂公式有帮助。

返回顶部
顶部