使用EXCEL进行合法性验证，不再需要担心号码错误

在工作场所中，人力资源部门是与ID号码联系最多的部门，因为每个公司都需要输入员工信息excel号码验证，这涉及存储ID卡的副本和输入身份信息. . 在工作中，经常会在输入ID号时遇到错误.

在系统中输入ID号，尤其是在EXCEL中，有很多错误的可能性，例如位数不等于18，位数错误，最后3位变为0，员工提供了一个伪造的ID号和身份我们可以从多个方面验证输入数字是否标准化，例如长度是否为18位数字，是否输入文本格式以及是否重复输入.

在EXCEL中，我们可以使用一种通用方法来避免上述各种错误. 此方法从ID编号的编辑规则开始，并判断输入的ID编号. 如果输入错误，则不是“允许输入”.

ID号的第18位数字是校验码. 它主要用于验证前17位数字输入是否正确. 取值范围为0到10. 取值为10时，用X表示. 校验码的计算方法如下:

1. 将前一个ID号的17位数字乘以不同的系数. 从第一个数字到第17个数字的系数是: 7，9，10，5，8，8，2，1，6，3，7excel号码验证，9，10，5，8，4，2，2.

2. 将17位数字与系数相乘的结果相加，然后除以11，其余部分只能包含11位数字，分别为0、1、2、3、4、5、6、7、8、9和10. 最后一张ID​​卡的对应编号为1，0，X，9，8，7，6，5，4，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），“ Legal”，“ Illegal”）

公式分析:

这是多个函数的嵌套公式. 该想法与上述验证方法相同. 以单元格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）依次提取ID号的前17位数字，以生成17列和1列的数组. 结果为{“ 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}）是将ID号的前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函数进行嵌套，并与ID卡号的最后一位数字进行比较，RIGHT（A2,1）用于提取ID卡号的最后一位数字.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-170844-1.html