Excel:定义一个 VBA 函数来缩短 PERCENTRANK

Question

在 Excel 中，公式通常变得很长，因此难以阅读。例如，我的公式如下所示:

=PERCENTRANK([vol];[@vol])-PERCENTRANK([pos];[@pos])

我只想写

=SCORE("vol", "pos")

如何从原始工作表公式创建用户定义函数？

Answer 1

您可以使用 VBA 的 WorksheetFunction 相对轻松地做到这一点。 .最困难的部分实际上是获取 [@Range] 的列所以你不必输入它——但这也不是太复杂。

在 VBE 中，创建一个新的标准模块并粘贴以下 UDF:

Public Function SCORE(vol As Range, pos As Range) As Double

    ' We need to get the entire column of the table for each argument
    Dim tbl As ListObject, volRng As Range, posRng As Range
    Set tbl = vol.ListObject

    Rem -> Table columns are relative to the table - not the worksheet
    Set volRng = tbl.Range.Columns(vol.Column - tbl.Range.Cells(1).Column + 1)
    Set posRng = tbl.Range.Columns(pos.Column - tbl.Range.Cells(1).Column + 1)

    With Application.WorksheetFunction
        SCORE = .PercentRank(volRng, vol) - .PercentRank(posRng, pos)
    End With

End Function

您必须记住，表格的列号并不总是与工作表的相同(如果您的表格不是从 A 列开始)。因此，您基本上必须通过从参数中减去列来抵消工作表列的数量 vol和 pos来自整个表格第一个单元格的列号 tbl.range.cells(1) .然后添加一个，因为 VBA 中的列号是“base 1”。

您仍然必须发送单个单元格的范围 - 所以在这里使用字符串参数不会很好。话虽如此，您的新工作表公式将如下所示:

=SCORE([@vol],[@pos])

或者

=SCORE([@vol];[@pos])

取决于您是否使用 , 分隔参数或 ; .