EXCEL- считать выходные дни в течение нескольких дат в рамках Countifs с перекрытием

Question

Я должен определить для каждой специальности общий процент операций, которые начались в нерабочее время или в выходные дни.

Это то, что я до сих пор:

=COUNTIFS(Table1[Start surgery];">17:00")+COUNTIFS(Table1[Start surgery];"<09:00")+COUNTIFS(Table1[Surgery date];"MOD(WEEKDAY(cell), 7) <2")

Первые 2 индикатора работают, но я не могу считать дни выходных. И второе - это то, что Excel может дважды сосчитать несколько операций, потому что операция может быть после 17:00 и в выходные дни, но это должно считаться как одно.

Кто может мне помочь:)?

Answer 1

Так как первая часть вашей формулы работает на время, я только что изменил последнюю часть вашей формулы...

=COUNTIFS(Table1[Start surgery];">17:00")+COUNTIFS(Table1[Start surgery];"<09:00")+SUMPRODUCT((WEEKDAY(Table1[Surgery date])=1)+(WEEKDAY(Table1[Surgery date])=7)

Где 1 и 7 эквивалентны субботе и воскресенью.

В настоящее время проблема с вышеприведенной формулой заключается в том, что время операции после 17:00 и до 09:00 выходных учитывается дважды!

Чтобы исправить это, все условия можно было бы переместить в sumproduct как одно потенциальное решение, и вы получите:

=SUMPRODUCT((WEEKDAY(Table1[Surgery Date])=1)+(WEEKDAY(Table1[Surgery Date])=7)+(WEEKDAY(Table1[Surgery Date])=MEDIAN(WEEKDAY(Table1[Surgery Date]),2,6))*(Table1[Start surgery]<"09:00")+(WEEKDAY(Table1[Surgery Date])=MEDIAN(WEEKDAY(Table1[Surgery Date]),2,6))*(Table1[Start surgery]>"17:00"))

Теперь количество скобок может быть там, потому что я не использовал Excel в то время. Другая потенциальная проблема, с которой мы можем столкнуться, связана со временем. ваше время n текст столбца времени или число, отформатированное для отображения как время? Это может включать изменение времени сравнения в другой формат, но сама формула должна оставаться неизменной.

Основная мысль здесь заключается в том, что мы помещаем каждое условие в sumproduct. Условие оценивается как ИСТИНА или ЛОЖЬ. Если условие подвергается математической операции, оно будет преобразовано в 1 для ИСТИНЫ и 0 в ЛОЖЬ. + действовать как операторы ИЛИ и * действовать как оператор И.