如何在电子表格中设置身份证号验证.doc

如何在电子表格中设置身份证号验证 在建设发展、公共、调控、效能和精细的“五型”财政工作中，我们要积极推行乡镇财政所标准化建设，逐步实现行为规范化、管理制度化、工作精细化、办公现代化。 电子表格的灵活机变应用，在工作精细化和办公现代化当中，有着极其重要的作用。本文择其点滴，就如何在电子表格中设置身份证号验证，与众做个探究。 在大批处理农户信息的过程中，经常需要涉及到一项重要资料：身份证号码。我们在录入身份证号码的过程中，常常会因为粗心大意弄错号码，导致其不能正确地联系和识别相关人员。要查找一个身份证号是否出错，一般的做法，是上网输号查证，看尾号是否符合标准，但这种办法效率很低，不适合成批查找处理。本人结合工作实践，试用了一种可以在电子表格中批量处理身份证号验证的函数公式，能有效地解决这个难题。 首先，让我们先来了解身份证号的编号规则。我们现在通行的身份证号为十八位号，前六位是地区代码，第七至十四位是出生年月日代码，第十五、十六位是乡镇代码，第十七位是性别识别码，男单女双，同时又是同地区、同出生日期、同性别人员的区别码，第十八位尾号则是前十七位号码的识别码，它是判断身份证号是否正确的重要依据。尾号设置的专门规则是：用前十七位号码，依次加权乘以7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2，再将得出的加权总和除以11，得出的余数，从0到10十一种，依次对应尾号：1、0、X、9、8、7、6、5、4、3、2。 现在，我们来设置相关公式和函数。在EXCEL表的B、C、D、E四列中，分别设立身份证号、尾号、参数、检验结果四个项目。 第一步：在身份证号列，将整列设为文本格式，在身份证号项目名称下的单元格中（如B3单元格），输入一个十八位的第二代身份证号； 第二步：在参数项目下的同行单元格内（D3单元格），输入如下函数公式：=12-(MID(B3,1,1)*7+MID(B3,2,1)*9+MID(B3,3,1)*10+MID(B3,4,1)*5+MID(B3,5,1)*8+MID(B3,6,1)*4+MID(B3,7,1)*2+MID(B3,8,1)*1+MID(B3,9,1)*6+MID(B3,10,1)*3+MID(B3,11,1)*7+MID(B3,12,1)*9+MID(B3,13,1)*10+MID(B3,14,1)*5+MID(B3,15,1)*8+MID(B3,16,1)*4+MID(B3,17,1)*2)+INT((MID(B3,1,1)*7+MID(B3,2,1)*9+MID(B3,3,1)*10+MID(B3,4,1)*5+MID(B3,5,1)*8+MID(B3,6,1)*4+MID(B3,7,1)*2+MID(B3,8,1)*1+MID(B3,9,1)*6+MID(B3,10,1)*3+MID(B3,11,1)*7+MID(B3,12,1)*9+MID(B3,13,1)*10+MID(B3,14,1)*5+MID(B3,15,1)*8+MID(B3,16,1)*4+MID(B3,17,1)*2)/11)*11，目的是将B3单元格的身份证号计算出加权总和后除以11得出余数，并用12相减得出参数； 第三步：在尾号项目列的同行单元格内（C3单元格）输入以下公式函数：=IF(D3=12,1,IF(D3=11,0,IF(D3=10,X,D3)))，目的是按照尾号的专用编码规则，将第二步D3单元格的参数计算结果转化为正确尾号； 第四步：在检验项目下的单元格（E3单元格）内输入公式函数：=IF(MID(B3,18,1)-D3=0,正确,错误)，目的是将B3身份证的尾号与计算出的正确尾号相对比，标示正确与否。 第五步：批量检验。将批量身份证号码输入或复制到B3以下的B列中，选定C3：E3，将光标放置到右下角，显黑十字时，按住鼠标左键，双击之后，你所输入的身份证号之后三列，即刻就能出现你想得到的一切结果。 这个方法，没有用到多么复杂的函数，只是几个常见函数综合起来与公式灵活应用，就得到了令人满意的神效。如果我们能多动脑筋，简简单单的一个EXCEL电子表就能使我们实现办公现代化，再结合金财系列软件的灵活运用，电脑与手工妥善结合等，我们“五型”财政建设中的精细化和效能化两型，就能有看得到的成果了。 （莲花县良坊镇财政所 刘志发） 办公室电话：0799-7281007邮编：337109