Google Sheet客户流失判定：求E、F列公式实现客户状态与末次订阅日期

列F：末次订阅日期（从F2单元格开始） #

公式：

=MAXIFS($B:$B, $A:$A, $A2)

逻辑说明：通过MAXIFS函数匹配当前行的客户ID（A2），在所有订阅日期（B列）中筛选出该客户的最大日期，即为该客户的末次订阅日期。

列E：客户状态（流失/现有客户，从E2单元格开始） #

公式：

=LET(
  last_date, F2,
  last_sub_type, XLOOKUP(last_date, $B:$B, $C:$C, "", 0, 1),
  days_elapsed, TODAY() - last_date,
  IF(
    OR(
      AND(last_sub_type="monthly", days_elapsed>90),
      AND(last_sub_type="yearly", days_elapsed>455)
    ),
    "流失",
    "现有客户"
  )
)

逻辑说明：

1. 用LET函数定义变量简化公式，避免重复计算：
   • last_date：引用F列的末次订阅日期
   • last_sub_type：通过XLOOKUP匹配末次日期对应的订阅类型（月度/年度）
   • days_elapsed：计算当前日期与末次订阅日期的间隔天数
2. 流失判定：
   • 若末次订阅为月度，且间隔超过90天 → 判定为「流失」
   • 若末次订阅为年度，且间隔超过455天（365天+90天缓冲期） → 判定为「流失」
   • 其他情况均为「现有客户」

补充优化：如果需要处理无订阅记录的空白行，可在公式外层添加IFERROR：

=IFERROR(
  LET(
    last_date, F2,
    last_sub_type, XLOOKUP(last_date, $B:$B, $C:$C, "", 0, 1),
    days_elapsed, TODAY() - last_date,
    IF(
      OR(
        AND(last_sub_type="monthly", days_elapsed>90),
        AND(last_sub_type="yearly", days_elapsed>455)
      ),
      "流失",
      "现有客户"
    )
  ),
  "无订阅记录"
)

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