【置顶公众号】或【设为星标】及时接收不迷路
关键字:text;excel教程;万能函数
全文1380字,预计4.5分钟读完
Excel里有400多个函数,每个函数都是针对某一种问题而设计的。
但有的函数,天生就是“卷王”,不仅要做自己的本职工作,别人的工作也要抢过来做,身兼数能,乐此不疲。
被广大函数爱好者冠以“ 万能函数”的雅号。
下面,就让我们一起来盘点一下吧!
还不会的同学,可以学起来啦~
如下图所示,我们通过条件筛选,可以快速得到动态求和的结果。
同理,这个功能也可以延伸。
总结:通过修改SUBTOTAL的第一参数能实现不同的功能,能实现11种函数的功能,具体对照关系如图所示。
每种功能还有两种情况,1-11是对 包含隐藏的数据进行求和, 101-111是 忽略隐藏的数据进行求和。
具备了 19种函数的功能,8种应用场景,如果要把功能和场景组合起来的话,能实现一百多种统计效果。
AGGREGATE的基本格式为:= AGGREGATE(统计功能,忽略哪些值,数据区域,k值)
下图列举了AGGREGATE的19种功能代码:
在数据源中有个错误值#N/A,这时候用公式=SUBTOTAL(9,D2:D21)无法得到合计金额,而用公式=AGGREGATE(9,6,D2:D21)则不受影响,因为第二参数我们使用了6。
关于AGGREGATE的更多详细用法可参阅往期教程: 一个抵19个的统计函数之王AGGREGATE
SUMPRODUCT函数可以实现的功能有:各种情况下的条件求和、条件计数;排名次、分组排名次等等统计功能。
示例4、按门店分组排名次
关于SUMPRODUCT函数的更多详细用法可查看教程:学会这个Excel“万能”函数,1个顶10个
万能函数之四:TEXT
=TEXT(要处理的数据, "要使用的格式代码")。
示例1、 一个公式实现三种功能
示例2、处理日期类问题
将日期显示完整的年月日,同时显示出是星期几,公式为:=TEXT(C2,"e年mm月dd日 aaaa")
示例3、分情况显示不同结果
假如将金额目标定为2000元,可以用TEXT实现判断超额还是未完成任务的效果,公式为:=TEXT(D2-2000,"超额0元;还差0元;刚好")
示例4、 金额变成大写
将金额变成大写,并且只保留整数部分,公式为:=TEXT(D2,"[DBNum2]0元")
XLOOKUP函数一共有六个参数,函数的格式是=XLOOKUP(查找值,查找范围,结果范围,[找不到时显示的值],[匹配方式], [查询模式])。
篇幅原因,这里不再展开讲解,感兴趣的同学可以看之前的教程:
Xlookup函数的7种基本用法,最后一个87%的人不会!
好啦,今天一共分享了五个万能函数,各有特点。
不过,要想能达到灵活应用,还需要下一番功夫才行!
不知道这五个函数你最喜欢哪个呢?
欢迎评论区告诉我们呀~
欢迎进群,领取各类Excel模板