excel数据清洗与身份证号码校验

2019-10-29  本文已影响0人  程政_2533

老规矩,先说背景:

收集上来的都是excel文件,包含了姓名、证件号码、金额、和日期,因为都是手工录入的,会出现千奇百怪的符号,可以用excel的替换、分列和函数来清洗与校验

接下来介绍几个技巧,帮助快速清洗数据(均用wps表格来操作)


1,替换功能去除文档内各种意外符号

    ①把各列数据的格式先确定:根据内容来确定单元格格式,主要有三种:文本格式、数值格式、日期格式

    修改单元格格式后会发现并没有什么变化,做一下下一步操作:选择需要更新的列,点击【数据】>【分列】功能来更新一下,注意分列的间隔符全部去掉勾选,选择当前列的格式,点击完成即可。

    ②姓名、证件号码均不能包含字母、汉字、数字以外的字符,使用CTRL+H来查询替换为空,替换的字符有:空格、回车、Tab、逗号、括号、句号、顿号、斜杠、反斜杠等等

    多一个技巧,采用筛选和排序可以方便直观的看到有哪些数据是不合规范的,复制整个单元格,粘贴到替换窗口,可以把不可见的字符粘贴出来,这样就可以批量删除了

2,校验身份证号码是否不符合规范

    在身份证号右侧插入一列,在身份证号码的第一行输入以下公式:

=IF(LOOKUP((LEFT(A2,1)*7+MID(A2,2,1)*9+MID(A2,3,1)*10+MID(A2,4,1)*5+MID(A2,5,1)*8+MID(A2,6,1)*4+MID(A2,7,1)*2+MID(A2,8,1)+MID(A2,9,1)*6+MID(A2,10,1)*3+MID(A2,11,1)*7+MID(A2,12,1)*9+MID(A2,13,1)*10+MID(A2,14,1)*5+MID(A2,15,1)*8+MID(A2,16,1)*4+MID(A2,17,1)*2)-ROUNDDOWN((LEFT(A2,1)*7+MID(A2,2,1)*9+MID(A2,3,1)*10+MID(A2,4,1)*5+MID(A2,5,1)*8+MID(A2,6,1)*4+MID(A2,7,1)*2+MID(A2,8,1)+MID(A2,9,1)*6+MID(A2,10,1)*3+MID(A2,11,1)*7+MID(A2,12,1)*9+MID(A2,13,1)*10+MID(A2,14,1)*5+MID(A2,15,1)*8+MID(A2,16,1)*4+MID(A2,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{"1","0","x","9","8","7","6","5","4","3","2"})=RIGHT(A2,1),"","证件号码校验错误")

注意录入后回车,按ctrl+h替换 A2 至你的表格数据里第一条证件号码的位置;

填充该列即可校验出哪些证件号码是错误的,意思是这个身份证号码是不存在的,属于伪造或者录入错误

再使用筛选功能把该列非空白的数据筛选出来,ctrl+a 全选,alt(左)+; 选择可见单元格,复制粘贴到一个空白表格,即可把非有效证件号码筛选出来,返给数据录入员重新审核录入;

上一篇下一篇

猜你喜欢

热点阅读