教育应用：分班（Excel_VBA） 规范化通用化应对各种要求 的实现

龙行九州

分班问题需要正确的要求表述
论坛中涉及到分班的帖子看了不少，都是各种各样的方法。然没有理想的分班方案，适应性很差。所以在此先理出分班的要求，再来分班，以提高通用性和实用性。
总分S分班因为非常容易实现不在本讨论之列。补平平均数法，各科分布不均，老师关注的那部分学生分班不匀成争议焦点。
分班问题要求是非常多的，不可能做到决对公平的。从众多的要求中理出主要地要求进入设计算法，才能编出合理的分班VBA。
因本人不擅长VBA，只是从要求的角度来提出这一问题。请大家再理一理合理的要求后，再出几个版本的好的分班VBA
1、分班结果任课教师最计较的是什么？——是学校如何给教师兑现的。一般地绝大多数都偏重于自己那一科的进优进线人数，其次才是平均数。
我县是方法是：每次联考将全县各校这一年级的分数全部汇总一起，象中考一样划出两档总分招录分数线。按同样的人数对各单科也划出两档招录分数线，同一名学生部分分进了线，总分也进了线，叫双进，好听的词叫这一科同步上了。
至于男生女生、住宿生是否平均，各科教师关心度不高。一般分班相对比较均衡后，男女生人数差别不是非常大的。
理出分数要求，可用多个不同功能的表，一步一步来实现功能，最后分班汇总及报告。甚至设计出通用的分班表，然后手工将学生分段后进入分班中。

科目有：语数外物史地生，没有化学九年级毕业了。所有计算 成绩优先考虑：语数外物 其次短线史地生。

分数划段对难易度影响很大，所以，设置区间分数线下得不是力，各科老师也不太关注。所以下文的分数段是指：尽量把这一步骤中的学生分得均匀些。重点关注的是 ：一批线有多少人，二批线（含一批）有多少人，常规及格优生多少人。不同步骤侧重不同的分数线，应该容易实现些。

1、对总分和各科都进一批线的学生 进行分班固定下来。各科各分数段人数尽量分布匀这个可以不考虑最好的学生各科都比较好，当然也可以考虑以兼容第二步的算法予以考虑。不考虑班级平均分问题。
2、将上一步得到的学生班号固定下来不再变化，但分数情况参与接下来的计算。对总分和各科都进二批线的学生 接着上面的分班补充分班。各科各分数段人数尽量分布均匀一点。不考虑班级科平均分问题。考虑第一步分配人数不整齐的补偿问题。
3、上面将一线生，二线生分完了。现在手工再加入认为有上升潜力的学生（算是三线生吧）接着分。现在情况变得复杂了一点，算法实现难度也大多了：
优先要求依次是
分完后，连同原来的已经分班所有学生各科各班的进一线人数大致相等，各班各科进二线（包括进一线）人大致相等，考虑上一步得到的结果各班人数不均的补偿问题。
不考虑各班各科平均成绩。
4、同样的方法手工分出一小部分所谓四线生吧，算法同上一步。手工选择人时要将某一两科比较好进了二线，其它学科都差总分没进线的学生编入进来参与分班。
5、同样的方法手工分出一小部分所谓五线生吧，只要有一科及格就搞进来。算法发生变化：分班后的结果使各班及格人数大致相等，还考虑的是和下一步的算法最好合在一起，结果容易得到一些，也更好一些。
6、全科不及格尾巴学生刹尾，接着继续分班，只一个要求：优先补平各学科的平均分，给总平均分各班差异，认为差异大再重分尾巴学生再看差异（自己要提前备份）。

设计考虑：
为灵活调整，不自动一揽子把各层学生分完给出最后结果，每执行一步后，可手工修改一下结果进入下一步，即人为干预，但干预的前提对基本算法步骤要了解的基础上。为干预得有目的，得一个即时报表，给出各分数段分布情况。
设置表：设置各科及总分数段、一线、二线分数线、及优生数。
学生成绩表：班号列单元格有数字了，就固定下来，不参与下一步的计算。分班号 姓名 纳入上面算法的第几步  总分 各科成绩
报表列表比较分班结果

假期事太多还没安定下来，表还没具体设计。VBA初学，无奈要实现这些功能实现不了。请有这方面要求的同行，先理出抽出正确的要求模型后，才能编出适应性广的分班表来。

相关帖子：
教育应用贴（分班、统计、课表等）1142楼添加运动会成绩统计表20121215
分班问题
随机算法对学生平衡分班，以每科平均分最接近为目标，人数科目班数什么都无限制。。。
http://club.excelhome.net/thread-156834-2-1.html

这些帖子中有些很好的算法，可以在分步中借鉴使用。


补充内容 (2013-9-25 23:46):
请用电梯直达
19楼考虑因素非常多的分班示例，还有分后自动统计各种均衡参数。
26楼修正每班必须10人以上，不能先固定某生在某班
30楼修正固定学生数太多时出错的问题

补充内容 (2013-9-25 23:53):
更正：26、30楼增加 保存恢复VBA分班前的手工分班数据，即恢复VBA运行前的分班列。并非不能固定某生在某班。

龙行九州

用EXCEL开发的教务小软件：新生录取分班

灰袍法师

楼主的想法很好

但是我觉得许多学校的分班要求都不一致，不可能做出一个通用的程序。

我当时就是考虑了这一点，所以把一切要求都看作分数，分批次，每批都是随机分取最好一个。

结果发现比考虑种种要求的程序，分得更均匀。

其实关键不是分班算法，而是定义分班结果好坏的评估函数。

物理平均分完全平衡，但是物理优等生人数＋2 VS 物理平均分＋2，但是物理优等生人数完全平衡

上述两个情况谁算“比较好”的结果？

没有定义好的评估函数，就不可能在分班程序逐渐把“最好 ”的结果提取出来。

上来问分班的，通常列举一大通要求，但是却都没有精确说明优先次序，以及发生冲突时的取舍标准。

所以我看完还是一点都不知道评估函数要怎么写。

九月来临，估计又有一大波上来问的帖子了。

龙行九州

EXCEL VBA 分班问题 设计 规范化设想：

1、为得出理想的结果，不能图简单，一次用一个算法将全部学生分完。各地各校要求不尽一样，合在一起分班的成绩差异性越大，结果越不理想。而用一个算法多次分批追加分班结果应该是比较理想的了，因为是有针对性的有目的性的将学生多次添加进去分班。
2、能将学生和成绩全部粘贴进去，增加批次列。每次执行一个批次的追加分班均是在前面的基础上再均衡，VBA不修改前一批次分班自动或手动的结果。每批分班后，彩色选定这一批次的分班号单元格，以提示本次手工可修正个别学生本次范围。（再执行后清除上次彩色）。
3、用各自的算法实现各班各科的均衡。如楼上的多次随机选优法就比较好，象名称一样比较均衡。
4、同时给出各班各科已经分班的结果报表（最终用公式实现，手工修改个别分班即时变化）。

龙行九州

补充：
计算慢一点不要紧，要结果好才行。即使花上几个小时也无所谓。
各种不同的要求，转化为不同的批次追加分班来实现。这个要看分班后任课教师们的关注点是什么来在做数据准备时做好设定。
每批次的分班任务就是对追加分班批次进行分班，达到和已分批次合在一起的结果最做优化。这是算法的核心，不管用什么算法，都是围绕这一目的。

灰袍法师

嗯，楼主在上面的意见，和我的想法是一致的。

我这个附件验证了一下 分批次分班是否更好，结果是非常肯定的。

附件的批次只是简单设置为 每批要分的学生数 ＝ 班数 x 10

每批都用之前的随机算法求 每班人数尽可能一样，每班男女数尽可能一样，然后是每班的各科成绩，总分平衡。

分班的结果已经比之前的做法好多了（当然耗时也多了）

估计增加一列：批次，然后按批次来分就足够用，又可以灵活处理各种情况了

至于要求 60-80 算普通生，80-90算良好，90-100算优秀，各班的各段人数差不多

我想可以这样处理：再增加一列＝学生的阶段指数，如果有单科是良好就得10分，优秀算20分，各单科的阶段指数加总就是这个学生的阶段指数。

这样也就可以把“各班学生的优良生分布也差不多”的要求也在结果中体现出来。

等我有空再改一下。

分班_每次随机分一个班_兼容人手分班结果.rar (50.98 KB, 下载次数: 225)

2013-7-16 04:51 上传

点击文件名下载附件

补充内容 (2013-8-1 03:39):
有更好的版本在后面回帖，此附件无需下载。

lzyamo3057

分班确实是很让人头痛的问题，以往具体的分法为：
1、考虑本地、外来学生人数基本相等
2、考虑男女生人数基本相等
3、考虑寄宿生男与寄宿生女人数基本相等
4、考虑各科平均分基本相近
5、考虑总分基本相近
具体的做法如下：
1、先拆分本地与外来学生
2、同时再拆本地与外来学生男女生。
到此，由原来的一个工作表拆成四个工作表。
3、对折开的四个工作表，按学生成绩排序。然后男生的工作表中插入签号：1、2、3、4。。。。4、3、2、1
女生的表格中，插入4、3、2、1。。。。1、2、3、4
根据这些签号，汇总成初始的班级。然后再考虑寄宿生人数的问题。

但是，几年来，会发现，抽到第1签的，往往会存在班级两级分化比较严重，优秀的学生多些，差的学生也多些。而抽到中间的签号的，班级中没有尖子生，中等的学生偏多些。

lzyamo3057

灰袍法师 发表于 2013-7-16 04:52
嗯，楼主在上面的意见，和我的想法是一致的。

我这个附件验证了一下 分批次分班是否更好，结果是非常肯定 ...

根据您的程序，我进行了一次试分班，然后按总分降序排列时，发现试分班考虑到各科平均分与总分平均分是非常接近，但是发现各班优秀也即种子选手人数分布不均匀。结果如下图。

lzyamo3057

考虑到生源素质不是很理想，所以每个班主任都希望得到所谓的种子选手人数应该差不多。

灰袍法师

这个附件跟上面是一样的，增加了“优秀生”计算列，单科80分以上算单科优秀，同时计算三个科目的优秀生平均分配。

可以看到“优秀生”之类的要求，一样是可以转化为“学科分数”，从而平均分配，实际上，任何要求都可以用这种“数字化”的方式来平均分配。

而且，“数字化”的时候，给的分数越高，其平均分配的程度就越高。

另一个做法是：

由于此附件只会分配 分班列还是空白 的记录，所以可以先把优秀生分配一次，分完再把其他普通生数据粘贴到末尾，再分一次就很平均了。

后面有更好的附件，此附件删除。