如何在Google Sheets中用ArrayFormula高效实现带空行处理的动态加权移动平均

针对你需要实现随时间衰减权重的动态加权平均，同时解决空值处理和性能卡顿的问题，以下是两种优化方案：

方案一：用SCAN实现高效累积计算（推荐） #

此方案利用SCAN函数进行逐行累积计算，避免生成庞大的矩阵，性能最优，同时完美处理空值：

=ArrayFormula(
  LET(
    non_empty_vals, FILTER(A2:A, A2:A<>""),
    total_non_empty, COUNT(non_empty_vals),
    decay_weights, POW($D$1, total_non_empty - SEQUENCE(total_non_empty)),
    cum_weighted_sum, SCAN(0, non_empty_vals * decay_weights, LAMBDA(acc, val, acc + val)),
    cum_weight_total, SCAN(0, decay_weights, LAMBDA(acc, w, acc + w)),
    avg_results, cum_weighted_sum / cum_weight_total,
    // 将计算结果映射回原表格行
    IF(A2:A<>"", VLOOKUP(ROW(A2:A), HSTACK(ROW(non_empty_vals), avg_results), 2, FALSE), "")
  )
)

公式说明： #

• LET：定义变量简化公式，减少重复计算
• non_empty_vals：筛选A列非空的数值，仅处理有效数据
• decay_weights：按你的逻辑生成衰减权重（最新行权重为1，上一行是D1的1次方，以此类推）
• SCAN：分别累积计算加权值总和和权重总和，替代原MMULT的矩阵运算
• VLOOKUP：将计算好的加权平均结果对应回原表格的行，空值行自动留空

方案二：优化MMULT计算范围（兼容旧版Sheets） #

如果无法使用SCAN（旧版Google Sheets），可以缩小MMULT的计算范围到非空行，避免百万级矩阵：

=ArrayFormula(
  LET(
    valid_data, FILTER(A2:B, A2:A<>""),
    vals, INDEX(valid_data,,1),
    wts, INDEX(valid_data,,2),
    row_nums, ROW(vals),
    // 仅在非空行范围内生成布尔矩阵
    bool_matrix, TRANSPOSE(row_nums <= TRANSPOSE(row_nums)),
    cum_weighted_sum, MMULT(bool_matrix * vals, wts),
    cum_weight_total, MMULT(bool_matrix, wts),
    avg_results, cum_weighted_sum / cum_weight_total,
    IF(A2:A<>"", VLOOKUP(ROW(A2:A), HSTACK(row_nums, avg_results), 2, FALSE), "")
  )
)

优化点： #

• 用FILTER提取有效数据，矩阵大小从整列的百万级缩小到非空行数量级
• 空值行通过外层IF返回空，避免MMULT参数错误

权重列的自动扩展优化 #

你的权重列公式也可以优化为自动扩展，无需手动下拉：

=ArrayFormula(
  IF(A2:A="", "",
    POW($D$1, COUNT(FILTER(A2:A, A2:A<>"")) - (ROW(A2:A) - ROW(A2) + 1))
  )
)

此公式会自动根据A列非空行数量更新权重，新增行时自动计算对应权重。

核心解决的问题 #

1. 空值处理：通过FILTER筛选有效数据，仅在非空行执行计算，同时空值行返回空
2. 性能优化：要么用SCAN的线性累积替代MMULT的矩阵运算，要么缩小MMULT的计算范围，彻底解决整列引用导致的卡顿

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