Excel/Power BI计算教师去重日期区间实际工作日（按教师/月分组）

完全可以实现，以下是两种工具的具体操作方案：

Excel 实现方案 #

1. 合并教师的重叠/重复日期区间 #

手动公式处理重叠区间效率极低，推荐用Power Query完成区间合并：

• 选中数据区域，点击「数据」→「从表格/区域」导入Power Query编辑器
• 按「教师」列排序，点击「转换」→「分组依据」，分组列选「教师」，新列名设为「日期区间」，操作选「所有行」
• 添加自定义列，用以下M函数合并该教师的所有重叠区间：

  MergeOverlappingIntervals = (intervals as list) as list =>
  let
      Sorted = List.Sort(intervals, (a,b) => Value.Compare(a[起始日期], b[起始日期])),
      Merged = List.Accumulate(Sorted, {}, (state, current) =>
          if List.IsEmpty(state) then
              {current}
          else
              let
                  Last = List.Last(state),
                  NewStart = Last[起始日期],
                  NewEnd = List.Max({Last[结束日期], current[结束日期]})
              in
                  if current[起始日期] <= Last[结束日期] then
                      List.RemoveLastN(state, 1) & {{起始日期=NewStart, 结束日期=NewEnd}}
                  else
                      state & {current}
      )
  in
      Merged
  

• 展开合并后的「日期区间」列，得到每个教师的无重叠日期区间

2. 计算实际工作日 #

对合并后的区间，用NETWORKDAYS.INTL函数计算工作日：

• 新增「实际工作日」列，公式：

  =NETWORKDAYS.INTL([@起始日期], [@结束日期], 1, 节假日区域)
  

  其中1代表周末为周六周日，可按需调整；节假日区域为可选参数，用于排除法定假日

3. 按教师+月份维度拆分计算 #

若需拆分到月份，可在Power Query中新增步骤，将跨月区间拆分为单月区间：

• 对合并后的区间添加自定义列，生成该区间覆盖的所有月份的起止日期，展开后用NETWORKDAYS.INTL计算每月工作日，最后按「教师+月份」汇总

Power BI 实现方案 #

Power BI更适合多维度分析，核心是通过日期表实现区间拆分与去重：

1. 建立基础日期表 #

新建计算表生成覆盖所有日期范围的日历：

日期表 = CALENDAR(MIN('教师日程表'[起始日期]), MAX('教师日程表'[结束日期]))

• 给日期表添加辅助列：

  是否工作日 = IF(WEEKDAY('日期表'[日期], 2) < 6, TRUE(), FALSE())
  月份 = FORMAT('日期表'[日期], "YYYY-MM")
  

2. 生成教师的无重叠区间计算表 #

新建计算表合并每个教师的重叠区间：

教师无重叠区间 = 
VAR AllIntervals = SELECTCOLUMNS('教师日程表', "教师", [教师], "起始日期", [起始日期], "结束日期", [结束日期])
VAR GroupedByTeacher = GROUPBY(AllIntervals, [教师], "区间列表", CONCATENATEX(CURRENTGROUP(), [起始日期] & "|" & [结束日期], ","))
VAR MergedIntervals = ADDCOLUMNS(GroupedByTeacher, "合并区间", 
    VAR IntervalList = SPLIT([区间列表], ",")
    VAR ParsedList = ADDCOLUMNS(IntervalList, "Start", DATEVALUE(LEFT([Value], FIND("|", [Value])-1)), "End", DATEVALUE(RIGHT([Value], LEN([Value])-FIND("|", [Value]))))
    VAR SortedList = SORT(ParsedList, [Start], ASC)
    VAR Merged = 
        ADDCOLUMNS(
            GENERATE(
                SortedList,
                VAR CurrentStart = [Start]
                VAR CurrentEnd = [End]
                VAR NextIntervals = FILTER(SortedList, [Start] <= CurrentEnd && [End] >= CurrentStart)
                RETURN ROW("合并起始", MINX(NextIntervals, [Start]), "合并结束", MAXX(NextIntervals, [End]))
            ),
            "教师", [教师]
        )
    RETURN DISTINCT(Merged)
)
SELECTCOLUMNS(MergedIntervals, "教师", [教师], "合并起始", [合并起始], "合并结束", [合并结束])

3. 创建度量值计算实际工作日 #

• 按教师维度的度量值：

  教师总实际工作日 = 
  CALCULATE(
      COUNTROWS('日期表'),
      '日期表'[是否工作日] = TRUE(),
      EXISTS(
          '教师无重叠区间',
          '教师无重叠区间'[教师] = SELECTEDVALUE('教师日程表'[教师]) &&
          '日期表'[日期] >= '教师无重叠区间'[合并起始] &&
          '日期表'[日期] <= '教师无重叠区间'[合并结束]
      )
  )
  

• 按教师+月份维度的度量值：

  教师月度实际工作日 = 
  CALCULATE(
      COUNTROWS('日期表'),
      '日期表'[是否工作日] = TRUE(),
      EXISTS(
          '教师无重叠区间',
          '教师无重叠区间'[教师] = SELECTEDVALUE('教师日程表'[教师]) &&
          '日期表'[日期] >= '教师无重叠区间'[合并起始] &&
          '日期表'[日期] <= '教师无重叠区间'[合并结束]
      ),
      ALLEXCEPT('日期表', '日期表'[月份], '教师日程表'[教师])
  )
  

内容的提问来源于stack exchange，提问作者xavier garcia pous