excel身份证号码格式-EXCEL｜用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。

身份证号码前17位对应系数

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甚至输入其它内容的时候，通过数据验证都可以进行错误提醒。

我们在EXCEL中具体演示一下（合法的号码专门隐去了，不能发出来）：

公式如下：

=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位数字，如果相等，代表身份证号码合法，否则是不合法。