Доброго времени суток!
Начало переработки:
если начало работы меньше или равно 18:00, то начало переработки 18:00
если начало работы больше 18:00, то начало переработки - это начало работы
Время переработки (кол-во минут переработки):
если начало работы меньше или равно 18:00, то сметное время минус (18:00 минус начало работы)
если начало работы больше 18:00, то время переработки совпадает со сметным временем
Конец переработки:
начало переработки + время переработки
Все остальные действия с переходом из вечера в ночь, из первой половины ночи во вторую аналогичны самой первой теме, где разбирали формулу с техниками, за исключением лишь того, что правило расчёта переработок теперь то, что описано выше.
В выходные и праздничные дни первое условие начинает действовать с 9 утра, оставшиеся 2 также действуют в отведенное для них время.
Примеры расчёта времени переработкиПримеры
Все примеры для рабочих дней. Для выходных по-другому.
Пример 1
Начало работы 9:00, конец работы 18:00, сметное время 900.
18:00-9:00=540 минут
900-540=360
Пример 2
Начало работы 9:00, конец работы 19:00, сметное время 900.
18:00 - 9:00 = 540 минут
900-540 = 360
Пример 3
Начало работы 15:00, конец работы 17:00, сметное время 240.
18:00-15:00=180 минут
240-180=60 минут
Пример 4
Начало работы 18:00, конец работы 21:00, сметное время 240.
18:00-18:00=0 минут
240-0=240 минут
Спойлер
Начало работы 19:00, конец работы 21:00, сметное время 240
Время начала лежит за границей 18:00, значит время переработки совпадает со сметным временем.
Рассчитываем время окончания переработки: 19:00 + 4 часа = 23:00
4 часа - это сметные минуты, переведённые в часы: 240/60=4
23 не переходит за границу 00:00, значит это первый вид переработки:
240*4,05=972 рубля
[вложение удалено администратором]
Решение в файле.
Нет умножения 4,05 на 60, т.к. расчёты изначально находятся в минутах. То есть в расчётах используется кол-во минут переработки.
[вложение удалено администратором]
Спасибо за формулу.
Но возник нюанс, который я решил самостоятельно частично. Если техник работает в выходной ДЕНЬНЕД(Е3;2)>5 и/или в праздничные (это я не смог реализовать в прошлой формуле и не знаю как реализовать здесь) дни, то границу по первому условию я передвинул до 00:00 часов и всё заработало, но формула стала больше в два раза:
Формула
=ЕСЛИ(ДЕНЬНЕД(E3;2)>5;
(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"00:00";--"00:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05;
(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"18:00";--"18:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05)
Если сократить для понимания в данном случае до первого условия, где граница стоит до 18:00, потому что остальные два без изменений, то получается так:
=ЕСЛИ(ДЕНЬНЕД(E3;2)>5;
((ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"00:00";--"00:00";STime_fact);"[м]");0);
((ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"18:00";--"18:00";STime_fact);"[м]");0)
Ну и собственно вопрос - как это условие ЕСЛИ(ДЕНЬНЕД(E3;2)>5 встроить в формулу очередным ЕСЛИ, чтобы не удлинять формулу почти полным дублированием (Значение, если истина); (Значение, если ложь) за исключением изменения по границе в первом условии?
И второй вопрос - как сюда подкрутить государственные праздничные дни, которые бы воспринимались формулой тоже как выходные?
Если по второму вопросу -трудозатратно или формула увеличится в разы, то можно и проигнорировать данный вопрос. Не отсохнут руки у меня в праздники ручками позаполнять данные.
Я изменил имя IsFirst. Теперь оно учитывает будние и выходные.
Изменил саму формулу.
Листу дал имя "лист", чтобы удобно писать формулы, чтобы в имени было короче.
[вложение удалено администратором]
А праздники на каждый год нужно выписывать отдельно и этот диапазон сверять с заполняемым в столбце Е? Функции специальной нет в эксель на этот случай?
Вот этот параметр за что в формуле отвечает "[м]"?
Даёт информацию, сколько в дате минут.
Создал имя Раб_день и внёс изменения в IsFirst.
На лист1 праздники записывайте в умную таблицу - имя использует имя умной таблицы и столбец умной таблицы. При добавлении данных в умную таблицу формула будет видеть добавленные данные.
В столбце C просто вписывайте даты, начиная со строки 2 - столбец C полностью весь будет просматриваться.
[вложение удалено администратором]
Высший пилотаж... Круто... Спасибо... А можете объяснить алгоритм?
У меня в теме:Расчёт выхода на работы с помощью формулы они тоже будут задействованы. Хочу попробовать сам всё сделать, но для этого нужно понимание алгоритма...
По какому месту из этой формулы у вас вопрос:?
=(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05
(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]");0)
И для чего нужен столбец С в Листе 1?
На листе 1 в столбец C записывайте рабочие выходные. Это когда происходит перенос праздников.
Например, какое-то воскресенье может быть рабочим. Функция ЧИСТРАБДНИ не узнает о том, что такое воскресенье рабочий день и посчитает его выходным. Поэтому нужно корректировать, используя список из рабочих выходных.
Я понял. Но я также понял, что не смогу это сделать сам...
Как она в диспетчере имён у вас принимает вид таблицы?
(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]");0)
Если фактическое начало рабочего дня (столбец F) и конец работы, рассчитанный по смете, попадают в интервал от 18:00 до 00:00 (рабочие дни) или в интервал от 09:00 до 00:00 (в выходные дни), то дальнейший расчёт, 0.
ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]") - это расчёт кол-ва переработанных минут.
ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm) - это расчёт конца времени переработки.
ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact) - это расчёт начала времени переработки
Конец переработки минус начало переработки - это кол-во дней. С помощью функции ТЕКСТ переводим кол-во дней в кол-во минут.
По какому фрагменту ещё вопросы?
Цитата:
Как она в диспетчере имён у вас принимает вид таблицы?
Кто она? Как понять "принимает вид таблицы"?
В диспетчере имен все имена с ярлычком, а таблица с иконкой таблица.
Это эксель автоматически создаёт имя для умной таблицы, я это имя не создавал. Создайте для примера умную таблицу и вы увидите в диспетчере имён имя этой умной таблицы.
Что значит создать умную таблицу?
Функция ЧИСТРАБДНИ?
Создайте тему на форуме: как создать умную таблицу.
Про ЧИСТРАБДНИ не понял.
Я понял уже... Форматировать как таблицу - создание умной таблицы)))
Да, это такой сленг "умная таблица". Официально она называется просто "таблица", наверное. Но тогда не понятно, про какую таблицу обсуждение.
Те манипуляции, которые вы проделали с прикручиванием праздников: Создал имя Раб_день и внёс изменения в IsFirst.
На лист1 праздники записывайте в умную таблицу - имя использует имя умной таблицы и столбец умной таблицы. При добавлении данных в умную таблицу формула будет видеть добавленные данные.
В столбце C просто вписывайте даты, начиная со строки 2 - столбец C полностью весь будет просматриваться. - можно считать константой по умолчанию и использовать в дальнейшем в моих аналогичных таблицах, в частности с выходом, за которые сотрудник получает 600 и 1000 рублей, где он гарантированно получает их при первом выходе в выходной день и/или праздничный или её нужно корректировать?
Не понял вас. Что вы хотите использовать: таблицы из листа 1 или что-то другое?
И таблицы из листа и =ЧИСТРАБДНИ(лист!$E8;лист!$E8;Таблица1[Праздники])+ЕЧИСЛО(ПОИСКПОЗ(лист!$E8;Лист1!$C:$C;0))<>0
Вы можете использовать таблицы из листа 1 и имя Раб_день во всём файле. Я сделал Раб_день для всей книги, а не только для листа "лист" - это видно в диспетчере имён.
Не про книгу речь, а про эксель в принципе, мне потом нужно все эти таблицы задействовать в одной, и таблица с выходами, и таблица с техниками работает с датами, в том числе и выходными и праздниками, вот я заранее и спрашиваю, могу я эту формулу использовать потом для разных данных?
Я не знаю, нужно пробовать делать.
Внёс изменения.
В K11 у вас учитывается только первый вид переработки, но по расчётам конец переработки входит и во второй вид переработки.
[вложение удалено администратором]
Неправильно считает при этих условиях:
дата 02.07.2017 (это выходной), начало работы 17:00, конец работы 21:00, сметное время 120.
Должно было быть:
120*4,05=486
а формула посчитала 972, то есть она где-то умножила результат на 2.
А как вообще должно считаться в выходные?
Начало переработки - это начало работы.
Время переработки - сметное время.
Конец переработки = начало переработки + сметное время
Вроде всё так...
Исправил, у меня неправильно считало конец времени переработки для выходных.
[вложение удалено администратором]
Спасибо огромное...