基于ExcelVBA实现自动成绩分析的实践

摘 要：成绩分析是课程考核工作的必要环节，科学有效的成绩分析建立在教育统计学基础上，常用的成绩分析方法有频数分析法、集中量分析法、差异量分析法。然而在实际工作中，许多教师没有教育统计学基础理论，不了解数理统计分析方法，而且手工计算过程纷繁复杂，不仅费时费力，而且容易出错，难以实现成绩分析的高效性和准确性。文章从工作实际出发，以Excel软件为开发平台，采用VBA技术自定义解决方案，针对课程考核结果进行自动成绩分析。

关键词：ExcelVBA开发；自动成绩分析；高效办公

中图分类号：TP319 文献标志码：A 文章编号：1673-8454（2014）04-0065-04

课程考核是检验教学质量的重要途径和关键手段，考核成绩作为量化评价指标，能够有效反映学生的学习情况和教师的教学效果。系统科学的成绩分析，不仅能够客观公正的反映学生的学习状态和知识技能的掌握程度，而且能够引导教师发现教学过程中存在的不足和主要问题，帮助教师正确认识问题背后的关键影响因素，促使教师不断改进教学方法，切实提高教学质量。

一、成绩分析方法

常用的成绩分析方法有频数分析法、集中量分析法、差异量分析法。频数分析法包括等级频数及频数分布曲线，集中量分析法包括算数平均数、中位数、众数，差异量分析法包括全距、平均差、标准差。教师手工计算上述参数存在一定难度，一方面许多教师没有教育统计学基础知识，不了解数理统计分析方法，另一方面手工计算过程纷繁复杂，不仅费时费力，而且容易出错，难以实现成绩分析的高效性和准确性，必须采用技术手段解决这一问题。考虑到《课程成绩登记表》普遍采用Excel软件，笔者以此为平台进行二次开发，采用VBA开发技术自定义解决方案，针对课程考核结果进行自动成绩分析。

二、开发工具

VBA（Visual Basic for Applications）是微软公司开发的一种通用控制Visual Basic编程语言，可以方便地调用和定制主应用程序对象。VBA内嵌在Microsoft Office（Word、Excel、Access等）软件中，用来扩展Microsoft Office应用程序功能。用户可以根据需要编写VBA宏代码，创建自定义解决方案，自动完成重复工作，提高工作效率，实现高效办公。VBA的开发和运行依赖主应用程序，本课题利用Excel中内嵌的VBA实现自动成绩分析。[1]

三、设计目标

在《课程成绩登记表中》设置“图表生成”按钮，鼠标单击后出现选择对话框，显示“请单击某一单元格”，选择某列数据标题单元格作为分析对象，确定后自动生成“成绩分析”工作表，自动统计并显示总人数、平均分、中位数、标准差、最高分、最低分、各分数段人数及百分比、峰度、偏度，自动生成柱状图和正态分布曲线，如图1所示。

四、功能设计

成绩分析针对某个班级的某门课程进行，VBA宏程序由数据准备、数据统计和曲线图生成三个模块构成。

数据准备模块包括原始成绩录入、数据列选择、分析页面生成三个环节。任课教师首先将原始成绩录入到《课程成绩登记表》中，之后点击“图表生成”按钮，在选择对话框中确定某列数据作为分析对象，完成数据列选择，单击“确定”，程序根据用户指定单元格所对应的数据列自动生成“成绩分析表”。

数据统计模块和曲线图生成模块是系统设计的核心部分，系统执行VBA宏程序，自动进行数据计算并显示统计结果，同时自动生成柱状图和正态曲线图。

1.数据准备

原始数据录入由任课教师手工完成，这里不再阐述。

原始成绩录入后，鼠标单击“图表生成”按钮，系统自动弹出“请选择”对话框，其宏代码如下。

err： On Error Resume Next

Set myCell = Application.InputBox（Prompt：=" "， Title：="请选择"， Default：="请单击红色字体中的某一单元格"， Left：=800， Top：=500， Type：=8）

If myCell Is Nothing Then Exit Sub

If myCell.Row <> 4 Then

MsgBox "单元格输入错误，请重新选择"

GoTo err

ElseIf myCell.Column > 8 Then

MsgBox "单元格输入错误，请重新选择"

GoTo err

ElseIf myCell.Column < 3 Then

MsgBox "单元格输入错误，请重新选择"

GoTo err

ElseIf myCell.Value = False Then

MsgBox "请单击红色字体中的某一单元格"

GoTo err

如果正确选择了红色区域的某一单元格作为分析列，单击“确定”后系统自动进入数据统计模块。考虑到操作过程中可能出现错误选择单元格的实际情况，为了提高程序的容错性，增加了错误处理程序，当操作人员不慎选择了非指定区域的其他单元格时，系统自动弹出错误提示框，如图 2所示。

成绩分析页面（sheet2）生成部分的宏代码如下。在生成成绩分析页面（sheet2）的同时，确定成绩登记表（sheet1）中的数据统计范围。

ColumnNo = myCell.Column

Set myCell_1 = Sheet1.Range（Cells（5， ColumnNo）， Cells（34， ColumnNo））

Set myCell_2 = Sheet1.Range（Cells（5， ColumnNo + 9）， Cells（34， ColumnNo + 9））endprint

Set ReturnRange = Sheet2.Range（Cells（2， 12）， Cells（52， 12））

Sheet2.Select

Sheet2.Activate

Sheet2.Visible = xlSheetVisible

2.数据统计

数据统计模块包括总人数（CountA）、平均分（Sum/ CountA）、中位数（Median）、标准差（StDev）、最高分（Max）、最低分（Min）、峰度系数（Kurt）、偏度系数（Skew）。

总人数为点计数据，将学生人数作为点计结果。

平均分（算术平均数） 和中位数属于集中量数据，用来描述数据集中趋势的统计量，能够反映成绩分布的一般水平。平均分反映的是一组成绩的总体平均水平。平均分的理想数值为75分，70-80分之间为合理范围。中位数像一条分界线，将数据分成前半部分和后半部分，用来代表一组成绩的中等水平。

标准差为差异量数据。可以直接地、概括地、平均地描述数据变异的大小，对于同质性数据来说，标准差越小，数据的变异程度越小，数据越整齐，分布范围越集中，反之标准差越大，数据的变异程度越大，数据越参差不齐，分布范围越广。通常情况下，一组成绩数据的标准差应该在7-10之间为合理范围，标准差小于5， 说明分数过度集中，试题区分度较低，标准差大于15，说明分数过度分散，试题区分度过高。

最高分和最低分为度量数据，既有相等单位又有绝对零点。

峰度系数和偏度系数表示频数分布的形态。偏度表示正态分布曲线的对称性，-1.5<偏度<+1.5，符合标准正态分布的对称性，偏度>+1.5，为正偏或右偏（长尾在右），说明试题难度偏高，偏度<-1.5，为负偏或左偏（长尾在左），说明试题难度偏低。峰度表示正态分布曲线的陡缓程度，-1.5<峰度<+1.5，符合标准正态分布的陡峭性。峰度>+1.5，为陡峭型分布，说明试题区分度过低，分数分布过于集中，峰度<-1.5，为平坡型分布，说明试题区分度较高，但分数之间的差异偏大。

数据统计模块的宏代码如下：

Sheet2.Cells（21， 7） = WorksheetFunction.CountA（myCell_1， myCell_2）

Sheet2.Cells（21， 4） = WorksheetFunction.Sum（myCell_1， myCell_2） / WorksheetFunction.CountA（myCell_1， myCell_2）

Sheet2.Cells（22， 4） = WorksheetFunction.Median（myCell_1， myCell_2）

Sheet2.Cells（23， 4） = WorksheetFunction.StDev（myCell_1， myCell_2）

Sheet2.Cells（24， 4） = WorksheetFunction.Max（myCell_1， myCell_2）

Sheet2.Cells（25， 4） = WorksheetFunction.Min（myCell_1， myCell_2）

Sheet2.Cells（26， 4） = WorksheetFunction.Kurt（myCell_1， myCell_2）

Sheet2.Cells（27， 4） = WorksheetFunction.Skew（myCell_1， myCell_2）

除了上述统计数据之外，还要计算各分数段人数及百分比，将分数划分为优秀（90-100）、良好（80-89）、一般（70-79）、及格（60-69）、不及格（60以下）五个等级，统计不同等级区间的人数分布情况，其宏代码如下：

Sheet2.Cells（22， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 89.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 89.5"）

Sheet2.Cells（22， 8） = Sheet2.Cells（22， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（23， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 79.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 79.5"） - Sheet2.Cells（22， 7）

Sheet2.Cells（23， 8） = Sheet2.Cells（23， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（24， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 69.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 69.5"） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）

Sheet2.Cells（24， 8） = Sheet2.Cells（24， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（25， 7） = WorksheetFunction.CountIfs（myCell_1， ">= 59.5"） + WorksheetFunction.CountIfs（myCell_2， ">= 59.5"） - Sheet2.Cells（24， 7） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）endprint

Sheet2.Cells（25， 8） = Sheet2.Cells（25， 7） / Sheet2.Cells（21， 7）

Sheet2.Cells（26， 7） = Sheet2.Cells（21， 7） - Sheet2.Cells（25， 7） - Sheet2.Cells（24， 7） - Sheet2.Cells（23， 7） - Sheet2.Cells（22， 7）

Sheet2.Cells（26， 8） = Sheet2.Cells（26， 7） / Sheet2.Cells（21， 7）

3.曲线图生成

曲线图生成包括直方图生成和正态曲线图生成两部分。[2]

绘制直方图的方法是：首先将0-100分以间隔2分的级差分成50个等级，分数段设置为0、2、4、6、8、10……96、98、100，在VBA宏程序中使用FREQUENCY函数统计各分数段人数，宏代码如下：

If ColumnNo = 3 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！C$5：C$34，K$2：K$52）+FREQUENCY（成绩登记表！L$5：L$34，K$2：K$52）"

ElseIf ColumnNo = 4 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！d$5：d$34，K$2：K$52）+FREQUENCY（成绩登记表！m$5：m$34，K$2：K$52）"

ElseIf ColumnNo = 5 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！e$5：e$34，K$2：K$52）+FREQUENCY（成绩登记表！n$5：n$34，K$2：K$52）"

ElseIf ColumnNo = 6 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！f$5：f$34，K$2：K$52）+FREQUENCY（成绩登记表！o$5：o$34，K$2：K$52）"

ElseIf ColumnNo = 7 Then

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！g$5：g$34，K$2：K$52）+FREQUENCY（成绩登记表！p$5：p$34，K$2：K$52）"

Else

Sheet2.Range（"l2：l52"）.FormulaArray = "=FREQUENCY（成绩登记表！h$5：h$34，K$2：K$52）+FREQUENCY（成绩登记表！q$5：q$34，K$2：K$52）"

End If

完成各分数段人数统计后，在Excel成绩分析表界面中使用SERIES函数绘制直方图，SERIES函数具体形式为“=SERIES（成绩分析！$Q$1，成绩分析！$K$2：$K$52，成绩分析！$L$2：$L$52，1）”，如图3所示。

绘制正态曲线图的方法是使用NORMDIST函数，该函数用于返回指定平均值和标准差的正态分布函数。平均分（平均值）和标准差在前面已经获得，将其带入NORMDIST函数生成正态曲线图。NORMDIST函数的具体形式为：“=NORMDIST（K2，D$21，D$23，0）”，其中D$21单元格的内容为平均分数值，D$23单元格的内容为标准差数值。

完成正态分布函数计算后，在Excel成绩分析表界面中仍然使用SERIES函数绘制正态分布图，SERIES函数具体形式为“=SERIES（成绩分析！$S$1，成绩分析！$K$2：$K$52，成绩分析！$S$2：$S$52，2）”，如图4所示。

五、结束语

系统实现了设计目标和功能要求，以Excel软件为平台进行二次开发，按照成绩分析工作的实际要求编写宏代码，开发成果具有较强的实用性和适用性。界面简洁清晰、操作简单，运行速度快，实现了成绩分析工作的科学性、高效性和准确性。

参考文献：

[1]李萍.利用ExcelVBA实现考场清册中照片的批量插入[J].中国教育信息化，2013（4）：81-83.

[2]马莹莹.用Excel画直方图和正态分布图[J].科技信息，2011（21）：269-270.（编辑：鲁利瑞）endprint