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、单击【出错警告】标签，输入【标题】和【错误信息】并【确定】。