利用函数和公式校验身份证号正确错误

rainfarm

工作中统计工作中身份证号的重要性不言而喻，出生日期和性别都可以直接提取，然后现实中在录入中难免出现一定的错误，如何判定身份证号的正误显得尤为重要。利用网络上的验证校验码的方法判断，自己加了一定的修改，可以直接利用。

=IF((CHOOSE(MOD(SUM((MID(B2,1,1)+MID(B2,11,1))*7+(MID(B2,2,1)+MID(B2,12,1))*9+(MID(B2,3,1)+MID(B2,13,1))*10+(MID(B2,4,1)+MID(B2,14,1))*5+(MID(B2,5,1)+MID(B2,15,1))*8+(MID(B2,6,1)+MID(B2,16,1))*4+(MID(B2,7,1)+MID(B2,17,1))*2+MID(B2,8,1)+MID(B2,9,1)*6+MID(B2,10,1)*3),11)+1,"1","0","X","9","8","7","6","5","4","3","2"))=RIGHT(B2,1),"正确","错误")

具体在利用时利用记事本将公式中的B2替换为身份证实际所在单元格，其余的单元格拖动填充即可。


补充内容 (2020-10-31 21:04):
加indirect函数可以避免删除1到17行引起的错误
=if(MID("10X98765432",MOD(SUMPRODUCT(MID(A3,ROW(INDIRECT("1:17")),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1)=RIGHT(A3),"正确","错误")

kuangben8

30.8 限定输入身份证号码.zip (7.59 KB, 下载次数: 182)

2018-3-19 22:24 上传

点击文件名下载附件

来源：excel home

mengrs

可以简化
=IF((MID("10X98765432",MOD(SUMPRODUCT(MID(B2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1))=RIGHT(B2),"正确","错误")

rainfarm

kuangben8 发表于 2018-3-19 22:25
来源：excel home

谢谢高手大神们

rainfarm

mengrs 发表于 2018-3-19 22:48
可以简化
=IF((MID("10X98765432",MOD(SUMPRODUCT(MID(B2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5 ...

谢谢大神们

wbpy

mengrs 发表于 2018-3-19 22:48
可以简化
=IF((MID("10X98765432",MOD(SUMPRODUCT(MID(B2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5 ...

输入正确身份证号后，显示正确，但是在正确身份证号16位至18位之间插入数学，总长度为19位了，结果仍然显示为正确，该如何解决？

wbpy

kuangben8 发表于 2018-3-19 22:25
来源：excel home

复制粘贴的数据有误，并不会出现错误提示，如何解决？

kuangben8

wbpy 发表于 2019-9-22 12:50
复制粘贴的数据有误，并不会出现错误提示，如何解决？

上传附件，模拟数据，我们看看。

ggjj82

wbpy 发表于 2019-9-22 12:45
输入正确身份证号后，显示正确，但是在正确身份证号16位至18位之间插入数学，总长度为19位了，结果仍然显 ...

厉害了，根据你的提示，我试了一下，此公式确实有缺陷：1、只能验证身份证号码是18位的，对16位身份证号码无法验证；2、在第17位与第18位数字之间插入任意多个数字，身份证号码仍显示正确。不知大神们现在完善公式了没有？

补充内容 (2020-1-26 22:44):
不好意思，写错了，是15位身份证号码。验证15位身份证号码现在这个问题基本可以忽略了，现在都是二代身份证，都是18位的；另外可以加个if判断，if(len()<>18,"错误"，公式判断18位正误）

ggjj82

wbpy 发表于 2019-9-22 12:45
输入正确身份证号后，显示正确，但是在正确身份证号16位至18位之间插入数学，总长度为19位了，结果仍然显 ...

这个简单，加个if判断就行了