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

Вход

Регистрация

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

 

= Мир MS Excel/Расчет будущей даты рабочего дня при нескольких переменных - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет будущей даты рабочего дня при нескольких переменных (Формулы/Formulas)
Расчет будущей даты рабочего дня при нескольких переменных
Xelix Дата: Вторник, 07.05.2019, 12:08 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
И снова здравствуйте!
Задачка попала - сам не рад.
Есть сотрудники, которые с определенной периодичностью должны проходить инструктажи по ТБ (лист "инструктажи")
Есть сотрудники, которые болеют, ходят в отпуск, отсутствуют по уважительным причинам в расчетный день инструктажа (лист "отсутствия сотрудников")
Есть выходные и праздничные дни (лист "праздничные дни")
Вопрос - насколько реально собрать эти данные в одну таблицу (без объединения ячеек), чтобы дата инструктажа выпадала на рабочий день, когда сотрудник на работе (не отсутствует).

Заранее огромное спасибо за любой ответ.

Путь изучения Excel тернист, но интересен.
Я стараюсь
К сообщению приложен файл: 5825370.xlsx(17.9 Kb)
 
Ответить
СообщениеИ снова здравствуйте!
Задачка попала - сам не рад.
Есть сотрудники, которые с определенной периодичностью должны проходить инструктажи по ТБ (лист "инструктажи")
Есть сотрудники, которые болеют, ходят в отпуск, отсутствуют по уважительным причинам в расчетный день инструктажа (лист "отсутствия сотрудников")
Есть выходные и праздничные дни (лист "праздничные дни")
Вопрос - насколько реально собрать эти данные в одну таблицу (без объединения ячеек), чтобы дата инструктажа выпадала на рабочий день, когда сотрудник на работе (не отсутствует).

Заранее огромное спасибо за любой ответ.

Путь изучения Excel тернист, но интересен.
Я стараюсь

Автор - Xelix
Дата добавления - 07.05.2019 в 12:08
Kostya_Ye Дата: Вторник, 07.05.2019, 14:28 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
Xelix,
Для поиска ближайшей к требуемой доступной даты в пределах 30 дней у меня вот так получилось для ячейки N3, формула массива Ctrl-Shift-Enter
Код
=MAX((ROW($A$1:$A$30)+151+H3)*(NOT(IFERROR(MATCH((ROW($A$1:$A$30)+151+H3);INDEX('Отсутствия сотрудников'!$E:$AC;MATCH($B3;'Отсутствия сотрудников'!$B:$B;0);0);0);0)))*(NOT(IFERROR(MATCH((ROW($A$1:$A$30)+151+H3);'Праздничные дни'!$B:$B;0);0)))*(WEEKDAY((ROW($A$1:$A$30)+151+H3);2)<6))

для O3 и далее заменить в формуле H3 на N3.


Сообщение отредактировал Kostya_Ye - Вторник, 07.05.2019, 14:31
 
Ответить
СообщениеXelix,
Для поиска ближайшей к требуемой доступной даты в пределах 30 дней у меня вот так получилось для ячейки N3, формула массива Ctrl-Shift-Enter
Код
=MAX((ROW($A$1:$A$30)+151+H3)*(NOT(IFERROR(MATCH((ROW($A$1:$A$30)+151+H3);INDEX('Отсутствия сотрудников'!$E:$AC;MATCH($B3;'Отсутствия сотрудников'!$B:$B;0);0);0);0)))*(NOT(IFERROR(MATCH((ROW($A$1:$A$30)+151+H3);'Праздничные дни'!$B:$B;0);0)))*(WEEKDAY((ROW($A$1:$A$30)+151+H3);2)<6))

для O3 и далее заменить в формуле H3 на N3.

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 14:28
Xelix Дата: Вторник, 07.05.2019, 14:42 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kostya_Ye,
Исправил 151 на 181 - заработало
Но, тянет 4.11.2019 как рабочий день (в таблице праздников данная дата как выходной)
И не перестает ставить даты при увольнении сотрудника


Сообщение отредактировал Xelix - Вторник, 07.05.2019, 14:51
 
Ответить
СообщениеKostya_Ye,
Исправил 151 на 181 - заработало
Но, тянет 4.11.2019 как рабочий день (в таблице праздников данная дата как выходной)
И не перестает ставить даты при увольнении сотрудника

Автор - Xelix
Дата добавления - 07.05.2019 в 14:42
Kostya_Ye Дата: Вторник, 07.05.2019, 14:55 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
Исправил 151 на 181
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа.
Дата увольнения в этой формуле никак не учитывается.
 
Ответить
Сообщение
Исправил 151 на 181
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа.
Дата увольнения в этой формуле никак не учитывается.

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 14:55
Xelix Дата: Вторник, 07.05.2019, 15:22 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа.

Тогда расчет дает 0.1.1900
 
Ответить
Сообщение
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа.

Тогда расчет дает 0.1.1900

Автор - Xelix
Дата добавления - 07.05.2019 в 15:22
Xelix Дата: Вторник, 07.05.2019, 15:40 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Что то я совсем запутался.
Поставил ограничение с датой увольнения.

НО половина ячеек считается, половина нет.
И при отсутствии даты увольнения не считает
Выдает 0.1.1900
К сообщению приложен файл: 8440012.xlsx(18.0 Kb)
 
Ответить
СообщениеЧто то я совсем запутался.
Поставил ограничение с датой увольнения.

НО половина ячеек считается, половина нет.
И при отсутствии даты увольнения не считает
Выдает 0.1.1900

Автор - Xelix
Дата добавления - 07.05.2019 в 15:40
Kostya_Ye Дата: Вторник, 07.05.2019, 15:41 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
формула массива Ctrl-Shift-Enter
 
Ответить
Сообщение
формула массива Ctrl-Shift-Enter

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 15:41
sboy Дата: Вторник, 07.05.2019, 15:43 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2498
Репутация: 699 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
для полугодового инструктажа (для года по аналогии) в О3
Код
=ЕСЛИОШИБКА(АГРЕГАТ(15;6;1/(1/((N3+180+СТРОКА(1:7))*ЕНД(ПОИСКПОЗ(N3+180+СТРОКА(1:7);праздники[Праздники:];))*(ДЕНЬНЕД((N3+180+СТРОКА(1:7));2)<5)*((N3+180+СТРОКА(1:7))<$G3)));1);"")
К сообщению приложен файл: 9574229.xlsx(17.9 Kb)


Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
для полугодового инструктажа (для года по аналогии) в О3
Код
=ЕСЛИОШИБКА(АГРЕГАТ(15;6;1/(1/((N3+180+СТРОКА(1:7))*ЕНД(ПОИСКПОЗ(N3+180+СТРОКА(1:7);праздники[Праздники:];))*(ДЕНЬНЕД((N3+180+СТРОКА(1:7));2)<5)*((N3+180+СТРОКА(1:7))<$G3)));1);"")

Автор - sboy
Дата добавления - 07.05.2019 в 15:43
Kostya_Ye Дата: Вторник, 07.05.2019, 15:55 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
НО половина ячеек считается, половина нет.

Вы поменяли в формуле диапазон $A$1:$A$30 от которого зависит диапазон просматриваемых дат и который связан с числом 151 (в сумме на максимуме требуемая дата инструктажа), формула введена как обычная, а не как формула массива, поэтому был некорректный расчет.
В приложенном файле все поправил.

sboy, ХитрО !

А для чего в формуле предназначена конструкция 1/(1/( ... )) ?
К сообщению приложен файл: 0111515.xlsx(20.3 Kb)


Сообщение отредактировал Kostya_Ye - Вторник, 07.05.2019, 16:06
 
Ответить
Сообщение
НО половина ячеек считается, половина нет.

Вы поменяли в формуле диапазон $A$1:$A$30 от которого зависит диапазон просматриваемых дат и который связан с числом 151 (в сумме на максимуме требуемая дата инструктажа), формула введена как обычная, а не как формула массива, поэтому был некорректный расчет.
В приложенном файле все поправил.

sboy, ХитрО !

А для чего в формуле предназначена конструкция 1/(1/( ... )) ?

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 15:55
Xelix Дата: Вторник, 07.05.2019, 16:08 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
sboy, спасибо! Все работает. Я еще правила оказания ПМП сделал тоже по формуле
Код
=АГРЕГАТ(15;6;1/(1/((H3+1+СТРОКА(1:5))*ЕНД(ПОИСКПОЗ(H3+1+СТРОКА(1:5);праздники[Праздники:];))*(ДЕНЬНЕД((H3+1+СТРОКА(1:5));2)<5)*((H3+1+СТРОКА(1:5))<$G3)));1)
 
Ответить
Сообщениеsboy, спасибо! Все работает. Я еще правила оказания ПМП сделал тоже по формуле
Код
=АГРЕГАТ(15;6;1/(1/((H3+1+СТРОКА(1:5))*ЕНД(ПОИСКПОЗ(H3+1+СТРОКА(1:5);праздники[Праздники:];))*(ДЕНЬНЕД((H3+1+СТРОКА(1:5));2)<5)*((H3+1+СТРОКА(1:5))<$G3)));1)

Автор - Xelix
Дата добавления - 07.05.2019 в 16:08
Xelix Дата: Вторник, 07.05.2019, 16:11 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kostya_Ye,
Я честно использую сочетание клавиш для массива, но ничего не происходит.
А когда {} ставлю с клавиатуры - формулы работают через раз.
Я даже полностью переписал Вашу формулу, используя функцию МАКС
 
Ответить
СообщениеKostya_Ye,
Я честно использую сочетание клавиш для массива, но ничего не происходит.
А когда {} ставлю с клавиатуры - формулы работают через раз.
Я даже полностью переписал Вашу формулу, используя функцию МАКС

Автор - Xelix
Дата добавления - 07.05.2019 в 16:11
sboy Дата: Вторник, 07.05.2019, 16:14 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 2498
Репутация: 699 ±
Замечаний: 0% ±

Excel 2010
А для чего в формуле предназначена конструкция 1/(1/( ... )

перевести нули в ошибки
Я забыл про лист отсутствие, сможете сами дополнить?


Яндекс: 410016850021169
 
Ответить
Сообщение
А для чего в формуле предназначена конструкция 1/(1/( ... )

перевести нули в ошибки
Я забыл про лист отсутствие, сможете сами дополнить?

Автор - sboy
Дата добавления - 07.05.2019 в 16:14
Kostya_Ye Дата: Вторник, 07.05.2019, 16:19 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
Xelix,
А когда {} ставлю с клавиатуры

Это не поможет, нужно именно сочетанием клавиш находясь в режиме редактирования формулы.

Предлагаю использовать формулу sboy, т.к. она не массивная и проще.

sboy,
перевести нули в ошибки

Век живи, век учись !
 
Ответить
СообщениеXelix,
А когда {} ставлю с клавиатуры

Это не поможет, нужно именно сочетанием клавиш находясь в режиме редактирования формулы.

Предлагаю использовать формулу sboy, т.к. она не массивная и проще.

sboy,
перевести нули в ошибки

Век живи, век учись !

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 16:19
Kostya_Ye Дата: Вторник, 07.05.2019, 16:28 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 3 ±
Замечаний: 0% ±

Excel 2016
Xelix, формула sboy с учетом периодов отсутствия
Код
=IFERROR(AGGREGATE(15;6;1/(1/((O3+180+ROW(1:7))*ISNA(MATCH(O3+180+ROW(1:7);праздники[Праздники:];))*ISNA(MATCH(O3+180+ROW(1:7);INDEX('Отсутствия сотрудников'!$E:$AC;MATCH(Инструктажи!$B3;'Отсутствия сотрудников'!$B:$B;0);0);0))*(WEEKDAY((O3+180+ROW(1:7));2)<5)*((O3+180+ROW(1:7))<$G3)));1);"")


Анализируемый диапазон вокруг целевой даты инструктажа определяется частью
Код
180+ROW(1:7)


Сообщение отредактировал Kostya_Ye - Вторник, 07.05.2019, 16:30
 
Ответить
СообщениеXelix, формула sboy с учетом периодов отсутствия
Код
=IFERROR(AGGREGATE(15;6;1/(1/((O3+180+ROW(1:7))*ISNA(MATCH(O3+180+ROW(1:7);праздники[Праздники:];))*ISNA(MATCH(O3+180+ROW(1:7);INDEX('Отсутствия сотрудников'!$E:$AC;MATCH(Инструктажи!$B3;'Отсутствия сотрудников'!$B:$B;0);0);0))*(WEEKDAY((O3+180+ROW(1:7));2)<5)*((O3+180+ROW(1:7))<$G3)));1);"")


Анализируемый диапазон вокруг целевой даты инструктажа определяется частью
Код
180+ROW(1:7)

Автор - Kostya_Ye
Дата добавления - 07.05.2019 в 16:28
Xelix Дата: Вторник, 07.05.2019, 16:29 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kostya_Ye, только заметил, что нет отсутствия сотрудников, пошел на форум - а тут уже помощь!
Огромное СПС!!!
 
Ответить
СообщениеKostya_Ye, только заметил, что нет отсутствия сотрудников, пошел на форум - а тут уже помощь!
Огромное СПС!!!

Автор - Xelix
Дата добавления - 07.05.2019 в 16:29
Xelix Дата: Вторник, 07.05.2019, 16:57 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Может кому пригодится.
Все работает.
Добавил форматирование ячеек для текущего месяца.
К сообщению приложен файл: 2241081.xlsx(30.3 Kb)


Сообщение отредактировал Xelix - Вторник, 07.05.2019, 17:26
 
Ответить
СообщениеМожет кому пригодится.
Все работает.
Добавил форматирование ячеек для текущего месяца.

Автор - Xelix
Дата добавления - 07.05.2019 в 16:57
sboy Дата: Вторник, 07.05.2019, 17:10 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 2498
Репутация: 699 ±
Замечаний: 0% ±

Excel 2010
Да


Яндекс: 410016850021169
 
Ответить
СообщениеДа

Автор - sboy
Дата добавления - 07.05.2019 в 17:10
Xelix Дата: Вторник, 07.05.2019, 17:26 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
sboy, спасибо!
 
Ответить
Сообщениеsboy, спасибо!

Автор - Xelix
Дата добавления - 07.05.2019 в 17:26
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчет будущей даты рабочего дня при нескольких переменных (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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