|
楼主 |
发表于 2019-4-19 20:10
|
显示全部楼层
Sheets.Add
ActiveSheet.Name = s
Sheets(s).Activate
Cells.clear
If Sheets(r).[g2] = "中国式排名" Then fs = 1 Else fs = 2
arr = Sheets(r).[a1].CurrentRegion
For i = 4 To UBound(arr)
d(arr(i, 3)) = d(arr(i, 3)) & i & ","
Next
k = d.keys: t = d.items
brr = Sheets(r).[a4].Resize(UBound(arr) - 3, UBound(arr, 2))
With Sheets("基本信息设置")
[a1] = .Range("d2") & .Range("d3") & .Range("d4") & .Range("d5") & .Range("d6") & "成绩统计表"
End With
For j = 1 To 4
Cells(2, j) = arr(3, j) '标题前四列
Cells(3, j).Resize(UBound(brr), 1) = Application.Index(brr, 0, j) '前四列基本信息
Next
For j = 5 To UBound(arr, 2)
Cells(2, 3 * j - 10) = arr(3, j) '科目标题
Call bp(j, fs)
Cells(2, 3 * j - 8) = arr(3, j) & vbLf & "班排" '班排标题
Cells(3, 3 * j - 10).Resize(UBound(brr), 1) = Application.Index(brr, 0, j) '原成绩数据
Call np(j, fs)
Cells(2, 3 * j - 9) = arr(3, j) & vbLf & "年排" '年排标题
Cells(3, 3 * j - 9).Resize(UBound(brr), 1) = [bc1].Resize(UBound(brr), 1).Value '年排数据
Next
[ba:bc].clear
Myc = [iv2].End(xlToLeft).Column
Cells(2, Myc + 1) = "进退" & vbLf & "情况"
'计算进退情况
Dim carr
If Len(ss) <> 0 Then
With Sheets(ss)
ssrr = .Range("a1").CurrentRegion
End With
With Sheets(s)
srr = .Range("a1").CurrentRegion
ReDim carr(1 To UBound(srr) - 2, 1 To 1)
For m = 1 To UBound(srr) - 2
For n = 3 To UBound(ssrr)
If .Cells(m + 2, 1) = Sheets(ss).Cells(n, 1) Then
carr(m, 1) = Sheets(ss).Cells(n, UBound(ssrr, 2) - 2) - .Cells(m + 2, UBound(srr, 2) - 2)
Exit For
Else
carr(m, 1) = "-"
End If
Next n
Next m
.Cells(3, UBound(srr, 2)).Resize(UBound(carr), 1) = carr
End With
Else
With Sheets(s)
srr = .Range("a1").CurrentRegion
ReDim carr(1 To UBound(srr) - 2, 1 To 1)
.Cells(3, UBound(srr, 2)).Resize(UBound(carr), 1) = "-"
End With
End If
With [a1].Resize(1, Myc + 1)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 18
.Font.Bold = True
End With
[a1].CurrentRegion.Borders.LineStyle = 1
'对成绩进行排序
mrr = Sheets(s).[a1].CurrentRegion
Set Rng = Range("a2:az" & UBound(mrr))
'rng.Sort Key1:="考号", Order1:=xlAscending, Header:=xlYes '以准考证号进行升序排序
Rng.Sort Key1:="总分", Order1:=xlDescending, Header:=xlYes '以总分进行降序排序
Set d = Nothing
MsgBox "本次成绩统计已完成!" & Chr(13) & Chr(13) & "总运行时间为:" & Timer - tim & " 秒!"
End Sub
Sub np(j, fs)
'年排
Dim i&, crr, n%
[ba:bc].clear
[ba1] = 1: [ba2] = 2: [ba1:ba2].AutoFill [ba1].Resize(UBound(brr), 1)
[bb1].Resize(UBound(brr), 1) = Application.Index(brr, 0, j)
[ba1].Resize(UBound(brr), 2).Sort [bb1], 2, Header:=xlNo
crr = [ba1].Resize(UBound(brr), 2)
[bc1] = 1: n = 1
For i = 2 To UBound(crr)
If crr(i, 2) < crr(i - 1, 2) Then
n = n + 1
If fs = 2 Then
Cells(i, "bc") = i
Else
Cells(i, "bc") = n
End If
Else
Cells(i, "bc") = Cells(i - 1, "bc").Value
End If
Next
[ba1].Resize(UBound(brr), 3).Sort [ba1], 1, Header:=xlNo
End Sub
Sub bp(j, fs)
'班排
Dim i&, crr, aa, y&, ii&, tt, n% 'y=56 ii=57
For i = 0 To UBound(k) 'UBound(k)=11
[ba:bc].clear
tt = t(i)
tt = Left(tt, Len(tt) - 1)
If InStr(tt, ",") Then
aa = Split(tt, ",")
For y = 0 To UBound(aa)
Cells(y + 1, "ba") = aa(y)
Cells(y + 1, "bb") = arr(aa(y), j)
Next
[ba1].Resize(UBound(aa) + 1, 2).Sort [bb1], 2, Header:=xlNo
crr = [ba1].Resize(UBound(aa) + 1, 2)
[bc1] = 1: n = 1
For ii = 2 To UBound(crr)
If crr(ii, 2) < crr(ii - 1, 2) Then
n = n + 1
If fs = 2 Then
Cells(ii, "bc") = ii
Else
Cells(ii, "bc") = n
End If
Else
Cells(ii, "bc") = Cells(ii - 1, "bc").Value
End If
Next
[ba1].Resize(UBound(aa) + 1, 3).Sort [ba1], 1, Header:=xlNo
For y = 0 To UBound(aa)
Cells(aa(y) - 1, 3 * j - 8) = Cells(y + 1, "bc").Value
Next
Else
Cells(1, "ba") = 1
Cells(1, "bb") = brr(tt, j)
Cells(1, "bc") = 1
Cells(tt, 3 * j - 8) = 1
End If
Next
End Sub
这是之前蓝版主也帮我做过一次,可是那个只是一个学校的,只有班级和年级的排名,这次这个加入各学校,我拿蓝版主的来研究半天,看不懂后面的班排和年排的代码啊。
|
|