谷歌表格中基于姓名、日期和状态自动追踪连续记录Streak的公式实现需求

嗨，这需求我太熟了！要在谷歌表格里实现按人、日期和状态自动追踪连续完成的Streak列，我给你一个能直接用的公式，顺便拆解一下逻辑：

先明确核心规则（对应你的需求和示例） #

• 按姓名独立追踪，不同用户的streak互不干扰
• 日期判断记录连续性：只有当前记录的日期是该用户上一条完成记录日期的次日，才算连续
• 状态触发规则：
  • 状态为「完成类」（你示例里的Approved，需求里的Complete）：如果是连续日期，streak在上一次基础上+1；如果是新的开始（无连续前序记录），streak设为1
  • 状态为「失败类」（你示例里的Rejected，需求里的Failed）：直接重置streak为0

自动计算的公式（可直接粘贴到Streak列的第一个单元格，比如D2） #

=ARRAYFORMULA(IF(A2:A="","",
  BYROW(A2:C, LAMBDA(row,
    LET(
      curr_date, INDEX(row, 1),
      curr_name, INDEX(row, 2),
      curr_status, INDEX(row, 3),
      -- 筛选当前用户所有早于当前日期的记录
      prev_records, FILTER(A2:C, B2:B = curr_name, A2:A < curr_date),
      -- 获取当前用户上一条有效完成记录的日期
      last_comp_date, IFERROR(MAX(FILTER(prev_records, INDEX(prev_records, , 3) = "Approved")), ""),
      -- 获取上一条有效完成记录对应的streak值
      last_streak, IF(last_comp_date = "", 0, LOOKUP(2, 1/(A2:A = last_comp_date) * (B2:B = curr_name) * (C2:C = "Approved"), D2:D)),
      
      -- 按状态和连续性计算当前streak
      IF(curr_status = "Rejected", 0,
        IF(last_comp_date = "" || curr_date - last_comp_date > 1, 1, last_streak + 1)
      )
    )
  ))
)

关键调整说明 #

1. 状态文本适配：如果你的状态列是需求里的Complete/Failed，只需要把公式里所有的"Approved"替换成"Complete"，"Rejected"替换成"Failed"即可
2. 空值处理：公式会自动跳过空行，避免出现错误值
3. 无序数据兼容：哪怕表格里的姓名和日期是乱序的，公式依然能准确追踪每个用户的独立streak

对应你的示例验证 #

• John 21/10/2024 Approved：无前置记录，streak=1
• Joe 21/10/2024 Approved：无前置记录，streak=1
• Jamie 22/10/2024 Rejected：状态触发重置，streak=0
• 如果Jamie后续有23/10/2024 Approved的记录，因为上一条是Rejected，所以streak=1；如果是22/10/2024 Approved（假设是另一条），无前置记录，streak=1

备注：内容来源于stack exchange，提问作者Rezio