怎么计算班级总分前90%同学的总分平均分

faslow · 发表于 2025-2-2 21:17

刚学的vba, 写的不好勿怪；

Sub test()
Dim classRng As Range, firstRng As Range, secondRng As Range, avgRng As Range
Dim newSheet As Worksheet
Dim classArr()
Dim class As String, avg90 As Long
'获取所有班级号,并存到数列classArr中
Set ws = ThisWorkbook.Sheets(1)
Set dict = CreateObject("scripting.dictionary")
Set classRng = ws.Range("A2", Cells(Rows.Count, "A").End(xlUp))
For Each cell In classRng
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, ""
End If
Next cell
classArr = dict.keys
dict.RemoveAll
'根据班级号循环求总分均值
For i = 0 To UBound(classArr)
class = classArr(i) '设置班级号
If ws.AutoFilterMode Then ws.AutoFilterMode = False '取消筛选模式
'筛选班级
Set firstRng = Range("A1").CurrentRegion
firstRng.AutoFilter field:=1, Criteria1:=class
'筛选班级后的数据复制新表，作为筛选总分前90%的数据源
On Error Resume Next
Set firstRng = firstRng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'创建新表
Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count))
'将第一次筛选的数据复制到新表
firstRng.Copy newSheet.Range("A1")
'二次筛选，筛选总分前90%的数据
Set secondRng = newSheet.Range("A1").CurrentRegion
secondRng.AutoFilter field:=3, Criteria1:=90, Operator:=xlTop10Percent
'获取筛选后的总分列
On Error Resume Next
Set avgRng = secondRng.SpecialCells(xlCellTypeVisible).Columns(3)
On Error GoTo 0
'计算均值，并存储到字典中
avgRng.Copy newSheet.Range("N1")
avg90 = Application.WorksheetFunction.Average(avgRng)
dict.Add class, avg90
Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True
Next i
If ws.AutoFilterMode Then ws.AutoFilterMode = False
Range("O1:X1") = dict.keys
Range("O2:X2") = dict.items
End Sub

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 怎么计算班级总分前90%同学的总分平均分