巧用Excel VBA编程进行成绩分析统计

本软件下载地址

小学初高中考试成绩统计分析管理系统

http://wenku.baidu.com/view/4c148eea647d27284a735167.html

高中考试成绩统计分析管理系统（有文理班）

http://wenku.baidu.com/view/c6c8c3062cc58bd63086bd60.html

 

【摘要】为了保证学生成绩录入和处理的公平性，为了防止考试改卷中的不正当竞争，学校一般都会将同一年级的学生打乱，混合编班进行测试和录入分数。为了提高数据分析的效率，避免不必要的重复工作，本文利用Excel VBA编程方法轻松实现了不破坏原始表，且不受班数和人数限制的前提下，及时准确地对成绩进行处理和分析。

【关键词】教学成绩 统计 分析 Excel VBA

在学校的教学活动中，通过统计分析学生的考试成绩，以便确定学生的接受效果和教学的方法是否得当，为教学方法的改进提供依据，是每一位任课教师所要做的重要教学工作之一。有了Excel，我们可用不用躬着身、驼着背、拿着计算器一个一个算着学生的成绩了，大大提高工作效率、规范处理过程、减少差错。

为了保证学生成绩录入和处理的公平性，为了防止考试改卷中的不正当竞争，很多学校采用全年级混合编班考试，而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，按座位号顺序(每本考卷的自然顺序)统一录入成绩。也就是说，把同年级多个班的学生成绩统一录入到一个Excel工作表中，再分析统计出全年级各科成绩。这种情况下，想在不破坏原始工作表的情况下统计分析各科各班成绩，采用排序、筛选等人工干预完成统计是一件很枯燥、很繁琐、工作量很大的事。那么，能不能用相对比较简单的办法来解决问题呢?答案显然是肯定的。

Excel是微软公司的Microsoft office的组件之一，它可以进行各种数据的处理、统计分析，在学校工作中有着广泛的应用。例如花名册、座位表、登分表等的电子文档都是使用它来创建的。Excel不仅具有强大的制表功能，同时还内置了系统开发工具VBA。VBA是指Visual Basic for Application，它是在Office中广泛应用的宏语言，可以直接对Excel对象进行编程，从而提高Excel的利用效率。使用它可以增强Excel的自动化能力，使用户更高效地完成特定任务。因此，笔者空闲时用VBA编了个程序，轻松快速地统计分析成绩（如个人总分，按班或年级排名、总分、平均分、最高分、最低分、名次或分数段人数、年级或班级前XX名等，并可输出打印），不破坏原始工作表，不受班数和人数限制，可自定义作弊、缺考、0分是否参与统计分析，经实际应用，操作简单，方便实用。

程序分为排名、统计、查询、打印四大模块，设计完成后，工作人员只需导入原始成绩表：第一行为列标题且有班级一列和不合并单元格即可，其它不受任何限制（如存在空行或空列；可以任意增加删除科目；可以任意增加删除辅助列如：学号、年级、座位号等；而且各列位置任意；不受班数、人数、科目数限制，行列不受限制等，如图1）。在设置工作表中设置各科满分、优秀线、及格线、低分线、分数段、名次段、任课教师后，即可进行成绩统计、查询及打印输出。

图1

一、个人总分计算

因原始成绩表不限制科目数且各列位置任意，而且还想任意搭配科目计算总分，如：文综（政史地）、理综（理化生），为了方便缺考、作弊的统计，原始成绩表录入-2代表缺考，计0分、-1代表作弊，计0分，不能用自动求和公式SUM计算总分。本软件按照设置的总分科目采用循环方式计算总分。

图2 总分科目

代码：利用VBA数组加快代码运行速度

Arr = Worksheets ("原始成绩").UsedRange.Value    '将原始成绩表写入数组

Arr1 = Worksheets ("总分设置").UsedRange.Value   '将总分设置表写入数组

For y = 2 To UBound(Arr1 ,2)  '总分科目循环

    ReDim Preserve ArrZF(1 To R, 1 To i)

    ArrZF(1, i) = Arr1(1, y)   '写入总分科目

    For x = 2 To UBound(Arr,1) '循环学生

        For j = 2 To UBound(Arr1,1)  '循环单科科目

            For k = 1 To  UBound(Arr,2) '列标题

                If Arr(1, k) = Arr1(j, y) And Val(Arr(x, k)) > 0 Then  '该科成绩大于0分

                    ArrZF(x, i) = Val(ArrZF(x, i)) + Val(Arr(x, k)) '计算总分

                End If

             Next

         Next

     Next

Next

运行结果如图3：

图3

二、排名

原始成绩表有可能录入-1、-2但又要按0分排名，除以年级为单位排名外还要能以班为单位排名，因此本软件不使用工作表排名公式Rank进行排名，而是将排序科目先降序排序再按顺序写入名次，排名操作窗口如图4。