Excel在成绩统计的运用

施得天

摘要：Excel作为现在最流行也是最强大的电子表格软件，拥有强大的统计功能。可以出色地完成成绩统计的工作。辅之以VBA程序可以快速地完成常规的成绩操作。

关键词：Excel；成绩统计；VBA；宏

中图分类号：TP311.1 文献标识码：A 文章编号：1009-3044（2018）19-0224-02

Abstract： Excel is the most popular and powerful spreadsheet software which has powerful statistical functions. The work of statistics can be accomplished well. With the VBA programming， the routine practices can be done quickly.

Key words： Excel； statistics of score； VBA； Macro

通过调查发现大部分非计算机专业的教师在使用Excel统计成绩、进行成绩分析的时候仍然采用手写成绩表的方式，后续的分析极为不方便。部分使用Excel处理的表格的教师在面对大量的数据时也会难免出现成绩手动录入错误。而通过Excel的一些进阶操作可以有效地规避错误并显著地提高成绩统计分析效率。本文就如何实现Excel 在成绩统计中的高效综合应用提出了一些见解。

1 模板

1.1 模板简介

Excel的模板文件（.xtml）可以将各种字体、内容、单元格、操作权限、计算函数的格式预置在工作簿中。它只有格式、主题，而没有具体的数据。相比每次新建工作簿的做法，使用模板可以减少相同工作的工作量，保证了每份成绩表的格式一致，方便后续的汇总，也能在一定程度上减少数据输入或格式错误。

1.2 创建Excel模板

1.2.1 成绩表字段数据格式

为了保证各成绩表格式一致，模板中应预置成绩录入的要求。下文中将每列的数据成为一个字段，每行的信息称为一条记录。格式最基本的要求就是字段的顺序即每列应该输入的信息。以高等数学的成绩表为例：大学开设不同课程的班级大多不是自然班，所以学生可能来自不同院系，不同专业。为了方便区分，学生个人信息要求填写学院、专业、姓名、学号加以区分并方便汇总。个人信息后的字段即为高等数学历次考试的成绩。

1.2.2 建立特定字段的联动菜单

对于一些特定的字段例如学院、专业，字段下的值只可能为固定的几个。为了减少相同的工作量并且防止手动输入时的人为错误。可以建立下拉菜单供快速选择。首先在工作表2中输入学院、专业可供选择的值。然后返回工作表1，在菜单中点击公式-建立名称管理器，新建名称：学院，引用位置为工作表2中的三个学院的单元格。再选中学院字段下的单元格，设置数据有效性，允许范围为序列，来源输入学院。确定后选择学院字段下的单元格就会自动出现选择菜单，大大减少了工作时间和出错的可能性。

对专业字段的下拉菜单的建立要比学院复杂一些。因为不同的学院下有不同的专业，所以专业的下拉菜单需要和学院字段联动。再次新建名称管理器，为每个学院各自新建名称，引用位置就是工作表中各学院下的专业所在单元格，新建如下：

新建完成后，选中工作表中的专业下的单元格，设置数据有效性，允许范围仍为序列。由于序列的值与学院的值有关，所以使用Indirect间接引用函数，使用方式为Indirect（$A3）（以上述成绩表模板为例），$A3即混合引用，指在专业字段的单元格位置改变的时候，引用的地址列数不变，行数随之变化。这样，专业单元格就会根据对应的学院的值和名称管理器的文件生成对应的下拉菜单。

1.2.3 成绩的数据有效性及条件样式

在各个考试成绩的字段下也可以使用数据有效性来规避一些错误。选中用于填写成绩的单元格，设置数据有效性，允许范围为整数并且大于0小于100。这样输入成绩时如果多输了一位，Excel就会弹出输入值非法的对话框。

成绩表中不及格的成绩一般都以红色标记，这也可以做到模板中。选中填写成绩的单元格，设置条件格式-突出选择单元格规则-小于，设置条件为小于60，格式为自定义-字体颜色设置为红色。这样输入成绩的同時，Excel就会自动标红低于60的成绩。

1.2.4 模板保护

模板的作用之一就是保证每份成绩表的格式的一致性。所以以模板创建的工作簿必须保证初始的格式不被破坏。Excel提供了保护工作表的功能以为了防止误操作改变模板预置的内容。先将预设置好的单元格格式改为保护，再在审阅-保护工作表中勾选允许编辑对象，输入保护密码。这样使用者就不会误操作更改被保护的单元格。

1.3 模板的使用

电脑上安装的Excel应用会在自己的根目录下建立模板文件夹template。所有在建立新的工作簿时调用的模板都保存在template这个文件夹里。将模板保存在template文件夹中，在打开Excel后选择新建-我的模板即可以保存的模板建立工作簿。

2 使用宏完成成绩的分析

2.1 Excel宏简介

宏（macro）是一种批量处理的称谓，它可以用一条或几条指令批量完成相同任务。教师在不同成绩进行分析时完成的工作本质上时相同的。这就可以使用宏代为完成。Excel中的宏可以保存在工作表中，在需要的时候进行调用就可以执行代码中的过程，辅助完成分析工作。

2.2 单一工作表的成绩分析

教师对于成绩最基本的分析是建立在一个完整的班级上的。比如在全体的基础上计算平均分，最高分，最低分，统计各分数段人数，及格率等数据。因为在相同的模板下，存放数据的单元格的位置是固定的。所以机器可以准确地找到所需的数据。在代码中编辑对特定单元格的操作，可以让宏代码按照要求在固定的单元格内计算出所需的数据。

ActiveCell.FormulaR1C1 = "=MAX（R[-19]C：R[-2]C）"

Range（"E22"）.Select

Selection.AutoFill Destination：=Range（"E22：K22"）， Type：=xlFillDefault

Range（"E22：K22"）.Select

Range（"E23"）.Select

…

由于各班的人数不同，不能每次在相同的地方生成统计数据，所以使用以下语句完成动态的统计。

ar = range（"e3：e" & Cells（Rows.Count， 2）.End（3）.Row）

这样，ar既存储了成绩的数据个数，又能表示成绩数据的单元格范围。在使用ar作为成绩数量时使用 Ubound（ar） 返回ar中最大下标，即为成绩的数量。

代码完成后保存。当再次使用时只需在开发工具-宏中找到所需的宏双击执行即可完成代码中包含的操作。

下图为使用宏后产生的各科成绩分析表。

2.3 对各条记录的分析

教师在分析本班的整体情况的同时也需要掌握每个学生的情况，比如个人的单科考试的成绩浮动，但是在一张成绩总表的大量数据中很难看出一个人成绩的变化趋势或是相比其他学生成绩的情况。最好的方法是给每个学生建立一张单独的工作表以方便查看。但是由于学生数量一般较大，手动为每个人建立表格是相当麻烦的。所以可以使用宏给每位学生建立以名字命名的工作表。同样的使用上述的ar变量存储学生个数。

Set d = CreateObject（"scripting.dictionary"）

ar = range（"c3：c" & Cells（Rows.Count， 2）.End（3）.Row）

For i = 1 To Sheets.Count

Debug.Print Sheets（i）.Name

d（Sheets（i）.Name & "|"） = ""

Next

k = d.keys

For i = 1 To UBound（ar）

If Not d.exists（ar（i， 1） & "|"） Then Sheets.Add： ActiveSheet.Name = ar（i， 1）

Next

…

上述代碼使用循环依次以学生姓名建立新工作表，在单元格为空时自动停止循环防止无限制地建立无效的工作表。这种循环结构结合前面所述的模板一起使用也能进一步为每个学生制作电子版的成绩单。建立完工作表后就需要在对应的工作表中填充每个学生的成绩。这个代码仍然需要使用循环结构，将每个学生的成绩对应地复制到他的个人工作表中，并循环操作至最后一名学生。

下图为使用VBA后产生的单个学生成绩表。

3 结语

本文只讲解了基本的模板建立和成绩分析操作，一些问题仍然无法避免：模板并不能保证规避所有的录入错误。也存在一些不方便的地方：比如对于格式的要求太严格，宏不能灵活地随着不同的模板而改变。这些可以进一步使用高级编程语言结合电子阅卷和更多样的模板来解决，为教师实现真正的减负，使教师从烦琐的成绩工作中解放出来，更多地投入到教学中。