用EXCEL模擬中考對(duì)成績(jī)等級(jí)轉(zhuǎn)換和等級(jí)排序

李春

自中考采用等級(jí)制來衡量考生成績(jī)以來，初中學(xué)校的老師就對(duì)將學(xué)生成績(jī)轉(zhuǎn)化為等級(jí)，并對(duì)等級(jí)的排名感興趣，學(xué)校處理成績(jī)用得最多的是電子表格EXCEL，但有感于等級(jí)排序的復(fù)雜性，很多學(xué)校用EXCEL很難實(shí)現(xiàn)等級(jí)排序，現(xiàn)在本人就成績(jī)等級(jí)轉(zhuǎn)換和等級(jí)排序提出一些見解，拋磚引玉。

首先，先設(shè)定一個(gè)成績(jī)處理表格，表頭如下：

中考中的等級(jí)排序，遵循了三個(gè)原則：首先是總分等級(jí)排序，其次是綜合等級(jí)排序，最后是科目順序。因等級(jí)為字符型，又不單純?yōu)樽帜福判蛞筇厥?，條件比較多，直接用函數(shù)排序有難度，將其按一定算法轉(zhuǎn)化為數(shù)字排序則可行（以下數(shù)字排序都是從大到小進(jìn)行排序）。

一、總分等級(jí)排序

總分等級(jí)，按A+〉A(chǔ)〉B+〉B〉C+〉C〉D〉E的順序排序，將“A+”轉(zhuǎn)為8，“A”轉(zhuǎn)為7，“B+”為6，“B”為5，“C+”為4，“C”為3，“D”為2，“E”為1，根據(jù)數(shù)字排序，很容易比較出總分的等級(jí)排序。

二、綜合等級(jí)排序

在總分等級(jí)相同的情況下，其次看綜合等級(jí)，如學(xué)生總分等級(jí)都為A，張三為6A，李四為5A1B+，王五為4A2B+，則張三排序最前，再下來為李四，王五。綜合等級(jí)排序里有一個(gè)“金牌原則”，比如：學(xué)生總分等級(jí)都為A，張三為1A+3A2B+，李四為6A，則張三排在李四前，即綜合等級(jí)中只要出現(xiàn)有一個(gè)上一等級(jí)的，排序在等級(jí)都是低一等級(jí)的前面。處理方法如下：將“A+”轉(zhuǎn)為10000000，“A”為1000000，“B+”為100000，“B”為10000，“C+”為1000，“C”為100，“D”為10，“E”為1，如張三為1A+3A2B+，李四為6A，轉(zhuǎn)為數(shù)字后張三為13200000，李四為6000000，則張三排在李四前。

三、科目順序排序

在總分等級(jí)，綜合等級(jí)完全相同的情況下，再考慮科目順序排序，科目順序排序按語文、數(shù)學(xué)、英語、物理、化學(xué)、政史排序，比如說張三、李四的總分等級(jí)都是A，綜合等級(jí)都是1A+3A1B+，如果張三的A+科目是英語，李四的A+科目是語文，則李四排在張三前。六個(gè)科目，設(shè)置一個(gè)六位數(shù)，第一位數(shù)字代表語文，第二位數(shù)代表數(shù)學(xué)，依此類推，等級(jí)按總分等級(jí)排序的數(shù)字轉(zhuǎn)換一致。如張三科目等級(jí)為AAA+AAB+，李四科目等級(jí)為A+AAAAB+，轉(zhuǎn)化后張三為778776，李四為877776，則李四排在張三前。

按上面的算法，排序數(shù)字按最大位數(shù)來產(chǎn)生，等級(jí)排序數(shù)的位數(shù)達(dá)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））））））））

向下拖曳向得出排序數(shù)。經(jīng)優(yōu)化，也可輸入如下公式

=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）

可得到同樣結(jié)果。最后按照等級(jí)排名從小到大排序，得出如下效果