如何将Excel函数运用到身份证的查询上
发布时间:2023-12-11 21:30:16
对于身份证大家肯定不会陌生,在Excel中经常需要根据身份证号码来提取一些信息,或者进行判断。光靠眼力去看,显然不够效率。以下是小编为您带来的关于Excel函数身份证的查询,希望对您有所帮助。
Excel函数身份证的查询
类型格式
15位身份证:前2位表示所属省份代码,3~6位表示所属城市和区县代码,7~12表示出生日期,格式为YYMMDD,13~15位是个人顺序码,其中第15位可以标识性别,为奇数表示男性,为偶数表示女性。
18位身份证:前2位表示所属省份代码,3~6位表示所属城市和区县代码,7~14表示出生日期,格式为YYYYMMDD,15~17位是个人顺序码,其中第17位可以标识性别,为奇数表示男性,为偶数表示女性。第18位是校验位,由前17位通过计算求得。
正确输入
因为Excel单元格只支持15位有效数字,输入多了就不能完全显示。这样就导致新一代身份证号码输入时不便,为了解决这个问题,我们可以采取文本输入的方法,可以在输入号码之前,先添加一个半角的单引号再输入其他数字,这样完成输入的结果就是一个文本型数据。或者也可以在输入之前事先将单元格格式设置为文本再行输入。但是如果在输入完成以后再更改单元格格式就不会有效果。
自动验证输入
①条件1:输入长度为15位或18位,函数公式可以这样写:=OR(LEN(A1)=15,LEN(A1)=18)
②条件2:前17位必须都是数字,公式:=ISNUMBER(-LEFT(A1,17))
③条件3:如果不全都是数字,那么它只能是18位,并且末尾字符是字母“X”,公式:=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))
提取生日
①提取6位或8位生日数字,=MID(A1,7,IF(LEN(A1)=15,6,8))
②对于15位号码,需要补足前面两位“19”数字,=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)
③将上面得到的8位数字转换成真实日期数值,=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0
计算年龄
年龄的计算实际上就是通过前面得到的出生日期来用DATEDIF函数计算到当前所相差的年份数(周岁):
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")
判断性别
①提取数字:=MID(A1,15,3),对于15位身份证号码,上述公式提取到是其末位数字,不包含其他字符占位。而对于18位的身份证号码,上述公式提取到的是其15~17位数字。
②判断奇偶性:=IF(MOD(MID(A1,15,3),2), "男","女")。通过MOD函数除以2取余数来进行奇偶判断,如果余数为1,表示奇数,得到男性判断,如果余数为0,得到女性判断。
上面大篇幅的介绍了15位和18位身份证号码的函数公式运用,自从2013年1月1日开始,第一代身份证已经不允许属于,这样我们函数公式也能够简化:
验证输入:=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),RIGHT(A1)="X"))
提取生日:=TEXT(MID(A1,7,8),"0-00-00")+0
计算年龄:=DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),NOW(),"Y")
判别性别:=IF(-1^MID(A1,15,3)=1,"女","男")


猜你喜欢
- 在Excel中,有时需要在某个单元格中输入一个对角线,表示该单元格中没有数据或不需要数据,如图: &nbs
- 微博、微信、QQ是大家手机上必备的软件之一,微博让大家足不出户便知天下事,有时候我们看到一个视频很喜欢,想要保存下来,分享给小伙伴知道,但是
- 本文教你如何破坏Microsoft Word文档,使其无法打开。步骤方法 1使用在线文件破坏器在网页浏览器中前往 htt
- PPT模板制作错误,可以撤销返回上一操作效果。默认情况下这个撤销次数是20次。如何重新设置撤销次数呢?1、在PPT上点击【文件】,再点击【选
- 对于经常使用Word进行办公的朋友来说,启动Word之后,第一件要做的事应该就是单击菜单“文件→打开”命令,调出“打开”对话框,再浏览找到需
- 对excel中的常用函数熟练掌握,给我们的统计分析工作带来很大的便捷,在excel的if函数如何做成绩判断?今天,小编就教大家在Excel中
- Excel中单元格的字符对齐方式,大体上可以分为:左对齐、右对齐、居中、顶端对齐、底端对齐及垂直居中等。默认情况下,在Excel2007中,
- 在Excel众多的功能中,填充数据的运用只是其中很小的一块,对于这个朋友们都经常使用到的功能,它到底能为大家的出色工作加分多少?今天,小编就
- 总的来说用户在wps软件中可以用来编辑各种各样的文件,在wps软件中有着其它热门办公软件强大的功能支持,所以用户可以编辑文档、表格等问题,其
- Intel5000元电脑也就是小编最近打算配置的主机了,也是目前2000年主流装机市场的新宠,今天小编主要给大家带来的Intel5000元电
- 说到aero效果很多用户都会懵逼,这是什么东西,其实这是Win7系统上透明的毛玻璃效果,让win7的界面变得更加美观,近来有用户反馈aero
- 在使用电脑的时候很多的用户会需要使用到索引服务,但是不知道windows search该怎么打开,今天就给你们带来了windows sear
- 大家都知道,当我们登录网银等网站或QQ时,登录之后会显示登录次数及登录时间等信息,这对于账户安全很有利。如果我们能让登录Windows10时
- 本文为大家分享Illustrator教程学习者使用AI混合变形工具3步做出欧普艺术效果的方法,教程非常简单,只需要三步就给一做出欧普艺术风格
- 隔空投送,是什么?利用“隔空投送”,您可以通过无线方式将文稿、照片、视频、网站、地图位置等发送到附近的 iPhone、iPad、iPod t
- 据系统部落小编了解 就在今天,微软公司针对Windows11预览版再次发布了一次更新,更新补丁号为:KB5008918。KB5008918补
- 惠普星13 Air锐龙版是目前惠普最为轻薄的消费类笔记本电脑,采用了镁铝合金材质,重量仅有1kg,对于校园学习,或者是经常出差、移动办公,惠
- Win10最新功能大盘点啦!随着Win10版本的不断更新,功能也越来越先进,可能很多用户更新了版本,却不知道新版本增添的功能。如果没有享用到
- 关于PowerPoint的问题很少谈论,但这并不意味着该程序没有问题。一些用户陷入了一个与PowerPoint有关的特定问题– C
- 这是前几天一位朋友遇到的一个问题,要对彩/票做一些预测,他从网上导了每一期的开奖数据下来,格式为TXT类型的,存放在一个文件夹里, 需要vb