最新资讯

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

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

    发布时间:2022-10-23 23:18 点击:

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

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

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

      今天,技巧君来讲讲身份证号!国际惯例,先来看下效果:

      身份证号构成

      身份证号由四部分组成:

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

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

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

      校验码:根据前面十七位数字码,按照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)

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

      身份证号提取籍贯信息

      函数:VLOOKUP(LEFT(A2,6),地区代码!A:B,2,1)

      说明:籍贯信息主要有地区代码表,将代码换算为地区就可以了

      注意事项:

      1、本文所用所有身份证号均为地区码+出生日期码+顺序码+校验码随机组合而成,非真实身份证号,如有雷同,纯属巧合!

      2、本文涉及的主要函数有:MID、MOD、TEXT、IF、VLOOKUP、DATEDIF、IFERROR、VALUE、内存数组等,这些公式都为常用公式,这里只做用途功能介绍,想要详细学习以上函数的基础用法,请持续关注 EXCEL精选技巧的后续文章!

      3、VLOOKUP函数已有文章做了详细阐述,请参考技巧君的另一篇文章《连VLOOKUP这些用法都不会,别说你会EXCEL!》点击技巧君头像,查看历史文章即可

      示例文件获取

      点击右上角红色关注 EXCEL精选技巧→点击技巧君的绿色头像→发送私信【身份证号查询】即可获取

      关注EXCEL精选技巧,每天学习3分钟,坚持一个月,你将大不同

      上一篇:win7怎么取消电脑自动休眠(电脑从不休眠,从不关闭显示屏怎样设置)

      下一篇:笔记本电脑尺寸(笔记本电脑尺寸有哪些)

win7怎么取消电脑自动休眠(电脑从不休眠,从不关闭显示屏怎样 excel表格自动生成时间(excel表格时间自动计算公式) win7专业版永久激活密钥(WIN7旗舰版系统激活的密钥在哪里可以找 电脑怎么压缩文件(电脑如何压缩文件) 网页证书风险怎么解决(只要登陆https开头的网站就显示证书风险 ps怎么把图片上的字去掉背景不变(怎样用ps快速将图片中的文字 电话呼叫转移怎么取消(换个手机怎么取消来电转接) 电脑屏幕倒过来了怎么复原(两个显示屏的电脑桌副屏倒过来了怎 c盘垃圾太多怎么清理(怎么从c盘彻底删除) 点怎么打出来(手机通话记录在营业厅能打出来吗)