利用Excel進(jìn)行快速分班的技巧

每逢新學(xué)年開(kāi)學(xué)之前，很多學(xué)校都面臨著一項(xiàng)重要的工作——學(xué)生分班，分班也就成為教學(xué)管理信息化的一個(gè)重要話題，分班的依據(jù)是學(xué)生的考試成績(jī)，總體要求是班級(jí)間各學(xué)科的總分或平均成績(jī)要互相接近，利用Excel可以大大提高工作效率，下面以某中學(xué)高一新生分班為例，談?wù)勅绾卫肊xcel中的若干方法進(jìn)行快速分班。

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

表1 分班辦法

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

然后劃分班級(jí)：分別用以下五種方法計(jì)算每個(gè)學(xué)生應(yīng)屬班級(jí)，如圖1所示

一、利用復(fù)制填充

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

二、利用自定義序列自動(dòng)填充

(1)選擇菜單“工具→選項(xiàng)”，在彈出的對(duì)話框中單擊“自定義序列”標(biāo)簽，在左側(cè)小窗口中單擊“新序列”，在右側(cè)小窗口中輸入“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”(第一名同學(xué)“班級(jí)”所在的單元格)中輸入“1班”后，用鼠標(biāo)左鍵雙擊“G4”單元格右下角的控點(diǎn)，即可自動(dòng)得出全部學(xué)生的班號(hào)。

三、利用多層嵌套的Excel函數(shù)

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

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

（3）選擇G4：G692單元格區(qū)域，選擇【設(shè)置單元格格式】，在【數(shù)字】選項(xiàng)卡中的【分類】列表中選擇【自定義】，并在類型文本框中輸入“＃＃班”，可以發(fā)現(xiàn)G列的數(shù)據(jù)后出現(xiàn)了“班”字樣。

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

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

先在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單元格直接復(fù)制G6的公式即可（將鼠標(biāo)移至G6單元格的右下角的復(fù)制點(diǎn)，鼠標(biāo)變成實(shí)心十字時(shí)往下拖動(dòng)鼠標(biāo)到G692單元格）。到此，分班就做好了，但不能按照“班級(jí)”列排序，因?yàn)榇肆邪健?/p>

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

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

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

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

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

3、刪除“班級(jí)”列，再按“班級(jí)轉(zhuǎn)換”列排序，同一個(gè)班的學(xué)生就排在一起了。

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

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

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

VBA全稱為Visual Basic For Application ，以高級(jí)程序設(shè)計(jì)語(yǔ)言Visual Basic為內(nèi)核，與EXCEL函數(shù)有機(jī)結(jié)合，功能強(qiáng)大。通過(guò)創(chuàng)建“宏”，將煩瑣和重復(fù)的數(shù)據(jù)處理過(guò)程交給程序代碼自動(dòng)完成。

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

2、在“分班數(shù)據(jù)表”中創(chuàng)建名為“分班”的宏，具體代碼如下：

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 讀入數(shù)據(jù)及變量初始化

bs = Val(InputBox(\"請(qǐng)輸入班級(jí)數(shù):\"))

Worksheets(\"分班數(shù)據(jù)\").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 數(shù)據(jù)處理

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 將分班結(jié)果輸入EXCEL 表格“班級(jí)”字段

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

End Sub

3、執(zhí)行宏后，對(duì)“分班數(shù)據(jù)表”按班級(jí)序號(hào)遞增排序，即完成任務(wù)。

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