每日Excel分享（技巧）| 这个身份证号码是真是假？让Excel告诉你

前言

身份证是每一位中国公民的身份编号，编码是有一定规律的，村长今天在这里跟大家介绍如何用Excel校验身份证号码的真伪，下图是第二代居民身份证号码的校验码计算模型（今天的文章不考虑15位的旧身份证号码）

接下来村长会跟大家介绍一下如何分步校验计算，并且如何识别和禁止错误身份证号码的录入。

首先我们假定A2单元格有一个身份证号码

第1步、将身份证号码前面17位数字分别提取出来，公式如下

MID(A2,ROW(INDIRECT('1:17')),1)

第2步、获取2^17，2^16，2^15.....，2^2，2^1，公式如下： 

2^(18-ROW(INDIRECT('1:17')))

第3步、将身份证号码前面17位数字分别乘以2^17，2^16......然后相加，公式如下：

SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17'))))

PS：也就是第1步乘以第2步得到的值相加

第4步、将第3步得到的结果除以11然后求余数，得到的结果 1，公式如下：

MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17')))),11) 1

第5步、用MID函数从'10X98765432'里面根据第4步得到的位置截取1个字符，公式如下

MID('10X98765432',MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17')))),11) 1,1)

这一步得到的这个字符就是我们身份证号码的最后一位，如果不是，则表示这个身份证号码是假的。

第6步、判断第5步取得的字符是否与身份证号码的最后一位相等，公式如下

=MID('10X98765432',MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17')))),11) 1,1)=RIGHT(A2)

结果为TRUE表示相等，身份证符合校验规则，为真；

结果为FALSE表示不相等，身份证不符合校验规则，为假。

条件格式标识不合格身份证

我的Excel

使用公式确定条件格式，操作步骤如下图所示：

公式如下：

=MID('10X98765432',MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17')))),11) 1,1)<>RIGHT(A2)

数据有效性禁止不合格身份证

我的Excel

使用公式确定数据有效性，操作步骤如下图所示：

公式如下：

=MID('10X98765432',MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT('1:17')),1)*2^(18-ROW(INDIRECT('1:17')))),11) 1,1)=RIGHT(A2)

各位亲，村长我计划在网上开一期数组公式入门的免费课程，不知道有多少人愿意听，如果大家感兴趣可以在文章最后留言报名并且留下你的QQ号码，满20人开课，免费教学，包教包会，不会教到会为止！