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

利用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宏编程，将烦琐和重复的数据处理过程变得轻松愉快，事半功倍。