利用Excel2007实现考试成绩分段人数的自动统计

姚琦玮

（湖南省株洲市第二中学G1414班 湖南株洲 412007）

利用Excel2007实现考试成绩分段人数的自动统计

姚琦玮

（湖南省株洲市第二中学G1414班 湖南株洲 412007）

针对考试成绩分段人数统计，本文提出了基于Excel2007的数据自动处理方案，解决了传统人工数据处理的种种弊端，提高了数据处理的效率和准确度，并为其他同类数据处理提供了有益的借鉴。

Excel 单元格 数据 函数

每一次考试结束后，老师都会对考试结果进行统计和分析，以确切了解每个学生排名、平均分、总分，并对全班考试成绩分布进行分段统计。如果采用传统人工方法完成这些重复性的事情，工作量大、枯燥不说，还容易出错。怎样解决这个问题呢？Microsoft2007中的Excel的数据处理功能提供了很好的解决方案，由于对学生成绩排序、平均分及总分统计操作相对简单，本文将不做介绍，只讨论大家不熟悉的考试成绩分段人数的自动统计方法。[1]

Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Excel中大量的公式函数可以应用选择，其中COUNTIF函数、DCOUNT函数、FREQUENCY函数和SUM函数可以应用到考试成绩的分析和统计。本文仅讨论COUNTIF函数的应用方法，其它函数的使用方法与COUNTIF类似。[2]

COUNTIF函数直译为“条件计数”函数，即统计指定的单元格中符合条件的单元格个数，公式表达式为：“COUNTIF(数据区间,”逻辑运算符 值”)，逻辑运算符有：“＞”、“＜”、“=”、“＞=”、”＜=”等。举例说，统计在Excel表格G22到G100单元格中成绩小于60分（不及格）的单元格个数，COUNTIF函数表示为：COUNTIF(G22:G100,”＜60”)。[3]

高中考试结果评定有两种形式，一种是百分制（或150分制），另一种为等级制（优、良、中、及格和不及格）。高中阶段语文、数学、英语总分150分，物理、化学、生物总分100分，体育则采用等级制，各种考试结果评定的对应表见表1。[4]

表1 各种考试结果评定对应表

按照等级制统计各科成绩对应等级的学生人数。按照如下步骤进行操作：

第一步：将学生各科成绩输入表2所示的学生成绩表（版面原因，部分数据隐藏），建立原始的Excel文档，保存为“学生考试成绩表.xlsx”。

表2 学生考试成绩表

第二步：在表2后面添加数据统计项目（分段）A51-A55，见表3。

表3 学生成绩统计项目

第三步：用鼠标选择B51单元格，在函数输入框输入=COUNTIF(B2:B49,”＜90”) ，系统会自动计算出选定单元格中值小于90的单元格数为0，也就是语文课不及格人数为0。见表4。

表4 统计“语文”成绩小于90分（既不及格）人数

同样，依次选择B52、B53、B54、B55单元格，并在函数输入框输入条件计数函数COUNTIF的对应表达式：=COUNTIF(B2:B49,”＜105”)-COUNTIF(B2:B49,”＜90”) 、=COUNTIF(B2:B49,”＜120”)-COUNTIF(B2:B49,”＜105”)、=COUNTIF(B2:B49,”＜135”)-COUNTIF(B2:B49,”＜120”)、=COUNTIF(B2:B49,”＞=135”) 。每完成一个单元格的函数输入，系统自动计算出在选定单元格中符合条件的人数。[5]

第四步：用鼠标选择“B51-B55”共5个单元格，同时按键“Ctrl”+“c”，然后将复制结果粘贴到“C51-C55”单元格，即可完成英语成绩分段的统计（粘贴的过程中系统会自动修改COUNTIF函数中的数据范围）。同样办法完成理科数学成绩统计。

第五步：参照第三步操作，只需将选择范围更改为“D2：D49”，分段分隔点改为百分制的分隔点，即可在“D51-D55”单元格中实现物理课程考试结果的统计。（单元格D51、D52、D53、D54、D55中依次输入函数：=COUNTIF(D2:D49,”＜60”)、=COUNTIF(D2:D49,”＜70”)-COUNTIF(D2:D49,”＜60”)、=COUNTIF(D2:D49,”＜80”)-COUNTIF(D2:D49,”＜70”)、=COUNTIF(D2:D49,”＜90”)-COUNTIF(D2:D49,”＜80”)、=COUNTIF(D2:D49,”＞=90”) ）。

第六步：参照第四步，完成化学、生物等其它百分制课程成绩统计。

第七步：体育成绩为等级制，所以判别条件为字符串“不及格”、“及格”、“中等”、“良好”和“优秀”。操作方法为在单元格“H51-H55”中依次输入如下函数：=COUNTIF(H2:H49,“=不及格”)、=COUNTIF(H3:H50,“=及格”)、=COUNTIF(H4:H51,“=中等”)、=COUNTIF(H5:H52,“=良好”)、=COUNTIF(H6:H53,“=优秀”) 。就可以统计出各等级学生人数了。

完成这些工作后，Excel就完成了各科成绩分段学生人数的统计，并且统计结果会根据考试数据的变化而自动更新。通过该表格，考试结果一目了然。如表5。

表5 各课程考试成绩分段人数统计结果表

当然，利用Excel其他功能，还可以插入各种统计图形，也可以对学生每次考试成绩的升降进行分析，绘制出成绩变化图等等，这些内容在本文中就不做讨论了。

Excel具有强大的数据处理能力，灵活应用相关功能函数，在提高劳动效率、确保结果准确、分析结果清晰直观的同时，还能使枯燥的数据处理变得充满乐趣。

[1]学校成绩统计 教你轻松搞定[J]. 王彦博. 电脑爱好者.2013(12)

[2]基于Excel VBA的S-P表试卷分析系统设计[J]. 郭孝存,王文忠,那木拉,陶红平. 阴山学刊(自然科学). 2011(02)

[3]基于Excel的学生考试试卷成绩分析[J]. 钱礼闰,李艳婷. 安庆师范学院学报(自然科学版). 2011(02)

[4]试卷分析与评价系统的主要指标及关键问题处理[J]. 崔国生,张楠,李卓玲. 沈阳工程学院学报(社会科学版). 2011(03)

[5]用EXCEL设计试卷分析模板[J]. 蒋勇. 黑龙江科技信息.2009(24)