Excel案例分享:用函数公式检查身份证号的真实性(Excel检查身份证号的真实性)。

在之前的文章《excel案例分享:只靠“拖”的五个高效技巧》中，我们学习了excel表格只靠“拖”的五个高效技巧。今天就来说说excel测试身份证号真实性的函数公式。来看看吧！

说起假疫苗、毒奶粉这种骇人听闻的事情，我们真的很气愤，也很无奈。作为普通人，只能希望有关部门加强管理检查，杜绝此类恶性事件再次发生。同时对自己的工作也要更加细心，在很多容易出错的环节反复检查，避免一些不好的后果，比如员工档案里的身份证号就是一个易发灾害的区域！

今天给大家分享一个检查身份证号是否有错的公式。

一般来说，身份证号的错误有两种，长度错误和内容错误。

大多数错误的原因发生在输入过程中。对于长度误差，目前都是18位数字。如果长度错了，就比较容易发现，但是如果其中一个数字错了，就很难发现，以后可能会造成一些严重的后果。所以今天的重点是检查内容的正确性。

一、身份证号码的组成原则

首先，你需要了解一点身份证号码的编码规则:

18位身份证号码的组成:ddddddyyyymmddxxsp共有18位，其中:6位是区号，7-14位是出生日期，117位是性别代码，最后一位是根据前面17位得到的校验位。

奇偶校验位的计算规则很复杂:

(1)前十七个数字本体码的加权求和公式。

S = Sum(Ai * Wi)，i = 0，...，16，先把前17位的权重相加。

Ai:表示I位置的ID号的数值。

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-108267-9-1082。

2.将这17位数字与系数相乘的结果相加。

3.11相加除以11的余数是多少？

4.余数只能有11个数字:0246-7-8-9-10。最后对应的身份证号码是0-X-9-8-7-642。

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(右(A2)= MID(& quot；10X98765432 & quot，MOD(SUM(MID(A2，ROW(INDIRECT(& quot；1:17 & quot；)),1)*2^(18-row(indirect(&quot；1:17 & quot；))))，11)+1，1)，& quot真的“，& quotFalse &quot

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

公式看起来很复杂。使用时，只需将两个A2号修改到自己表中ID号对应的位置即可。

先简单说一下公式的思路。核心部分是

MID(& quot；10X98765432 & quot，MOD(SUM(MID(A2，ROW(INDIRECT(& quot；1:17 & quot；)),1)*2^(18-row(indirect(&quot；1:17 & quot；)))),11)+1,1)

这个字符串的作用是根据校验码的规则计算前17位，然后与RIGHT(A2)进行比较。RIGHT(A2)的作用是从A2中提取最右边的数字，根据比较结果使用if函数进行判断。

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

结果是假的。

你不妨用这个公式来检查你的表格中是否有错误的身份证号码，及时更正错误的号码，把问题扼杀在萌芽状态！

相关学习推荐:excel教程

WPS Office WPS Office是一站式办公服务平台，让你高效解决办公问题，无需广告。WPS Office可以实现办公软件最常用的功能，如文本、表格、演示、PDF阅读等。请来拯救下载体验！

下载

这几篇文章你可能也喜欢：

• Excel图表学习如何使用控件制作条形图(关于使用Excel图表控件的教程)
• Excel案例分享:同比增长率和环比增长率(环比增长)的计算方法
• excel如何将数据合并到单元格中(Excel如何将数据合并到单元格中)
• Excel子弹图反映Excel图表学习中现实与目标的差异
• Excel案例共享:利用幂查询实现多表合并

本文由主机参考刊发，转载请注明：Excel案例分享:用函数公式检查身份证号的真实性(Excel检查身份证号的真实性)。 https://zhujicankao.com/120678.html