利用Excel進行快速分班的技巧

每逢新學年開學之前，很多學校都面臨著一項重要的工作——學生分班，分班也就成為教學管理信息化的一個重要話題，分班的依據是學生的考試成績，總體要求是班級間各學科的總分或平均成績要互相接近，利用Excel可以大大提高工作效率，下面以某中學高一新生分班為例，談談如何利用Excel中的若干方法進行快速分班。

處理思路：XXX中學高中部今年招收了689名新生。為了保證各個班級的學生的學習成績大致平均，學校準備依照學生的中考成績的名次采用“蛇形”分班法，即按表1 所示的方式分成12個教學班。

表1 分班辦法

首先將學生情況及成績調入Excel工作表中，計算學生名次。先把D4：D692單元格區域命名為“中考成績”，然后在E4單元格中輸入“=RANK(D4，中考成績)”，或者直接輸入“=RANK(D4， ■D■4：■D■692)”，并為學生名次排序。

然后劃分班級：分別用以下五種方法計算每個學生應屬班級，如圖1所示

一、利用復制填充

在G4單元格鍵入“1班”，拖動單元格右下角的填充柄向下填充到G15，得到“1班~12班”，在G16、G17分別鍵入“12班、11班”，并同時選中G16、G17單元格并拖動填充柄填充至G27，得到“12班~1班”，選中G4至G27并拖動填充柄或復制粘帖至單元格G692結束。此方法雖然用法簡單，但是每當班數變化時就必須重復這些步驟。

二、利用自定義序列自動填充

(1)選擇菜單“工具→選項”，在彈出的對話框中單擊“自定義序列”標簽，在左側小窗口中單擊“新序列”，在右側小窗口中輸入“12班，11班，10班，9班，8班，7班，6班，5班，4班，3班，2班，1班， 1班， 2班，3班， 4班， 5班， 6 班， 7班， 8班， 9班，10班， 11班，12班”。注意序列是“12班~1班，1班~12班”，輸入完畢，單擊［添加］按鈕并［確定］退出，如圖2。

(2)在“G4”(第一名同學“班級”所在的單元格)中輸入“1班”后，用鼠標左鍵雙擊“G4”單元格右下角的控點，即可自動得出全部學生的班號。

三、利用多層嵌套的Excel函數

（1）將G2單元格命名為“班級數”，并輸入我們想要的班級數目“12”。

（2）在G4單元格內輸入公式：“=IF(MOD(ROW()-3， 班數)=0，IF(MOD((ROW()-3)/ 班級數，2)=0，1， 班級數)，IF(MOD(INT((ROW()-3)/班級數)，2)=0，MOD(ROW()-3，班級數)，班級數+1-MOD(ROW()-3，班級數)))，并拖動填充柄將此單元格填充到G5：G692。在輸入公式時，除漢字而外，所有字符一律要在英文輸入狀態下輸入，且不要加進空格。

（3）選擇G4：G692單元格區域，選擇【設置單元格格式】，在【數字】選項卡中的【分類】列表中選擇【自定義】，并在類型文本框中輸入“＃＃班”，可以發現G列的數據后出現了“班”字樣。

注意：如果分16個班，則將G2單元格改為“16”即可。

四、利用多層嵌套的Excel函數另外一種方法

先在G4單元格輸入“1”，G5單元格輸入“2”，在G6單元格輸入公式“=IF(G5-G4=1，IF(G5=12，G5，G5+1)，IF(G5-G4=-1，IF(G5=1，G5，G5-1)，IF(G5=12，G5-1，G5+1)))”，G7－G692單元格直接復制G6的公式即可（將鼠標移至G6單元格的右下角的復制點，鼠標變成實心十字時往下拖動鼠標到G692單元格）。到此，分班就做好了，但不能按照“班級”列排序，因為此列包含公式。

公式解釋：G6單元格主要根據上面兩個單元格（即G5、G4）來判斷，G5－G4有三種可能，即等于1、等于－1和等于0。

(1)等于1的話，如果G5已經是12（即最后一個班）那么G6就等于12（即與G5相同），否則的話G6就等于G5+1，即遞增。

(2)等于－1的話，如果G5已經是1（即第1個班）那么G6就等于1（即與G5相同），否則的話G6就等于G5－1，即遞減。

(3)等于0的話，如果G5已經是12（即最后一個班），那么G6就等于G5－1，即遞減，否則G5就是1（即第1個班），那么G6就等于G5+1，即遞增。

2、可以先選定G4－G692單元格，點“編輯”菜單的“復制”，再選定H4單元格，點“編輯”菜單的“選擇性粘貼”，在彈出的對話框中選“數值”，再點“確定”即可。

3、刪除“班級”列，再按“班級轉換”列排序，同一個班的學生就排在一起了。

注意：如果分16個班，則將G6單元格公式中的兩個“G5＝12”改為“G5＝16”，分M個班就改成G5＝M

以上方法如果要考慮入學成績（總分）和男女人數的勻稱搭配可首先用數據菜單里的“排序”命令將所有的名單按主關鍵詞“性別”，次要關鍵詞“成績”作一次降序排序，再使用上述方法。

五、利用Excel提供的VBA編程可以快速分班

VBA全稱為Visual Basic For Application ，以高級程序設計語言Visual Basic為內核，與EXCEL函數有機結合，功能強大。通過創建“宏”，將煩瑣和重復的數據處理過程交給程序代碼自動完成。

1、首先在EXCEL軟件中，按“姓名”、“性別”…“總分”“班級”的字段順序建立學生分班成績表（假定命名為“分班數據表”），其中“班級”字段的各單元格為空，留待運行宏代碼時自動填入分班結果。為保證分班后各班同一分數段男女生數的平衡，先將“分班數據表”按“性別”（主關鍵字），“總分”（次關鍵字）遞減排序。

2、在“分班數據表”中創建名為“分班”的宏，具體代碼如下：

Sub 分班()

Rem 定義變量

Dim i， j， k， n， bs， xks， num， s， t， max， z， x As Integer

Dim bj(500)， sum(10， 10)， cj(500， 10)， maxs(10)， mins(10) As Integer

Dim sc(10)， minb(10)， xsbj(10)， bjbj(10) As Integer

Rem 讀入數據及變量初始化

bs = Val(InputBox(\"請輸入班級數:\"))

Worksheets(\"分班數據\").Activate

n = 0

Do While Not IsEmpty(Cells(1， n + 1))

n = n + 1

Loop

xks = n - 4

n = 0: k = 2

Do While Not IsEmpty(Cells(k， 2))

n = n + 1

For i = 1 To xks

cj(n， i) = Cells(k， i + 3)

Next i

k = k + 1

Loop

For i = 1 To bs

For j = 1 To xks

sum(i， j) = 0

Next j， i

Rem 數據處理

num = 0

Do While (num * bs <= n)

If n - (num + 1) * bs < 0 Then z = n - num * bs Else z = bs

For i = 1 To z

For j = 1 To xks

cj(i， j) = cj(num * bs + i， j)

Next j， i

For i = 1 To bs

xsbj(i) = 0: bjbj(i) = 0

Next i

For k = 1 To z

For i = 1 To xks

maxs(i) = 0: mins(i) = 15000

Next i

For i = 1 To xks

For j = 1 To bs

If (bjbj(j) = 0) And (sum(j， i) > maxs(i)) Then maxs(i) = sum(j， i)

If (bjbj(j) = 0) And (sum(j， i) < mins(i)) Then mins(i) = sum(j， i): minb(i) = j

Next j， i

For i = 1 To xks

sc(i) = maxs(i) - mins(i)

Next i

max = sc(1): s = 1

For i = 2 To xks

If max < sc(i) Then

max = sc(i): s = i

End If

Next i

max = -1

For i = 1 To z

If (xsbj(i) = 0) And (max < cj(i， s)) Then max = cj(i， s): t = i

Next i

xsbj(t) = 1: bj(num * bs + 1) = minb(s): x = minb(s)

For i = 1 To xks

sum(x， i) = sum(x， i) + cj(t， i)

Next i

bjbj(x) = 1

Next k

num = num + 1

Loop

Rem 將分班結果輸入EXCEL 表格“班級”字段

For i = j To n: Cells(i + 1， 1) = bj(i): Next i

End Sub

3、執行宏后，對“分班數據表”按班級序號遞增排序，即完成任務。

正確利用EXCEL數據填充，單元數據引用，公式與函數以及通過EXCEL宏編程，將煩瑣和重復的數據處理過程變得輕松愉快，事半功倍。