Excel: Расчет работы сотрудника после основного рабочего времени

[Данила]

Сотрудник может начать работать в любое время с 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 можно использовать "И", вместо "ЕСЛИ", если будет удобнее читать формулу, но формулы с "ЕСЛИ" будут быстрее работать, т.к. если одно условие не выполняется, то второе уже не смотрится. А при использовании "И" всегда вычисляются оба выражения.

[fatalistd]

Доброго времени суток,

к кому можно обратиться с разработкой таблицы ? не за бесплатно.

[Администратор]

Написал вам письмо на почту с заголовком "Письмо с Форума по VBA, Excel и Word". Написал именно на почту, а не на форум.