用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+”转为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）

可得到同样结果。最后按照等级排名从小到大排序，得出如下效果