Excel:计算一个时间段内有多少行 [英] Excel: Counting how many rows fall within a time period

问题描述

我有一个2列的Excel工作表

I have an excel sheet with 2 columns

A列="CA"，"CR"或"IN"

Column A = "CA", "CR" or "IN"

B列=日期&时间格式DD/MM/YYYY HH:MM

Column B = Date & time format DD/MM/YYYY HH:MM

我想在具有此条件的每一行的底部进行计数:

I want to make a count at the bottom of a column for each row that has this criteria:

i)第1-8行="CA"

i) Row 1-8 = "CA"

ii)第1-8行需要检查一个时间范围，即>"17:00"和<"04:59"

ii) Row 1-8 needs to check for a time range, namely > "17:00" and < "04:59"

这是我到目前为止提出的:

This is what I've come up with so far:

= COUNTIFS(A2:A8，"CA"，B2:B8，RIGHT(TEXT(B2:B8，"hh:mm")，5)>"04:59")，B2:B8，RIGHT(TEXT(B2:B8，"hh:mm")，5)<"17:00")

=COUNTIFS(A2:A8,"CA",B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)>"04:59"), B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)<"17:00")

我认为在text函数中使用范围是错误的，但不知道如何解决.

I presume using a range within a the text function is wrong, but don't know how to resolve this.

由于B列采用日期和时间格式，因此我必须将其更改为函数中的字符串，这样我才可以在时间上进行测试-也许有更好的方法了?)

Because Column B is in a date and time format, I'm having to change it to a string within the function so I can make a test on just the time - Maybe there's a better way?)

谢谢

推荐答案

COUNTIFS 的问题在于您不能太多地操纵条件.您可以插入仅包含时间的列，在这种情况下，可以使用 COUNTIFS ，但如果不能，则可以使用 SUMPRODUCT 代替:

The problem with COUNTIFS is that you can't manipulate the conditions a lot. You could insert a column containing the time only, in which case you could use COUNTIFS but if you cannot, you can use SUMPRODUCT for substitute:

=SUMPRODUCT((A1:A6="CA")*(((TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0))+(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)))>0))

这对示例范围A1:B6应用了一些条件:

This applies a few conditions on an example range A1:B6:

(A1:A6 ="CA")，确保该行具有 CA ，

(TEXT(B1:B6，"hh:mm")* 1&TIME; TIME(17,0,0))确保时间在17:00以上

(TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0)) that ensures that the time is above 17:00

(TEXT(B1:B6，"hh:mm")* 1< TIME(5,0,0))确保时间在05:00之前(您有4个:59，如果您的意思确实是少于4:59 ，请更改此部分.

(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)) that ensures that the time is before 05:00 (You have 4:59 in your question, if you really meant less than 4:59 then change this part).

最后两个条件的 + 与两个条件进行或"运算，然后整个内容与第一个条件进行与"运算.

The + for the last two conditions 'OR's the two conditions, then the whole thing is 'AND'ed with the first condition.

这篇关于Excel:计算一个时间段内有多少行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！