用WPS表格完成片区成绩统计

我校在对教师进行绩效考核中，需要各位教师所教学科的人平分、及格率、优生率、差生率在片区几所学校所有班级中的排位情况，每到期末我的工作量都相当大，为了达到一劳永逸的目的，就制作了一个片区成绩统计表。下面就将此表制作的过程作一简要说明。望这篇文章能起到抛砖引玉的作用，敬请各位同仁指教。

一、制作所需表格

首先，将需要的工作表制作出来。为了保证各表间数据引用方便，利于修改，我们尽量使几个工作表的样式、格式一致。为了减少工作量，示例表中我只做了三所学校(分别是“学校甲”、“学校乙”，“学校丙”)，每所学校三个班，实际中我们可以根据实情进行增减，方法都是一样的。

1.制作学校甲三个班的成绩统计表，如图1：

因为现在的学籍管理要求每个班人数不得超过70人，所以我就为每个班预定了70行(图1为了完整显示内容，隐藏了部分单元格)，再将每个班学校名称列和班次列的数据录入。

2.将工作表“学校甲”复制出工作表“首页”，在基本不动表格样式的情况下，做出如图2所示表格：

3.再将工作表“学校甲”复制一个工作表“片区汇总”，将三个班后的分析部分及空行删除掉(图3)，

再将“学校甲”三个班的表格复制两次到此表中(不要复制标题行，第一次复制后将“学校甲”替换为“学校乙”，第二次复制后将“学校甲”替换为“学校丙”，如图4)，同样删除各班后分析部分及空行。

4.因为我们想要了解本校各科各项指数在片区中的排位，所以另外还要制作一张各项指数的统计表。我们依然可以将工作表“学校甲”复制出“片区统计”，将表格调整为图5样式制作出“人平分”的统计表，再复制出“及格率”、“优生率”、“差生率”的统计表。

至此，需要的工作表就全制作好了(为了减少工作量，工作表“学校乙”、“学校丙”待工作表“学校甲”所有需要的公式录入完成后再复制)。

二、利用数据有效性制作下拉列表

表格是制作出来了，但表格内还有很多地方需要填入数据，如标题行还需要此次检测的年份、年级、期段，成绩栏还需要显示各学科名称等，为了使工作簿能多次使用，我们可以利用数据有效性来制作下拉列表，提供选择项。

首先，在工作表“首页”任一空白处将年份、年级、期段、学科的序列录入。如图6：

接着，选中“首页”标题行中第一个合并的单元格，再点击菜单栏中的“数据”——“有效性”(图7)，

在弹出的对话框“允许”下选择“序列”(图8)，

在“来源”处输入年份序列下所有年份的范围(也可以点击“来源”处文本框右侧的按钮后再拖选所有年份的单元格，如图9)，再点“确定”。

这样，年份的下拉列表就制作完成了(图10)。

用同样的方法，也将年级、期段、学科的下拉列表也制作出来(“学科”的下拉列表可以只做一个再复制或拖拽填充出来，但前提是在首次输入学科序列时，必须在行号、列号前加绝对引用符号“$”，否则，后面的下拉列表就会变)。将所有下拉列表都制作出来后，我们就可以将录入年份、年级、期段、学科序列的所在行全部隐藏起来。

(未完，2楼继续)

三、利用函数求人平分、及格率、优生率、差生率

接下来就将所有表中涉及到的函数分别进行说明。

1.工作表“首页”中，在“各学科总分”后的“总分”单元格下用SUM函数求出所有学科的总分数,在单元格O4中录入公式：“=SUM(E4:N4)”(其它如“学校甲”、“片区汇总”表中“总分”一列都如此，后面就不缀述了)。

接着，在“及格分数段”后的单元格内求出及格分数段(因为各学科的总分不确定，所以只能用公式求)，在“及格分数段”后的单元格内录入函数“=E4*0.6”，再复制出所有学科的及格分数段。

再接着，在“各科优生段”后的单元格内求出优生分数段(因为我校的各科“优生”是指进入全片区所有学生前30%的学生，所以“优生段”就是指所有学生数的前30%最后一名的分数，例如：片区某年级共500人，前30%就是150人，那么前第150名的分数就是每个学科的优生段。“各学科差生段”也类似，只不过改为求后30%第一名的分数为差生段。)，在“各科优生段”后第一个单元格内录入公式“=LARGE(片区汇总!E5:E634,ROUND(COUNT(片区汇总!E5:E634)*0.3,0))”，这个公式主要是用LARGE函数求出工作表“片区汇总”第一个学科学生成绩的第K个最大值(这个“K”的值就通过COUNT函数求出“片区汇总”第一个学科的总人数，再乘以0.3，再用ROUND函数四舍五入求出的整数值)，再将这个公式复制到其它学科。

最后，用SMALL函数求出“各学科差生段”，第一个学科的公式是：“=SMALL(片区汇总!E6:E634,ROUND(COUNT(片区汇总!E6:E634)*0.3,0))”，这个公式是用SMALL函数求出工作表“片区汇总”第一个学科学生成绩的第K个最小值(这个“K”的值与上面的 “K”值相同)，再复制出其它学科的差生段公式。

这样，工作表“首页”就完全制作成功了(图11)，这个表中的及格段、优生段、差生段数据将作为其它工作表引用的基础。

2.将工作表“学校甲”制作完成。

首先，将标题行完善，在第一个合并的单元格中录入公式“=IF(首页!$D$1="","",首页!$D$1)”(公式中的if函数是为了在表格无数据时使该单元格也显示为空白，纯属美观需要，并不是必须的，如果只要正确求得数据，录入“=首页!$D$1”就可以了，本文IF函数的作用都如此)，在第二个合并的单元格中录入公式“=IF(首页!$F$1="","",首页!$F$1)”，在第三个合并的单元格中录入公式“=IF(首页!$I$1="","",首页!$I$1)”，这样，“首页”标题选择了什么年份、年级、期段，“学校甲”就会显示相同的内容了。

接着，用同样的方法将学科名称也与“首页”同步，为了保证拖拽复制的准确，在录入公式时，就不加绝对引用符号：“=IF(首页!E3="","",首页!E3)”。

接下来，再将各班“人平分”、“及格率”、“优生率”、“差生率”四个指数的公式录入，在这里就会引用到“首页”求出来的各学科“及格段”、“优生段”、“差生段”的数据了。分别在第一个学科下的四个指数单元格中录入公式：人平分—— “=IF(ISERROR(AVERAGE(E5:E74)),"",AVERAGE(E5:E74))”，及格率—— “=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$5)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$5) /COUNTA(E5:E74))”，优生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$6) /COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$6)/COUNTA(E5:E74))”，差生率——“=IF(ISERROR(COUNTIF(E5:E74,">="&首页!E$7)/COUNTA(E5:E74)),"",COUNTIF(E5:E74,">="&首页!E$7) /COUNTA(E5:E74))”。接着再选中刚才录入数据的四个单元格，向右拖拽填充，将公式也复制到其它学科的单元格内。接着再将所有学科下“及格率”、“优生率”、“差生率”这三项的单元格选中，通过依次点击“右键”——“设置单元格格式”——“数字”——“百分比”——“确定”，将其设置成百分比(如果设置成百分比后无法正确显示数据，就将其字号减小)。

最后，再选中1班四个指数项的所有单元格，将其复制到2班、3班。工作表“学校甲”制作就算完成了(图12)。

3.制作完成工作表“片区汇总”。

首先，按照上述的方法将标题与学科部分的公式录入完成。

为了减少工作量，让各班分数只录入一次，可以利用公式将各班的分数引用到“片区汇总”中来。在1班第一个学生的第一个学科成绩单元格内录入公式：“=IF(学校甲!E5="","",学校甲!E5)”，再拖拽复制出1班所有学生各科成绩的公式。用同样的方法我们依次将2班、3班的公式录入。

最后，我们将工作表“学校甲”复制出工作表“学校乙”、工作表“学校丙”，再按照上面的方法也将学校乙、学校丙各班学生的成绩公式录入。

至此，工作表“片区汇总”也制作完成了(图13)。

四、完成工作表“片区统计”

接下来是制作最麻烦的一个工作表“片区统计”。

1.还是按前面的方法将标题行完善。

2.将“学科”行也按前面的方法录入公式，但这里要注意的是：我们要将各班的某个统计指数排位，所以，在录入各学科名称的引用公式时，要隔一列录入一个学科名称引用公式。在第一个学科后的那个单元格录入公式：“=IF(C4="","","名次")”，这样，当第一个学科显示学科名称时，该单元格就会显示“名次”二字，否则就显示空白，再将这个公式复制到每个学科后的单元格内。按照同样的方法，分别将“及格率”、“优生率”、“差生率”的“学科”、“名次”的公式也录入(因为这个表中列数太多，为了方便公式的录入，可以将“学科”列或“名次”列的填充上颜色)。

3.接下来是最麻烦的一步——引用各班的各项指数，这就不能复制了，必须得一个单元格一个单元格的录入公式。例如，在“人平分”项，“学校甲1 班”第一个学科单元格中录入公式：“=学校甲!E76”，这个公式表示该单元格的数据引用工作表“学校甲”E76单元格的数据，工作表“学校甲”E76单元格就是学校甲1班第一个学科的人平分。

4.最后，利用RANK函数求出各项指数各班各学科片区排位——这也是我们最终想要得到的数据。在“人平分”指数项“学校甲1班”第一个学科后的 “名次”列录入公式：“=IF(ISERROR(RANK(C5,C$5:C$13)),"",RANK(C5,C$5:C$13))”(这个公式的意思是：如果用RANK函数求单元格C5相对于C5至C13的降序排位的结果是错误的——ISERROR函数就是检测一个值是否错误，此单元格就显示为空白，否则就显示用RANK函数求单元格C5相对于C5至C13的降序排位的结果)，再拖拽复制公式到C13单元格，再选中C5：C13后复制公式到“人平分” 指数项其它学科后的“名次”列。再按上述方法将“及格率”、“优生率”、“差生率”的名次排位公式录入(图14)。

“片区统计”完成了，前面所有工作表的数据，都是为得到本表的统计结果服务的。

五、完善工作簿“片区成绩统计”

到此，工作簿“片区成绩统计”已经基本完成了，但是，为了防止工作表的格式以及公式不小心被修改或删掉，可以将以后不需编辑的单元格保护起来。在以后的使用过程中，实际只需要对“首页”中检测的年份、年级、期段、学科名称、各学科总分以及各班学生的考号、姓名、各科成绩进行录入，所以，可以分别将 “首页”及各校统计表中需要录入数据的单元格选中，再点击“工具”——“保护”——“允许用户编辑区域”(图15)

——“新建”(图16)

——“确定”(图17)

——“保护工作表”(图18)

——输入密码后点“确定”，再输入一次密码点“确定”(图19)。

这样，“片区成绩统计”工作簿就算完全制作成功了。最后，将选中工作表“首页”中“年份”单元格，再将本工作簿保存为模板，以备后用。

附件：片区成绩统计示例表.xls 密码：123。

片区成绩统计示例表.xls
片区成绩统计示例表.xlt