求Google Sheets中满足多条件的D列自动计算公式（支持ARRAYFORMULA）

表格列规则 #

• A列：导入的TRUE/FALSE值列表
• B列：递增整数序列，在TRUE与FALSE切换时重置计数
• C列：包含与B列无关的整数、空白或特定字符串
• D列核心规则：
  • 当C列为空白或整数时，D列值与B列相等
  • 当C列为字符串时，保持最近的B列整数值，之后从该值继续递增，直到B列因A列切换而重置
• 附加约束：当A列为FALSE时，C列为空，且B列与D列值完全匹配

当前尝试与问题 #

已尝试暴力计算和XLOOKUP函数，但无法让D列在字符串段期间及之后准确计算。当前使用的公式：
D2=XLOOKUP(1000, $C$2:indirect("C"&row()), $B$2:indirect("B"&row()), "calculate normally", -1, -1)

希望将公式包裹在ARRAYFORMULA中实现批量自动填充。

解决方案 #

以下是可实现需求的ARRAYFORMULA公式：

=ARRAYFORMULA(
  IF(
    A2:A=FALSE, B2:B,
    IF(
      OR(C2:C="", ISNUMBER(C2:C)), B2:B,
      LET(
        prev_valid, XLOOKUP(ROW(C2:C), FILTER(ROW(C2:C), OR(C2:C="", ISNUMBER(C2:C))), B2:B, , 1),
        prev_row, XLOOKUP(ROW(C2:C), FILTER(ROW(C2:C), OR(C2:C="", ISNUMBER(C2:C))), ROW(C2:C), , 1),
        prev_valid + COUNTIF(INDIRECT("C"&prev_row&":C"&ROW(C2:C)), "<>") - 1
      )
    )
  )
)

公式逻辑解析 #

1. 基础判断：若A列为FALSE，直接返回B列值，满足附加约束
2. 常规情况：当C列为空白或整数时，D列同步B列值
3. 字符串段处理：
   • 用XLOOKUP找到当前行之前最近的、C列为空白/整数的行对应的B列值（prev_valid）和行号（prev_row）
   • 统计从prev_row到当前行的非空（字符串）行数，将其减1后加到prev_valid上，实现从最近有效B值开始的连续递增

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