|
分班的主要步骤如下:
( 1) 按照专业名称,把生源Excel 表划分成各个专业表,统计各专业学生数,并将每个专业学生信息表保存为一个工作簿文件。
( 2) 把各专业学生信息表按照性别划分成男、女生表。
( 3) 将每张男、女生表按照生源地和成绩降序排列。由于全国各地高考总分不同,按照生源地和成绩降序2 个关键字进行排序,使同省份学生成绩由高到低排列,保证每个平行班成绩均衡,学生的学习能力基本一致。
( 4) 对排好序的学生表增加序号,并以Z 形方式根据序号给每位学生分配班级信息。第一轮按照1 班到n 班顺序将学生分配到各班,第二轮从n 班到1 班顺序将学生分配到各班。
( 5) 将男、女生表按照班级信息,划分到各个班级,检查是否存在同名学生,按照姓名升序排序,并统计各班级学生数,以" 专业名称+ n 班+ 人数" 命名每张班级表。
专业分组代码- For rowData = 2 To shtData.Range( "A1" ) .CurrentRegion.Rows.Count
- sDept = shtData.Cells( rowData,"G" ) .Value '设置分组依据
- bln = False
- For Each shtNew In Worksheets
- If shtNew.Name = sDept Then
- bln = True
- Exit For
- End If
- Next
- If bln = False Then
- Set shtNew = Worksheets.Add( after: =Worksheets( Worksheets.Count) )
- shtNew.Name = sDept
- shtData.Range( "A1: J1" ) .Copy shtNew.Range( "A1" )
- End If
- rowNew = shtNew.Range( "A1" ) .CurrentRegion.Rows.Count + 1
- Intersect( shtData.Rows( rowData) ,shtData.Range( "A: J" ) ) .Copy shtNew.Cells( rowNew,1)
- Next rowData
复制代码
排序与添加序号代码- fPath = wbkThis.Path & " \分4 班专业表" '设置路径
- fName = Dir( fPath & " * .xlsx" )
- Do While fName <> " "
- Set wbkOpen = Workbooks.Open( fPath & fName)
- Set shtData = wbkOpen.Worksheets( 2)
- shtData.Range( "K1" ) .Value = " sxh" ‘添加顺序号
- shtData.Range( " L1" ) .Value = " bj" ‘添加班级
- shtData. Range ( " A1" ) . Sort Key1: = shtData. Range ( " J1" ) ,Order1: = xlAscending,Key2: =
- shtData.Range( " I1" ) ,Type: = xlSortValues,Order2: = xlDescending,Header: = xlYes
- For rowData = 2 To shtData.Range( "A1" ) .CurrentRegion.Rows.Count
- shtData.Cells( rowData,"K" ) .Value = rowData - 1
- Next
- Loop
复制代码
分班级号- For rowData = 2 To shtData.Range( "A1" ) .CurrentRegion.Rows.Count
- sxh = shtData.Cells( rowData,"K" ) .Value
- If ( ( sxh - 1) \ 4) Mod 2 = 0 Then‘\是整除
- Select Case sxh Mod 4
- Case 1
- shtData.Cells( rowData," L" ) .Value = 1
- Case 2
- shtData.Cells( rowData," L" ) .Value = 2
- Case 3
- shtData.Cells( rowData," L" ) .Value = 3
- Case 0
- shtData.Cells( rowData," L" ) .Value = 4
- End Select
- Else
- Select Case sxh Mod 4
- Case 1
- shtData.Cells( rowData," L" ) .Value = 4
- Case 2
- shtData.Cells( rowData," L" ) .Value = 3
- Case 3
- shtData.Cells( rowData," L" ) .Value = 2
- Case 0
- shtData.Cells( rowData," L" ) .Value = 1
- End Select
- End If
- Next
复制代码
同名检查代码- For rowData = 2 To zhs - 1
- sKey = shtData.Cells( rowData," B" ) .Value
- Set Rng = Range ( Cells ( rowData + 1," B" ) ,Cells ( zhs," B" ) ) . Find ( sKey,LookAt: =
- xlWhole)
- If Rng Is Nothing Then
- Else
- msgResult = MsgBox( shtData.Name & " 的B" & rowData & " 单元格数据重复" ,vbYes,
- " 同名同姓警告" )
- End If
- Next
复制代码
摘自文献:运用Excel VBA 实现高校新生分班 |
|