Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Вычитание времени и суммирование разницы по столбцам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычитание времени и суммирование разницы по столбцам (Формулы/Formulas)
Вычитание времени и суммирование разницы по столбцам
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);))))


Спасибо
К сообщению приложен файл: 6678544.xls (39.5 Kb)
 
Ответить
СообщениеДоброе утро. В 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Формула массива
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-C9:AG9);(C10:AG10<C9:AG9)+C10:AG10-C9:AG9))

PS. Объединенные ячейки ЗЛО!
К сообщению приложен файл: 5606484_01.xls (43.0 Kb)



Номер мобильного модема (без голосовой связи)
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
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
если сначала
ctrl+h
найти: :
заменить на: :
заменить все, то можно
Код
=СУММ(C10:AG10)+СЧЁТЕСЛИ(C10:AG10;0)-СУММ(C9:AG9)


ЮMoney 41001841029809
 
Ответить
Сообщениеесли сначала
ctrl+h
найти: :
заменить на: :
заменить все, то можно
Код
=СУММ(C10:AG10)+СЧЁТЕСЛИ(C10:AG10;0)-СУММ(C9:AG9)

Автор - Nic70y
Дата добавления - 03.07.2020 в 09:24
AlexM Дата: Пятница, 03.07.2020, 09:36 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Nic70y, а если окончание рабочего дня, например 1:00?



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеNic70y, а если окончание рабочего дня, например 1:00?

Автор - AlexM
Дата добавления - 03.07.2020 в 09:36
Nic70y Дата: Пятница, 03.07.2020, 09:47 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
а если
у меня всегда одна отмазка:
какой пример - такой и ответ :)


Ю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
Дата добавления - 03.07.2020 в 10:30
AlexM Дата: Пятница, 03.07.2020, 10:54 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Формула как-то странно посчитала... итог 8:07
формат ячейки должен быть [ч]:мм в моем файле он установлен.
И про ЗЛО писал. В объединенные ячейки формулы массивы просто не вставляются.



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Формула как-то странно посчитала... итог 8:07
формат ячейки должен быть [ч]:мм в моем файле он установлен.
И про ЗЛО писал. В объединенные ячейки формулы массивы просто не вставляются.

Автор - AlexM
Дата добавления - 03.07.2020 в 10:54
supercelt Дата: Пятница, 03.07.2020, 14:49 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
AlexM, На самом деле вставляются, если сначала вставить формулу, а уже потом объединить ячейки)
 
Ответить
Сообщение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
Дата добавления - 03.07.2020 в 15:03
AlexM Дата: Пятница, 03.07.2020, 15:18 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
вставить формулу, а уже потом объединить ячейки
Я это имел ввиду

что даёт минус
преобразует формат значения в числовой. Числа остаются числами, а текстовые значения, которые нельзя преобразовать в число, становятся ошибкой. ЕЧИСЛО() возвращает ИСТИНА для чисел и ЛОЖЬ для ошибки.
Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0?



Номер мобильного модема (без голосовой связи)
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 формула сделана на основе первой.
К сообщению приложен файл: 5433095.xls (47.0 Kb)
 
Ответить
СообщениеДобрый день! В продолжении темы... Посмотрите пожалуйста на строку 29. Вот по дням ночной интервал считает на ура. А в ячейке AM9 сложить эти интервалы почему-то не может... а почему?
ПС. Формула в 29 строке вычисляет ночные часы из интервалов старт-конец при заданных параметрах ночной смены. А в AM29 формула сделана на основе первой.

Автор - supercelt
Дата добавления - 07.07.2020 в 16:02
Pelena Дата: Вторник, 07.07.2020, 19:06 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

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))
К сообщению приложен файл: 5109325.xls (48.5 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-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
Дата добавления - 08.07.2020 в 10:14
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычитание времени и суммирование разницы по столбцам (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!