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

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

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

图1

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

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

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

=MEDIAN(INDEX(I7:I13,WEEKDAY(B7,2))+0,INDEX(J7:J13,WEEKDAY(B7,2))+0,MOD(B7,1))<>MOD(

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

公式取了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中下载。