Excel跨天班次每小时在岗人数COUNTIFS统计异常排查

问题现状 #

• 统计目标：计算运营各时段每小时在岗员工人数，已知最早班次03:00开始，最晚班次次日06:00结束
• 现存问题：03:00-23:00时段统计结果准确，23:00至次日07:00时段结果存在偏差

现有使用公式 #

23:00-00:00时段公式 #

=COUNTIFS('May 2-May 8'!$D:$D, ">"&AC$30, 'May 2-May 8'!$C:$C,"<"&AD$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF") + COUNTIFS('May 2-May 8'!$D:$D, "<="&$I$30, 'May 2-May 8'!$C:$C,"<"&AC$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF")

00:00-07:00时段公式 #

=COUNTIFS('May 2-May 8'!$D:$D, "<="&$L$30, 'May 2-May 8'!$C:$C,">="&V$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF")

偏差原因 #

现有公式的时间判断逻辑没有覆盖跨天班次的全部场景：Excel中纯时间格式存储为0（对应00:00）到1（对应24:00）的小数，跨次日0点的班次下班时间数值小于上班时间数值，原有分段判断漏算了「上班时间在统计时段前、下班时间在统计时段后且跨天」的人员，部分场景还会出现重复计数。

通用修复方案 #

不需要分时段写不同判断公式，直接用统一逻辑判断统计时点是否在班次的上下班区间内即可，该公式对03:00到次日06:00全时段生效：

=COUNTIFS(
  'May 2-May 8'!$H:$H,"SKD",
  'May 2-May 8'!$I:$I,"CREW CHIEF",
  'May 2-May 8'!$C:$C,"<="&T,
  'May 2-May 8'!$D:$D,">="&T
)+COUNTIFS(
  'May 2-May 8'!$H:$H,"SKD",
  'May 2-May 8'!$I:$I,"CREW CHIEF",
  'May 2-May 8'!$C:$C,">"&'May 2-May 8'!$D:$D,
  'May 2-May 8'!$C:$C,"<="&T
)+COUNTIFS(
  'May 2-May 8'!$H:$H,"SKD",
  'May 2-May 8'!$I:$I,"CREW CHIEF",
  'May 2-May 8'!$C:$C,">"&'May 2-May 8'!$D:$D,
  'May 2-May 8'!$D:$D,">="&T
)

注：公式中T替换为对应统计时段的时点单元格引用即可，逻辑为同时统计三类在岗人员：1. 上下班时间不跨天、统计时点在班次区间内的人员；2. 班次跨天、统计时点在上班时间到24点区间内的人员；3. 班次跨天、统计时点在0点到下班时间区间内的人员

参考截图 #

内容的提问来源于stack exchange，提问作者John Nance