超级强大1：七年级新生分班，解决了重名与择班问题

aoe1981

　　原帖地址：http://club.excelhome.net/thread-1133631-1-1.html（向“达州张先生”致以敬意！）
　　为何新发帖？
　　1.原帖过于火爆，楼层太多，恐找寻不便；
　　2.原帖各路英豪，思路纷呈，各有所长，本帖集中于本人的思路予以解决；
　　3.原帖遗留有“重名”与“择班”问题，悬而未决，本帖用本人的思路予以解决；
　　4.本帖属于个人的原创性的东西较多。
　　与原帖的关系？
　　要更好的理解本帖，必须以原帖为重要和必要基础，否则，本帖极可能是无本之木、无源之水。
　　本帖附件：
　　 5七年级分班(1234523451或1234554321分班及解决重名与择班).rar (41.98 KB, 下载次数: 869)

2014-8-2 14:47 上传

点击文件名下载附件

　　附件截图：
　　
　　附件详细说明再行补充。
　　附件的效率性、真实数据下的可靠性，有待检验。


　　声明：上图中按钮点击的正确顺序见76楼：
　　http://club.excelhome.net/forum. ... 1141181&pid=7780008
　　附件已于声明之时更新，图片不予更新，便于对比。

　　二次声明：2014年8月2日附件更新出于222楼考虑：
　　http://club.excelhome.net/forum. ... 1141181&pid=7784649

aoe1981

　　本楼先上一张工作簿结构图：
　　
　　内中共有4个模块，分别为4段独立的代码，各完成相对独立的功能。当然，在操作上有逻辑上的先后顺序，也考虑、规避了部分出错情况。

aoe1981

　　先说“分班”模块，与先前帖子中上传附件一致（http://club.excelhome.net/forum. ... 631&pid=7772329）。
　　
　　上述图片中打箭头的两行代码只能同时运行一行，目前运行后采用的是12345、23451、34512……这样的分班方法（个人的测试告诉我，在总人数偏小时，这个方法分班后的成绩均衡性好一些也即各班平均分标准差小一些，有待更多测试）。如果取消注释第一个箭头所在行，注释第二个箭头所在行，则变为1234554321、2345115432、3451221543……这样的分班方法（同理，人数多时，这个方法好）。
　　以上核心分班算法来自于香川大侠，在此再次申明版权！同时，需注意的是，没有采用香川大侠独创的自定义函数法，因代码过长。

aoe1981

排序、重名、择班三个模块为本附件中新增的3个模块，下面争取较清楚的介绍下……

aoe1981

　　截取一段排序模块中的代码：

1. Public Sub px1() '总分降序＋数学降序＋语文降序＋综合降序
   
2. Dim zfl%, xbl%, xhl%, xml%, sxl%, ywl%, zhl%, h&, l&
   
3. Application.ScreenUpdating = False
   
4. With 报名信息表
   
5.     zfl = .Rows(2).Find(what:="总分").Column
   
6.     sxl = .Rows(2).Find(what:="数学").Column
   
7.     ywl = .Rows(2).Find(what:="语文").Column
   
8.     zhl = .Rows(2).Find(what:="综合").Column
   
9.     h = .UsedRange.Rows.Count
   
10.     l = .UsedRange.Columns.Count
    
11.     With .Sort
    
12.         .SortFields.Clear
    
13.         .SortFields.Add Key:=Range(Cells(3, zfl), Cells(h, zfl)), Order:=xlDescending
    
14.         .SortFields.Add Key:=Range(Cells(3, sxl), Cells(h, sxl)), Order:=xlDescending
    
15.         .SortFields.Add Key:=Range(Cells(3, ywl), Cells(h, ywl)), Order:=xlDescending
    
16.         .SortFields.Add Key:=Range(Cells(3, zhl), Cells(h, zhl)), Order:=xlDescending
    
17.         .SetRange Range(Cells(3, 1), Cells(h, l))
    
18.         .Apply
    
19.     End With
    
20. End With
    
21. Application.ScreenUpdating = True
    
22. End Sub

复制代码

　　貌似很复杂，或者是编写的很啰嗦，有三个原因吧：
　　1.基本代码来自于Excel2010中录制，在此基础上修改了下，囿于水平；
　　2.增强了排序操作的适应性，当“报名信息表”增加减少学生（增加减少行）、增加减少列、移动列时，代码能够自动识别，保证排序的正确性，当然，不是针对所有情况，比如：第一行是表头，第二行是标题行，这个是万万不能改动的！
　　3.由于单纯总分降序排序后总分相等的学生可能很多，你再做一些其他的排序，再返回总分降序排序时，会发现名次不是很吻合，因为，你没有明确告诉计算机总分相同时以什么指标为依据排序，它采取了默认的方法……为了规避这种现象，我实际上用的是：总分降序＋数学降序＋语文降序＋综合降序，这样唯一性大大增强，当然，首要是针对真实的数据，不是变造的。

aoe1981

　　重名模块的作用在于定位、统计重名。
　　定位的意思是在“报名信息表”中进行选定操作，当对其中一部分重名学生进行手动择班（放在不同班，可随意择班，此时不用考虑成绩、性别），选定会被取消，造成不便，有两种方法可以解决：
　　1.在此之前，先按姓名升序排序，则重名被靠拢在一起；
　　2.改完一组后，还可以继续运行本模块，进行再一次、再两次……的重名定位。
　　重名统计功能会罗列出某某名字出现了多少次……这样，此时，也可以手动记录在纸上，方便比对程序运行的正确性，检测其可靠性。

　　另外，很重要的一点是：重名问题被归入择班问题一并解决。

aoe1981

　　择班模块是我这个附件中的重头戏了，花费了更多的时间和精力！
　　择班模块的运行分为5个大的步骤：
　　（一）读取分班信息；
　　（二）查找、存储择班与分班不一致的学生，并简要报告；
　　（三）交换择班学生与对应班最佳匹配学生；
　　（四）给各分班报表重新赋值；
　　（五）重新计算各班男女人数与总分。
　　要说明的有以下几点：
　　1.在原始数据表“报名信息表”中有两列：“择班”、“班级”。前者用来手动事先择班，附件中设置了灵活的有效性序列，可以点击选择；后者用来计算机分班后自动填充所分班级信息；
　　2.什么叫做“需调整的择班学生”，就是自动分班所在班级与事先择班班级不一致的学生；
　　3.分班思路：不是把事先择班的学生先剔除出去，而是照常进行分班，最后在进行择班调整；
　　4.调整依据：从对应班里选择同性别名次最接近的学生进行对换，当然该对调学生不能是择班学生，这是为了防止重复调换，导致程序死循环或替换不干净。这样于实际中有个别情况可能不太合理，举例就是：甲择了3班分在5班，其名次112，性别男；乙择了5班分在3班，其名次是111，性别男。很明显，这时，将这二人直接对调效果更好，但是我的附件中不会这样，它只会从对应班的非择班生里选择同性别的、名次最接近的、首次找到的一个学生进行对调！

aoe1981

　　关于学生总分成绩的排名，这个自然是降序处理，便于序列填充名次。
　　为什么是序列填充名次呢？
　　为了确保名次的唯一性。
　　因此，本附件中坚决杜绝rank的美式排名以及中国式排名，这两种排名中均有重复名次的出现。
　　为了保证相对公平，在总分降序的基础上，加入数学降序、语文降序、综合降序……这样唯一性强多了……

　　所以这样做是前后相承的，在处理择班生对调时，其依据不再是总分，而是名次最为接近，所以，排名的正确性构成了后序操作的重要基础！

aoe1981

　　为了对比测试择班处理后对成绩均衡性、性别均衡性的影响，可以在分班完成后，复制粘贴以下信息：
　　
　　然后在处理完择班学生，重新统计分班信息后，将二者进行一个对比……
　　可以计算一下各班平均分的标准差（我在2010中用的是stdev.p()函数），看看有何变化……

aoe1981

　　本帖标题中的“超级强大”，有点过于自大自恋，呵呵，为何这样？
　　1.吸引眼球；
　　2.从个人写代码的小小“历史”来看，这些代码确实是个人经历的很复杂的事了，请教了论坛中很多高手的……感谢他们的无私、热心帮助！我一开始不敢想像自己能够完成；
　　3.毕竟该附件没有充分经历实践中真实数据的考验，各模块之间在运行上是否有何逻辑上的冲突，检测还不足够！最重要的是，各模块代码相当部分在功能上还是多有重复的，没有能够采用更高级的编程技术予以模块化、简化……这些都是可能的问题……
　　大家就姑且谅解吧，容许我在不伤害公序良俗的基础上小小的“吹”一下吧……