excel按照学校统计sheeet1总分分数段人数的vba代码
时间: 2024-05-27 10:13:50 浏览: 30
以下是按照学校统计sheet1总分分数段人数的VBA代码:
Sub Count_Score_Range()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Score_Range As Variant
Dim School As Variant
Dim Total_Score As Integer
Dim Score_Range_Count As Integer
Score_Range = Array("0-59", "60-69", "70-79", "80-89", "90-100")
School = Array("School A", "School B", "School C")
For i = 0 To UBound(School)
For j = 0 To UBound(Score_Range)
Score_Range_Count = 0
For k = 2 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & k).Value = School(i) Then
Total_Score = 0
For l = 2 To 6
Total_Score = Total_Score + Range(Col_Letter(l) & k).Value
Next l
If Total_Score >= Split(Score_Range(j), "-")(0) And Total_Score <= Split(Score_Range(j), "-")(1) Then
Score_Range_Count = Score_Range_Count + 1
End If
End If
Next k
Range(Col_Letter(j + 8) & (i + 2)).Value = Score_Range_Count
Next j
Next i
End Sub
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
该代码首先定义了两个数组:Score_Range和School,分别存储分数段和学校名称。然后使用三重循环遍历每个学校、每个分数段和每个学生的总分,计算出每个分数段中符合条件的学生人数,并将结果写入到对应的单元格中。Col_Letter函数用于将列号转换成字母。