Изменение цвета ячейки в excel в зависимости от даты
Как изменить указанное значение или цвет ячейки при установленном флажке в Excel?
В этой статье говорится об изменении значения или цвета указанной ячейки с помощью флажка. Например, когда установлен определенный флажок, значение или цвет фона указанной ячейки будет изменен автоматически. Пожалуйста, следуйте приведенным ниже методам шаг за шагом, чтобы решить эту проблему.
Измените указанное значение ячейки, когда флажок установлен с формулой
Предположим, вам нужно заполнить ячейку A1 значением «test», когда установлен флажок 1. И очистите ячейку при снятии флажка. Пожалуйста, сделайте следующее.
Вам необходимо сначала связать флажок с определенной ячейкой, а затем применить формулу для ее достижения.
1. Чтобы связать флажок с определенной ячейкой, выберите ее, затем введите = ссылка на ячейку в строке формул и нажмите клавишу Enter. Здесь я привязываю флажок 1 к ячейке C2 с вводом = C2 в панель формул после установки флажка.
Для флажка ActiveX Control щелкните его правой кнопкой мыши и выберите Свойства из контекстного меню. И в Свойства диалоговом окне введите связанную ячейку в LinkedCell поле, а затем закройте диалоговое окно. Смотрите скриншот:
2. Выберите ячейку, в которой нужно изменить ее значение (здесь я выбираю A1), затем введите формулу. = ЕСЛИ (C2; "Тест"; "") в панель формул и нажмите Enter ключ. Смотрите скриншот:
Внимание: В формуле C2 - это ячейка, связанная с флажком 1, «Test» - это значение, которое необходимо отобразить в ячейке A1, когда флажок установлен.
Вы можете увидеть результаты в виде снимков экрана ниже, показанных после установки или снятия соответствующего флажка.
Измените указанное значение ячейки, когда флажок установлен с кодом VBA
Вы также можете изменить указанное значение ячейки, если установлен флажок с кодом VBA. Пожалуйста, сделайте следующее.
1. Щелкните правой кнопкой мыши вкладку «Лист» со значением ячейки, которое необходимо изменить в зависимости от флажка, затем щелкните Просмотреть код из контекстного меню.
2. в Microsoft Visual Basic для приложений окна, скопируйте и вставьте приведенный ниже код VBA в Код: окно.
Код VBA: изменение указанного значения ячейки при установленном флажке
Внимание: В коде Чекбокс1 это имя Флажок (элементы управления ActiveX). Вы можете изменить его по своему усмотрению.
3. нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.
4. Выберите пустую ячейку (A1), значение которой вы хотите изменить на основе флажка, и установите флажок 1 на своем листе. Теперь Kutools for Excel появится диалоговое окно, введите значение, которое вы хотите заполнить в выбранной пустой ячейке, а затем щелкните значок OK кнопка. Смотрите скриншот:
Затем указанное значение сразу отображается в выбранной ячейке. При снятии флажка ячейка будет пустой.
Внимание: Этот код будет напоминать вам вводить текстовое значение по кругу, когда вы каждый раз устанавливаете флажок.
Измените указанный цвет ячейки, когда флажок установлен с кодом VBA
В этом разделе показано, как изменить указанный цвет ячейки, когда флажок установлен с кодом VBA.
1. Щелкните правой кнопкой мыши Вкладка листа с цветом ячейки, который необходимо изменить в зависимости от флажка, затем щелкните Просмотреть код из контекстного меню.
2. в Microsoft Visual Basic для приложений окна, скопируйте и вставьте приведенный ниже код VBA в окно кода.
Код VBA: изменение указанного цвета ячейки при установленном флажке
Внимание: В коде Чекбокс1 это имя Флажок (элементы управления ActiveX). Вы можете изменить его по своему усмотрению.
3. Выберите ячейку, для которой нужно изменить цвет фона, затем установите флажок. Вы можете видеть, что цвет заливки выбранной ячейки изменился на красный. Выбранная ячейка будет заполнена без цвета фона после снятия флажка. Смотрите скриншоты:
Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.
Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel
Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.
Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:
- Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
- В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
- В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
А в результате выделились все даты актуальность которых превышает 90 дней.
Иногда во время анализа данных необходимо выделить цветом даты за определенный период времени. Еще лучше если выделение цветом дат будет выполнятся автоматически по условию при каждом изменении параметров начала и конца периода времени.
Как выделить даты цветом только за определенный промежуток времени в Excel
Ниже на рисунке показано, как можно применить условное форматирование для автоматического выделения цветом диапазона значений с днями между начальной и конечной датой. Если искомая дата начала и/или конца периода будет изменятся, тогда условное форматирование будет приспособлено к новым условиям и автоматически подсветит цветом все соответственные ячейки с новым условием.
Чтобы создать такое правило автоматического форматирования цветом по условию, выполните следующие действия:
- Выделите целевой диапазон ячеек, в котором нужно подсветить даты цветом (в данном примере это D2:D17). После чего выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Возникнет окошко «Создание правила форматирования ячеек» точно так, как изображено внизу на рисунке:
- В появившемся окне укажите на опцию «Использовать формулу для определения форматируемых ячеек». Таким образом у нас появилась возможность применять формулу с логическим выражением для определения условий форматирования. Если в своем итоговом результате вычисления формула будет возвращать логическое значение ИСТИНА для значения текущей ячейки, тогда сразу же будет для нее применен формат, заданный пользователем в настройках данного инструмента.
- Заполните поле ввода следующей формулой:
В результате из списка разных дат за разные года выделились цветом только даты за период 2010-2012 год.
Добрый день!
Есть список автомобилей с дотами окончания страховок. Нужно сделать, чтоб в обычном состоянии цвет ячейки с датой был зеленым, за две недели до окончания - желтый, за неделю - красный, а на следующий день по окончании черный с белым шрифтом.
Заранее спасибо!
Добрый день!
Есть список автомобилей с дотами окончания страховок. Нужно сделать, чтоб в обычном состоянии цвет ячейки с датой был зеленым, за две недели до окончания - желтый, за неделю - красный, а на следующий день по окончании черный с белым шрифтом.
Заранее спасибо! Verter
_Boroda_, Приятного время суток. Ув. Гуру Ms Excel
подскажите пожалуйста , не могу можно ли чтоб ячейка сама меняла свет в зависимости от даты ?
к примеру в ячейчи ставлю дату , допустим 01.01.2019 он заливается зелёным , по истечением времени , допустим 2 дня , она зальётся красным автомотически.
к примеру прилепляю фаил , как хотелось бы видеть данную таблицу , там где ячейки залиты уже , это пример.
Если это услуга помощи платная . давай те обговорим детали
_Boroda_, Приятного время суток. Ув. Гуру Ms Excel
подскажите пожалуйста , не могу можно ли чтоб ячейка сама меняла свет в зависимости от даты ?
к примеру в ячейчи ставлю дату , допустим 01.01.2019 он заливается зелёным , по истечением времени , допустим 2 дня , она зальётся красным автомотически.
к примеру прилепляю фаил , как хотелось бы видеть данную таблицу , там где ячейки залиты уже , это пример.
Если это услуга помощи платная . давай те обговорим детали zTheBlackCat
zTheBlackCat, можно, но только
- Прочитайте Правила форума
- Создайте свою тему согласно п.5q Правил форума
От какой даты пляшем? В какой ячейке та дата, которая нулевая? Или для разных раскрасок это разные даты?
Если вопрос с ответом не в ветке "Фриланс", то благодарность по желанию
zTheBlackCat, можно, но только
- Прочитайте Правила форума
- Создайте свою тему согласно п.5q Правил форума
От какой даты пляшем? В какой ячейке та дата, которая нулевая? Или для разных раскрасок это разные даты?
Если вопрос с ответом не в ветке "Фриланс", то благодарность по желанию _Boroda_
Если вопрос с ответом не в ветке "Фриланс", то благодарность по желанию Автор - _Boroda_
Дата добавления - 12.11.2018 в 11:28
_Boroda_, простите пожалуйста , в предь такое не повториться , невнимательно прочитал правило
мне по новой тему создать ?
_Boroda_, простите пожалуйста , в предь такое не повториться , невнимательно прочитал правило
мне по новой тему создать ? zTheBlackCat
Да, конечно
и я дописал там выше
От какой даты пляшем? В какой ячейке та дата, которая нулевая? Или для разных раскрасок это разные даты?
Поясните подробно почему так покрашена каждая ячейка
Только не здесь, а в новой теме
Да, конечно
и я дописал там выше
От какой даты пляшем? В какой ячейке та дата, которая нулевая? Или для разных раскрасок это разные даты?
Поясните подробно почему так покрашена каждая ячейка
Только не здесь, а в новой теме _Boroda_
Как мы знаем, условное форматирование может автоматически изменять формат ячейки в зависимости от содержимого ячейки. В этом руководстве я расскажу, как условное форматирование ячеек на основе даты в Excel.
Даты условного форматирования равны / больше / старше СЕГОДНЯ
Для условного форматирования дат необходимо создать новое правило.
1. Выберите ячейки, в которые нужно добавить условное форматирование, нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. in Выберите тип правила раздел, затем выберите одну формулу, так как вам нужно ввести Формат значений, где эта формула истинна текстовое окно.
- равно сегодня: =$B2=TODAY()
- больше, чем сегодня: =$B2>TODAY()
- Старше сегодняшнего: =$B2
3. Нажмите Формат идти на Формат ячеек В диалоговом окне выберите один формат, в котором вы хотите выделить ячейки по мере необходимости.
4. Нажмите OK > OK. Даты отформатированы.
Внимание: Сегодня 15
Условное форматирование прошедших дат
Если вы хотите условное форматирование прошедших дат, вы можете сделать следующее в диалоговом окне «Новое правило форматирования».
1. Выберите одну из формул ниже по своему усмотрению и введите ее в поле Формат значений, где эта формула истинна текстовое окно.
- Более 30 дней назад: =TODAY()-$B2>30
- С 30 по 15 суток назад включительно: =AND(TODAY()-$B2>=15, TODAY()-$B2 <=30)
- Менее 15 дней назад: =AND(TODAY()-$B2>=1, TODAY()-$B2 <15)
2. Затем нажмите Формат выбрать один формат в Формат ячеек Диалог.
3. Нажмите OK > OK чтобы закрыть диалоги.
Внимание: Сегодня 15
Условное форматирование будущих дат
Для условного форматирования будущих дат вы можете сделать следующее:
1. Выберите одну из формул ниже по своему усмотрению и введите ее в поле Формат значений, где эта формула истинна текстовое окно.
- Произойдет более чем через 30 дней: =$B2-TODAY()>30
- От 30 до 15 дней включительно: =AND($B2-TODAY()>=15, $B2-TODAY() <=30)
- Менее чем за 15 дней: =AND($B2-TODAY()>=1, $B2-TODAY() <15)
2. Затем нажмите Формат выбрать один формат в Формат ячеек Диалог.
3. Нажмите OK > OK чтобы закрыть диалоги.
Условное форматирование между двумя датами
Чтобы условное форматирование между двумя датами, выберите ячейки и используйте эту формулу, =AND($B2>=$D$2,$B2 <=$E$2)D2 - дата начала, E2 - дата окончания. Затем отформатируйте ячейки в нужном вам формате.
Условное форматирование на основе даты в другом столбце
Если вы хотите условное форматирование ячеек на основе даты в другом столбце, вы можете сделать это.
1. Предположим, вы хотите выделить продажи в столбце C, а относительные даты в столбце B - 16/8/2018, сначала выберите диапазон столбца C.
2. Включите диалоговое окно «Новое правило форматирования» и введите эту формулу. =$B2=DATEVALUE("8/16/2018") в Формат значений, где эта формула истинна текстовое поле, затем отформатируйте ячейки в Формат ячеек Диалог.
Примечание: В приведенных выше формулах B2 - это первая ячейка даты выбранного диапазона дат.
Читайте также: