Excel:跟踪给定时刻的员工数量 [英] Excel: track number of employees at a given moment

问题描述

我正在尝试制作一张Excel工作表，其中我每天有30分钟的时间间隔(从10:00开始至20:00结束)，我想计算出有多少员工可用在每个过程中.

I am trying to come up with an Excel sheet where I have a set of 30-minute intervals throughout the day (starting at 10:00 and ending at 20:00) and I would like to calculate how many employees are available during each of them.

我得到的数据是他们的上班时间开始/结束以及午餐时间的开始-结束时间(通常为1小时，但对于兼职人员则为最近30分钟).

The data I have is their shift start/end and the start-end times of their lunch breaks (normally 1 hour, but for part-timers these last 30 minutes).

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

您有什么建议吗?

谢谢！

推荐答案

您应该能够使用逻辑AND，NOT和IF公式执行此操作，为每个员工进行测试，并且每次"IF"都更大时(等于或等于换档开始时间"AND""IF"(小于或等于)在午休时段内(等于)等于换档停止位置"AND""NOT"(使用类似的逻辑).假设当时仍然有正在到达或离开的员工，那么您在样本表的单元格G2中输入的公式将显示为

You should be able to do this with the logical AND, NOT, and IF formulas, testing for each employee and each time "IF" this time is larger (or equal) than the shift start "AND" "IF" it is smaller (or equal) than the shift stop, "AND" "NOT" within the lunch break (using a similar logic). Assuming that an employee that is arriving or leaving is still present at that time, the formula you would enter in cell G2 of the sample sheet could read

=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中编写:

Copy&paste this to the other cells from G2:AA4 (for each employee and time), and you see when each employee is present ("TRUE") or not ("FALSE"). Then, you can simply add the values for each column, e.g., for column G, you could write in cell G5:

=SUM(G$2:G4)

(同样，将其复制并粘贴到其他列中以获取其他时间的结果.)

(Again, copy&paste to the other columns for getting the results for the other times.)

编辑:正如Dirk在下面指出的那样，有更多简洁的方法可以将其写为公式.在这里使用更长的版本的好处是可以进行更多的自定义.但是，为此，我们需要将公式放在由内而外"的上方.因此，例如，如果您想用文本而不是TRUE/FALSE来注册员工的缺勤，例如，如果存在，则使用"present"，如果不存在，则使用"，则必须使用IF作为外部函数，并且使用AND和NOT在IF条件中，如下所示:

As Dirk pointed out below, there are more concise ways of writing this as a formula. The advantage of using the lengthier version here would be to allow more customization. However, for this purpose, we'd need to put the formula above "inside out". So if, e.g., you'd want to register the employee absences with texts rather than TRUE/FALSE, e.g., "present" if present and "" if absent, you'd have to use IF as the outside function and AND and NOT inside the IF conditions, like this:

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

您可以使用类似的方法进行最终计数

The you can do the final counting by using something like

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

这篇关于Excel:跟踪给定时刻的员工数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！