从身份证号码中可以提取到哪些信息,用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中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持。

(0)

相关推荐