Option Explicit

Sub RankStudents()
    Dim a(1 To 100, 1 To 4) As Variant
    Dim i As Integer, j As Integer
    Dim totalScore As Integer, mainSubjectScore As Integer
    Dim currentRank As Integer, nextRank As Integer
    
    ' 填充示例数据，你可以根据实际情况替换这部分数据
    For i = 1 To 100
        a(i, 1) = "姓名" & i
        a(i, 2) = Int((250 - 150 + 1) * Rnd + 150) ' 随机生成总分（假设在150到250之间）
        a(i, 3) = Int((100 - 80 + 1) * Rnd + 80)   ' 随机生成主科成绩（假设在80到100之间）
    Next i
    
    ' 按总分和主科成绩降序排列
    For i = 1 To 99
        For j = i + 1 To 100
            If a(i, 2) < a(j, 2) Or (a(i, 2) = a(j, 2) And a(i, 3) < a(j, 3)) Then
                ' 交换数据
                SwapArrayElements a, i, j
            End If
        Next j
    Next i
    
    ' 初始化排名
    currentRank = 1
    nextRank = 1
    
    ' 分配排名
    For i = 1 To 100
        If i = 1 Then
            ' 第一个人直接排第1名
            a(i, 4) = currentRank
        Else
            If a(i, 2) = a(i - 1, 2) And a(i, 3) = a(i - 1, 3) Then
                ' 总分和主科成绩相同，名次并列
                a(i, 4) = currentRank
            Else
                ' 总分或主科成绩有变化，更新名次
                currentRank = nextRank
                a(i, 4) = currentRank
            End If
        End If
        ' 更新下一名的名次
        nextRank = currentRank + 1
    Next i
    
    ' 输出结果
    For i = 1 To 100
        Debug.Print a(i, 1), a(i, 2), a(i, 3), a(i, 4)
    Next i
End Sub

Sub SwapArrayElements(ByRef arr() As Variant, ByVal i As Integer, ByVal j As Integer)
    ' 交换数组中的两个元素
    Dim temp As Variant
    temp = arr(i)
    arr(i) = arr(j)
    arr(j) = temp
End Sub