如何将Office365 Excel标准公式转为数组公式？解决循环引用问题

问题描述 #

现有两个用于计算两组数据加权平均值的公式：一个是可正常运行的单单元格公式（无循环引用），另一个是存在循环引用错误的数组公式。计算逻辑要求当前行结果需引用上一行的计算结果（首行除外），需要将标准公式转换为可正常运行的数组公式，解决循环引用问题。

现有公式 #

标准单单元格公式（无循环引用，运行正常） #

=LET(a, A2, b, B2, c, C2, d, D1, e, E1,
    dd, IF(a = 1, 0, d),
    ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd)  )

存在循环引用错误的数组公式 #

=LET(a, A9:A12, b, B9:B12, c, C9:C12, d, D8:D11, e,E8:E11,
    dd, IF(a = 1, 0, d),
    ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd)  )

测试数据表格 #

修正后的数组公式（无循环引用） #

这类依赖上一行结果的递推计算，普通数组公式会因引用自身计算区域触发循环引用，使用SCAN函数可以实现逐行累积计算，完美解决这个问题：

=LET(
    data_range, A2:A5,
    col_B, B2:B5,
    col_C, C2:C5,
    col_D, D1:D4,
    SCAN(0, SEQUENCE(ROWS(data_range)), LAMBDA(prev_result, row_num,
        LET(
            seq_val, INDEX(data_range, row_num),
            b_val, INDEX(col_B, row_num),
            c_val, INDEX(col_C, row_num),
            d_val, INDEX(col_D, row_num),
            dd, IF(seq_val=1, 0, d_val),
            ee, IF(seq_val=1, 0, prev_result),
            (b_val * c_val + dd * ee) / (b_val + dd)
        )
    ))
)

公式说明 #

• SCAN函数按行遍历数据，prev_result参数存储上一行的计算结果，实现递推逻辑
• 首行处理：当seq_val=1时，dd和ee设为0，直接计算首行加权平均值
• 后续行：用上一行结果prev_result作为ee，结合当前行B、C列值和上一行D列值，完成加权平均计算

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