职场人士每天都要处理大量的数据表格,但很多时候明明有很多便捷方法可以用,但就是不知道去哪里找,今天我就分享5个excel中最常用的公式,让你的工作效率大大提高~
一、身份证号码计算出生年月
要从身份证号码中得到出生日期,这种问题对于从事人资行政岗位的小伙伴一定不陌生,公式也比较简单:=TEXT(MID(A2,7,8),"0-00-00")就能得到所需结果,如图所示:
![514132c37a56a1347d5f32c3219546ba.png](https://i-blog.csdnimg.cn/blog_migrate/10b40cb76aede4ac08da52ba8d18c5ad.png)
要明白这个公式的原理,首先要知道身份证号码中的一些规则,目前使用的身份证基本都是18位的,从第七位数字开始的八个数字就表示出生日期。
这个公式中涉及到两个函数,首先来看MID函数,MID函数有三个参数,格式为:=MID(在哪提取,从第几个字开始取,取几个字)。
MID(A2,7,8)表示从A2单元格的第七个数字开始截取八位,效果如图所示:
![fb0f5df3970322f65746e0bad8e7507b.png](https://i-blog.csdnimg.cn/blog_migrate/6ff79ab6d779b6356933ab0b3ff67823.png)
出生日期提取出来后却不是我们需要的效果,这时候就该函数魔术师TEXT出马了,TEXT函数只有两个参数,格式为=TEXT(要处理的内容,“以什么格式显示”),本例中要处理的内容就是MID函数这部分,显示格式为"0-00-00",当然你要用"0年00月00日"这个格式显示也没问题,公式改为=TEXT(MID(A2,7,8),"0年00月00日")就可以了:
![c376be69ff701dd39bd00fd441dadfd1.png](https://i-blog.csdnimg.cn/blog_migrate/83147ba2875925ce7e7962dbf5104553.png)
二、根据身份证号码计算年龄
有了出生日期,当然就会想到计算年龄,公式为:=DATEDIF(B2,TODAY(),"Y")
![5044087fc3ff7f2e85fb323adad2c21e.png](https://i-blog.csdnimg.cn/blog_migrate/6687f23fe6e2b418fd83c43f64cffd1f.jpeg)
这里用到了一个Excel的隐藏函数DATEDIF,函数需要三个参数,基本结构为=DATEDIF(起始日期,截止日期,计算方式)。
本例中的起始日期就是出生日期,用B2作为第一参数;截止日期是今天,用TODAY()函数作为第二参数;计算方式为按年计算,用"Y" 作为第三参数。
如果需要直接从身份证号码计算年龄的话,公式可以写为=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"Y")
![6cbc5c6c8853fdd565c59b84b873fddc.png](https://i-blog.csdnimg.cn/blog_migrate/37b2aefd2bdd23f2b68057dd4f8ee244.jpeg)
三、按照区间获得不同结果
这类问题多见于绩效考核,例如公司对员工进行了绩效考核,需要按照考核成绩确定奖励级别,定级规则为:50分以下为E,50-65(含)为D,65-75(含)为C,75-90(含)为B,90以上为A。
可以使用公式=LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"})得到每个员工的奖励级别,结果如图所示:
![eeae7466f2d1f3ae2ce156874b75416f.png](https://i-blog.csdnimg.cn/blog_migrate/406eec9165321364e0bf56d81780825e.jpeg)
要解释这个公式的原理就费劲了,可以参考之前的LOOKUP函数相关教程。
其实要解决这类问题记住套路就够了:LOOKUP按区间返回对应结果的套路为=LOOKUP(成绩,{下限值列表},{奖励级别列表}),下限值之间用分号隔开,奖励级别之间同样用分号隔开。
也可以将成绩下限与奖励级别的对应关系录入在表格里,公式可以修改为=LOOKUP(E2,$I$2:$J$6),结果如图所示。
![da88fc75ad5c7538e18e67656b758e42.png](https://i-blog.csdnimg.cn/blog_migrate/1380cb8467c589341158f1977624b56c.jpeg)
四、单条件匹配数据
要想纵横职场,不会匹配怎么行?要做单条件匹配不会VLOOKUP怎么行?
VLOOKUP函数的基本结构为=VLOOKUP(找什么,在哪找,第几列,怎么找),例如按照姓名找最高学历,可以使用公式=VLOOKUP(G2,B:E,4,0)得到所需结果,如图所示:
![3b1a0f14cad6ae2366398b0cb52d1c23.png](https://i-blog.csdnimg.cn/blog_migrate/906b58757bc45035fcd3425aeb9d2a0d.jpeg)
使用这个函数有两个要点一定要知道:①要找的内容必须在查找范围的首列,例如按姓名查找时,查找范围是从B列开始而不是A列。
②第几列指的是查找范围的列而不是表格中的列,例如要找最高学历,在查找范围的第4列,而不是表格中的列数5。
五、多条件匹配数据
学会多条件匹配数据就真的无敌了!
举一个按姓名和商品名称两个条件匹配销售数量的例子,如图所示:
![c02ec4bdf15fd9fdc5010a378b7e038b.png](https://i-blog.csdnimg.cn/blog_migrate/0ab765d3dbaee36946522cc4d354011e.jpeg)
公式为=LOOKUP(1,0/(($A$2:$A$10=E2)*($B$2:$B$10=F2)),$C$2:$C$10)
使用LOOKUP函数进行多条件匹配的套路为:=LOOKUP(1,0/((查找范围1=查找值1)*(查找范围2=查找值2)*……*(查找范围n=查找值n)),结果范围),需要注意的是多个查找条件之间是相乘的关系,同时它们需要放在同一组括号中作为0/的分母。
好了,最常用的五类公式就分享到这里,用好了真的可以纵横职场哦!