Excel在高中考试成绩分析中的应用

吴正芳，王敏

（1.襄阳市第三中学，襄阳　441000；2.湖北文理学院数学与计算机科学学院，湖北 襄阳 441053）

Excel在高中考试成绩分析中的应用

吴正芳1，王敏2

（1.襄阳市第三中学，襄阳441000；2.湖北文理学院数学与计算机科学学院，湖北 襄阳 441053）

0　引言

考试是教育活动中评价教学质量、衡量教学效果的重要手段之一，对学生的成绩进行统计分析，能够给教学活动、学生管理提供参考，是提升教学效果和学生成绩的指导方向。高中考试成绩的统计分析通常包括个人单科排名、个人总分的排名、各科目的最高分、最低分、平均分、中位数，各分数段的人数统计、多次考试成绩的比较以及成绩预警等。学生多、考试科目多、考试频繁，由此带来了大量的统计分析工作负荷，运用现代信息技术手段高效完成上述统计是非常必要的，Ex cel电子表格程序正是一个合适的工具［1］。本文以Ex cel 2010为例介绍。

Excel是Microsoft Office中的电子表格程序，用户界面友好、计算能力强大、操作简便。使用Excel可以创建电子表格并设置格式，以便分析数据和做出更明智的业务决策，还可以使用Excel跟踪数据，生成数据分析模型，编写公式以对数据进行计算，以多种方式透视数据，并以各种具有专业外观的图表来显示数据。

1　运用“条件格式”实现快速直观分析

1.1成绩预警

面对成绩表格中的大量数据，在不改变数据现有排序的情况下，常常需要以醒目的样式显示成绩低于某一具体指定分数的数据。使用条件格式可以快捷地以指定的样式直观地标记出符合指定规则的数据［2］。例如：把语文、数学、英语成绩低于90分的数据用浅红填充色深红色文本标记。操作方法是：选择要设置条件的单元格区域B2：D11，在“开始”选项卡的功能区中“样式”组中选择“条件格式”，进一步选择“突出显示单元格的规则”下的“小于”项，在“小于”对话框中设置数值为“90”，单元格格式为“浅红填充色深红色文本”。结果如图1所示。

图1　分数预警

1.2成绩对比

在教学管理中，经常通过对相近两次考试成绩的比较来衡量学生这一阶段的学习情况，并且希望能够把比较的结果直观地显示出来。条件格式的数据条功能，可以非常直观地查看选定区域中数值的前后变化情况。例如：比较学生模拟考试一和模拟考试二的成绩。操作方法是：选择要设置条件的单元格区域D2：D7，在“开始”选项卡的功能区中“样式”组中选择“条件格式”，进一步选择“数据条”中的“绿色数据条”渐变填充。结果如图2所示。

图2　成绩对比

2　运用函数实现常用综合统计分析

基于学生的单科成绩统计个人总分、排名，统计课程最高分、最低分、平均分、中位数以及各分数段的人数统计等，是在考试成绩统计分析中经常用到的。Excel电子表格程序提供了丰富的函数，可以方便快捷地实现以上统计分析需求［2］。例如：统计学生语文、数学、英语成绩的总分和排名，分别统计三科成绩和总分的平均分、中位数、最高分、最低分，按分数段分别统计三科成绩的分布情况。结果如图3所示。

图3　综合统计分析

涉及的函数和操作分别是：

总分：SUM函数返回参数中的所有数字的和。在E2单元格中输入=SUM（B2：D2），得到程1的总分，选中E2单元格，使用填充柄拖动至E11单元格，实现其他学生总分的填充输入。

排名：RANK.EQ函数返回一个数字在数字列表中的排位，其大小与列表中的其他值相关，如果多个值具有相同的排位，则返回该组数值的最高排位。在F2单元格中输入=RANK.EQ（E2，$E$2：$E$11），得到程1的排名，选中F2单元格，使用填充柄拖动至F11单元格，实现其他学生排名的填充输入。这里省略了order参数，order省略或者order为0时默认的排序方式为降序，order取非零值时为升序。

平均分：AVERAGE函数返回参数的算术平均值。在B12单元格中输入=AVERAGE（B2：B11），得到语文成绩的平均分，选中B12单元格，使用填充柄拖动至E12单元格，实现数学、英语和总分的平均值的填充输入。

中位数：MEDIAN函数返回给定数值序列的中值。在B13单元格中输入=MEDIAN（B2：B11），得到语文成绩的中位数，选中B13单元格，使用填充柄拖动至E13单元格，实现数学、英语和总分的中位数的填充输入。

最高分：MAX函数返回一组值中的最大值。在B14单元格中输入=MEDIAN（B2：B11），得到语文成绩的最高分，选中B14单元格，使用填充柄拖动至E14单元格，实现数学、英语和总分的最高分的填充输入。

最低分：MIN函数返回一组值中的最小值。在B15单元格中输入=MEDIAN（B2：B11），得到语文成绩的最高分，选中B15单元格，使用填充柄拖动至E15单元格，实现数学、英语和总分的最低分的填充输入。

分数段统计：FREQUENCY函数返回一个垂直数组，用于计算数值在某个区域内的出现频率。在F18：F21单元格区域中从小到大依次输入89.9，99.9，109.9，119.9作为区间分割点。选中B18：B22单元格区域，输入=FREQUENCY（B2：B11，$F$18：$F$21），按＜Ctrl＞+ ＜Shift＞+＜Enter＞，得到语文成绩的分数段统计情况，选中B18：B22单元格区域，使用填充柄拖动至D22单元格，实现数学、英语成绩的分数段统计。

需要注意的是，公式和函数中涉及的标点符号必须使用半角状态下的英文标点符号；在涉及单元格引用时，默认为相对引用，有些地方需要使用绝对引用；用于分数段统计的函数FREQUENCY返回的结果为数组，输入时要以数组公式的形式输入，即输入时要选择结果区域，公式编辑完成后要按＜Ctrl＞+＜Shift＞+＜Enter＞。用同样的方法可以实现对单科成绩排名的统计，也能在分数段统计中设置更多的分割点以实现更细致的分段。

3　结语

本文介绍了使用Excel电子表格程序的条件格式功能和函数实现高中考试成绩的成绩预警、成绩对比、计算总分、平均分、中位数、最高分、最低分、按分数段统计人数、排名等成绩统计分析需求，为教学活动、学生管理提供参考。可用于解决类似的统计分析问题，具有一定的实际意义。在此基础上，还可以通过锁定公式、设计模板、VBA等方式实现高效的成绩统计分析［3］。

［1］宋赟.基于数据挖掘技术的学生成绩分析［D］.重庆师范大学，2009

［2］王小燕.MS Excel数据分析在成绩分析暨试卷质量评估的应用［J］.现代教育技术，2010，10：135～137

［3］娄青.基于Excel VBA开发普通高中成绩分析系统［J］.中国教育信息化，2012，10：76～79

Excel；Examination Analysis；Conditional Formatting；Function

Application of Excel in High School Examination Results Analysis

WU Zheng-fang1，WANG Min2
（1.No.3 High School of Xiangyang，Xiangyang 441000；2.School of Mathematics and Computer Science，Hubei University of Arts and Science，Xiangyang 441053）

1007-1423（2015）09-0051-03

10.3969/j.issn.1007-1423.2015.09.012

吴正芳（1978-），女，湖北襄阳人，本科，中教一级，从事领域为高中数学教学工作

王敏（1982-），女，山西晋城人，硕士，讲师，研究方向为计算机教育、现代教育技术

2015-01-13

2015-02-28

介绍使用Excel电子表格程序的条件格式功能和函数实现高中考试成绩的成绩预警、成绩对比、计算总分、平均分、中位数、最高分、最低分、按分数段统计人数、排名等成绩统计分析需求，为教学活动、学生管理提供参考依据。

Excel；成绩分析；条件格式；函数

湖北文理学院实践性课程教学方法改革项目

Introduces the application of conditional formatting and function of Excel in high school examination results analysis.Realizes the results warning，result contrast and calculation of total score，average score，median score，score section etc by using conditional formatting and function of Excel.The method can provide reference for teaching activity and student management.