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

Вход

Регистрация

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

 

= Мир MS Excel/Расчёт рабочего Контрольного срока - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчёт рабочего Контрольного срока (Формулы/Formulas)
Расчёт рабочего Контрольного срока
Diman4as_ Дата: Суббота, 13.01.2024, 23:43 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Коллеги, доброго времени!

Возникла необходимость расчёта контрольного срока по заданному времени.
Дано: Сотруднику принимает в работу задачу. Фиксирует дату со временем (например, 15.01.2024 09:30).
Необходимо рассчитать контрольный срок у данной задачи, т.е. к какому времени сотрудник должен её решить.

При этом учитываются только рабочие часы.
Режим работы сотрудника: пн-чт 08:30 - 17:30, пт 08:30 - 16:15.
Обеденный перерыв: пн-пт 12:30 - 13:15.
На решение задачи у сотрудника ровно 12 рабочих часов.
Помогите, пожалуйста с формулой, которая сотруднику покажет, к какому времени он должен управиться.

Праздничные дни и выходные в расчёте не должны учитываться как рабочие.
 
Ответить
СообщениеКоллеги, доброго времени!

Возникла необходимость расчёта контрольного срока по заданному времени.
Дано: Сотруднику принимает в работу задачу. Фиксирует дату со временем (например, 15.01.2024 09:30).
Необходимо рассчитать контрольный срок у данной задачи, т.е. к какому времени сотрудник должен её решить.

При этом учитываются только рабочие часы.
Режим работы сотрудника: пн-чт 08:30 - 17:30, пт 08:30 - 16:15.
Обеденный перерыв: пн-пт 12:30 - 13:15.
На решение задачи у сотрудника ровно 12 рабочих часов.
Помогите, пожалуйста с формулой, которая сотруднику покажет, к какому времени он должен управиться.

Праздничные дни и выходные в расчёте не должны учитываться как рабочие.
Автор - Diman4as_
Дата добавления - 13.01.2024 в 23:43
Gustav Дата: Воскресенье, 14.01.2024, 00:53 | Сообщение № 2
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Пожалуй, напишу несколько предложений для начала разговора.

При всей своей кажущейся легкости и несложной решаемости в уме или на бумажке, формулу для Excel здесь составить весьма и весьма непросто, если вообще возможно в старых версиях Excel (до версий 365\2021++).

Задача, скорее, может быть решена макросом (либо с использованием функции LET в последних версиях), ибо явно просматривается процедурный перебор нескольких соседних рабочих дней, конкретную последовательность которых предстоит определить на первом этапе решения.

Кол-во рабочих дней интервала просмотра я бы прикинул с помощью такой оценочной формулы (с предусмотренным явным избытком):
Код
=ОКРУГЛВВЕРХ([Колво часов на задачу] / 7;) + 2
Взял здесь "7 часов" в знаменателе как минимальную длительность рабочего дня в неделю (в пятницу). Для еще большей гарантии можно бы взять и "6 часов" - случай, когда пятница вдруг является еще и ПРЕДпраздничным днем.

Зная начальную дату (дату выдачи задания) и кол-во рабочих дней просмотра, можно получить дату последнего рабочего дня просмотра:
Код
=РАБДЕНЬ.МЕЖД(дата_начала_задания; раб_дней_просмотра; массив_выходных; массив_праздников)
(с массивами выходных и праздников здесь, как понимаете, своя отдельная "головная боль" - ну, да ладно!)

Ну, а дальше надо тоже крепко думать. Я бы сгенерировал последовательность рабочих дат между первым и последним рабочими днями интервала просмотра. И после, в зависимости от дня недели, вычислил длительность каждого рабочего дня: 8,25 часов для пн-чт и 7 часов для пятницы. Затем на последовательности этих значений можно как-то "набирать" сумму, равную кол-ву часов, выделенных на задачу, начиная с некоторого значения (смещения от начала) внутри первого рабочего дня.

В общем, буду с интересом следить за этим топиком и, возможно, как-то сам еще поучаствую.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеПожалуй, напишу несколько предложений для начала разговора.

При всей своей кажущейся легкости и несложной решаемости в уме или на бумажке, формулу для Excel здесь составить весьма и весьма непросто, если вообще возможно в старых версиях Excel (до версий 365\2021++).

Задача, скорее, может быть решена макросом (либо с использованием функции LET в последних версиях), ибо явно просматривается процедурный перебор нескольких соседних рабочих дней, конкретную последовательность которых предстоит определить на первом этапе решения.

Кол-во рабочих дней интервала просмотра я бы прикинул с помощью такой оценочной формулы (с предусмотренным явным избытком):
Код
=ОКРУГЛВВЕРХ([Колво часов на задачу] / 7;) + 2
Взял здесь "7 часов" в знаменателе как минимальную длительность рабочего дня в неделю (в пятницу). Для еще большей гарантии можно бы взять и "6 часов" - случай, когда пятница вдруг является еще и ПРЕДпраздничным днем.

Зная начальную дату (дату выдачи задания) и кол-во рабочих дней просмотра, можно получить дату последнего рабочего дня просмотра:
Код
=РАБДЕНЬ.МЕЖД(дата_начала_задания; раб_дней_просмотра; массив_выходных; массив_праздников)
(с массивами выходных и праздников здесь, как понимаете, своя отдельная "головная боль" - ну, да ладно!)

Ну, а дальше надо тоже крепко думать. Я бы сгенерировал последовательность рабочих дат между первым и последним рабочими днями интервала просмотра. И после, в зависимости от дня недели, вычислил длительность каждого рабочего дня: 8,25 часов для пн-чт и 7 часов для пятницы. Затем на последовательности этих значений можно как-то "набирать" сумму, равную кол-ву часов, выделенных на задачу, начиная с некоторого значения (смещения от начала) внутри первого рабочего дня.

В общем, буду с интересом следить за этим топиком и, возможно, как-то сам еще поучаствую.
Автор - Gustav
Дата добавления - 14.01.2024 в 00:53
Gustav Дата: Воскресенье, 14.01.2024, 03:23 | Сообщение № 3
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
А еще вот такой концептуальный уточняющий вопрос. В 9:30 дали задание на 3 часа. Контрольным сроком в этом случае считать 12:30 (начало обеда) или 13:15 (конец обеда)?

То же самое и по поводу пятницы. В 9:30 пятницы дали задание на 6 часов. Контрольным сроком в этом случае считать 16:15 пятницы или 08:30 понедельника?

Кстати, вопрос о пятнице весьма существенен. Представьте себе, человек сидит на удалёнке (как я, например) и имеет возможность довольно свободно распоряжаться временем. И ему срочно надо куда-то съездить по личным делам в формально рабочее время. В этом случае эти 6 часов он вполне может и в субботу отработать, в принципе по срокам никого не подводя.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 14.01.2024, 03:36
 
Ответить
СообщениеА еще вот такой концептуальный уточняющий вопрос. В 9:30 дали задание на 3 часа. Контрольным сроком в этом случае считать 12:30 (начало обеда) или 13:15 (конец обеда)?

То же самое и по поводу пятницы. В 9:30 пятницы дали задание на 6 часов. Контрольным сроком в этом случае считать 16:15 пятницы или 08:30 понедельника?

Кстати, вопрос о пятнице весьма существенен. Представьте себе, человек сидит на удалёнке (как я, например) и имеет возможность довольно свободно распоряжаться временем. И ему срочно надо куда-то съездить по личным делам в формально рабочее время. В этом случае эти 6 часов он вполне может и в субботу отработать, в принципе по срокам никого не подводя.
Автор - Gustav
Дата добавления - 14.01.2024 в 03:23
Gustav Дата: Воскресенье, 14.01.2024, 21:38 | Сообщение № 4
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Что ж, раз все молчат - продолжу. Воплотил свой алгоритм в формулу с использованием функции LET, но не для Excel, а для Таблиц Google. Причина - в доступности Таблиц Google для всех желающих (нужно только завести свой аккаунт Google, если еще его нет), а также в том, что я, скорее, демонстрирую возможный алгоритм решения, нежели реализацию для конкретного продукта. Для конкретного же продукта формулу можно внимательно рассмотреть и, взяв за основу, воплотить в конкретную реализацию. Например, в виде макроса - пользовательской функции на VBA (UDF) для Excel, либо с использованием нескольких доп.столбцов (для промежуточных вычислений) для старых версий Excel, либо, слегка изменив имеющийся синтаксис Google (при подавляющем совпадении они с Excel всё же несколько различаются), приспособить формулу для Excel свежих версий 365\2021++.

[vba]
Код
=LET(
стартДатаВремя; A1;
часов;          B1;

работаНач;      TIMEVALUE("08:30");
работаКон;      TIMEVALUE("17:30");
работаКон5;     TIMEVALUE("16:15");
обедНач;        TIMEVALUE("12:30");
обедКон;        TIMEVALUE("13:15");

полдня1часы;    (обедНач - работаНач) * 24;
полдня2часы;    (работаКон - обедКон) * 24;
полдня2часы5;   (работаКон5 - обедКон) * 24;

датаНач;        INT(стартДатаВремя);
времяНач;       стартДатаВремя - датаНач;

рабДней;        ROUNDUP(часов / 7;) + 2;
датаКон;        WORKDAY.INTL(датаНач; рабДней; "0000011");
дни;            SEQUENCE(датаКон - датаНач + 1; 1; датаНач);

полдни1;        MAP(дни; LAMBDA(x; {x \ 1 \ IF(WORKDAY.INTL(x-1;1)<>x; 0; полдня1часы)}));
полдни2;        MAP(дни; LAMBDA(x; {x \ 2 \ IF(WORKDAY.INTL(x-1;1)<>x; 0; IF(WEEKDAY(x;2)=5; полдня2часы5; полдня2часы))}));
полдниВсе;      {полдни1; полдни2};
полдниФильтр;   FILTER(полдниВсе; INDEX(полдниВсе;0;3) > 0);
полдни;         SORT(полдниФильтр; 1;TRUE; 2;TRUE);
нараст;         SCAN(0; INDEX(полдни;0;3); LAMBDA(s;v;s+v));
дляВПР;         {{0;нараст}\{полдни;{WORKDAY.INTL(датаКон; 1) \ 1 \ полдня1часы}}};

часыСтартЗадан; IFS(времяНач >= обедКон;   (времяНач - обедКон) * 24 + полдня1часы;
                    времяНач >= обедНач;   полдня1часы;
                    времяНач >= работаНач; (времяНач - работаНач) * 24);
рабЧасыНач;     IF(WORKDAY.INTL(датаНач-1;1)<>датаНач; 0; полдня1часы + IF(WEEKDAY(датаНач;2)=5; полдня2часы5; полдня2часы));
часыСтарт;      IF(часыСтартЗадан > рабЧасыНач; рабЧасыНач; часыСтартЗадан);
часыФиниш;      часыСтарт + часов;

финишЧасы;      VLOOKUP(часыФиниш; дляВПР; 1);
финишДата;      VLOOKUP(часыФиниш; дляВПР; 2);
финишПоловина;  VLOOKUP(часыФиниш; дляВПР; 3);
финишДельта;    часыФиниш - финишЧасы;
финишВремя;     CHOOSE(финишПоловина; работаНач; обедКон) + финишДельта / 24;

TO_DATE(финишДата + финишВремя)
)
[/vba]
Чтобы протестировать формулу, нужно ввести в ячейки A1:B1 исходные данные:
в ячейку A1 - дату-время начала выполнения задания в формате ДД.ММ.ГГГГ чч:мм, например, 15.01.2024 09:30
в ячейку B1 - число рабочих часов, отведенных на выполнение работы, например, 12. Дробное число часов вводится в ячейку B1 как обычное десятичное число с дробной частью: так значение "20 часов 30 минут" нужно ввести как число 20,5
Саму формулу введем, например, в ячейку C1 и сделаем ее числовой формат таким же, как и у ячейки A1 - ДД.ММ.ГГГГ чч:мм. При указанных выше исходных данных получим ответ: 16.01.2024 14:00

Чуть позже прокомментирую алгоритм, текущие ограничения и возможные перспективы развития формулы. Одним словом, to be continued...

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЧто ж, раз все молчат - продолжу. Воплотил свой алгоритм в формулу с использованием функции LET, но не для Excel, а для Таблиц Google. Причина - в доступности Таблиц Google для всех желающих (нужно только завести свой аккаунт Google, если еще его нет), а также в том, что я, скорее, демонстрирую возможный алгоритм решения, нежели реализацию для конкретного продукта. Для конкретного же продукта формулу можно внимательно рассмотреть и, взяв за основу, воплотить в конкретную реализацию. Например, в виде макроса - пользовательской функции на VBA (UDF) для Excel, либо с использованием нескольких доп.столбцов (для промежуточных вычислений) для старых версий Excel, либо, слегка изменив имеющийся синтаксис Google (при подавляющем совпадении они с Excel всё же несколько различаются), приспособить формулу для Excel свежих версий 365\2021++.

[vba]
Код
=LET(
стартДатаВремя; A1;
часов;          B1;

работаНач;      TIMEVALUE("08:30");
работаКон;      TIMEVALUE("17:30");
работаКон5;     TIMEVALUE("16:15");
обедНач;        TIMEVALUE("12:30");
обедКон;        TIMEVALUE("13:15");

полдня1часы;    (обедНач - работаНач) * 24;
полдня2часы;    (работаКон - обедКон) * 24;
полдня2часы5;   (работаКон5 - обедКон) * 24;

датаНач;        INT(стартДатаВремя);
времяНач;       стартДатаВремя - датаНач;

рабДней;        ROUNDUP(часов / 7;) + 2;
датаКон;        WORKDAY.INTL(датаНач; рабДней; "0000011");
дни;            SEQUENCE(датаКон - датаНач + 1; 1; датаНач);

полдни1;        MAP(дни; LAMBDA(x; {x \ 1 \ IF(WORKDAY.INTL(x-1;1)<>x; 0; полдня1часы)}));
полдни2;        MAP(дни; LAMBDA(x; {x \ 2 \ IF(WORKDAY.INTL(x-1;1)<>x; 0; IF(WEEKDAY(x;2)=5; полдня2часы5; полдня2часы))}));
полдниВсе;      {полдни1; полдни2};
полдниФильтр;   FILTER(полдниВсе; INDEX(полдниВсе;0;3) > 0);
полдни;         SORT(полдниФильтр; 1;TRUE; 2;TRUE);
нараст;         SCAN(0; INDEX(полдни;0;3); LAMBDA(s;v;s+v));
дляВПР;         {{0;нараст}\{полдни;{WORKDAY.INTL(датаКон; 1) \ 1 \ полдня1часы}}};

часыСтартЗадан; IFS(времяНач >= обедКон;   (времяНач - обедКон) * 24 + полдня1часы;
                    времяНач >= обедНач;   полдня1часы;
                    времяНач >= работаНач; (времяНач - работаНач) * 24);
рабЧасыНач;     IF(WORKDAY.INTL(датаНач-1;1)<>датаНач; 0; полдня1часы + IF(WEEKDAY(датаНач;2)=5; полдня2часы5; полдня2часы));
часыСтарт;      IF(часыСтартЗадан > рабЧасыНач; рабЧасыНач; часыСтартЗадан);
часыФиниш;      часыСтарт + часов;

финишЧасы;      VLOOKUP(часыФиниш; дляВПР; 1);
финишДата;      VLOOKUP(часыФиниш; дляВПР; 2);
финишПоловина;  VLOOKUP(часыФиниш; дляВПР; 3);
финишДельта;    часыФиниш - финишЧасы;
финишВремя;     CHOOSE(финишПоловина; работаНач; обедКон) + финишДельта / 24;

TO_DATE(финишДата + финишВремя)
)
[/vba]
Чтобы протестировать формулу, нужно ввести в ячейки A1:B1 исходные данные:
в ячейку A1 - дату-время начала выполнения задания в формате ДД.ММ.ГГГГ чч:мм, например, 15.01.2024 09:30
в ячейку B1 - число рабочих часов, отведенных на выполнение работы, например, 12. Дробное число часов вводится в ячейку B1 как обычное десятичное число с дробной частью: так значение "20 часов 30 минут" нужно ввести как число 20,5
Саму формулу введем, например, в ячейку C1 и сделаем ее числовой формат таким же, как и у ячейки A1 - ДД.ММ.ГГГГ чч:мм. При указанных выше исходных данных получим ответ: 16.01.2024 14:00

Чуть позже прокомментирую алгоритм, текущие ограничения и возможные перспективы развития формулы. Одним словом, to be continued...
Автор - Gustav
Дата добавления - 14.01.2024 в 21:38
Gustav Дата: Воскресенье, 14.01.2024, 22:06 | Сообщение № 5
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата Gustav, 14.01.2024 в 21:38, в сообщении № 4 ()
to be continued...

Продолжаем дальше. Алогритм, заложенный в формулу, фактически строит внутри себя таблицу "дляВПР" следующего вида (4 колонки):
[vba]
Код
Часов от начала 1-го раб.дня -- Дата -- "Половина" раб.дня -- Часов "половины"
------------------------------------------------------------------------------
0       15.01.2024      1   4
4       15.01.2024      2   4,25
8,25    16.01.2024      1   4
12,25   16.01.2024      2   4,25
16,5    17.01.2024      1   4
20,5    17.01.2024      2   4,25
24,75   18.01.2024      1   4
28,75   18.01.2024      2   4,25
33      19.01.2024      1   4
37      19.01.2024      2   3
40      22.01.2024      1   4
[/vba]
После чего ищет в первой колонке этой таблицы значение "часыФиниш", которое в нашем примере равно числу 13 = 1 + 12 (т.е. сумме: смещение в часах начала выполнения задания относительно времени начала работы "08:30" в первый рабочий день интервала + число часов, запланированных на выполнение задания). Наконец, найдя в таблице "дляВПР" нужные значения даты и номера "половинки" - внутри "половинки" вычисляется точное значение времени окончания работы.

Формула в текущем виде рассчитана на пятидневную рабочую неделю с понедельника по пятницу (с уменьшенным кол-вом рабочих часов в пятницу), с двумя стандартными выходными (суббота, воскресенье), без учета праздников, выпадающих на рабочие дни. Для учета праздников нужно формировать "где-то в сторонке" (на другом листе) массив праздников, и подключать его четвертым параметром во все имеющиеся в формуле вызовы функции WORKDAY.INTL (сейчас их, вроде бы, пять).

Если помимо праздников, выпадающих на стандартные рабочие дни с понедельника по пятницу, захочется еще учесть "чёрные" (рабочие) субботы или воскресенья, возникающие иногда в связи с правительственными переносами рабочих и праздничных дней, то на эту тему можно изучить, например, мой материал здесь: Функция РАБДЕНЬ. Как учесть рабочую субботу 20.02.16?

Правительственные переносы рабочих и праздничных дней могут вызвать ситуации, когда, например, в "чёрную" субботу работать придётся, скажем, по графику пятницы. Это пока никак не учтено в формуле, но, по всей видимости, нужно будет создавать (так же "в сторонке") какой-то массив дат с указанием принятых для них нестандартных дней недели. Ссылки на подобный массив должны будут появиться "в окрестностях" применения в формуле функции WEEKDAY (сейчас в формуле имеется два таких места).

Никак пока не учтено в формуле и сокращение рабочего времени на 1 час в предпраздничный день. Думается, что при необходимости такого учета, нужно будет обработать (функциями MAP и LAMBDA) массив рабочих часов 2-й половины дня - "полдни2". А именно, нужно будет уменьшить на 1 кол-во часов в третьей колонке, если дата будет являться предпраздничным днём. Для проверки на "предпраздничность" нужно будет также организовать некоторый справочник "в сторонке", в котором перечислить дни с типом "предпраздничный", взяв их из официального Производственного календаря текущего года.

Что же касается моего вопроса в сообщении №3, пока оставшегося без ответа, то сейчас при формировании таблицы "дляВПР" как-то сам собой, практически естественным образом, выстроился порядок, когда в качестве контрольного срока берется более позднее значение, т.е. получающийся срок окончания работы, в точности попадающий на конец текущего рабочего дня "17:30", "озвучивается" как "08:30" следующего рабочего дня. Это же касается обеда - вместо "12:30" говорим "13:15", это же касается конца недели - вместо "16:15" пятницы говорим "08:30" понедельника.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 14.01.2024, 22:08
 
Ответить
Сообщение
Цитата Gustav, 14.01.2024 в 21:38, в сообщении № 4 ()
to be continued...

Продолжаем дальше. Алогритм, заложенный в формулу, фактически строит внутри себя таблицу "дляВПР" следующего вида (4 колонки):
[vba]
Код
Часов от начала 1-го раб.дня -- Дата -- "Половина" раб.дня -- Часов "половины"
------------------------------------------------------------------------------
0       15.01.2024      1   4
4       15.01.2024      2   4,25
8,25    16.01.2024      1   4
12,25   16.01.2024      2   4,25
16,5    17.01.2024      1   4
20,5    17.01.2024      2   4,25
24,75   18.01.2024      1   4
28,75   18.01.2024      2   4,25
33      19.01.2024      1   4
37      19.01.2024      2   3
40      22.01.2024      1   4
[/vba]
После чего ищет в первой колонке этой таблицы значение "часыФиниш", которое в нашем примере равно числу 13 = 1 + 12 (т.е. сумме: смещение в часах начала выполнения задания относительно времени начала работы "08:30" в первый рабочий день интервала + число часов, запланированных на выполнение задания). Наконец, найдя в таблице "дляВПР" нужные значения даты и номера "половинки" - внутри "половинки" вычисляется точное значение времени окончания работы.

Формула в текущем виде рассчитана на пятидневную рабочую неделю с понедельника по пятницу (с уменьшенным кол-вом рабочих часов в пятницу), с двумя стандартными выходными (суббота, воскресенье), без учета праздников, выпадающих на рабочие дни. Для учета праздников нужно формировать "где-то в сторонке" (на другом листе) массив праздников, и подключать его четвертым параметром во все имеющиеся в формуле вызовы функции WORKDAY.INTL (сейчас их, вроде бы, пять).

Если помимо праздников, выпадающих на стандартные рабочие дни с понедельника по пятницу, захочется еще учесть "чёрные" (рабочие) субботы или воскресенья, возникающие иногда в связи с правительственными переносами рабочих и праздничных дней, то на эту тему можно изучить, например, мой материал здесь: Функция РАБДЕНЬ. Как учесть рабочую субботу 20.02.16?

Правительственные переносы рабочих и праздничных дней могут вызвать ситуации, когда, например, в "чёрную" субботу работать придётся, скажем, по графику пятницы. Это пока никак не учтено в формуле, но, по всей видимости, нужно будет создавать (так же "в сторонке") какой-то массив дат с указанием принятых для них нестандартных дней недели. Ссылки на подобный массив должны будут появиться "в окрестностях" применения в формуле функции WEEKDAY (сейчас в формуле имеется два таких места).

Никак пока не учтено в формуле и сокращение рабочего времени на 1 час в предпраздничный день. Думается, что при необходимости такого учета, нужно будет обработать (функциями MAP и LAMBDA) массив рабочих часов 2-й половины дня - "полдни2". А именно, нужно будет уменьшить на 1 кол-во часов в третьей колонке, если дата будет являться предпраздничным днём. Для проверки на "предпраздничность" нужно будет также организовать некоторый справочник "в сторонке", в котором перечислить дни с типом "предпраздничный", взяв их из официального Производственного календаря текущего года.

Что же касается моего вопроса в сообщении №3, пока оставшегося без ответа, то сейчас при формировании таблицы "дляВПР" как-то сам собой, практически естественным образом, выстроился порядок, когда в качестве контрольного срока берется более позднее значение, т.е. получающийся срок окончания работы, в точности попадающий на конец текущего рабочего дня "17:30", "озвучивается" как "08:30" следующего рабочего дня. Это же касается обеда - вместо "12:30" говорим "13:15", это же касается конца недели - вместо "16:15" пятницы говорим "08:30" понедельника.
Автор - Gustav
Дата добавления - 14.01.2024 в 22:06
Diman4as_ Дата: Воскресенье, 14.01.2024, 22:46 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Gustav, Спасибо большое за столь подробное описание решения!
К сожалению, макросы нежелательны в использовании в компании...
Попробую на ваших комментариях создать всё с помощью формул и промежуточных вспомогательных столбцах.
А1 - время принятия в работу задачи.
B1 - контрольный срок в часах
.......
а далее уже эксперементировать!
Если сможете посодействовать, буду премного благодарен!


Сообщение отредактировал Diman4as_ - Воскресенье, 14.01.2024, 22:50
 
Ответить
СообщениеGustav, Спасибо большое за столь подробное описание решения!
К сожалению, макросы нежелательны в использовании в компании...
Попробую на ваших комментариях создать всё с помощью формул и промежуточных вспомогательных столбцах.
А1 - время принятия в работу задачи.
B1 - контрольный срок в часах
.......
а далее уже эксперементировать!
Если сможете посодействовать, буду премного благодарен!
Автор - Diman4as_
Дата добавления - 14.01.2024 в 22:46
Gustav Дата: Вторник, 16.01.2024, 01:37 | Сообщение № 7
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Свершилось! В сообщении №5 я фактически набросал "техзадание" по развитию своей формулы из сообщения №4. И вот в итоге сам же это задание и реализовал.

Получилась реально супер-формула, учитывающая все существенные нюансы Производственного календаря 2024 года, а именно:
* "красные" выходные и праздники - единый список всех годовых суббот и воскресений (за исключением "черных" суббот) + все праздничные дни, выпадающие на дни с понедельника по пятницу;
* предпраздничные дни - с сокращением длительности рабочего дня на 1 час;
* "черные" рабочие субботы;
* дни, которые были перенесены на рабочие субботы - для определения "истинного дня недели" рабочей субботы, для расчета возможного сокращения рабочего времени ("пятница/не пятница").

Вот эта ОБНОВЛЕННАЯ формула:
[vba]
Код
=LET(
стартДатаВремя; A1;
часов;          B1;

работаНач;      TIMEVALUE("08:30");
работаКон;      TIMEVALUE("17:30");
работаКон5;     TIMEVALUE("16:15");
обедНач;        TIMEVALUE("12:30");
обедКон;        TIMEVALUE("13:15");

всеВыходные;    $Z$1:$Z$118;
предПраздни;    {--"22.2.24";--"7.3.24";--"8.5.24";--"11.6.24";--"2.11.24"};
чернСубботы;    {--"27.4.24";--"2.11.24";--"28.12.24"};
заменыНаЧС;     {--"29.4.24";--"30.4.24";--"30.12.24"};

полдня1часы;    (обедНач - работаНач) * 24;
полдня2часы;    (работаКон - обедКон) * 24;
полдня2часы5;   (работаКон5 - обедКон) * 24;

датаНач;        INT(стартДатаВремя);
времяНач;       стартДатаВремя - датаНач;

рабДней;        ROUNDUP(часов / 7;) + 2;
датаКон;        WORKDAY.INTL(датаНач; рабДней; "0000000"; всеВыходные);
дни;            SEQUENCE(датаКон - датаНач + 1; 1; датаНач);

полдни1;        MAP(дни; LAMBDA(x; {x \ 1 \ IF( WORKDAY.INTL(x-1; 1; "0000000"; всеВыходные)<>x; 0;
                    полдня1часы)} ));
полдни2;        MAP(дни; LAMBDA(x; {x \ 2 \ IF( WORKDAY.INTL(x-1; 1; "0000000"; всеВыходные)<>x; 0;
                    IF(WEEKDAY(XLOOKUP(x; чернСубботы; заменыНаЧС; x);2) = 5; полдня2часы5; полдня2часы)
                    + ISNA(XMATCH(x; предПраздни)) - 1)} ));
полдниВсе;      {полдни1; полдни2};
полдниФильтр;   FILTER(полдниВсе; INDEX(полдниВсе;0;3) > 0);
полдни;         SORT(полдниФильтр; 1;TRUE; 2;TRUE);
нараст;         SCAN(0; INDEX(полдни;0;3); LAMBDA(s;v;s+v));
дляВПР;         {{0;нараст}\{полдни;{WORKDAY.INTL(датаКон; 1; "0000000"; всеВыходные) \ 1 \ полдня1часы}}};

часыСтартЗадан; IFS(времяНач >= обедКон;   (времяНач - обедКон) * 24 + полдня1часы;
                    времяНач >= обедНач;   полдня1часы;
                    времяНач >= работаНач; (времяНач - работаНач) * 24);
рабЧасыНач;     IF( WORKDAY.INTL(датаНач-1; 1; "0000000"; всеВыходные) <> датаНач; 0;
                    полдня1часы
                    + IF(WEEKDAY(XLOOKUP(датаНач; чернСубботы; заменыНаЧС; датаНач); 2) = 5; полдня2часы5; полдня2часы)
                    + ISNA(XMATCH(датаНач; предПраздни)) - 1);
часыСтарт;      IF(часыСтартЗадан > рабЧасыНач; рабЧасыНач; часыСтартЗадан);
часыФиниш;      часыСтарт + часов;

финишЧасы;      VLOOKUP(часыФиниш; дляВПР; 1);
финишДата;      VLOOKUP(часыФиниш; дляВПР; 2);
финишПоловина;  VLOOKUP(часыФиниш; дляВПР; 3);
финишДельта;    часыФиниш - финишЧасы;
финишВремя;     CHOOSE(финишПоловина; работаНач; обедКон) + финишДельта / 24;

TO_DATE(финишДата + финишВремя)
)
[/vba]

Все перечисленные выше "нюансы" представляют собой списки значений, которые используются в формуле под следующими идентификаторами (соответственно):
[vba]
Код
* всеВыходные;    $Z$1:$Z$118;
* предПраздни;    {--"22.2.24";--"7.3.24";--"8.5.24";--"11.6.24";--"2.11.24"};
* чернСубботы;    {--"27.4.24";--"2.11.24";--"28.12.24"};
* заменыНаЧС;     {--"29.4.24";--"30.4.24";--"30.12.24"};
[/vba]
Как видно, значения коротких списков "зашиты" прямо внутрь формулы. Список же "всеВыходные" ссылается на диапазон рабочего листа "где-то в сторонке" (мною выбрано начало колонки Z). Значения для списка "всеВыходные" подготовлены с использованием технологической "оснастки", описанной здесь: Функция РАБДЕНЬ. Как учесть рабочую субботу 20.02.16?. В прилагаемом к этому сообщению файле Excel этот список красных дат представлен. Можно скопировать его и вставить в свой файл для использования в качестве четвертого параметра функции WORKDAY.INTL (при этом третий обязательно должен выглядеть как строка из семи нулей "0000000" - с деталями можно ознакомиться по ссылке выше).

Два слова о третьем и четвертом списках. Что это такое? Если мы обратимся к Производственному календарю 2024, опубликованному по ссылке: https://www.consultant.ru/law/ref/calendar/proizvodstvennye/2024/ , то там есть такой абзац:
Цитата
В 2024 году в соответствии с Проектом Постановления Правительства РФ "О переносе выходных дней в 2024 году" переносятся следующие выходные дни:

* субботы 6 января на пятницу 10 мая;
* воскресенья 7 января на вторник 31 декабря;
* субботы 27 апреля на понедельник 29 апреля;
* субботы 2 ноября на вторник 30 апреля;
* субботы 28 декабря на понедельник 30 декабря.

Первые два переноса, когда два "красных" дня при переносе делают нерабочими два "черных" дня, нам в данном случае не интересны. А вот в оставшихся трёх переносятся "черные" рабочие дни на "черные" субботы, наследуя при этом день недели переносимого рабочего дня. Т.е. в три чёрные субботы 2024 мы работаем два раза "по понедельнику" и один раз "по вторнику". В данном случае это тоже не очень интересно, так как сохраняется стандартная продолжительность рабочего дня с понедельника по четверг - 8 часов 15 минут. А вот если бы в "черную" субботу мы работали "по пятнице", то продолжительность дня бы в этом случае составила 7 часов (и формула это всё учитывает!). Ну, и не нужно быть супердогадливым, чтобы понять, что массив "чернСубботы" содержит 3 первые даты в трех последних строках списка переносов, а массив "заменыНаЧС" - 3 вторые даты (т.е. даты, по которым и определяется "день недели" при работе в "черную" субботу).

Формулой можно смело пользоваться на протяжении всего 2024 года. При этом вряд ли имеет смысл обращение с ней в прошлое (до 2024). А вот в будущее (2025+), по мере его приближения - конечно, имеет. В этом случае после опубликования Производственного календаря 2025 нужно будет дополнить вышеперечисленные списки данными 2025 года (или просто заменить их)
К сообщению приложен файл: reddays24.xlsx (10.1 Kb)

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 16.01.2024, 01:43
 
Ответить
СообщениеСвершилось! В сообщении №5 я фактически набросал "техзадание" по развитию своей формулы из сообщения №4. И вот в итоге сам же это задание и реализовал.

Получилась реально супер-формула, учитывающая все существенные нюансы Производственного календаря 2024 года, а именно:
* "красные" выходные и праздники - единый список всех годовых суббот и воскресений (за исключением "черных" суббот) + все праздничные дни, выпадающие на дни с понедельника по пятницу;
* предпраздничные дни - с сокращением длительности рабочего дня на 1 час;
* "черные" рабочие субботы;
* дни, которые были перенесены на рабочие субботы - для определения "истинного дня недели" рабочей субботы, для расчета возможного сокращения рабочего времени ("пятница/не пятница").

Вот эта ОБНОВЛЕННАЯ формула:
[vba]
Код
=LET(
стартДатаВремя; A1;
часов;          B1;

работаНач;      TIMEVALUE("08:30");
работаКон;      TIMEVALUE("17:30");
работаКон5;     TIMEVALUE("16:15");
обедНач;        TIMEVALUE("12:30");
обедКон;        TIMEVALUE("13:15");

всеВыходные;    $Z$1:$Z$118;
предПраздни;    {--"22.2.24";--"7.3.24";--"8.5.24";--"11.6.24";--"2.11.24"};
чернСубботы;    {--"27.4.24";--"2.11.24";--"28.12.24"};
заменыНаЧС;     {--"29.4.24";--"30.4.24";--"30.12.24"};

полдня1часы;    (обедНач - работаНач) * 24;
полдня2часы;    (работаКон - обедКон) * 24;
полдня2часы5;   (работаКон5 - обедКон) * 24;

датаНач;        INT(стартДатаВремя);
времяНач;       стартДатаВремя - датаНач;

рабДней;        ROUNDUP(часов / 7;) + 2;
датаКон;        WORKDAY.INTL(датаНач; рабДней; "0000000"; всеВыходные);
дни;            SEQUENCE(датаКон - датаНач + 1; 1; датаНач);

полдни1;        MAP(дни; LAMBDA(x; {x \ 1 \ IF( WORKDAY.INTL(x-1; 1; "0000000"; всеВыходные)<>x; 0;
                    полдня1часы)} ));
полдни2;        MAP(дни; LAMBDA(x; {x \ 2 \ IF( WORKDAY.INTL(x-1; 1; "0000000"; всеВыходные)<>x; 0;
                    IF(WEEKDAY(XLOOKUP(x; чернСубботы; заменыНаЧС; x);2) = 5; полдня2часы5; полдня2часы)
                    + ISNA(XMATCH(x; предПраздни)) - 1)} ));
полдниВсе;      {полдни1; полдни2};
полдниФильтр;   FILTER(полдниВсе; INDEX(полдниВсе;0;3) > 0);
полдни;         SORT(полдниФильтр; 1;TRUE; 2;TRUE);
нараст;         SCAN(0; INDEX(полдни;0;3); LAMBDA(s;v;s+v));
дляВПР;         {{0;нараст}\{полдни;{WORKDAY.INTL(датаКон; 1; "0000000"; всеВыходные) \ 1 \ полдня1часы}}};

часыСтартЗадан; IFS(времяНач >= обедКон;   (времяНач - обедКон) * 24 + полдня1часы;
                    времяНач >= обедНач;   полдня1часы;
                    времяНач >= работаНач; (времяНач - работаНач) * 24);
рабЧасыНач;     IF( WORKDAY.INTL(датаНач-1; 1; "0000000"; всеВыходные) <> датаНач; 0;
                    полдня1часы
                    + IF(WEEKDAY(XLOOKUP(датаНач; чернСубботы; заменыНаЧС; датаНач); 2) = 5; полдня2часы5; полдня2часы)
                    + ISNA(XMATCH(датаНач; предПраздни)) - 1);
часыСтарт;      IF(часыСтартЗадан > рабЧасыНач; рабЧасыНач; часыСтартЗадан);
часыФиниш;      часыСтарт + часов;

финишЧасы;      VLOOKUP(часыФиниш; дляВПР; 1);
финишДата;      VLOOKUP(часыФиниш; дляВПР; 2);
финишПоловина;  VLOOKUP(часыФиниш; дляВПР; 3);
финишДельта;    часыФиниш - финишЧасы;
финишВремя;     CHOOSE(финишПоловина; работаНач; обедКон) + финишДельта / 24;

TO_DATE(финишДата + финишВремя)
)
[/vba]

Все перечисленные выше "нюансы" представляют собой списки значений, которые используются в формуле под следующими идентификаторами (соответственно):
[vba]
Код
* всеВыходные;    $Z$1:$Z$118;
* предПраздни;    {--"22.2.24";--"7.3.24";--"8.5.24";--"11.6.24";--"2.11.24"};
* чернСубботы;    {--"27.4.24";--"2.11.24";--"28.12.24"};
* заменыНаЧС;     {--"29.4.24";--"30.4.24";--"30.12.24"};
[/vba]
Как видно, значения коротких списков "зашиты" прямо внутрь формулы. Список же "всеВыходные" ссылается на диапазон рабочего листа "где-то в сторонке" (мною выбрано начало колонки Z). Значения для списка "всеВыходные" подготовлены с использованием технологической "оснастки", описанной здесь: Функция РАБДЕНЬ. Как учесть рабочую субботу 20.02.16?. В прилагаемом к этому сообщению файле Excel этот список красных дат представлен. Можно скопировать его и вставить в свой файл для использования в качестве четвертого параметра функции WORKDAY.INTL (при этом третий обязательно должен выглядеть как строка из семи нулей "0000000" - с деталями можно ознакомиться по ссылке выше).

Два слова о третьем и четвертом списках. Что это такое? Если мы обратимся к Производственному календарю 2024, опубликованному по ссылке: https://www.consultant.ru/law/ref/calendar/proizvodstvennye/2024/ , то там есть такой абзац:
Цитата
В 2024 году в соответствии с Проектом Постановления Правительства РФ "О переносе выходных дней в 2024 году" переносятся следующие выходные дни:

* субботы 6 января на пятницу 10 мая;
* воскресенья 7 января на вторник 31 декабря;
* субботы 27 апреля на понедельник 29 апреля;
* субботы 2 ноября на вторник 30 апреля;
* субботы 28 декабря на понедельник 30 декабря.

Первые два переноса, когда два "красных" дня при переносе делают нерабочими два "черных" дня, нам в данном случае не интересны. А вот в оставшихся трёх переносятся "черные" рабочие дни на "черные" субботы, наследуя при этом день недели переносимого рабочего дня. Т.е. в три чёрные субботы 2024 мы работаем два раза "по понедельнику" и один раз "по вторнику". В данном случае это тоже не очень интересно, так как сохраняется стандартная продолжительность рабочего дня с понедельника по четверг - 8 часов 15 минут. А вот если бы в "черную" субботу мы работали "по пятнице", то продолжительность дня бы в этом случае составила 7 часов (и формула это всё учитывает!). Ну, и не нужно быть супердогадливым, чтобы понять, что массив "чернСубботы" содержит 3 первые даты в трех последних строках списка переносов, а массив "заменыНаЧС" - 3 вторые даты (т.е. даты, по которым и определяется "день недели" при работе в "черную" субботу).

Формулой можно смело пользоваться на протяжении всего 2024 года. При этом вряд ли имеет смысл обращение с ней в прошлое (до 2024). А вот в будущее (2025+), по мере его приближения - конечно, имеет. В этом случае после опубликования Производственного календаря 2025 нужно будет дополнить вышеперечисленные списки данными 2025 года (или просто заменить их)
Автор - Gustav
Дата добавления - 16.01.2024 в 01:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчёт рабочего Контрольного срока (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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