Rob保留每天完成的每项工作的统计信息。例如,他通过输入以下格式的每个作业的开始时间(B列)和结束时间(C列)来跟踪每个作业花费的确切小时数和分钟数,格式为:05/11/20 11:25 am。在单独的列(G列)中,他具有每月的每一天的日期。他想在每个日期的右侧(在H列中)显示在每个http://calendarcorner.net/[calendar]日期中他完成了多少工作。罗布想知道他将使用什么公式来计算此计数。

这项任务并不像乍看起来那样容易。原因是由于开始时间和结束时间的存储方式。

B列在同一列中同时包含开始日期和时间,格式为“ 05/11/20 11:25 am”,C列包含相同格式的结束日期和时间。您可能会认为可以通过这种方式在G列中使用COUNTIF函数:

=COUNTIF(C$2:C$1000,G2)

但是,这不起作用。原因很简单-列G(在此例中为单元格G2)包含一个没有时间的日期。例如,它可能包含日期05/11/20。与单元格C2(可能包含20年5月11日上午11:25)进行比较时,它们并不相同。请记住,Excel会根据每个单元格中存储的日期和时间序列号进行比较。给定刚才提到的示例值,单元格G2将包含序列号39217,而单元格C2将包含序列号39217.47569。由于这两个值不相同,因此COUNTIF不会将它们视为相等。

显然,一种解决方案是添加另一列,该列仅包含每个作业的结束日期,而没有时间。然后,您可以在公式中使用COUNTIF函数,因为您可以将“苹果与苹果”进行比较。但是有一种解决方案不需要在新列中使用中间结果。此方法使用COUNTIFS函数,该函数对范围内的所有单元格应用两个条件:

=COUNTIFS(C$2:C$1000,">=" & G2,C$2:C$1000,"<" & (G2+1))

此公式使用两个条件,可以有效地查看C列中单元格中的值是否大于或等于G2中的值但小于G2 + 1。换句话说,它将计算在G2中指定的日期内发生的所有事件。

另一种方法是确保将C列中的任何整数值与G列中存储的日期进行比较。这可以通过使用SUMPRODUCT函数以这种方式完成:

=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)

将C2:C1000范围内的每个值的整数与G2中的日期进行比较,以给出True和False值的数组。将这些值分别乘以1,即可将True和False值分别转换为1和0值。然后,公式将这些乘积求和,得到所需的计数。

如果愿意,还可以使用以下公式,该公式使用SUMPRODUCT基本上执行相同的任务:

=SUMPRODUCT(--(INT(C$2:C$1000)=G2))

使用COUNTIFS函数或SUMPRODUCT函数确定结果的好处之一是您不需要使用数组公式。但是,有些人更喜欢使用数组公式。如果您想使用它们,则可以使用以下任意一种:

=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))

=SUM(IF(ROUNDDOWN(C$2:C$1000,0)=G2,1,0))

请记住,必须使用Ctrl + Shift + Enter将数组公式输入到单元格中。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(10422)适用于Office 365中的Microsoft Excel 2007、2010、2013、2016、2019和Excel。您可以在此处找到适用于Excel的较早菜单界面的本技巧的版本: