分享

三大函数家族能搞定80%的工作,谁最受欢迎?

 Excel不加班 2021-08-26

函数中最受欢迎的有三大家族,一个是以SUM函数为首的求和家族,一个是以VLOOKUP函数为首的查找引用家族,另外一个就是以IF函数为首的逻辑函数家族。根据二八定律,学好这三大家族的函数,就能完成80%的工作。

一起来看看SUM函数家族怎么完成各种汇总?

1.现在要知道所有产品的销售数量

=SUM(C2:C8)

SUM函数这个估计大家都很熟,就是对区域的数据进行求和。需要注意的是,如果区域存在文本,将自动被忽略。如现在C2单元格的值为:零,直接用SUM求和不会有任何影响。

2.如果想知道电脑的销售数量

=SUMIF(B2:B8,"电脑",C2:C8)

SUMIF→SUM+IF,IF就是如果的意思,也就是如果满足条件就对区域中的数据进行求和。

语法:

=SUMIF(条件区域,条件,求和区域)

但这个语法并不是硬性规定的,允许只存在两个参数。

如求数量大于50的和。

=SUMIF(C2:C8,">50",C2:C8)

缩写

=SUMIF(C2:C8,">50")

也就是说当条件区域跟求和区域一样的时候,求和区域可以不用写。

SUMIF函数有时可以取代VLOOKUP函数进行查找,当返回值为数值的时候用SUMIF函数更好,即使查找不到对应值也不会显示错误值。VLOOKUP函数查找要求很严,需要格式一样才行,2012-1-10为日期格式,如果现在用文本格式却查不到,返回错误值#N/A,而用SUMIF却可以,在这里也能体现出SUMIF的便利。

=VLOOKUP("2012-1-10",A2:C8,3,0)

正确写法:

=VLOOKUP(--"2012-1-10",A2:C8,3,0)

=SUMIF(A2:A8,"2012-1-10",C2:C8)

继续回到求和的问题上。

3.对2013-1-16之前的电脑的销售数量进行求和

=SUMIFS(C2:C8,A2:A8,"<2013-1-16",B2:B8,"电脑")

SUMIF是条件求和,SUMIFS就是多条件求和。

语法:

=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

4.对汇总外的所有表格的A1进行汇总。

SUM函数有一种特殊的表示方法,可以快速对多个表格进行汇总,这个很好用。

=SUM('*'!A1)

*是通配符,代表所有字符,用在这里的意思就是代表除了公式所在表格(汇总)的其他所有表格。

如果现在只需要包含月的所有表格的A1进行汇总。

=SUM('*月'!A1)

5.格式相同的多表汇总

很多人都喜欢将每月的数据分别存放在各个表格,然后再汇总。有6个月的数据,表格格式相同。

现在想在汇总表依次获取每月的销售量,该怎么做呢?

有人也许会说,这还不简单,直接SUM函数然后分别引用表格不就可以。

=SUM('1月'!C:C)

=SUM('2月'!C:C)

……

=SUM('6月'!C:C)

当然这也是种办法,不过用Excel就得考虑扩展性,假如是12个月?或者是按销售人员分表,那估计就得几百个表格,这样还能继续更改公式吗?

当然你时间比较充足,要这么做也无可厚非。但对于懒惰成性的卢子而言,这种事情是绝不允许发生的。现在每个表格名已知,区域也已知,只要引用起来不就完事OK。

=A2&"!C:C"

这样就可以获得1-6月C列区域,那是不是这样就能直接求和呢?

=SUM(A2&"!C:C")

得到的是#VALUE!,警告你出错了,原因是里面的区域仅仅是文本,而不是真正的区域,那该怎么办呢?

别急,先看下INDIRECT函数,这个函数可以对文本进行引用,一般叫间接引用。

有间接引用自然有直接引用。比如现在有三个人,分别叫甲、乙、丙,现在甲要知道丙的事情,可以直接去问丙,也可以通过乙间接去了解丙的事情。也就是说直接引用就是直接输入区域就行,不通过第三者,我们正常的引用都是直接引用,如区域C:C。间接引用就是通过第三者才能获得的,如INDIRECT(“C:C”)。也就是说,只要嵌套个INDIRECT函数就可以。

=SUM(INDIRECT(A2&"!C:C"))

再来看一个例子,加深理解。现在要得到A4跟B5的值,我们可以利用INDIRECT函数间接引用。

看到这里也许细心的朋友会发现,还少了一个重要的函数SUMPRODUCT的说明,这个低版本的朋友应该用得非常多,特别是多条件求和。但对于高版本的朋友而言,这个的作用就很小,这里简单提下用法就行。

6.毕竟是出自一家人,帮助都差不多,非常好理解,统计总金额。

=SUMPRODUCT(B2:B4,C2:C4)


只强调一句,SUMPRODUCT 将非数值型的数组元素作为0处理,如B4现在的值是文本=SUMPRODUCT({2;5;"无"},C2:C4),“无”在这里就等同于0。不再对这个函数的基础用法进行说明,否则会让大家误认为侮辱你们的智商。

推荐:天天被吊打的VLOOKUP,结果谁都离不开!

上文:透视表的这个用法太好用,简直绝了!


三大家族,你最喜欢用哪个函数?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多