利用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("请输入班级数:"))