获取分数最高前五名学生的VBA代码如何实现按分数降序输出

修改后可按分数从高到低输出前5名的VBA代码 #

Sub Test_GetTopFive()
    GetTopFive Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
End Sub

Sub GetTopFive(r As Range)
    Dim v, t, i As Long, j As Long, temp, res, cnt As Long
    ' 获取第5名的分数阈值
    t = Application.WorksheetFunction.Aggregate(14, 6, r.Columns(2), 5)
    v = r
    ' 统计符合条件的记录数
    cnt = 0
    For i = 1 To UBound(v, 1)
        If Not IsError(v(i, 1)) And v(i, 2) >= t Then
            cnt = cnt + 1
        End If
    Next
    ' 初始化存储结果的数组
    ReDim res(1 To cnt, 1 To 2)
    cnt = 0
    For i = 1 To UBound(v, 1)
        If Not IsError(v(i, 1)) And v(i, 2) >= t Then
            cnt = cnt + 1
            res(cnt, 1) = v(i, 1)
            res(cnt, 2) = v(i, 2)
        End If
    Next
    ' 冒泡排序：按分数降序排列
    For i = 1 To UBound(res, 1) - 1
        For j = i + 1 To UBound(res, 1)
            If res(i, 2) < res(j, 2) Then
                ' 交换姓名
                temp = res(i, 1)
                res(i, 1) = res(j, 1)
                res(j, 1) = temp
                ' 交换分数
                temp = res(i, 2)
                res(i, 2) = res(j, 2)
                res(j, 2) = temp
            End If
        Next
    Next
    ' 输出排序后的结果
    For i = 1 To UBound(res, 1)
        Debug.Print res(i, 1), res(i, 2)
    Next
End Sub

核心修改说明 #

• 新增临时数组存储所有符合前5分数阈值的学生信息，避免直接按原表格顺序输出
• 加入冒泡排序逻辑，对筛选出的记录按分数列做降序排列，保证分数最高的学生优先输出
• 排序完成后统一批量输出到立即窗口

内容的提问来源于stack exchange，提问作者YasserKhalil