Excel按年月分组计算多日期区间的唯一工作日数

嗨，我来帮你搞定这个Excel里的问题！看起来你需要把同一年月分组下的所有子任务日期区间合并，去掉重叠的日期，然后统计其中的唯一工作日数量对吧？比如你示例里Jun-24的结果是10，就是把那5个区间的工作日去重后算出来的，我给你两种可行的方法，你可以根据自己的Excel版本和数据量来选：

方法一：用Excel动态数组函数（适合365/2021版本） #

如果你的Excel支持动态数组和LET、GROUPBY这些函数，直接用公式就能搞定，不需要手动操作。这里分两步：

1. 先提取唯一的年月分组 #

在空白列（比如F列）输入公式，得到所有不重复的年月：

=UNIQUE(B2:B9)

2. 计算每个年月的唯一工作日数 #

在F列旁边的G列，输入这个分组计算的公式（假设F2是第一个唯一年月）：

=GROUPBY(B2:B9,B2:B9,LAMBDA(yrMon,LET(
    // 把年月转成当月第一天的日期
    monthStart, DATEVALUE(yrMon&"-01"),
    // 得到当月最后一天
    monthEnd, EOMONTH(monthStart,0),
    // 生成当月所有日期的序列
    allMonthDates, SEQUENCE(monthEnd - monthStart + 1, monthStart),
    // 提取当前年月下的所有日期区间，展开成单个日期的文本列表
    intervalDates, TEXTSPLIT(TEXTJOIN(",",TRUE,TEXT(ROW(INDIRECT("'"&yrMon&"'!A"&DAY(FILTER(C2:C9,B2:B9=yrMon))&":A"&DAY(FILTER(D2:D9,B2:B9=yrMon)))),"m/d/yyyy")),","),
    // 判断当月日期是否在任意一个区间内
    inInterval, ISNUMBER(MATCH(allMonthDates, DATEVALUE(intervalDates), 0)),
    // 判断是否是工作日（周一到周五）
    isWorkday, WEEKDAY(allMonthDates,2) < 6,
    // 筛选出同时满足在区间内且是工作日的日期
    validDates, FILTER(allMonthDates, inInterval * isWorkday),
    // 去重后统计数量
    COUNT(UNIQUE(validDates))
)),0)

输入后会自动生成每个年月的结果，和你示例里Jun-24的10完全匹配~

方法二：用Power Query（适合所有版本，大量数据更友好） #

如果你的Excel版本比较旧，或者数据量很大，用Power Query可视化操作更简单，还不容易出错：

1. 导入数据到Power Query：选中你的数据区域，点击「数据」选项卡 -> 「从表格/区域」（如果弹出对话框，勾选「我的表格有标题」）
2. 转换日期格式：选中「Start Date」和「End Date」列，右键 -> 「更改类型」 -> 「日期」，确保这两列是日期格式
3. 展开每个区间的所有日期：添加自定义列，公式写{[Start Date]..[End Date]}，然后点击列标题旁的箭头，选择「展开到新行」
4. 筛选工作日：添加列 -> 「自定义列」，公式写Date.DayOfWeek([Date], Day.Monday) < 5（这个公式会把周一到周五标记为true，周末为false），然后筛选这个列只保留true的行
5. 分组统计唯一工作日：点击「转换」选项卡 -> 「分组依据」，分组依据选「Yr Mon」，新列名填「Distinct Countable Days In Month」，操作选「计数（唯一值）」，列选「Date」
6. 加载回Excel：点击「主页」选项卡 -> 「关闭并上载」，就能得到每个年月的唯一工作日数了

这个方法的好处是，以后数据更新了，只需要右键表格 -> 「刷新」就能自动更新结果，非常省心~

备注：内容来源于stack exchange，提问作者Niky Rathod