浅谈使用Excel函数制作多班级成绩分析系统

摘要：本文利用Excel函数及Excel VBA设计制作了多班成绩分析系统，应用此程序可以使教师的成绩分析工作事半功倍。

关键词：Excel VBA；Excel函数；成绩分析

Multi-class Performance Analysis System of Discussion on Using Excel Functions Production

Wang Xiaolin

(Dalian Xingang Clearance Technical School,Dalian116036,China)

Abstract:By using Excel functions and Excel VBA design produced a multi-class performance analysis system,apply this procedure to the results of the teachers to work more with less.

Keywords:Excel VBA;Excel function;Performance

一、设计思路

（一）合并数据。图1和图2为“校分”导出的两个工作表。合并的方法是将“校分”导出的Excel文件放在一个文件夹内（一个班级是一个Excel文件），这样方便用VBA命令打开，但文件名不能包括“统计”二字，因为VBA存放的文件名为“统计.xls”，若含有则不能被VBA打开。

图1 “校分”导出的数据工作表1图2“校分”导出的数据工作表2

下面是合并文件的程序代码，可以参见注释，如果不熟悉代码可以参考相关的VB或VBA书籍。

Sub combine()

Sheet1.Range(Cells(2,1),Cells(1000,15)).Clear

On Error Resume Next

Application.ScreenUpdating＝False

fn＝Dir(ActiveWorkbook.Path＆“\*.xls”)

Do

If InStr(1,fn,“统计”)＝0 Then

Workbooks.Open ActiveWorkbook.Path＆“\”＆fn

student＝Workbooks(fn).Sheets(2).[A1].CurrentRegion.Rows.Count－2

num＝ThisWorkbook.Sheets(1).[A1].CurrentRegion.Rows.Count

ThisWorkbook.Activate

Range(Cells(num＋1,1),Cells(num＋student,1)).Cells.Value＝Workbooks(fn).Sheets(1).Cells(4,6)

Range(Cells(num＋1,2),Cells(num＋student,2)).Cells.Value＝Workbooks(fn).Sheets(1).Cells(4,1)

Range(Cells(num＋1,3),Cells(num＋student,3)).Cells.Value＝Workbooks(fn).Sheets(1).Cells(4,5)

Workbooks(fn).Sheets(2).Activate

Range(Cells(3,2),Cells(student＋2,13)).Copy ThisWorkbook.Sheets(1).Cells(num＋1,4)

Workbooks(fn).Sheets(1).Activate

Range(Cells(7,1),Cells(7,11)).Copy ThisWorkbook.Sheets(2).Cells(2,2)

Workbooks(fn).Close

End If

fn＝Dir

Loop Until Len(fn)＝0

End Sub

至此，多个数据文件就合并到一起了，如图3所示。

（二）总体分析。合并好数据之后就要对合并的数据进行分析，为了条理清楚，又新建了一个工作表，如图4所示。

图4本文设计的第二个工作表

在此工作表中阴影部分为需要统计的量，即是不变的，其余部分都是变量。

第一行是大题的个数，没有公式；第二行是各大题的分值，来源于数据的第一个工作表，具体见代码；第三行是统计的平均分，公式为“＝AVERAGE(合计选E2:E927)”，这是B3的公式，从C3到L3可以用填充柄自动填充，由于引用的数据在第一个工作表，所以要表明地址，即在前面加上“合计选”；第四行是得分率，公式为“＝B3/B2”；第五、六行是最值，依次是“＝MAX(合计选E2:E927)”和“＝MIN(合计选E2:E927)”；第七行是出现频率最高的分值，公式为“＝MODE(合计选E4:E929)”；第八行是相对标准差，公式为“＝STDEV(合计选E5:E930)／B3”；第九行和十行分别表示偏斜度和峰值，是来分析分数的正态分布情况，具体可以参照手册。

从C15到C24是各分数段学生的个数，以前的文献中多采用countif函数处理，这里采用更为简便的数组函数，公式为“｛＝FREQUENCY(合计选O2:O1000,A15:A24)｝”，由于是数组函数，所以输入完公式后，要按Ctrl＋Shift＋回车。

最后就是直方图，用的数据就是B14:C24。

这样整体分析工作表就做好了，以后只要将“合并”工作表的数据重新采集，那么“总体分析”工作表的数据和直方图就随之改变。但是还有一个问题就是本软件默认可以分析10道题，由于有时不是10道，那就会造成像图4中第5到第10题那样的效果，数值为“0”或除数为“0”，这显然应该进一步改进。还有就是第15到18行，有的分数段没人，所以就是“0”，从而造成直方图中也没有人，而且还使此分数段在图中占有一个位子，使图形偏离中心。要解决这两个问题可以采用隐藏行或列的方法具体代码如下。

Sub hid()

Sheet2.Activate

Columns.Hidden＝False

For ic＝2 To 11

If Cells(2,ic).Value＝0 Then

Columns(ic).Select

Selection.EntireColumn.Hidden＝True

End If

Next ic

Rows.Hidden＝False

For ic＝15 To 24

If Cells(ic,3).Value＝0 Then

Rows(ic).Select

Selection.EntireRow.Hidden＝True

End If

Next ic

End sub

这样就完成了成绩分析的工作。

二、结束语

本文利用Excel函数及Excel VBA设计制作了多班成绩分析系统，应用此程序可以使一门课程的分布情况一目了然，让教师进一步了解学生对教学内容的掌握，同时还可以对下一学期的教学工作具有一定的指导作用。