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在工作中的熟练度非常有帮助。
总的来说,大家还需要根据自己的实际情况,学习能够提高自己工作效率的函数,那才是真正的“万能公式”。