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屋!