Sci论文 - 至繁归于至简,Sci论文网。 设为首页|加入收藏
当前位置:首页 > 计算机论文 > 正文

Excel函数和VBA校验个人身份证号应用实例论文

发布时间:2022-09-30 10:11:59 文章来源:SCI论文网 我要评论














SCI论文(www.scipaper.net):
 
  摘要:在学生管理工作中,涉及的事务信息量繁杂且重要,信息核查费时费力且无法保证完全准确。本文通过数据有效性、Excel函数、VBA等多种方法,提取个人身份证号进行信息校验,长期解决学生管理中的实际问题,实现数据高效处理。
 
  关键词:Excel函数;VBA校验;身份证号;数据处理
 
  Application example of verifying personal ID number by using Excel function and VBA
 
  Zhou Jie1,Zhang Xin2
 
  (1.Xinjiang Vocational and Technical College of Construction,Urumqi Xinjiang,830000;2.Guping Information Security Technology Co.,Ltd,Urumqi Xinjiang,830000)
 
  Abstract:Via student management,a large amount of complex data need to be processed with accuracy and completed within timely manner,Most of the time,the cross checking process is time consuming and labor intensive.In this paper,based on data authentication,Excel function and VBA,a solution has been built for extracting personal ID number for verification purpose.Examples in this paper demonstrated how to solve practical problems in student management and achieve efficient data process.
 
  Key words:Excel function;VBA verification;ID number;data processing
 
  学生管理在学校工作中是一项重要而复杂的工作。学生基本信息是学校掌握学生基本情况的一手数据,也是其他管理工作的基础。身份证号作为每个公民唯一的标识信息,与学籍注册、信息采集、体检、资助、社保、火车磁条、毕业证办理等各项工作密切相关,是绝不能出错的信息,一旦出错将会给其他各项工作带来不便,甚至造成无法挽回的损失。
 
  相关文献显示,我国已经有不少对身份号码合规性进行校验的方法,最常见的是利用加权取余法进行校验,即将身份证的前17位与系数相乘后相加,再用除法得到余数进行校验,不过,少有涉及从用户填写的身份证号中提取信息与用户所填写的相关信息进行校验的方法。在学生信息管理中,对身份证号码的校验,工作人员可通过Excel函数和VBA进行,这有助于减少人为出错率,促使工作高效开展[1]。Excel函数十分简单,VBA是新一代标准宏语言,相对于其他计算机语言来说比较容易学习。工作人员掌握VBA语言,通过VBA程序代码可以让复杂的工作简易化,使工作自动化。

\
 
 
  如何使用Excel校验身份证号码,是人们需要普遍关注的问题。工作人员首先要了解身份证号码的编码原则。身份证号码是每位公民唯一的、不变的18位标识,由17位数字和1位校验码组成,从左到右依次为1-6位的数字地址码,即常住户口所在县(市、区)的行政区划代码,按GB/T2260的规定执行,学校可以利用前6位数字来校验学生的户籍地址信息。7-14位为数字出生日期码,即出生的年、月、日,按GB/T7408的规定执行,学校可以利用这8位数字验证学生的年龄信息。
 
  15-17位为数字顺序码,即在同一地址所标识的区域范围内,对同年、同月、同日出生的人编定的顺序号,由县、区级政府所辖派出所分配,每个派出所分配码为10个连续号码,第17位(即倒数第2位)为性别,奇数为男性,偶数为女性,学校可以利用第17位数字验证学生的性别信息。18位为数字校验码,校验码是根据前面17位数字码按照ISO 7064:1983.MOD 11-2校验码规则计算出来的,因此,学校可以通过检查身份证号码位数、地址码、顺序码、出生日期格式等来判定身份证号码是否正确,并进一步校验学生的其他信息是否准确。
 
  一、身份证号位数校验
 
  验证身份证号码位数是否为18位字符的方法如下。
 
  方法一:使用数据有效性校验。将身份证号列的“文本长度”设置为“等于”,长度为“18”。
 
  并在“出错警告中”设置当输入位数不符合18位时“停止”运行,并提弹错误信息。
 
  方法二:使用函数校验。利用函数IF和len函数校验,公式为=if(len(E2)=18,"正确","错误")。IF函数的作用是判定条件是否成立,有三个参数,第一个参数是条件语句,第二个参数是条件成立返回的值,第三个参数是条件不成立返回的值[2]。Len函数的作用是返回文本字符串的字符个数,只有一个参数,即所需要统计字符个数的字符串。这种方法弊端是校验结果需要占用新的一列。
 
  方法三:使用VBA校验。为方便调用,创建单独函数IDNumberLenJudge,主程序向该函数传递身份证号码(IDNumber)及身份证号码所在行数(RowNumber)两个参数,以此判断身份证号码是否满足18位的长度。
 
  二、地址码校验
 
  身份证号1-6位数字是地址码,地址码是户口所在县(市、区)的行政区划代码,按GB/T2260的规定执行。首先,学校需要先将中华人民共和国民政部网站发布的行政区划代码复制到Excel中,本例是将其复制到同一个工作簿的另一张工作表Sheet2的A列中,重命名Sheet2为“全国行政区划代码”。
 
  方法一:使用数据有效性校验。利用Left函数从E列提取身份号码的前6位,将其格式用VALUE设置为数值型,公式为=VALUE(LEFT(E2,6))。再对G列进行数据有效性设置,设置有效性条件允许“序列”,来源为Sheet2中的A列。
 
  当结果不在Sheet2的A列全国行政区划代码范围内时,Excel会弹出“停止”的终止出错警告对话框,并显示“错误信息请输入正确的行政区划代码”。
 
  方法二:利用函数校验。利用IF、ISERROR、VLOOKUP函数校验,公式为=IF(ISERROR(VLOOKUP(G3,Sheet2!A:A,1,FALSE)),"不属于","属于")。该方法的结果需要占用新的一列H列,VLOOKUP函数的作用是在Sheet2的A列中查找是否有G3的值(即G3是否在A列中),ISERROR函数的作用是校验VLOOKUP函数返回的值的正确性[3]。在本例中,校验结果H3显示G3单元格“654121”不属于Sheet2表A列的行政区划代码范围。另外,也可以用Vlookup函数提取户籍所在地信息。
 
  方法三:使用VBA校验定义函数ZoneCodeJudge对身份证号中的地址码进行校验,地址码不在已有范围内的,则将该单独地址码文字颜色标为黄色,主函数会根据该函数的返回值进行弹窗提示。
 
  三、性别校验
 
  性别信息隐藏在顺序码中,学校只需要验证第17位是奇数还是偶数即可得知持证人的性别,奇数为男性,偶数为女性。
 
  方法一:用函数提取性别后进行判断。需要使用If、Mod、Mid函数。公式为=IF(MOD(MID(E2,17,1),2),"男","女")。Mid函数的作用是返回文本字符串中,从指定位置开始特定数目的字符,有三个参数,第一个参数是指定某个字符串,第二个参数是指定起始位置,第三个参数是指定字符数数目[4]。例如,Mid(E2,17,1)即将E2单元格中的字符串从第17位开始数,返回1个字符,如E2单元格中存储的是“650102198111072524”,则返回的结果是字符“2”。Mod函数的作用是返回两数相除的余数,有两个参数,第一个参数是被除数,第二个参数是除数。最后,使用If函数判断所得的余数是否为0。如果是,则说明身份证号码的第17位是偶数,持证人是女性,反之则是男性。然后将得出的结果和所填写的结果通过数据对比检验是否一致。
 
  方法二:使用VBA校验。定义方法sexJudge对身份证中的性别信息进行提取判断,并将分析结果写入指定单元格。具体代码如下:

  \
 
  四、年龄计算
 
  方法一:使用函数校验。使用TODAY函数返回系统当前日期,再用YEAR函数取得当前年度,使用MID函数从身份证号中提取出出生年度的四位数值,再将两者相减即可得到该生年龄,最后用连接符“&”添加文字“岁”。公式为=YEAR(TODAY())-YEAR(K2)&"岁"[5]。该方法没有严格按照月份进行年龄计算。
 
  方法二:使用VBA校验。定义方法AgeCal对身份证中的生日信息进行提取判断,并计算出现在的年龄,写入指定单元格,误差在一个月内。具体代码如下:

  \
 
  五、VBA主函数
 

\
 
 
  为便于运行,可通过主函数将上述单独的VBA函数及方法进行关联调用,整体逻辑为先获取Excel表中所有身份证号及所有全国行政区划代码,再逐个对身份证号进行核查。明确判断身份证位数,如不满足18位则标红记录并对下一个身份证号进行核查;满足18位的情况下再进行行政区划代码校验,如不在已有行政区划代码中,则对单元格标黄并对下一个身份证号进行核查,在满足的情况下提取出性别和年龄信息。具体代码如下:

  \
\
 
  综上所述,Excel是办公软件中必不可少的应用软件之一,也是数据处理的重要工具。除了筛选、排序、数据透视等一些基本功能,利用Excel的数据校验、函数、VBA编程可以极大提高学生管理工作的效率和准确率。VBA语法简单、功能较多,相较于其他程序语言较容易理解,适合从事行政管理工作中的非编程专业人员。本文中的实例可以解决学生管理中的实际问题,可有效提高工作效率。
 
  【参考文献】
 
  [1]李新英.EXCEL VBA在教育管理中的应用[J].智库时代,2019(06).
 
  [2]王红然,王书旺.基于Excel函数的身份证号码探密[J].电子技术与软件工程,2018(22).
 
  [3]王建君.关于Excel中vlookup函数在学生信息管理中的应用[J].电子技术与软件工程,2021(02).
 
  [4]张祖平.Excel中的身份证号码玄机[J].电脑知识与技术(经验技巧),2020(06).
 
  [5]潘皎.浅析EXCEL函数在学生信息管理中的应用[J].黑龙江教育学院学报,2019(10).
 
关注SCI论文创作发表,寻求SCI论文修改润色、SCI论文代发表等服务支撑,请锁定SCI论文网!

文章出自SCI论文网转载请注明出处:https://www.scipaper.net/jisuanjilunwen/44180.html

发表评论

Sci论文网 - Sci论文发表 - Sci论文修改润色 - Sci论文期刊 - Sci论文代发
Copyright © Sci论文网 版权所有 | SCI论文网手机版 | 豫ICP备2022008342号-1 | 网站地图xml | 百度地图xml