Excel 2019特定时间段资源分配计算求助（无法使用XLOOKUP）

核心逻辑 #

通过日期交集计算+工作日统计的组合公式，实现Excel 2019兼容的月度资源占比计算，核心是用NETWORKDAYS、EOMONTH、MAX、MIN替代XLOOKUP完成日期范围匹配。

最终公式（以C2单元格为例） #

=IFERROR(NETWORKDAYS(MAX($A2,C$1), MIN($B2,EOMONTH(C$1,0)))/NETWORKDAYS(C$1,EOMONTH(C$1,0)), 0)

将公式输入C2后，横向拖动填充至所有月份列，纵向拖动填充至所有人员行即可。

公式拆解说明 #

1. 确定当月边界：EOMONTH(C$1,0) 返回C1单元格对应月份的最后一天（比如C1是07/01/24，返回07/31/24）
2. 锁定当月有效分配区间：
   • MAX($A2,C$1)：取人员分配开始日期与当月第一天的较大值，确保只计算当月内的起始点
   • MIN($B2,EOMONTH(C$1,0))：取人员分配结束日期与当月最后一天的较小值，确保只计算当月内的结束点
3. 统计有效工作日：NETWORKDAYS(...) 计算上述区间内的工作日数（默认排除周六周日）
4. 计算当月总工作日：NETWORKDAYS(C$1,EOMONTH(C$1,0)) 统计当月全部工作日数
5. 异常处理：IFERROR(...,0) 处理分配区间完全不在当月的情况，返回0避免错误值

示例验证（匹配你提供的测试数据） #

• 第一行（09/02/24至09/06/24）：
  9月有效工作日区间为09/02-09/06，共5天；9月总工作日21天，5/21≈0.24，与示例一致。
• 第二行（07/01/24至10/15/24）：
  10月有效工作日区间为10/01-10/15，共10天；10月总工作日21天，10/21≈0.48，与示例一致。
• 第三行（11/01/24至12/21/24）：
  12月有效工作日区间为12/01-12/21，共15天；12月总工作日22天，15/22≈0.68，与示例一致。

注意事项 #

• 确保所有日期单元格为日期格式，避免文本格式导致函数计算错误
• 如需排除自定义节假日，可在NETWORKDAYS函数中添加第三参数（例如NETWORKDAYS(..., ..., $F$1:$F$10)，其中$F$1:$F$10为节假日列表）
• 公式中的引用规则：$A2/$B2固定列（Start/End列），C$1固定行（月份表头行），确保拖动填充时引用正确

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