用VBA数组分别计算每班总分平均分，输出到F:I列
用VBA数组分别计算每班总分平均分，输出到F:I列Public Sub asd()
'i,k,m,
Dim i As Integer
Dim k As Integer
Dim m As Integer
Dim arr As Variant
Dim ar As Variant
With ThisWorkbook.Sheets("sheet1")
arr = .Range("A2:D" & .Cells(Rows.Count, 1).End(xlUp).Row)
End With
ReDim ar(1 To 4, 1 To 1)
For i = 1 To UBound(arr)
For k = 1 To UBound(ar, 2)
If i = 1 Then m = 1: ar(1, 1) = arr(1, 1)
If ar(1, k) = arr(i, 1) Then
ar(2, k) = ar(2, k) + 1
ar(3, k) = ar(3, k) + arr(i, 4)
ar(4, k) = ar(3, k) / ar(2, k)
Exit For
ElseIf k = UBound(ar, 2) Then
m = m + 1
ReDim Preserve ar(1 To 4, 1 To m)
ar(1, m) = arr(i, 1)
ar(2, m) = ar(2, m) + 1
ar(3, m) = ar(3, m) + arr(i, 4)
ar(4, m) = ar(3, m) / ar(2, m)
End If
Next
Next
.Resize(UBound(ar, 2), UBound(ar)) = WorksheetFunction.Transpose(ar)
Erase ar
Erase arr
i = 0
k = 0
m = 0
End Sub
页:
[1]