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

Вход

Регистрация

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

 

= Мир MS Excel/контроль оплаты по датам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » контроль оплаты по датам (Условное Форматирование/Conditional Formattings)
контроль оплаты по датам
antoshavaluykin Дата: Четверг, 13.05.2021, 18:04 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Друзья, добрый день.
Прошу помочь сделать небольшую "считалку"
Необходимые условия:

- в колонке Н - необходимо, чтобы дата в начале каждого месяца (к примеру первого числа) менялась на 25 число текущего месяца (это дата , до которой нужно провести оплату);
- установить правило , чтобы за 10 дней до значения в колонке Н (на основании текущей даты (выделено желтым) подсвечивалось цветом вся строка;
- правила должны работать исключая выходные дни (если попадает выходной день , то подсветка срабатывает раньше).

Буду очень благодарен.
К сообщению приложен файл: 0829366.xlsx(13.0 Kb)
 
Ответить
СообщениеДрузья, добрый день.
Прошу помочь сделать небольшую "считалку"
Необходимые условия:

- в колонке Н - необходимо, чтобы дата в начале каждого месяца (к примеру первого числа) менялась на 25 число текущего месяца (это дата , до которой нужно провести оплату);
- установить правило , чтобы за 10 дней до значения в колонке Н (на основании текущей даты (выделено желтым) подсвечивалось цветом вся строка;
- правила должны работать исключая выходные дни (если попадает выходной день , то подсветка срабатывает раньше).

Буду очень благодарен.

Автор - antoshavaluykin
Дата добавления - 13.05.2021 в 18:04
ZetMenChavo Дата: Четверг, 13.05.2021, 22:31 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Здравствуйте. Уточните пожалуйста,
Цитата
исключая выходные дни
т.е. за 10 дней считаются только 10 будних дней до 25 числа? И, скажем, если в этот отрезок войдет два выходных, то перекраситься строки должны 13го (25-10-2) и т.д., верно?
 
Ответить
Сообщениеantoshavaluykin, Здравствуйте. Уточните пожалуйста,
Цитата
исключая выходные дни
т.е. за 10 дней считаются только 10 будних дней до 25 числа? И, скажем, если в этот отрезок войдет два выходных, то перекраситься строки должны 13го (25-10-2) и т.д., верно?

Автор - ZetMenChavo
Дата добавления - 13.05.2021 в 22:31
ZetMenChavo Дата: Четверг, 13.05.2021, 22:51 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Для колонки H использовал формулу
Код
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());25)

а для перекрашивания строк Условным форматированием
Код
=ЧИСТРАБДНИ($C$2;$H$9)<=10

эта функция считает количество рабочих дней между двумя датами
К сообщению приложен файл: 4751712.xlsx(16.7 Kb)
 
Ответить
Сообщение antoshavaluykin, Для колонки H использовал формулу
Код
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());25)

а для перекрашивания строк Условным форматированием
Код
=ЧИСТРАБДНИ($C$2;$H$9)<=10

эта функция считает количество рабочих дней между двумя датами

Автор - ZetMenChavo
Дата добавления - 13.05.2021 в 22:51
antoshavaluykin Дата: Пятница, 14.05.2021, 09:43 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, спасибо огромное! очень помогли!
 
Ответить
СообщениеZetMenChavo, спасибо огромное! очень помогли!

Автор - antoshavaluykin
Дата добавления - 14.05.2021 в 09:43
antoshavaluykin Дата: Пятница, 14.05.2021, 10:06 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, подскажите, а можно еще добавить две крайние колонки , в одной установить слово "пора оплачивать" при выполненном условии (когда перекрашены строки) и в следующем вручную выбирать "выполнено" если уже оплатили ( пропадает окраска )? буду очень благодарен
 
Ответить
СообщениеZetMenChavo, подскажите, а можно еще добавить две крайние колонки , в одной установить слово "пора оплачивать" при выполненном условии (когда перекрашены строки) и в следующем вручную выбирать "выполнено" если уже оплатили ( пропадает окраска )? буду очень благодарен

Автор - antoshavaluykin
Дата добавления - 14.05.2021 в 10:06
ZetMenChavo Дата: Пятница, 14.05.2021, 10:39 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Да можно. Формулу в УФ надо изменить на
Код
=(ЧИСТРАБДНИ($C$2;$H$9)<=10)*("ЯЧЕЙКА"<>"выполнено")

Где "ЯЧЕЙКА" надо заменить на адрес ячейки где будет искаться слово "выполнено" при этом не забудьте поставить $ перед столбцом ($A1) в ссылке, что бы формула искала только в нужном столбце.
А в самой табе нужно будет использовать ф.ЕСЛИ с тем же условием что и в УФ
Код
=ЕСЛИ((ЧИСТРАБДНИ($C$2;$H$9)<=10)*("ЯЧЕЙКА"<>"выполнено");"пора оплачивать";"")


Сообщение отредактировал ZetMenChavo - Пятница, 14.05.2021, 10:51
 
Ответить
Сообщениеantoshavaluykin, Да можно. Формулу в УФ надо изменить на
Код
=(ЧИСТРАБДНИ($C$2;$H$9)<=10)*("ЯЧЕЙКА"<>"выполнено")

Где "ЯЧЕЙКА" надо заменить на адрес ячейки где будет искаться слово "выполнено" при этом не забудьте поставить $ перед столбцом ($A1) в ссылке, что бы формула искала только в нужном столбце.
А в самой табе нужно будет использовать ф.ЕСЛИ с тем же условием что и в УФ
Код
=ЕСЛИ((ЧИСТРАБДНИ($C$2;$H$9)<=10)*("ЯЧЕЙКА"<>"выполнено");"пора оплачивать";"")

Автор - ZetMenChavo
Дата добавления - 14.05.2021 в 10:39
ZetMenChavo Дата: Пятница, 14.05.2021, 10:50 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Так же советую посмотреть на Ютюбе как работают формулы ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД. Из описания в Эксель ясно что там можно дополнительно настраивать выходные и праздники, но так как я сам узнал про них лишь когда искал способ решения вашего вопроса, то в полной мере их возможности мне пока не известны, а вам они явно пригодятся


Сообщение отредактировал ZetMenChavo - Пятница, 14.05.2021, 10:57
 
Ответить
Сообщениеantoshavaluykin, Так же советую посмотреть на Ютюбе как работают формулы ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД. Из описания в Эксель ясно что там можно дополнительно настраивать выходные и праздники, но так как я сам узнал про них лишь когда искал способ решения вашего вопроса, то в полной мере их возможности мне пока не известны, а вам они явно пригодятся

Автор - ZetMenChavo
Дата добавления - 14.05.2021 в 10:50
antoshavaluykin Дата: Понедельник, 17.05.2021, 11:21 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, а если Вас не затруднит можно это транслировать в файл? что-то у меня не получается...буду признателен
 
Ответить
СообщениеZetMenChavo, а если Вас не затруднит можно это транслировать в файл? что-то у меня не получается...буду признателен

Автор - antoshavaluykin
Дата добавления - 17.05.2021 в 11:21
antoshavaluykin Дата: Понедельник, 17.05.2021, 11:23 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, очень интересная тема, не часто сталкиваюсь, но изучу обязательно)
 
Ответить
СообщениеZetMenChavo, очень интересная тема, не часто сталкиваюсь, но изучу обязательно)

Автор - antoshavaluykin
Дата добавления - 17.05.2021 в 11:23
ZetMenChavo Дата: Понедельник, 17.05.2021, 11:36 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Еще раз здравствуйте).
Цитата
а если Вас не затруднит можно это транслировать в файл?

Странно, я помню что делал это в вашем файле и сохранял его, похоже забыл тогда его прикрепить. Если ещё нужно то вот.
Я ещё в столбце J сделал раскрывающиеся списки, состоящие правда из одного слова "выполнено", но зато так проще его вводить чем каждый раз вручную.
К сообщению приложен файл: 2935151.xlsx(17.1 Kb)
 
Ответить
Сообщениеantoshavaluykin, Еще раз здравствуйте).
Цитата
а если Вас не затруднит можно это транслировать в файл?

Странно, я помню что делал это в вашем файле и сохранял его, похоже забыл тогда его прикрепить. Если ещё нужно то вот.
Я ещё в столбце J сделал раскрывающиеся списки, состоящие правда из одного слова "выполнено", но зато так проще его вводить чем каждый раз вручную.

Автор - ZetMenChavo
Дата добавления - 17.05.2021 в 11:36
antoshavaluykin Дата: Понедельник, 17.05.2021, 12:13 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, Круто!!! Спасибо ВАм огромное!
 
Ответить
СообщениеZetMenChavo, Круто!!! Спасибо ВАм огромное!

Автор - antoshavaluykin
Дата добавления - 17.05.2021 в 12:13
antoshavaluykin Дата: Понедельник, 17.05.2021, 16:20 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, сформировал итоговый документ и поменял в одной колонке "срок" дату на 05.05.2021 и сразу не работает окраска всех строк, (попробовал поиграть вручную с датой в "С2")
К сообщению приложен файл: 8033392.xlsx(17.1 Kb)


Сообщение отредактировал antoshavaluykin - Понедельник, 17.05.2021, 16:20
 
Ответить
СообщениеZetMenChavo, сформировал итоговый документ и поменял в одной колонке "срок" дату на 05.05.2021 и сразу не работает окраска всех строк, (попробовал поиграть вручную с датой в "С2")

Автор - antoshavaluykin
Дата добавления - 17.05.2021 в 16:20
ZetMenChavo Дата: Вторник, 18.05.2021, 10:44 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Нашёл в чём была проблема, в формуле УФ
Код
=(ЧИСТРАБДНИ($C$2;$H$9)<=10)*($J9<>"выполнено")

ссылка во втором параметре в функции ЧИСТРАБДНИ была абсолютной ($H$9) из-за чего все строки реагировали лишь на изменения даты только в девятой строке, я убрал знак $ перед номером строки, так что теперь будет работать как надо.

Плюс я ещё подправил УФ что бы он выключался если дата в таблице указана прошедшая
Код
=(ЧИСТРАБДНИ($C$2;$H9)<=10)*($J9<>"выполнено")*($C$2<=$H9)

А что бы было видно где прошедшие даты, сделал ещё одно УФ, перекрашивающее ячейки в красный

Если в колонке "сроки" планируете выставлять лишь 25 число, то редактировать месяц вручную не придётся, т.к. он будет меняться сам в начале следующего месяца
Код
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());25)

Но если планируете ставить числа более близкие к началу месяца, то придется переделать функцию что бы работало как надо.
А то, к примеру, хотите что бы отсчитывалось от 5-го числа следующего месяца, но поменяв 25 на 5 получается прощедшее число, т.к. месяц функцией высчитывается текущий.
К сообщению приложен файл: 4874573.xlsx(17.0 Kb)


Сообщение отредактировал ZetMenChavo - Вторник, 18.05.2021, 13:52
 
Ответить
Сообщениеantoshavaluykin, Нашёл в чём была проблема, в формуле УФ
Код
=(ЧИСТРАБДНИ($C$2;$H$9)<=10)*($J9<>"выполнено")

ссылка во втором параметре в функции ЧИСТРАБДНИ была абсолютной ($H$9) из-за чего все строки реагировали лишь на изменения даты только в девятой строке, я убрал знак $ перед номером строки, так что теперь будет работать как надо.

Плюс я ещё подправил УФ что бы он выключался если дата в таблице указана прошедшая
Код
=(ЧИСТРАБДНИ($C$2;$H9)<=10)*($J9<>"выполнено")*($C$2<=$H9)

А что бы было видно где прошедшие даты, сделал ещё одно УФ, перекрашивающее ячейки в красный

Если в колонке "сроки" планируете выставлять лишь 25 число, то редактировать месяц вручную не придётся, т.к. он будет меняться сам в начале следующего месяца
Код
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());25)

Но если планируете ставить числа более близкие к началу месяца, то придется переделать функцию что бы работало как надо.
А то, к примеру, хотите что бы отсчитывалось от 5-го числа следующего месяца, но поменяв 25 на 5 получается прощедшее число, т.к. месяц функцией высчитывается текущий.

Автор - ZetMenChavo
Дата добавления - 18.05.2021 в 10:44
ZetMenChavo Дата: Вторник, 18.05.2021, 13:55 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin, Вот файл для случаев что я описал выше. Добавил новый столбец, где число месяца указывается отдельно, а дата в столбце срок уже формируется в зависимости от того прошедшее это число или нет.
К сообщению приложен файл: 9570898.xlsx(17.1 Kb)
 
Ответить
Сообщениеantoshavaluykin, Вот файл для случаев что я описал выше. Добавил новый столбец, где число месяца указывается отдельно, а дата в столбце срок уже формируется в зависимости от того прошедшее это число или нет.

Автор - ZetMenChavo
Дата добавления - 18.05.2021 в 13:55
antoshavaluykin Дата: Вторник, 18.05.2021, 17:07 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, вообще все отлично!!! Вы очень помогли!! Теперь у меня появился наглядный опыт работы с таким форматом. Может еще кому пригодится Ваш совет и большая проделанная работа!
 
Ответить
СообщениеZetMenChavo, вообще все отлично!!! Вы очень помогли!! Теперь у меня появился наглядный опыт работы с таким форматом. Может еще кому пригодится Ваш совет и большая проделанная работа!

Автор - antoshavaluykin
Дата добавления - 18.05.2021 в 17:07
antoshavaluykin Дата: Среда, 19.05.2021, 11:16 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
ZetMenChavo, а в последнем файле Вы уже скорректировали эту рекомендацию ? "Но если планируете ставить числа более близкие к началу месяца, то придется переделать функцию что бы работало как надо.
А то, к примеру, хотите что бы отсчитывалось от 5-го числа следующего месяца, но поменяв 25 на 5 получается прощедшее число, т.к. месяц функцией высчитывается текущий"
ставлю в колонке день 5 число и формула пересчитывает на след месяц уже ...
 
Ответить
СообщениеZetMenChavo, а в последнем файле Вы уже скорректировали эту рекомендацию ? "Но если планируете ставить числа более близкие к началу месяца, то придется переделать функцию что бы работало как надо.
А то, к примеру, хотите что бы отсчитывалось от 5-го числа следующего месяца, но поменяв 25 на 5 получается прощедшее число, т.к. месяц функцией высчитывается текущий"
ставлю в колонке день 5 число и формула пересчитывает на след месяц уже ...

Автор - antoshavaluykin
Дата добавления - 19.05.2021 в 11:16
ZetMenChavo Дата: Среда, 19.05.2021, 11:27 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 71
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
antoshavaluykin,
Цитата
а в последнем файле Вы уже скорректировали эту рекомендацию ?

Да, файл 9570898.xlsx из предыдущего сообщения, увидите там столбец "День", значит это тот.
 
Ответить
Сообщениеantoshavaluykin,
Цитата
а в последнем файле Вы уже скорректировали эту рекомендацию ?

Да, файл 9570898.xlsx из предыдущего сообщения, увидите там столбец "День", значит это тот.

Автор - ZetMenChavo
Дата добавления - 19.05.2021 в 11:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » контроль оплаты по датам (Условное Форматирование/Conditional Formattings)
  • Страница 1 из 1
  • 1
Поиск:

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