如何用Office365 Excel数组公式计算指定人员5天加权平均价格（动态溢出）

需求 #

计算选定人员5天内每日的加权平均价格，要求使用可自动溢出至结果区域的动态数组公式，禁止使用VBA，仅用数组公式实现。

原始数据 #

尝试的公式（无法正常运行） #

=LET(data,A4:D12,dates,index(data,,1),names,INDEX(data,,2),amt,INDEX(data,,3),price,INDEX(data,,4),days,M4#,
    weightedPrice, amt*price,
    resp, MAP(days,LAMBDA(day,SUMIFS(weightedPrice,names,F4,dates,day)/SUMIFS(amt,names,F4,dates,day))),
resp  )

解决方案 #

原公式问题在于SUMIFS对动态数组迭代的兼容缺陷，以下是两种修正后的可行方案：

方案1：SUMPRODUCT适配动态数组 #

=LET(
    data,A4:D12,
    dates,INDEX(data,,1),
    names,INDEX(data,,2),
    amt,INDEX(data,,3),
    price,INDEX(data,,4),
    targetName,F4,
    targetDays,M4#,
    weightedPrice,amt*price,
    resp,MAP(targetDays,LAMBDA(d,
        SUMPRODUCT((names=targetName)*(dates=d)*weightedPrice)/SUMPRODUCT((names=targetName)*(dates=d)*amt)
    )),
    resp
)

方案2：矩阵乘法高效批量计算 #

=LET(
    data,A4:D12,
    dates,INDEX(data,,1),
    names,INDEX(data,,2),
    amt,INDEX(data,,3),
    price,INDEX(data,,4),
    targetName,F4,
    targetDays,M4#,
    nameMatch,names=targetName,
    dayMatch,TOROW(dates=targetDays),
    weightedTotal,MMULT(amt*price*nameMatch,dayMatch),
    totalAmt,MMULT(amt*nameMatch,dayMatch),
    IFERROR(weightedTotal/totalAmt,"")
)

公式说明 #

1. 方案1：用SUMPRODUCT替代SUMIFS，直接通过逻辑判断筛选目标数据，避免动态数组迭代中的兼容问题，逐个日期计算加权平均。
2. 方案2：利用MMULT矩阵乘法一次性批量计算所有目标日期的结果，数据量较大时效率更高；IFERROR处理无对应数据的日期，返回空值而非错误值。

注：F4为选定人员的单元格，M4#为包含5天日期的动态数组区域（如M4:M8）。

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