excel从身份证号码提取年龄周岁(excel从身份证号码提取年龄)
Excel干货:身份证号提取生日、年龄、性别、籍贯,建议收藏!
咱们每个人都有一个唯一的身份证号,其实身份证号中包含了很多的个人信息,你知道怎么从身份证号中提取个人的生日、年龄、性别、籍贯等信息吗?技巧君今天和大家分享一下如何利用Excel从身份证号中提取个人信息!
文末有示例文件获取方法哦身份证号的构成身份证号有18位,排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。顺序码的奇数分给男性,偶数分给女性。
身份证号构成
从这个构成中,我们可以发现,只需要提取对应的编码就可以提取出身份证号中的个人信息!
提取性别=IF(MOD(MID(B2,17,1),2),\"男\",\"女\")
身份证号提取性别
说明:MID(B2,17,1),提取第17位数字MOD(MID(B2,17,1),2),对2取余,奇数余数得1,偶数余数为0提取出生年月
=--TEXT(MID(B2,7,8),\"0-00-00\")
提取出身年月
说明:MID(B2,7,8),提取第7位开始后面的8位数字,即出生年月日编码TEXT(MID(B2,7,8),\"0-00-00\"),转换为年-月-日的格式--TEXT(MID(B2,7,8),\"0-00-00\"),减负运算换算为真正的日期提取实岁
=DATEDIF(TEXT(MID(B2,7,8),\"0-00-00\"),TODAY(),\"Y\")
提取实岁
说明:DATEDIF(TEXT(MID(B2,7,8),\"0-00-00\"),TODAY(),\"Y\"),计算当前日期和出生日期的年差值,即实际岁数提取虚岁
=(YEAR(NOW())-MID(B2,7,4))
提取虚岁
说明:MID(B2,7,4):提取第7位数开始的4位数字,即出生年份YEAR(NOW()):计算当前日期的年份(YEAR(NOW())-MID(B2,7,4)):虚岁=当前年份-出生年份提取籍贯
=VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0)
提取籍贯
说明:--MID(B2,1,6),提取前六位地址编码VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0),在地址编码表里找到籍贯示例文件获取:
点击右上角红色按钮关注“Excel精选技巧”,然后私信发送【身份证号提取个人信息】即可
关注Excel精选技巧,每天学习3分钟,坚持一个月,你将大不同!Excel——根据身份证号码精准计算实际年龄的方法
一、任务需求:根据员工信息表中的身份证号码,精确计算每位员工的实际年龄。
根据身份证号码精准计算实际年龄
二、任务难点:
1、数据量大,无法单个员工计算。
2、需要计算的是员工实际的年龄(即30岁差一天也只能算是29岁),不是虚岁。
三、解决思路:
1、身份证内包含出生年月,可以提取出来。
2、用当前的时间减去出生年月可以得出实际的年龄。
四、具体方法:
(一)提取出生年月日
身份证号码的第7-14位是个人的出生年月日,利用MID()函数可以把出生年月日从身份证号码里提取出来。
MID()函数解析:
MID()函数
作用:从字符串中提取一定长度的字符串。
参数解析:
1.Text:指定所要提取的字符串。
2.Star_num:准备提取的第一个字符的位置,按从左到右的顺序由1开始计算。
3.Num_chars:指定所要提取的字符串的长度。
本例在“提取出生年月日”列下第一行(C2)单元格中输入公式=MID(B2,7,8),然后向下填充即可将所有人的出生年月日从身份证号码中提取出来。(B2是商鞅的身份证号码,从第7位开始,提取8位)
提取出生年月日
(二)转化为日期格式
由MID()函数提取出来的出生年月日是字符串型数据,需要利用TEXT()函数转为Excel能识别的日期格式。
TEXT()函数解析:
TEXT()函数
作用:将数值转换为按指定数字格式。
参数解析:
1.Value:要转化的数据。
2.Format_text:想要转化的数值格式。
如下为TEXT()函数常用的一些格式:
TEXT()函数常用格式
本例在“转化为日期格式”列下第一行(D2)单元格内添加公式=TEXT(C2,"0000-00-00"),然后向下填充即可将“出生年月日”转化为日期格式。(C2是商鞅的出身年月日,"0000-00-00"是日期的格式)
将提取的“出生年月日”转为“日期格式”
(三)获取当前系统日期
为增加表格的灵活性,使用TODAY()函数获取当前系统日期。
TODAY()函数解析:该函数是没有参数的可以直接使用。
TODAY()函数
本例在“获取当前系统日期”列下第一行(E2)单元格内添加公式= TODAY(),然后向下填充即可获取到当前系统日期。
获取当前系统日期
(四)计算实际年龄
实际年龄等于当前系统时间和出身时间的年份差,利用DATEDIF()函数可以计算出两个日期之间的年份差。
DATEDIF(start_date,end_date,unit)函数解析:
作用:Excel隐藏函数,插入公式里面没有。可以返回两个日期之间的年\月\日间隔数,常使用DATEDIF()函数计算两日期之差。
参数解析:
1.Start_date:起始日期(必须是1900年之后)
2.End_date:结束日期(结束日期必须大于起始日期)
3.Unit:返回的日期类型,即要计算的是年、月、日中的哪一个的差,具体类型如下:
“Y”计算年数差;
“M”计算月数差;
“D”计算天数差。
本例在“年龄”列第一个单元格中输入公式:=DATEDIF(D2,E2,"Y"),然后向下填充即可计算出实际的年龄。(D2是商鞅的出生年月日,E2是当前系统日期,“Y”表示计算年数差)
五、拓展创新
在实际中,以上方法步骤过多,不太方便。可以将所有的公式合并为一条公式:=DATEDIF(TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y"),迅速计算出实际的年龄。
以上就是根据身份证号码精准计算实际年龄的方法,总结如下:
1、利用MID()函数从身份证号码中提取出生年月日。
2、利用TEXT()函数把提取的出生年月日转为日期格式。
3、利用TODAY()函数获取到当前系统日期。
4、利用DATEDIF()函数计算出当前系统日期和出生日期的年份差(即实际年龄)。
该计算思路和公式能让工作效率大大提高,把困难的问题变得简单轻松,你学“废”了吗?
我是今日Excel与编程,想了解更多的Excel与编程知识请关注我
声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送至邮件举报,一经查实,本站将立刻删除。转载务必注明出处:http://www.hixs.net/article/20240122/169625640794161.html