当前位置:首页 > 办公 > Excel教程 > 正文内容

SUMPRODUCT函数详解(2)

酷网3周前 (10-08)Excel教程28

工作表有一类最基本的函数是基于某条件统计或汇总结果,例如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。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39541.html

分享给朋友:

相关文章

数据排名只会rank函数就out了,这三组Excel函数公式更高效

数据排名只会rank函数就out了,这三组Excel函数公式更高效

有人的地方就有江湖,有数据的地方就有排名。相信大家对Excel数据排名不会陌生,里面使用最多的一个排名函数,它就是Rank函数。   因为场景的不同,数据排名也有不同的操作,Rank函数已经不能解决我们的操作。...

怎么在 Microsoft Excel 中进行回归分析

怎么在 Microsoft Excel 中进行回归分析

回归分析在对大量数据进行分析和开展预测与估计时非常实用。如要在 Microsoft Excel 表格中进行回归分析,请按以下说明操作。 步骤 方法 1确定您的 Excel 程序支持回归分析 当您的 Excel 版本显示...

使用Excel如何快速拆分字符串

使用Excel如何快速拆分字符串

  我们在使用Excel表格处理个人信息数据的时候,可能会遇到类似于名字加电话,也就是汉子加数字的组合,为了更好的归类数据同时也方便查看,我们通常会将这串组合数据拆分开,那么如何操作才...

Excel中RANK函数怎么做成绩排名

Excel中RANK函数怎么做成绩排名

当你用excel时需要根据成绩或者业绩进行排序的时候,你是否还在一个一个的比照呢,那样就太没效率了,接下来我教大家用rank函数来完成吧,具体怎么做下面小编来教你吧。 使用excel中的rank函数进行排序的步骤: 打开...

7 个表格函数技巧,让你少做 80% 重复工作

7 个表格函数技巧,让你少做 80% 重复工作

日常办公时,我们经常会用表格处理数据。虽然能掌握基础的操作,但是涉及「函数」时,很多人就开始头疼了。 毕竟在实现精通 Office 的路上,函数一直都是绕不过的一道坎。 今天,WPS君就给大家整理了「常用的 7 个函数」...

都进来看看!这招以一敌五的Excel绝技,连透视表都甘拜下风!

都进来看看!这招以一敌五的Excel绝技,连透视表都甘拜下风!

在武侠小说里,经常会有一种情况——带着主角光环的人,总是凭借“一招鲜,吃遍天”的“伟大漏洞”,处处逢凶化吉。都说“职场如战场”,来到了职场,每一位EXCELER,面对来自四面八方的工作压力,也算是“处处逢凶”了。那么能不...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。