史上最全SUMPRODUCT函数应用教程

跟李锐学Excel, 高效工作,快乐生活。

史上最全

SUMPRODUCT函数

应用教程及案例解析

SUMPRODUCT函数是一个使用频率很高的数学函数,凡工作中涉及到条件计数或条件求和的问题,都可以用SUMPRODUCT函数来解决。

为了让大家认识SUMPRODUCT函数那些不为人知的强大功能,本文贴合办公实际场景,整理了多种SUMPRODUCT函数的应用方法,除了原理和基础性讲解外,还提供了使用场景介绍,帮助读者加深理解,便于在自己的实际工作中直接借鉴和使用。

由于正文字数限制,本教程给出Excel案例和公式解法,对公式的原理解析和详细说明请点击本文底部的“阅读原文”获取。

适用对象:本文面向的读者包括所有需要用到查找引用数据的用户,无论是初入职场的应届毕生生,还是在职场拼杀多年的白领精英,都将从本文找到值得学习的内容。

软件版本:本文的写作环境是Window10家庭版操作系统上的简体中文版Excel 2013。本文绝大多数内容也适用于Excel的早期版本(2010、2007和2003),或者英文版和繁体中文版,所以读者大不必因自用版本不同而过多担心。

本文学习要点(强烈推荐收藏本教程

1、SUMPRODUCT函数语法解析及基础用法

2、SUMPRODUCT函数单条件计数

3、SUMPRODUCT函数多条件计数

4、SUMPRODUCT函数多条件求和统计

5、SUMPRODUCT函数多条件统计示例

6、SUMPRODUCT函数模糊条件求和

7、SUMPRODUCT函数跨列条件求和

8、SUMPRODUCT函数实现多权重综合评价

9、SUMPRODUCT函数二维区域条件求和

01 SUMPRODUCT函数语法解析及基础用法 

SUMPRODUCT函数是Excel中的数学函数,用于在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

其基本语法为:

SUMPRODUCT(array1,[array2], [array3], ...)

SUMPRODUCT 函数语法具有下列参数:

Array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。

Array2, array3,...:可选。 2 到 255 个数组参数,其相应元素需要进行相乘并求和。

备注

数组参数必须具有相同的维数。 否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。

函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

为了大家更好的理解,下面结合一个实际案例来介绍。

上表中左侧是数据源区域,包含员工的姓名、基础津贴和工种强度系数。

需要统计的是所有员工的应发津贴之和。

应发津贴=基础津贴*工种强度系数

这个问题用SUMPRODUCT函数处理,会变得非常简单。

这里给出两种方法。

方法1:

=SUMPRODUCT(B2:B12,C2:C12)

方法2:

=SUMPRODUCT(B2:B12*C2:C12)

两种方法的区别在于,SUMPRODUCT函数的两个参数之间的连接符号不同,方法1用逗号,连接,方法2用乘号*连接。

这个案例的数据源中全部是数值,所以两种方法返回的结果一致。

如果当数据源中包含文本数据时,使用方法1依然可以返回正确结果,但使用方法2会导致文本和数值相乘,返回错误值#VALUE!

这只是SUMPRODUCT函数的基础用法,下面咱们再多来看几个案例,介绍这个函数丰富的应用方法。

02 SUMPRODUCT函数单条件计数 

SUMPRODUCT函数处理条件计数问题也是顺手拈来。

表格左侧是数据源区域,要在右侧的黄色单元格,用公式统计女生数量。

D2单元格输入以下公式:

=SUMPRODUCT(N(B2:B12="女"))

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

03 SUMPRODUCT函数多条件计数 

SUMPRODUCT函数不单能搞定单条件计数统计,多条件计数也没问题。

表格中左侧是数据源区域,右侧黄色单元格输入公式。

要统计高于80分的女生人数,E2单元格输入以下公式:

=SUMPRODUCT((B2:B12="女")*(C2:C12>80))

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

04 SUMPRODUCT函数多条件求和统计 

SUMPRODUCT函数不但能搞定条件计数功能,还可以处理条件求和统计。

今天咱们就来结合一个实际案例,介绍SUMPRODUCT函数多条件求和的用法。

表格中左侧是数据源区域,右侧黄色单元格输入公式。

要统计高于80分的女生总分,E2单元格输入以下公式:

=SUMPRODUCT((B2:B12="女")*(C2:C12>80)*C2:C12)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

05 SUMPRODUCT函数多条件统计示例

为了大家更好地理解SUMPRODUCT函数多条件统计的用法,咱们再来看一个案例。


表格左侧是员工业绩表,右侧的黄色区域需要输入公式,统计3月份指定员工的业绩之和。

G3单元格输入以下公式,并向下填充

=SUMPRODUCT((MONTH($A$2:$A$14)=3)*($B$2:$B$14=F3),$C$2:$C$14)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

06 SUMPRODUCT函数模糊条件求和

SUMPRODUCT函数不但能够搞定精确条件查询,模糊条件求和也不在话下。

由于SUMPRODUCT函数不支持通配符*和?的使用,所以遇到模糊条件求和时,需要配合其他函数嵌套完成。

下面,咱们就来介绍一下SUMPRODUCT函数模糊条件求和的用法。

表格左侧是数据源区域,我们要统计的是销售部门女性员工的奖金之和。

这里的销售部门就是一个模糊条件,包括销售1部、销售2部……等。

在F2单元格输入以下公式:

=SUMPRODUCT(ISNUMBER(FIND("销售",C2:C12))*(B2:B12="女"),D2:D12)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

07 SUMPRODUCT函数跨列条件求和

今天来结合一个实际工作中经常会遇到的问题,介绍SUMPRODUCT函数跨列条件求和的方法。


数据源中包含每个分公司各个季度的计划数据和实际数据,要在黄色区域分别对计划、实际数据进行汇总。

看了这么多干货,记得收藏哦~

在J3单元格输入以下公式,再填充至K6单元格区域。

=SUMPRODUCT(($B$2:$I$2=J$2)*$B3:$I3)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

08 SUMPRODUCT函数实现多权重综合评价

工作中遇到的KPI绩效多权重计算问题,也可以通过SUMPRODUCT函数解决。

这个表格中展示了参与KPI考核的四项占比,以及每个员工这四项分别的得分。

需要根据每个考核项各自占比不同,计算员工的KPI综合得分。

黄色区域输入公式,进行计算。

先给出公式,再解析原理。

在F3单元格输入以下公式,向下填充。

=SUMPRODUCT(B$2:E$2,B3:E3)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

09 SUMPRODUCT函数二维区域条件求和

工作中经常遇到二维区域的条件求和,比如这种:

表格左侧是数据源,右侧黄色区域要输入公式,根据二维区域条件求和。

在F2单元格输入以下公式,填充至F2:K4单元格区域

=SUMPRODUCT(($A$2:$A$18=F$1)*($B$2:$B$18=$E2),$C$2:$C$18)

(更详细的公式原理解析和说明请点击本文底部的“阅读原文”获取)

【跟李锐学Excel】推荐阅读

(点击蓝字可直接跳转)

Excel教程2016合集(文尾有彩蛋)

李 锐

微软全球最有价值专家MVP

新浪微博Excel垂直领域第一签约自媒体

百度名家,百度阅读认证作者

每日分享职场办公技巧教程

高效工作,快乐生活!

微博 @Excel_函数与公式

微信公众号(ExcelLiRui)

(0)

相关推荐

  • sumproduct函数的使用方法

    sumproduct函数的语法格式:=SUMPRODUCT(array1,array2,array3, ...)Array为数组.注意:Array即数组个数不超过255个 1.基础用法 在E2中输入= ...

  • 史上最全MATCH函数应用教程

    跟李锐学Excel, 高效工作,快乐生活. 史上最全 MATCH函数 应用教程及案例解析 MATCH函数是Excel中广泛应用的查找引用函数,除自身具有返回查找数据的相对位置的功能外,MATCH函数还 ...

  • 史上最全LOOKUP函数应用教程

    跟李锐学Excel, 高效工作,快乐生活. 史上最全 LOOKUP函数 应用教程及案例解析 LOOKUP函数是Excel中威力十分强大的查找引用函数,前面教程中介绍的VLOOKUP函数的功能就已经很给 ...

  • 史上最全FREQUENCY函数应用教程

    跟李锐学Excel, 高效工作,快乐生活. 史上最全 FREQUENCY函数 应用教程及案例解析 FREQUENCY函数是一个很强大的频率统计函数,凡工作中涉及到条件分段统计.数据分布统计和数值数据重 ...

  • 史上最全DATEDIF函数应用教程

    跟李锐学Excel, 高效工作,快乐生活. 史上最全 DATEDIF函数 应用教程及案例解析 工作中经常会遇到涉及日期计算的问题,比如计算两个日期之间的天数.月数.年数. 处理这类问题要使用到的一个高 ...

  • 史上最全COUNTIF函数应用教程

    每天跟李锐学习职场办公必备干货!高效工作,快乐生活. 在职场办公中,经常需要对数据进行条件计数统计,COUNTIF函数是工作中使用频率超高的条件计数统计函数之一,本文完整详尽的介绍了COUNTIF函数 ...

  • 史上最全SUMIF函数应用教程

    在职场办公中,经常需要对数据进行条件求和汇总,SUMIF函数是工作中使用频率超高的条件求和函数之一. 本文完整详尽的介绍了SUMIF函数的技术特点和应用方法,除了原理和基础性讲解外,还提供了大量贴近工 ...

  • 史上最全VLOOKUP函数应用教程(文末有彩蛋)

    每天跟李锐学习职场办公必备干货!高效工作,快乐生活. 职场一族在日常工作中经常需要对数据进行查询调用,VLOOKUP函数是工作中使用频率超高的查询函数之一,可谓Excel函数中的大众情人. 本文完整详 ...

  • 【初学者福音】史上最全IF函数应用教程

    每天跟李锐学习职场办公必备干货!高效工作,快乐生活. IF函数是Excel中最常用的函数之一,凡工作中涉及到条件逻辑判断.多层级条件嵌套判断的问题,都可以用IF函数来解决.而且IF函数与很多函数结合使 ...

  • 【初学者福音】史上最全SUM函数应用教程

    每天跟李锐学习职场办公必备干货!高效工作,快乐生活. 大家都知道,SUM求和是职场办公必备技能,但90%的人止步于SUM函数最基础的求和用法,而在明明能借助这个强大函数来提高效率时,选择了粗笨的手动汇 ...