SUMPRODUCT函数详解(2)
发布时间:2022-01-24 13:19:57
工作表有一类最基本的函数是基于某条件统计或汇总结果,例如COUNTIF函数或SUMIF函数,都是相当有用和灵活的函数,但仅局限于单条件。而本文将着重探讨基于多条件获得结果的函数——SUMPRODUCT函数。
SUMPRODUCT函数是Excel中最“多才多艺”的函数之一。其基本功能是,数组中相应的项目相乘并返回结果之和。下面将介绍SUMPRODUCT函数经典的用法。
SUMPRODUCT函数的标准用法
SUMPRODUCT函数的基本用法是:一个数组中的每个值与另一个数组中相应的值相乘,返回乘积的和。
示例1:如果单元格区域A9:A11中分别包含值1、2、3,单元格区域B9:B11中分别包含值10、20、30,那么
=SUMPRODUCT(A9:A11,B9:B11)
返回结果140,或者(1*10)+(2*20)+(3*30)=10+40+90=140。
SUMPRODUCT函数的“进化”用法
在Excel中,有两个非常有用的支持条件统计和求和的函数,分别是COUNTIF函数和SUMIF函数,但仅支持单个条件测试。
举个简单的双条件测试例子。例如,公式=COUNTIF(A1:A10,>=10)-COUNTIF(A1:A10,>20)将得到在单元格区域A1:A10中介于10和20之间有多少项。然而,这种摸拟双条件测试的方法是有局限的,它不能处理不同的单元格区域或者更多的条件。
为了理解SUMPRODUCT函数的使用,先举个例子。示例数据如下:
下面的公式很容易得出“Ford”的数量:
=COUNTIF(A1:A10,”Ford”)
返回的结果为4。
同样,下面的公式直接获得“Ford”销售价格之和:
=SUMIF(A1:A10,”Ford”,C1:C10)
返回的结果为33873。
然而,假设想要统计6月份“Ford”的销售数量,使用下面的数组公式:
=SUM(IF(A1:A10=”Ford”,IF(B1:B10=”June”,1,0),0))
输入后同时按下Ctrl+Shift+Enter键,返回结果3。
同样,要统计6月份“Ford”销售价格之和,使用下面的数组公式:
=SUM(IF(A1:A10=”Ford”,IF(B1:B10=”June”,C1:C10,0),0))
返回结果27000。
下面我们看看使用SUMPRODUCT函数来获取结果的情形。下面的公式:
=SUMPRODUCT((A1:A10=”Ford”)*(B1:B10=”June”))
得到6月份“Ford”的销售数量,结果为3。
下面的公式:
=SUMPRODUCT((A2:A10=”Ford”)*(B2:B10=”June”)*(C2:C10))
得到6月份“Ford”销售价格之和。
“*”号用作AND(与)运算符,公式的其含义是单元格区域A2:A10中的值为“Ford”并且B2:B10中的值为“June”所对应的C2:C10中的值相加。
然而,如果我们想在公式中使用OR(或)运算符呢?使用“+”号来代表OR运算符。例如,下面的公式:
=SUMPRODUCT((A1:A10=”Ford”)+(A1:A10=”Renault”))
计算单元格区域A1:A10中为“Ford”或“Renault”的数量,结果为6。
SUMPRODUCT函数的优点
正如上文所描述的,多条件测试是SUMPRODUCT函数的主要优点。然而,SUMPRODUCT函数还有其它两个优点:一个是它能够处理关闭的工作簿中的数据,不受源工作簿是否关闭的影响;另一个是能够处理文本值以满足需求。
例如,如果想在另一个工作簿中计算“Sumproduct函数.xlsm”工作簿中的数据,那么使用下面的公式计算该工作簿中大于15000的值的和。
=SUMIF([Sumproduct函数.xlsm]Sheet1!$C$2:$C$10,”>15000″)
此时,如果“Sumproduct函数.xlsm”工作簿关闭,再重新计算工作表是时该公式会返回#VALUE。同样,如果“Sumproduct函数.xlsm”工作簿已经关闭后,再在其它工作簿中使用上面的公式,会返回#VALUE。
然而,使用SUMPRODUCT函数就不会存在上面的问题。无论“Sumproduct函数.xlsm”工作簿是否关闭,将下面的公式输入到其它工作簿中,都会得到正确的结果。
=SUMPRODUCT(–(‘[Sumproduct函数.xlsm]Sheet1’!$C$2:$C$10>15000),–(‘[Sumproduct函数.xlsm]Sheet1’!$C$2:$C$10))
第二个主要的优点是SUMPRODUCT函数能够处理具有不同数据类型文本的列。例子数据如下图所示,在B列的第一行中是文字文本值作为标题,其余行为数值。
如果使用下面的公式:
=SUMPRODUCT((A1:A4=”x”)*(B1:B4))
则会返回错误#VALUE。
要忽略错误,则要使用双一元操作符(两个减号):
=SUMPRODUCT(–(A1:A4=”x”),(B1:B4))
返回正确的结果,本例中是4。
猜你喜欢
- 面积图可用于表现数据整体变化的幅度大小,在制作面积图时,我们可能常常会遇到不同数据系列发生重叠或交叉的问题,导致图表展示效果不太理想,这篇文
- excel中的表格怎样转换成WORD表格excel中的表格怎样转换成WORD表格 首先,用Excel编辑
- 迅捷pdf编辑器怎么给PDF文字添加高亮?迅捷pdf编辑器中导入的pdf文件,想要给文件中的部分文字添加高亮,该怎么添加呢?下面我们就来看看
- Win10专业版怎么快速退出工作组?许多用户在使用电脑的时候经常会出现一些电脑问题,而最近许多小伙伴问我,自己的电脑怎么都退出不了工作组,下
- 今天主要是和大家分享关于office工具使用的一个小技巧,在平时工作中,或许我们会遇到很多不能解决的问题,有时不知如何百度,这时我们可以利用
- Win10系统电脑在使用久了以后经常就会遇到各种各样的问题,例如小编这次就遇到了开机的之后显示被拒绝访问的情况,如果遇到这种情况应该怎么办呢
- 首先,在开始--所有程序--附件里打开记事本, 然后复制下面给出对应程序的修复代码,粘贴到记事本里,然后任意文件名保存。把保存的文件后缀改为
- 在Excel中不仅可以为单元格或单元格区域设置背景,还可以为整个excel2007工作表设置图片背景,这节课excel教程网的季老师就为大家
- 如何在Word 2016中删除样式格式?如果您不会像重新应用其他样式一样从Word 2016中的文本中删除样式格式。删除样式的唯一官方方法是
- 在Word文档中,我们经常会插入一下表格,以进行数据的处理。大家都知道,在Word文档中插入的表格,同样可以进行各种专业化的数据处理和格式调
- 最近有win10用户跟小编反映电脑每次开机之后,键盘上的数字小键盘都是默认关闭的,每次都需要按下NumLock键里开启小键盘才行,但是每次都
- word 07与03版本有很大的不同,需要一些时间去熟悉。具体该怎么去做的呢?今天,小编就教大家在Word中设置密码的操作方法。Word中设
- Win10系统如何禁止软件联网?在windows10系统中玩游戏时,发现点击反应都慢半拍,检查了一下,原来是系统中某个软件在后台运行导致的,
- 在wps文字中添加和删除页眉横线的方法:点击WPS页眉左上角“插入”→“页眉页脚” 菜单栏最右边出现“页眉页脚”快
- 我们将手机连接电脑后,可以对手机上的信息进行备份,防止信息丢失,还可以直接安装软件到手机里,但是很多用户升级Win10系统后,就不知道怎么连
- 电脑的CPU使用率高要怎么办?许多用户在使用电脑的时候经常发现电脑卡顿,这个时候有部分小伙伴就会去查看电脑的CPU使用率,发现CPU使用率非
- 安装office2010以后,打开word或excel,默认输入法往往是“微软拼音”或其他输入法,反正不是自己想用的输入法,好多朋友用着不习
- 大家知道,Excel中没有分栏功能。当Excel表格很长很长时,可以复制到Word中进行分栏。如图:Excel长表格跨了三页 图1
- 现如今,网络资源越来越丰富,我们可以利用的也越来越多,但有些网页上的表格并没有提供下载功能,是不是就不能用了呢?当然不,Excel2013可
- excel中一个单元格的数据怎么分列成多个数据?Excel是一款功能非常强大的办公软件,工作中我们也不可避免的会用到excel的各种功能,下