解决ARRAYFORMULA()循环依赖错误：实现日期自动填充逻辑

场景与需求 #

• 表格包含A、B两列：A列手动输入-或留空，B列为日期列
• 填充规则：
  • 当A列某行输入-时，该行B列日期 = 上一行B列日期 + 1天
  • 当A列留空时，该行B列日期与上一行B列日期一致
• 当前手动方案：在B5单元格使用公式 =IF(A5="-",OFFSET(B5,-1,0)+1,OFFSET(B5,-1,0))，拖拽可实现需求，但需手动下拉，希望改为自动填充的数组公式。

问题原因 #

你尝试的两种写法出现「Circular Dependency（循环依赖）」错误，核心原因是：

• OFFSET(B5:B,-1,0) 或 OFFSET(r,-1,1) 直接引用了B列自身正在计算的区域，公式本身就在B列执行，导致系统判定循环引用（比如计算B6时需要B5的值，但B5是当前公式正在生成的）。
• 不需要用FLATTEN解决，改用累积计算的思路即可。

解决方案 #

使用SCAN函数实现自动填充，无需手动下拉，且不会产生循环依赖。假设B4是B列的初始日期（即B5的上一行基准日期），将以下公式放在B5单元格：

=ARRAYFORMULA(SCAN(B4,A5:A,LAMBDA(prev,a,IF(a="-",prev+1,prev))))

公式说明 #

• SCAN函数会从初始值B4开始，逐行遍历A5:A的内容
• LAMBDA(prev,a,...)中：
  • prev代表上一行计算出的日期值
  • a代表当前行A列的内容
  • 逻辑判断：如果当前A列是-，则返回prev+1（日期加1天），否则返回prev（和上一行日期一致）

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