求Google Sheets每月重置的逆向递减剩余时长动态数组公式

以下是适配你需求的公式，可直接在目标列（比如示例表的剩余小时数列）的首行（如D2）输入：

=BYROW(A2:A, LAMBDA(date, IF(date="",, 
  LET(
    month_key, TEXT(date, "YYYY-MM"),
    total_deducted, SUMIFS(C2:C, TEXT(A2:A, "YYYY-MM"), month_key, ROW(A2:A), ">="&ROW(date)),
    MAX(0, 24 - total_deducted)
  )
)))

公式逻辑说明 #

• 按月重置计算：通过TEXT(date, "YYYY-MM")将日期转换为年月标识，确保每个月份的计算独立，初始时长始终为24小时。
• 动态适配数据范围：BYROW函数遍历整列数据，新增行时会自动触发计算，无需手动调整范围。
• 逆向扣除逻辑：利用ROW(A2:A) >= 当前行号的条件，累加当前行及下方（更旧记录）的所有同月份时长，再用24小时减去该总和，实现从最新记录往上的逆向扣除计算。
• 非负保障：MAX(0, ...)确保剩余时长不会出现负数，若当月总扣除时长超过24小时，剩余显示为0。

使用注意事项 #

1. 确保A列的日期为Google Sheets可识别的日期格式，否则年月分组会失效。
2. C列的已记录时长需为数值格式，保证SUMIFS能正确求和。
3. 空行自动返回空值，不影响整体计算。

内容的提问来源于stack exchange，提问作者Mikołaj Gano