应用Excel VBA建立教学工作量计算系统分析

摘　要：摘要：本文主要阐述应用 excel vba 技术开发用于计算工作量的系统，并实现易于使用、准确录入、高效汇总等功能。

关键词：关键词：Excel ；VBA ；教学工作量

中图分类号：TP39   文献标识码：A    文章编号：
    1. 引言
    高校教师的教师的工作量统计工作比较繁重，而且各个高校间对于教师教学要求不一样，教师的教学工作量计算的方法也层出不穷。部分高校使用手工进行统计，缺点是工作量过大，录入信息不规范、计算容易出错。对此早期笔者曾经使用web 、java、oracle技术开发一个专门的教师工作量管理系统，应用于我院的教师教学工作量的统计，但是运行后发现效果不佳，主要原因是每一年学院对于教师教学要求不同，计算方法有所变化导致系统维护代价过大。结合其他院校这方面的工作情况，笔者应用Excel VBA技术开发用于计算工作量的系统。这样的系统优点在于Excel文件更改更为简单，维护代价小，而另一方面Excel是最为广泛的办公软件，对于大部分没有计算机技术背景的教学秘书、教师等来说，最为合适。
    2. 系统分析与设计
    我院的教师的教学工作量主要分为授课与非授课工作量：授课工作量主要包括理论教学、实验实训、实习指导、课程设计论文指导等等；非授课工作量主要包括指导教学范畴训练、指导教学新手、监考、出卷、编写教学大纲、编写人才培养方案、实验室建设、新专业申报等等。
   为了便于对教师教学工作量的审查、核算的工作，在教师工作量申报统计表，按照教师工作量的一个项目对应一个表的原则划分15个工作表，工作表中的栏目对教师工作量的发生时间、地点、参与人数，以及成果进行详细的描述。这样可以更好把握每一个教师的工作量分布情况。
    另外再增加两个工作表分别是教师各项工作量汇总表和基础数据表：教师各项工作量汇总表用来汇总教师的各个项目的工作量并统计总工作量；基础数据表作为对系统的项目中的下拉列表、自动计算人数计算的数据支持。
    3. 系统主要功能以及实现的方法
    3.1自动匹配录入信息。
    在我院的教师工作量计算办法中，主要涉及人数、人数系数的计算，较为复杂和容易出错。所以在教师工作量统计表中，应避免教师直接输入人数这类容易出错的数据。例如在教师理论课工作量计算表中，教师选择班级，选择课程名，填入实际的周数和周学时数，其他的班级人数、人数系数、合班情况、工作量，系统根据教师的提供的数据，自动计算出工作量。

 
                                                                             图3-1
    例如：在图3-1中，这是教师理论课工作量的工作表，其中部分深颜色的栏目：教师编号、课程代码、课程总学时、班级人数、人数系数、工作量是系统自动生成数据。自动生成的数据主要实现两个功能。
    第一个是检查录入数据的正确性。例如录入教师姓名是系统中存在的，则显示教师编号，否则显示错误。课程代码也是如此。
    第二个是生成统计基础数据。
    如：班级人数、人数系数的计算。
    以上功能主要应用VLOOKUP()函数，下面列出根据班级名称生成班级人数的函数公式：
    =IF(F7<>"",G7,IF(D7<>"",VLOOKUP(D7,Sheet2!$A$2:$B$203,2,FALSE)+IF(E7<>"",VLOOKUP(E7,Sheet2!$A$2:$B$203,2,FALSE),0),IF(E7<>"",VLOOKUP(E7,Sheet2!$A$2:$B$203,2,FALSE),0)))
    3.2 统计教师各项目的工作量
    实现教师15个项目工作量工作表数据汇总到一个工作表，方便统计教师各项工作的分布情况。
    伪代码如下：
    1、定义结构体Gzlei，包括教师姓名、工作量、工作量所在项目编号
Type Gzllei
 name As String
 kss As Single
 index As Integer
End Type
    2、读取“表1”到“表15”各项表的数据
Const str2 As String = "C412:D811"
Const numOfsheet = 15
Dim gzlbg(1000) As Gzllei
Dim myarr1(), myrange As Range
Dim ss As Integer
ss = 0
For i = 1 To numOfsheet 
    Set myrange = Worksheets("表" + CStr(i)).Range(str2)
    myarr1 = myrange
    For j = 0 To 400 - 1
        If myarr1(j + 1, 1) <> "" Then
            gzlbg(ss).name = myarr1(j + 1, 1)
            gzlbg(ss).kss = myarr1(j + 1, 2)
            gzlbg(ss).index = i
            ss = ss + 1
        End If
    Next j
Next i
    3、然后进行排序，最后合并输出教师各项数据到汇总表如图3-2。
 
                                                                    图3-2
    3.3 汇总各教师的工作量数据。
    教学工作量统计的工作流程是由教师各自填报一个工作量统计表文件，再到系汇总审核，最后由教务处对系上报的数据进行汇总审核。
   在过去采用人工进行汇总时，教学秘书需要是将一个一个的教师工作量统计表文件的数据，都复制到一个总教师工作量统计表文件。这个简单、重复的粘贴和复制的汇总工作，往往是最容易出错的环节，教学秘书也是疲于汇总工作量，更谈不上对各个教师工作量的审核。
    系统有必要实现从一个个教师的工作量统计表文件中抽取数据，导入一个工作量统计表文件（一个系所有的教师的工作量数据）的数据复制的功能。将教学秘书从这种简单、重复、繁多并且容易出错的的中间环节解脱出来，更多的精力放在对教师工作量的审核工作上来。
    本系统的这部分功能采用VBA代码实现。下面介绍汇总“教师工作量统计表”表1的代码，其他表的汇总类同。
    Updating = False
    Path1 = "D:教师工作量汇总" '源文件存放的路径，根据实际修改
    f = Dir(p & "*.xls")
    Dim arr(0 To 1000, 0 To 9) As String
    Dim count As Integer
    count = 0
    Do While f <> ""
        MsgBox "" + f
        (p & f)
        Worksheets("表1").Activate
        '获取excel文件中表一的数据给二维数组赋值
        For row1 = 7 To 26
        For col1 = 3 To 11
        arr(row1-7 + 20 * count, col1 - 3) = Cells(row1, col1)
        Next col1
        Next row1
        count = count + 1
       
        f = Dir
    Loop
    Windows("系汇总表").Activate
Worksheets("表1").Activate
    For row2 = 0 To 1000
    For col2 = 0 To 9
    Cells(row2 + 7, col2 + 3) = arr(row2, col2)
    Next col2
    Next row2
  
    在以上代码，主要有以下步骤，第一步：找出指定目录下所有excel文件。第二步：逐个打开excel文件工作表“表1”，并把“表1”的指定区域的数据全部复制到数组arr；第三步：将arr数组的数据，输出到“系汇总表”中的“表1”中。
    4．系统一些注意的事项
   4.1保护公式
    为防止单元格中创建的公式被误删或更改，可以先选中有公式的单元格，在菜单“格式”中选择“单元格”，选择“保护”中的“锁定”前打勾，然后单击菜单“ 工具”，选择“保护”——“保护工作表”，输入密码。
    4.2 空格引起的错误
    在系统合并同一名教师的工作量，可以以教师编号或者教师姓名为根据字段。一般来说，教师编号具有唯一性，而名字可能有重名。①无论选择哪一个字段，这些数据应该注意空格问题，系统合并同一教师工作量时，如果在根据字段出现空格会引起系统逻辑统计错误，因为系统会当作两个不同的教师。
    为防止这类错误，可以在教师工作量各项统计表中，多增加一栏辅助信息，例如教师编号或姓名：让系统实现根据姓名自动生成教师编号，或者教师编号自动生成教师姓名。
    5. 结语
    Excel是一个强大的办公软件，一些简单计算系统可以使用Excel中的函数和Excel的VBA进行设计，但是区别于一般的软件系统设计。②Excel对于计算特别擅长，集成许多统计的功能，不需要特地去编写复杂的代码，直接应用就可以。

参考文献：
[1] 李凤岭．EXCEL中的“分类汇总”[J]．科技与企业，2011(16);
[2] 张爽．浅谈Excel在人力资源中的初级应用.[J]．商情，2011(42).