Excel公式优化：按日期拆分员工假期工时并填充工作表

前提假设（需匹配你的实际表结构调整） #

• Sheet3 列定义：A=员工ID，B=假期开始日期，C=假期结束日期，D=总假期工时
• Sheet1 结构：A列为员工ID，第1行（B1:X1）为考勤日期，目标计算区域为B2:Xn（对应每个员工的每日工时单元格）

核心公式（分版本） #

版本1：Excel 365/2021（支持LET、FILTER动态数组） #

在Sheet1的B2单元格输入以下公式，下拉+右拉填充：

=IF(OR(Sheet2!你的考勤判断条件, Sheet4!你的节假日判断条件), 原有考勤/节假日返回值,
    LET(
        empID, $A2,
        currDate, B$1,
        // 筛选当前员工的所有假期数据
        holidayData, FILTER(Sheet3!$B:$D, Sheet3!$A:$A=empID),
        startDates, INDEX(holidayData,,1),
        endDates, INDEX(holidayData,,2),
        totalHoursList, INDEX(holidayData,,3),
        // 计算单假期天数与每日拆分工时
        holidayDaysList, endDates - startDates + 1,
        dailyHoursList, totalHoursList/holidayDaysList,
        // 筛选当前日期对应的有效假期工时
        validHours, FILTER(dailyHoursList, (currDate>=startDates)*(currDate<=endDates)),
        // 输出带工时的VAC标识，无匹配则为空
        IF(COUNTA(validHours)>0, "VAC-"&TEXT(TEXTJOIN(", ",TRUE,validHours),"0.00"), "")
    )
)

版本2：旧版Excel（无动态数组支持） #

若不支持LET/FILTER，使用嵌套INDEX/MATCH实现单假期拆分（多假期需额外调整）：

=IF(OR(Sheet2!你的考勤判断条件, Sheet4!你的节假日判断条件), 原有考勤/节假日返回值,
    IFERROR(
        IF(AND(B$1>=INDEX(Sheet3!$B:$B,MATCH($A2,Sheet3!$A:$A,0)), B$1<=INDEX(Sheet3!$C:$C,MATCH($A2,Sheet3!$A:$A,0))),
            "VAC-"&TEXT(INDEX(Sheet3!$D:$D,MATCH($A2,Sheet3!$A:$A,0))/(INDEX(Sheet3!$C:$C,MATCH($A2,Sheet3!$A:$A,0))-INDEX(Sheet3!$B:$B,MATCH($A2,Sheet3!$A:$A,0))+1),"0.00"),
            ""
        ),
        ""
    )
)

关键说明 #

1. 保留原有逻辑：外层IF(OR(...), 原有返回值, ...)完全保留你已有的考勤、节假日判断逻辑，只需替换你的考勤判断条件和原有考勤/节假日返回值为实际内容。
2. 工时拆分逻辑：按假期总天数（含起止日期）平均拆分总工时，确保假期内每日单元格自动填充VAC-XX.XX格式的标识。
3. 多假期处理：365版本公式支持同一员工多条假期记录，若当日存在多个假期，会用逗号分隔显示多个工时；旧版Excel需额外添加TEXTJOIN配合SMALL实现多假期匹配。

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