Excel里有400+个函数，这5个「万能函数」最值得学！

Excel里有4 00 多个函数，每个函数都是针对某一种问题而设计的。

但有的函数，天生就是「卷王」，不仅要做自己的本职工作，别人的工作也要抢过来做， 身兼数能，乐此不疲 。

被广大函数爱好者冠以「 万能函数 」的雅号。

下面，就让我们一起来盘点一下吧！

1、 SUBTOTAL

2、 AGGREGATE

3、 SUMPRODUCT

4、 T EXT

5、XLOOKUP

还不会的同学，可以学起来啦~

编辑｜ 六姑娘

作者 | 老菜鸟

万能函数之一、SUBTOTAL

功能1：动态求和

公式为=SUBTOTAL(9,D2:D21)

如下图所示，我们通过条件筛选，可以快速得到动态求和的结果。

同理，这个功能也可以延伸 。

动态求 平均值 ：

=SUBTOTAL(1,D2:D21)

动态求 最大值 ：

=SUBTOTAL(4,D2:D21)

动态求 最小值 ：

=SUBTOTAL(5,D2:D21)

总结：通过修改SUBTOTAL的第一参数能实现不同的功能， 能实现11种函数的功能 ，具体对照关系如图所示。

每种功能还有两种情况， 1-11 是对 包含隐藏的数据进行求和 ， 101-111 是 忽略隐藏的数据进行求和。

万能函数之二：AGGREGATE

具备了 19种函数 的功能，8种应用场景，如果要把功能和场景组合起来的话，能实现一百多种统计效果。

AGGREGATE的基本格式为：= AGGREGATE（统计功能，忽略哪些值，数据区域，k值）

下图列举了AGGREGATE的19种功能代码：

还有8种应用场景：

举个例子：

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

关于AGGREGATE的更多详细用法可参阅往期教程：

万能函数之三：SUMPRODUCT

SUMPRODUCT函数可以实现的功能有：各种情况下的条件求和、条件计数；排名次、分组排名次等等统计功能。

下面 举几个 例子 看看。

示例1、条件求和

=SUMPRODUCT((条件区域=条件)*数据区域 )

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

示例2、多条件求和

=SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)*数据区域)

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

示例3、按金额排名次

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

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

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

关于SUMPRODUCT函数的更多详细用法可查看教程：

万能函数之四：TEXT

=TEXT(要处理的数据, "要使用的格式代码")。

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

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

示例2、处理日期类问题

将日期显示完整的年月日，同时显示出是星期几，公式为：=TEXT(C2,"e年mm月dd日 aaaa")

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

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

示例4、 金额变成大写

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

万能函数之五：XLOOKUP

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

篇幅原因，这里不再展开讲解，感兴趣的同学可以看之前的教程：

好啦，今天一共分享了五个万能函数，各有特点。

不过，要想能达到灵活应用，还需要下一番功夫才行！

不知道这五个函数你最喜欢哪个呢？欢迎群里交流哟~

宠 粉 福 利

2元领取：全套Excel技巧视频+200套模板