excel成绩管理技巧

成绩统计是教育反馈中的重要环节。随着宜昌市新课程改革如火如荼地推进，中考评价也一改以前的分数评价为等级评价，评价改革的实验范围也从去年仅在城区试点扩展到今年众多周边县市。新的评价制度，更科学更有利于学生发展了。同时教师在成绩统计时的工作量也明显加重了，出错机率也明显增多了：教师不仅要输入分数，还要把各科转换成等级，同时还要分别统计A、B、C、D、E、F六个等级的人数和百分率，而以前只需统计高分率和及格率等。按原有的成绩统计方法，现在的工作量比以前增加了好几倍。 

虽然此类功能的软件目前也存在，但多用高级语言编写，教师对其数据的操作很受程度本身的。而教师对Microsoft EXCEL 却比较熟悉，应用EXCEL的函数可方便地实现等级成绩转化和统计，教师可对EXCEL表的输出格式可自由调整，使整个系统更具灵活性。

二、设计思想

Microsoft EXCEL中提供了丰富的函数，涉及统计、数学与三角函数、查找与引用、逻辑、文本、信息、数据库、财务、日期与时间等九大块功能函数，完全可以满足等级成绩统计系统的编制需求。虽然Microsoft EXCEL中还提供宏的支持，但从应用角度上看，不使用宏的系统在教师具体使用中遇到的问题会更少，不少系统的防火墙可能会禁止宏的运行，也有许多宏语句需要系统插件支持。所以单纯使用函数来实现系统，使系统更绿色，系统的易用性和通用性更好。利用Microsoft EXCEL的菜单工具不仅可以方便地设置单元格的格式及打印报表格式，还可方便地实现单元格或工作表的锁定、数据有效性检查和系统的加密等。

《学生等级成绩统计系统》按照实施等级评价的学校对成绩统计的要求，按教师最熟知的报表格式设计表单。使用该系统，只需教师或成绩统计人员简单的几项设置、再录入或导入学生学号、姓名及语数外物化五科的原始成绩，其它各项系统将自动统计完成。从而大大提高了初级中学的学生等级成绩统计的工作质量和工作效率。

三、系统的构成和设计

1、系统设计

（1）系统开发环境：

Microsoft EXCEL 2003、windows XP

（2）系统应用环境：

Microsoft EXCEL 2000（XP、2003等）、windows2000（XP、2003等）

（3）系统结构：由一个主文件和若干说明文件组成。

◆主文件：是一个Microsoft EXCEL 工作薄。它由若干工作表组成，包括：“设置”表、班级表（1班、2班、…）、中间过程表（“M”表）、年级排序表（“年级（录入序）”表、“年级（学号序）”表、“年级（等级序）”表）、统计表（“单科统计”表、“总分统计”表）。

各工作表间数据相关如下图1所示

图1：表数据相关图

◆说明文件：TXT格式文件或WORD格式文件，介绍系统功能及使用方法等。

2、具体功能设计：

（1）分数到等级的转化：可以使用函数IF，对条件进行真与假的判断，并作对应出等级值的输出。以语文为例：按照等级统计的要求，100—120分，A等；90—99分，B等；80—分，C等；70—79分，D等；60—69分，E等；0—59分，F等。则函数语句为：

=IF(D4>=100,"A",IF(D4>=90,"B",IF(D4>=80,"C",IF(D4>=70,"D",IF(D4>=60,"E

注：D4为语文原始分数所在单元格。

（2）等级字串的合成：可以联合CONCATENATE函数和COUNTIF函数来实现。COUNTIF函数，可计算指定区域中满足给定条件的单元格的个数，如统计J4至N4单元格中“A”的数目，则函数语句为：=COUNTIF(J4:N4,"A")，与此类推，统计出“B”、“C”、“D”、“E”、“F”的数目。CONCATENATE函数，可将几个文本字符串合并为一个文本字符串，上述“A”、“B”、“C”…的数目分别与“A”、“B”“C”…字符本身连接合并，生成诸如“2A1C2D”的形式。

（3）等级总分的计算：使用IF（）语句先给每个等级赋分，再用运算符“+”号来求和。如“A”为6分，“B”为5分，“C”为4分，“D”为3分，“E”为2分，“F”为1分，则给J4单元格赋分的语句为：

=IF(J4="A",6,IF(J4="B",5,IF(J4="C",4,IF(J4="D",3,IF(J4="E",2,IF(J4="F",1,0))))))

（4）名次的生成：使用RANK（）函数和COUNTIF（）函数来实现。例如要按等级总分由高到低排序，同时符合金牌优先原则（即：等级总分相同时，A等级数多者优先；同时如A等级数也相同，B等级数多者优先，以此类推），则可以按以下思路设计：

Step1：使用COUNTIF（）函数分别统计每个学生“A”、“B”、“C”…的等级数目；

Step2：按总等级分（如Q4单元格）--A等个数（如T4单元格）--B等个数（如U4单元格）--C等个数（如V4单元格）--D等个数（如W4单元格）--E等个数（如X4单元格）的顺序，计算出一个综合指标参量，写入单元格Y4：=Q4*100000+T4*10000+U4*1000+V4*100+W4*10+X4；

Step3：使用RANK（）函数返回单元值（如Y4单元格）在数字列表（如Y4至Y83单元格）中的排位：=RANK(Y4,Y$4:Y$83,0)。

至此，可生成特定要求的成绩名次。

（5）数据的重排：要实现按特定要求的数据重新排列，如按等级总分从高到低排列、按学号顺序的重新排列等，需考虑以下几个问题：

◆怎样生成一个索引值，而这个索引值将是数据重排时索引的唯一依据；

◆怎样保证所有的数据都能参与到重排，且不出现掉队的记录，如当几条记录同条件满足要求时，不能出现一个记录重排数列中有而另一条记录数列中却不存在的现象。

解决第一个问题的方法与名次生成过程的做法类似，即按要求把重排的关键唯一性指标合成新的数值，该数值的大小将决定重排时的位次；解决第二个问题的方法则是为原始的每一条记录设定一个唯一性序号，将它作为数据重排时的最后一个关键性参数。

具体数据重排过程需要三个函数的参与：

◆CONCATENATE（），将几个文本字符串合并为一个文本字符串，从而生成新的重排索引值。本系统是这样实现的：=CONCATENATE(99999999-Y4,999-(99-A4)))，其中Y4单元格为符合金牌优先原则的等级序的综合指标数据，而A4单元格则是原始数据的唯一性序号，用99去“减”是为了保证每次生成的新字串位数相同；

◆SMALL（），返回数据集中第 k 个最小值；

◆MATCH（），返回在指定方式下与指定数值匹配的数组中元素的相应位置；本系统用它与SMALL（）函数联用，实现了最小值相对位置的返回，并将值写至AA4单元格；

◆INDEX（），返回表或区域中的值或值的引用。以本系统为例，需重排的原始数据范围为B4单元格至R83单元格，则用下述语句就实现了指定范围、符合要求的数据重排：

= INDEX($B$4:$R$83,$AA4-3,COLUMN(C$3))。

3、系统通用性考虑：

（1）变量设计：根据系统通用性的要求，本系统应有下述几个相关变量：学校名称、年级名称、班级简称、班级表大标题、年级总表大标题及系统当前时间等变量。其中前五个变量均为文本变量，可设定相关的单元格，接受用户输入，其他地方要用时则直接引用该单元格，这几个变量可集中在一个表中等待用户输入，本系统“设置”表将完成上述变量的设定；后一个变量为日期变量，在需引用处使用：=TODAY（）语句。

（2）容错设计：

◆空位容错：考虑到最终用在进行数据录入时可能出现的情况，比如缺考的成绩不做记录出现空位，或用其它标志进行标记等，为防统计时出错，在语句设计时用IF（）函数过滤；

◆空行容错：是指系统设计为每班八十人班额，而实际人数未达到这个数，对空行的处理。可对空行赋于最大的重排索引值，从而将空行置于重排数据阵列的最后，并用IF（）函数让之不显示出来。

◆空表容错：是指系统默认的年级最大班级数的可能大于实际的班级数，这时闲置的工作表会导致总表统计时相关行出错。本系统的解决办法是使用ISERROR（）函数，用它来检验相关单元格的数值是否有错，如有错则赋值为“TRUE”，系统在进行数据重排或数据统计时将参照这个参数进行，从而过滤到可能出现的错误。因此本系统可根据实际年级的班级数，删除多余的班级工作表。

（3）非工作区保护：为了防止用户在使用过程中误改公式或格式，对于除用户录入区外的其它区域加以锁定。

（4）打印报表调整：打印输出样式，会根据参考班级人数、班级数、参考科目的不同而不同。当班级人数较多时，可允许自行调整行高，让一个班级的成绩表能在一张A4纸张上打印；在分科统计和总分统计时，无有效数据的行或列可以隐藏，使之不显示和不打印。上述目标可在非工作区保护过程设置中，通过勾选“设置行格式”和“设置列格式”选项来实现。

4、功能改进和扩充

◆可进一步优化代码，减少整个系统的计算量，加快系统的数据响应速度。

◆如果用于全县学校的等级成绩分析，可将每个班级表的班额扩大为每校每年级最大可能人数；

◆可加入除语数外物化的其它学科的统计；

◆可增加图表分析功能，用图表更形象展示各等级人数的分布情况；

四、应用举例

1、班级成绩表生成

作为班主任的王老师，在进行简单的班级设置后，转入对应的班级工作表，从其它电子表格复制过来学生的学号和姓名，并开始录入学生语数外物化各科的成绩。这时他发现，当他录入某生成绩之后，该生各科对应的等级已自动生成了，而且总等级分也正确显示了。当本班的成绩全部录入后，王老师惊喜地看到了每位学生的名次，特别是在数据录入区域的右侧（用鼠标向右拖动水平滚动条），学生成绩正按等级总分由高到低重新整齐排列着。王老师点击了一下工具栏上的“打印预览”，发现连打印格式都已经设好了，可王老师唯一不满意的是，全班学生成绩不能在同一页上显示，于是王老师用鼠标选中所有学生成绩记录所在的行，点击右键，选择“行高”，重新输入一个较小的行高值，再“打印预览”时，王老师满意了，于是，在A4纸张上打印出了一张漂亮的学生成绩表。

2、 年级成绩汇总与统计

作为年级组组长的万主任，在组织好各班成绩后，转入名为“年级（录入序）”的工作表，认真核对了各班录入的原始成绩。确定无误后，他转入了名为“年级（学号序）”的工作表，按学号查找了几位学生的等级成绩和他们的年级排名。全年级这次考试到底哪些学生考得比较好呢？万主任转入名为“年级（等级序）”的工作表，他高兴地看到：全年级所有学生的成绩正按等级总分从高到低的顺序整齐排列着。至于全年级各科各班又考得如何，王老师转入了名为“分科统计”和名为“总分统计”的工作表，发现各班各科成绩正按等级A、B、C、D、E、F、总等级分等项目显示的清清楚楚，这使万主任很方便地对比了解了各科各班的成绩情况。最后，万主任点击了工具栏上的“打印”，整齐漂亮的汇总表出现了……

“学生等级成绩统计系统”是根据我市我县初级中学的等级评价要求编制的，适合我市特别是我县所有初级中学，用于月考、期中考试、期末考试的等级成绩统计。本系统系统要求低，教师易掌握。本系统自网络发布以来，被全县一些学校试用，大大提高了这些学校的等级成绩统计工作的效率。