Расчет будущей даты рабочего дня при нескольких переменных
Xelix
Дата: Вторник, 07.05.2019, 12:08 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
И снова здравствуйте! Задачка попала - сам не рад. Есть сотрудники, которые с определенной периодичностью должны проходить инструктажи по ТБ (лист "инструктажи") Есть сотрудники, которые болеют, ходят в отпуск, отсутствуют по уважительным причинам в расчетный день инструктажа (лист "отсутствия сотрудников") Есть выходные и праздничные дни (лист "праздничные дни") Вопрос - насколько реально собрать эти данные в одну таблицу (без объединения ячеек), чтобы дата инструктажа выпадала на рабочий день, когда сотрудник на работе (не отсутствует). Заранее огромное спасибо за любой ответ. Путь изучения Excel тернист, но интересен. Я стараюсь
И снова здравствуйте! Задачка попала - сам не рад. Есть сотрудники, которые с определенной периодичностью должны проходить инструктажи по ТБ (лист "инструктажи") Есть сотрудники, которые болеют, ходят в отпуск, отсутствуют по уважительным причинам в расчетный день инструктажа (лист "отсутствия сотрудников") Есть выходные и праздничные дни (лист "праздничные дни") Вопрос - насколько реально собрать эти данные в одну таблицу (без объединения ячеек), чтобы дата инструктажа выпадала на рабочий день, когда сотрудник на работе (не отсутствует). Заранее огромное спасибо за любой ответ. Путь изучения Excel тернист, но интересен. Я стараюсь Xelix
Ответить
Сообщение И снова здравствуйте! Задачка попала - сам не рад. Есть сотрудники, которые с определенной периодичностью должны проходить инструктажи по ТБ (лист "инструктажи") Есть сотрудники, которые болеют, ходят в отпуск, отсутствуют по уважительным причинам в расчетный день инструктажа (лист "отсутствия сотрудников") Есть выходные и праздничные дни (лист "праздничные дни") Вопрос - насколько реально собрать эти данные в одну таблицу (без объединения ячеек), чтобы дата инструктажа выпадала на рабочий день, когда сотрудник на работе (не отсутствует). Заранее огромное спасибо за любой ответ. Путь изучения Excel тернист, но интересен. Я стараюсь Автор - Xelix Дата добавления - 07.05.2019 в 12:08
Kostya_Ye
Дата: Вторник, 07.05.2019, 14:28 |
Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
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.
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
Сообщение отредактировал 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 как рабочий день (в таблице праздников данная дата как выходной) И не перестает ставить даты при увольнении сотрудника
Kostya_Ye, Исправил 151 на 181 - заработало Но, тянет 4.11.2019 как рабочий день (в таблице праздников данная дата как выходной) И не перестает ставить даты при увольнении сотрудника Xelix
Сообщение отредактировал 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
0% ±
Excel 2016
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа. Дата увольнения в этой формуле никак не учитывается.
Этого не надо было делать. 151 нужно для генерации списка 30 дат до требуемой даты инструктажа. Дата увольнения в этой формуле никак не учитывается. Kostya_Ye
Ответить
Сообщение Этого не надо было делать. 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.1900Xelix
Ответить
Сообщение Этого не надо было делать. 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
Что то я совсем запутался. Поставил ограничение с датой увольнения. НО половина ячеек считается, половина нет. И при отсутствии даты увольнения не считает Выдает 0.1.1900 Xelix
Ответить
Сообщение Что то я совсем запутался. Поставил ограничение с датой увольнения. НО половина ячеек считается, половина нет. И при отсутствии даты увольнения не считает Выдает 0.1.1900 Автор - Xelix Дата добавления - 07.05.2019 в 15:40
Kostya_Ye
Дата: Вторник, 07.05.2019, 15:41 |
Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
0% ±
Excel 2016
формула массива Ctrl-Shift-Enter
Ответить
Сообщение формула массива Ctrl-Shift-Enter
Автор - Kostya_Ye Дата добавления - 07.05.2019 в 15:41
sboy
Дата: Вторник, 07.05.2019, 15:43 |
Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация:
724
±
Замечаний:
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);"")
Добрый день. для полугодового инструктажа (для года по аналогии) в О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
Яндекс: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
0% ±
Excel 2016
НО половина ячеек считается, половина нет.
Вы поменяли в формуле диапазон $A$1:$A$30 от которого зависит диапазон просматриваемых дат и который связан с числом 151 (в сумме на максимуме требуемая дата инструктажа), формула введена как обычная, а не как формула массива, поэтому был некорректный расчет. В приложенном файле все поправил.sboy , ХитрО ! А для чего в формуле предназначена конструкция 1/(1/( ... )) ?
НО половина ячеек считается, половина нет.
Вы поменяли в формуле диапазон $A$1:$A$30 от которого зависит диапазон просматриваемых дат и который связан с числом 151 (в сумме на максимуме требуемая дата инструктажа), формула введена как обычная, а не как формула массива, поэтому был некорректный расчет. В приложенном файле все поправил.sboy , ХитрО ! А для чего в формуле предназначена конструкция 1/(1/( ... )) ?Kostya_Ye
Сообщение отредактировал 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
Ответить
Сообщение 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
Ответить
Сообщение Kostya_Ye, Я честно использую сочетание клавиш для массива, но ничего не происходит. А когда {} ставлю с клавиатуры - формулы работают через раз. Я даже полностью переписал Вашу формулу, используя функцию МАКС Автор - Xelix Дата добавления - 07.05.2019 в 16:11
sboy
Дата: Вторник, 07.05.2019, 16:14 |
Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация:
724
±
Замечаний:
0% ±
Excel 2010
А для чего в формуле предназначена конструкция 1/(1/( ... )
перевести нули в ошибки Я забыл про лист отсутствие, сможете сами дополнить?
А для чего в формуле предназначена конструкция 1/(1/( ... )
перевести нули в ошибки Я забыл про лист отсутствие, сможете сами дополнить?sboy
Яндекс: 410016850021169
Ответить
Сообщение А для чего в формуле предназначена конструкция 1/(1/( ... )
перевести нули в ошибки Я забыл про лист отсутствие, сможете сами дополнить?Автор - sboy Дата добавления - 07.05.2019 в 16:14
Kostya_Ye
Дата: Вторник, 07.05.2019, 16:19 |
Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
0% ±
Excel 2016
Xelix , А когда {} ставлю с клавиатуры
Это не поможет, нужно именно сочетанием клавиш находясь в режиме редактирования формулы. Предлагаю использовать формулу sboy , т.к. она не массивная и проще. sboy , Век живи, век учись !
Xelix , А когда {} ставлю с клавиатуры
Это не поможет, нужно именно сочетанием клавиш находясь в режиме редактирования формулы. Предлагаю использовать формулу sboy , т.к. она не массивная и проще. sboy , Век живи, век учись !Kostya_Ye
Ответить
Сообщение Xelix , А когда {} ставлю с клавиатуры
Это не поможет, нужно именно сочетанием клавиш находясь в режиме редактирования формулы. Предлагаю использовать формулу sboy , т.к. она не массивная и проще. sboy , Век живи, век учись !Автор - Kostya_Ye Дата добавления - 07.05.2019 в 16:19
Kostya_Ye
Дата: Вторник, 07.05.2019, 16:28 |
Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 271
Репутация:
228
±
Замечаний:
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);"")
Анализируемый диапазон вокруг целевой даты инструктажа определяется частью
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);"")
Анализируемый диапазон вокруг целевой даты инструктажа определяется частью Kostya_Ye
Сообщение отредактировал 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);"")
Анализируемый диапазон вокруг целевой даты инструктажа определяется частью Автор - Kostya_Ye Дата добавления - 07.05.2019 в 16:28
Xelix
Дата: Вторник, 07.05.2019, 16:29 |
Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Kostya_Ye, только заметил, что нет отсутствия сотрудников, пошел на форум - а тут уже помощь! Огромное СПС!!!
Kostya_Ye, только заметил, что нет отсутствия сотрудников, пошел на форум - а тут уже помощь! Огромное СПС!!! Xelix
Ответить
Сообщение Kostya_Ye, только заметил, что нет отсутствия сотрудников, пошел на форум - а тут уже помощь! Огромное СПС!!! Автор - Xelix Дата добавления - 07.05.2019 в 16:29
Xelix
Дата: Вторник, 07.05.2019, 16:57 |
Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Может кому пригодится. Все работает. Добавил форматирование ячеек для текущего месяца.
Может кому пригодится. Все работает. Добавил форматирование ячеек для текущего месяца. Xelix
Сообщение отредактировал Xelix - Вторник, 07.05.2019, 17:26
Ответить
Сообщение Может кому пригодится. Все работает. Добавил форматирование ячеек для текущего месяца. Автор - Xelix Дата добавления - 07.05.2019 в 16:57
sboy
Дата: Вторник, 07.05.2019, 17:10 |
Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация:
724
±
Замечаний:
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