如何按轮班/非轮班类型计算多时段月度日历/工作日天数？

问题背景 #

• 员工分为两类：*轮班（Shift）*员工休假按日历天计算；*非轮班（No Shift）*员工按工作日计算
• 需统计最多4个休假时段的月度累计天数，4个时段对应单元格：X2-Y2、Z2-AA2、AB2-AC2、AD2-AE2
• 现有公式存在两个核心问题：嵌套层级过多导致冗长难维护；未加入员工类型判断逻辑
• 尝试用WORKDAY.INTL处理轮班员工时，误将日期序列号当作天数，该函数实际用于计算工作日后的日期，不适合直接统计日历天数

现有公式（供对比） #

=IF(ISBLANK(X2),0,IF(ISBLANK($AD2),IF(ISBLANK($AB2),
  IF(ISBLANK($Z2),(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$X2),
  MIN(EOMONTH(AF$1,0),$Y2),1,Holidays))),(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$X2),
  MIN(EOMONTH(AF$1,0),$Y2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$Z2),
  MIN(EOMONTH(AF$1,0),$AA2),1,Holidays)))),(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$X2),
  MIN(EOMONTH(AF$1,0),$Y2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$Z2),
  MIN(EOMONTH(AF$1,0),$AA2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$AB2),
  MIN(EOMONTH(AF$1,0),$AC2),1,Holidays)))),(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$X2),
  MIN(EOMONTH(AF$1,0),$Y2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$Z2),
  MIN(EOMONTH(AF$1,0),$AA2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$AB2),
  MIN(EOMONTH(AF$1,0),$AC2),1,Holidays)))+(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,$AD2),
  MIN(EOMONTH(AF$1,0),$AE2),1,Holidays)))))

优化后的公式 #

假设员工类型存储在单元格B2，月度起始日期在AF$1，优化公式如下：

=IF(B2="Shift",
 SUMPRODUCT(MAX(0,MIN(EOMONTH(AF$1,0),CHOOSE({1,2,3,4},Y2,AA2,AC2,AE2))-MAX(AF$1,CHOOSE({1,2,3,4},X2,Z2,AB2,AD2))+1)),
 SUMPRODUCT(MAX(0,NETWORKDAYS.INTL(MAX(AF$1,CHOOSE({1,2,3,4},X2,Z2,AB2,AD2)),MIN(EOMONTH(AF$1,0),CHOOSE({1,2,3,4},Y2,AA2,AC2,AE2)),1,Holidays)))
)

公式说明 #

1. 员工类型判断：通过IF(B2="Shift",...)区分两类员工的计算逻辑
2. 轮班员工日历天计算：
   • 用CHOOSE({1,2,3,4},...)一次性提取4个时段的起止日期
   • 计算每个时段与当月的交集：MIN(当月最后一天, 时段结束日) - MAX(当月第一天, 时段开始日) + 1
   • 用MAX(0,...)过滤无效时段（如未填写的时段会得到负数结果，自动转为0）
   • SUMPRODUCT自动求和4个时段的有效天数
3. 非轮班员工工作日计算：
   • 复用上述交集逻辑，替换为NETWORKDAYS.INTL计算工作日（自动排除周末和指定节假日Holidays）
   • 同样通过MAX(0,...)过滤无效时段，SUMPRODUCT求和
4. 空时段处理：无需嵌套多层ISBLANK判断，空单元格参与计算时会自动生成负数，被MAX(0,...)过滤，等同于忽略空时段

兼容旧版Excel的调整 #

若使用旧版Excel不支持数组常量{1,2,3,4}，可将CHOOSE替换为INDEX数组形式，例如：

CHOOSE(ROW($1:$4),X2,Z2,AB2,AD2)

需按Ctrl+Shift+Enter作为数组公式输入

内容的提问来源于stack exchange，提问作者zalp 2468