Excel中身份证号录入正确性校验公式

很多办公室工作人员在工作中总会遇到人员身份证号录入的情况，由于身份证号位数比较多，所以在录入的过程中难免会有录入错误的时候，如果需要录入的身份证号比较多，那么核对身份证号是否正确就是一项比较繁琐的事情。因此今天特意向大家分享一个检验输入的身份证号编码是否正确的公式，帮助大家在录入身份证号时保证录入的正确性。

假如身份证号录入在D1单元格，公式为：

=IF(A1="","",IF(LEN(A1)<>18,"身份证号错误,位数不是18位",IF(OR(MID(A1,7,4)*1<1843,MID(A1,7,4)*1>2123),"身份证号出生日期年份错误",IF(OR(MID(A1,11,2)*1>12,MID(A1,11,2)*1=0),"身份证号出生日期月份错误",IF(OR(MID(A1,13,2)*1>31,MID(A1,13,2)*1=0),"身份证号出生日期天数错误",IF(CHOOSE(MOD(SUMPRODUCT(MID(A1,ROW($1:$17),1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,"1","0","X","9","8","7","6","5","4","3","2")=RIGHT(A1,1),"身份证正确","身份证校验位错误"))))))

使用如下：（公式太长不再直接输入，直接复制到单元格里）

公式比较长，如果不想花时间理解公式原理的朋友，请直接收藏本文，需要用时复制到表中，将身份证号所在的单元格对应改一下即可用。如果想了解公式编写原理的请接着往下看。

编写校验身份证号正确与否的函数公式用到了身份证号编码的以下几点规则：

一、现在使用的身份证号长度均为18位数；所以公式中用if函数和len函数嵌套判断录入的身份证号位数是否为18，如果不是18位，肯定录入有错。

二、中间第7位到第14位为代表出生日期的编码；这里嵌套了三个if+or+mid函数，分别取出代表生日的年、月、日各自进行一个简单判断，判断年时设定年分为1843至2123之间为正确，超出判断为不正确，这个可根据自己的要求自行设定；当判断月时，当月为01至12之间的数字为正确，否则此时的身份证号肯定录入有错；当判断天数时，当天数为01至31之间的数字时为正确，否则此时的身份证号肯定录入有错。

三、最后一位为身份证号的校验位。而校验位的计算规则为分别取身份证号的1到17位对应乘以它们各自的一个系数，这17个系数分别为7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2，将对应相乘所得到的17个积相加起来，将加出来的和再除以11，并取除11后得到的余数。余数的可能性只能是0,1,2,3,4,5,6,7,8,9,10这11个数，而这11个数就对应身份证号的第18位校验位，分别为：1,0,X,9,8,7,6,5,4,3,2。

根据这个计算规则，我们用MID(A1,ROW($1:$17),1)*1先将身份证号的前17位分别取出并转换为数值型，再通过SUMPRODUCT(MID(A1,ROW($1:$17),1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})部分计算身份证号前17位乘以各自系数后相加的和。然后嵌套mod函数获取相加后的和与11的余数，最后通过CHOOSE函数取余数对应的校验位。将通过CHOOSE取到的校验位和录入的身份证号的最后一位作比较，如不等，则所录入的身份证号一定错误！

以上是公式的编写的一个基本思路，当然还可以根据身份证号的其它特点进行更精准的校验，但那样公式会更长更复杂，个人觉得没有必要，能精确验证到这一步，已经能检测出绝大多的录入错误了！

本文内容就到这里，文中如有错误之处，欢迎批评指正，如果本文对你有那么一点帮助，那请给个关注+点赞，有你的认可才有我继续分享的动力！