VBA使自定义函数的命名范围参数同时引用单个单元格和一系列单元格

Question

percentrank函数可以为它的两个参数使用相同的命名范围，例如：

=percentrank(inputrange,inputrange)

它将第一个参数视为计算截止点百分比的单元格范围，并使用第二个参数返回单个单元格的值，以在截止点中排名。单个单元格由进入/调用函数的行确定。

我想重新创建此功能，并使用第二个range引用来查找单个单元格值(基于输入函数的行)。这就是我所拥有的：

Public Function QUARTILE_RANK(DataRange As Range, RefCell As Range)

If RefCell <> vbNullString Then

    q1 = Application.WorksheetFunction.Quartile(DataRange, 1)
    q2 = Application.WorksheetFunction.Quartile(DataRange, 2)
    q3 = Application.WorksheetFunction.Quartile(DataRange, 3)
    q4 = Application.WorksheetFunction.Quartile(DataRange, 4)

    If (RefCell <= q1) Then QUARTILE_RANK = 1
    If (RefCell > q1) And (RefCell <= q2) Then QUARTILE_RANK = 2
    If (RefCell > q2) And (RefCell <= q3) Then QUARTILE_RANK = 3
    If (RefCell > q3) Then QUARTILE_RANK = 4
Else
    QUARTILE_RANK = vbNullString
End If

End Function

如果我为两个参数传递相同的命名范围，它会将这两个范围视为单元格数组。我希望它以这种方式处理第一个参数，但使用第二个参数找到一个单元格的值。也就是说，我希望它能镜像PERCENTRANK函数的功能。

编辑：

Sean Chesire的输入帮助我让它工作起来。我很欢迎任何改进的建议，因为我相信其他人也写过同样的函数。

下面是最终的形式：

Public Function QUARTILE_RANK(DataRange As Range, RefRange As Range)

Dim refCell As Range

If RefRange.Rows.Count > 1 Then
    Set refCell = RefRange.Cells(Application.Caller.Row, 1)
Else
    Set refCell = RefRange ' maybe they only passed a cell reference
End If

If refCell <> vbNullString Then

    q1 = Application.WorksheetFunction.Quartile(DataRange, 1)
    q2 = Application.WorksheetFunction.Quartile(DataRange, 2)
    q3 = Application.WorksheetFunction.Quartile(DataRange, 3)
    q4 = Application.WorksheetFunction.Quartile(DataRange, 4)

    If (refCell <= q1) Then QUARTILE_RANK = 1
    If (refCell > q1) And (refCell <= q2) Then QUARTILE_RANK = 2
    If (refCell > q2) And (refCell <= q3) Then QUARTILE_RANK = 3
    If (refCell > q3) Then QUARTILE_RANK = 4
Else
    QUARTILE_RANK = vbNullString
End If

End Function

Answer 1

这将告诉您公式是从哪个单元格调用的：

Function testit() As String
testit = Application.Caller.Parent.Parent.Name & " " & Application.Caller.Parent.Name & " " & Application.Caller.Address
End Function

然后，您应该能够测试给定的范围，并确定应该在另一个数组中引用哪个值

Answer 2

=LOOKUP(PERCENTRANK(vals,vals),{0,0.25,0.5,0.75},{1,2,3,4})