Excel中,关于身份证的四类操作技巧,再不掌握就Out了!
关于身份证及其号码,大家都不陌生,而且也都知道身份证号码中包含出生年月、性别等信息,但是如何提取和计算,并不是每位亲都掌握的。今天,小编就带大家了解身份证号码中的这些秘密。
一、身份证号码解读。
从上图中可以看出,每个人的身份证号码由18位组成,其中:
1-2位:代表省。
3-4位:代表市。
5-6位:代表区。
7-14位:代表出生年月。
15-17位:代表顺序码。
18位:为识别码。
我们今天重点要讨论的是如何提取出生年月、判断性别、计算年龄、防止重复、判别身份证号码的正确性等。
二、技巧解读。
(一)、提取出生年月。
函数:Text+Mid。
功能及语法结构:
Text:将指定的值设置为指定的指定的形式。
语法结构:=Text(值或单元格引用,格式代码)。
Mid:从指定字符串的指定位置提取指定长度的值。
语法结构:=Mid(字符串,开始位置,字符长度)。
目的:提取身份证号码中的出生年月,并设置为日期格式。
方法:
在目标单元格中输入公式:=TEXT(MID(F3,7,8),'00-00-00')。
解读:
首先利用Mid函数提取F3单元格中从第7位开始,长度为8的8位数字,然后用Text函数将其设置为日期格式。
(二)判断性别。
函数:IF+Mod+Mid。
功能及语法结构:
IF:判断是否满足某个条件,如果满足指定条件,返回一个值,否则返回另一个值。
语法结构:=IF(判断条件,条件成立时的返回值,条件不成立时的返回值)。
Mod:返回两个数相除的余数。
语法结构:=Mod(被除数,除数)。
目的:根据身份证号码判断相应的性别。
方法:
在目标单元格中输入公式:=IF(MOD(MID(F3,17,1),2),'男','女')。
解读:
1、在身份证号中,第17位代表性别,如果为奇数,则为男,如果为偶数,则为女。
2、公式中首先用Mid函数提取指定身份证号码(F3)中的第17位,作为Mod函数的被除数,然后用Mod函数计算余数,并将余数返回给IF函数的第一个参数,然后用IF函数判断,如果为奇数,则返回“男”,如果为偶数,则返回“女”。
(三)计算年龄。
函数:Datedif。
功能:以指定的方式统计两个日期之间的差值。
语法结构:=Datedif(开始日期,终止日期,统计方式)。常见的“统计方式”有“Y”、“M”、“D”三种,即“年”、“月”、“日”。
目的:根据身份证号码计算对应的年龄。
方法:
在目标单元格中输入公式:=DATEDIF(TEXT(MID(F3,7,8),'00-00-00'),TODAY(),'y')。
解读:
1、如果数据表中没有出生年月,则可以用Text+Mid函数进行提取,如果有,则可以直接使用。
2、结束日期用Today()函数替代,其目的在于保持年龄的自动更新。
(四)查重及禁止重复录入
1、常规(错误)做法。
函数:Countif。
功能:计算指定区域中满足指定条件的单元格个数(单条件计数)。
语法结构:=Countif(条件范围,条件)。
目的:判断身份证号码是否有重复值。
方法:
在目标单元格中输入公式:=IF(COUNTIF(F$3:F$12,F3)>1,'重复','')。
解读:
从公式的执行结果中发现有4个人的身份证号码重复,但经过分析,发现并不重复,Why?公式错误?分析公式,并没有错误,其实根本的原因在于在Excel系统中,超过15位的数值全部按0存储。所以用=IF(COUNTIF(F$3:F$12,F3)>1,'重复','')判断的结果就会显示重复。
2、正确做法。
目的:判断身份证号码是否有重复值。
方法:
在目标单元格中输入公式:=IF(COUNTIF(F$3:F$12,F3&'*')>1,'重复','')。
解读:
1、对比两个公式,发现只是在新公式的对比条件中增加了一个*,但得到了正确的结果。
2、在Excel中,*被称为通配符,加上通配符的作用就是将当前的数字内容转换为文本,然后进行对比,从而得到正确的结果。
3、禁止重复录入。
方法:
1、选定目标单元格区域。
2、【数据】-【数据验证】,打开【数据验证】对话框。
3、选择【允许】中的【自定义】,在【公式】中输入:=COUNTIF(F$3:F$12,F3&'*')=1。
4、单击【出错警告】标签,输入【标题】和【错误信息】并【确定】。