二维码 购物车
部落窝在线教育欢迎您!

AGGREGATE等5个万能函数技巧解读,快来学习吧!

 

作者:老菜鸟来源:部落窝教育发布时间:2021-11-03 15:42:21点击:449

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:


Excel里有400多个函数,每个函数都是针对某一种问题而设计的,但是在这些函数中,有的函数身兼数能,一个函数就能具备很多种功能,因此被广大的函数爱好者冠以“万能函数”的称号,今天就来盘点一下Excel中都有哪些“万能函数”。

 

万能函数之一:SUBTOTAL

功能1.动态求和

公式为=SUBTOTAL(9,D2:D21),具体效果看动图演示。



注意对比公式求和的结果与下方状态栏的求和结果始终保持一致。

 

功能2.动态求平均值

公式为=SUBTOTAL(1,D2:D21),具体效果看动图演示




注意对比公式求平均值的结果与下方状态栏的求平均值结果始终保持一致。

 

功能3.动态求最大值

公式为=SUBTOTAL(4,D2:D21),有兴趣的同学可以自己测试。

 

功能4.动态求最小值

公式为=SUBTOTAL(5,D2:D21),有兴趣的同学可以自己测试。


这时候可能有些细心的伙伴已经发现了,通过修改SUBTOTAL的第一参数就能实现不同的功能,也就是说第一参数有多少选项这个函数就有多少个功能。


的确是这样的,SUBTOTAL函数的第一参数就能实现11种函数的功能,具体对照关系如图所示。

 

 

每种功能还有两种情况,1-11是对包含隐藏的数据进行求和,101-111是忽略隐藏的数据进行求和,隐藏的意思就是通过右键隐藏行。虽然对于使用者来说隐藏和筛选都可以让某些行看不见,但是对于这个函数来说是有区别的,详见动图。



可以看到使用9109在有隐藏行的情况下求和结果不同,但对于筛选求和则无影响。


这是第一个万能函数,具备了22种功能的SUBTOTAL

 

万能函数之二:AGGREGATE

这是一个比SUBTOTAL更为强大的万能函数,它具备19种函数的功能,8种应用场景(可以选择忽略哪些类型的值)。如果要把功能和场景组合起来的话,能实现一百多种统计效果。可以说SUBTOTAL会的AGGREGATE都会,但是AGGREGATE会的SUBTOTAL很多都不会。


AGGREGATE的基本格式为:= AGGREGATE(统计功能,忽略哪些值,数据区域,k值)


下图列举了AGGREGATE19种功能代码:

 

 

可以看出AGGREGATE函数的统计功能确实要比SUBTOTAL函数更加丰富。


还有8种应用场景:

 

 

在这8个应用场景中,前四个比较少用到,个人觉得最有用的是6(忽略错误值),举个例子来说。

在数据源中有个错误值#N/A,这时候用公式=SUBTOTAL(9,D2:D21)就无法得到合计金额,而用公式=AGGREGATE(9,6,D2:D21)则不受影响,因为第二参数我们使用了6

 

 

关于AGGREGATE的更多用法可以参阅往期教程:

https://mp.weixin.qq.com/s/lxE7OPuMiY6wgz6LrcxCjQ

这里要给大家强调的是:AGGREGATE虽说有四个参数,但是只有第一、三两个参数是必须要填的,这时候第二参数就默认为0,例如=AGGREGATE(9, ,D2:D21)。另外一点就是当第一参数填14-19的时候,第四参数就必须要填了,例如=AGGREGATE(14,7,D2:D21,2),就表示101日的第二高金额。

 

 

万能函数之三:SUMPRODUCT

SUMPRODUCT函数可以实现的功能有:各种情况下的条件求和、条件计数;排名次、分组排名次等等统计功能。与前两个函数的不同之处在于,SUMPRODUCT函数的参数中并没有一个选项是决定函数功能的,这个函数玩的就是套路,下面举几个例子让大家看看。


示例1.条件求和

公式为=SUMPRODUCT((A2:A21="一分店")*D2:D21)

 

 

SUMPRODUCT条件求和的套路为:=SUMPRODUCT((条件区域=条件)*数据区域)

 

示例2.多条件求和

公式为=SUMPRODUCT((A2:A21="一分店")*(B2:B21="朱毓华")*D2:D21)

 

 

SUMPRODUCT多条件求和的套路为:=SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)*数据区域)


示例3.按金额排名次

公式为=SUMPRODUCT(($D$2:$D$21>=D15)*1)

 

 

这个结果和公式=RANK(E2,$E$2:$E$21,1)一致,如果只是单纯排名次的话,SUMPRODUCT并没有太大的优势,但如果要做分组排名的话,RANK就无能为力了。

 

示例4.按门店分组排名次

公式为=SUMPRODUCT(($D$2:$D$21>=D2)*($A$2:$A$21=A2))

 

 

从结果不难看出,每个门店的金额都是单独排名次的。

关于SUMPRODUCT函数的更多示例介绍,可以参考之前的教程:

https://mp.weixin.qq.com/s/65Kf6oPOJW2TbJsNgIaQ4w

 

以上介绍的三个万能函数都是统计类的,SUBTOTALAGGREGATE的统计功能更强,是通过筛选或者隐藏来实现按条件统计的,这也是两个函数独一无二的特性。而SUMPRODUCT的各种使用套路中,几乎都出现了一个*号,关于*号和这个函数之间的秘密,之前也有专门的教程,有兴趣的朋友可以去看看。

https://mp.weixin.qq.com/s/8aK-zGSFTDXCVv0ZDCmGzA

 

接下来要介绍的则是非统计类的万能函数。

 

万能函数之四:TEXT

TEXT函数只有两个参数,格式为:=TEXT(要处理的数据, "要使用的格式代码")

函数的功能说起来也很简单,就是将单元格里的数据按照指定的格式代码显示出来,但是格式代码就太多了,如果一种格式代码算一种功能的话,TEXT函数到底有多少种功能怕是数不清了,以下仅为大家分享几个有代表性的示例。


示例1. 一个公式实现三种功能

在金额前面加上人民币符号¥,后面加上单位元,同时不不显示小数部分,公式为:=TEXT(D2,"¥0")

 

 

示例2.处理日期类问题

将日期显示完整的年月日,同时显示出是星期几,公式为:=TEXT(C2,"emmdd aaaa")

 

 

示例3.分情况显示不同结果

假如将金额目标定为2000元,可以用TEXT实现判断超额还是未完成任务的效果,公式为:=TEXT(D2-2000,"超额0;还差0;刚好")

 

 

示例4.金额变成大写

将金额变成大写,并且只保留整数部分,公式为:=TEXT(D2,"[DBNum2]0")

 

 

通过这四个例子,相信大家已经对TEXT的功能有所了解,但要真的用好TEXT函数就必须掌握格式代码,对于新手来说,自定义格式是探究格式代码的一个捷径,具体方法可以参考之前的教程:

https://mp.weixin.qq.com/s/uhEd32PrQPzs_yXMIMTAFA

 

四个多功能函数介绍完了,最后要介绍的这个万能函数是Excel365才有的,功能主要集中在数据匹配方面,估计有些朋友已经猜到了,这个函数就是XLOOKUP

 

万能函数之五:XLOOKUP

XLOOKUP函数一共有六个参数,函数的格式是=XLOOKUP(查找值,查找范围,结果范围,[找不到时显示的值],[匹配方式], [查询模式])

其中第四参数[匹配方式]有四个选项,第五参数[查询模式]也有四个选项,再考虑函数本身的匹配特性,少了VLOOKUPLOOKUP的一些限制,是一个名副其实的万能匹配函数。

但是由于这个函数仅在Excel365版才有,很多用户只能先通过教程来了解其功能了。

参考教程:https://mp.weixin.qq.com/s/D_zrKppWUlGDlRZQTdOxIQ

 

今天一共分享了五个万能函数,在不考虑与其他函数嵌套使用的前提下,每个函数各有特点,要想能达到灵活应用还需要下一番功夫才行,不知道这五个函数你最喜欢哪个呢?

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

将excel中的数据按照指定数量进行重复,非得用VBA才能实现吗?

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。