身份证号码的处理是Excel中很典型的应用场景 。
本文可以帮你解决以下问题：
- 如何正确输入身份证号
- 检查重复
- 查询出生地
- 提取出生日期
- 计算年龄
- 判断性别
- 验算校验码
认识身份证号码
18位号码都有其特定的含义，根据国家质量技术监督局发布的规则编制而成 。从前往后依次是：六位数字地址码、八位数字出生日期码、三位数字顺序码、校验码 。
身份证号码
正确输入身份证号
在Excel中直接输入身份证号无法正常显示 ， 原因是Excel将其当作数字处理，超过11位的数字以科学计数法显示 。身份证号码要以文本格式存在 ， 至少有两种方法 。
方法一：在身份证前加单引号”’”.
方法二：输入前先将单元格设置为文本格式 。
正确输入身份证号
检查重复
理论上身份证号码不会重复，但在过去人工编码的年代难免有错误，实际上存在重号的问题 。一次性的需求可以用数据透视或条件格式快速获取重复数据 。
条件格式标记重复
用公式查找重复时，COUNTIF是常用函数，需要注意一个细节：COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但是Excel中的数字精度只有15位，并且身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容 。
如下所示，前15位相同，后3位不同的数据，COUNTIF判定为相同 。
=COUNTIF(A2:A3,A2)
COUNTIF查找重复失败
正确的做法是在第二参数后加上一个星号 &'*'，把本型数字转换为文本 。再配合IF判断是否重复 。
=IF(COUNTIF($A$2:$A$12,A2&"*")>1,"重复","")
COUNFIF查找重复
地址码查询出生地
身份证号码前六位表示出生地编码，包含了省市县各级信息：第1、2位数字表示：所在省份的代码；
第3、4位数字表示：所在城市的代码；
第5、6位数字表示：所在区县的代码；
例如110105代表北京市朝阳区 。
根据身份证查询出生地，首先需要获取籍贯对照表 。
籍贯对照表
函数LEFT提取身份证的前六位作为查询条件，直接VLOOKUP即可：
=VLOOKUP(--LEFT(A2,6),籍贯对照表!A:D,4,0)
VLOOKUP查询籍贯
如需同时返回省份和市区信息，用XLOOKUP比较方便：
=XLOOKUP(--LEFT(A2,6),籍贯对照表!A:A,籍贯对照表!C:C&","&籍贯对照表!D:D)
XLOOKUP查询籍贯
出生日期码提取出生日期
身份证第七到十四位出生日期码，包含出生年月日 。第7、8、9、10位数字表示：出生年份；
第11、12位数字表示：出生月份；
第13、14位数字表示：出生日期 。
如”19870817”表示1987年8月17日 。
MID直接提取第7位开始的连续8个字符，即第7到14位 。
=MID(A2,7,8)
MID提取出生日期码
直接提取出来的文本格式并不是大众熟知的日期格式，可以用TEXT转换：
=TEXT(MID(A2,7,8),"0000!/00!/00")注意，TEXT第二参数的设置方式并不唯一，如
=--TEXT(MID(A2,7,8),"0-00-00")也可以完成转换 。
TEXT MID提取出生日期
也可以用DATE函数实现：
=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))三个MID分别提取年 ， 月，日数据作为DATE的三个参数 。
DATE MID提取出生日期
出生日期码计算年龄
已经获取了出生日期的基础上计算年龄就容易很多了：=(TODAY()-DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)))/365TODAY获取当前日期，DATE获取身份证上的出生日期，两者相减后除以365，如要取整 ， 一般用INT函数直接舍弃小数部分 。
出生日期码计算年龄
顺序码判断性别
第十五位到第十七位：表示在同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号 。其中第十七位奇数分给男性，偶数分给女性 。MID提取第十七位后，至少有三个函数可以判断其奇偶性：MOD,ISODD,ISEVEN，根据奇偶输出男女则可以用IF或CHOOSE.
=CHOOSE(ISODD(MID(A2,17,1)) 1,"女","男")
顺序码判断性别
校验码
第十八位根据前面十七位数字码 ， 按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码 。计算规则可大致归纳为三步 。- 第一步：
从第一位到第十七位的系数分别为：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 ；
用这个系数与身份证前17位两两相乘：
=C2*C3
17位系数
- 第二步：
=SUM(C5:S5)把17个乘积加总起来，得到222
=MOD(C6,11)加总所得的和除以11，取其余数，得到2
加总并取余数
- 第三步：
从余数转换规则可以看出，余数为0，校验位为1 ， 余数为1，校验位为0，当余数为2时，校验位为X.
余数转换
第4步：验算校验码
当你有了余数转换规则和17位的系数，再把上面的三个步骤整合起来形成一个公式 ， 就可以制作这样一个用于计算第18位值的小工具了:
=IF(XLOOKUP(MOD(SUM(MID(G2,ROW(1:17),1)*A2:A18),11),C3:C13,D3:D13)=RIGHT(G2,1),"正确","错误")
校验位验证
总结
身份证号码中至少可以得到如图所示的6组信息
【Excel中提取身份证信息的方法和原理，一次性说明白】身份证操作汇总