Сотрудник может начать работать в любое время с 9:00 до 9:00. Может работать с 9 до 18, может работать с 1 до 2 и т.д.
Условные минуты: D9/кол-во часов в C9.
Виды переработок:
1. с 18:00 до 00:00: переработанные часы * усл. минуты * 4,05;
2. с 00:00 до 03:00: переработанные часы * усл. минуты * 1,7 * 4,05;
3. после 03:00: переработанные часы* усл. минуты * 2,5 * 4,05.
Во времени могут быть минуты, а не ровные часы.
Формулу нужно сделать в E9. Я хочу сделать все одной формулой.
В C9 общее время, а не только переработанное.
D9 нужно делить на общее время, а не только переработанное.
Помогите пожалуйста.
[вложение удалено администратором]
Новая версия, в прежней D9 делилось на переработанное время. В этой версии D9/кол-во минут в C9.
В формуле это имя "m". Я их назвал "условные минуты".
Я ещё внёс изменения в ваше первое сообщение, посмотрите, всё ли там правильно.
Файл представлен в другом ответе.
Чтобы вам понять формулу, вам надо посмотреть в интернете или ещё где-нибудь следующее: как узнать, пересекаются два интервала времени или нет.
У меня такой принцип в формуле. Если начало работы меньше или равно концу заданного периода и конец работы больше или равен началу заданного периода, то интервалы пересекаются. Для третьего периода смотрится только конец работы и начало заданного периода, т.к. этого достаточно. Но можно сделать, как и в двух других.
О пересечении интервалов есть информация в интернете, это ещё кажется в каком-то разделе математики есть. Вы можете использовать свой способ, я использую такой способ. Я нашёл способ в интернете, но переделал под себя.
В моей формуле определение, пересекается ли рабочее время с тремя периодами переработки, делается в трёх именах, начинающихся "Is". В примечаниях имён я написал об этом, но там другими словами.
Спасибо большое, все работает, даже не знаю, чтобы я без Вас делал.
Но появилась одна дыра: ставлю начало работ любое время и окончание работ до 9:00, то итог - 0.
Есть подозрение, что в EndTime нужно поставить больше 9:00, но там формула немного странная:
=ЕСЛИ(Лист2!$B9<--"09:00";1+Лист2!$B9;Лист2!$B9), вот этот момент:<--"09:00" - что означают два прочерка --?
В общем, с любого времени и до 9:00 она должна считать, а не обнулять.
Два знака минуса перед временем переводят время из текстового вида (как мы видим на мониторе) в числовой вид, чтобы со временем можно было сравнить число.
Внёс изменения в имя EndTime:
=ЕСЛИ(Лист2!$B9<=--"09:00";1+Лист2!$B9;Лист2!$B9)
Огромное Вам человеческое СПАСИБО за помощь. Процветания и роста Вашему форуму... Всем буду советовать этот сайт.
Вот тут разобрался немного: =ЕСЛИ(Лист2!$B9<--"09:00";1+Лист2!$B9;Лист2!$B9), можно было решить не через <=--"09:00", а даже и так: <--"09:01".
Но в основной алгоритм я буду вникать еще долго.
Самое главное - что есть теперь во что вникать и с чем разбираться.
Для меня будет очень полезным уроком то, что Вы наваяли, мне очень интересен Эксель, очень мощная прога и практически безгранична. Я с удовольствием изучаю и познаю все тонкости ее работы.
Подскажите пожалуйста как сделать, чтобы созданная Вами формула по этому виду переработок, если сотрудник работал в выходные или праздничные дни, считала сумму переработок не с 18:00, а с 9:00.
Все остальные условия те же самые.
У вас же три вида переработки, какие виды переработки используются в выходные и праздничные?
Спасибо... Разобрался сам... Сделал без имён, сделал копируемую формулу, сделал отбор по должности и сделал отбор у техников по выходным и рабочим дням.
Выложу ради визуального восприятия этой громадины:
Формула
=ЕСЛИ(B11="Техник";(ЕСЛИ(ДЕНЬНЕД(A11;2)>5;((ЕСЛИ(ЕСЛИ(И(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)>=--"18:00");ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"00:00";1+"00:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)) -ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=--"09:00";--"09:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60);0)+ЕСЛИ(ЕСЛИ(И(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"00:00");ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11))-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";1+"00:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*1,7;0)+ЕСЛИ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(D11<=--"09:00";1+D11;D11)-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";1+"03:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*2,5;0))*4,05); ((ЕСЛИ(ЕСЛИ(И(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)>=--"18:00");ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"00:00";1+"00:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)) -ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=--"18:00";--"18:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60);0)+ЕСЛИ(ЕСЛИ(И(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"00:00");ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11))-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";1+"00:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*1,7;0)+ЕСЛИ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(D11<=--"09:00";1+D11;D11)-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";1+"03:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*2,5;0))*4,05))); ((ЕСЛИ(ЕСЛИ(И(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"00:00");ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11))-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";1+"00:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*1,7-ТЕКСТ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";1+"03:00";ЕСЛИ(D11<=--"09:00";1+D11;D11))-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"00:00";1+"00:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60);0)+ЕСЛИ(ЕСЛИ(ЕСЛИ(D11<=--"09:00";1+D11;D11)>=1+"03:00";ИСТИНА;ЛОЖЬ);ТЕКСТ(ЕСЛИ(D11<=--"09:00";1+D11;D11)-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";1+"03:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60)*2,5-ТЕКСТ(ЕСЛИ(D11<=--"09:00";1+D11;D11)-ЕСЛИ(ЕСЛИ(C11<--"09:00";1+C11;C11)<=1+"03:00";1+"03:00";ЕСЛИ(C11<--"09:00";1+C11;C11));"[м]")/60*F11/(ТЕКСТ(E11;"[м]")/60);0)) * 5,49))
В ней теперь никто не разберётся, но она рабочая!
Смотря на вашу формулу, пришли идеи оптимизации. Можно отказаться от использования слов "ИСТИНА", "ЛОЖЬ".
IsFirst:
=ЕСЛИ(Лист2!StartTime<=1+"00:00";Лист2!EndTime>=--"18:00")
IsSecond:
=ЕСЛИ(Лист2!StartTime<=1+"03:00";Лист2!EndTime>=1+"00:00")
IsThird:
=Лист2!EndTime>=1+"03:00"
В IsFirst и IsSecond можно использовать "И", вместо "ЕСЛИ", если будет удобнее читать формулу, но формулы с "ЕСЛИ" будут быстрее работать, т.к. если одно условие не выполняется, то второе уже не смотрится. А при использовании "И" всегда вычисляются оба выражения.
Доброго времени суток,
к кому можно обратиться с разработкой таблицы ? не за бесплатно.
Написал вам письмо на почту с заголовком "Письмо с Форума по VBA, Excel и Word". Написал именно на почту, а не на форум.