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

Вход

Регистрация

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

 

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

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

Excel 2010
Здравствуйте, мастера Excel-я!
Прошу Вашей помощи в создании формулы для решения следующей задачи.
В раздельных ячейках с точность до минут фиксируются дата и время начала и окончания рабочего процесса.
На отдельном листе (Справочник) с точность до минут указываются:
- время начала и окончания рабочего дня, время начала и окончания обеденного перерыва
- список праздничных и нерабочих дней (определяются ежегодно Постановлением Правительства РФ)
- список выходных дней, объявленных рабочими (определяются ежегодно Постановлением Правительства РФ)
- список дат, когда работа выполнялась в обычные выходные или праздничные дни.
Справочник ведется вручную.
Требуется подсчитать количество рабочих часов и минут от начала до окончания рабочего процесса, при этом не должны считаться все календарные выходные, праздничные и нерабочие дни, а выходные дни, объявленные рабочими должны включаться в подсчет рабочих часов. Отдельно буду благодарен за указание в дополнительно столбце перевода подсчитанного количества рабочих часов в формат "00дн. 00ч. 00м."
Файл с исходными данными прилагаю.
Форум курил, но решения аналогичной задачи не обнаружил. Формулы ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД не помогают, а в составлении серьезных формул не преуспел.
Заранее благодарю за снисходительность и поддержку!
К сообщению приложен файл: 7060981.xlsx(15Kb)
 
Ответить
СообщениеЗдравствуйте, мастера Excel-я!
Прошу Вашей помощи в создании формулы для решения следующей задачи.
В раздельных ячейках с точность до минут фиксируются дата и время начала и окончания рабочего процесса.
На отдельном листе (Справочник) с точность до минут указываются:
- время начала и окончания рабочего дня, время начала и окончания обеденного перерыва
- список праздничных и нерабочих дней (определяются ежегодно Постановлением Правительства РФ)
- список выходных дней, объявленных рабочими (определяются ежегодно Постановлением Правительства РФ)
- список дат, когда работа выполнялась в обычные выходные или праздничные дни.
Справочник ведется вручную.
Требуется подсчитать количество рабочих часов и минут от начала до окончания рабочего процесса, при этом не должны считаться все календарные выходные, праздничные и нерабочие дни, а выходные дни, объявленные рабочими должны включаться в подсчет рабочих часов. Отдельно буду благодарен за указание в дополнительно столбце перевода подсчитанного количества рабочих часов в формат "00дн. 00ч. 00м."
Файл с исходными данными прилагаю.
Форум курил, но решения аналогичной задачи не обнаружил. Формулы ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД не помогают, а в составлении серьезных формул не преуспел.
Заранее благодарю за снисходительность и поддержку!

Автор - Delta4
Дата добавления - 26.02.2016 в 17:30
Pelena Дата: Пятница, 26.02.2016, 19:14 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9879
Репутация: 2265 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Была похожая тема с замечательной формулой от MCH

Если её немного дополнить рабочими праздничными днями, то получится, что Вы неверно рассчитали ответ в Вашем файле примере ;)
Код
=(ДЕНЬНЕД(A2;2)<6)*(Справочник!$G$2-МАКС(МИН(B2;Справочник!$G$2);Справочник!$F$2)+Справочник!$I$2-МАКС(МИН(B2;Справочник!$I$2);Справочник!$H$2))+(ДЕНЬНЕД(C2;2)<6)*(МАКС(МИН(D2;Справочник!$G$2);Справочник!$F$2)-Справочник!$F$2+МАКС(МИН(D2;Справочник!$I$2);Справочник!$H$2)-Справочник!$H$2)+(ЧИСТРАБДНИ(РАБДЕНЬ(A2+1;-1;Справочник!$B$2:$B$19);РАБДЕНЬ(C2-1;1;Справочник!$B$2:$B$19);Справочник!$B$2:$B$19)-2+СУММПРОИЗВ((Справочник!$C$2:$D$19>=A2+1)*(Справочник!$C$2:$D$19<=C2-1)))*Справочник!$J$2
К сообщению приложен файл: 3717650.xlsx(16Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Была похожая тема с замечательной формулой от MCH

Если её немного дополнить рабочими праздничными днями, то получится, что Вы неверно рассчитали ответ в Вашем файле примере ;)
Код
=(ДЕНЬНЕД(A2;2)<6)*(Справочник!$G$2-МАКС(МИН(B2;Справочник!$G$2);Справочник!$F$2)+Справочник!$I$2-МАКС(МИН(B2;Справочник!$I$2);Справочник!$H$2))+(ДЕНЬНЕД(C2;2)<6)*(МАКС(МИН(D2;Справочник!$G$2);Справочник!$F$2)-Справочник!$F$2+МАКС(МИН(D2;Справочник!$I$2);Справочник!$H$2)-Справочник!$H$2)+(ЧИСТРАБДНИ(РАБДЕНЬ(A2+1;-1;Справочник!$B$2:$B$19);РАБДЕНЬ(C2-1;1;Справочник!$B$2:$B$19);Справочник!$B$2:$B$19)-2+СУММПРОИЗВ((Справочник!$C$2:$D$19>=A2+1)*(Справочник!$C$2:$D$19<=C2-1)))*Справочник!$J$2

Автор - Pelena
Дата добавления - 26.02.2016 в 19:14
_Boroda_ Дата: Пятница, 26.02.2016, 19:37 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А у меня немного не так получилось
Код
=(ЧИСТРАБДНИ.МЕЖД(A2;C2;1;Справочник!B$2:B$21)+СЧЁТЕСЛИМН(Справочник!C$2:C$21;">="&A2;Справочник!C$2:C$21;"<="&C2)+СЧЁТЕСЛИМН(Справочник!D$2:D$21;">="&A2;Справочник!D$2:D$21;"<="&C2)-2)*(Справочник!I$2-Справочник!F$2-Справочник!H$2+Справочник!G$2)+(Справочник!I$2-B2-МАКС(B2;Справочник!H$2)+МАКС(B2;Справочник!G$2))+(D2-Справочник!F$2-МИН(D2;Справочник!H$2)+МИН(D2;Справочник!G$2))


Проверить нужно. Ща.

Да, начальные дни я дважды посчитал
В формуле 2 вычел, файл перевложил. Ответы одинаковые, но формулы разные.
К сообщению приложен файл: 7060981_2.xlsx(16Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Пятница, 26.02.2016, 19:47
 
Ответить
СообщениеА у меня немного не так получилось
Код
=(ЧИСТРАБДНИ.МЕЖД(A2;C2;1;Справочник!B$2:B$21)+СЧЁТЕСЛИМН(Справочник!C$2:C$21;">="&A2;Справочник!C$2:C$21;"<="&C2)+СЧЁТЕСЛИМН(Справочник!D$2:D$21;">="&A2;Справочник!D$2:D$21;"<="&C2)-2)*(Справочник!I$2-Справочник!F$2-Справочник!H$2+Справочник!G$2)+(Справочник!I$2-B2-МАКС(B2;Справочник!H$2)+МАКС(B2;Справочник!G$2))+(D2-Справочник!F$2-МИН(D2;Справочник!H$2)+МИН(D2;Справочник!G$2))


Проверить нужно. Ща.

Да, начальные дни я дважды посчитал
В формуле 2 вычел, файл перевложил. Ответы одинаковые, но формулы разные.

Автор - _Boroda_
Дата добавления - 26.02.2016 в 19:37
Delta4 Дата: Пятница, 26.02.2016, 22:09 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за отклик!
Я пытался использовать формулу от МСН, но нужного результата не добился.
Надеюсь, что Ваш вариант мне поможет.
Но Ваши файлы не открываются. При запуске Excel сообщает, что "Файл поврежден, поэтому его нельзя открыть.
Где может быть засада?
 
Ответить
СообщениеСпасибо за отклик!
Я пытался использовать формулу от МСН, но нужного результата не добился.
Надеюсь, что Ваш вариант мне поможет.
Но Ваши файлы не открываются. При запуске Excel сообщает, что "Файл поврежден, поэтому его нельзя открыть.
Где может быть засада?

Автор - Delta4
Дата добавления - 26.02.2016 в 22:09
_Boroda_ Дата: Пятница, 26.02.2016, 22:16 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если не открываются оба файла (и мой, и Ленин), то засада у Вас на компе. Ну и ладно, просто скопируйте мою формулу из сообщения выше и вставьте ее к себе в ячейку Е2.
В F2 просто ссылка на Е2 (формула =E2 ) и формат ДД\д чч\ч мм\м
А в Е2 формат [чч]\ч мм\м

Попробуйте еще вот этот файл. Там хоть на функцию и ругается, но в 2010 Excel ничего страшного. Если откроется, то пересохраните файл в xlsx
К сообщению приложен файл: 7060981_222.xls(44Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли не открываются оба файла (и мой, и Ленин), то засада у Вас на компе. Ну и ладно, просто скопируйте мою формулу из сообщения выше и вставьте ее к себе в ячейку Е2.
В F2 просто ссылка на Е2 (формула =E2 ) и формат ДД\д чч\ч мм\м
А в Е2 формат [чч]\ч мм\м

Попробуйте еще вот этот файл. Там хоть на функцию и ругается, но в 2010 Excel ничего страшного. Если откроется, то пересохраните файл в xlsx

Автор - _Boroda_
Дата добавления - 26.02.2016 в 22:16
Pelena Дата: Пятница, 26.02.2016, 22:32 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9879
Репутация: 2265 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Вложила формулу в предыдущий пост.
В ячейке Справочник!$J$2 продолжительность рабочего дня, т.е. в Вашем случае 8:00
А для дней соответственно
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2

и формат ДД чч:мм
Исправила формулу


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВложила формулу в предыдущий пост.
В ячейке Справочник!$J$2 продолжительность рабочего дня, т.е. в Вашем случае 8:00
А для дней соответственно
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2

и формат ДД чч:мм
Исправила формулу

Автор - Pelena
Дата добавления - 26.02.2016 в 22:32
Delta4 Дата: Пятница, 26.02.2016, 22:58 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Елена и Борода (прошу прощения за никнейм), спасибо за помощь!
Формулу вставил, форматы установил. Но результат получается неверный. На моих данных (в файле) длительность от 12:00 18.02.2016 до 13:00 24.02.2016 (20.02 рабочий день, без работы в праздник 23-го) должна составить:
18.02 = 5 ч.
19.02 = 8 ч.
20.02 = 8 ч.
24.02 = 5 ч.
ИТОГО: 26 рабочих часов (в ячейке Е2) или 03д 02ч 00м (в ячейке F2)
А по формуле получилось 24 часа или 1 день.
Однако, если время начала и окончания процесса совпадает с началом и окончанием рабочего дня по Справочнику, то расчет получается верным (см. строка 3)
На всякий случай прикладываю файл в 97-2003.
Посмотрите, пожалуйста.
К сообщению приложен файл: _-97-2003-.xls(35Kb)
 
Ответить
СообщениеЕлена и Борода (прошу прощения за никнейм), спасибо за помощь!
Формулу вставил, форматы установил. Но результат получается неверный. На моих данных (в файле) длительность от 12:00 18.02.2016 до 13:00 24.02.2016 (20.02 рабочий день, без работы в праздник 23-го) должна составить:
18.02 = 5 ч.
19.02 = 8 ч.
20.02 = 8 ч.
24.02 = 5 ч.
ИТОГО: 26 рабочих часов (в ячейке Е2) или 03д 02ч 00м (в ячейке F2)
А по формуле получилось 24 часа или 1 день.
Однако, если время начала и окончания процесса совпадает с началом и окончанием рабочего дня по Справочнику, то расчет получается верным (см. строка 3)
На всякий случай прикладываю файл в 97-2003.
Посмотрите, пожалуйста.

Автор - Delta4
Дата добавления - 26.02.2016 в 22:58
Delta4 Дата: Пятница, 26.02.2016, 23:00 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
ИТОГО: 26 рабочих часов (в ячейке Е2) или 03д 02ч 00м (в ячейке F2).
Выделил шрифтом отклонение...


Сообщение отредактировал Delta4 - Пятница, 26.02.2016, 23:01
 
Ответить
СообщениеИТОГО: 26 рабочих часов (в ячейке Е2) или 03д 02ч 00м (в ячейке F2).
Выделил шрифтом отклонение...

Автор - Delta4
Дата добавления - 26.02.2016 в 23:00
Pelena Дата: Пятница, 26.02.2016, 23:03 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 9879
Репутация: 2265 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Почему 24-го 5 часов? с 9:00 до 12:00 всего 3 часа
Почему не учитывается 23 февраля, если в соответствии со Справочником в этот день работы велись? В новом примере этого нет


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПочему 24-го 5 часов? с 9:00 до 12:00 всего 3 часа
Почему не учитывается 23 февраля, если в соответствии со Справочником в этот день работы велись? В новом примере этого нет

Автор - Pelena
Дата добавления - 26.02.2016 в 23:03
_Boroda_ Дата: Пятница, 26.02.2016, 23:22 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 9377
Репутация: 3950 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Да-да, у Вас в старом файле была работа в вых и праздн дни 05.01.2016 и 23.02.2016. Только что сообразил, у Вас же в последнем столбце в рабочих днях, то есть 8 часов = день
Тогда формула
Код
=ОТБР(E3*3)+E3-ОТБР(E3*3)/3

в смысле, вот так
Код
=ОТБР(E2*24/Справочник!J$2)+E2-ОТБР(E2*24/Справочник!J$2)/24*Справочник!J$2

24 лишнее
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2

А основная формула укоротилась за счет расчета длительности раб дня не другом листе
Код
=(ЧИСТРАБДНИ.МЕЖД(A2;C2;1;Справочник!B$2:B$21)+СЧЁТЕСЛИМН(Справочник!C$2:C$21;">="&A2;Справочник!C$2:C$21;"<="&C2)+СЧЁТЕСЛИМН(Справочник!D$2:D$21;">="&A2;Справочник!D$2:D$21;"<="&C2)-2)*Справочник!J$2+(Справочник!I$2-B2-МАКС(B2;Справочник!H$2)+МАКС(B2;Справочник!G$2))+(D2-Справочник!F$2-МИН(D2;Справочник!H$2)+МИН(D2;Справочник!G$2))
К сообщению приложен файл: -97-2003-1.xls(36Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа-да, у Вас в старом файле была работа в вых и праздн дни 05.01.2016 и 23.02.2016. Только что сообразил, у Вас же в последнем столбце в рабочих днях, то есть 8 часов = день
Тогда формула
Код
=ОТБР(E3*3)+E3-ОТБР(E3*3)/3

в смысле, вот так
Код
=ОТБР(E2*24/Справочник!J$2)+E2-ОТБР(E2*24/Справочник!J$2)/24*Справочник!J$2

24 лишнее
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2

А основная формула укоротилась за счет расчета длительности раб дня не другом листе
Код
=(ЧИСТРАБДНИ.МЕЖД(A2;C2;1;Справочник!B$2:B$21)+СЧЁТЕСЛИМН(Справочник!C$2:C$21;">="&A2;Справочник!C$2:C$21;"<="&C2)+СЧЁТЕСЛИМН(Справочник!D$2:D$21;">="&A2;Справочник!D$2:D$21;"<="&C2)-2)*Справочник!J$2+(Справочник!I$2-B2-МАКС(B2;Справочник!H$2)+МАКС(B2;Справочник!G$2))+(D2-Справочник!F$2-МИН(D2;Справочник!H$2)+МИН(D2;Справочник!G$2))

Автор - _Boroda_
Дата добавления - 26.02.2016 в 23:22
Delta4 Дата: Пятница, 26.02.2016, 23:30 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Елена, простите меня за невнимательность!
Я, олух, посчитал рабочие часы 24-го с 13 до 18, а надо было с 9 до 12...
 
Ответить
СообщениеЕлена, простите меня за невнимательность!
Я, олух, посчитал рабочие часы 24-го с 13 до 18, а надо было с 9 до 12...

Автор - Delta4
Дата добавления - 26.02.2016 в 23:30
Delta4 Дата: Пятница, 26.02.2016, 23:41 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Вам, Мастера!
Еще одна непонятка осталась.
Строка 4 в приложенном файле. 53 часа равны 06д 05ч 00м.
А формула посчитала 06д 15ч 00м
Непонятно, где ошибка в пересчете...
К сообщению приложен файл: 1792046.xls(35Kb)
 
Ответить
СообщениеСпасибо Вам, Мастера!
Еще одна непонятка осталась.
Строка 4 в приложенном файле. 53 часа равны 06д 05ч 00м.
А формула посчитала 06д 15ч 00м
Непонятно, где ошибка в пересчете...

Автор - Delta4
Дата добавления - 26.02.2016 в 23:41
Pelena Дата: Пятница, 26.02.2016, 23:49 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 9879
Репутация: 2265 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Для кол-ва дней так попробуйте (моя прежняя формула была неверна)
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеДля кол-ва дней так попробуйте (моя прежняя формула была неверна)
Код
=ОТБР(E2/Справочник!J$2)+E2-ОТБР(E2/Справочник!J$2)*Справочник!J$2

Автор - Pelena
Дата добавления - 26.02.2016 в 23:49
Delta4 Дата: Пятница, 26.02.2016, 23:53 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Елена, все получилось!
 
Ответить
СообщениеЕлена, все получилось!

Автор - Delta4
Дата добавления - 26.02.2016 в 23:53
Delta4 Дата: Пятница, 26.02.2016, 23:54 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Александр, Елена, огромная Вам благодарность и плюс в репу!
 
Ответить
СообщениеАлександр, Елена, огромная Вам благодарность и плюс в репу!

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

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