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

wufei123 2024-05-24 阅读:41 评论:0

在之前的文章《excel函数学习之神奇的aggregate，竟可一个抵19个！》中，我们了解了一种强大的统计函数。而今天我们来聊聊sumproduct函数，sumproduct函数可以得到两列数据的乘积之和，但是在运用函数的过程中有非常多需...

在之前的文章《excel函数学习之神奇的aggregate，竟可一个抵19个！》中，我们了解了一种强大的统计函数。而今天我们来聊聊sumproduct函数，sumproduct函数可以得到两列数据的乘积之和，但是在运用函数的过程中有非常多需要注意的地方，特别是运用上逻辑值和数组之后，下面我们一起来看看讲解！

1、SUMPRODUCT的实质

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

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

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

2、大多数错误的原因

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

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

3、另一种常见写法，逗号变乘号（*）

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

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

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

4、逗号和乘号（*）的差别

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

意义的变化有何影响呢？

我们还是通过例子来看：

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

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

可以看到第一个（单价*数量）运算结果就是错误值。接下来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公式你还是看不懂，比如这种：

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

这是一个多条件求和的问题，还有这个：

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

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

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

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

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

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

通过*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、店面=二分店和名称=衬衫。具体内容如图所示：

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

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

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

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

一个数组（A1:A9）与一个数据（B1）相乘时，是这组数分别与这个数据相乘；

两个数组（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)。