Excel在学生成绩统计分析中的有效应用

向泽林 谭俊

摘  要：学生成绩统计分析是每位教师在教学工作中的重要一环，如何方便、快捷地处理，并对教学效果进行客观评价，一直是广大教师特别关注的问题。文章主要讲述Excel中的函数、直方图、预测工作表等功能在成绩统计分析上的应用，这些操作不仅可以优化繁琐的计算过程，还可以系统地分析成绩分布、变化趋势等，从而进一步完善教学，改进出题和考试方式等，使之真正为教学服务。

关键词：Excel 2016;成绩;教学效果;评价学习

中图分类号：TP391.1       文献标识码：A 文章编号：2096-4706（2020）07-0178-03

The Effective Application of the Excel in Statistical Analysis of Student Achievements

XIANG Zelin1，TAN Jun2

（1.Foundation Section of Chengdu Institute Sichuan International Studies University，Chengdu  611844，China;2.English Department of Translation and Interpretation of Chengdu Institute Sichuan International Studies University，Chengdu  611844，China）

Abstract：Statistical analysis of student achievements is an important part of every teachers teaching work. How to deal with it conveniently and quickly and to evaluate the teaching effect objectively，has been a special concern of most teachers. This paper focuses on the application of certain Excels functions，such as the function，histogram，forecasting table，in the statistical analysis process. Such applications not only optimize the tedious calculation process but also analyze the distribution of scores，the changing trends，etc. systematically. So as to further improve teaching and the ways of examination，thus better serve for the teaching.

Keywords：Excel 2016;achievement;teaching effects;evaluation

0  引  言

学生成绩是检查教学质量的一个量化指标。每次考试结束后，老师都要对学生的成绩进行仔细统计、详细分析、全面评价。纸质加人工的传统管理方式，不仅效率低、效果差，而且容易出错，但利用MS Office办公软件中的Excel就能轻松、高效地解决这些的问题。本文将结合四川外国语大学成都学院计算机等级考试成绩和翻译学院2017级英语专业四级模拟考试成绩，介绍如何使用Excel 2016进行成绩统计、分析、图表展示等，从而达到对学生的学习情况进行客观评价，以及对学生过级率进行预测，进而有助于有针对性地采取措施，改进教学。

1  成绩查询、排名、标准分计算及特殊标注

1.1  VLOOKUP函数快速查找对应班级成绩

实际教学工作中，每位老师经常会遇到要统计自己所教班级学生的成绩，进而与全院所有班级作对比，并查看与其他院系老师、学生的差异，从而评估自己的教学效果。比如负责2017级翻译1班的老师，在专四模拟考试成绩公布后，她所接收到的是翻译学院2017级1～8班全体学生模考1的成绩总表，其数据表结构和记录如表1所示（其他三次模考成绩类似）。

从“学生模考成绩表”可以看出表中罗列出的信息有学生的学号、各部分对应的分值，以及总成绩等，但没有班级，并且每次模考数据单独在一个工作表中。这些都对统计分析非常不方便，也不便于观察学生的成绩变化，因此我们就应该将四个工作表的模考数据集中在一个“汇总”工作表中，如图1所示。那怎样才能在汇总表中快速得出翻译1班所有学生每次考试一一对应的成績呢？

通过仔细观察，我们发现两个工作表共同拥有“学号”这个字段，这种情况下我们就可以利用Excel中的VLOOKUP函数来完成。方法是，在E3单元格中输入公式：=VLOOKUP（A3，模考1！$B$2：$P$277，15，FALSE），确定即可计算出第一位学生模考1的成绩，然后向下填充，计算出其他所有学生模考1的成绩。按照同样的方法和步骤，快速计算出每位学生其余3次模考的成绩。

接下来，教师可用平均函数AVERAGE计算出翻译1班和2017级翻译学院所有学生四次模考的平均成绩，如表2所示。通过表格数据的比对，就能轻易看出这位老师所教翻译1班的学生水平，基本上和年级平均分持平，属于中等。当然，除此之外，教师还可以纵向比较，观察不同年级学生的学习情况。

1.2  名次及标准分计算

很多老师在分析学生成绩高低时，习惯性用排序（降序）的方式，这种方法固然可以实现，但排序后所有记录的前后顺序就会被打乱，比如不会再按照学号从小到大排列了。因此遇到这种情况就可以利用RANK函数来进行操作。为了数据更有针对性，后面操作就用最临近考试的第四次模考成绩作为依据。方法是，在“汇总”表中I3单元格输入公式：="第"&RANK（H3，$H$3：$H$33，0）&"名"，得出第一位同学在翻译1班的名次（以“第几名”的形式显示），然后向下填充，计算出其余同学的班级排名，这样的好处就是数据的顺序没有任何改变。

为进一步了解学生此次考试情况，教师还可以利用学生成绩算出标准差，进一步算出学生标准分，标准分=（个人分-人均分）÷标准差（标准分是评价一个学生成绩的重要指标）。方法如下，在J3单元格输入公式：=（H3-AVERAGE（$H$3：$H$33））/STDEVP（$H$3：$H$33），然后填充得出每位学生标准分。分值越大，说明该学生成绩越优秀，与成绩分数、排名都不谋而合，但更能客观反映学生学习情况。

1.3  重点突出显示

有时候教师查看学生成绩后，希望将满足一些特定条件的学生重点标注出来（比如成绩较高的或不及格的），便于教师在后续教学中真正做到因材施教。若采取逐条记录的方法去操作，效率过低，特别是数据量较大时。这种情况下我们就可以利用Excel提供的条件格式，通过编辑公式来轻松实现。例如，在“汇总”数据表中，假设学生模考1、模考2、模考3三次模考每次成绩都在48分以上，就属于过专四比较有保障的范畴，于是需要特别标注出来，那如何实现呢？方法是，选中所有数据区域（标题行除外），条件格式中新建规则，在使用公式设置格式文本框中输入：=AND（$F3>=48，$G3>=48，$H3>=48），然后根据需要设置显示格式（本例为灰色填充），确定即可将满足条件的记录突出显示。

通过以上一系列操作，就完成了翻译1班学生四次模拟考试成绩的统计分析和格式设置，结果如图2所示。

2  直方图显示学生成绩情况

直方图能直观反应一组数据的分布特征，用它可以观察数据是否对称分布（正态分布），判断数据有误异常，它能很好地体现学生整体学习情况。比如在“汇总”表中，获得翻译1班所有学生的模考4的成绩后，教师就可以通过创建直方图分析该班此次考试成绩整体情况如何，方法如下。

选取模考4成绩列区域，绘制图表类型中的直方图，并設置好图表标题、坐标轴标题、图例、箱宽度等，本例“箱宽度（分数段间隔）”设置为5，最后得到的效果如图3所示。

通过图表，可以直观地看到翻译1班模考4的成绩基本符合正态分布（低分和高分比较对称，占比小;中等分占绝大多数），且主要分布在34到44分之间。如果按照45分以上在全国英语专四考试时及格概率比较大来推算的话，仅仅只有7个人左右能取得合格证书，所以要整体增加通过率的话，教师必须攻克[34，44]这部分分数段的学生，让他们的成绩提升起来。

3  预测成绩趋势变化

在全国计算机等级考试MS Office一级考试中，我校学生的一次性通过率基本上都能维持在80.00%以上。现在，教师希望通过2016年3月至2019年9月的等级考试成绩的历史数据来创建预测，推断今后几年考试的通过情况、波动幅度等，从而为接下来的教学做好相应的调整。操作方法如下：选取日期和过级率两列数据，创建“预测工作表”，即可自动生成预测数据和图表，如图4所示。它不仅详细地显示了历史数据和预测数据，还创建了预测图，并清楚地展现出接下来几次考试通过率的走势，同时标注了上限和下限范围。预测数据虽然有波动，但整体还是保持在80.00%到85.00%之间。当然，如果历史数据更多一些，预测的准确性也就会更客观、更准确。

4  结  论

目前Office版本很多，本文基于Excel 2016进行操作（本文所有数据、图表都是在Excel应用软件平台完成），它操作简单、易学，同时功能非常强大。由于篇幅原因，文章只是简单阐述了它在学生成绩管理中最常用、最基本的一些统计和分析应用，它使得成绩管理工作实现程序化和自动化，从而极大地提高了工作效率。另外，本文用到的所有计算、统计方法都可以直接应用到教师平常对学生成绩的统计分析工作中。

参考文献：

[1] 教育部考试中心.全国计算机等级考试二级教程——MS Office高级应用：2013年版 [M].北京：高等教育出版社，2013.

[2] 韩辉珍.Excel在学生成绩管理中的应用 [J].中国科技信息，2008（23）：39+41.

[3] 袁西亮，巫锦润.Excel在学生成绩统计与分析中的应用 [J].考试周刊，2010（36）：170-171.

作者简介：向泽林（1975.12—），男，汉族，四川资阳人，讲师，研究生，研究方向：计算机基础教学、信息化办公;谭俊（1978.07—），女，汉族，新疆伊宁人，讲师，研究生，研究方向：英语语言学、英语教学法。