Как сделать в excel чтобы ячейка меняла цвет при определенном значении даты
Иногда во время анализа данных необходимо выделить цветом даты за определенный период времени. Еще лучше если выделение цветом дат будет выполнятся автоматически по условию при каждом изменении параметров начала и конца периода времени.
Как выделить даты цветом только за определенный промежуток времени в Excel
Ниже на рисунке показано, как можно применить условное форматирование для автоматического выделения цветом диапазона значений с днями между начальной и конечной датой. Если искомая дата начала и/или конца периода будет изменятся, тогда условное форматирование будет приспособлено к новым условиям и автоматически подсветит цветом все соответственные ячейки с новым условием.
Чтобы создать такое правило автоматического форматирования цветом по условию, выполните следующие действия:
- Выделите целевой диапазон ячеек, в котором нужно подсветить даты цветом (в данном примере это D2:D17). После чего выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Возникнет окошко «Создание правила форматирования ячеек» точно так, как изображено внизу на рисунке:
- В появившемся окне укажите на опцию «Использовать формулу для определения форматируемых ячеек». Таким образом у нас появилась возможность применять формулу с логическим выражением для определения условий форматирования. Если в своем итоговом результате вычисления формула будет возвращать логическое значение ИСТИНА для значения текущей ячейки, тогда сразу же будет для нее применен формат, заданный пользователем в настройках данного инструмента.
- Заполните поле ввода следующей формулой:
В результате из списка разных дат за разные года выделились цветом только даты за период 2010-2012 год.
Как мы знаем, условное форматирование может автоматически изменять формат ячейки в зависимости от содержимого ячейки. В этом руководстве я расскажу, как условное форматирование ячеек на основе даты в Excel.
Даты условного форматирования равны / больше / старше СЕГОДНЯ
Для условного форматирования дат необходимо создать новое правило.
1. Выберите ячейки, в которые нужно добавить условное форматирование, нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. in Выберите тип правила раздел, затем выберите одну формулу, так как вам нужно ввести Формат значений, где эта формула истинна текстовое окно.
- равно сегодня: =$B2=TODAY()
- больше, чем сегодня: =$B2>TODAY()
- Старше сегодняшнего: =$B2<TODAY()
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 - это первая ячейка даты выбранного диапазона дат.
Можно выделить данные в ячейках с помощью кнопки Цвет заливки, чтобы добавить или изменить цвет фона или узор в ячейках. Вот как это сделать:
Выберите ячейки, которые нужно выделить.
Чтобы использовать другой цвет фона для всего таблицы, нажмите кнопку Выбрать все. При этом линии сетки будут скроются, но вы сможете улучшить читаемость, отобразив границы ячеек вокруг всех ячеек.
Щелкните Главная > стрелку рядом с кнопкой Цвет заливки или нажмите клавиши ALT+H, H.
Выберите нужный цвет в группе Цвета темы или Стандартные цвета.
Чтобы использовать дополнительный цвет, выберите команду Другие цвета, а затем в диалоговом окне Цвета выберите нужный цвет.
Совет: Чтобы применить последний выбранный цвет, достаточно нажать кнопку Цвет заливки . Кроме того, в группе Последние цвета доступны до 10 цветов, которые вы выбирали в последнее время.
Применение узора или способов заливки
Если вас не устраивает сплошная заливка цветом, попробуйте применить узор или один из доступных способов заливки.
Выделите ячейку или диапазон ячеек, которые нужно отформатировать.
На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F.
На вкладке Заливка выберите в разделе Цвет фона нужный цвет.
Чтобы использовать двухцветный узор, выберите цвет в поле Цвет узора, а затем выберите сам узор в поле Узор.
Чтобы создать узор со специальными эффектами, нажмите кнопку Способы заливки и выберите нужные параметры.
Совет: В поле Образец можно просмотреть выбранный фон, узор и способ заливки.
Удаление цвета, узора и способа заливки из ячеек
Чтобы удалить все цвета фона, узоры и способы заливки, просто выделите ячейки. На вкладке Главная нажмите стрелку рядом с кнопкой Цвет заливки и выберите пункт Нет заливки.
Цветная печать ячеек, включая цвет фона, узор и способ заливки
Откройте вкладку Разметка страницы и нажмите кнопку вызова диалогового окна Параметры страницы.
На вкладке Лист в группе Печать снимите флажки черно-белая и черновая.
Примечание: Если цвета на листе не отображаются, возможно, выбран высококонтрастный режим. Если цвета не отображаются при предварительном просмотре, возможно, не выбран цветной принтер.
Если вы хотите выделить текст или числа, чтобы сделать данные более заметными, попробуйте изменить цвет шрифта или добавить цвет фона к ячейке или диапазону ячеек.
Вы ячейка или диапазон ячеек, для которых нужно добавить цвет заливки.
На вкладке Главная нажмите кнопку Цвет заливкии выберите нужный цвет.
Примечание: Эффекты узорной заливки для цветов фона недоступны для Excel в Интернете. Если применить любой из Excel на компьютере, он не будет отображаться в браузере.
Удаление цвета заливки
Если вы решите, что цвет заливки не нужен сразу после его вжатия, просто нажмите кнопку Отменить.
Чтобы позже удалить цвет заливки, вы выберите ячейку или диапазон ячеок, которые вы хотите изменить, и нажмите кнопку Очистить > Очистить форматы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Microsoft Office Excel может содержать огромные таблицы с большим количеством информации. Для лучшего восприятия данных целесообразно использовать средства визуализации и окрашивания нужных областей. Однако вручную раскрашивать каждую ячейку слишком долго и трудоемко. Поэтому сегодня разберемся, как сделать в excel цвет ячейки в зависимости от значения.
Инструменты поиска
Самый простой способ задать цвет для какой-либо ячейки – найти нужное значение и воспользоваться инструментами форматирования. По условиям задачи необходимо выделить магазины по показателям прибыли.
1. Выделяете последний столбец и нажимаете кнопку Найти и выделить на главной вкладке панели инструментов.
На заметку! Можно воспользоваться сочетанием клавиш Ctrl+F.
2. Раскрываете настройки функции через кнопку Параметры, выбираете область поиска по значениям, а блоке Найти вписываете число с единицей в начале. Знаки вопроса нужны для обозначения разрядности цифр.
3. Нажимаете кнопку Найти все и, поставив курсор в поле ниже, используете сочетание клавиш Ctrl+A, чтобы выделить все результаты поиска, которые будут также подсвечены на рабочем листе.
4. Сразу же переходите к кнопке заливки фона и выбираете нужный цвет. Только после этого закрываете окно поиска.
5. Аналогично делаете с магазинами, у которых прибыль больше двадцати и тридцати тысяч соответственно.
В итоге получается разноцветный столбец прибыли, по которому сразу понятно, какой магазин работает лучше.
Недостатком этого метода является то, что при изменении значения, окраска остается той же. Поэтому ниже рассмотрим, как сделать так, чтобы цвет ячейки excel менялся при изменении данных внутри.
Форматирование
Чтобы менять окрашивание ячеек по определенным условиям, необходимо воспользоваться Условным форматирование.
1. На главной вкладке панели инструментов ищете одноименную кнопку.
2. Из выпадающего списка выбираете строку Управление правилами и переходите в новое окно.
3. Нажимаете кнопку Создать и в новом окне устанавливаете правило форматирования. Выбираете нужную опцию и задаете условия отбора информации.
4. Щелкаете по кнопке Формат и настраиваете форматирование ячеек, которые принимают значение ИСТИНА.
5. Закрываете все окна нажав поочередно ОК и получаете готовое правило. Затем указываете диапазон применения и подтверждаете действие кнопкой Применить.
6. Аналогично создаете правила для магазинов с прибылью от 20000 до 30000 тысяч и от 30000 до 40000 тысяч. Для окончания настройки нажимаете ОК.
Преимуществом использования правил форматирования является то, что можно изменить цвет одной ячейки в зависимости от значения другой ячейки. То есть, если конечная цифра связана формулой с другими данными, то при изменении одной из ее составляющих меняется конечное число, которое уже подпадает под созданные ранее правила форматирования. Для наглядности увеличим объем вычетов для Магазина 9 так, чтобы Прибыль снизилась до уровня тридцати тысяч.
Ячейка автоматически окрасилась в зеленый цвет, поскольку отвечает заданным ранее условиям.
Как видите, окрашивать зоны рабочего листа не трудно. Важно продумать количество, а также условия для правил форматирования ячеек. При этом необходимо использовать весь диапазон значений, чтобы при изменении данных информация принимала вид в соответствии с установленными правилами.
Читайте также: