|
要求(vba实现):从“总成绩”表中提取各科(包括“总分”)全级前20名学生成绩信息(包括考号、姓名、班级、班级名次、年级名次)
- Sub 取前20名()
- Dim xRng As Range
- Set d = CreateObject("scripting.dictionary")
- km = Sheet2.[a3] '科目
- Set xRng = Sheet1.[a1:m1].Find(km) '找到科目在总成绩表中的对应列
- If Not xRng Is Nothing Then
- c = xRng.Column
- Else
- MsgBox "无对应科目,请重新选择"
- Exit Sub
- End If
- With Sheet1
- maxr = .[a65536].End(3).Row '总成绩表最大行
- arr = .Range("a1:m" & maxr) '总成绩表读入数组
- ReDim brr(1 To maxr, 1 To 2) 'brr为级次、班次表
- Set xRng = .Range(.Cells(2, c), .Cells(maxr, c)) '科目列所有成绩列(用于计算级次)
- For i = 2 To maxr '字典d保存科目列对应各班的所有成绩(用于计算班次)
- bj = arr(i, 3)
- If Not d.exists(bj) Then Set d(bj) = .Cells(i, c) Else Set d(bj) = Union(d(bj), .Cells(i, c))
- Next
- For i = 2 To maxr
- bj = arr(i, 3): cj = arr(i, c)
- brr(i, 1) = Application.WorksheetFunction.Rank(cj, xRng) '级次
- brr(i, 2) = Application.WorksheetFunction.Rank(cj, d(bj)) '班次
- Next
- .Range("n1:o" & maxr) = brr '第14列级次,第15列班次
- .Range("a2:o" & maxr).Sort key1:=.Cells(2, 14) '按级次排序
- crr = .Range("a1:o" & maxr) '排序后读入数组crr
- ReDim drr(2 To 21, 1 To 6) 'drr为最终显示结果的数组
- For i = 2 To 21
- drr(i, 1) = crr(i, 1): drr(i, 2) = crr(i, 2): drr(i, 3) = crr(i, 3) '考号、姓名、班级
- drr(i, 4) = crr(i, c): drr(i, 5) = crr(i, 15): drr(i, 6) = crr(i, 14) '成绩、班次、级次
- Next
- Sheet2.[c3:h22] = drr
- .UsedRange.ClearContents
- .Range("a1:m" & maxr) = arr '总成绩表恢复原序
- End With
- End Sub
- Private Sub Worksheet_Change(ByVal Target As Range)
- With Target
- If .Row < 3 Or .Row > 22 Or .Column > 1 Then Exit Sub
- End With
- Call 取前20名
- End Sub
复制代码
|
-
-
各科全级前20名.rar
36.11 KB, 下载次数: 53
要求(vba实现):从“总成绩”表中提取各科(包括“总分”)全级前20名学生成绩信息(包括考号、姓名、班级 ...
|