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

摘要：高校教务管理中数据处理是一项重要的基础工作。文章针对教务管理中数据处理工作量大，周期性重复，数据复杂，耗时长等问题，利用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自带的分类汇总形成的数据需要一个个查找使用，无法满足自动化操作的需求。