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

Вход

Регистрация

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

 

= Мир MS Excel/как учесть сверхурочные часы согласно нормативу - Мир MS Excel

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

Excel 2007
Добрый день Боги екселя. Буду очень благодарна в решении моей проблемы. Прошу подскажите как найти решение в следующем. Допускается переработка часов в день только 2 часа, итого за месяц не должно превышать 12 часов, т.е если у кого имеется сверхурочные часы свыше 8 часов, то можно ставить 9, 10 часов, но не выше соответственно у кого отработано 9 часов, то 9. Задача состоит в том, как суммировать эти 1 или 2 часа к 8 часам с учетом данных окрашенных в синий цвет на первичном листе в имеющуюся уже формулу. Написал формулу подсчета сверхурочных часов за каждый день, не выше 2 часов по фактически отработанному времени, но как использовать ее именно по тем дням синим цветом в имеющейся формуле(+условия УФ) не могу.
Код
=ЕСЛИ(СУММ(ЕСЛИ(ЕЧИСЛО(первичный!D8);ЕСЛИ(первичный!D8>8;первичный!D8-8;)))>=2;10;первичный!D8)
применить в формуле
Код
=ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=7;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(ДЕНЬНЕД(D$10)=1;СУММПРОИЗВ(Ч(D$10=праздники))>0);"В";МИН(--'исходный '!D8;7;ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=8;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(И(ДЕНЬНЕД(D$10;2)>5;СУММПРОИЗВ(Ч(D$10=Перенос_С))=0);СУММПРОИЗВ((D$10=праздники)+(D$10=Перенос_На))>0);"В";МИН(--'исходный '!D8;8))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)="сменный";ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ('исходный '!D8=1;"";МИН('исходный '!D8;ОКРУГЛ('исходный '!D8*(ИНДЕКС('норма часов'!$A$12:$K$31;ПОИСКПОЗ(ТЕКСТ((финальный!$M$10);"ММММ");'норма часов'!$A$12:$A$31;0);6))/('исходный '!$AR8-СУММЕСЛИ('исходный '!$D8:$AH8;1));2))));"")))

Однако, на первичном листе можно поменять вариант отметки дней с сверхурочным нормативом, т.е эти дни отметить каким то другим образом приемлемым в решении задачи. Например не цветом, а как то по другому, чтобы помогло в преобразовании формулы. Очень прошу не оставьте без внимания мою тему и окажите мне помощь. Возможны ли и другие варианты решения данной задачи.
К сообщению приложен файл: 10102018.xlsx(84.9 Kb)


Сообщение отредактировал Coraline - Четверг, 11.10.2018, 08:42
 
Ответить
СообщениеДобрый день Боги екселя. Буду очень благодарна в решении моей проблемы. Прошу подскажите как найти решение в следующем. Допускается переработка часов в день только 2 часа, итого за месяц не должно превышать 12 часов, т.е если у кого имеется сверхурочные часы свыше 8 часов, то можно ставить 9, 10 часов, но не выше соответственно у кого отработано 9 часов, то 9. Задача состоит в том, как суммировать эти 1 или 2 часа к 8 часам с учетом данных окрашенных в синий цвет на первичном листе в имеющуюся уже формулу. Написал формулу подсчета сверхурочных часов за каждый день, не выше 2 часов по фактически отработанному времени, но как использовать ее именно по тем дням синим цветом в имеющейся формуле(+условия УФ) не могу.
Код
=ЕСЛИ(СУММ(ЕСЛИ(ЕЧИСЛО(первичный!D8);ЕСЛИ(первичный!D8>8;первичный!D8-8;)))>=2;10;первичный!D8)
применить в формуле
Код
=ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=7;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(ДЕНЬНЕД(D$10)=1;СУММПРОИЗВ(Ч(D$10=праздники))>0);"В";МИН(--'исходный '!D8;7;ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=8;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(И(ДЕНЬНЕД(D$10;2)>5;СУММПРОИЗВ(Ч(D$10=Перенос_С))=0);СУММПРОИЗВ((D$10=праздники)+(D$10=Перенос_На))>0);"В";МИН(--'исходный '!D8;8))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)="сменный";ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ('исходный '!D8=1;"";МИН('исходный '!D8;ОКРУГЛ('исходный '!D8*(ИНДЕКС('норма часов'!$A$12:$K$31;ПОИСКПОЗ(ТЕКСТ((финальный!$M$10);"ММММ");'норма часов'!$A$12:$A$31;0);6))/('исходный '!$AR8-СУММЕСЛИ('исходный '!$D8:$AH8;1));2))));"")))

Однако, на первичном листе можно поменять вариант отметки дней с сверхурочным нормативом, т.е эти дни отметить каким то другим образом приемлемым в решении задачи. Например не цветом, а как то по другому, чтобы помогло в преобразовании формулы. Очень прошу не оставьте без внимания мою тему и окажите мне помощь. Возможны ли и другие варианты решения данной задачи.

Автор - Coraline
Дата добавления - 11.10.2018 в 08:41
Coraline Дата: Четверг, 11.10.2018, 12:58 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Pelena, добрый день. Но я не знаю почему все меня стали обходить стороной. Даже не читают и не заходят, наверно я так надоела всем. Вы сможете мне помочь в очередной раз разобраться в проблеме. Очень прошу помогите мне, иначе мне "копец" будет. Я надеюсь на вашу отзывчивость и добродетельность. :help:
 
Ответить
СообщениеPelena, добрый день. Но я не знаю почему все меня стали обходить стороной. Даже не читают и не заходят, наверно я так надоела всем. Вы сможете мне помочь в очередной раз разобраться в проблеме. Очень прошу помогите мне, иначе мне "копец" будет. Я надеюсь на вашу отзывчивость и добродетельность. :help:

Автор - Coraline
Дата добавления - 11.10.2018 в 12:58
_Boroda_ Дата: Четверг, 11.10.2018, 13:11 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13698
Репутация: 5585 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У Вас в формуле как-то странно написано - норма часов Иванова считается по пятидневке, у Титовой по шестидневке, у остальных вообще вместо нормы часов считается норма дней (все это по 40-час. неделе)
Как нужно-то по-нормальному? Если режим работы 7 или 8, то в принципе неважно, а вот по какому столбцу таблицы листа "норма часов" считать норму часов для сменного режима работы?

Да, и еще - что делать, когда закончится месячная норма 12 переработанных часов? Например, Сидоров с 3.09 по 10.09 (6 раб. дней) перераработалэти 12 часов. А 11.09 снова переработал, но лимит 12 часов уже исчерпан. Что ему пишем 11.09 - 10 или 8 часов?

* Ой, простите, я не Pelena. Мне не отвечать? :D


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ Вас в формуле как-то странно написано - норма часов Иванова считается по пятидневке, у Титовой по шестидневке, у остальных вообще вместо нормы часов считается норма дней (все это по 40-час. неделе)
Как нужно-то по-нормальному? Если режим работы 7 или 8, то в принципе неважно, а вот по какому столбцу таблицы листа "норма часов" считать норму часов для сменного режима работы?

Да, и еще - что делать, когда закончится месячная норма 12 переработанных часов? Например, Сидоров с 3.09 по 10.09 (6 раб. дней) перераработалэти 12 часов. А 11.09 снова переработал, но лимит 12 часов уже исчерпан. Что ему пишем 11.09 - 10 или 8 часов?

* Ой, простите, я не Pelena. Мне не отвечать? :D

Автор - _Boroda_
Дата добавления - 11.10.2018 в 13:11
Coraline Дата: Четверг, 11.10.2018, 13:28 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Boroda , ничего страшного, это очень здорово, что Вы хоть один не проигнорировали меня. Я поняла, сменный режим можно во внимание не брать у них будет по факту, а вот для 7 и 8 часов нужно. Еще, одна загвозка, сейчас по подробнее.При автоматизации учета рабочего времени, где уже табеля с фактически отработанному времени (первичный лист) обрабатываются с помощью формул и применением УФ и конечный результат выводится в нормированном виде (финальный лист) возникла задача следующего характера: нужно учесть допустимую переработку при сверхурочных работах) в размере 2 часов, при этом общая продолжительность сверхурочных работ не должна превышать 12 часов в месяц. Первичный лист заполняется руководителями подразделений, сверхурочные работы имеют разный размер переработанных часов по дням. Необходимо произвести расчет рабочего времени с условием переработки часов не более 2 часов в день и только по тем дням где должны указать руководители. Здесь возникает вопрос каким образом руководители подразделений должны отметить те дни переработки, где будут учитываться эти 2 часа, чтобы формула считала их. Вначале их отметила синим цветом, однако формула не сможет произвести расчет, а макрос применять нежелательно. На финальном листе стоит, формула которая преобразует факт.отработанное время согласно нормам времени, учитывает праздничные и выходные, переносы дней выходных при наличии таковых, учитывает дни не выхода на работу по приказам, а также режим работы ( 8 часовой при пятидневки и 7 часовой при шестидневки) Пож-та помогите мне разобраться.
К сообщению приложен файл: 1295532.xlsx(84.8 Kb)


Сообщение отредактировал Coraline - Четверг, 11.10.2018, 13:44
 
Ответить
СообщениеBoroda , ничего страшного, это очень здорово, что Вы хоть один не проигнорировали меня. Я поняла, сменный режим можно во внимание не брать у них будет по факту, а вот для 7 и 8 часов нужно. Еще, одна загвозка, сейчас по подробнее.При автоматизации учета рабочего времени, где уже табеля с фактически отработанному времени (первичный лист) обрабатываются с помощью формул и применением УФ и конечный результат выводится в нормированном виде (финальный лист) возникла задача следующего характера: нужно учесть допустимую переработку при сверхурочных работах) в размере 2 часов, при этом общая продолжительность сверхурочных работ не должна превышать 12 часов в месяц. Первичный лист заполняется руководителями подразделений, сверхурочные работы имеют разный размер переработанных часов по дням. Необходимо произвести расчет рабочего времени с условием переработки часов не более 2 часов в день и только по тем дням где должны указать руководители. Здесь возникает вопрос каким образом руководители подразделений должны отметить те дни переработки, где будут учитываться эти 2 часа, чтобы формула считала их. Вначале их отметила синим цветом, однако формула не сможет произвести расчет, а макрос применять нежелательно. На финальном листе стоит, формула которая преобразует факт.отработанное время согласно нормам времени, учитывает праздничные и выходные, переносы дней выходных при наличии таковых, учитывает дни не выхода на работу по приказам, а также режим работы ( 8 часовой при пятидневки и 7 часовой при шестидневки) Пож-та помогите мне разобраться.

Автор - Coraline
Дата добавления - 11.10.2018 в 13:28
_Boroda_ Дата: Четверг, 11.10.2018, 13:46 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13698
Репутация: 5585 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
сменный режим можно во внимание не брать
Это для переработок, а для расчета часов важно откуда брать данные сменных товарищей
В остальном Вы еще больше запутали
Короче, держите. Логику формулы не менял, сделал так, что первые по порядку переработки считаются (не более 2 часов в сутки) до тех пор, пока в сумме не наберется 12 часов. Потом переработки просто игнорируются

Сменные считаются по шестидневкам (столбец G листа норма часов)

Формулу здесь не привожу - она страшная :D
К сообщению приложен файл: 10102018_2.xlsx(91.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
сменный режим можно во внимание не брать
Это для переработок, а для расчета часов важно откуда брать данные сменных товарищей
В остальном Вы еще больше запутали
Короче, держите. Логику формулы не менял, сделал так, что первые по порядку переработки считаются (не более 2 часов в сутки) до тех пор, пока в сумме не наберется 12 часов. Потом переработки просто игнорируются

Сменные считаются по шестидневкам (столбец G листа норма часов)

Формулу здесь не привожу - она страшная :D

Автор - _Boroda_
Дата добавления - 11.10.2018 в 13:46
Coraline Дата: Четверг, 11.10.2018, 14:09 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Boroda, просто супер, хорошо, что есть умы у кого можно поучиться. А Вы не подскажите, как можно сделать, чтобы переработка выбиралась по какому то признаку из первички, а не первые по порядку. но вы поняли. Не предложите какой нибудь вариант
 
Ответить
СообщениеBoroda, просто супер, хорошо, что есть умы у кого можно поучиться. А Вы не подскажите, как можно сделать, чтобы переработка выбиралась по какому то признаку из первички, а не первые по порядку. но вы поняли. Не предложите какой нибудь вариант

Автор - Coraline
Дата добавления - 11.10.2018 в 14:09
Coraline Дата: Четверг, 11.10.2018, 14:46 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Boroda, прошу прощения, а возможно формулу откорректировать, если руководители будут ставить знак плюс в ячейке после числа вместо заливки. Например, было в ячейке:
11
отметят и стало:
11+
Формулами можем обработать знак "+" и число.

или делать целевые числа отрицательными (число не портится, т.е. не становится текстом)

-11
К сообщению приложен файл: _10102018_4.xlsx(88.8 Kb)


Сообщение отредактировал Coraline - Четверг, 11.10.2018, 15:04
 
Ответить
СообщениеBoroda, прошу прощения, а возможно формулу откорректировать, если руководители будут ставить знак плюс в ячейке после числа вместо заливки. Например, было в ячейке:
11
отметят и стало:
11+
Формулами можем обработать знак "+" и число.

или делать целевые числа отрицательными (число не портится, т.е. не становится текстом)

-11

Автор - Coraline
Дата добавления - 11.10.2018 в 14:46
Coraline Дата: Пятница, 12.10.2018, 07:22 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Доброе утро. Пож-та не покидайте меня. Подскажите как можно так сделать? :help:


Сообщение отредактировал Coraline - Пятница, 12.10.2018, 13:09
 
Ответить
СообщениеДоброе утро. Пож-та не покидайте меня. Подскажите как можно так сделать? :help:

Автор - Coraline
Дата добавления - 12.10.2018 в 07:22
Coraline Дата: Пятница, 12.10.2018, 14:13 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
у кого нибудь найдется времечко обратить на мое сообщение внимание - pliz
 
Ответить
Сообщениеу кого нибудь найдется времечко обратить на мое сообщение внимание - pliz

Автор - Coraline
Дата добавления - 12.10.2018 в 14:13
_Boroda_ Дата: Пятница, 12.10.2018, 14:31 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13698
Репутация: 5585 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Проверяйте
Код
=ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)="сменный";ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ('исходный '!D8=1;"";МИН('исходный '!D8;ОКРУГЛ('исходный '!D8*(ИНДЕКС('норма часов'!$A$12:$K$31;ПОИСКПОЗ(ТЕКСТ($M$10;"ММММ");'норма часов'!$A$12:$A$31;0);7))/('исходный '!$AR8-СУММЕСЛИ('исходный '!$D8:$AH8;1));2))));ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=7;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(ДЕНЬНЕД(D$10)=1;СУММПРОИЗВ(Ч(D$10=праздники))>0);"В";МИН(ABS('исходный '!D8);ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)+МИН(2*('исходный '!D8<0);МАКС(;12-СУММЕСЛИ($C11:C11;">"&8)+СЧЁТЕСЛИ($C11:C11;">"&8)*ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)))))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=8;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(И(ДЕНЬНЕД(D$10;2)>5;СУММПРОИЗВ(Ч(D$10=Перенос_С))=0);СУММПРОИЗВ((D$10=праздники)+(D$10=Перенос_На))>0);"В";МИН(ABS('исходный '!D8);8+МИН(2*('исходный '!D8<0);МАКС(;12-СУММЕСЛИ($C11:C11;">"&8)+СЧЁТЕСЛИ($C11:C11;">"&8)*8))))););"")))
К сообщению приложен файл: _10102018_5.xlsx(91.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПроверяйте
Код
=ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)="сменный";ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ('исходный '!D8=1;"";МИН('исходный '!D8;ОКРУГЛ('исходный '!D8*(ИНДЕКС('норма часов'!$A$12:$K$31;ПОИСКПОЗ(ТЕКСТ($M$10;"ММММ");'норма часов'!$A$12:$A$31;0);7))/('исходный '!$AR8-СУММЕСЛИ('исходный '!$D8:$AH8;1));2))));ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=7;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(ДЕНЬНЕД(D$10)=1;СУММПРОИЗВ(Ч(D$10=праздники))>0);"В";МИН(ABS('исходный '!D8);ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)+МИН(2*('исходный '!D8<0);МАКС(;12-СУММЕСЛИ($C11:C11;">"&8)+СЧЁТЕСЛИ($C11:C11;">"&8)*ЕСЛИ(ДЕНЬНЕД(D$9)=7;5;7)))))););ЕСЛИ(ВПР($B11;приказы!$B$11:$C$49;2;0)=8;ЕСЛИОШИБКА(ЕСЛИ(ЕОШ(--'исходный '!D8);'исходный '!D8;ЕСЛИ(ИЛИ(И(ДЕНЬНЕД(D$10;2)>5;СУММПРОИЗВ(Ч(D$10=Перенос_С))=0);СУММПРОИЗВ((D$10=праздники)+(D$10=Перенос_На))>0);"В";МИН(ABS('исходный '!D8);8+МИН(2*('исходный '!D8<0);МАКС(;12-СУММЕСЛИ($C11:C11;">"&8)+СЧЁТЕСЛИ($C11:C11;">"&8)*8))))););"")))

Автор - _Boroda_
Дата добавления - 12.10.2018 в 14:31
Coraline Дата: Пятница, 12.10.2018, 14:50 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 94
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Отлично, я восхищаюсь вашим умом. Мне конечно очень далеко до этого. Но я буду стараться. Нет слов похвалы и восторга. Спасибо Вам,, пусть на вашем жизненном пути встречаются тоже такие добрые и гениальные люди как вы сами.
 
Ответить
СообщениеОтлично, я восхищаюсь вашим умом. Мне конечно очень далеко до этого. Но я буду стараться. Нет слов похвалы и восторга. Спасибо Вам,, пусть на вашем жизненном пути встречаются тоже такие добрые и гениальные люди как вы сами.

Автор - Coraline
Дата добавления - 12.10.2018 в 14:50
Мир MS Excel » Вопросы и решения » Вопросы по Excel » как учесть сверхурочные часы согласно нормативу (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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