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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчета отработанных часов по графику - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчета отработанных часов по графику (Формулы/Formulas)
Подсчета отработанных часов по графику
Рамиля Дата: Среда, 30.03.2016, 08:19 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Для подсчета отработанных часов по графику записанных в одной ячейке в виде "8-15" я применяю формулу
Код
ЕСЛИ(ЕОШ(НАЙТИ("-";E5;1));0;--ТЕКСТ((ОТБР(ПСТР(E5;ПОИСК("-";E5)+1;9))-ОТБР(ЛЕВБ(E5;ПОИСК("-";E5)-1)))/24+(ОСТАТ(ПСТР(E5;ПОИСК("-";E5)+1;9);1)-ОСТАТ(ЛЕВБ(E5;ПОИСК("-";E5)-1);1))/24/60%;"ч,мм"))

Формула работает с любым промежутком времени, кроме "0-24". Как подкорректировать эту формулу под данный промежуток? Еще в медицине в графике иногда отображается вынужденный выход на работу в виде "8-15; 20-24" в одной ячейке. Как в этом случае формулой подсчитать часы? Я без вашей помощи реализовать описанные ситуации не смогла. Помогите пожалуйста!
[moder]Формулы нужно оформлять тегами!
Поправила.[/moder]
К сообщению приложен файл: 7099162.xls(46Kb)


Сообщение отредактировал Manyasha - Среда, 30.03.2016, 11:07
 
Ответить
СообщениеДля подсчета отработанных часов по графику записанных в одной ячейке в виде "8-15" я применяю формулу
Код
ЕСЛИ(ЕОШ(НАЙТИ("-";E5;1));0;--ТЕКСТ((ОТБР(ПСТР(E5;ПОИСК("-";E5)+1;9))-ОТБР(ЛЕВБ(E5;ПОИСК("-";E5)-1)))/24+(ОСТАТ(ПСТР(E5;ПОИСК("-";E5)+1;9);1)-ОСТАТ(ЛЕВБ(E5;ПОИСК("-";E5)-1);1))/24/60%;"ч,мм"))

Формула работает с любым промежутком времени, кроме "0-24". Как подкорректировать эту формулу под данный промежуток? Еще в медицине в графике иногда отображается вынужденный выход на работу в виде "8-15; 20-24" в одной ячейке. Как в этом случае формулой подсчитать часы? Я без вашей помощи реализовать описанные ситуации не смогла. Помогите пожалуйста!
[moder]Формулы нужно оформлять тегами!
Поправила.[/moder]

Автор - Рамиля
Дата добавления - 30.03.2016 в 08:19
китин Дата: Среда, 30.03.2016, 08:35 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3454
Репутация: 549 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
а такой вот монстр получился
Код
=ЕСЛИОШИБКА(--ПСТР(E5;ПОИСК("-";E5)+1;2)-(--ЛЕВБ(E5;ПОИСК("-";E5)-1));0)+ЕСЛИОШИБКА(--ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);"");ПОИСК("-";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);""))+1;2)-(--ЛЕВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);"");ПОИСК("-";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);""))-1));0)

[p.s.]а в этой конструкции :"8-15; 20-24" значок ; (точка с запятой) должен присутствовать обязательно. а то работать не будет
К сообщению приложен файл: 99162.xlsx(15Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538


Сообщение отредактировал китин - Среда, 30.03.2016, 08:37
 
Ответить
Сообщениеа такой вот монстр получился
Код
=ЕСЛИОШИБКА(--ПСТР(E5;ПОИСК("-";E5)+1;2)-(--ЛЕВБ(E5;ПОИСК("-";E5)-1));0)+ЕСЛИОШИБКА(--ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);"");ПОИСК("-";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);""))+1;2)-(--ЛЕВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);"");ПОИСК("-";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(E5);ЛЕВБ(СЖПРОБЕЛЫ(E5);ПОИСК(";";СЖПРОБЕЛЫ(E5))+1);""))-1));0)

[p.s.]а в этой конструкции :"8-15; 20-24" значок ; (точка с запятой) должен присутствовать обязательно. а то работать не будет

Автор - китин
Дата добавления - 30.03.2016 в 08:35
Nic70y Дата: Среда, 30.03.2016, 08:40 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Код
=ЕСЛИ(E5="";0;ПОДСТАВИТЬ(ПСТР(E5;ПОИСК("-";E5)+1;2);";";)-ПОДСТАВИТЬ(ЛЕВБ(E5;2);"-";)+ЕСЛИ(ЕЧИСЛО(ПОИСК(";";E5));ПОДСТАВИТЬ(ПРАВБ(E5;2);"-";)-ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;2);"-";);0))
в этой конструкции :"8-15; 20-24" значок ; (точка с запятой) должен присутствовать обязательно
аналогично
К сообщению приложен файл: 8259930.xls(44Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Код
=ЕСЛИ(E5="";0;ПОДСТАВИТЬ(ПСТР(E5;ПОИСК("-";E5)+1;2);";";)-ПОДСТАВИТЬ(ЛЕВБ(E5;2);"-";)+ЕСЛИ(ЕЧИСЛО(ПОИСК(";";E5));ПОДСТАВИТЬ(ПРАВБ(E5;2);"-";)-ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;2);"-";);0))
в этой конструкции :"8-15; 20-24" значок ; (точка с запятой) должен присутствовать обязательно
аналогично

Автор - Nic70y
Дата добавления - 30.03.2016 в 08:40
Рамиля Дата: Среда, 30.03.2016, 09:08 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Но в случае "7-14,42" (к примеру 7,7ч. рабочий день) формула не срабатывает...
 
Ответить
СообщениеНо в случае "7-14,42" (к примеру 7,7ч. рабочий день) формула не срабатывает...

Автор - Рамиля
Дата добавления - 30.03.2016 в 09:08
китин Дата: Среда, 30.03.2016, 09:37 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3454
Репутация: 549 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
формула не срабатывает...

и не сработает. мы делали в полном соответствии с вашим примером.а там "7-14,42" не было :p


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
формула не срабатывает...

и не сработает. мы делали в полном соответствии с вашим примером.а там "7-14,42" не было :p

Автор - китин
Дата добавления - 30.03.2016 в 09:37
Рамиля Дата: Среда, 30.03.2016, 10:53 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Вы правы! В примере я упустила данный интервал. Но, все же, может поможете?
 
Ответить
СообщениеВы правы! В примере я упустила данный интервал. Но, все же, может поможете?

Автор - Рамиля
Дата добавления - 30.03.2016 в 10:53
Рамиля Дата: Четверг, 31.03.2016, 19:04 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
На промежуток к примеру "7-14,42" и "12-15,12; 20-24" я, предложенные Вами формулы скорректировала, но на интервал в виде "0-7; 9-15,42" не могу осилить. Помогите, пожалуйста!!!!!!!
[moder]Приложите файл с полным набором "а еще у меня бывает ..."


Сообщение отредактировал _Boroda_ - Четверг, 31.03.2016, 19:09
 
Ответить
СообщениеНа промежуток к примеру "7-14,42" и "12-15,12; 20-24" я, предложенные Вами формулы скорректировала, но на интервал в виде "0-7; 9-15,42" не могу осилить. Помогите, пожалуйста!!!!!!!
[moder]Приложите файл с полным набором "а еще у меня бывает ..."

Автор - Рамиля
Дата добавления - 31.03.2016 в 19:04
Рамиля Дата: Пятница, 01.04.2016, 08:32 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
К предыдущему посту прилагаю пример с полным набором своих наработок, с которыми предполагается работа, связанная с табелем.
[moder]А переход через сутки возможен? например - 22-6
К сообщению приложен файл: 0095489.xls(54Kb)


Сообщение отредактировал _Boroda_ - Пятница, 01.04.2016, 09:47
 
Ответить
СообщениеК предыдущему посту прилагаю пример с полным набором своих наработок, с которыми предполагается работа, связанная с табелем.
[moder]А переход через сутки возможен? например - 22-6

Автор - Рамиля
Дата добавления - 01.04.2016 в 08:32
Nic70y Дата: Пятница, 01.04.2016, 09:37 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Код
=(ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(E5;ПОИСК(";";E5&";")-1);"-";"    ");5))&":0";",";":");",";":")-ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ЛЕВБ(E5;ПОИСК(";";E5&";")-1);"-";"    ");5))&":0";",";":");",";":")+ЕСЛИ(ЕЧИСЛО(ПОИСК(";";E5));ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;11);"-";"    ");5))&":0";",";":")-ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;11);"-";"    ");5))&":0";",";":");0))*24
К сообщению приложен файл: 1004500.xls(48Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Код
=(ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ЛЕВБ(E5;ПОИСК(";";E5&";")-1);"-";"    ");5))&":0";",";":");",";":")-ПОДСТАВИТЬ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ЛЕВБ(E5;ПОИСК(";";E5&";")-1);"-";"    ");5))&":0";",";":");",";":")+ЕСЛИ(ЕЧИСЛО(ПОИСК(";";E5));ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;11);"-";"    ");5))&":0";",";":")-ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(E5;ПОИСК(";";E5)+2;11);"-";"    ");5))&":0";",";":");0))*24

Автор - Nic70y
Дата добавления - 01.04.2016 в 09:37
Рамиля Дата: Пятница, 01.04.2016, 10:03 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
А переход через сутки возможен? например - 22-6
Нет, т.к. график расписывается на каждый день от нуля часов и до 24 часов.
 
Ответить
Сообщение
А переход через сутки возможен? например - 22-6
Нет, т.к. график расписывается на каждый день от нуля часов и до 24 часов.

Автор - Рамиля
Дата добавления - 01.04.2016 в 10:03
Nic70y Дата: Пятница, 01.04.2016, 10:19 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Нет
чем не устраивает пост №9?


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Нет
чем не устраивает пост №9?

Автор - Nic70y
Дата добавления - 01.04.2016 в 10:19
Рамиля Дата: Пятница, 01.04.2016, 10:40 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
чем не устраивает пост №9?
Устраивает. Но остался вопрос по ночным.
К сообщению приложен файл: 8527972.xls(52Kb)
 
Ответить
Сообщение
чем не устраивает пост №9?
Устраивает. Но остался вопрос по ночным.

Автор - Рамиля
Дата добавления - 01.04.2016 в 10:40
_Boroda_ Дата: Пятница, 01.04.2016, 14:51 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 9381
Репутация: 3951 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Предлагаю немного изменить. Рабочее время можно вот так
Код
=СУММПРОИЗВ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"0,00");",";":")*{-1:1:-1:1})*24

Предваряя вопросы, сразу краткая объяснялка
Приклеиваем справа к Е5 кусок ";0;0" и слева ";", в полученном меняем ";" на 33 пробела, от полученного 4 раза откусываем куски по 33 символа, начиная с 33, 66, 99 и 132 символов (получаем массив из 4-х элементов), двумя минусами преобразуем полученные числа с пробелами в обычные числа, с помощью ТЕКСТ преобразуем эти числа в формат двух знаков после запятой, в полученном меняем запятую на двоеточие (получаем время в формате ч:мм), у первого и третьего элементов меняем знак и всё это складываем.
Для ночных так
Код
=ЕСЛИ(СУММПРОИЗВ(ТЕКСТ(ОСТАТ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0,0001";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"[=0,0001]ч:м:с;0,00");",";":")+"2:";1);"[>0,33334]""8:00"";чч:мм:сс")*{-1:1:-1:1})<=0,000000001;8;)+СУММПРОИЗВ(ТЕКСТ(ОСТАТ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"0,00");",";":")+"2:";1);"[>0,33334]""8:00"";чч:мм")*{-1:1:-1:1})*24
Кусок с ЕСЛИ явно можно как-то засунуть в основной расчет, но что-то не соображаю уже.
К сообщению приложен файл: 0095489_1.xlsx(21Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПредлагаю немного изменить. Рабочее время можно вот так
Код
=СУММПРОИЗВ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"0,00");",";":")*{-1:1:-1:1})*24

Предваряя вопросы, сразу краткая объяснялка
Приклеиваем справа к Е5 кусок ";0;0" и слева ";", в полученном меняем ";" на 33 пробела, от полученного 4 раза откусываем куски по 33 символа, начиная с 33, 66, 99 и 132 символов (получаем массив из 4-х элементов), двумя минусами преобразуем полученные числа с пробелами в обычные числа, с помощью ТЕКСТ преобразуем эти числа в формат двух знаков после запятой, в полученном меняем запятую на двоеточие (получаем время в формате ч:мм), у первого и третьего элементов меняем знак и всё это складываем.
Для ночных так
Код
=ЕСЛИ(СУММПРОИЗВ(ТЕКСТ(ОСТАТ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0,0001";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"[=0,0001]ч:м:с;0,00");",";":")+"2:";1);"[>0,33334]""8:00"";чч:мм:сс")*{-1:1:-1:1})<=0,000000001;8;)+СУММПРОИЗВ(ТЕКСТ(ОСТАТ(ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(E5;"-";";")&";0;0";";";ПОВТОР(" ";33));{1:2:3:4}*33;33);"0,00");",";":")+"2:";1);"[>0,33334]""8:00"";чч:мм")*{-1:1:-1:1})*24
Кусок с ЕСЛИ явно можно как-то засунуть в основной расчет, но что-то не соображаю уже.

Автор - _Boroda_
Дата добавления - 01.04.2016 в 14:51
китин Дата: Пятница, 01.04.2016, 14:57 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3454
Репутация: 549 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
[offtop]блин сам себе проспорил бутылку коньяка :'( придется вечерком выпить.


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение[offtop]блин сам себе проспорил бутылку коньяка :'( придется вечерком выпить.

Автор - китин
Дата добавления - 01.04.2016 в 14:57
Рамиля Дата: Понедельник, 04.04.2016, 08:18 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо Всем за помощь! Все предложенные Вами формулы интересны! Буду пользоваться ими обязательно!
 
Ответить
СообщениеСпасибо Всем за помощь! Все предложенные Вами формулы интересны! Буду пользоваться ими обязательно!

Автор - Рамиля
Дата добавления - 04.04.2016 в 08:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчета отработанных часов по графику (Формулы/Formulas)
Страница 1 из 11
Поиск:

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