Сколько часов находится в заданном интервале времени?

Question

Я хочу считать часы как временной интервал в заданных временных интервалах. Мне немного сложно выразить то, что я хочу, словами на английском, поэтому позвольте мне объяснить, что я хочу на примере:

  A                 B               C        D          E
1 Start of Fault    End of Fault     Time Ranges        Number of Faults
2 12:00             13:00           00:00    01:00      X
3 12:00             14:00           01:00    02:00      X
4 08:30             23:00           02:00    03:00      X
5 00:00             24:00           03:00    04:00      X
. ...               ...             ...      ...        ...

Как видите, у меня есть время начала и окончания ошибки. В столбцах C и D у меня есть диапазоны времени, начиная с 00:00 и заканчивая в 24:00. Также,

X = How many faults did happen between a time range (00:00-01:00)?

X должен сканировать столбцы A и B, и, если заданный диапазон времени (скажем, 12:00-13:00) включен в любые интервалы времени (скажем, 12:00-13:00, 08:30-23:00), X количество пересечений между заданным временным диапазоном и временными интервалами.

Я пытался вычислить X, используя эту фолмула Excel:

=IF(AND($A$2>=C2;$B$2<=D2);1;0)+IF(AND($A$3>=C2;$B$3<=D2);1;0)+IF(AND($A$4>=C2;$B$4<=D2);1;0)+IF(AND($A$5>=C2;$B$5<=D2);1;0)

Но эта формула работает, только если временной диапазон и временной интервал равны.

Есть ли способ для меня получить значение X с помощью формулы Excel?

Заранее спасибо.

PS: мой родной язык не английский.

Answer 1

Ваша формула будет:

=COUNTIFS(A:A,"<"&D2,B:B,">="&C2)

Единственная проблема с вашим "Временным диапазоном" заключается в том, что время начала и окончания перекрываются, то есть некоторые значения могут учитываться дважды. Чтобы это исправить, я решил использовать A<D но B>=C Если это не ваше ожидаемое поведение, и вы хотите, чтобы они учитывались дважды в течение одной и той же "минуты", тогда вы можете изменить A<D на A<=D

Поместите формулу в E2 затем заполните.