excel表格_excel函数公式大全_execl从入门到精通
公式总结：提取员工身份信息，人事小姐姐拿好不谢！2024-03-11 15:40:19
之前写过用自定义函数提取员工身份证个人信息，很多小伙伴表示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将女表示为1