如何用ArrayFormula或Query优化需批量粘贴的Google Sheets公式

问题分析 #

原公式重复调用FILTER和嵌套ARRAYFORMULA，每行都重复执行筛选与权重计算，既需要手动批量粘贴，又因重复运算导致计算缓慢。核心逻辑是：当前行D列值 + 满足条件的历史记录加权平均，权重基于日期差的指数衰减（exp(-0.000792*日期差)），筛选条件为：

• B列值 = 当前行C列值
• C列值 ≠ 当前行B列值
• A列日期 < 当前行A列日期

优化方案：BYROW + ARRAYFORMULA 一次性计算整列 #

直接在目标列首行（比如E2）输入以下公式，自动填充整列，无需批量粘贴：

=ARRAYFORMULA(IF(ROW(A:A)=1, "结果", IF(A:A="", "", BYROW(A2:A, LAMBDA(current_date, 
  LET(
    current_row, ROW(current_date),
    current_c, INDEX(C:C, current_row),
    current_b, INDEX(B:B, current_row),
    current_d, INDEX(D:D, current_row),
    // 筛选符合条件的历史记录
    filtered_dates, FILTER(A:A, B:B=current_c, C:C<>current_b, A:A<current_date),
    filtered_d, FILTER(D:D, B:B=current_c, C:C<>current_b, A:A<current_date),
    // 计算权重：指数衰减
    weights, IFERROR(exp(-0.000792*(current_date - filtered_dates)), 0),
    total_weight, SUM(weights),
    // 计算加权平均并与当前D值相加
    IF(total_weight=0, current_d, current_d + SUMPRODUCT(weights, filtered_d)/total_weight)
  )
)))))

优化点说明 #

1. 整列自动计算：ARRAYFORMULA配合BYROW，遍历每行并自动生成结果，无需手动粘贴公式
2. 减少重复计算：用LET函数封装重复的筛选、权重计算逻辑，避免同一行内多次调用FILTER
3. 错误处理：IFERROR避免无符合条件记录时的计算错误，total_weight=0时直接返回当前D值
4. 性能提升：相比原公式每行重复执行多次筛选，此方案每行仅执行1次筛选与权重计算，大幅降低运算量

替代简化方案（适配旧版Google Sheets） #

若你的Google Sheets版本不支持BYROW/LAMBDA，可使用以下数组公式：

=ARRAYFORMULA(IF(A2:A="", "", D2:D + 
  MMULT(
    --(B2:B=TRANSPOSE(C2:C)) * --(C2:C<>TRANSPOSE(B2:B)) * --(A2:A>TRANSPOSE(A2:A)) * EXP(-0.000792*(A2:A-TRANSPOSE(A2:A))),
    D2:D
  ) / 
  MMULT(
    --(B2:B=TRANSPOSE(C2:C)) * --(C2:C<>TRANSPOSE(B2:B)) * --(A2:A>TRANSPOSE(A2:A)) * EXP(-0.000792*(A2:A-TRANSPOSE(A2:A))),
    SIGN(D2:D)
  )
))

注意：此方案通过矩阵乘法（MMULT）实现加权计算，数据量较大时可能仍有性能压力，但比原公式效率更高。

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