Вычитание времени и суммирование разницы по столбцам
supercelt
Дата: Пятница, 03.07.2020, 08:54 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Доброе утро. В AH9 надо одной формулой сделать следующее: Начать сканировать весь месяц с C по AG, в строке 9 - время начала работ, в 10 - конец. Для каждого дня найти длительность смены, путём вычитания времени, игнорировать пустые дни и все ячейки в которых - не дата (к примеру ОТ, НД, может быть что угодно), затем суммировать все разности времени получившиеся и получить в итоге 32:07. Ну а потом перевести это время в количество 8 часовых смен. Как-то так. Но проблема, не получается именно просуммировать разницы. Пытался работать вот с такой формулой, считает #знач и это всё портит.Код
=СУММПРОИЗВ(--((ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(":";C10:AG10));))*(ЕСЛИОШИБКА(ОСТАТ(C10:AG10-C9:AG9;1);))))
Спасибо
Доброе утро. В AH9 надо одной формулой сделать следующее: Начать сканировать весь месяц с C по AG, в строке 9 - время начала работ, в 10 - конец. Для каждого дня найти длительность смены, путём вычитания времени, игнорировать пустые дни и все ячейки в которых - не дата (к примеру ОТ, НД, может быть что угодно), затем суммировать все разности времени получившиеся и получить в итоге 32:07. Ну а потом перевести это время в количество 8 часовых смен. Как-то так. Но проблема, не получается именно просуммировать разницы. Пытался работать вот с такой формулой, считает #знач и это всё портит.Код
=СУММПРОИЗВ(--((ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(":";C10:AG10));))*(ЕСЛИОШИБКА(ОСТАТ(C10:AG10-C9:AG9;1);))))
Спасибо supercelt
Ответить
Сообщение Доброе утро. В AH9 надо одной формулой сделать следующее: Начать сканировать весь месяц с C по AG, в строке 9 - время начала работ, в 10 - конец. Для каждого дня найти длительность смены, путём вычитания времени, игнорировать пустые дни и все ячейки в которых - не дата (к примеру ОТ, НД, может быть что угодно), затем суммировать все разности времени получившиеся и получить в итоге 32:07. Ну а потом перевести это время в количество 8 часовых смен. Как-то так. Но проблема, не получается именно просуммировать разницы. Пытался работать вот с такой формулой, считает #знач и это всё портит.Код
=СУММПРОИЗВ(--((ЕСЛИОШИБКА(ЕЧИСЛО(ПОИСК(":";C10:AG10));))*(ЕСЛИОШИБКА(ОСТАТ(C10:AG10-C9:AG9;1);))))
Спасибо Автор - supercelt Дата добавления - 03.07.2020 в 08:54
AlexM
Дата: Пятница, 03.07.2020, 09:19 |
Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4491
Репутация:
1115
±
Замечаний:
0% ±
Excel 2003
Формула массиваКод
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
PS. Объединенные ячейки ЗЛО!
Формула массиваКод
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
PS. Объединенные ячейки ЗЛО! AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Формула массиваКод
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
PS. Объединенные ячейки ЗЛО! Автор - AlexM Дата добавления - 03.07.2020 в 09:19
Nic70y
Дата: Пятница, 03.07.2020, 09:24 |
Сообщение № 3
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация:
2273
±
Замечаний:
0% ±
Excel 2010
если сначала ctrl+h найти: : заменить на: : заменить все, то можноКод
=СУММ(C10:AG10)+СЧЁТЕСЛИ(C10:AG10;0)-СУММ(C9:AG9)
если сначала ctrl+h найти: : заменить на: : заменить все, то можноКод
=СУММ(C10:AG10)+СЧЁТЕСЛИ(C10:AG10;0)-СУММ(C9:AG9)
Nic70y
ЮMoney 41001841029809
Ответить
Сообщение если сначала ctrl+h найти: : заменить на: : заменить все, то можноКод
=СУММ(C10:AG10)+СЧЁТЕСЛИ(C10:AG10;0)-СУММ(C9:AG9)
Автор - Nic70y Дата добавления - 03.07.2020 в 09:24
AlexM
Дата: Пятница, 03.07.2020, 09:36 |
Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4491
Репутация:
1115
±
Замечаний:
0% ±
Excel 2003
Nic70y , а если окончание рабочего дня, например 1:00?
Nic70y , а если окончание рабочего дня, например 1:00?AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Nic70y , а если окончание рабочего дня, например 1:00?Автор - AlexM Дата добавления - 03.07.2020 в 09:36
Nic70y
Дата: Пятница, 03.07.2020, 09:47 |
Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация:
2273
±
Замечаний:
0% ±
Excel 2010
у меня всегда одна отмазка: какой пример - такой и ответ
у меня всегда одна отмазка: какой пример - такой и ответ Nic70y
ЮMoney 41001841029809
Ответить
Сообщение у меня всегда одна отмазка: какой пример - такой и ответ Автор - Nic70y Дата добавления - 03.07.2020 в 09:47
supercelt
Дата: Пятница, 03.07.2020, 10:30 |
Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Формула массива [Перевод / Translate] =СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9)) PS. Объединенные ячейки ЗЛО!
Формула как-то странно посчитала... итог 8:07. А вот скажите, почему формулаКод
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);)-ЦЕЛОЕ(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);))*24))
выдаёт ноль. Если вместо L поставить K (то есть мы не лезем на ячейки в которых не время), то считает правильно. Но если залезть на ячейку с текстом ОТ, то итог получается ноль, хотя функция ЕСЛИОШИБКА должна отрабатывать только один элемент массива, а она влияет на общий результат почему-то
Формула массива [Перевод / Translate] =СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9)) PS. Объединенные ячейки ЗЛО!
Формула как-то странно посчитала... итог 8:07. А вот скажите, почему формулаКод
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);)-ЦЕЛОЕ(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);))*24))
выдаёт ноль. Если вместо L поставить K (то есть мы не лезем на ячейки в которых не время), то считает правильно. Но если залезть на ячейку с текстом ОТ, то итог получается ноль, хотя функция ЕСЛИОШИБКА должна отрабатывать только один элемент массива, а она влияет на общий результат почему-тоsupercelt
Ответить
Сообщение Формула массива [Перевод / Translate] =СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9)) PS. Объединенные ячейки ЗЛО!
Формула как-то странно посчитала... итог 8:07. А вот скажите, почему формулаКод
=СУММПРОИЗВ(--(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);)-ЦЕЛОЕ(ЕСЛИОШИБКА(ОСТАТ(C10:L10-C9:L9;1);))*24))
выдаёт ноль. Если вместо L поставить K (то есть мы не лезем на ячейки в которых не время), то считает правильно. Но если залезть на ячейку с текстом ОТ, то итог получается ноль, хотя функция ЕСЛИОШИБКА должна отрабатывать только один элемент массива, а она влияет на общий результат почему-тоАвтор - supercelt Дата добавления - 03.07.2020 в 10:30
AlexM
Дата: Пятница, 03.07.2020, 10:54 |
Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4491
Репутация:
1115
±
Замечаний:
0% ±
Excel 2003
Формула как-то странно посчитала... итог 8:07
формат ячейки должен быть [ч]:мм в моем файле он установлен. И про ЗЛО писал. В объединенные ячейки формулы массивы просто не вставляются.
Формула как-то странно посчитала... итог 8:07
формат ячейки должен быть [ч]:мм в моем файле он установлен. И про ЗЛО писал. В объединенные ячейки формулы массивы просто не вставляются.AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Формула как-то странно посчитала... итог 8:07
формат ячейки должен быть [ч]:мм в моем файле он установлен. И про ЗЛО писал. В объединенные ячейки формулы массивы просто не вставляются.Автор - AlexM Дата добавления - 03.07.2020 в 10:54
supercelt
Дата: Пятница, 03.07.2020, 14:49 |
Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
AlexM , На самом деле вставляются, если сначала вставить формулу, а уже потом объединить ячейки)
AlexM , На самом деле вставляются, если сначала вставить формулу, а уже потом объединить ячейки)supercelt
Ответить
Сообщение AlexM , На самом деле вставляются, если сначала вставить формулу, а уже потом объединить ячейки)Автор - supercelt Дата добавления - 03.07.2020 в 14:49
supercelt
Дата: Пятница, 03.07.2020, 15:03 |
Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
AlexM , а что даёт минус в формуле перед С9?Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
AlexM , а что даёт минус в формуле перед С9?Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
supercelt
Ответить
Сообщение AlexM , а что даёт минус в формуле перед С9?Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))
Автор - supercelt Дата добавления - 03.07.2020 в 15:03
AlexM
Дата: Пятница, 03.07.2020, 15:18 |
Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4491
Репутация:
1115
±
Замечаний:
0% ±
Excel 2003
вставить формулу, а уже потом объединить ячейки
Я это имел ввиду преобразует формат значения в числовой. Числа остаются числами, а текстовые значения, которые нельзя преобразовать в число, становятся ошибкой. ЕЧИСЛО() возвращает ИСТИНА для чисел и ЛОЖЬ для ошибки.Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0?
вставить формулу, а уже потом объединить ячейки
Я это имел ввиду преобразует формат значения в числовой. Числа остаются числами, а текстовые значения, которые нельзя преобразовать в число, становятся ошибкой. ЕЧИСЛО() возвращает ИСТИНА для чисел и ЛОЖЬ для ошибки.Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0? AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение вставить формулу, а уже потом объединить ячейки
Я это имел ввиду преобразует формат значения в числовой. Числа остаются числами, а текстовые значения, которые нельзя преобразовать в число, становятся ошибкой. ЕЧИСЛО() возвращает ИСТИНА для чисел и ЛОЖЬ для ошибки.Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0? Автор - AlexM Дата добавления - 03.07.2020 в 15:18
supercelt
Дата: Вторник, 07.07.2020, 16:02 |
Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Добрый день! В продолжении темы... Посмотрите пожалуйста на строку 29. Вот по дням ночной интервал считает на ура. А в ячейке AM9 сложить эти интервалы почему-то не может... а почему? ПС. Формула в 29 строке вычисляет ночные часы из интервалов старт-конец при заданных параметрах ночной смены. А в AM29 формула сделана на основе первой.
Добрый день! В продолжении темы... Посмотрите пожалуйста на строку 29. Вот по дням ночной интервал считает на ура. А в ячейке AM9 сложить эти интервалы почему-то не может... а почему? ПС. Формула в 29 строке вычисляет ночные часы из интервалов старт-конец при заданных параметрах ночной смены. А в AM29 формула сделана на основе первой. supercelt
Ответить
Сообщение Добрый день! В продолжении темы... Посмотрите пожалуйста на строку 29. Вот по дням ночной интервал считает на ура. А в ячейке AM9 сложить эти интервалы почему-то не может... а почему? ПС. Формула в 29 строке вычисляет ночные часы из интервалов старт-конец при заданных параметрах ночной смены. А в AM29 формула сделана на основе первой. Автор - supercelt Дата добавления - 07.07.2020 в 16:02
Pelena
Дата: Вторник, 07.07.2020, 19:06 |
Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19182
Репутация:
4420
±
Замечаний:
±
Excel 365 & Mac Excel
потому что функции МАКС и МИН в результате дают одно число, а Вам надо сравнивать массивы поэлементно ВариантКод
=СУММ(ЕСЛИОШИБКА(--ТЕКСТ(1-ТЕКСТ(1-(ОСТАТ(C10:AG10-$AN$3;1)+(ОСТАТ(C10:AG10-$AN$3;1)<ОСТАТ(C9:AG9-$AN$3;1)));"ч:мм;\0")-(ТЕКСТ(ОСТАТ(C9:AG9-$AN$3;1)-ОСТАТ($AL$3-$AN$3;1);"ч:мм;\0")+ОСТАТ($AL$3-$AN$3;1));"ч:мм;\0");0))
потому что функции МАКС и МИН в результате дают одно число, а Вам надо сравнивать массивы поэлементно ВариантКод
=СУММ(ЕСЛИОШИБКА(--ТЕКСТ(1-ТЕКСТ(1-(ОСТАТ(C10:AG10-$AN$3;1)+(ОСТАТ(C10:AG10-$AN$3;1)<ОСТАТ(C9:AG9-$AN$3;1)));"ч:мм;\0")-(ТЕКСТ(ОСТАТ(C9:AG9-$AN$3;1)-ОСТАТ($AL$3-$AN$3;1);"ч:мм;\0")+ОСТАТ($AL$3-$AN$3;1));"ч:мм;\0");0))
Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение потому что функции МАКС и МИН в результате дают одно число, а Вам надо сравнивать массивы поэлементно ВариантКод
=СУММ(ЕСЛИОШИБКА(--ТЕКСТ(1-ТЕКСТ(1-(ОСТАТ(C10:AG10-$AN$3;1)+(ОСТАТ(C10:AG10-$AN$3;1)<ОСТАТ(C9:AG9-$AN$3;1)));"ч:мм;\0")-(ТЕКСТ(ОСТАТ(C9:AG9-$AN$3;1)-ОСТАТ($AL$3-$AN$3;1);"ч:мм;\0")+ОСТАТ($AL$3-$AN$3;1));"ч:мм;\0");0))
Автор - Pelena Дата добавления - 07.07.2020 в 19:06
supercelt
Дата: Среда, 08.07.2020, 10:14 |
Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Pelena , ух ты! Класс. Спасибо большое)
Pelena , ух ты! Класс. Спасибо большое)supercelt
Ответить
Сообщение Pelena , ух ты! Класс. Спасибо большое)Автор - supercelt Дата добавления - 08.07.2020 в 10:14