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

Вход

Регистрация

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

 

= Мир MS Excel/Формула подсчёта рабочих дней с учетом праздничных дней - Мир MS Excel

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

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

Автор - ZorKon71
Дата добавления - 09.11.2019 в 22:40
bmv98rus Дата: Суббота, 09.11.2019, 22:49 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениекросс

Автор - bmv98rus
Дата добавления - 09.11.2019 в 22:49
Pelena Дата: Суббота, 09.11.2019, 23:14 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19160
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Посмотрите такой вариант. Списки праздников и переносов на листе Праздники
К сообщению приложен файл: _______2018.xlsx (22.6 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПосмотрите такой вариант. Списки праздников и переносов на листе Праздники

Автор - Pelena
Дата добавления - 09.11.2019 в 23:14
Gustav Дата: Суббота, 09.11.2019, 23:14 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2695
Репутация: 1123 ±
Замечаний: 0% ±

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеУ меня был подход к решению здесь:
http://www.excelworld.ru/forum/2-19373-225876-16-1500307095

Автор - Gustav
Дата добавления - 09.11.2019 в 23:14
ZorKon71 Дата: Воскресенье, 10.11.2019, 18:47 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Pelena, что-то не понял, в чём заключается вариант?
 
Ответить
СообщениеPelena, что-то не понял, в чём заключается вариант?

Автор - ZorKon71
Дата добавления - 10.11.2019 в 18:47
ZorKon71 Дата: Воскресенье, 10.11.2019, 19:16 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
У меня был подход к решению здесь:
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
 
Ответить
Сообщение
У меня был подход к решению здесь:
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)}

Автор - ZorKon71
Дата добавления - 10.11.2019 в 19:16
Pelena Дата: Воскресенье, 10.11.2019, 19:36 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19160
Репутация: 4412 ±
Замечаний: ±

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

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


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
в чём заключается вариант?

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

Автор - Pelena
Дата добавления - 10.11.2019 в 19:36
ZorKon71 Дата: Воскресенье, 10.11.2019, 19:53 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

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

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

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


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

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

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

Автор - ZorKon71
Дата добавления - 10.11.2019 в 19:53
Pelena Дата: Воскресенье, 10.11.2019, 20:19 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19160
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
То есть Вам не количество дней, а дату надо получить?
Тогда см. рецепт от Gustav
К сообщению приложен файл: _2018.xlsx (24.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТо есть Вам не количество дней, а дату надо получить?
Тогда см. рецепт от Gustav

Автор - Pelena
Дата добавления - 10.11.2019 в 20:19
ZorKon71 Дата: Воскресенье, 10.11.2019, 21:26 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

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

А у Вас с доработанным всё хорошо:
 
Ответить
СообщениеДа. Но тогда я смотрю надо справочник дорабатывать, иначе не правильно считает...
С обычным справочником:

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

Автор - ZorKon71
Дата добавления - 10.11.2019 в 21:26
Gustav Дата: Воскресенье, 10.11.2019, 22:08 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2695
Репутация: 1123 ±
Замечаний: 0% ±

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

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

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

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


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

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

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

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

Автор - Gustav
Дата добавления - 10.11.2019 в 22:08
bmv98rus Дата: Понедельник, 11.11.2019, 08:00 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 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)
К сообщению приложен файл: Copy_of_1266.xlsx (31.4 Kb)


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

Сообщение отредактировал bmv98rus - Вторник, 12.11.2019, 07:31
 
Ответить
СообщениеТогда избавляемся от полного списка выходных и празников и модифицируем до массивной
Код
=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)

Автор - bmv98rus
Дата добавления - 11.11.2019 в 08:00
ZorKon71 Дата: Пятница, 15.11.2019, 21:01 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 35
Репутация: 0 ±
Замечаний: 0% ±

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

Спасибо.
Т.е. в списке необходимо иметь только выходные дни. Вроде всё работает, хотя и просто.

С только же праздниками в шаблоне обязательно должны быть выходные (единички) "0000011".

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

Спасибо.
Т.е. в списке необходимо иметь только выходные дни. Вроде всё работает, хотя и просто.

С только же праздниками в шаблоне обязательно должны быть выходные (единички) "0000011".

А вот здесь не понял. Это в том же списке? Единички показывают на что не надо реагировать?

Автор - ZorKon71
Дата добавления - 15.11.2019 в 21:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула подсчёта рабочих дней с учетом праздничных дней (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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