史上最全的Excel条件和函数SUMIF经典教程
在职场办公中,往往需要有条件地对数据进行汇总。SUMIF函数是工作中经常使用的条件和函数之一。
本文介绍了SUMIF函数丰富的用法,方便您在自己的实际工作中直接学习和使用。
基本函数语法分析
SUMIF函数可以对满足指定条件的范围内的值求和。该函数具有非常强大的条件求和功能,在工作中应用广泛。它的基本语法是:
SUMIF(范围,标准,
=SUMIF(B2:B9,& # 8221;女& # 8221;、C2:C3)
=SUMIF(B2:B9,& # 8221;女& # 8221;,C2:C99)
=SUMIF(B2:B9,& # 8221;女& # 8221;,C2)
三个公式返回的结果是一致的。SUMIF函数的sum_range参数中的单元格数目与range中的单元格数目不同,但sum_range的范围是根据C2:C9计算的,即C2是起始单元格,并扩展到与B2:B9大小和形状相同的单元格。等效于以下公式:
=SUMIF(B2:B9,& # 8221;女& # 8221;,C2:C9)
波动性将导致工作表的重新计算,这将花费比预期更长的时间。这种情况在工作中要尽量避免。
(5)5)SUMIF函数中criteria参数的格式会限制其选择条件之和的范围。也就是说,如果第二个参数是数值,SUMIF函数只计算第一个参数是数值格式的单元格对应的求和面积,而忽略文本、逻辑值、错误值等其他格式。利用SUMIF函数的这个特性,我们可以剔除错误的值,并对它们求和。
单字段单条件求和
当然,工作中最常见的要求是单个条件的总和。SUMIF函数在这方面是得心应手的!
先说SUMIF函数统计单个字段和单个条件的和,也说明一下SUMIF第三个参数默认时的操作方式和原理。
SUMIF函数的单条件求和应用广泛。很多情况下,当条件区域和求和区域重合时,公式书写可以简化。下面用一个案例来介绍具体的方法。
要求从数据源中统计出90分以上分数的总和,先给出公式。
=SUMIF(B2:B12,& # 8221;& gt90″)
单字段多条件求和
在上一个教程中,我们学习了SUMIF函数的单条件求和。那么,当工作中出现对某个领域的并行多条件求和的需求时,该如何处理呢?
下面结合下面的案例详细介绍一下。
表格的左侧是数据源区域。要求统计北京分公司、上海分公司、广州分公司的总销售额。如果你只想要一个分公司(比如北京)的销量,那就很简单了。公式是
=SUMIF(A2:A12,& # 8221;北京& # 8221;,B2:B12)
多户呢?当然,最直接的方法是这样的:
=SUMIF(A2:A12,& # 8221;北京& # 8221;,B2:B12)+ SUMIF(A2:A12,& # 8221;上海& # 8221;,B2:B12)+ SUMIF(A2:A12,& # 8221;广州& # 8221;,B2:B12)
这是唯一的方法吗?如果要数的分支数增加,公式不是越来越长吗?
当然会有更好的办法!
给出这里使用的公式:
=SUM(SUMIF(A2:A12,{ & # 8220;北京& # 8221;,”上海& # 8221;,”广州& # 8221;},B2:B12))
前三个结果的统计和
在之前的课程中,我们学习了SUMIF函数的单条件和多条件求和。那么当我们在工作中遇到涉及数值的问题时,应该用什么思路去解决呢?
在下面的案例中,我们将使用SUMIF函数结合LARGE函数来解决一个极值统计问题。
表格左侧是数据源区域,要求统计前三个结果的总和。
我们可以分两步来思考这个问题。第一步,用公式从数据中提取前三个等级,第二步,求和。这样数据源发生变化,前三个结果会随着公式结果动态更新,从而始终保证结果正确。
这是公式。
=SUMIF(B2:B12,& # 8221;& gt”& amp大(B2:b 12.4)
模糊条件求和
有时候要根据模糊条件求和,SUMIF函数支持使用通配符。这里用一个案例来介绍模糊条件求和的方法。
表格的左侧是数据源区域。要求统计姓“张”的员工,即姓名以“张”开头的员工的业绩总和,并给出公式。
=SUMIF(A2:A12,& # 8221;张* & # 8221;,B2:B12)
显示日期间隔统计
在我们的工作中,经常会遇到按日期区间计数的需求,比如从月初到当前日期的销售额,或者周年的销售额(比如持续5天)...如何达到这样的条件之和?
今天我们结合一个简单的案例来介绍一下基于日期区间的求和法。
公式如下所示。
=SUM(SUMIF(A2:A12,{ & # 8220;& gt=2016/4/1″,”& gt2016/4/5″},B2:B12)*{1,-1})
计算登记者的非空收据号。
作品中的数据来源可能来自各种渠道,有的是系统导出的,有的是手工填写收集的,有的是第三方机构提供的。数据源中的某些字段不可避免地会有空值或无效值。这时候往往需要排除这些无效记录进行统计。
今天我们结合一个简单的案例来说明如何使用SUMIF函数计算涉及非空值的求和条件。
登记人为空的记录都是无效记录,在统计入库数量时不会考虑入库数量。将只计算登记者的非空接收数量。
给出公式:
=SUMIF(A2:A8,& # 8221;*”,B2:B8)
列分类汇总
SUMIF函数强大的条件求和功能,在很多工作场景中都有广泛的应用。无论是财务人员还是营销人员,他们都需要汇总数据源中跨列的条件,例如,计划总额和实际完成总额将汇总到包含计划和实际销售额的表中。
在这种情况下,每个业务员的计划数据和实际完成数据交替出现。最后要在黄色区域输入公式,完成对应的计划和实际总和的统计。
在H3单元格中输入以下公式,并将其填入H3单元格:I9。
=苏米夫(美元2元:加元2元,加元2元,B3元:G3元)
实现搜索参考功能。
看了这个标题,有些家伙很不解。用VLOOKUP函数和INDEX+MATCH查引用不是他们的事吗?SUMIF为什么要来凑热闹?
你说得对。除了条件求和,SUMIF在某些场景下还可以实现查找引用的功能。
在本例的表格中,左侧是数据源区域,右侧黄色区域是公式区域。
为了根据销售员找到相应的结果,使用了一个公式。
给出公式(H2输入)
= SUMIF($ A $ 2:A $ 12,$G2,B$2:B$12)
排除误差值的总和
由于各种原因,我们在处理数据时不可避免地会遇到错误值。当数据源包含错误值时,通用求和公式也会返回错误值。那么如何才能在不影响条件求和的情况下消除误差值呢?
让我们来看看这个案例:
如果是职场小白,看这个数据源就蒙了,几乎各种错误值都会报出来。我该怎么办?
行家伸手就知道有没有。
给定公式,在单元格D2中输入以下公式
=SUMIF(B2:B12,& # 8221;& lt9e307 & # 8243)
计算数量和非空接收日期。
当我们遇到数据源时,不可避免地会有一些字段的值为空或无效。这时候往往需要排除这些无效记录进行统计。
之前,我们介绍了在文本数据中掺杂空值的处理方法。参见注册人非空收据的SUMIF函数统计。今天,我们将介绍在下一个日期数据中掺杂空值的处理方法。
下面用一个案例来说明如何使用SUMIF函数对求和条件中涉及的空值进行计数。
收货日期为空的记录都是无效记录,所以在统计收货数量时不会考虑,只统计非空的登记人的收货数量。
给出公式:
=SUMIF(A2:A8,& # 8221;& lt& gt”,B2:B8)
多列区域条件求和
在前面的教程中,我们介绍了SUMIF函数的各种条件求和方法,都是条件区域只有一列,求和区域只有一列的场景。那么,如果条件区域和求和区域都是多列区域,该怎么办呢?
上图是某企业的员工人数信息表。雇员编号和相应的姓名放在多列中。需要根据B10:B12单元格中的员工姓名提取相应的员工编号。
给出一个公式
在单元格B10中输入以下公式,并将其复制到单元格B12。
=SUMIF(B$2:D$6,A10 A $ 2:C $ 6)