请教大家两个问题

第一个可以用COUNT→MAX=来实现
第二个每太看明白，是同一科目的排序还是最高分数的排序，同一科目的话比较好做，用RANK比较后IF判断={1,2,3,4,5}排序即可

Option Explicit
Sub abc()
　Dim a, i, j, p, max
　Cells.Interior.ColorIndex = xlNone
　a = [a1].CurrentRegion.Value
　For i = 2 To UBound(a)
　　max = a(i, 2)
　　For j = 3 To UBound(a, 2)
　　　If max < a(i, j) Then max = a(i, j)
　　Next
　　For j = 2 To UBound(a, 2)
　　　If a(i, j) = max Then Cells(i, j).Interior.Color = vbYellow
　　Next
　Next
　p = UBound(a)
　a = [a1].Offset(p + 2).CurrentRegion.Resize(, 4).Value
　For i = 2 To UBound(a)
　　a(i, 4) = -i
　Next
　Call bsort(a, 2, UBound(a), 1, UBound(a, 2), 3)
　Call rank(a, 2, UBound(a), 3, 1, True) '美式排名
　Call bsort(a, 2, UBound(a), 1, UBound(a, 2), 4)
　[a1].Offset(p + 1).Resize(UBound(a), 3) = a
End Sub
Function bsort(a, first, last, left, right, key)
　Dim i, j, k, t
　For i = first To last - 1
　　For j = first To last + first - 1 - i
　　　If a(j, key) < a(j + 1, key) Then
　　　　For k = left To right
　　　　　t = a(j, k): a(j, k) = a(j + 1, k): a(j + 1, k) = t
　　　　Next
　　　End If
　　Next
　Next
End Function
Function rank(a, first, last, key, col, order As Boolean)
　Dim i As Long, j As Long, m As Long
　m = 1: a(first, col) = 1
　For i = first + 1 To last
　　If order Then
　　　m = m + 1
　　Else
　　　If a(i, key) <> a(i - 1, key) Then m = m + 1
　　End If
　　If a(i, key) = a(i - 1, key) Then
　　　a(i, col) = a(i - 1, col)
　　Else
　　　a(i, col) = m
　　End If
　Next
End Function