论文部分内容阅读
自中考采用等级制来衡量考生成绩以来,初中学校的老师就对将学生成绩转化为等级,并对等级的排名感兴趣,学校处理成绩用得最多的是电子表格EXCEL,但有感于等级排序的复杂性,很多学校用EXCEL很难实现等级排序,现在本人就成绩等级转换和等级排序提出一些见解,抛砖引玉。
首先,先设定一个成绩处理表格,表头如下:
中考中的等级排序,遵循了三个原则:首先是总分等级排序,其次是综合等级排序,最后是科目顺序。因等级为字符型,又不单纯为字母,排序要求特殊,条件比较多,直接用函数排序有难度,将其按一定算法转化为数字排序则可行(以下数字排序都是从大到小进行排序)。
一、总分等级排序
总分等级,按A+〉A〉B+〉B〉C+〉C〉D〉E的顺序排序,将“A+”转为8,“A”转为7,“B+”为6,“B”为5,“C+”为4,“C”为3,“D”为2,“E”为1,根据数字排序,很容易比较出总分的等级排序。
二、综合等级排序
在总分等级相同的情况下,其次看综合等级,如学生总分等级都为A,张三为6A,李四为5A1B+,王五为4A2B+,则张三排序最前,再下来为李四,王五。综合等级排序里有一个“金牌原则”,比如:学生总分等级都为A,张三为1A+3A2B+,李四为6A,则张三排在李四前,即综合等级中只要出现有一个上一等级的,排序在等级都是低一等级的前面。处理方法如下:将“A+”转为10000000,“A”为1000000,“B+”为100000,“B”为10000,“C+”为1000,“C”为100,“D”为10,“E”为1,如张三为1A+3A2B+,李四为6A,转为数字后张三为13200000,李四为6000000,则张三排在李四前。
三、科目顺序排序
在总分等级,综合等级完全相同的情况下,再考虑科目顺序排序,科目顺序排序按语文、数学、英语、物理、化学、政史排序,比如说张三、李四的总分等级都是A,综合等级都是1A+3A1B+,如果张三的A+科目是英语,李四的A+科目是语文,则李四排在张三前。六个科目,设置一个六位数,第一位数字代表语文,第二位数代表数学,依此类推,等级按总分等级排序的数字转换一致。如张三科目等级为AAA+AAB+,李四科目等级为A+AAAAB+,转化后张三为778776,李四为877776,则李四排在张三前。
按上面的算法,排序数字按最大位数来产生,等级排序数的位数达15位。在单元格Z2输入公式
=IF(V2="A+",8,IF(V2="A",7,IF(V2="B+",6,IF(V2="B",5,IF(V2="C+",4,IF(V2="C",3,IF(V2="D",2,IF(V2="E",1,0))))))))*10^15+(COUNTIF(E2:O2,"=A+")*10^7+COUNTIF(E2:O2,"=A")*10^6+COUNTIF(E2:O2,"=B+")*10^5+COUNTIF(E2:O2,"=B")*10^4+COUNTIF(E2:O2,"=C+")*1000+COUNTIF(E2:O2,"=C")*100+COUNTIF(E2:O2,"=D")*10+COUNTIF(E2:O2,"=E"))*10^7+IF(E2="A+",8,IF(E2="A",7,IF(E2="B+",6,IF(E2="B",5,IF(E2="C+",4,IF(E2="C",3,IF(E2="D",2,IF(E2="E",1,0))))))))*10^5+IF(G2="A+",8,IF(G2="A",7,IF(G2="B+",6,IF(G2="B",5,IF(G2="C+",4,IF(G2="C",3,IF(G2="D",2,IF(G2="E",1,0))))))))*10^4+IF(I2="A+",8,IF(I2="A",7,IF(I2="B+",6,IF(I2="B",5,IF(I2="C+",4,IF(I2="C",3,IF(I2="D",2,IF(I2="E",1,0))))))))*10^3+IF(K2="A+",8,IF(K2="A",7,IF(K2="B+",6,IF(K2="B",5,IF(K2="C+",4,IF(K2="C",3,IF(K2="D",2,IF(K2="E",1,0))))))))*10^2+IF(M2="A+",8,IF(M2="A",7,IF(M2="B+",6,IF(M2="B",5,IF(M2="C+",4,IF(M2="C",3,IF(M2="D",2,IF(M2="E",1,0))))))))*10+IF(O2="A+",8,IF(O2="A",7,IF(O2="B+",6,IF(O2="B",5,IF(O2="C+",4,IF(O2="C",3,IF(O2="D",2,IF(O2="E",1,0))))))))
向下拖曳向得出排序數。经优化,也可输入如下公式
=VLOOKUP(V2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^15+(COUNTIF(E2:O2,"=A+")*10^7+COUNTIF(E2:O2,"=A")*10^6+COUNTIF(E2:O2,"=B+")*10^5+COUNTIF(E2:O2,"=B")*10^4+COUNTIF(E2:O2,"=C+")*1000+COUNTIF(E2:O2,"=C")*100+COUNTIF(E2:O2,"=D")*10+COUNTIF(E2:O2,"=E"))*10^7+VLOOKUP(E2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^5+VLOOKUP(G2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^4+VLOOKUP(I2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^3+VLOOKUP(K2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^2+VLOOKUP(M2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10+VLOOKUP(O2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)
可得到同样结果。最后按照等级排名从小到大排序,得出如下效果
首先,先设定一个成绩处理表格,表头如下:
中考中的等级排序,遵循了三个原则:首先是总分等级排序,其次是综合等级排序,最后是科目顺序。因等级为字符型,又不单纯为字母,排序要求特殊,条件比较多,直接用函数排序有难度,将其按一定算法转化为数字排序则可行(以下数字排序都是从大到小进行排序)。
一、总分等级排序
总分等级,按A+〉A〉B+〉B〉C+〉C〉D〉E的顺序排序,将“A+”转为8,“A”转为7,“B+”为6,“B”为5,“C+”为4,“C”为3,“D”为2,“E”为1,根据数字排序,很容易比较出总分的等级排序。
二、综合等级排序
在总分等级相同的情况下,其次看综合等级,如学生总分等级都为A,张三为6A,李四为5A1B+,王五为4A2B+,则张三排序最前,再下来为李四,王五。综合等级排序里有一个“金牌原则”,比如:学生总分等级都为A,张三为1A+3A2B+,李四为6A,则张三排在李四前,即综合等级中只要出现有一个上一等级的,排序在等级都是低一等级的前面。处理方法如下:将“A+”转为10000000,“A”为1000000,“B+”为100000,“B”为10000,“C+”为1000,“C”为100,“D”为10,“E”为1,如张三为1A+3A2B+,李四为6A,转为数字后张三为13200000,李四为6000000,则张三排在李四前。
三、科目顺序排序
在总分等级,综合等级完全相同的情况下,再考虑科目顺序排序,科目顺序排序按语文、数学、英语、物理、化学、政史排序,比如说张三、李四的总分等级都是A,综合等级都是1A+3A1B+,如果张三的A+科目是英语,李四的A+科目是语文,则李四排在张三前。六个科目,设置一个六位数,第一位数字代表语文,第二位数代表数学,依此类推,等级按总分等级排序的数字转换一致。如张三科目等级为AAA+AAB+,李四科目等级为A+AAAAB+,转化后张三为778776,李四为877776,则李四排在张三前。
按上面的算法,排序数字按最大位数来产生,等级排序数的位数达15位。在单元格Z2输入公式
=IF(V2="A+",8,IF(V2="A",7,IF(V2="B+",6,IF(V2="B",5,IF(V2="C+",4,IF(V2="C",3,IF(V2="D",2,IF(V2="E",1,0))))))))*10^15+(COUNTIF(E2:O2,"=A+")*10^7+COUNTIF(E2:O2,"=A")*10^6+COUNTIF(E2:O2,"=B+")*10^5+COUNTIF(E2:O2,"=B")*10^4+COUNTIF(E2:O2,"=C+")*1000+COUNTIF(E2:O2,"=C")*100+COUNTIF(E2:O2,"=D")*10+COUNTIF(E2:O2,"=E"))*10^7+IF(E2="A+",8,IF(E2="A",7,IF(E2="B+",6,IF(E2="B",5,IF(E2="C+",4,IF(E2="C",3,IF(E2="D",2,IF(E2="E",1,0))))))))*10^5+IF(G2="A+",8,IF(G2="A",7,IF(G2="B+",6,IF(G2="B",5,IF(G2="C+",4,IF(G2="C",3,IF(G2="D",2,IF(G2="E",1,0))))))))*10^4+IF(I2="A+",8,IF(I2="A",7,IF(I2="B+",6,IF(I2="B",5,IF(I2="C+",4,IF(I2="C",3,IF(I2="D",2,IF(I2="E",1,0))))))))*10^3+IF(K2="A+",8,IF(K2="A",7,IF(K2="B+",6,IF(K2="B",5,IF(K2="C+",4,IF(K2="C",3,IF(K2="D",2,IF(K2="E",1,0))))))))*10^2+IF(M2="A+",8,IF(M2="A",7,IF(M2="B+",6,IF(M2="B",5,IF(M2="C+",4,IF(M2="C",3,IF(M2="D",2,IF(M2="E",1,0))))))))*10+IF(O2="A+",8,IF(O2="A",7,IF(O2="B+",6,IF(O2="B",5,IF(O2="C+",4,IF(O2="C",3,IF(O2="D",2,IF(O2="E",1,0))))))))
向下拖曳向得出排序數。经优化,也可输入如下公式
=VLOOKUP(V2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^15+(COUNTIF(E2:O2,"=A+")*10^7+COUNTIF(E2:O2,"=A")*10^6+COUNTIF(E2:O2,"=B+")*10^5+COUNTIF(E2:O2,"=B")*10^4+COUNTIF(E2:O2,"=C+")*1000+COUNTIF(E2:O2,"=C")*100+COUNTIF(E2:O2,"=D")*10+COUNTIF(E2:O2,"=E"))*10^7+VLOOKUP(E2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^5+VLOOKUP(G2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^4+VLOOKUP(I2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^3+VLOOKUP(K2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10^2+VLOOKUP(M2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)*10+VLOOKUP(O2,{"A+",8;"A",7;"B+",6;"B",5;"C+",4;"C",3;"D",2;"E",1},2,0)
可得到同样结果。最后按照等级排名从小到大排序,得出如下效果