高校毕业生信息采集数据规范性检测的VBA实现.doc

高校毕业生信息采集数据规范性检测的VBA实现余 磊，田向阳淮北师范大学就业指导中心，安徽 淮北，235000摘 要：针对高校毕业生信息数据规范性检测的特点和传统检测方法存在的不足，在对毕业生信息采集数据常见问题分析的基础上，提出一种数据规范性检测的自动化解决方案，并基于Excel环境运用VBA对其程序实现。该方案不仅能提高工作效率和数据检测的精确度，而且能提高计算机资源的利用率，对促进就业工作自动化的发展与创新有着重要的指导意义。关键词：高校、毕业生、数据规范性检测、自动化、Excel、VBA1、引言高校毕业生信息数据，是毕业生派遣方案编制、就业报到证办理和毕业生跟踪服务的重要依据，及时准确地做好毕业生信息采集与核对工作不仅是做好毕业生工作的重要环节，也是就业工作中的难点1。毕业生信息采集的主要内容包括学号、姓名、性别、身份证号、学院名称、专业名称、生源所在地、手机号码、家庭联系电话、电子邮箱等关键信息2。高校毕业生就业管理系统的网络一体化，要求毕业生信息数据不仅内容正确，也要格式规范。非规范的数据格式将影响就业数据的正确性，数据规范性检测已成为毕业生信息采集与核对的基础工作。对照标准数据和规范条例的人工检测，仍是目前毕业生信息规范性检测的最常用方法，在操作上具有劳动强度大、工作效率低的缺点，在数据检测上易存在漏检、错检等不足。由于毕业生信息数据规范性检测具有重复性、周期性和计算机管理的特点，可运用计算机技术实现数据规范性检测的自动化解决，不仅能够提高工作效率，而且可以实现数据检测的精确性。Excel因其在数据管理与数据挖掘上具有操作简单、使用方便、功能强大等优点，目前已是高校就业数据管理主要办公软件3。VBA是类似Visual Basic的一种宏语言，主要用于Office办公软件的功能扩展，通过VBA编程可实现重复任务的自动化，数据的复杂性操作， Excel菜单和界面的自定义等功能4。因此，可以在Excel环境下，运用VBA实现毕业生信息采集数据规范性检测的自动化处理。2、毕业生信息采集数据中常见问题分析在毕业生信息数据的采集过程中，受信息组织方式、信息使用习惯、信息更新滞后、数据操作不当等因素影响，采集的毕业生信息数据在格式往往存在较多不规范之处。归纳起来，主要表现为以下几个方面。（1）标准型数据错误。行政区划和专业名称的标准化和代码化，要求生源地和专业名称数据必须与国家最新公布的行政区划表和专业名称表中的数据保持一致。在生源地、专业名称等标准型数据字段录入时，经常存在简称、旧称、相似称、错别字、数据模糊等现象。如：“安徽省巢湖市居巢区”是旧称；“安徽省合肥市”存在县级生源地模糊；“安徽省宿州市墉桥区”中的存在错别字。标准型数据错误将无法正确生成相应数据的代码，造成数据统计错误。（2）文本型数字数据类型错误。文本型数字数据是对毕业生某些属性的客观描述，不参加数值运算，由于受数字使用习惯的影响，文本型数字数据常常被当作数值型数据录入。数据类型错误，将导致数据表示方式和数据运算错误，特别是在Excel中，数值型数据受计算机字长的限制，当输入的数值超过机器所能表示的范围，会自动转为科学计数的格式。例如：身份证号码作为数值数据录入时，被科学计数方式表示后，再对其进行文本类型纠正后，后三位被置零，检查不到位则造成数据错误。（3）数据中存在多余空格。造成多余空格存在的原因主要有三种：一种是数据录入操作不当，在数据的开头和结尾添加多余空格；二是在姓名录入时，为保持字段值整体上的美观，在两个字的姓名中间添加空格来实现宽度对齐；三是在身份证号、手机号等数字型字符录入时，为易于识别或避免科学计数表示，用空格对其进行等距间隔。在数据处理时，多余的空格容易造成数据字段长度的增加和数据的不一致性，若不去除，将会造成数据错误。（4）数据有效长度不合格。学号、身份证号码、手机、联系电话等数据都具有效长度，在信息录入时，由于粗心、数据误操作等原因，可能造成信息漏录现象。数据的有效长度不合格将直接造成数据错误。以上是毕业生信息采集数据中存在主要问题，也是毕业生信息数据规范性检测时需要解决的关键问题。在毕业生信息数据中还存在一些其它问题也需要关注和解决，如：电子邮箱格式错误、数据空值、字段乱序、标题非首行等情况。3、规范性检测方案设计3.1、基础性工作毕业生信息采集通常以专业或班级为单位，采集的数据需经就业管理部门进行规范性检测、校对和汇总后，再以某种方式供毕业生审核确认。为降低数据规范性检测和数据汇总的复杂度，要求数据表格在字段名称和排顺上要具一致性，即制作格式统一的信息采集Excel文件模板。3.2、方案设计分析（1）交互式窗体设计在软件设计中，无法通过程序实现的选择事项，通常借助窗体和控件以人机交互的方式来实现5。经分析，在毕业生信息校对中，需要手动选择的事项有：校对文件的选择、生源地代码和专业代码的添加选择，相应的窗体和控件设计如图1所示。图1.数据规范性检测程序窗口界面（2）常量和全局变量定义1）常量。为提高程序的易维护性，一些常用固定值，用常量说明语句定义。如：文件默认操作位置、单元格属性的统一设定值、标题顺序常量、错误标识颜色、身份证号长度等。2）全局变量。为降低程序的时间和空间复杂度，对于生命周期长、作用域广和重用率高的数据以全局变量的方式存储和使用6。在生源地、专业等标准型数据检测时，用于比对的标准参照数据的重用率较高，用全局数组变量存储，不仅能避免Excel表格频繁切换的高时间、空间复杂度问题，还可以增加标准参照数据应用的灵活性。表格行数、表格列数、字段列位置等表格属性是数据遍历和精确定位的必需数据，具有生命周期长和作用域广的特点，也以全局变量的形式存储和使用。（3）辅助函数设计为增强程序与数据的相对独立性，提高程序执行效率，降低程序设计的复杂度，对功能独立的操作用函数对其封装。1）文件打开函数。具有Excel文件打开、文件打开错误类型判断和提示功能。2）标题行一致性判断与校正函数。参照标题顺序常量，进行标题行位置、标题顺序、标题名称正误、字段缺失情况判断，对标题非首行和标题乱序等情况具有自动校正功能，对字段缺失和标题名称错误等情况给出错误类型提示。3）表格属性全局变量赋值函数。首先删除指定工作簿当前工作表的空白行列，然后基于当前工作表对行数、列数、字段的列位置等全局变量进行赋值操作。4）单元格属性设置函数。根据预设常量，实现对指定工作簿当前工作表的单元格前景色、背景色、字体、字体大小、字体颜色、边框等属性的初始化设置功能。5）全局数组变量的标准参照数据赋值函数。基于行政区划表、专业名称表和学院名称表，实现生源地数组、专业名称数组、学院名称数组等全局变量的标准代码和标准惨遭数据的赋值操作。6）代码添加函数。当交互式窗体中的“添加生源地代码”或“添加专业代码”可选项勾选时，实现相应字段数据代码的添加操作。（4）数据规范性检测函数设计为提高代码的重用率和扩大函数的适用范围，通常把具有共性的操作用同一函数封装。由毕业生信息采集数据常见问题的分析可知，数据规范性检测操作跟数据的文本字符类型和字符结构形式有关。因此，毕业生信息采集数据的规范性检测可通过数字型数据检测和字符型数据检测两个函数来实现。1）数字型数据规范性检测函数。首先对校对数据进行空格去除、文本格式设置和错误标识去除操作，然后再进行数据空值、数据长度和身份证尾数错误判断，对存在错误的数据给出错误类型提示和错误颜色标识。2）字符型数据规范性检测函数。根据文本字符结构形式可把字符型数据分为三种：一是标准型数据，如生源地、专业、学院字段数据；二是特殊格式数据，如电子邮箱字段数据；三是非标准型数据，如姓名、性别、家庭住址字段数据。首先对校对数据进行空格和错误标识去除操作，再根据校对数据的格式类型进行相应错误类型判断操作，并给出错误类型提示和错误颜色标识。（5）主过程和程序菜单1）主过程。程序的使用接口，主要对辅助函数和数据规范性检测函数进行组织，实现对毕业生信息采集数据的规范性检测功能。2）程序菜单设计。用VBA自定义菜单，并指定主过程为其调用对象，然后加载于Excel菜单栏，实现规范性检测程序的菜单调用，方便程序的操作和使用。程序菜单如图2所示。图2.规范性检测程序菜单3.3、数据规范性检测函数的VBA实现限于篇幅，以下只给出规范性检测函数的VBA代码。（1）数字型数据规范性检测函数Private Function digital_type(bookname As Workbook, row_num As Integer, col_num As Integer, data_len As Integer, field_name As String)Dim ws As WorksheetDim str, error_msg As StringSet ws = bookname.Worksheets(1)error_msg = With ws.Cells(row_num, col_num) If InStr(1, Trim(.Value), 【, vbTextCompare) Then .Value = + Left(Trim(.Value), InStr(1, Trim(.Value), 【, vbTextCompare) - 1) End If str = Replace(Trim(.Value), , ) .Value = + str If Len(.Value) 0 Then If Len(.Value) data_len Then error_msg = 长度错误 End If If Right(.Value, 3) = 000 Then error_msg = Trim(error_msg + + 尾数错误) End If Else error_msg = NULL + field_name + 为空 End If If Len(error_msg) 0 Then .Value = .Value + 【 + error_msg + 】 .Font.ColorIndex = error_color End IfEnd WithEnd Function（2）字符型数据规范性检测函数Private Function text_type(bookname As Workbook, row_num As Integer, col_num As Integer, standard_flag As Boolean, email_flag As Boolean, standard_data() As String, array_num As Integer, field_name As String)Dim ws As WorksheetDim i As IntegerDim str, error_msg As StringSet ws = bookname.Worksheets(1)error_msg = 【数据错误】With ws.Cells(row_num, col_num) If InStr(1, Trim(.Value), 【, vbTextCompare) Then .Value = + Left(Trim(.Value), InStr(1, Trim(.Value), 【, vbTextCompare) - 1) End If str = Replace(Trim(.Value), , ) .Value = + str If Len(.Value) 0 Then If standard_flag And Not email_flag Then For i = 1 To array_num Step 1 If Trim(.Value) = Trim(standard_data(i, 2) Then error_msg = If Len(standard_data(i, 1) = 6 And Right(standard_data(i, 1), 2) = 00 Then error_msg = 【非县级生源地】 End If Exit For End If Next i ElseIf email_flag And Not standard_flag And InStr(Trim(.Value), ) Then error_msg = ElseIf Not (standard_flag Or email_flag) Then error_msg = End If Else error_msg = 【NULL + field_name + 为空】 End If If Len(error_msg) 0 Then .Value = .Value + error_msg .Font.ColorIndex = error_color End IfEnd WithEnd Function 4、结语Excel 环境下的毕业生信息采集数据规范性检测的VBA实现方案，在设计思路上，采用程序功能模块化的思想，具有逻辑清晰、层次分明的优点；在算法实现上，采用的功能模块函数封装方法，不仅提高了程序代码的重用率和执行效率，还增强了程序的健壮性和扩展性；在设计基础上，是基于Excel原有界面、功能和操作方式的数据处理功能扩充，具有操作简单、应用灵活的特点；在应用实践中，已被证实自动化程度高，不仅能提高工作效率和数据检测的精确度，而且能提高计算机资源的利用率；在自动化办公方面，能够为就业工作自动化的发展与创新提供参考思路。参考文