打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
身份证号码中原来隐藏着这么多的秘密,你知道怎么处理吗?

        每个人都有一张身份证,身份证号码中不仅包含了出生年月,还有性别等信息……如果给你几千条或几万条人员信息,你能快速的从身份证号码中提取出生年月,性别等信息吗?


一、从身份证号码中提取出生年月。

方法:

在目标单元格中输入公式:=TEXT(MID(C3,7,8),'00-00-00')。

解读:

1、MId函数的作用为:从文本字符传中指定的起始位置返回指定长度的字符串。语法结构为:=Mid(字符串,起始位置,长度)。

2、Text函数的作用为:根据制定的数值格式将数值转换为文本。

3、公式:=TEXT(MID(C3,7,8),'00-00-00')中,首先用Mid函数提取C3单元格中从第7位开始,长度为8的字符,然后用Text函数设置成格式为“00-00-00”(暨年月日)的文本。


二、从身份证号码中提取性别。

方法:

在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),'男','女')。

解读:

1、Mod函数的作用为:返回两数相除的余数。语法结构为:=Mod(被除数,除数),例如=Mod(6,2),返回值为0;=Mod(3,2),返回值为1。

2、If函数的作用为:判断条件是否成立,如果成立返回一个值,否则返回另外一个值。

3、公式:=IF(MOD(MID(C3,17,1),2),'男','女')中,首先利用Mid函数提取C3单元格中字符串的第17位,然后除以2,返回余数(暨Mod函数的作用,其返回的结构只有0和1两种),最后用If函数判断余数,如果为“1”,返回“男”,如果为“0”,返回“女”。


三、从身份证号码中提取年龄

方法:

在目标单元格中输入公式:=DATEDIF(D3,TODAY(),'y')。

解读:

1、Datedif函数是系统隐藏函数,其作用为按照指定的类型计算两个日期之间的差值。语法结构为:=Datedif(开始日期,结束日期,统计方式),常见的“统计方式”有:“Y”、“M”、“D”;分别为“年”、“月”、“日”。

2、Today()函数的作用为获取当天的日期。


四、用身份证号码计算退休年龄。

方法:

在目标单元格中输入公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。

解读:


1、Edate函数的作用为返回指定日期之前或之后X月的日期。语法结构为:=Edate(指定日期,月份)。

2、公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)中,首先用判断性别,如果为“男”性,则公式为:=Edate(D3,1*120+600),暨从出生日期算起之后的720个月(暨60年)的日期;如果为“女性”,则公式为:=Edate(D3,0*120+600),暨从出生日期算起之后的600个月(暨50年)的日期。


五、判断身份证号是否重复。

1、常规操作(错误办法)。

方法:

在目标单元格中输入公式:=IF(COUNTIF($C$3:$C$9,C3)>1,'重复','')。

解读:

1、Countif函数是单条件计数函数,其语法结构为:=Countif(条件范围,条件)。

2、利用Countif函数统计出当前值在C3:C9范围内数量,紧接着和1进行比较,并用If函数来判断,如果比较的结果成立,则返回“重复”,否则返回空值。

3、从单元格值中可以看出,两个身份证号并不重复,但是返回的结果为“重复”,Why?因为在Excel中的数字的有效位数为15位,超过15位的全部按“0”处理。而上述的两个号码中只有最后两位不同,故被Excel作为“0”处理,因此判断的结果为“重复”。


2、正确做法。

方法:

在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3&'*')>1,'重复','')。

解读:

1、此公式和“常规操作”的公式比较,只是在C3后添加了&'*',但能得到正确的结果,Why?

2、“*”在Excel中被称为通配符,起作用就是将当前的数字强制转换为文本,然后进行比较,从而得到了正确的结果。


六、防止身份证号重复。

方法:

1、选定目标单元格。

2、【数据】-【数据验证】,选择【允许】中的【自定义】,并在【公式】中输入:=COUNTIF(C$3:C$9,C3)=1。

3、完善提示信息并【确定】。


结束语:
        通过本文的学习,相信大家对身份证号码中隐藏的秘密有了更多的了解,如果有不明白的地方,欢迎在留言区留言讨论哦!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
身份证号相关的6个函数公式都不掌握,还怎么成为Excel达人?
身份证号相关的6个函数公式实用技巧都不掌握,那就真的Out了!
原来身份证号码还隐藏着这些秘密!教你用Excel快速提取出来
15个excel常用函数,可直接套用,几乎每天都用得到,收藏备用吧
excel技巧:与身份证有关的5个Excel函数公式
EXCEL技巧四:利用身份证号码提取性别、年龄、出生年月并测算取暖费
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服