基于VBA技术的教务管理数据自动化处理系统研究和应用

2022-05-31张万昌黄宁

电脑知识与技术 2022年11期

张万昌黄宁

摘要：高校教务管理中数据处理是一项重要的基础工作。文章针对教务管理中数据处理工作量大，周期性重复，数据复杂，耗时长等问题，利用VBA编程二次开发数据自动化处理系统。可实现按多个标志字符串为条件将数据分列、以单元格内容为完整字符串从中查找特定字符串实现多种方式替换、指定多个条件列实现多条件汇总、数据分类提取到不同工作表，删除特定行和序号重置等功能。该系统在编程过程中依据功能之间的逻辑顺序进行系统化程序设计，使用中可根据需求选定不同功能、设置不同参数，从数据整理到汇总、数据提取实现全自动化执行，按照设定条件生成不同结果。

关键词：Excel;VBA;二次开发;教务管理;数据处理;自动化

中图分类号：TP311 文献标识码：A

文章编号：1009-3044（2022）11-0065-04

1 引言

Microsoft Office是日常工作中应用最为广泛的一款办公软件，Excel作为其应用程序之一，拥有较强的数据存储能力，同时具备一定的数据处理能力。对于数据量少结构简单且规范的数据， Excel可满足处理需求，但是对于数据量大结构复杂的数据仅靠Excel自带的功能来处理是很困难的。Excel集成了VBA（Visual Basic for Application）编程环境，可以通过二次开发来增强Excel的数据处理能力。VBA是寄生于应用程序之内的一种面向对象的编程语言，语法简单，易学易懂，同时可实现可视化编程，交互性强。这些特点使得它在很多行业得到了广泛的应用，王锦秀针对期刊编辑过程中的一些问题，利用Word VBA 实现表格转置、求和校验、三位分节等功能[1];崔玉洁基于 Word VBA 建立智能编校系统，可以对敏感词与重复词句进行标注、格式调整[2];刘超利用VBA开发了爆破辅助设计系统，有效地提高了设计精度[3];王乔利用VBA编程来实现煤田钻孔综合成果整理和煤田钻孔孔斜批量处理等[4-5]。通过搜索知网文献，在高校教务管理中，利用VBA编程集中在教学工作量的统计、报表的汇总，成绩测评以及成绩管理，教学数据分析和评价等方面的应用，这些工作数据量小，数据结构规整，且均是对单一数据的统计分析，在单一功能方面实现了一定的自动化操作[6-9]。而对于数据量大结构复杂数据的处理以及同时实现多个功能全自动操作的研究很少。

甘肃开放大学是国家开放大学办学体系的省级分校，每学期的新生和毕业生数据约2万条之多，对这么多的数据进行处理是非常繁杂的工作。稍有不慎很容易出错，这对教务管理人员是非常大的考验，通过一定的方法实现自动化处理变得十分必要。

2 原始數据特征和工作需求分析

原始数据特征和需求分析是进行数据处理和统计工作的基础。只有了解原始数据结构、数据类型，以及不同数据之间的关系，再结合需求，才能提出对应的解决思路或方法。

国家开放大学教务管理系统中可以下载省校总的原始数据表，数据共18列，采用其中4列数据即可完成工作需求。这4列数据标题依次为学校名称、姓名、专业和学历层次。每行数据互不重复，单元格数据全部为文本（字符串）格式，学校名称所在列单元格的数据包含了学生所属的市级分校和学习中心的完整信息，该列单元格中字符串长度不唯一，结构不规范，是数据整理的关键。可归为以下6类形式：“***分校***”“***分校”“***学院***”“***学院”“***工作站”“***教学点”（不含“分校”或“学院”字符，是一个独立的学习中心），“分校”或“学院”前面的字符长度也不完全相同（图1）。

实践中，需要完成以下工作才能得到最终结果：第一、将学校名称这一列数据分为分校和学习中心两列数据，同时一方面分校名称必须按照标准名称进行统一，另一方面由于某些学习中心实际管理分校并非原始数据中的分校，需要更改，这是数据整理阶段需要完成的工作;第二、将整理好的数据按照分校、专业、学历层次进行统计;第三、将统计好的数据按照不同学历层次分表提取，不同的工作表中以市级分校或独立的学习中心为行标题、以专业为列标题;第四、删除合计列数值为0的行;第五、所有工作表的序号重置。

数据提取到的表需要设计成标准模板。所有表在一个Excel文件下，不同表的名称以不同的学历层次命名。所有表均是统一的格式设置，行标题和列标题位置一致;由于分校的前后顺序有要求，所有表中行标题按照顺序写有完整的分校名称（学习中心）。标准模板如图2所示。

3 系统设计过程

3.1 可视化界面设计

通过Excel开发工具菜单点击VB编辑器或Alt+F11快捷键进入VBA编辑窗口。点击插入用户窗体，在属性窗口修改窗体名称为myform，caption属性为“数据自动化处理系统”。点击工具箱插入控件，在属性窗口修改caption及Font相关属性，添加的控件类型及作用如表1所示。拖动控件可以调整位置和大小[10]，设计界面如图3所示。

3.2 定义公共变量和自定义函数

在代码编辑窗口，定义公共变量：字符型变量 file，Workbook对象变量 wb，Worksheet对象变量 sh。编写自定义函数Public Selected_File（）和Public Selected_Folder（），两个函数通过FileDialog对象显示选择对话框，前者选择一个Excel文件，后者用于选择文件夹，两个自定义函数返回值均是字符型。

3.3 控件事件过程

1）控件属性初始化设置。在窗体的activate事件过程中对不同控件的属性进行初始化设置，除了只用于显示的文本框，其他可以输入的文本框value属性设置为代码所在工作簿第一张工作表指定的单元格值。周期性重复工作每次用到相同的参数，在第一次输入之后，关闭程序时保存工作簿，下一次点击之后显示的是上一次使用的参数，这样可以避免重复输入。对只用于显示的文本框的Locked 属性设置为True[11];所有复合框用list方法和Array函数添加用于提示性的字符串和“A”到“Z”的字符值，将ListIndex值设置为0，显示第一个值（提示性字符串），Style属性设置为2，复合框只允许选择而不允许输入。

2）命令按钮“选择并打开原始数据文件”事件过程。在命令按钮“选择并打开原始数据文件”的Click事件过程中调用Selected_File（）函数，将返回值赋值给公共变量file，前面的文本框的value屬性设置为flie，用于显示选择的文件路径，文本框的locked属性设置为True;用Workbooks对象集合的Open方法打开原始数据文件，实例化对象变量wb和sh，wb表示原始数据文件对象，sh代表文件中的第一张工作表对象。

3）数据整理页面控件事件过程。数据整理页面的“按标志字符串分列”“同位替换”和“异位替换”功能区文本框，均不许在文本框输入空格、非打印字符、中文逗号和连续的逗号，首尾也不允许出现逗号。可在所有文本框的change和exit事件过程中可写入不同的代码，删除首尾逗号必须在exit事件过程中实现，在change事件过程中会导致无法输入逗号。

具体代码编写过程中，在change事件过程中使用replace函数可以实现删除空格、替换中文逗号为英文逗号、删除两个连续逗号的操作;使用Clean函数可以清除非打印字符[12]126;在exit事件过程中组合使用right函数和left函数可以删除首尾英文逗号。

在change事件过程中可以不用循环语句来删除数量大于两个的连续逗号，这是由于当文本框内容发生变化时change事件便会触发，变化一次触发一次，在不断触发过程中即可实现删除多个连续逗号的目的。

4）多条件汇总页面控件事件过程。该页面文本框的change 事件过程中，与“数据整理”功能区有所不同。“输入汇总工作表名称”标签对应的文本框除了不能出现空格和非打印字符外，再无其他要求，只需使用replace函数和Clean函数便可实现;“输入汇总条件列”标签对应文本框要求输入作为汇总条件的列号（不区分大小写），可以是多列，中间为英文逗号隔开，不能出现英文字母和英文逗号以外的其他任何字符，只允许单个英文字母和英文逗号间隔出现，首尾也不能出现英文逗号，因此需要使用for循环语句检查文本格式，循环过程中，用Mid函数依先后顺序截取每一个字符，使用Like方法对比是否是英文字母或英文逗号，不是则提示错误并删除重新填写，Like语句为Mid（TextBox.Value， i， 1） Like "[a-zA-Z，]"[12]130。另外需要判断有没有连续的英文字母出现，出现则提示错误并删除重新填写，Like语句为TextBox.Value Like "*[a-zA-Z] [a-zA-Z]*"，涉及两个连续字符比较，不能使用change事件，需在exit事件过程中写入代码;“如需求和请输入求和列号”标签对应文本框只允许出现单个英文字母，只用一次like方法，判断不是则删除重写，Like语句为TextBox.Value Like "[a-zA-Z]"。

5）数据提取页面控件事件过程。命令按钮“选择模板文件”的Click事件过程中调用Selected_File（）函数选择模板文件，对应的文本框用于显示选择的模板文件路径;命令按钮“选择保存位置”的Click事件过程中调用Selected_Folder（）函数选择保存的文件夹位置，对应的文本框用于显示选择的文件夹路径;该功能区“模板中行标题与列标题交叉的单元格为”要求输入单元格名称，例如“C3”，只能是一个英文字母和一个数字的组合，标签对应文本框的exit事件过程中写入相应代码，使用的Like语句为 TextBox.Value Like "[a-zA-Z][0-9]"。

6）主程序过程。命令按钮“执行”的Click事件过程执行的是主程序，按照不同功能之间的逻辑关系，在程序编制过程中采用系统化设计，设计流程图如图4所示。

不同功能的实现思路如下：

①多条件分列功能实现。Excel自带的分列功能只能按固定长度或分隔符分列，多条件分列可按照多个标志字符串进行分列。解决思路：首先将分列功能区文本框中的字符串用split函数分割并存入数组，用for循环语句遍历需要分列的每一个单元格，内部嵌套for循环遍历数组的每一个元素，如果单元格字符串中存在元素，则将元素以及元素之前的字符串截取保存到第一列，元素之后的字符串保存到第二列，并退出循环数组，如果单元格字符串中无任何一个标志字符串，则将原单元格内容放置到前一列，所有循环结束，给分列后的两列添加标题。

②异位替换和同位替换功能实现。实践中，某些情况下需要按照单元格字符串中存在某一子字符串而将单元格内容做整体替换，或者将其他列对应行单元格内容做整体替换。这种特殊的替换方式Excel中自带的查找替换功能是无法实现的。具体实现方法：用split函数分割异位替换和同位替换的文本框中的字符串，并分别存入数组a1、a2和a3、a4。a1和a2对应下标的元素一个是查找值，一个是替换值，位置关系是一一对应的，数组a1中的元素互不相同，a2可以相同，这可以实现多个查找内容替换为同一个的目的;a3和a4的关系与a1和a2的关系是一样的。首先进行异位替换操作，用for循环语句遍历查找列的每一个单元格，内部嵌套for循环遍历数组a1每一个元素，如果单元格字符串内部查找到元素，则将替换列对应单元格的值用数组a2中对应元素替换。同位替换过程同异位替换类似，只是查找和替换单元格为同一个单元格。

需要说明的是同位替换和异位替换功能均是多个查找和替换操作同时在进行，且均是从单元格字符串的内部查找，如果查找字符串的长度同单元格字符串长度一致，作用同整体查找替换相同。

③多条件汇总功能实现。多条件汇总与Excel中的分类汇总功能一致，不同的是通过编程实现的多条件汇总数据可以直接进行分类输出，而Excel自带的分类汇总形成的数据需要一个个查找使用，无法满足自动化操作的需求。

实现思路：首先新建指定名称的用于保存汇总数据的工作表，用split函数分割指定条件列文本框中的字符串并存入数组;用for循环语句遍历原始数据工作表的每一行数据，内部嵌套使用for循环语句遍历数组，数组的每一个元素代表了汇总条件的一列，在遍历数组的过程中，用逗号链接所有汇总条件列的字符串，遍历数组结束后，链接好的字符串作为字典的键存入字典，键对应的值进行累加计数，在这一过程中链接好的字符串如果是重复的，则字典不会加入新的键，对应的值增加1。外层循环结束之后，将字典的键全部存入新建的汇总工作表中的第一列，字典的值存入第二列，之后将第一列数据用逗号分列，汇总完成。

④分表提取数据功能实现。这一功能是将多条件汇总后的数据按照不同条件提取到模板工作簿中不同的工作表。汇总工作表中的数据有一列包含提取到的模板工作簿中所有工作表名称，一列包含提取的行标题，一列包含提取的列标题，一列为提取的值。

编程思路：从汇总工作表第2行（第一行为标题）开始遍历以下各行数据，内部嵌套for循环语句遍历模板工作簿中每一个工作表，如工作表名称与汇总工作表名称所在列单元格中的值相同，则将数据提取到该工作表;确定好需要提取到的工作表之后，从该工作表中行标题和列标题交叉的位置向下搜索与汇总工作表中行标题所在列单元格内容相同的单元格，搜索到后记录该单元格行号，如果没有搜索到则将行标题值填充到末端的空白单元格中，并记录该空白单元格行号;再从交叉单元格向右搜索与汇总工作表列标题所在列单元格内容相同的单元格，同样记录到搜索到的单元格列号，没有搜索到则填充到末端的空白单元格，并记录空白单元格列号;最后将汇总工作表提取值所在列单元格的值填充到提取工作表中记录到的行号和列号交叉的单元格中，循环结束之后所有数据提取完毕。

⑤删除特定行和工作表序号重置功能实现。如果模板工作簿的工作表中预先填写了行标题，在提取完数据之后，有些行并没有提取到数据，合计所在的列数值为0，需要删除该行。遍历每一个工作表合计列的每一个单元格数据，如果值为0，则使用语句sh.Rows（i）.Select和Selection.Delete Shift：=xlUp可以删除（sh代表一个工作表对象，i为行号） [12]260。删除行操作之后第一列的序号是不连续的，还需将序号重置，在这一过程中，要确定填写序号的起始行和结束行位置，起始行是提取数据插入单元格的下一行，结束行每一张工作表删除的行数不一，位置也是不同的，解决思路是从第一列数据的最后一行向上搜索，遇到单元格数据为数值时该行即为需要填写序号的最后一行，确定好起始行和结束行后便可循环修改序号，实现序号重置。

在主程序的编写过程中，根据窗口中输入的参数情况判断是否执行某一项功能，参数未设置情况下进入下一项功能，因此程序可以执行某一项功能，也可以按逻辑顺序执行某几项或全部的功能。

4 应用实例

选取甘肃开放大学2021年第一学期新生和毕业生数据进行测试，测试结果如表4所示。整个测试是在相同配置环境下进行的。通过数据自动化处理系统完成工作，所需时间仅为21分32秒，统计好的数据表按照指定位置保存，打开检查数据，各工作表格式完整，均实现了刪除合计数为0的行，序号全部重置。与原数据表核对，结果正确无误。人工操作与系统自动化操作对比数据如表2所示。表中人工操作时间是纯工作时间。通过分析表中数据，人工操作时间主要在数据整理、分表提取数据以及核对结果等过程中，而实践中完成整个工作的时间跨度通常在10天以上。对比两种处理方式，采用数据自动化处理系统效率非常明显，极大地节约了时间。

5 结束语

本文的应用案例中，同以往的工作方法相比，应用数据自动化处理系统使数据处理与统计工作效率显著提高，所需时间不到原来的1.5%，同时消除了以往繁复操作导致的错误，保证了数据处理的质量，有效缓解了工作人员的劳动强度。

实践表明，利用VBA技术开发的数据自动化处理系统功能强大，自动化程度高，可以实现需求，解决实践问题。该系统的功能也是通用的，可以应用到其他方面，具有一定的创新和应用价值。

参考文献：

[1] 王锦秀，康鲁豫，李育燕.基于Word VBA的表格编校质量提升方法[J].编辑学报，2021，33（3）：322-326.

[2] 崔玉洁，文娟，廖坤，等.基于Word VBA技术的一键智能编校系统[J].编辑学报，2018，30（6）：624-626.

[3] 刘超，莫东旭，薛小蒙，等.基于Visual Lisp和VBA的爆破辅助设计系统研究与应用[J].矿业研究与开发，2020，40（9）：149-153.

[4] 王乔，窦延宝，石立华，等.应用Excel VBA编制煤田钻孔综合成果整理程序[J].煤田地质与勘探，2016，44（1）：27-30.

[5] 王乔，万单静，张博，等.应用Excel VBA编制煤田钻孔孔斜批量处理程序[J].煤田地质与勘探，2017，45（1）：41-44.

[6] 王淏，亢娟娜.Excel VBA在测评成绩计算中的应用[J].办公自动化，2021，26（2）：26，63-64.

[7] 朱西敏，韩爱庆，张未未，等.基于Excel VBA的试卷质量分析与评价系统[J].智能计算机与应用，2020，10（3）：246-249，255.

[8] 刘庭立，陈雪泉，张文治，等.高校基于Excel Vba考试系统的研究[J].广东职业技术教育与研究，2020（2）：46-47.