Excel行列SUMIFS溢出公式：按任务相关月份平摊工时

• 现有表格以任务/子任务为行，包含「开始日期」「结束日期」「月份数」「各人员工时」「总工时」列，子任务工时会自动汇总到对应主任务
• 需要筛选出子任务，将每个子任务的总工时按直线平均法（总工时÷月份数），仅平摊到该任务的执行月份区间内，无关月份留空
• 当前遇到的问题：用SUMIFS计算出的每月平均工时会填充到所有表头月份，无法仅在任务对应月份显示；尝试过IF日期判断、用LET定义区间后乘0/1的方法，均未得到正确结果

• 子任务筛选公式：=FILTER(A5:A16,MOD(A5:A16,1)<>0)
• 日期表头生成公式：=DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1)

使用以下溢出公式可实现子任务工时的精准平摊：

=LET(
    tasks, FILTER(A5:A16,MOD(A5:A16,1)<>0),
    starts, FILTER(B5:B16,MOD(A5:A16,1)<>0),
    ends, FILTER(C5:C16,MOD(A5:A16,1)<>0),
    totals, FILTER(J5:J16,MOD(A5:A16,1)<>0),
    months, FILTER(D5:D16,MOD(A5:A16,1)<>0),
    headers, DATE(YEAR(MIN(B5:B16)),SEQUENCE(1,DATEDIF(MIN(B5:B16),MAX(C5:C16),"M")+1,MONTH(MIN(B5:B16)),1),1),
    BYROW(SEQUENCE(ROWS(tasks)), LAMBDA(r,
        LET(
            start, INDEX(starts,r),
            end, INDEX(ends,r),
            total, INDEX(totals,r),
            month_cnt, INDEX(months,r),
            avg_hour, total/month_cnt,
            BYCOL(headers, LAMBDA(h,
                IF(AND(h>=start, h<=EOMONTH(end,0)), avg_hour, "")
            ))
        ))
    )
)

公式说明 #

1. 用LET一次性定义所有所需变量：筛选后的子任务列表、对应起止日期、总工时、月份数，以及自动生成的日期表头
2. BYROW逐个遍历子任务，对每个任务单独计算平摊值
3. 先算出当前任务的每月平均工时：平均工时 = 总工时 ÷ 月份数
4. BYCOL逐个检查表头日期，判断该日期是否落在任务的执行区间内（用EOMONTH(end,0)确保覆盖结束月份的整月），符合条件则显示平均工时，否则留空
5. 公式会自动溢出生成完整的平摊表格，无需手动拖拽填充

内容的提问来源于stack exchange，提问作者Mark S.