Формула подсчёта рабочих дней с учетом праздничных дней

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Формула подсчёта рабочих дней с учетом праздничных дней (Формулы/Formulas)

ZorKon71

Дата: Суббота, 09.11.2019, 22:40 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

А не поможете решить задачку, с учётом переносов выходных?
Есть таблица, в которой две даты. Очень много строк.
Надо проверить, что бы вторая дата была не меньше чем через 5 рабочих дней.
В противном случае надо сигнализировать.
Использовал РАБДЕНЬ() и список дат-прадников. Но как перенести… не понятно. Таких дней не много, но надо учесть.
Нашёл формулы использующие массивы, но такая формула по столбцу просто не копируется. В приложенном файле кусочек таблицы (на листе справа). Я хотел для начала высчитать правильную дату, потом сравнить. Но вопрос как высчитать грамотно. Может формулу в макрос преобразовать?
Может кто-нибудь уже решил проблему?

К сообщению приложен файл: _______2018.rar (18.2 Kb)

Ответить

bmv98rus

Дата: Суббота, 09.11.2019, 22:49 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4112

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

кросс

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

Pelena

Дата: Суббота, 09.11.2019, 23:14 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 19394

Репутация: 4546 ±

Замечаний: ±

Excel 365 & Mac Excel

Посмотрите такой вариант. Списки праздников и переносов на листе Праздники

К сообщению приложен файл: _______2018.xlsx (22.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Gustav

Дата: Суббота, 09.11.2019, 23:14 | Сообщение № 4

Группа: Админы

Ранг: Участник клуба

Сообщений: 2791

Репутация: 1155 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

У меня был подход к решению здесь:
http://www.excelworld.ru/forum/2-19373-225876-16-1500307095

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

Ответить

ZorKon71

Дата: Воскресенье, 10.11.2019, 18:47 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Pelena, что-то не понял, в чём заключается вариант?

Ответить

ZorKon71

Дата: Воскресенье, 10.11.2019, 19:16 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Цитата Gustav, 09.11.2019 в 23:14, в сообщении № 4 ()

У меня был подход к решению здесь:
http://www.excelworld.ru/forum/2-19373-225876-16-1500307095

Спасибо, но не удалось. (см.файлы, я добавил колонку N - c Вашей формулой, в P - перечень праздников.) Т.е. получилась Ваша формула такой:

Код

=РАБДЕНЬ.МЕЖД(L1;5;"0000000";$P:$P)

Жаль.
Я помню, можно было свою функцию добавить как макрос.
Вот бы сделать так с формулой для массива?

Код

{=НАИМЕНЬШИЙ(ЕСЛИ((A1:A366<(L1+1))+(A1:A366*C1:C366=0);МАКС(A1:A366)*2;A1:A366);5)}

К сообщению приложен файл: RasDate2018.rar (24.4 Kb)

Сообщение отредактировал ZorKon71 - Воскресенье, 10.11.2019, 19:20

Ответить

Pelena

Дата: Воскресенье, 10.11.2019, 19:36 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 19394

Репутация: 4546 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата ZorKon71, 10.11.2019 в 18:47, в сообщении № 5 ()

в чём заключается вариант?

Вариант Формулы подсчёта рабочих дней с учетом праздничных дней в соответствии с названием темы. Формула в столбце N.
Что-то не так?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

ZorKon71

Дата: Воскресенье, 10.11.2019, 19:53 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Цитата Pelena, 10.11.2019 в 19:36, в сообщении № 7 ()

Что-то не так?

БОЖЕ!
Вы гений!!! Это уже готовый расчёт. Даже проще получилось. Я пошёл для моей задаче более сложным путём. :cranky:
Спасибо огромное...

А можно ли что-нибудь придумать, чтобы получить правильную дату?
Т.е. в колонке M получить дату через определённое кол-во раб.дней в соответствии даты в колонке L, но более простым путём чем я нашёл...
Спасибо! respect

Сообщение отредактировал ZorKon71 - Воскресенье, 10.11.2019, 19:56

Ответить

Pelena

Дата: Воскресенье, 10.11.2019, 20:19 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 19394

Репутация: 4546 ±

Замечаний: ±

Excel 365 & Mac Excel

То есть Вам не количество дней, а дату надо получить?
Тогда см. рецепт от Gustav

К сообщению приложен файл: _2018.xlsx (24.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

ZorKon71

Дата: Воскресенье, 10.11.2019, 21:26 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Да. Но тогда я смотрю надо справочник дорабатывать, иначе не правильно считает...
С обычным справочником:

А у Вас с доработанным всё хорошо:

Ответить

Gustav

Дата: Воскресенье, 10.11.2019, 22:08 | Сообщение № 11

Группа: Админы

Ранг: Участник клуба

Сообщений: 2791

Репутация: 1155 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата ZorKon71, 10.11.2019 в 19:16, в сообщении № 6 ()

Спасибо, но не удалось. (см.файлы, я добавил колонку N - c Вашей формулой, в P - перечень праздников.) Т.е. получилась Ваша формула такой:
=РАБДЕНЬ.МЕЖД(L1;5;"0000000";$P:$P)

Не удалось, потому что читали невнимательно. В $P:$P должны быть не только праздники (примерно 15 дней в году), а праздники+выходные-рабочие "субботы" (примерно 120 дней в году, т.е. все выходные дни вне зависимости от происхождения). Тогда применение шаблона "0000000" имеет смысл. С только же праздниками в шаблоне обязательно должны быть выходные (единички) "0000011".

Цитата Pelena, 10.11.2019 в 20:19, в сообщении № 9 ()

Тогда см. рецепт от Gustav

Лена, спасибо за подготовленный актуальный пример!

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

Ответить

bmv98rus

Дата: Понедельник, 11.11.2019, 08:00 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4112

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

Тогда избавляемся от полного списка выходных и празников и модифицируем до массивной

Код

=WORKDAY.INTL(L1;5;"0000000";(ISNUMBER(MATCH(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)))-1;Праздники!$A$1:$A$99;))+(WEEKDAY(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)))-1;2)>5)*ISERROR(MATCH(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)))-1;Праздники!$D$1:$D$4;))>0)*(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)))-1))

Так как стоит константа 5 дней, то 2*INT(5/5)) можно опустить, но в целом это поправка на набегание дней за счет выходных, но и она не покроет набор праздников если указано более 365 дней вместо 5.
~~Вариант без WORKDAY.INTL - не доработал.~~

Код

=SMALL(IF(ISERROR(MATCH(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)));Праздники!$A$1:$A$99;))*((WEEKDAY(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)));2)<6)+ISNUMBER(MATCH(L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5)));Праздники!$D$1:$D$4;)));L1+ROW($A$1:INDEX(A:A;5+14+2*INT(5/5))));5)

для произвольного количества дней (U1)

Код

=SMALL(IF(ISERROR(MATCH(L1+ROW($A$1:INDEX(A:A;$U$1+14+3*INT($U$1/7)));Праздники!$A$1:$A$99;))*((WEEKDAY(L1+ROW($A$1:INDEX(A:A;$U$1+14+3*INT($U$1/7)));2)<6)+ISNUMBER(MATCH(L1+ROW($A$1:INDEX(A:A;$U$1+14+3*INT($U$1/7)));Праздники!$D$1:$D$4;)));L1+ROW($A$1:INDEX(A:A;$U$1+14+3*INT($U$1/7))));U1)