Excel VBAѧϰ

Sub test()
    Dim arr, brr, i&, r&, k
    Set d = CreateObject("scripting.dictionary")
    Set d1 = CreateObject("scripting.dictionary")
    With Sheet1
        r = .Cells(Rows.Count, 1).End(3).Row
        For i = 2 To r
            d(.Cells(i, 1).Value * 1) = 0
        Next
        For i = 0 To d.Count - 1
            k = Application.Large(d.Keys, i + 1)
            d(k) = i + 1
            d1(i + 1) = k
        Next
        For i = 2 To r
            .Cells(i, "c") = d(Cells(i, 1) * 1)
        Next
        For i = 6 To 9
            .Cells(i, "E") = d1(.Cells(i, "D").Value * 1)
        Next
    End With
End Sub

Sub test()
    Dim ar, br, d, i%
    ar = Range("d5").CurrentRegion
    br = Range("a1").CurrentRegion
    Set d = CreateObject("scripting.dictionary")
    For i = 2 To UBound(br)
        d(br(i, 1)) = ""
    Next
    For i = 2 To UBound(ar)
        ar(i, 2) = Application.Large(d.Keys, ar(i, 1))
    Next
    Range("d5").Resize(UBound(ar), 2) = ar
End Sub