Excel案例分享：利用函数公式来检验身份证号码的真假 excel输入身份证号码

在之前的文章《Excel案例分享：5个只靠“拖拉”就实现的高效技巧》中，我们学习了5个Excel表格只靠“拖拉”就实现的高效技巧。而今天我们聊聊Excel检验身份证号码真假的函数公式，快来看看！

说起假疫苗、毒奶粉这些骇人听闻的事情，真的是愤怒加无奈，对于这类事情，作为普通人的我们，只能希望有关部门能够加大管理检查力度，杜绝再次发生这样的恶性事件。同时对于自己的工作也要更加细心，在很多容易出现错误的环节反复核查，避免产生一些不良的后果，比如员工档案中的身份证号码就是一个非常容易发生问题的重灾区！

今天我们就来和大家分享一个核查身份证号码是否有错误的公式。

一般来说，身份证号码会出现两类错误，长度错误和内容错误。

出现错误的原因大多数都输入过程中发生的，对于长度错误来说，目前都是18位的号码，如果长度出现错误，比较容易发现，但是如果其中的某一个数字出现错误，那就非常难以发现，以后就有可能会造成一些严重的后果，因此，今天重点就是核查内容的正确性。

一、身份证号组成原理

首先需要了解一点关于身份证号码的编码规则：

18位身份证号码组成：ddddddyyyymmddxxsp共18位，其中：1-6位是地区编码，7-14为出生日期，15-17这三位是性别代码，最后一位是根据前面的17个数字得到的校验位。

校验位的计算规则比较复杂：

（1）前十七位数字本体码加权求和公式

S = Sum(Ai * Wi), i = 0, … , 16 ，先对前17位数字的权求和

Ai:表示第i位置上的身份证号码数字值

Wi:表示第i位置上的加权因子

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

（2）计算模

Y = mod(S, 11)

（3）通过模得到对应的校验码

Y: 0 1 2 3 4 5 6 7 8 9 10

校验码: 1 0 X 9 8 7 6 5 4 3 2

要检验一个身份证号码是否有误，原理就是按照这个规则计算出校验码，再与最后一位数字去比较，如果不一致就是有问题了。对于这个规则理解起来还是比较费劲的，我们举个例子来看看：

1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为：

7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。

2、将这17位数字和系数相乘的结果相加。

3、用加出来和除以11，看余数是多少？

4、余数只可能有0－1－2－3－4－5－6－7－8－9－10这11个数字。其分别对应的最后一位身份证的号码为1－0－X －9－8－7－6－5－4－3－2。

5、通过校验码对比得知如果余数是3，就会在身份证的第18位数字上出现的是9。如果对应的数字是10，身份证的最后一位号码就是罗马数字2。

例如：某男性的身份证号码为【53010219200508011x】， 我们看看这个身份证是不是合法的身份证。

首先我们得出前17位的乘积和

【(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+(2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8*5)+(0*8)+(1*4)+(1*2)】

是189，然后用189除以11得出的结果是189/11=17—-2，也就是说其余数是2。最后通过对应规则就可以知道余数2对应的检验码是X，所以，可以判定这是一个正确的身份证号码。

二、Excel检验身份证号码真假

原理明白了，现在的问题就是如何将这个计算过程用Excel的公式来表示出来，在这里将公式分享给大家：

=IF(RIGHT(A2)=MID(“10X98765432”,MOD(SUM(MID(A2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17″)))),11)+1,1),”真”,”假”)

注意这是个数组公式，输入或者粘贴公式后，需要同时按住Ctrl和shift键再回车。

公式看上去非常复杂，使用的时候只需要把两个A2修改为自己表格中身份证号对应的位置即可。

简单来说一下公式的思路吧，核心部分就是

MID(“10X98765432”,MOD(SUM(MID(A2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1)

这一串，作用是将前面的17位数字按照校验码的规则计算出来，再与RIGHT(A2)做比较，RIGHT(A2)的作用就是从A2中提取最右边的一位出来，根据比较结果用if函数来进行判断。

我们可以再用这个公式来检验一下文章开头图片中的身份证号码是否正确：

结果是假。

不妨用这个公式去检验一下你的表格中是否有错误的身份证号码吧，对于判定为错误的号码及时进行更正，将问题扼杀在萌芽阶段！