身份证号码的处理是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)))/365

TODAY获取当前日期，DATE获取身份证上的出生日期，两者相减后除以365，如要取整，一般用INT函数直接舍弃小数部分。

出生日期码计算年龄

顺序码判断性别

第十五位到第十七位：表示在同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。

MID提取第十七位后，至少有三个函数可以判断其奇偶性：MOD,ISODD,ISEVEN，根据奇偶输出男女则可以用IF或CHOOSE.

=CHOOSE(ISODD(MID(A2,17,1))+1,"女","男")

顺序码判断性别

校验码

第十八位根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。计算规则可大致归纳为三步。

  • 第一步：

你需要了解的是，计算规则中给第1~17位每一位都分配了一个对应的系数，这个系数是固定不变的。

从第一位到第十七位的系数分别为：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组信息

身份证操作汇总