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

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