身份证号码在Excel下的实时鉴错方法--中国期刊网

字体：大中小

首页> 原创作品> 正文

身份证号码在Excel下的实时鉴错方法

发表时间：2020/12/28 来源：《基层建设》2020年第24期作者：王兴敏

[导读] 摘要：本文介绍了如何利用Excel VBA功能实时判断输入Excel电子表格的身份证号码是否存在错误的方法和步骤。

        四川省煤田测绘工程院四川成都 610072
        摘要：本文介绍了如何利用Excel VBA功能实时判断输入Excel电子表格的身份证号码是否存在错误的方法和步骤。
        关键词：身份证号码；Excel；实时；鉴错
        一、引言
        Excel是微软公司Microsoft Office办公自动化软件的重要套件之一，其独特的VBA集成开发环境使软件的功能得到了极大拓展，广泛应用于各类工程建设和办公应用。在测绘地理信息领域，由于电子化和信息化的普及，集成了海量信息的各类数据库不断在行业发挥出巨大作用。出于信息安全考虑，我国公民的身份证号码作为个人的重要隐私信息仅供公安部门掌握，但是近年来，随着确权颁证工作的不断深入，各类确权建立信息数据库均需录入公民身份证号码，因此对于身份证号码在录入时的实时鉴错需求比较迫切。本文根据Excel VBA的特点，结合身份证号码校验规则，探讨了如何在Excel电子表格下实时鉴别输入的身份证号码是否有误。
        二、方法步骤
        1.触发方式选择
        Excel VBA下可以有多种方式触发加载宏运行。我们来比较一下几种常见触发方式的特点：
        （1）快捷键触发。即录制VBA加载宏的时候指定某一组合快捷键，触发时同时按下这几个键即可，听起来方便，但有人不习惯，因为键多了不能保证每次都几个键同时按下去。
        （2）工作簿ThisWorkbook下的BeforeSave过程。当点击工作簿“保存”功能后，在保存工作簿前触发，由于工具栏一般设置在应用程序顶部，且图标较小，操作起来容易误点其他图标，因此不方便。

图1 BeforeSave过程所在位置
（3）工作簿ThisWorkbook下的AfterSave过程。当点击工作簿“保存”功能后，在保存工作簿后触发，由于工具栏一般设置在应用程序顶部，且图标较小，操作起来容易误点其他图标，因此不方便。

图2 AfterSave过程所在位置
（4）工作簿ThisWorkbook下的SheetActivate过程。当本工作表被激活时触发，比如从其他工作表切换到本工作表，由于新建Excel工作簿默认有3个工作表，且表格相邻，鼠标任意点击其他表格再重新点击回来就能触发，操作上比较灵活，因此考虑采用这种触发方式。

        图3 SheetActivate过程所在位置
        2.鉴错结果呈现方式设定
        为了使实时鉴错结果呈现方式更直观，考虑采用单元格填充为红色的方式。同时，为了使修改正确之后单元格的红色填充自动消失，需要在每次运行宏的时候先将工作表全部填充为无色，待运行到有错误的单元格时再将该单元格填充红色。将工作表全部填充为无色的VBA语句如下：
        Cells.Select
        Cells.Interior.Pattern = xlPatternNone          '设置工作表无填充色
        3.非法号码判定
        身份证号码非法的判定包含长度非法判定、非法字符的判定和末尾校验码计算错误的判定三种。由于当前已统一采用18位身份证号码，故本文只讨论18位身份证号码的校验。
        长度非法判定指输入的身份证号码长度不是18个字符长度的。
        非法字符包括以下两类：
        （1）前17位代码中输入了阿拉伯数字0～9以外的字符的。
        （2）第18位代码输入了“X”或“x”以外的字符的。
        核心代码示例如下：
        For k = 1 To 17
        b(k) = Mid(s1, k, 1)
        If b(k) = "0" Or b(k) = "1" Or b(k) = "2" Or b(k) = "3" _
        Or b(k) = "4" Or b(k) = "5" Or b(k) = "6" _
        Or b(k) = "7" Or b(k) = "8" Or b(k) = "9" Then
        btm(k) = Val(b(k))                  '本体码赋值
        dycj(k) = btm(k) * jqyz(k)          '对应乘积
        jsh = jsh + dycj(k)                 '级数和
        Else
        Cells(i, n1(j)).Interior.Color = 255        '单元格填充红色
        Exit For
        End If
        Next
        b(18) = right(s1, 1)
        If b(18) <> "X" And b(18) <> "x" Then
        Cells(i, n1(j)).Interior.Color = 255        '单元格填充红色
        End If
        末尾校验码计算错误是指根据身份证号码的校验规则计算出的校验码与输入的末尾校验码不一致的。核心代码示例如下：
        ys = jsh Mod 11
        Select Case ys
        Case 0: xym = "1"
        Case 1: xym = "0"
        Case 2: xym = "X"
        Case 3: xym = "9"
        Case 4: xym = "8"
        Case 5: xym = "7"
        Case 6: xym = "6"
        Case 7: xym = "5"
        Case 8: xym = "4"
        Case 9: xym = "3"
        Case 10: xym = "2"
        Case Else: Cells(i, n1(j)).Interior.Color = 255        '单元格填充红色
        End Select
        ws = Right(s1, 1)
        If xym <> ws Then
        Cells(i, n1(j)).Interior.Color = 255        '单元格填充红色
        End If
        程序运行结果示例如下图：

        图4. 身份证号码实时鉴错程序运行结果示意图
        三、结语
        本方法操作简便，易于实现，且修改后也可以实时消除错误提示状态，对身份证号码的输入进行了有效的实时提示，有助于内业技术人员及时纠正错误，对提高地理信息系统中身份证号码的采集输入效率帮助较大。
        参考文献
        [1]马维峰编著. Excel VBA应用开发从基础到实践. 北京：电子工业出版社2006.8
        [2]彭澎. Visual Basic程序设计. 北京：中国电力出版社 2006.2
        [3]杨晶. VB6.0程序设计教程与实训. 北京：科学出版社 2006.9
        作者简介
        王兴敏（1983- ），女，汉族，四川成都人，工程师， 2005年9月毕业于中央广播电视大学工商管理专业，现就职于四川省煤田测绘工程院，主要从事地理信息系统内业。联系方式（手机：13880337025，QQ：786830869，E－mail：786830869@qq.com，邮编：610072，地址：四川省成都市青华路39号四川省煤田测绘工程院）