计数与求和于一身的SUMPRODUCT函数(一)
发布时间:2023-10-07 18:48:50
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)
示例2
下图是一个评分表,有5个评价指标,每个指标的权重是不同的,现在要计算每个人的评分,而这些评分就是每个指标分数与指标权重相乘相加的合计数,也就是数学中的,那么,计算公式如下。
=SUMPRODUCT($B$2:$F$2,B5:F5)
SUMPRODUCT可以替代COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函数,其原理就是使用条件表达式构建只有数字0和1组成的数组,然后将这个数组中的所有数字1和0相加,就是满足条件的单元格个数;将这些只有数字0和1的数组与实际求和区域的每个单元格数据相乘相加,就得到满足条件的合计数。
但是,条件表达式的结果是两个逻辑值TRUE和FALSE,而SUMPRODUCT会把这两个逻辑值都当做0处理,因此,需要将条件表达式乘以数字1,或者除以数字1,或者输入两个负号,以使其转换为数字1和0。
猜你喜欢
- 对于电脑系统来说,一般都有非常多的重要文件,而microsoft visual就是其中之一,许多小伙伴在不知情的情况下不小心删除了,这种情况
- 我们在word 2013文档中插入艺术字后,功能区中会出现“绘图工具-格式”选项卡,在该选项卡下可以对艺术字进行编辑操作,word 2013
- 在word中,设置表格线条的方法:1、选中表格;2、单击页面布局----页面边框按钮;3、弹出边框和底纹对话框,选择边框选项卡,在样式处选择
- d盘一般是我们大量存储文件的分区,用的时间久久会想清理或者格式化再重新使用。最近有用户遇到win10电脑无法格式化D盘的问题,提示被占用的情
- 如何将Word文档上的文字快速转换成PDF文件,从网上下载出来的Word文档是扫描版的,领导要求将Word扫描图片转换成PDF格式,用最新的
- 在阅读Word文档时,如果文件较长又需要对照浏览的话,一直上下翻非常不方便,今天给大家介绍Word文档分屏阅读的方法。word分屏显示两个文
- 很多时候我们写word文档进度缓慢,都是因为前期偷懒没写大纲,没有认真思索,导致后面总是一边想一边写,一边写一边改,苦不堪言。写议论文要先列
- 不同版本的word,查看的方法可能不太一样,以下为word2010版本的查看方法:打开word2010,点击”文件“,选择”最近所用文件“;
- Excel中的换行功能具体该如何操作才能达到换行的效果呢?下面是小编带来的关于excel表格内进行换行的方法,希望阅读过后对你有所启发!ex
- 在编辑文档时经常都是白纸黑字,这样显得有点枯燥。比如我们写信时能加上花边就好看多了,给word文档加上花边之后可以使文档变得美观,更能吸引读
- (这是系列文章中的第一篇。第二篇文章,如何使用Excel数据表创建蒙特卡洛模型和预测。)当我为雇主创建模型和预测时,我知道我的结果将是错误的
- 在我们的Excel的实际操作过程中,有时需要找到整列中的最后一个有数据的单元格,怎么实现呢?下面小编就为大家介绍excel如何获取某列中的最
- Word是我们平常很常用的办公软件,我想,你一定经历过这样的事情:1、快下班了,电脑突然死机,今天做的Word文档忘记保存了。2、上学上课的
- 在Word2007文档中,通过启用打印XML标记功能,可以在打印XML文档时打印XML标记。在Word2007文档中,通过启用打印XML标记
- iOS 16 带来锁定屏幕迄今最大更新,以及新的共享、通信和多种智能功能,将会共同改变用户的 iPhone 体验方式。并提供能够与家庭成员无
- 点左上角的按钮,在出来的菜单里有个选项。也可以点旧菜单,工具》选项。
- Win10桌面怎么显示我的电脑?现在很多用户发现系统在重新安装Win10系统之后,桌面缺少了我的电脑(此电脑)图标,为此有用户想要在Win1
- 有朋友要求Excel工作表的A1单元格和B1单元格中有两个数字,这两个数字有一部分相同,现在要找出其中相同的数字并写入单元格C1,找出A1中
- 用户在使用Word 2013打开Word 2010创建的.docx文档时会显示“兼容模式”,说明当前打开的Word 2010文档有可能无法支
- 对于Word的操作命令,用户不可能全部记住。Word提供了一个内置宏“ListCommands”,使用该宏可以在一个新文档中列出Word的命