Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сумма ячеек со временами в формате чч:мм (перед часам может быть минус)
 
#1
02.10.2019 18:39:19
Добрый день.
Пытаюсь сложить несколько ячеек со временем в формате чч:мм (перед часами может быть минус) обычным =СУММ(B1:B9)
Пробовал выставлять формат ячеек и Общий и чч:мм - в обоих случаях сумма получается 00:00
Подскажите, как сделать это правильно?
Файл во вложении.
Заранее спасибо.
Изменено: bairog - 02.10.2019 18:41:34
 
 
 
#2
02.10.2019 18:52:34
1. отрицательное время не бывает.
2. У вас в ячейках текст.
Что делать.
1. Копируете любую пустую ячейку.
2. Выделяете диапазон с данными.
3. кликнуть правой кнопкой мыши.
4. Выбрать "Специальная вставка"
5. Сложить, Ок.
6. Поставить диапазону формат Время часы и минуты

Отрицательное время суммироваться не будет, так как эти значения останутся текстом.

Сумму можно считать формулой массива
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-B1:B9);--B1:B9))-СУММ(ЕСЛИ(ЕОШ(-B1:B9);--ПРАВБ(B1:B9;5)))
Изменено: АlехМ - 02.10.2019 19:01:49
Алексей М.
 
 
 
#3
02.10.2019 19:40:41
Цитата
АlехМ написал:
отрицательное время не бывает.
Это не не совсем правильное утверждение. Достаточно вспомнить
UTC−3:30 Канада (Ньюфаундленд)
UTC−9:30 Маркизские острова (Франция)

=TEXT(ABS(SUMPRODUCT(1-(LEFT(B1:B9)="-")*2;IFERROR(--RIGHT(B1:B9;5);B1:B9)));IF(SUMPRODUCT(1-(LEFT(B1:B9)="-")*2;IFERROR(--RIGHT(B1:B9;5);B1:B9))<0;"-";"")&"чч:мм")
а раз уж массивная
=TEXT(ABS(SUM((1-(LEFT(B1:B9)="-")*2)*IFERROR(--RIGHT(B1:B9;5);B1:B9)));IF(SUM((1-(LEFT(B1:B9)="-")*2)*IFERROR(--RIGHT(B1:B9;5);B1:B9))<0;"-";"")&"чч:мм")
Изменено: БМВ - 02.10.2019 19:48:46
По вопросам из тем форума, личку не читаю.
 
 
 
#4
02.10.2019 20:16:16
А чего сразу UTC и Канада? У кота Мск -1 ))
 
 
 
#5
02.10.2019 20:25:12
Цитата
Юрий М написал:
У кота Мск -1
Ну то что у кота -1 это …. :-)
Юр, я спецом взял зоны где 30  мин еще есть. чтоб именно выгледело в формате чч:мм.
По вопросам из тем форума, личку не читаю.
 
 
 
#6
02.10.2019 20:28:20
Миш, ну это же всё равно не отрицательное время :-]
 
 
 
#7
02.10.2019 20:30:41
А кто говорит про отрицательное , даже у ТС
Цитата
bairog написал:
со временем в формате чч:мм (перед часами может быть минус)
,а я
Цитата
БМВ написал:
Это не совсем
ну пусть это будет дельта времени в формате чч:мм, а дельта может иметь положительный и отрицательный знак.
Изменено: БМВ - 02.10.2019 20:31:46
По вопросам из тем форума, личку не читаю.
 
 
 
#8
03.10.2019 06:49:16
Если время с минусом надо вычитать, то можно такие массивные формулы использовать:
Код
=СУММ(-ЕСЛИ(ЕОШ(-B1:B9);ПОДСТАВИТЬ(B1:B9;"-";);-B1:B9))
Код
=СУММ(-ЕСЛИ(ЕОШ(-B1:B9);ПСТР(B1:B9;2;9);-B1:B9))

*

Код
=СУММ(-ЕСЛИ(ЕОШ(-B1:B9);ПРАВБ(B1:B9;5);-B1:B9))
Изменено: Светлый - 03.10.2019 07:13:04
 
 
 
#9
03.10.2019 07:25:02
Светлый,  До примерно у всех одно и тоже, именно суммирование у меня
=SUM((1-(LEFT(B1:B9)="-")*2)*IFERROR(--RIGHT(B1:B9;5);B1:B9))
можно так, а все остальное - определение и подстановка знака, ведь результат может быть отрицательным и в формат чч:мм не переведется.
Изменено: БМВ - 03.10.2019 07:37:50
По вопросам из тем форума, личку не читаю.
 
 
 
#10
03.10.2019 08:04:57
Цитата
БМВ написал:
можно так
Эта формула не неправильно воспринимает значения в 5 и 6 ячейках массива.
Изменено: Светлый - 03.10.2019 08:08:25
 
 
 
#11
03.10.2019 08:31:21
Возможно что-то не заметил, но ручной счет давал тотже результат.
По вопросам из тем форума, личку не читаю.
 
 
 
#12
03.10.2019 09:21:00
В этих ячейках числовое значение, показанное как время. Правые символы из него будут взяты как большое целое число (сутки), а минуты, подразумеваемые в этих ячейках, к сумме не добавятся.
 
 
 
#13
03.10.2019 09:36:48
Светлый,  , ну в примере там просто 0 и по этому результат верный, но согласен, для общего случая лучше подправить.
=SUM(IFERROR(--B1:B9;(1-(LEFT(B1:B9)="-")*2)*RIGHT(B1:B9;5)))
Изменено: БМВ - 03.10.2019 09:52:37
По вопросам из тем форума, личку не читаю.
 
 
 
#14
03.10.2019 12:10:15
Цитата
БМВ написал:
ну пусть это будет дельта времени в формате чч:мм, а дельта может иметь положительный и отрицательный знак.
Всё верно, это фактически и есть дельта по времени (может быть + или -).
Задача стандартная: есть норма времени (8:00 скажем) и есть фактически отработанное человеком время. Исходя из этого нужно посчитать дельту за каждый день и потом эти дельты просуммировать за месяц скажем.
Что странно - попробовал не вводить дельту вручную, а считать по простейшей формуле
Код
=ЕСЛИ(ИЛИ(C2="";B2="");"";C2-B2)
а потом просто суммировать ячейки обычным
Код
=СУММ(D2:D31)
И всё работает (формат ячеек всё тот же ч:мм) - файл во вложении


Конечно есть ограничение, что каждая дельта (и соответственно сумма) не может быть больше +23:59 (и соответственно меньше -23:59) - но мне и так подходит.
Почему это вдруг заработало, если вы говорили, что формат не подходящий для минуса?

P.S. Всем спасибо за ответы.
 
 
 
#15
03.10.2019 12:25:08
Цитата
bairog написал:
если вы говорили, что формат не подходящий для минуса?
перевести текст, что был у вас в ячейках с минусом в число(коим является время) просто так не получается и для этого шаманили
Другое дело, что формат ч:мм лично у меня не работает с отрицательным числом в созданных файлах, а в файле из №14 норм.
По вопросам из тем форума, личку не читаю.
 
 
 
#16
03.10.2019 12:49:43
использование отрицательных значений времени:
Параметры
Дополнительно
в последнем экране, в группе: При пересчете этой книги
Использовать систему дат 1904 - поставьте птичку
Ок

посчитайте где угодно 4 часа - 3 часа. увидите -1 час
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#17
03.10.2019 12:52:57
Цитата
Ігор Гончаренко написал:
Использовать систему дат 1904
Игорь, Спс. не было времени туда заглянуть.
По вопросам из тем форума, личку не читаю.
 
 
 
#18
03.10.2019 23:19:46
Цитата
Ігор Гончаренко написал:
Использовать систему дат 1904
Формат ячейки [чч]:мм и массивная формула:
Код
=СУММ(-ЕСЛИОШИБКА(-B1:B9;ПРАВБ(B1:B9;5)))
Код
=СУММ(-ЕСЛИОШИБКА(-B1:B9;ПСТР(B1:B9;2;9)))
 
 
 
Страницы: 1
Наверх