Excel:跟踪某一时刻的员工数量

Question

我正在尝试制作一份Excel表格，其中我有一组30分钟的全天间隔（从10:00开始，到20:00结束），我想计算每个间隔期间有多少员工可用。

我掌握的数据是他们的轮班开始/结束时间和午休时间的开始-结束时间（通常为1小时，但对于兼职人员来说，这是最后30分钟）。

以下是一个示例文件：http://www.mediafire.com/download/op3rarsiqj1sm77/time-tracker.xlsx

你有什么建议吗？

谢谢！

Answer 1

您应该能够使用逻辑AND、NOT和IF公式来实现这一点，对每个员工进行测试，每次"IF"大于（或等于）轮班开始时的"AND"IF"小于（或等于"午休时的轮班停止时的"one_answers"NOT"（使用类似的逻辑）。假设到达或离开的员工当时仍然在场，您将在样本表的单元格G2中输入的公式可以读取

=AND(
     AND(IF(G$1>=$B2,TRUE(),FALSE()),
         IF(G$1<=$C2,TRUE(),FALSE())),
     NOT(AND(IF(G$1>$D2,TRUE(),FALSE()),
             IF(G$1<$E2,TRUE(),FALSE()))))

复制&将其粘贴到G2:AA4的其他单元格中（针对每个员工和时间），您可以看到每个员工何时在场（"TRUE"）或不在场（"FALSE"）。然后，您可以简单地添加每列的值，例如，对于g列，您可以在单元格G5:中写入

=SUM(G$2:G4)

（同样，复制并粘贴到其他列以获得其他时间的结果。）

编辑：正如Dirk在下面指出的，有更简洁的方法可以将其作为公式来编写。在这里使用较长版本的优点是可以进行更多的自定义。然而，出于这个目的，我们需要将上面的公式"由内而外"。因此，例如，如果你想用文本而不是TRUE/FALSE来登记员工缺勤，例如，"present"（如果在场）和"（如果缺席），你必须使用if作为外部函数，并在if条件中使用and和NOT，如下所示：

=IF(
     AND(AND(G$1>=$B2,G$1<=$C2),
         NOT(AND(G$1>$D2,G$1<$E2))),
    "present","")

你可以使用之类的东西进行最后的计数

=COUNTIF(G$2:G4,"present")

Answer 2

您可以使用COUNTIFS公式来实现这一点。

只需将其放入细胞G2:

=COUNTIFS($B$2:$B$4,"<="&G1,$C$2:$C$4,">"&G1)-COUNTIFS($D$2:$D$4,"<="&G1,$E$2:$E$4,">"&G1)

并填充到AA2。