excel - 如何仅使用 vba 代码对二维数组中的一列数字进行排名

Question

我有一个 2D 数字数组，5 列和 5 行。第 4 列包含第 1 到 3 列的计算结果，我希望第 5 列是第 4 列的 RANK。我只想在数组中执行此操作而不使用工作表。
请注意，我只是为了在工作时清晰工作而使用工作表。
我只想使用代码，因为它将处理大量计算，并且从工作表中写入/读取会太慢。

Sub RankArray()

Dim arr()
ReDim arr(1 To 5, 1 To 5)

For y = 1 To 5
    For x = 1 To 3
        arr(y, x) = Int((99 * Rnd) + 1)
        Sheet1.Cells(y, x) = arr(y, x)
    Next x
    arr(y, 4) = arr(y, 1) + arr(y, 2) + arr(y, 3)
    Sheet1.Cells(y, 4) = arr(y, 4)
Next y

For y = 1 To 5
    'arr(y, 5) = WorksheetFunction.Rank(arr(y, 4), Range("D1:D5"))    
    arr(y, 5) = WorksheetFunction.Rank(arr(y, 4), Range(arr(1, 4), arr(5, 4)))
    Sheet1.Cells(y, 5) = arr(y, 5)
Next y

End Sub

程序一直运行，直到它到达第二个循环中的“排名”行 - 然后给出: -

"Runtime error 1004

"Application-defined or object-defined error"

注释掉的行有效-但这使用了工作表中的数据，这不是我想要的。
那么问题是什么？为什么 Rank 在这种情况下不起作用？
我正在使用 Excel 2007。

Answer 1

Range 需要两个范围而不是数组中的项目。但 Rank 也不喜欢它想要范围引用的数组。

首先我们想要一个第 4 列的一维数组:

    Dim t As Variant
    t = Application.Transpose(Application.Index(arr, 0, 4))

这将从第 4 列创建一个一维数组

然后我们在 SUMPRODUCT 中使用它

arr(y, 5) = Application.Evaluate("SumProduct(--({" & Join(t, ",") & "} <= " & arr(y, 4) & "))")

我还将输出更改为仅在工作表中一次以节省一些时间。

Sub RankArray()

    Dim arr()
    ReDim arr(1 To 5, 1 To 5)

    Dim y As Long
    For y = 1 To 5
        Dim x As Long
        For x = 1 To 3
            arr(y, x) = Int((99 * Rnd) + 1)
        Next x
        arr(y, 4) = arr(y, 1) + arr(y, 2) + arr(y, 3)
    Next y

    Dim t As Variant
    t = Application.Transpose(Application.Index(arr, 0, 4))

    For y = 1 To 5
        arr(y, 5) = Application.Evaluate("SumProduct(--({" & Join(t, ",") & "} <= " & arr(y, 4) & "))")
    Next y

    Sheet1.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr

End Sub

一注:

如果数组的行数超过 45-50 行，这将不起作用 Evaluate有 255 个字符的限制。