如何用单个ARRAYFORMULA计算支出回本平均天数

最终公式（Main工作表B2单元格） #

=ARRAYFORMULA(IF(A2:A="",,LET(
  users, A2:A,
  data_range, Data!B2:Z,
  header_row, Data!B1:Z1,
  // 获取每个用户首次正收入的日期行
  first_income_row, BYROW(users, LAMBDA(u, MATCH(TRUE, INDEX(data_range,, MATCH(u&"_INCOME", header_row, 0))>0, 0))),
  // 提取首次正收入后的支出数据
  filtered_spend, BYROW(users, LAMBDA(u, FILTER(INDEX(data_range,, MATCH(u&"_SPEND", header_row, 0)), Data!A2:A >= INDEX(Data!A2:A, first_income_row)))),
  // 计算累计收入（从首次正收入开始）
  cum_income, BYROW(users, LAMBDA(u, SCAN(0, INDEX(data_range,, MATCH(u&"_INCOME", header_row, 0)), LAMBDA(acc, val, acc+val)))),
  cum_income_filtered, BYROW(SEQUENCE(ROWS(users)), LAMBDA(i, INDEX(cum_income, i, SEQUENCE(ROWS(INDEX(cum_income,i))) >= INDEX(cum_income,i,first_income_row)))),
  // 计算累计支出
  cum_spend, BYROW(filtered_spend, LAMBDA(s, SCAN(0, s, LAMBDA(acc, val, acc+val)))),
  // 匹配每笔累计支出被累计收入覆盖的天数
  days_match, BYROW(SEQUENCE(ROWS(users)), LAMBDA(i, IFERROR(MAP(INDEX(cum_spend,i), LAMBDA(sp, XMATCH(TRUE, INDEX(cum_income_filtered,i)>=sp, 0)), "")))),
  // 过滤未被覆盖的支出，计算平均值
  avg_days, BYROW(days_match, LAMBDA(d, IFERROR(AVERAGE(FILTER(d, NOT(ISBLANK(d)) AND d<=ROWS(d))), 0))),
  avg_days
)))

公式核心逻辑 #

• 定位首次正收入：通过MATCH找到每个用户第一笔大于0的收入所在行，以此作为统计支出的起始点
• 过滤有效支出：只保留首次正收入日期之后的支出数据
• 累计计算：用SCAN生成累计收入和累计支出序列，方便判断覆盖时点
• 匹配覆盖天数：用XMATCH找到累计收入首次超过对应累计支出的天数
• 计算有效平均值：排除未被覆盖的支出（无匹配天数的项），对有效天数取平均

关键注意事项 #

• 需保证Data工作表的列头严格遵循[用户名]_SPEND、[用户名]_INCOME、[用户名]_MISC的命名规则
• 若用户无任何正收入，公式返回0，可将末尾的0改为""以返回空白
• 公式已通过ARRAYFORMULA实现整列自动计算，无需下拉填充

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