用EXCEL模拟中考对成绩等级转换和等级排序.docVIP

用EXCEL模拟中考对成绩等级转换和等级排序 　　自中考采用等级制来衡量考生成绩以来，初中学校的老师就对将学生成绩转化为等级，并对等级的排名感兴趣，学校处理成绩用得最多的是电子表格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+”转“A”为1000000，“B+”为100000，“B”为10000，“C+”为1000，“C”为100，“D”为10，“E”为1，如张三为1A+3A2B+，李四为6A，转为数字后张三李四为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