如何强制Excel函数支持数组输入以实现SUMPRODUCT批量计算？

现有如下数据集：

已编写VBA自定义eval函数用于解析计算字符串公式：

Public Function eval(s As String) As Variant
    eval = Evaluate(s)
End Function

目标是将Val列的值代入Eq列的公式，计算结果与Weight列相乘后用SUMPRODUCT求和。尝试了以下公式：

=SUMPRODUCT(IF(ISNUMBER(H$7:H$31),Dist(H$7:H$31,$D$7:$D$31,$E$7:$E$31))

其中Dist是Lambda函数：

LAMBDA(val,eq,weight,eval(CONCAT("=",SUBSTITUTE(eq,"val",val))))

但表格行数超过1行时，会返回#N/A或#VALUE!错误，原因是LOGNORM.DIST这类函数不支持多值数组输入，必须逐值处理。

方法1：用BYROW逐行计算（无需修改VBA） #

核心是让不支持数组的函数逐个处理单行数据，用BYROW遍历每一行的Eq和Val，传入Lambda函数计算：

1. 重新定义Dist Lambda函数（聚焦单行的Eq和Val处理）：

   LAMBDA(row_data, eval(CONCAT("=",SUBSTITUTE(INDEX(row_data,1),"val",INDEX(row_data,2)))))
   

2. 结合BYROW和SUMPRODUCT的最终公式：

   =SUMPRODUCT(BYROW(CHOOSE({1,2},D7:D31,H7:H31), Dist)*E7:E31)
   

   BYROW会把每一行的Eq（D列）和Val（H列）打包成数组，传给Dist计算单个结果，最后和Weight列（E列）相乘后求和。

方法2：修改VBA函数支持数组输入 #

把原eval函数改成能批量处理数组的版本，自动逐元素计算：

Public Function eval(rng As Variant) As Variant
    Dim arr As Variant, i As Long, j As Long
    ' 转换输入为数组
    If TypeName(rng) = "Range" Then
        arr = rng.Value
    Else
        arr = rng
    End If
    ' 初始化输出数组
    ReDim output(1 To UBound(arr, 1), 1 To UBound(arr, 2)) As Variant
    
    ' 逐单元格计算
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            If VarType(arr(i, j)) = vbString Then
                output(i, j) = Evaluate("=" & arr(i, j))
            Else
                output(i, j) = arr(i, j)
            End If
        Next j
    Next i
    
    eval = output
End Function

然后简化Lambda函数只做字符串替换：

LAMBDA(val,eq, SUBSTITUTE(eq,"val",val))

最终SUMPRODUCT公式：

=SUMPRODUCT(eval(Dist(H7:H31,D7:D31))*E7:E31)

修改后的eval会自动遍历数组中的每个公式字符串，逐个计算结果，避免数组输入冲突。

方法3：利用动态数组隐式计算（Office 365+） #

直接生成替换后的公式数组，传入eval计算：

=SUMPRODUCT(eval("="&SUBSTITUTE(D7:D31,"val",H7:H31))*E7:E31)

Office 365及以后版本支持动态数组，会自动逐元素处理替换后的公式字符串；旧版本需按Ctrl+Shift+Enter作为数组公式输入。

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