站长家园(原代码之家)(www.adminjie.com)网站源码,微信源码,游戏源码,商业源码分享平台。
当前位置:网站首页 技术文章 网络编程 正文

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

时间:2022-05-24 [网络编程]作者:fabuyuan 浏览:8 次

在之前的文章《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)

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

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

相关学习推荐:excel教程

以上就是Excel函数学习之无所不能的SUMPRODUCT!的详细内容,更多请关注站长家园其它相关文章!

本文标签:  Excel

转载请注明来源:Excel函数学习之无所不能的SUMPRODUCT!

本文永久链接地址:https://www.adminjie.com/post/12732.html

免责声明:
本站所发布的一切资源仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。

附:
二○○二年一月一日《计算机软件保护条例》第十七条规定:为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬!鉴于此,也希望大家按此说明研究软件!

版权声明:
一、本站致力于为软件爱好者提供国内外软件开发技术和软件共享,着力为用户提供优资资源。
二、本站提供的部分源码下载文件为网络共享资源,请于下载后的24小时内删除。如需体验更多乐趣,还请支持正版。
三、我站提供用户下载的所有内容均转自互联网。如有内容侵犯您的版权或其他利益的,若有侵犯你的权益请:提交版权证明文件到邮箱 2225329873#qq.com(#换为@) 站长会进行审查之后,情况属实的会在三个工作日内为您删除。

  • 站长家园(原代码之家)会员升级
  • 最新文章
    • git怎么撤回刚删除的分支

      git怎么撤回刚删除的分支

      git撤回刚删除分支的方法:1、利用“gitremoteprune”命令查看被删除的分支;2、利用“gitreflog”命令找到被删除分支的最后一次提交记...

    • 如何安装并管理多版本node?方法介绍

      如何安装并管理多版本node?方法介绍

      如何安装并管理多版本node?下面本篇文章给大家介绍一下多版本node的安装与切换详细操作,希望对大家有所帮助!安装多版本node的原因:在项目开发过程中,不同...

    • git为什么会产生冲突

      git为什么会产生冲突

      在git中,冲突产生的原因是因为在合并文件时同一个文件的同一个位置都修改了,并且内容不同;也即两个已经提交的分支的相同文件相同位置的不同操作进行了合并,所以产生...

    • git分离头指针是什么

      git分离头指针是什么

      git分离头指针是一种HEAD指针不再指向分支,而是直接指向某个commit的状态;一般情况下HEAD指针指向分支,而分支是指向提交,分离头指针指的是变更没有基...

    • git bash做什么的

      git bash做什么的

      gitbash是用于git版本控制,上传下载项目代码用的;gitbash是Windows下的一个命令行工具,是基于“msysGNU”环境,有git分布式版...

    热门文章