巧用WPS构建中学高效易用的成绩分析管理系统

摘 要：文章首先介绍了电子表格在成绩分析管理中的必要性;然后从实际需求出发分析了中学成绩分析管理系统的功能特点;最后详细阐述了如何巧妙利用WPS内置的公式和单元格自动填充实现这些功能的方法，并给出了关键公式。

关键词：电子表格 ;成绩分析 ;教学管理

引言：虽然很多学校已经购买专业的成绩管理系统，但是仍不能满足不同学校不同教师个性化的需求，因此很多教师还需要用电子表格分析自己需要的数据。电子表格以其灵活易用的特点在数据分析处理中仍占有重要的地位。

笔者在多年利用电子表格分析处理学生成绩的过程中不断改进完善系统的基本功能，逐渐形成了一套高效易用的成绩分析管理系统。该系统巧妙运用电子表格提供的基本公式，不用专业的VBA编写代码，因此普通教育工作者可依葫芦画瓢构建出适合自己学校的成绩分析系统。本文是笔者多年利用电子表格分析学生考试成绩的经验总结，希望能起到抛砖引玉的作用。

一、成绩分析管理系统的功能模块

中学的成绩分析流程如下：

1.以备课组为单位进行成绩录入分析打印;

2.备课组上报成绩给年级长，年级长进行总成绩的汇总分析;

3.级长上报年级成绩给学校教务处，教务处进行成绩存档。

因此，成绩分析管理系统的功能模块如下：

1.备课组成绩分析模块

该模块主要功能是：

（1）成绩录入功能：a、客观题成绩导入功能，方便导入机器改答题卡的成绩;b、主观题成绩录入功能。

（2）学生成绩单功能：分别产生按学号排序和按成绩名次排序两种成绩单。

（3）成绩分析功能：计算班级平均分、分数段人数、最高分、最低分、优秀率、及格率等统计数据。

（4）其他个性化需求功能：比如产生教师和备课组长要上交到学校存档的考试质量分析报表。

2.年级总成绩汇总分析模块

该模块主要功能是：

（1）成绩汇总功能：自动汇总各备课组上报的各科成绩，产生成绩总表。

（2）学生总成绩单功能：分别产生按学号排序和按总成绩名次排序的成绩单。

（3）总成绩分析功能：计算班级平均分、分数段人数、最高分、最低分、优秀率、及格率等统计数据。

（4）上线情况分析功能：上线指的是估算上重点线、本科线的人数，计算各班、各科单上线与双上线情况，计算各班、各科的贡献率、命中率、完成率等数据。

（5）其他个性化需求功能：比如产生要上交到学校存档的考试质量分析报表。

3.历次考试成绩汇总分析模块

该模块主要功能是：

（1）历次成绩汇总功能：自动汇总历次考试的成绩，产生学年成绩总表。

（2）学生个人历次成绩单功能：能自动生成带表头的学生个人历次成绩单，方便班主任在家长会、期末向家长发放学生历次成绩单。

（3）历次成绩对比分析功能：能产生任意两次考试成绩的对比分析数据，学生成绩的进退一目了然。

（4）历次考试光荣榜功能：产生各次考试的光荣榜，年级前50名名单、班级前10名名单、各科前5名名单、进步最大的前30名名单等数据。

二、成绩分析管理系统功能模块实现

通常使用电子表格构建的成绩分析系统不易于扩展、移植、复用，需求稍微有一点变化，则无法使用，需要重新修改。我们在实现各功能模块时要充分利用电子表格自动填充的功能尽量使公式可扩展可移植可复用。

由于很多地方反复要使用学生的学号和姓名等信息，因此把学生的基本信息单独做一个表格供其他地方引用。当重新分班或者学生转学等发生学生变动情况，只需修改学生基本信息表，其他地方无需修改。

1.备课组成绩分析模块

（1）成绩录入功能

成绩一般是两部分：选择题和主观题。选择题使用改卷机改卷，由于学生没正确填涂考号或缺考等原因造成个别学生没有选择题成绩，因此使用复制粘贴的方法往往发生错位。我们的解决方案是新建“选择题录入”工作表，在“原始分输入”表中利用如下公式将对应学生的选择题分数找出来。

HLOOKUP（“总分”，选择题录入！$A$1：$Z$997，MATCH（A2，选择题录入！$E$1：$E$997，0），FALSE）

该公式利用了电子表格的自动填充功能，下拉填充即可。使用时只需要把从改卷机导出来的选择题成绩的学生考号和成绩两列复制到“选择题”工作表，无需考虑排序问题，则“原始分输入”表将自动找出对应学生的选择题分数。

（2）学生成绩单功能

为了方便生成按学号排序和按成绩名次的成绩单，要在“原始分输入”工作表增加几列并分别用如下公式生成学号序号：COUNTIFS（A$2：A$911，”<”&A2，$B$2：$B$911，$B2）+1

生成班名序號：COUNTIFS（Q$2：Q$911，”<”&Q2，B$2：B$911，B2）+1

生成级名序号：P2+COUNTIF（P$1：P1，P2）

其中P列为用公式RANK（ ）生成的级排名，之所以还要额外生成级名序号列，目的是要产生唯一的级名，以方便产生按成绩高低排序的成绩单。

新建“成绩打印1”和“成绩打印2”两个工作表分别用于产生按学号排序和按成绩名次的成绩单。在“成绩打印1”工作表先用如下公式产生按学号排序的学号：

{HLOOKUP（“学号”，原始分输入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分输入！$T$1：$T$911&原始分输入！$S$1：$S$911，0），FALSE）}

然后使用如下公式从“原始分输入”表查找对应学号的成绩：

VLOOKUP（$A3，原始分输入！$A$2：$S$911，COLUMN（D$54），FALSE）

在“成绩打印2”工作表先用如下公式产生按成绩名次排序的学号：

{HLOOKUP（“学号”，原始分输入！$A$1：$A$911，MATCH（“01”&ROW（X1），原始分输入！$T$1：$T$911&原始分输入！$R$1：$R$911，0），FALSE）}

然后使用如下公式从“原始分输入”表查找对应学号的成绩：

VLOOKUP（$A3，原始分输入！$A$2：$S$911，COLUMN（D$54），FALSE）

以上公式均充分利用电子表格自动填充功能，下拉或左右拉填充即可。

（3）成绩分析功能

用如下公式生成各班均分，下拉填充即可自动生成1、2、……班的均分。

AVERAGEIF（原始分输入！$B$2：$B$912，ROW（$T1），原始分输入！E$2：E$912）

用如下公式算出各班最高分，下拉填充即可自动算出1、2、……班的最高分。

DMAX（原始分输入！$B$1：$P$555，”总分”，EVALUATE（“A”&17+ROW（$A1）*2&”：A”&18+ROW（$A1）*2））

计算各班最低分类似

DMIN（原始分输入！$B$1：$P$555，”总分”，EVALUATE（“A”&17+ROW（$A1）*2&”：A”&18+ROW（$A1）*2））

2.年级总成绩汇总分析模块

各科将成绩上报给级长后只需要运行年级总成绩汇总分析模块即可自动汇总出总成绩，不用任何复制粘贴操作。

（1）成绩汇总功能

为方便分别统计含借读生和不含借读生的成绩，设置“考生范围”参数，当该参数为0时只统计录取生的成绩，不统计借读生的成绩;当该参数为1时统计所有考生的成绩。在成绩汇总表用如下公式可以实现上述成绩汇总功能，该公式也充分利用电子表格自动填充功能，往下拉和往右拉单元格即可自动填充。

IF（OR（考生范围=1，$AF2=”录取生”），VLOOKUP（$A2，EVALUATE（“[“&D$1&”.xls]原始分输入！$A$1：$S$1000”），MATCH（“总分”，EVALUATE（“[“&D$1&”.xls]原始分输入！$A$1：$S$1”），0），FALSE），””）

（2）其余功能实现如前所述，大同小异，不再重复。

3.历次考试成绩汇总分析模块

（1）文件命名规则：为方便利用公式产生历次成绩，需要将历次成绩分别放入文件夹里并依次命名为1、2、3……

（2）使用参数生成历次成绩表

a、先用如下公式生成学号和学生姓名。

IF（INT（（ROW（$A1）-1）/ShowNum）<COUNTIF（学生基本信息！$A$1：$B$812，显示班级），OFFSET（学生基本信息！$A$1，MATCH（显示班级，学生基本信息！$B$2：$B$812，0）+INT（（ROW（$A1）-1）/ShowNum），COLUMN（A$2）-1），””）

公式中參数“ShowNum”为显示几次考试数据，参数“显示班级”为需要显示的班级，这样做的目的是尽量减少电子表格的计算量，提高表格显示速度。

b、再使用以下公式生成如图1的历次成绩表。

VLOOKUP（$C2，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全级成绩统计.xls]原始分输入’！$c$1：$ak$1000”），MATCH（D$1，EVALUATE（“’”&MOD（ROW（$A1）-1，ShowNum）+1&”＼[全级成绩统计.xls]原始分输入’！$c$1：$ak$1”），0），FALSE）

c、再新建一工作表用以下公式生成带表头的个人历次成绩表

IF（MOD（ROW（$C1），ShowNum+1）=0，C$2，OFFSET（数据导入！$A$1，ROW（$C1）-INT（ROW（$C1）/（ShowNum+1）），COLUMN（C$2）-1））

三、结语

本文阐述了如何巧妙利用WPS电子表格自带的基本公式和自动填充功能构建高效易用的成绩分析管理系统，该系统易于扩展、移植、复用，不同学校只需导入本校学生信息即可使用，所有公式无需更改。教师只要录入考试分数，所有考试分析数据自动生成，可直接打印，省时省力。有别于利用VBA编写代码构建的系统，该系统构建门槛低适合在广大教育工作者中推广应用。

作者简介：吴校玉，男（1977.12—），侗族，籍贯 广西柳州，本科，中级，物理