看我的加班电话数就知道我有多忙了——根据时间段统计
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社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。