Почему СУММЕСЛИМН возвращает некорректный результат?

Question

[SkyDelete]

В таблице есть лист Shifts, содержащий данные по сменам сотрудников, на листе Shifts fill rate by hours считаю сколько сотрудников на смене в конкретный час такой формулой (количество начатых смен - количество завершенных смен).

=СЧЁТЕСЛИМН(Shifts!$D$3:$D; "<="&D$1;Shifts!$A$3:$A;$A3)
-СЧЁТЕСЛИМН(Shifts!$E$3:$E; "<="&D$1;Shifts!$A$3:$A;$A3)

В большинстве часов результат корректный, но не всегда.

Для диагностики разделил формулу на две отдельных, видно в 4 строке, что в 10/13/19 часов, не хватает одной начатой смены, при этом в остальные часы - всё корректно.

В 5 строке, аналогичным образом не хватает завершенных смен в 13/19/22 часа.

Answer 1

[Даниил Бабкин]

1. Уберите строки для диапазонов, оставь только поля:
=COUNTIFS(Shifts!$D:$D; "<="&B$1;Shifts!$A:$A;$A2)-COUNTIFS(Shifts!$E:$E; "<="&B$1;Shifts!$A:$A;$A2)
Из-за этого у вас поле А2 вылетало из расчета

2. Основная проблема - в несовпадении форматов времени в полях D, Е листа Shifts и шапки на листе Shifts fill...
Быстрорешение: настрой на листе Shifts fill... шапку по всем правилам (формат данных выстави "Время") и скопируйте из нее значения времени на лист Shifts. После можешь копировать время на Shifts из ранее отработанных смен. Я скопировал время 16:00:00 для 9.12 - заработало, как надо
Нормальное решение - сделайте справочник времени и берите данные из него через выпадающий список. И для шапки на Shifts fill..., и для обозначения начала и конца смены.

Comments

[SkyDelete]

Спасибо за ответ.
1. Тут мой косяк, в оригинальной таблице заголовок занимает 2 строки, упустил этот момент.
2. Решительно не понимаю чем отличается формат данных, вроде все настраивал одинаково, но действительно копирование времени помогло.
Так к сожалению не получится, поскольку получаю уже заполненную таблицу, вручную корректировать все строки - нереально.

[Даниил Бабкин]

SkyDelete, формат данных влияет напрямую. Система не может самостоятельно понять, текст у вас или время.
В вашем случае смену формата проще всего сделать через формулы.
На листе Shifts в E3 введите формулу (допустим, в F3):
=time(hour(E3);minute(E3);second(E3))

На листе Shifts fill... - аналогичная формула:
=time(hour(B1);minute(B1);second(B1))

В дальнейшем есть 2 варианта:
1. Копируете из F в E данные через "Специальную вставку - Сохранить только значения" и оставляете как есть, но время вводите внимательно
2. в формулах ссылаетесь на поле F, а не E. Это более удобный вариант

У меня все получилось.
Удачи :-)