Che79, Большое спасибо за формулу. В данном примере она работает. Но когда я ввожу в ячейку Е15 срок отпуска. То изменяется периоды в диапазоне ячеек D20:D31 (месяцы). А данные диапазона D20:D31 берутся из диапазона H20:H31 Они вычитаются. Например, количество календарных дней в мае минус количество праздников в мае. И если ввести в ячейку Е15 27.05.17 В ячейке В20 будет месяц май и отнимется 1 праздничный день 31 минус 1 = 30. А должно быть 31 минус 3 = 28. То есть по моему столбцы B, D и Н должны быть взаимосвязаны – если изменяются данные в столице Е15 → изменится месяц в диапазоне В20:В31 и → соответсятвенно должны изменится названия месяцев в диапазоне H20:H31. Как бы динамические периоды должны быть. Я изменил Ваш файл с учётом праздников Украины. Прикрепляю этот файл он называется: «6225674 + Украина.xlsx» Посмотрите пожалуйста. А на листе 1 я изменил для примера период отпуска: в ячейку Е15 поставил 27.06.2017. И в ячейке В31 месяй май, а у него получился 31 день. А должно быть 28. Вопрос: может лучше количество дней, столбец D20:D31 заполнять вручную, без формулы, в связи с тем что надо постоянно менять периоды?
Che79, Большое спасибо за формулу. В данном примере она работает. Но когда я ввожу в ячейку Е15 срок отпуска. То изменяется периоды в диапазоне ячеек D20:D31 (месяцы). А данные диапазона D20:D31 берутся из диапазона H20:H31 Они вычитаются. Например, количество календарных дней в мае минус количество праздников в мае. И если ввести в ячейку Е15 27.05.17 В ячейке В20 будет месяц май и отнимется 1 праздничный день 31 минус 1 = 30. А должно быть 31 минус 3 = 28. То есть по моему столбцы B, D и Н должны быть взаимосвязаны – если изменяются данные в столице Е15 → изменится месяц в диапазоне В20:В31 и → соответсятвенно должны изменится названия месяцев в диапазоне H20:H31. Как бы динамические периоды должны быть. Я изменил Ваш файл с учётом праздников Украины. Прикрепляю этот файл он называется: «6225674 + Украина.xlsx» Посмотрите пожалуйста. А на листе 1 я изменил для примера период отпуска: в ячейку Е15 поставил 27.06.2017. И в ячейке В31 месяй май, а у него получился 31 день. А должно быть 28. Вопрос: может лучше количество дней, столбец D20:D31 заполнять вручную, без формулы, в связи с тем что надо постоянно менять периоды?Никанор
Che79, Большое спасибо! Сейчас проверяю. Кажется всё правильно. Я изменил для Украины ещё перепроверю разные варианты. 1 вопрос: Скажите пожалуйста как изменять данные при наступлении новых месяцев? В диапазон I21:U21 вносить дни праздников я так понял? Куда вносить новые праздники и когда?
И вопрос не по формуле: дайте пожалуйста ссылку как на этом сайте цитировать из цитаты ответивших.
Che79, Большое спасибо! Сейчас проверяю. Кажется всё правильно. Я изменил для Украины ещё перепроверю разные варианты. 1 вопрос: Скажите пожалуйста как изменять данные при наступлении новых месяцев? В диапазон I21:U21 вносить дни праздников я так понял? Куда вносить новые праздники и когда?
И вопрос не по формуле: дайте пожалуйста ссылку как на этом сайте цитировать из цитаты ответивших.Никанор
В диапазон I21:U21 вносить дни праздников я так понял?
да, верно. И неважно, в какой последовательности по строкам начиная с I20 (в Вашем реальном файле может быть другой диапазон), т.е. праздники могут идти не в хронологическом порядке. Важно, чтобы если в одном месяце (например, январь или май) праздников больше одного, то они заносились в одну строку, каждая дата в отдельную ячейку, иначе ВПР() вернёт неверный результат.
Про цитирование: Выделяете фразу из поста и жмёте на кнопку "Цитата" в правом нижнем углу. Но цитируйте без фанатизма, ибо полное / излишнее цитирование постов (кратких и не очень) - это нарушение Правил форума.
В диапазон I21:U21 вносить дни праздников я так понял?
да, верно. И неважно, в какой последовательности по строкам начиная с I20 (в Вашем реальном файле может быть другой диапазон), т.е. праздники могут идти не в хронологическом порядке. Важно, чтобы если в одном месяце (например, январь или май) праздников больше одного, то они заносились в одну строку, каждая дата в отдельную ячейку, иначе ВПР() вернёт неверный результат.
Про цитирование: Выделяете фразу из поста и жмёте на кнопку "Цитата" в правом нижнем углу. Но цитируйте без фанатизма, ибо полное / излишнее цитирование постов (кратких и не очень) - это нарушение Правил форума.Che79
Делай нормально и будет нормально!
Сообщение отредактировал Che79 - Четверг, 14.12.2017, 20:03
Che79, Здравствуйте! Можете ли Вы немного изменить формулы для таблицы расчёта отпускных? Я немного изменил для наглядности и для дальнейшей работы таблицы праздничных дней. И прилагаю файл. В этом файле я добавил две таблицы в диапазонах 18:O32;R18:X32. В эти таблицы внёс название месяцев и даты праздников.
Помогите, пожалуйста, написать формулы для столбца D – календарные дни. Условия для заполнения этого столбца D: 1 условие: ячейки столбца D заполняются если заполнены ячейки столбца В: Если заполнена – не пустая ячейка В20 заполняется ячейка D20, А ЕЕСЛИ пустая ячейка В20 то и пустая ячейка D20. Если заполнена – не пустая ячейка В21 заполняется ячейка D21, А ЕЕСЛИ пустая ячейка В21 то и пустая ячейка D21. Если заполнена – не пустая ячейка В22 заполняется ячейка D22, А ЕЕСЛИ пустая ячейка В22 то и пустая ячейка D22. И так далее до ячейки D31.
2 условие: количество календарных дней в месяце, указанном в ячейках столбца В отнять (минус) количество праздничных дней, указанных в двух таблицах за 2016 и 2017 годы. Например, если в ячейке В20 указан месяц Ноябрь 2016, то формула в ячейке D20 должна давать результат количество календарных дней в Ноябре 2016 минус ячейку столбца О30 (количество праздничных дней в Ноябре 2016). Равно 30 минус 0 = 30. А если в ячейке В26 указан месяц Май 2017 то формула в ячейке D26 должна давать результат количество календарных дней в Мае 2017 минус ячейку столбца О24 (количество праздничных дней в Мае 2017). Равно 31 минус 3 = 28. И так далее.
Che79, Здравствуйте! Можете ли Вы немного изменить формулы для таблицы расчёта отпускных? Я немного изменил для наглядности и для дальнейшей работы таблицы праздничных дней. И прилагаю файл. В этом файле я добавил две таблицы в диапазонах 18:O32;R18:X32. В эти таблицы внёс название месяцев и даты праздников.
Помогите, пожалуйста, написать формулы для столбца D – календарные дни. Условия для заполнения этого столбца D: 1 условие: ячейки столбца D заполняются если заполнены ячейки столбца В: Если заполнена – не пустая ячейка В20 заполняется ячейка D20, А ЕЕСЛИ пустая ячейка В20 то и пустая ячейка D20. Если заполнена – не пустая ячейка В21 заполняется ячейка D21, А ЕЕСЛИ пустая ячейка В21 то и пустая ячейка D21. Если заполнена – не пустая ячейка В22 заполняется ячейка D22, А ЕЕСЛИ пустая ячейка В22 то и пустая ячейка D22. И так далее до ячейки D31.
2 условие: количество календарных дней в месяце, указанном в ячейках столбца В отнять (минус) количество праздничных дней, указанных в двух таблицах за 2016 и 2017 годы. Например, если в ячейке В20 указан месяц Ноябрь 2016, то формула в ячейке D20 должна давать результат количество календарных дней в Ноябре 2016 минус ячейку столбца О30 (количество праздничных дней в Ноябре 2016). Равно 30 минус 0 = 30. А если в ячейке В26 указан месяц Май 2017 то формула в ячейке D26 должна давать результат количество календарных дней в Мае 2017 минус ячейку столбца О24 (количество праздничных дней в Мае 2017). Равно 31 минус 3 = 28. И так далее.Никанор
Che79, Да! Так нужно – чудесно. Большое спасибо. Я проверил несколько вариантов. И для всех все месяцев результат правильный. А можно ли чуть -чуть усовершенствовать формулу для столбца В. У меня сейчас ячейки столбца В объединены с соседней ячейкой столбца С. Это сделано для того что бы если название месяца длинное что бы оно поместилось уже в объединённые ячейки. Вопрос: Можно ли изменить формулы для диапазона B20:C31, что бы не было объединений ячеек? Это возможно если в формуле присутствует слово ТЕКСТ.
В данном примере формулу для ячейки В31 изменил с =
Код
КОНМЕСЯЦА(E15;-1)
на
Код
=ТЕКСТ(ДАТАМЕС(E15;-1);"[$-419]ММММ ГГГГ")
И теперь не нужно объединять ячейки В31 и С31. А как изменить формулы в ячейках B20:C30 я не знаю. Подскажите пожалуйста, как в диапазон ячеек B20:C30 изменить формулы и дописать ТЕКСТ, что бы не объединять ячейки.
Che79, Да! Так нужно – чудесно. Большое спасибо. Я проверил несколько вариантов. И для всех все месяцев результат правильный. А можно ли чуть -чуть усовершенствовать формулу для столбца В. У меня сейчас ячейки столбца В объединены с соседней ячейкой столбца С. Это сделано для того что бы если название месяца длинное что бы оно поместилось уже в объединённые ячейки. Вопрос: Можно ли изменить формулы для диапазона B20:C31, что бы не было объединений ячеек? Это возможно если в формуле присутствует слово ТЕКСТ.
В данном примере формулу для ячейки В31 изменил с =
Код
КОНМЕСЯЦА(E15;-1)
на
Код
=ТЕКСТ(ДАТАМЕС(E15;-1);"[$-419]ММММ ГГГГ")
И теперь не нужно объединять ячейки В31 и С31. А как изменить формулы в ячейках B20:C30 я не знаю. Подскажите пожалуйста, как в диапазон ячеек B20:C30 изменить формулы и дописать ТЕКСТ, что бы не объединять ячейки.Никанор
Сообщение отредактировал Никанор - Вторник, 19.12.2017, 14:16
Объясняю. Первоначально, когда я создавал таблицу. И если название месяца длинное оно не помещалось в одну ячейку. Поэтому я объединил две ячейки. Но можно было и раздвинуть ячейку не объединяя двух ячеек. Это я делал просто для форматирования – удобного размещения на странице.
Иногда приходилось вырезать. И когда я вставлял, то появлялось диалоговое окно: "Данная операция приведёт к отмене объединения ячеек. Продолжить?" Как на 1 рисунке, что я добавил.
А потом, когда вставлял, то ячейки были уже не объединены и не было видно название месяцев. Как на рисунке 2, что я добавил. И мне вновь приходилось объединять ячейки. Что усложняло и занимало время. Вот поэтому я и хотел бы что бы ячейки не были объединены.
Вот я дал объяснение почему хотелось бы что бы в формулах возле месяца был ТЕКСТ.
Объясняю. Первоначально, когда я создавал таблицу. И если название месяца длинное оно не помещалось в одну ячейку. Поэтому я объединил две ячейки. Но можно было и раздвинуть ячейку не объединяя двух ячеек. Это я делал просто для форматирования – удобного размещения на странице.
Иногда приходилось вырезать. И когда я вставлял, то появлялось диалоговое окно: "Данная операция приведёт к отмене объединения ячеек. Продолжить?" Как на 1 рисунке, что я добавил.
А потом, когда вставлял, то ячейки были уже не объединены и не было видно название месяцев. Как на рисунке 2, что я добавил. И мне вновь приходилось объединять ячейки. Что усложняло и занимало время. Вот поэтому я и хотел бы что бы ячейки не были объединены.
Вот я дал объяснение почему хотелось бы что бы в формулах возле месяца был ТЕКСТ.Никанор
Che79, Здравствуйте! Извините, пожалуйста, за беспокойство. Помогите, пожалуйста, в следующем вопросе. Файл прилагаю. На «Лист2 Так было» до изменений. На Лист1 я немного усовершенствовал и сделал следующее: Первое: Слово – ТЕКСТ «на» которое было в ячейке Е14 я перенёс в ячейку F14. И в ячейке F14 изменилась формула на следующую:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " рабочих дней"
И теперь в ячейке F15 #ЗНАЧ! Помогите пожалуйста заменить формулу в ячейку F15, что бы не было #ЗНАЧ! Здесь должно быть как на листе 2 Так было: по 28.12.17 От этой ячейки зависит результат в ячейке В42 И теперь в ячейке В42 тоже #ЗНАЧ!
Я думаю, что при изменении формулы в ячейке F15 изменится #ЗНАЧ! на результат.
Che79, Здравствуйте! Извините, пожалуйста, за беспокойство. Помогите, пожалуйста, в следующем вопросе. Файл прилагаю. На «Лист2 Так было» до изменений. На Лист1 я немного усовершенствовал и сделал следующее: Первое: Слово – ТЕКСТ «на» которое было в ячейке Е14 я перенёс в ячейку F14. И в ячейке F14 изменилась формула на следующую:
Код
="на "&ОКРУГЛВВЕРХ((F13-F12)/365*24;0)& " рабочих дней"
И теперь в ячейке F15 #ЗНАЧ! Помогите пожалуйста заменить формулу в ячейку F15, что бы не было #ЗНАЧ! Здесь должно быть как на листе 2 Так было: по 28.12.17 От этой ячейки зависит результат в ячейке В42 И теперь в ячейке В42 тоже #ЗНАЧ!
Я думаю, что при изменении формулы в ячейке F15 изменится #ЗНАЧ! на результат.Никанор
Здравствуйте уважаемые помощники! В продолжение темы. Как изменить формулу в ячейке F15 что бы считало не рабочие дни, а календарные. В ячейке F14 – результат: «20 календарных дней». Результат в ячейке F15 должен быть: 10 февраля. Файл прилагаю.
Здравствуйте уважаемые помощники! В продолжение темы. Как изменить формулу в ячейке F15 что бы считало не рабочие дни, а календарные. В ячейке F14 – результат: «20 календарных дней». Результат в ячейке F15 должен быть: 10 февраля. Файл прилагаю.Никанор
, если честно, на самом файле не проверял... UPD Или можно схулиганить через форматы ячеек. Посмотрите формулы и ФЯ в зелёных ячейках в приложенном файле.
, если честно, на самом файле не проверял... UPD Или можно схулиганить через форматы ячеек. Посмотрите формулы и ФЯ в зелёных ячейках в приложенном файле.Che79
Che79, так! Я проверил. Спасибо огромное за молниеносный правильный ответ на 8-ой минуте после моего вопроса! Вы правильно написали, даже не проверяя на файле.
А если бы в ячейке F15 была дата без текста (формат ячейки Дата), то формула была бы такой:
Код
=E15+F14-1
Che79, так! Я проверил. Спасибо огромное за молниеносный правильный ответ на 8-ой минуте после моего вопроса! Вы правильно написали, даже не проверяя на файле.
А если бы в ячейке F15 была дата без текста (формат ячейки Дата), то формула была бы такой:
даёт правильный результат и считает правильно. Она добавляет календарные дни для расчёта конца отпуска. Ячейка F14 – сколько календарных дней в отпуске. В этой ячейке в формуле присутствует ТЕКСТ. Ячейка Е15 – начало отпуска; Ячейка F15 – дата конца отпуска включительно отпуска; Но по законодательству, ЕСЛИ на время отпуска припадают праздничные дни, то отпуск увеличивается на количество праздничных дней, которые припадают в моём примере в периоде Е15 и F15 включительно. Праздничные дни находятся в диапазоне ячеек I20:AG31. То есть надо, что бы формула добавляла количество праздничных дней, которые припадают на период отпуска. Эти дни находятся в диапазоне ячеек I20:AG31.
Подобная формула, которая учитывает праздничные дни, находится в диапазоне ячеек E20:E31. Вот эта формула
Как должно быть в приложенных двух файлах. В примере формула должна добавить 3 (три) дня, так как на период с 22 декабря до 8 января припадает 3 (три) праздника: 25 декабря, 1 января и 7 января. И результат должен быть 12 января.
Вторую формулу, которую нужно изменить. В ячейке F14 – сколько календарных дней в отпуске. В этой ячейке формат ячейки дата и отсутствует ТЕКСТ.
Здравствуйте! Помогите, пожалуйста, ещё усовершенствовать две формулы.
даёт правильный результат и считает правильно. Она добавляет календарные дни для расчёта конца отпуска. Ячейка F14 – сколько календарных дней в отпуске. В этой ячейке в формуле присутствует ТЕКСТ. Ячейка Е15 – начало отпуска; Ячейка F15 – дата конца отпуска включительно отпуска; Но по законодательству, ЕСЛИ на время отпуска припадают праздничные дни, то отпуск увеличивается на количество праздничных дней, которые припадают в моём примере в периоде Е15 и F15 включительно. Праздничные дни находятся в диапазоне ячеек I20:AG31. То есть надо, что бы формула добавляла количество праздничных дней, которые припадают на период отпуска. Эти дни находятся в диапазоне ячеек I20:AG31.
Подобная формула, которая учитывает праздничные дни, находится в диапазоне ячеек E20:E31. Вот эта формула
Как должно быть в приложенных двух файлах. В примере формула должна добавить 3 (три) дня, так как на период с 22 декабря до 8 января припадает 3 (три) праздника: 25 декабря, 1 января и 7 января. И результат должен быть 12 января.
Вторую формулу, которую нужно изменить. В ячейке F14 – сколько календарных дней в отпуске. В этой ячейке формат ячейки дата и отсутствует ТЕКСТ.Никанор
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
