如何在Google Sheets中用ArrayFormula引用列内前一行并解决循环依赖

问题原因 #

你当前的公式转换为ArrayFormula时触发循环依赖，核心原因是公式引用了当前计算列（G列）的上一行结果，而ArrayFormula会一次性计算整列数据，这种逐行依赖的逻辑会被判定为循环引用。

解决方案：使用SCAN函数替代 #

Google Sheets的SCAN函数专门处理需要基于前一行结果计算当前值的场景，不会触发循环依赖。完全匹配你原公式逻辑的替代公式如下：

=SCAN(,SEQUENCE(ROWS(G:G)),LAMBDA(acc,row,
  IF(row<5,"", // 前4行留空，对应原公式从第5行开始的逻辑，可按需调整行号
    IFERROR(
      IF(REGEXMATCH(INDEX(C:C,row),"DAY"),
        INDEX(E:E,row),
        acc + INDEX(H:H,row+1)
      ),
      acc + INDEX(H:H,row+1)
    )
  )
))

公式说明 #

• SCAN的第一个参数留空，代表从第一个有效行开始累计计算
• SEQUENCE(ROWS(G:G))生成整列的行号序列，实现逐行遍历
• LAMBDA(acc,row)的两个参数：
  • acc：代表上一行G列的计算结果
  • row：代表当前处理的行号
• 逻辑完全对齐原公式：
  1. 若当前行C列匹配"DAY"，则G列对应行取E列的值
  2. 否则用上一行G列的值（acc）加上下一行H列的值
  3. IFERROR处理异常情况，兜底执行累加逻辑

调整提示 #

• 若原公式中的H6是笔误（应为当前行H列，即H5），只需把INDEX(H:H,row+1)改为INDEX(H:H,row)
• 若数据起始行不是第5行，修改row<5中的数字即可

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