Excel身份证如何提取和计算？请看这四类操作技巧

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

      关于身份证及其号码，大家都不陌生，而且也都知道身份证号码中包含出生年月、性别等信息，但是如何提取和计算，并不是每位亲都掌握的。今天，小编就带大家了解身份证号码中的这些秘密。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

一、身份证号码解读。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

从上图中可以看出，每个人的身份证号码由18位组成，其中：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1-2位：代表省。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

3-4位：代表市。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

5-6位：代表区。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

7-14位：代表出生年月。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

15-17位：代表顺序码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

18位：为识别码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

我们今天重点要讨论的是如何提取出生年月、判断性别、计算年龄、防止重复、判别身份证号码的正确性等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

二、技巧解读。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

（一）、提取出生年月。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

函数：Text+Mid。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

功能及语法结构：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

Text：将指定的值设置为指定的指定的形式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=Text(值或单元格引用,格式代码)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

Mid：从指定字符串的指定位置提取指定长度的值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=Mid(字符串,开始位置,字符长度)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

目的：提取身份证号码中的出生年月，并设置为日期格式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

在目标单元格中输入公式：=TEXT(MID(F3,7,8),"00-00-00")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

解读：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

首先利用Mid函数提取F3单元格中从第7位开始，长度为8的8位数字，然后用Text函数将其设置为日期格式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

（二）判断性别。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

函数：IF+Mod+Mid。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

功能及语法结构：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

IF：判断是否满足某个条件，如果满足指定条件，返回一个值，否则返回另一个值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=IF(判断条件,条件成立时的返回值,条件不成立时的返回值)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

Mod：返回两个数相除的余数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=Mod(被除数,除数)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

目的：根据身份证号码判断相应的性别。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

在目标单元格中输入公式：=IF(MOD(MID(F3,17,1),2),"男","女")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

解读：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1、在身份证号中，第17位代表性别，如果为奇数，则为男，如果为偶数，则为女。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

2、公式中首先用Mid函数提取指定身份证号码（F3）中的第17位，作为Mod函数的被除数，然后用Mod函数计算余数，并将余数返回给IF函数的第一个参数，然后用IF函数判断，如果为奇数，则返回“男”，如果为偶数，则返回“女”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

（三）计算年龄。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

函数：Datedif。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

功能：以指定的方式统计两个日期之间的差值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=Datedif(开始日期,终止日期,统计方式)。常见的“统计方式”有“Y”、“M”、“D”三种，即“年”、“月”、“日”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

目的：根据身份证号码计算对应的年龄。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

在目标单元格中输入公式：=DATEDIF(TEXT(MID(F3,7,8),"00-00-00"),TODAY(),"y")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

解读：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1、如果数据表中没有出生年月，则可以用Text+Mid函数进行提取，如果有，则可以直接使用。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

2、结束日期用Today()函数替代，其目的在于保持年龄的自动更新。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

（四）查重及禁止重复录入文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1、常规（错误）做法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

函数：Countif。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

功能：计算指定区域中满足指定条件的单元格个数（单条件计数）。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

语法结构：=Countif(条件范围,条件)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

目的：判断身份证号码是否有重复值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

在目标单元格中输入公式：=IF(COUNTIF(F$3:F$12,F3)>1,"重复","")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

解读：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

从公式的执行结果中发现有4个人的身份证号码重复，但经过分析，发现并不重复，Why？公式错误？分析公式，并没有错误，其实根本的原因在于在Excel系统中，超过15位的数值全部按0存储。所以用=IF(COUNTIF(F$3:F$12,F3)>1,"重复","")判断的结果就会显示重复。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

2、正确做法。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

目的：判断身份证号码是否有重复值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

在目标单元格中输入公式：=IF(COUNTIF(F$3:F$12,F3&"*")>1,"重复","")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

解读：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1、对比两个公式，发现只是在新公式的对比条件中增加了一个*，但得到了正确的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

2、在Excel中，*被称为通配符，加上通配符的作用就是将当前的数字内容转换为文本，然后进行对比，从而得到正确的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

3、禁止重复录入。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

1、选定目标单元格区域。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

2、【数据】-【数据验证】，打开【数据验证】对话框。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

3、选择【允许】中的【自定义】，在【公式】中输入：=COUNTIF(F$3:F$12,F3&"*")=1。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html

4、单击【出错警告】标签，输入【标题】和【错误信息】并【确定】。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/34676.html