用公式来自动校验身份证号码(附excel表格)

在工作中,经常会碰到身份证号码录入出错的情况,而单位有很多人的身份证号需要核对时,则会很麻烦,这里提供一个可以自动校验身份证号码的公式以及表格,可以方便快速的校对身份证号。

在系统中尤其是在EXCEL中录入身份证号码,出错的可能性很多,如位数不等于18位、号码中数字错误、后3位变为0、员工提供假身份证号码、身份证号码输入重复等,我们可以从多个方面来验证输入的号码是否规范,如长度是否为18位、是否输入的文本格式、是否输入重复等。

在EXCEL中,我们可以采用一种通用的方法来避免前述各种错误的产生,这种方法是从身份证号码的编辑规则入手,对输入的身份证号码进行判断,如果错误则不允许输入。

校验码和其计算方法

身份证号码第18位为校验码,它主要是来验证前17位数字输入的是否正确,它的取值范围为0至10,当为10的时候用X来表示。校验码的计算方法如下:

1.将前面的身份证号码17位数分别乘以不同的系数,从第1位到第17位的系数分别为:7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2。

2.将这17位数字和系数相乘的结果相加,然后除以11,余数只可能有0、1、2、3、4、5、6、7、8、9、10这11个数字。其分别对应的最后一位身份证的号码为1、0、X 、9、8、7、6、5、4、3、2 (即余数0对应1,余数1对应0,余数2对应X……) 。

根据校验码的计算方法,如果身份证号码位数不对、一不小心把数字输错、后3位全为0甚至输入其它内容的时候,通过数据验证都可以进行错误提醒。

公式如下:

=IF(VLOOKUP(MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,”X”;3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)&””=RIGHT(A2,1),”合法“,”不合法“)

公式解析:

这是多个函数的嵌套公式,其中的思想就是和前面讲的验证方法一致,以B2单元格公式为例。

ROW($1:$17)是生成1到17的数字,生成1列17行的数组{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}。

MID(A2,ROW($1:$17),1)是依次提取身份证号码前17个数字,生成1列17行的数组,结果为{“1″;”3″;”0″;”1″;”8″;”2″;”1″;”9″;”8″;”6″;”0″;”2″;”1″;”5″;”5″;”0″;”3″}。

SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})是将身份证号前17位分别与对应的系数相乘,然后相加得出结果,结果为287。

MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)是对SUMPRODUCT公式的结果(287)除以11求余,结果为1。

VLOOKUP(MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,”X”;3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)&””可以视为VLOOKUP(1,{0,1;1,0;2,”X”;3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0),这个是VLOOKUP的基础用法,第二个参数是2列11行的数组,用连接符“&”连接””是为了将结果转换为文本格式,结果为0。

然后我们用IF函数嵌套,通过与身份证号码最后一位对比,RIGHT(A2,1)是提取身份证号码最后1位数字,如果相等,代表身份证号码合法,否则是不合法。

excel表格下载

该表格可以自动校验身份证号码,而且以不同的颜色显示身份证号是否正确。

点击下载:身份证号码校验excel表格

示例:

原创文章,转载请注明: 转载自科技爱好者博客

本文链接地址: 用公式来自动校验身份证号码(附excel表格) (https://www.lxx1.com/5593)

如果博客对您有帮助,请给我 赞助


热度:957℃

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据