从身份证号码中可以提取到哪些信息,用Excel这些函数公式来完成
【温馨提示】亲爱的朋友,阅读之前请您点击【关注】,您的支持将是我最大的动力!
作为公司的人事部门,员工信息中不可缺少的就是身份证号码,身份证号码中包含了很多信息,以前小编也分享过提取信息的教程。今天小编再来做个分析总结,从身份证号码组成讲起。
【本教程中的身份证号码为模拟号码,请勿对号入座】
身份证号码组成:
l 1-2位代表省、直辖市、自治区
l 3-4位代表地级市、盟、自治州
l 7-14位代表出生年、月、日
l 17位代表性别
l 18位为校验码
一、省、市、县提取公式:
省:=VLOOKUP(LEFT(B2,2),地区编码!A:B,2,0)
市:=VLOOKUP(LEFT(B2,4),地区编码!D:E,2,0)
县:=VLOOKUP(LEFT(B2,6),地区编码!G:H,2,0)
公式中用到了LEFT和VLOOKUP函数
LEFT(提取的字符串,提取的个数):从第一个字符开始提取指定个数的字符。
VLOOKUP(你找谁,在哪里找,在第几列找,精确找还是模糊找):
【在哪里找】用的是下面的地区编码。这个编码表可能从网上下载,也可以私信我发你。
二、出生日期提取公式:
公式:=--TEXT(MID(B2,7,8),"0000-00-00")
MID(提取的字符串,从第几个字符开始,提取几个字符)
TEXT函数格式化提取的字符串,然后加上两个减号将文本格式转换成数字形态的日期,再设置单元格格式为日期。
三、根据出生日期计算年龄
年龄从身份证号码中是不能直接提取出来的,但可以根据生日或7-10位年份计算出来
公式1:=YEAR(TODAY())-YEAR(B10)
公式2:=DATEDIF(--TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"y")
四、提取生肖
公式:=VLOOKUP(MOD(MID(B2,7,4),12),D2:E19,2,0)
MOD为取两数相除的余数
五、提取星座公式
公式:=VLOOKUP(--MID(B2,11,4),F10:G22,2,1)
六、提取性别
公式:=IF(MOD(MID(B2,17,1),2)=1,"男","女")
MID(B2,17,1)提取身份证第17位的代表性别的数字,用MOD函数取得除以2的余数,再用IF函数判断余数是否为1,为1则为奇数返回“男”,否则为“女”。
小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持。