我校在对教师进行绩效考核中，需要各位教师所教学科的人平分、及格率、优生率、差生率在片区几所学校所有班级中的排位情况，每到期末我的工作量都相当大，为了达到一劳永逸的目的，就制作了一个片区成绩统计表。下面就将此表制作的过程作一简要说明。望这篇文章能起到抛砖引玉的作用，敬请各位同仁指教。
一、制作所需表格
首先，将需要的工作表制作出来。为了保证各表间数据引用方便，利于修改，我们尽量使几个工作表的样式、格式一致。为了减少工作量，示例表中我只做了三所学校(分别是学校甲、学校乙，学校丙)，每所学校三个班，实际中我们可以根据实情进行增减，方法都是一样的。
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
【用WPS表格完成片区成绩统计】相关文章：