成绩分析模板2017修改记
缘起:2016年以来,根据有关人士的要求,成绩分析模板相继增加了前50名入模分析表、学科英雄榜。由于原登分册中,总名次与班级名次,实行非统计对象与统计对象的双轨排名,即统计对象单独排名,但非统计对象与分数相同的统计对象,有相同的名次。所以,以班级号*1000+班级名次形成的查询编号,可能失去唯一性。在实践中,入模分析表与各种表彰表,都出现错漏与重复现象。
修改思路:
首先,尝试将非统计人员的名次取消,这样就不会出现相同的查询编号。但实践中出现某些表的公式没有计算结果。通过公式审核,发现统计数据出现错误值。判断:名次中出现空值,公式就无法正常计算。
PS:在公式审核的过程中,掌握了F9的妙用。在公式中选中某一部分,按F9就会显示该部分的计算结果;点撤销,即可恢复公式。
然后,设想所有人员都进行绝对排名,由此形成唯一的查询编号。考虑某些查询需要排除非统计对象,估计有难度,而且可能会出现中断,所以没有进行实践。
最后,采用将非统计对象的名次,置于所有统计对象之后,而且也不重复,以保证查询编号的唯一性,以及非统计对象不进入各类表彰与入模统计。
修改经过:
1.修改登分册的总名次,公式如下:
=IF(kh=0,"",IF(L2="",SUMPRODUCT((zf>M2)*(bz=""))+SUMPRODUCT((M$2:M2=M2)*(L2="")),COUNTA(kh)-COUNTA(bz)+SUMPRODUCT((zf>M2)*(bz""))-1+SUMPRODUCT((M$2:M2=M2)*(L2""))))
意为:若A列当前考号为空,M列当前总名次也为空,以避免出现错误值;
若A列当前考号存在,则继续判断。若L列当前备注为空(即为统计对象),则计算M列当前总分,在所有统计对象的总分集合中的绝对名次;否则,计算非统计对象在所有统计对象名次之后的绝对名次。
PS:实践中,仍出现相同名次。通过公式审核,发现在SUMPRODUCT函数中,(M$2:M2=M2)*(L2="")与(M$2:M2=M2)*(L2"")的引用范围不对称。均修改为(M$2:M2=M2)*(L$2:L2=L2),错误解决。由此联想,或者原来的错误根源,也许并不在于非统计对象与统计对象的双轨排名,而在于sumproduct函数的各参数的引用不对称。有待验证。
2.修改登分册的查询编号,公式为:
=IF(A2=0,"",E2*1000+N2)
意为:若首列当前考号存在,则用E列当前班级号乘以1000,再加上N列当前总名次,作为S列的当前查询编号。
PS:用总名次还是班名次作查询编号的尾数,是有区别呢,还是有区别呢?
3.修改登分册的班名次,公式如下:
=IF(kh=0,"",IF($L2="",SUMPRODUCT((zf>$M2)*(bz="")*(bj=$E2))+SUMPRODUCT(($M$2:$M2=$M2)*($L$2:$L2=$L2)*($E$2:$E2=$E2)),SUMPRODUCT((bj=$E2)*(bz=""))+SUMPRODUCT((zf>$M2)*(bz"")*(bj=$E2))+SUMPRODUCT(($M$2:$M2=$M2)*($L$2:$L2=$L2)*($E$2:$E2=$E2))))
4.在登分册中,增加各科科次,同样将非统计对象置后,以免其出现在各表彰表中;最好定义各科次为名称。然后供分班成绩表中查询。修改名称CXQY的列数。
5.在登分册中,增加一列为其它学科,以备录入思品或体育或其它。
6.在分析表中,修改各班后10名平均分,已排除非统计对象,例如:
原语文平均分公式:
=AVERAGE(IF((bj=分析表!$A30)*(bmc>$B4-10),yw))修改为:=AVERAGE(IF((bj=分析表!$A30)*(bmc>$B4-10)*(bmc
7.在分析表中,修改入模表,以名次而非分数为查询依据,排除非统计人员。公式如下:=IF(ISERROR(SUMPRODUCT((bj=$A57)*(bz="")*(yw>=INDEX(yw,MATCH(50,ywkc,0),0)))),"",SUMPRODUCT((bj=$A57)*(bz="")*(yw>=INDEX(yw,MATCH(50,ywkc,0),0))))
8.修改完成并验证无差后,将各表中的公式列保护,将辅助列隐藏,将各科成绩录入区域设置数据有效范围。