用公式来自动校验身份证号码
在工作中，经常会碰到身份证号码录入出错的情况，而单位有很多人的身份证号需要核对时，则会很麻烦，这里提供一个可以自动校验身份证号码的公式以及表格，可以方便快速的校对身份证号。
在系统中尤其是在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位数字，如果相等，代表身份证号码合法，否则是不合法。