三茅
会员中心

推荐 江苏快3爱彩乐遗漏查询,用EXCEL做身份证合法验证,再也不用担心号码是错的了

2019-09-24 11:21:36 阅读(2103) 评论(13) 收藏(27)

职场中,5分快乐8预测计划资源部门是和身份证号接触最多的部门了,因为每个公司都需要录入员工信息,这涉及身份证复印件的存管与身份信息的录入,在工作中,经常会碰到身份证号码录入出错的情况。

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

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

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

1.将前面的身份证号码17位数分别乘以不同的系数,从第1位到第17位的系数分别为:7910584216379105842


2.将这17位数字和系数相乘的结果相加,然后除以11,余数只可能有01234567891011个数字。其分别对应的最后一位身份证的号码为10X 98765432 (即余数0对应1,余数1对应0,余数2对应X……) 江苏快3爱彩乐遗漏查询。

根据校验码的计算方法,如果身份证号码位数不对、一不小心把数字输错、后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),"合法","不合法")


公式解析:

江苏快3爱彩乐遗漏查询这是多个函数的嵌套公式,其中的思想就是和前面讲的验证方法一致,以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位数字,如果相等,代表身份证号码合法,否则是不合法。


(本文版权归作者及三茅5分快乐8预测计划资源网所有,如需转载或摘录请注明出处!)
Eddy55265

13楼 Eddy55265

如果正确的身份证号输入,公式验证不合法,
1.有可能是‘A2“单元格在公式中所对应的单元格并非身份证号;
2.公式中一共有2个”A2“,有些只改了一个”A2“;
不过有些员工是15位身份证号,虽然现在这种情况很少了,但是还是有特例存在。

2019-10-17 13:28:12 回复 赞(0)
Eddy55265

12楼 Eddy55265

如果正确的身份证号输入,公式验证不合法,
1.有可能是‘A2“单元格在公式中所对应的单元格并非身份证号;
2.公式中一共有2个”A2“,有些只改了一个”A2“;
不过有些员工是15位身份证号,虽然现在这种情况很少了,但是还是有特例存在。

2019-10-17 13:28:08 回复 赞(0)
A风景

11楼 A风景

输入正确的身份证号码,随便改一下也是显示不合法???

2019-10-11 13:17:52 回复 赞(0)
A风景

10楼 A风景

输入正确的身份证号码,显示不合法

2019-10-11 13:14:24 回复 赞(0)
果壳的妞妞1

9楼 果壳的妞妞1

我竟然跟着你的步骤做出来了,好神奇

2019-10-09 10:43:06 回复 赞(0)
马振娥

8楼 马振娥

谢谢学习了

2019-10-09 10:42:46 回复 赞(0)
毛葫芦黑

7楼 毛葫芦黑

老师你还有视频课吗?地址在哪里?

2019-10-09 10:42:33 回复 赞(0)
sallyyu

6楼 sallyyu

还是去学习老师的视频课吧,更直接一点

2019-10-09 10:42:04 回复 赞(0)
小可爱本人

5楼 小可爱本人

不合法的意思是什么?

2019-10-08 15:40:46 回复 赞(0)
不做樊胜美2017

4楼 不做樊胜美2017

为什么要验证呢?

2019-09-29 09:31:59 回复 赞(0)
格格白丽娟

3楼 格格白丽娟

我用这个身份证检验我家的员工居然有“不合法”出现????难道。。。江苏快3爱彩乐遗漏查询。江苏快3爱彩乐遗漏查询。。江苏快3爱彩乐遗漏查询。?

2019-09-29 09:12:12 回复 赞(0)
格格白丽娟

2楼 格格白丽娟

******就是不合法的吗?不一定吧,验证这个的目的是什么呢?

2019-09-27 17:26:37 回复 赞(0)
黑夜里的眼睛

1楼 黑夜里的眼睛

#赞赏# 牛

2019-09-25 14:24:47 回复 赞(0)

上一篇:没有出格,何惧热议

订阅
高级5分快乐8预测计划资源管理师。 5分快乐8预测计划资源、EXCEL实战讲师。 畅销书《Excel5分快乐8预测计划资源管理实操从入门到精通》作者,当当、天猫、..