用WPS表格完成片区成绩统计(4)
三、利用函数求人平分、及格率、优生率、差生率
接下来就将所有表中涉及到的函数分别进行说明。
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班的公式录入。