如何为客户服务主管生成活动图？ [英] How can I generate activity map for customer care executives?
问题描述
我有客户服务主管的数据，该数据告诉他们他们一次又一次接听了多少个电话。我需要找出特定主管在特定时期是忙还是闲。办公室的时间是10:00到17:00，所以我在10:00到17:00的每一段中都切了一个小时。
I have data of customer care executives which tells about how many calls they have attend from one time another time continuously. I need to find out whether particular executive is either busy or free in a particular period. The timings for the office is 10:00 to 17:00, so I have sliced the time with one hour in each slice from 10:00 to 17:00.
我拥有的数据如下所示：
The data that I have would look like as:
注意：
-
此处给出的数据是原始数据的一部分，我们有20位高管，他们有5到10行数据。为简化起见，我们使用了3位主管，每位主管少于5行。
The data given here is some part of original data and we have 20 executives and they have 5 to 10 rows of data. For simplification we have used 3 executives with less than 5 rows for each one.
开始时间不遵循任何升序或降序
The start timings do not follow any ascending or descending order
必填：结果表应提供每小时特定主管是否忙还是闲的信息。如果他待命一分钟，则应在整个一小时内忙碌
Required: The result table should give whether particular executive is busy or free in every hour. If he is on call for one minute it should give busy for that entire one hour period
结果应为：
The result should be like:
同一文件是
感谢提前！！！
推荐答案
您需要在OR函数中放置一个额外的逻辑测试，该测试测试开始时间小于时间间隔开始和结束时间大于时间间隔结束。因此，在G31单元格中，您的公式应为：
You need to put in an extra logical test in your OR function that tests for start times less than the time interval start and end times greater than the time interval end. So in cell G31 your formula should read:
= IF（OR（COUNTIFS（$ A $ 3：$ A $ 14，A31，$ C $ 3：$ C $ 14， > 0，$ D $ 3：$ D $ 14，> = 14:00，$ D $ 3：$ D $ 14，< 15：00），COUNTIFS（$ A $ 3：$ A $ 14，A31，C $ 3：$ C $ 14，> 0，$ E $ 3：$ E $ 14，> = 14:00，$ E $ 3：$ E $ 14，< 15：00）， COUNTIFS（$ A $ 3：$ A $ 14，A31，C $ 3：$ C $ 14，> 0，$ D $ 3：$ D $ 14，< 14：00，$ E $ 3：$ E $ 14，> = 15： 00）），忙，免费）
=IF(OR(COUNTIFS($A$3:$A$14,A31,$C$3:$C$14,">0",$D$3:$D$14,">=14:00",$D$3:$D$14,"<15:00"),COUNTIFS($A$3:$A$14,A31,C$3:$C$14,">0",$E$3:$E$14,">=14:00",$E$3:$E$14,"<15:00"),COUNTIFS($A$3:$A$14,A31,C$3:$C$14,">0",$D$3:$D$14,"<14:00",$E$3:$E$14,">=15:00")),"Busy","Free")
这篇关于如何为客户服务主管生成活动图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
