Excel按成績快速分班有妙招

知識點：Excel中利用公式排序和在Word中“排序”的用法 適用場景：利用Word或者Excel記錄的學生成績單快速進行分班。

學校開學第一件事就是對新生進行分班，通常分班都要求先按總分排出名次，再按名次把第1名分到1班、第2名2班、第3名3班、第4名4班、第5名5班、第6名1班、第7名2班、第8名3班、第9名4班、第10名5班……如此順序進行分班，以保證把高分、低分學生平均分配到各個班，同時還要保持各班女生人數(shù)的平均。在Office 2007中要按這種要求分班是很簡單的，我們就以分成5班來說吧。

在Excel中快速分班

通常學生記錄表都被保存在Excel工作表中，我們只要使用Excel 2007中的函數(shù)就可以非常輕松地自動完成分班操作。

利用函數(shù)排序

用Excel 2007打開學生記錄表，在最右側(cè)增加兩列(E、F列)分別輸入列標題為排序總分、班級，并先在I1單元格輸入分班數(shù)5。在E2輸入公式=D2+IF(C2=“女”，1000)+COUNTIF(D$1:D1，D2)/1000，在F2輸入公式=IF(D2，MOD(RANK(E2，E:E)，H$1)+1，“”)。選中E2:F2單元格拖動填充柄將公式向下復制填充到F2000，復制行數(shù)應確保超過可能的最高學生人數(shù)。馬上可看到所有學生的班級已經(jīng)自動分配出來了(圖1)。最后選中E列右擊選擇“隱藏”。

E列公式給女生分數(shù)全部加1000，并用COUNTIF統(tǒng)計同分記錄數(shù)順次加上0.001、0.002、0.003……使其變成不同分數(shù)。這樣按E列排名時，女生會集中排在前面，也不會出現(xiàn)同分同名次的問題。F列則是用RANK求出E列分數(shù)的名次，再按名次分班，同時用If判斷總分為空的行班級為空。由于女生都排在前面會先平分到各班也就實現(xiàn)了男女平衡。

注：有的學校要求按名次以1234554321的順序分班，這也簡單。在I1輸入公式=IF(ROW()>H$1，H$1*2+1-ROW()，ROW())，并向下復制填充至I40即可按分班數(shù)自動生成1234554321序列。然后把F2的公式改成=IF(D2，INDEX(I:I，MOD(RANK(E2，E:E)，H$1*2)+1)，“”)并向下填充即可。

分班打印

分班后往往需要把各班的學生名單分開打印輸出，這可以用復選來實現(xiàn)。選中A:F列，在“數(shù)據(jù)”選項卡下單擊“篩選”，在表格首行會顯示篩選按鈕。現(xiàn)在單擊F1單元格的篩選按鈕，在彈出列表中只勾選“1”復選項，確定即可篩選出1班學生名單(圖2)。只要直接打印或復制它到表中保存即可。