您现在的位置: 首页 > 动态 > 文章正文动态

身份证号码提取出生日期公式(Excel如何从身份证号码中提取计算出生日期、性别及年龄呢)

发布时间：2022-10-21 11:03 点击：次

一个身份证号竟然包含这么多信息，你还敢随便泄露你的身份证号吗？

从事人事工作的朋友们少不了与身份证号打交道，数据量大的时候如何批量操作能？

【本文示例文件获取方法见文末】

今天，技巧君来讲讲身份证号！国际惯例，先来看下效果：

身份证号构成

身份证号由四部分组成：

地址码：表示编码对象常住户口所在县（市、旗、区）的行政区划代码

出生日期码：表示编码对象出生的年、月、日

顺序码：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性

校验码：根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

所以从身份证号中提取信息，我们需要对身份证号进行分段处理，要从地址码中对应提取出籍贯，必须有一个地区和编码之间的编码表，下面来看一下具体怎么制作吧！

身份证号提取性别

函数公式：=IF(MOD(MID(A2,17,1),2),"男","女")

说明：

a、MID(A2,17,1)函数提取第17位数

b、MOD函数对2取余数，偶数余数为0，说明为女性，奇数余数为1，说明为男性，再用IF函数来判断性别

c、外层再嵌套IFERROR规避错误值

身份证号提取年龄

函数：DATEDIF(--TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")

说明：

a、MID(A2,7,8)提取出生日期码

b、TEXT函数将文本处理为日期格式，--减负运算将文本转化为日期值

c、DATEDIF函数计算出生日期和今天的差值，即得到年龄

身份证号提取出生日期

函数：TEXT(MID(A2,7,8),"0年00月00日")

说明：

a、MID(A2,7,8)，提取出生日期码

b、TEXT函数处理为日期格式

身份证号提取生肖

函数：MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(A2,7,4)-4,12)+1,1)

说明：

a、MID(A2,7,4)提取出生年份

b、MOD函数对12取余数，刚好对应12生肖的序列代码，里面日期的-4和外面的 +1是为了和前面的生肖相对应

身份证号提取星座

函数：VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";20,"水瓶座";50,"双鱼座";81,"白羊座";111,"金牛座";142,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";267,"天秤座";298,"天蝎座";328,"射手座";357,"摩羯座"},2,TRUE)

说明：这个函数主要是了解星座和日期之间的换算关系，有兴趣可以做进一步了解，因计算规则较为复杂，这里不做详述