如何在Excel中计算项目员工的部分月份工作日薪资成本

以下是适配需求的Excel公式，可返回2020年1-12月的1×12薪资成本数组，核心逻辑是计算每个员工在目标月份内的实际工作日天数，再结合对应时段的日薪资核算总成本：

=LET(
    -- 项目分配表数据
    namePrj, TB_Prj[Employee],
    startPrj, TB_Prj[Start Date],
    endPrj, TB_Prj[End Date],
    -- 薪资表数据（优先使用日薪资字段）
    nameSal, TB_Roster[Employee],
    startSal, TB_Roster[Salary Start Date],
    endSal, TB_Roster[Salary End Date],
    dailySal, TB_Roster[Salary Daily],
    -- 目标月份的月初集合（H1:S1为2020年1-12月的月初日期，如1/1/2020、2/1/2020...）
    SOMs, H1:S1,
    -- 遍历每个月份计算成本
    BYCOL(SOMs, LAMBDA(SOM,
        LET(
            EOM, EOMONTH(SOM, 0),
            -- 筛选出该月内有项目参与的员工及其有效工作区间
            prjFilter, (startPrj <= EOM) * (endPrj >= SOM),
            activeNames, FILTER(namePrj, prjFilter),
            activeStart, FILTER(startPrj, prjFilter),
            activeEnd, FILTER(endPrj, prjFilter),
            -- 计算每个员工在该月的实际工作日天数
            workDays, NETWORKDAYS(MAX(activeStart, SOM), MIN(activeEnd, EOM)),
            -- 匹配每个员工对应时段的有效日薪资
            salFilter, (startSal <= EOM) * (IF(endSal = "", EOM, endSal) >= SOM),
            salMatch, XMATCH(activeNames, FILTER(nameSal, salFilter)),
            validDailySal, INDEX(FILTER(dailySal, salFilter), salMatch),
            -- 汇总该月所有员工的成本
            SUM(workDays * validDailySal)
        )
    ))
)

关键逻辑说明： #

1. 项目区间筛选：不再判断整月是否完全在项目期内，而是找出所有项目起止与目标月份有重叠的员工，计算其在该月的实际工作区间（MAX(项目开始日, 月初) 至 MIN(项目结束日, 月末)）
2. 工作日计算：用NETWORKDAYS统计上述区间内的周一至周五天数（若需考虑法定节假日，可添加第三个参数指定节假日范围）
3. 薪资匹配：筛选出员工在目标月份内的有效薪资记录（空结束日期视为覆盖至月末），通过XMATCH关联员工与对应日薪资
4. 成本核算：每个员工的成本为「实际工作日天数 × 对应日薪资」，最后汇总该月所有员工成本

注意事项： #

• 确保所有日期字段格式为Excel可识别的日期类型（MM/DD/YYYY）
• 薪资表的无重叠多段记录需保证时间线连续，避免出现薪资空档
• 若需使用月度薪资折算日薪，可将dailySal替换为TB_Roster[Salary Monthly]/NETWORKDAYS(SOM, EOM)，但优先使用薪资表自带的日薪资字段以保证准确性

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