如何用Office365 Excel数组公式计算累计总量与加权平均价格

非数组公式实现 #

无需数组公式时，可使用以下公式（下拉复制到每行即可）：
=(SUM(H3)*SUM(I3)+B4*C4)/(SUM(H3)+B4)

其中SUM()函数的作用是，在第4行（首行数据）引用第3行（表头）时返回0，避免出现引用错误。核心逻辑为：

• 累计总量 = 前一日累计总量 + 当日销量
• 加权平均价格 = (前一日累计总量×前一日加权均价 + 当日销量×当日单价) / 累计总量

原数组公式的问题 #

你尝试的数组公式因直接引用公式所在区域的前一行结果，导致循环依赖无法生效：

=LET(day, A4:A12, amt, B4:B12, price, C4:C12, prevTotalAmt, OFFSET(H4:H12,-1,), prevAvgPrice,OFFSET(I4:I12,-1,),
    newTotalAmt,    IF(day = 1, 0, prevTotalAmt) + amt,
    newTotalPrice,   (IF(day = 1, 0, prevTotalAmt * prevAvgPrice) + amt * price) / newTotalAmt,
HSTACK(newTotalAmt, newTotalPrice) )

正确的数组公式解法 #

使用SCAN函数可以处理这种递推式的累计计算，它能在数组中逐行传递前一次的计算结果，避免循环引用。公式如下：

=LET(
    amt, B4:B12,
    price, C4:C12,
    // 计算每日累计总量：从0开始逐行累加销量
    totalAmt, SCAN(0, amt, LAMBDA(acc, curr, acc + curr)),
    // 计算每日累计总金额：从0开始逐行累加当日销售额
    totalValue, SCAN(0, amt*price, LAMBDA(acc, curr, acc + curr)),
    // 计算加权平均价格
    avgPrice, totalValue/totalAmt,
    // 合并结果为两列输出
    HSTACK(totalAmt, avgPrice)
)

公式说明 #

1. 累计总量：通过SCAN从初始值0开始，逐行累加当日销量，得到每行对应的累计总量
2. 累计总金额：同样用SCAN累加每日的销售额（销量×单价），得到累计总金额
3. 加权均价：直接用累计总金额除以累计总量，得到当日的加权平均价格
4. 结果输出：用HSTACK将累计总量和加权均价合并成两列，一次性填充所有行数据

示例数据 #

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