看我的加班电话数就知道我有多忙了——根据时间段统计

excelperfect

有时候，我们需要从网站上或者服务器上下载数据，然后根据条件进行统计，特别是统计特定的时间。本文来源于chandoo.org，要求根据工作时间段统计工作时间外所接电话数。

示例数据如下图1所示。有两个表，一个是通话记录，记录了每次通话开始的时间；一个是办公时间，告诉每周七天的工作时段。

图1

现在想知道，在工作时段外共接了多少个电话？

我们先使用辅助列来解答。

在单元格C7中输入下面的公式：

=MEDIAN(INDEX($I$7:$I$13,WEEKDAY($B7,2))+0,INDEX($J$7:$J$13,WEEKDAY($B7,2))+0,MOD($B7,1))<>MOD($B7,1)

将公式下拉至数据列末尾。

公式取了3个值，即该行所在日的工作开始时间、结束时间和通话时间，然后取其位于中间的值，再与通话时间比较，如果不相等，说明该通话时间处于工作时间之外，公式返回TRUE。

然后，统计TRUE值的个数，即在工作时间之处的电话数：

=COUNTIF(C7:C1172,'TRUE')

返回：693。

如果不使用辅助列，可以使用公式：

=SUMPRODUCT(1-COUNTIFS(H7:H13,TEXT(B7:B1172,'DDDD'),I7:I13,'<='&MOD(B7:B1172,1),J7:J13,'>='&MOD(B7:B1172,1)))

如果使用表内部标记，假设电话记录表名为：calls.ans，办公时间表名为：working.hours.ans，那么可使用公式：

=SUMPRODUCT(1-COUNTIFS(working.hours.ans[日],TEXT(calls.ans[通话时间],'DDDD'),working.hours.ans[开始时间],'<='&MOD(calls.ans[通话时间],1), working.hours.ans[结束时间],'>='&MOD(calls.ans[通话时间],1)))

示例工作簿可以在chandoo.org上下载，或者在完美Excel社群知识星球App中下载。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。