当前所在位置: 首页 > 儿童教育 > 正文

史上最全条件求和函数SUMIF教程

2023-01-26 餐饮美食网 【 字体:

在职场办公中,经常需要对数据进行条件求和汇总,SUMIF函数是工作中使用频率超高的条件求和函数之一。

本文介绍了SUMIF函数的丰富用法,便于你在自己的实际工作中直接借鉴和使用。

1SUMIF函数基础语法解析

SUMIF函数可以对范围中符合指定条件的值求和,该函数拥有十分强大的条件求和功能,在工作中有极其广泛的应用,其基本语法为:

SUMIF(range,criteria,[sum_range])

range:必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

criteria:必需。用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数。

sum_range:可选。要求和的实际单元格(如果要对未在range参数中指定的单元格求和)。如果省略sum_range参数,Excel会对在range参数中指定的单元格(即应用条件的单元格)求和。

说明:

(1)criteria中的任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号括起来。如果条件为数字,则无需使用双引号。

(2)criteria参数中支持使用通配符(包括问号“?”和星号“*”)。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符“~”。

(3)使用SUMIF函数匹配超过255个字符的字符串或字符串#VALUE!时,将返回不正确的结果。

(4)当sum_range参数与range参数的大小和形状可以不同。求和的实际单元格通过以下方法确定:使用sum_range参数中左上角的单元格作为起始单元格,然后包括与range参数大小和形状相对应的单元格。注意,这种情况下会使SUMIF函数具有易失性,即引发工作表重算。

SUMIF函数本身不是易失性函数,但当SUMIF函数中的range和sum_range参数包含的单元格个数不相等时,会具备易失性。如以下公式:

=SUMIF(B2:B9,”女”,C2:C3)

=SUMIF(B2:B9,”女”,C2:C99)

=SUMIF(B2:B9,”女”,C2)

三个公式返回的结果一致,SUMIF函数的sum_range参数的单元格个数都与range的单元格个数不同,但都会将sum_range的区域按照C2:C9计算,即以C2为起始单元格,延伸至大小和形状与B2:B9相同的单元格。相当于以下公式:

=SUMIF(B2:B9,”女”,C2:C9)

易失性会引发工作表的重新计算,计算时间会比预期的要长,工作中应尽量避免这种情况出现。

(5)SUMIF函数中criteria参数的格式会限定其选择条件求和的范围。即如果第二参数是数值,SUMIF函数就只对第一参数是数值格式的单元格对应的求和区域中进行统计,而忽略其他格式如文本、逻辑值、错误值等。利用SUMIF函数的这个特性,我们可以排除错误值进行求和。

2SUMIF函数统计单字段单条件求和

工作中最常见的需求当然就是单条件求和啦,SUMIF函数在这方面可谓得心应手!

下面就来讲讲SUMIF函数统计单字段单条件求和条件求和,还讲解了当SUMIF的第三参数缺省时的运算方式和原理。

SUMIF函数的单条件求和应用非常广泛,在很多情况下,当条件区域和求和区域重合时还可以简化公式写法,下面结合一个案例来介绍具体的方法。

要求从数据源中统计90分以上的成绩之和,先给出公式

=SUMIF(B2:B12,”>90″)

3SUMIF函数统计单字段多条件求和

上一节教程中我们学会了SUMIF函数的单条件求和,那么当工作中出现对某个字段并列多条件求和的需求,又如何应对呢?

我们结合下面这个案例来具体介绍。

表格中左侧是数据源区域,要求统计北京分公司、上海分公司、广州分公司这三家销售额总和,如果是只求一家分公司(如北京)的销售额那很简单,公式为

=SUMIF(A2:A12,”北京”,B2:B12)

多家怎么办呢?最直接的办法当然是这样:

=SUMIF(A2:A12,”北京”,B2:B12)+ SUMIF(A2:A12,”上海”,B2:B12)+ SUMIF(A2:A12,”广州”,B2:B12)

是不是只有这种方法呢?如果需要统计的分公司增加,岂不是公式越来越长?

当然会有更好的办法啦!

给出这里使用的公式:

=SUM(SUMIF(A2:A12,{“北京”,”上海”,”广州”},B2:B12))

4SUMIF函数统计前3名成绩和

前面的课程中我们学会了SUMIF函数的单条件求和、多条件求和,那么当我们在工作中遇到涉及数值大小的问题,该用什么思路去解决呢?

下面这个案例,我们就用SUMIF函数结合LARGE函数配合搞定一个极值统计问题。

表格中左侧是数据源区域,要求统计前三名成绩之和。

我们可以分为两步来思考这个问题,第一步是需要从数据中用公式提取前三名的成绩,第二部是将它们汇总求和。这样即可数据源变动,前三名成绩也会随公式结果动态更新,从而始终保证结果的正确。

这里给出公式

=SUMIF(B2:B12,”>”&LARGE(B2:B12,4))

5SUMIF函数模糊条件求和

有时我们要按照模糊条件求和,而SUMIF函数支持通配符的使用,下面我们结合案例来介绍模糊条件求和的方法。

表格左侧是数据源区域,要求统计姓“张”的员工成绩之和,也就是姓名中以“张”开头的,我给出公式。

=SUMIF(A2:A12,”张*”,B2:B12)

6SUMIF函数根据日期区间统计

在工作中我们经常遇到按日期区间统计的需求,比如需要统计月初到当前日期的销售额,或统计周年庆(比如历时5天)的销售额……针对这类条件求和如何实现呢?

今天我们结合一个简单案例,来介绍以日期区间作为条件的求和方法。

下面给出公式。

=SUM(SUMIF(A2:A12,{“>=2016/4/1″,”>2016/4/5″},B2:B12)*{1,-1})

7SUMIF函数统计登记人非空的入库数

工作中的数据源可能来自多种渠道,有的是系统导出,有的是人工填写收集,有的是第三方机构提供,都难免遇到数据源中某字段有空值或者无效值的情况,这时往往需要排除这些无效记录进行统计。

今天我们结合一个简单案例,讲解如何利用SUMIF函数统计求和条件涉及非空值的方法。

登记人为空的记录都属于无效记录,统计入库数量时不予考虑,仅统计登记人非空的入库数。

给出公式:

=SUMIF(A2:A8,”*”,B2:B8)

8SUMIF函数隔列分类汇总

SUMIF函数强大的条件求和功能在多种工作场景中均有广泛应用。无论是财务还是市场销售人员,都会面临在数据源中跨列条件求和的需求,比如在包含计划和实际销售额的表格中分别汇总计划总和、实际完成总和。

在这里案例中,每个业务员制定的计划数据和实际完成数据交替出现,最后要在黄色区域输入公式,完成对应的计划和实际总和的统计。

在H3单元格输入以下公式,填充至H3:I9单元格区域即可

=SUMIF($B$2:$G$2,H$2,$B3:$G3)

9SUMIF函数实现查找引用功能

看了这个标题有的小伙们就纳闷了,查找引用不是VLOOKUP函数、INDEX+MATCH他们的事吗?怎么SUMIF也来凑热闹?

你没看错,SUMIF除了条件求和,在一些场景下也能实现查找引用功能。

这个案例的表格中,左侧是数据源区域,右侧的黄色区域是公式区域。

要实现按照业务员查找对应的成绩,一个公式搞定。

给出公式(H2输入)

=SUMIF($A$2:$A$12,$G2,B$2:B$12)

10SUMIF函数排除错误值求和

出于各种原因,我们在处理数据时难免遇到错误值,当数据源中包含错误值时,普通的求和公式返回的也是错误值,那么如何既能排除错误值又能不影响条件求和呢?

我们来看这个案例:

如果是职场小白,一看这数据源就蒙圈了,各种错误值差不多都来报道啦,怎么办?

行家伸伸手,便知有没有

给出公式,D2单元格输入以下公式

=SUMIF(B2:B12,”<9e307″)

11SUMIF函数统计入库日期非空的数量和

我们遇到的数据源难免遇到某字段有空值或者无效值的情况,这时往往需要排除这些无效记录进行统计。

之前我们介绍过当文本数据中掺杂空值的处理方法,见《SUMIF函数统计登记人非空的入库数》,今天再来介绍下日期数据中掺杂空值的处理方法。

下面我们结合案例,讲解如何利用SUMIF函数统计求和条件涉及空值的方法。

入库日期为空的记录都属于无效记录,统计入库数量时不予考虑,仅统计登记人非空的入库数。

给出公式:

=SUMIF(A2:A8,”<>”,B2:B8)

12SUMIF函数多列区域条件求和

前面的教程中我们介绍了SUMIF函数各种各样的条件求和方法,都是条件区域只有一列,求和区域也只有一列的场景,那么如果遇到条件区域和求和区域都是多列区域,如何处理呢?

上图展示的是某企业的员工工号信息表,工号和对应姓名放置在多列区域中,需要在B10:B12单元格区域根据员工的姓名提取对应的员工工号。

给出公式

在B10单元格输入以下公式,将公式向下复制到B12单元格。

=SUMIF(B$2:D$6,A10,A$2:C$6)

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag