我们都知道当数据过多的时候，我们制作Excel图表就会显得非常的复杂，图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
excel提取员工身份信息，人事小姐姐拿好不谢
Excel函数
2023-01-08
之前写过用自定义函数提取员工身份证个人信息，很多小伙伴表示VBA较难理解，是否能用函数获取？
今天我们就来总结下通过身份证我们可以获取哪些信息，分别利用哪些公式提取？
身份证号码的构成
在说公式之前我们先了解下身份证号码的构造：公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成；
排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码，其中顺序码最后一位也就是整个身份证号码倒数第二位为性别判断码，奇数为男性、偶数为女性，通过这个我们可以轻易判断性别。
分析完身份证号码的组成之后，我们可以轻易获取员工的出生年月（生日）、性别、籍贯以及其它信息。
获取员工生日
公式：=TEXT(MID(B2,7,8),"0000-00-00")
通过上面对身份证号码的分析得到7至14位为员工的出生年月日，截取出来即可；
两个函数的嵌套，其中MID函数为截取函数，截取出来的内容为【19900809】，在利用text函数格式化输入，格式化为日期格式，获得最终结果。
获取员工性别
公式：=IF(MOD(MID(B2,17,1),2),"男","女")
通过第一步我们得知身份证号码倒数第二位为性别标识码，奇数男性、偶数女性，提取出来判断奇偶数即可；
第一步同样是MID函数取出倒数第二位数字，接着用MOD取余函数，判断取出来的数字除以2是否能整除，能整除 余数为0，代表女性，不能整除 余数为1，代表男性；
最后利用IF函数返回男女，不能整除判断条件为1（true），返回IF函数第二个参数男，能整除判断条件为0（false），返回IF函数第三个参数女。
一共嵌套了三个函数，小伙伴们拆开看要容易理解一点。
计算年龄
公式：=DATEDIF(TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y")
计算年龄只要计算员工从出生至今一共经历多少个年份即可；
第一步提取员工出生年月日并格式化为日期格式，第二步将员工出生日期与当前日期做差，返回整年数；
先利用mid函数取出员工出生年月日，在利用text转换成日期格式；DATEDIF 返回两个日期之间的年月日间隔数，参数"Y"代表返回两日期之间的整年数。
获取员工籍贯
开头对身份证号码分析过，前两位代表省份，具体数字对应哪个省份?有一张对应关系表，如下图A/B两列所示，对应表在网页上都有，小伙伴们可自行搜索；
第一步提取开头两位数字，这里还是用MID函数：=MID(E2,1,2)，
接着用匹配函数匹配上省份即可：=VLOOKUP( F2*1,A:B,2,0)；
这里的F2函数之所以要乘以数字1，主要是因为身份证号码是文本格式，利用MID函数取的前两位也是文本格式，而省份匹配表A列却是数值格式，直接匹配的话无法返回正确结果，F2*1直接将文本变成了数值格式，在进行匹配，返回正确内容！
制作员工生日提醒
出生年月提取：=TEXT(MID(B2,7,8),"0000-00-00")
30天生日提醒：=TEXT(30-DATEDIF(C2-30,TODAY(),"YD"),"还有0天生日;生日已过;今天生日")
这里的30天主要是提前30提醒，改成15天、10天、3天即可；
其中DATEDIF函数的参数"YD" 表示起始日期与结束日期的同年间隔天数，忽略日期中的年份；
TEXT函数的第二个参数"还有0天生日;生日已过;今天生日"表示如果第一个参数结果X大于0，则返回"还有X天生日"，如果参数结果小于0，则返回"生日已过"，如果参数结果等于0，则表示今天生日，返回"今天生日"。
小结
以上就是关于身份证账号相关的公式，以及可以获取的那些信息，主要运用的到函数主要包括三个：MID取值函数、TEXT格式化输出函数、DATEDIF返回日期差函数；
公式皆不复杂，繁琐在于多层嵌套，小伙伴们在书写的时候从里向外一步步嵌套即可。如果逻辑理解不了也无所谓，毕竟我们不一定要学会如何造车子，会开就好！
excel提取员工身份信息，人事小姐姐拿好不谢的下载地址：
相关文章
- 详细阅读
-
excel 合并上百个工作簿数据，你花了一天别人只用了10秒详细阅读
在工作中，可能许多朋友都会碰到一个情况，那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中，许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
-
Excel文本函数，excel拆分和组合函数，掌握这些就够了！详细阅读
今天在这里为你分享5个Excel文本函数，这些拆分和组合函数，你一定会用上的。①LEFT函数公式：=LEFT(A2,1)在Excel表格中，需要想要拆分汉字，想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
-
Excel函数一键变翻译，学会了这个方法，再也用苦思冥想了！详细阅读
相信大家也和我一样，才开始看到Excel可以当做翻译软件的时候会很好奇，这究竟是怎样做到的？其实，这个方法并不是很难，它是由一个函数公式而制作出来的，好了，首先我们一起来看看成......
2023-01-08
-
Excel函数公式大全，办公室人员必备！详细阅读
函数可以说是所用快捷方法中最为简单的一种方法，为什么很多人认为函数用起来很难了？主要是因为它拥有很长的函数公式，记不住。其实不管是学Excel函数，还是学习其他的一些快捷方法......
2023-01-08