|
楼主 |
发表于 2017-9-26 14:33
|
显示全部楼层
帮忙看看这个程序运行缓慢,要等三、四分钟 的样子。- Sub grf() '短跑预赛分组
-
- Application.ScreenUpdating = False '加在程序开始
- pds = Sheet2.[m1] '跑道数
- With Sheet1
- arr = .Range("a1:f" & .[a65536].End(3).Row)
- org = arr
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
-
- For i = 2 To UBound(arr) '参加各项目的人数,行号
- d1(arr(i, 4)) = d1(arr(i, 4)) & "," & i
- Next
-
- For Each xm In d1.keys '对于每个项目
- xrr = Split(d1(xm), ",") '各项目的行号
- rs = UBound(xrr) '第1步:统计项目人数
- zs = Int((rs - 0.001) / pds) + 1 ' 第2步:确定组次
- For k = 1 To UBound(xrr) '第3步:对项目进行分组
- i = xrr(k)
- n = n + 1
- If n > zs Then n = 1
- arr(i, 5) = xm & n
- arr(i, 6) = Rnd '随机数辅助列,用于乱序
- Next
- Next
- .Range("a1:f" & .[a65536].End(3).Row) = arr
- .Range("a2:f" & .[a65536].End(3).Row).Sort key1:=.[e2], key2:=.[f2] '第4步:在各组内对运动员进行顺机排序
- arr = .Range("a1:f" & .[a65536].End(3).Row)
- .Range("a1:f" & .[a65536].End(3).Row) = org '恢复原序
- For i = 2 To UBound(arr)
- d2(arr(i, 5)) = d2(arr(i, 5)) & "," & i '每组人所在行
- Next
- End With
-
- With Sheet2 '第5步:确定道次
- .[a2].Resize(10000, 8).Clear
- For Each zu In d2.keys '对于每个组
- xrr = Split(d2(zu), ",")
- bzrs = UBound(xrr) '本组人数
- ReDim brr(1 To pds, 1 To 6)
- qs = Int((pds - bzrs + 1) / 2) + 1 '起始跑道
- n = 0
- For k = 1 To pds
- brr(k, 1) = Right(zu, 1)
- brr(k, 2) = k
- If k >= qs Then
- n = n + 1
- If n <= bzrs Then
- i = xrr(n)
- For j = 1 To 4: brr(k, j + 2) = arr(i, j): Next
- End If
- End If
- Next
- r = .[a65536].End(3).Row + 2
- .Cells(r, 1).Resize(pds, 6) = brr '显示本项目安排结果
- Next
- rr = .[a65536].End(3).Row + 2 '加空行
- For i = rr To 3 Step -1
- If .Cells(i, 1) <> .Cells(i - 1, 1) Then .Rows(i).Insert
-
- Next
- r = .[a65536].End(3).Row '加表头
- For i = 4 To r
- If .Cells(i, 2) = "1" Then .Cells(i - 1, 1) = "组次": .Cells(i - 1, 2) = "道次": .Cells(i - 1, 3) = "号码": .Cells(i - 1, 4) = "姓名": .Cells(i - 1, 5) = "单位": .Cells(i - 1, 6) = "项目": .Cells(i - 1, 7) = "成绩": .Cells(i - 1, 8) = "备注"
- Next
- Dim a '加标题
- a = Format(Date, "yyyy年") '当前年月日
- r = .[a65536].End(3).Row
- For i = 3 To r Step pds + 3
- If .Cells(i, 1) = "组次" Then .Cells(i - 1, 1) = "双河初中" & a & "田径运动会" & .Cells(i + 2, 6) & "检录表":
-
- Range(Cells(i - 1, 1), Cells(i - 1, 8)).Select '合并单元格并居中
-
- With Selection
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = False
- End With
- Selection.Merge
-
- Next
- End With
-
-
- Application.ScreenUpdating = True '打开屏幕刷新
-
- End Sub
复制代码 |
|