如何将数组公式结果传递给Excel自定义函数BasedLerp？

问题原因 #

当你在函数参数中直接使用H14:H36/I14:I36这类数组运算时，Excel返回的是二维Variant数组（而非Range对象），但原函数的数组处理逻辑只适配了一维数组和Range对象，无法正确识别和遍历二维数组，导致返回#VALUE!错误。

修改后的函数代码 #

Function BasedLerp(a As Single, b As Single, t As Variant) As Variant
    Dim r() As Single
    Dim i As Integer, j As Integer
    Dim tArr As Variant
    Dim rowCount As Long, colCount As Long
    
    ' 将输入的t统一转换为二维数组处理
    If TypeOf t Is Excel.Range Then
        tArr = t.Value
    ElseIf IsArray(t) Then
        tArr = t
    Else
        ' 单个值的情况
        aVal = (a - (b / 2)) * 2
        x = (aVal - b) / 2
        BasedLerp = (aVal + ((b - aVal) * t)) - x
        Exit Function
    End If
    
    ' 处理二维数组
    rowCount = UBound(tArr, 1)
    colCount = UBound(tArr, 2)
    ReDim r(1 To rowCount, 1 To colCount)
    
    aVal = (a - (b / 2)) * 2
    x = (aVal - b) / 2
    
    For i = 1 To rowCount
        For j = 1 To colCount
            ' 跳过错误值（比如除数为0的情况）
            If Not IsError(tArr(i, j)) Then
                r(i, j) = (aVal + ((b - aVal) * tArr(i, j))) - x
            Else
                r(i, j) = CVErr(xlErrValue)
            End If
        Next j
    Next i
    
    BasedLerp = r
End Function

关键修改点 #

• 统一数组处理逻辑：不管t是Range还是数组运算返回的二维数组，都先转换为Variant二维数组tArr，避免类型判断混乱。
• 支持二维数组遍历：使用双重循环处理行和列，适配Excel返回的二维数组结构。
• 错误值处理：增加对tArr中错误值的判断（比如除数为0时的#DIV/0!），返回对应的错误值而不是直接崩溃。

使用说明 #

现在可以直接用你期望的方式调用函数：

=BasedLerp([基准值];1;(H14:H36/I14:I36))

注意：如果是Excel 365或2021版本，直接输入公式后按回车即可；旧版本需要按Ctrl+Shift+Enter作为数组公式输入。

内容的提问来源于stack exchange，提问作者Jakub Dobi