Excel 万能公式有哪些？

作为一个整天和数据打交道的数据分析师，对于Excel还算精通。如果让我在Excel的400多个函数中挑几个“万能公式”，那么我觉得这3个值得重点学习。

1、Vlookup函数

VLOOKUP是一个查找函数，也可以算是一个“万能公式”，因为如果会用这个函数的话，可以极大地提升我们的工作效率。

一些求职者在简历中都会写自己精通Excel，想检验他说的是不是真话，一个简单的办法就是看他理不理解、会不会用VLOOKUP函数。

VLOOKUP函数语法：=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

函数中共4个参数，要想理解这4个参数，你可以把VLOOKUP函数想象成你的秘书，我们就把它叫做小V吧。

比如，有这么一张表格，记录的二手房某地挂牌销售的一些二手房的相关信息（数据都是随机生成的哈，不是真的）。

现在有一个新表，登记了几个业主的名字，需要查询到这些业主各自的房屋户型是什么。

数量少的时候，肯定是可以一个一个去对，但是数量多的时候呢？俗话说有事秘书干，这活就得让小V去做，但是你要告诉小V怎么去做。

第一步，告诉小V，你想找什么（lookup_value）。在这里，你想找的是马念梅这个人的相关信息。

lookup_value=A2

​

​

第二步，你要告诉小V，去哪找呢（table_array,）?去另一张表里找。

​

但这里需要注意的一点是，在选择区域的时候，需要保证你要查找的内容处在第一列的位置上。也就是【户主姓名】必须在第一列的位置，否则小V就不知道该怎么干了，要不说它脑子不好使呢。

​

​

第三步，你告诉小V，你想看到什么结果。这里我们要看的是【户主姓名】对应的【房屋户型】信息，那么你就要告诉小V，只看【房屋户型】，而【房屋户型】在第二步选择好的范围中，处于第3列，所以这里要填数字3。

​

​

第四步、你告诉小V，你是想精确查找还是模糊查找，这两个的区别大家先不用管，只需要记住，一般情况下，我们都是要精确查找，所以填FALSE，表示精确查找。

​

​

这样，小V就帮你找到了【马念梅】的【房屋户型】信息。然后将函数向下填充，我们就可以找到其他人各自的【房屋户型】信息了。

​

​

这就是VLOOKUP函数的基本用法。

VLOOKUP还有进阶用法，包括函数嵌套、模糊查找，其应用范围也是也是非常的广，在工作中确实可称为一个“万能公式”。关于VLOOKUP的更多用法，可以看看我推荐的数据分析课，里面很多案例都会用到vlookup函数，你会发现它真的“万能”。

2、Subtotal函数

Subtotal函数是一个多功能的函数，通过修改参数，可以实现11个函数功能，所以如果记住它的用法的话，就相当于学会了11个函数，可以称为“万能”。

SUBTOTAL函数语法：=SUBTOTAL(function_num,ref1,[ref2], ...)

这里面最重要的就是第一个参数function_num（功能代码），后面ref1,[ref2],…就是指的我们选择的数据。

function_num共有22个，1-11和101-111，这两组对应的函数是相同的，区别在于1-11会计算隐藏单元格内的值，101-111则不会。

​

​

接下来结合实例做具体的说明：

还是刚才的二手房交易信息。

​

​

我们计算一下建筑面积的总和，输入SUBTOTAL，参数选择9，然后选择要计算的单元格，最后得出结果。

​​

​​

我们如果要计算这些房子的平均值，那么我们只需要改动一个参数，把9改为1即可。

​

​

接下来说说101-111，它指的是忽略隐藏值，具体效果，可以看对比图。

​

3、Aggregate函数

Aggregate函数其实与Subtotal相似，它能够实现Subtotal的11个函数，而且在此基础上又增加了8个函数。

​

​

不过Aggregate最强的还在于它增加了第二个参数Options，这个参数有0-7共8个选项，可以用来设定对忽略哪些值，被忽略的值不参加函数运算。

​

​

越来越复杂了是吧？其实常用的功能也就那么几个，举个例子来说吧，我们在用Excel处理数据的时候，经常会遇到“#N/A”这个符号，表示数值出错。

在这种情况下，sum和subtotal都是没办法进行正常计算的，会提示错误。

但是如果使用Aggregate函数求和，第二个参数Option选择“2”，忽略错误值，那么金额可以忽略掉出错的E7单元格，计算其他单元格的总和了。

​

​

Aggregate引入了忽略行为选项，在组合之下可以实现更多功能，用处非常广泛。

4、Text函数

我们在使用Excel，尤其是用作对外展示的报告结果时，经常需要对数字格式进一定的处理，让数据看起来符合特定的格式。

只要你需要将一个数值变成特定格式的文本，那么就可以用Text函数。但实际使用的时候，Text可以实现的功能远不止是一个转换格式那么简单。

Text函数语法：=TEXT(value,format_text)

和前面几个函数比起来，Text函数看起来简单多了，毕竟只有一个参数format_text，但是它能够实现的功能可不少。因为format_text这个参数没有固定的选项，你可以自定义要这个格式。

1、数字补齐

比如我们的二手房信息表中，有一个入户编号，它最大是9位数，不够的前面用0补齐，这时候我们就可以输入函数=Text(C2,"000000000")。

在这个函数里面，参数“000000000”，0表示的是占位符，意思是如果对应的位置上有数字，那么就显示元数字，如果没有的话，就填充0。

2、处理日期

在不同场合，我们要显示的日期格式也不一样，有时候我们可能需要写成“2023年8月2日”，有时候可能要写成“2023-8-12”，有时候我们可能需要的是一个“八月”。这时候我们就可以用Text函数来实现我们想要的效果。

常见的时间格式以及用法可以参考下图。

3、添加单位

有时候我们在表达和金额相关的数据时，需要加上表示货币的符号，或者加上“元”等单位，这时候我们就可以输入函数=Text(E2,"¥0元")，这里面“0”也是代表的占位符。

4、简单的条件判断

说起条件判断，你可能想起来的是if函数，其实text函数也可以实现简单的条件判断功能。

举个例子来看，比如我们要根据房屋面积大小，来划分户型，假设，大于130平米的房子，我们要标记为“超大户型”，大于100平米小于130平米的，我们要标记为“大户型”，小于100平米的，我们需要标记为“小户型”——当然这都是我为了演示公式而瞎编的。

那么这个公式我们就可以这样写，和if类似，=TEXT(E2,"[>130]超大户型;[>100]大户型;小户型")。

5、提取身份证中的出生日期

Text函数和其他函数结合在一起使用，那么可以实现更多、更实用的功能。

比如说，我们要提取身份证的出生年月日，然后将其显示成“XXXX-XX-XX”的格式，那么就可以和MID公式结合。

=TEXT(MID(C2,7,8),"0000-00-00")

其中MID公式用于从指定位置开始提取指定数目的字符，我们都知道，身份证从第7位数字开始代表的是一个人的出生日期，一共8个数字，那么就可以用MID函数来提取，再用Text函数进行格式的转化。

当然了，Text还有更多用处，这里就不一一介绍了，大家只要记住，在涉及到格式转化的时候，就可以考虑一下能不能用Text实现。

小结

其实Excel中的函数公式非常多，目前已经超过了450个，随着Excel版本的不断更新，也不断有新函数被加入其中。

不同行业、不同岗位的人，常用的函数是不一样的。对于很多上班族来说，常用的也就一个sum求和，而对于我们做数据分析的人来说，由于工作习惯不一样，每个人常用的函数也不一样。

更多的Excel使用技巧，可以看看这个数据分析训练营，参加就能免费领取Excel秘籍，你会发现很多公式在实际工作中的神奇用法，对于提高Excel在工作中的熟练度非常有帮助。

总的来说，大家还需要根据自己的实际情况，学习能够提高自己工作效率的函数，那才是真正的“万能公式”。

‍