利用Excel统计分析考试成绩

利用Excel统计分析考试成绩

姚上村

摘要本文通过ScoreAna学校成绩统计分析程序，介绍如何在Excel中制作个性化的自定义工具栏按钮，以及如何快速分析处理大批量的数据等。

关键词 Excel VBA，个性化的工具栏按钮，排名次，算法一、Excel VBA简介Excel的应用范围很广，如:建立员工工资表，人事档案管理，股市行情分析等等，它非常适合对小型的数据进行快速分析处理，并生成报表。Excel在微软的Office办公套件中最先支持VBA，从而实现Office应用程序自动化，或创建自定义的解决方案。

打开Excel后，只要按Alt + F11就可以进入VBA集成开发环境。要想快速地熟悉VBA，最好的方法莫过于在Excel中录制一个完成某项任务的宏，然后在集成开发环境中查看该宏的VBA源代码。ScoreAna程序代码有一部分就是在录制宏的基础上修改而成的。

VBA中所有可执行语句都要包含在某个子程序中。这些子程序可以分为三类:一类是Sub过程，如宏，用来完成某个任务;第二类是Function函数，它向调用者返回一个值，如ScoreAna中的Function ClassPlace(cell)返回该学生的班级名次;第三类是事件，如Private Sub Workbook_Open()，当打开工作簿时，会激活该事件子程序，完成特定的任务。

二、个性化的自定义工具栏按钮

我们可以在Excel中自定义工具栏，添加工具栏按钮;通过录制宏，可以获取VBA代码。但按钮上的图形只能选择office自带的按钮。您可能使用过Acrobat 的office插件，只要点击一个按钮，就可以将office文档转换成PDF文档～它的按钮当然不是office内置的，真漂亮。如何在工具栏上使用自己制作的按钮,请看

ScoreAna中创建个性化工具栏的过程BuildCustomToolbar()的部分代码: Sub BuildCustomToolbar()

Dim oCmdBar As CommandBar

Dim btnNew As CommandBarControl

DeleteToolbar ‘调用DeleteToolbar子过程，如果ScoreAna工具栏存在，先删除它

'生成ScoreAna工具栏

1605692 第1页共 4页

Set oCmdBar = CommandBars.Add(Name:="scoreAna")

oCmdBar.Visible = True

With oCmdBar

'添加“设置参数”按钮

With .Controls.Add(msoControlButton)

.Caption = "设置参数" ‘按钮标题

.OnAction = "setPara" ‘单击该按钮时，执行的子过程名

.Tag = .Caption ‘按钮标签

‘.FaceId=80 ‘注释掉，不使用office自带的按钮

‘从Sheet1工作表复制名为”Picturepara”的图形

Worksheets("Sheet1").Shapes("Picturepara").Copy

.PasteFace ‘粘贴作为该按钮的图形

End With

…….

End With

End Sub

然后在Workbook_Open()事件中调用BuildCustomToolbar()过程，就可以象Acrobat插件一样，在打开Excel文档时看到自己的个性化工具栏了～～Private Sub Workbook_Open()

'最大化窗口

Application.WindowState = xlMaximized

'停止自动计算，及保存前计算

Application.Calculation = xlCalculationManual

Application.CalculateBeforeSave = False

'创建自定义工具栏

BuildCustomToolbar

End Sub

请注意，Workbook_Open()事件过程要放在“ThisWorkbook”模块

中;BuildCustomToolbar()子过程则放在“模块1”或“模块2”等模块中。

三、统计分析学生成绩

包括计算每个学生的班级名次、年级名次，统计每学科平均成绩，各班分数段统计等。这其中的难点是为每个学生排名次。ScoreAna程序通过在工作表中调用函数ClassPlace(cell)和GradePlace(cell)，分别得到该生的班级名次和年级名次。

1605692 第2页共 4页

算法是程序设计的灵魂。ScoreAna通过以下两点改进，使得排名次算法的时间只需要1秒左右:一是将各学生的总成绩读入内存数组再排序，而不是直接对Excel单元格的值进行排序，因为Excel对单元格等对象的大量分析会消耗大量的时间。这也是时间缩减的主要因素。二是排序算法由原来的冒泡排序改为计数排序，使

2得算法的复杂度由原来的O(N)降为O(N)。这样即使是1亿个学生要排名次，也只需1秒。计数排序也使得实现“相同分数的学生并列名次”更简单。以下是ClassPlace(cell)函数的代码:

Function ClassPlace(cell)

Dim sheetname, CellName, i, j, place, cellValue, score, sheetObj, classCur

Static scores(99, 20000) As Integer ‘设置为静态数组，避免重复计算score = cell.Value

'总分为空或零，不排班级名次

If score = "" Or score = 0 Then ClassPlace = "": Exit Function

classCur = classNum(cell) ‘cell所在班级

If classCur < 1 Then ClassPlace = "": Exit Function ‘调用来自模板工作表class00，不排名次

'排名次，计数排序算法

'适用于总分为整数或带.5小数的，其它小数部分需要修正算法

‘最高分不超过20000,0.5,10000

If Not classLW(classCur) Then ‘cell所在班级尚未排名次

Set sheetObj = cell.Parent ‘cell所在工作表

For i = 0 To 20000

scores(classCur, i) = 0 ‘每个分数点(相隔0.5分)的学生数，重置零

Next i

For i = ScoreStartRow To MaxMember + ScoreStartRow - 1

cellValue = sheetObj.Evaluate(totalScoreCol & i).Value

'如果出现姓名为空，就意味着本班排名结束