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

计数与求和于一身的SUMPRODUCT函数(一)

酷网3个月前 (10-23)Excel教程38

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。

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

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

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

分享给朋友:

相关文章

Windows API查看器(适用于Excel)

Windows API查看器(适用于Excel)

Windows API Viewer是一款独立的、功能强大的工具,适用于MS Excel,创建可以插入到代码模块中的Windows API代码。 它为x86和x64平台提供了API列表,可以轻松地在各种解决方案中使用。该...

文本函数(参考)

要获取有关某个函数的详细信息,请在第一列中单击其名称。 注意: 版本标记指示引入函数的Excel版本。这些函数在更早的版本中不可用。例如,版本标记2013表示此函数在Excel2013和所有更高版本中可用。 函数...

OFFSET函数也可解决排列组合问题

OFFSET函数也可解决排列组合问题

昨天的排列组合问题,今天换成OFFSET函数来解决。 相同的样表: 需要把“级别“与“等级“的所有组合列出来,有三个级别、四个等级,所以组合的可能性有3*4=12种。 分步实现: 第一步:级别重复四次 公式: =OFF...

excel图表制作:制作漂亮的用户窗体按钮

excel图表制作:制作漂亮的用户窗体按钮

在很多场合,我们都能看到这样的效果,当鼠标移动到某个元素上面时,该元素会变成另外一种颜色,达到强调的效果。下面,我们来实现当鼠标移动到用户窗体按钮上时,会使用颜色高亮显示,让用户窗体更生动,如下图1所示。 图1 其实,...

excel如何使用宏?excel表格宏的使用

excel如何使用宏?excel表格宏的使用

      宏类似于计算机程序,但是它是完全运行于Excel之中的,我们可以使用宏来完成枯燥的、频繁的重复性工作。如:电子表格需要进行某种规则或模式的批量重复处理。你可以设置宏,来轻松地完成这些重复性操作,而不...

Vlookup函数最经典的12种用法!太太太太太太太有用了

Vlookup函数最经典的12种用法!太太太太太太太有用了

Vlookup函数的教程网上已非常多了,而贴近工作用的Vlookup函数应用示例却很少。今天给同学们带来一期Vlookup函数示例大全,希望能给大家的工作带来帮助。 一 【例1】查找个人信息 =VLOOKUP($B18,...

发表评论

访客

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