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

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社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。

(0)

相关推荐