Excel统计员工排班期内休假天数的公式及工具求助

问题说明 #

• 核心需求：统计员工在另一工作表的排班区间（如C1:D1、C2:D2）内的休假天数（K1:N1区间），排班包含所有日期（周末、节假日均计入），不能使用NETWORKDAY函数
• 匹配规则：需按班组和月份匹配，预期结果为1月11天、2月21天
• 已尝试无效方法：Countifs、Sumproduct函数，以及公式=IF(AND($G2=B:B,R$1=F:F),MAX(0,MIN(D:D,N:N)-MAX(C:C,K:K)+1))

以下提供Power Query和VBA脚本两种可行解决方案：

一、Power Query Editor 实现步骤 #

1. 导入数据：将排班表、休假表分别导入Power Query（点击「数据」选项卡→「自表格/区域」）
2. 拆分跨月区间：
   • 对排班表：添加自定义列，判断排班区间是否跨月，若跨月则拆分为对应月份的子区间（比如1月28日-2月5日拆成1月28-31日、2月1-5日），提取每个子区间的月份
   • 对休假表：同理拆分休假区间，提取对应月份
3. 匹配合并：
   • 按「班组」+「月份」为关键字合并两张表，筛选出排班子区间与休假子区间有重叠的记录
4. 计算重叠天数：
   添加自定义列计算重叠天数：

   Number.Max({0, Number.Min([排班结束日期], [休假结束日期]) - Number.Max([排班开始日期], [休假开始日期]) + 1})
   

5. 分组求和：按「班组」+「月份」分组，对重叠天数求和，即可得到各班组对应月份的休假总天数

二、VBA脚本自定义函数 #

直接在Excel中插入模块，粘贴以下代码，即可通过自定义函数快速计算：

Function CalcLeaveDays(targetTeam As String, targetMonth As Integer, scheduleSheetName As String, leaveSheetName As String) As Integer
    Dim wsSch As Worksheet, wsLeave As Worksheet
    Dim schRow As Long, leaveRow As Long
    Dim schStart As Date, schEnd As Date
    Dim leaveStart As Date, leaveEnd As Date
    Dim overlapStart As Date, overlapEnd As Date
    Dim total As Integer
    
    Set wsSch = ThisWorkbook.Worksheets(scheduleSheetName)
    Set wsLeave = ThisWorkbook.Worksheets(leaveSheetName)
    total = 0
    
    '遍历排班表中目标班组的所有记录
    For schRow = 2 To wsSch.Cells(wsSch.Rows.Count, "B").End(xlUp).Row
        If wsSch.Cells(schRow, "B").Value = targetTeam Then
            schStart = wsSch.Cells(schRow, "C").Value
            schEnd = wsSch.Cells(schRow, "D").Value
            
            '判断排班区间是否覆盖目标月份
            If Date.Month(schStart) <= targetMonth And Date.Month(schEnd) >= targetMonth Then
                '遍历所有休假记录
                For leaveRow = 2 To wsLeave.Cells(wsLeave.Rows.Count, "K").End(xlUp).Row
                    leaveStart = wsLeave.Cells(leaveRow, "K").Value
                    leaveEnd = wsLeave.Cells(leaveRow, "N").Value
                    
                    '计算重叠区间的首尾日期
                    overlapStart = IIf(schStart > leaveStart, schStart, leaveStart)
                    overlapEnd = IIf(schEnd < leaveEnd, schEnd, leaveEnd)
                    
                    '截取目标月份内的重叠部分
                    overlapStart = IIf(Date.Month(overlapStart) < targetMonth, DateSerial(Date.Year(overlapStart), targetMonth, 1), overlapStart)
                    overlapEnd = IIf(Date.Month(overlapEnd) > targetMonth, DateSerial(Date.Year(overlapEnd), targetMonth, Date.DaysInMonth(Date.Year(overlapEnd), targetMonth)), overlapEnd)
                    
                    '累加有效天数
                    If overlapStart <= overlapEnd Then
                        total = total + (overlapEnd - overlapStart + 1)
                    End If
                Next leaveRow
            End If
        End If
    Next schRow
    
    CalcLeaveDays = total
End Function

使用方法 #

在结果单元格中输入：

=CalcLeaveDays(G2, MONTH(R1), "排班表", "休假表")

替换参数中的单元格引用、工作表名称即可。

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