Добавление условного форматирования в ячейку в Excel позволяет применять различные параметры форматирования, такие как цвет, когда данные в этой ячейке соответствуют заданным вами условиям.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Mac, Excel для Office 365 и Excel Online.
Как использовать условное форматирование
Чтобы упростить условное форматирование, Excel поддерживает предустановленные параметры, которые охватывают часто используемые ситуации, такие как:
- Даты
- Дублирующиеся данные
- Значения выше или ниже среднего значения в диапазоне ячеек
В случае дат предустановленные параметры упрощают процесс проверки данных на даты, близкие к текущей дате, такие как вчера, завтра, последняя неделя или следующий месяц.
Однако если вы хотите проверить даты, которые выходят за пределы перечисленных параметров, настройте условное форматирование, добавив собственную формулу, используя одну или несколько функций даты в Excel.
Проверьте даты просрочки 30, 60 и 90 дней
Настройте условное форматирование с помощью формул , задав новое правило, которому Excel будет следовать при оценке данных в ячейке.
В Excel применяется условное форматирование сверху вниз, как они отображаются в диалоговом окне « Диспетчер правил условного форматирования ».
Даже если к некоторым ячейкам может применяться несколько правил, первое правило, соответствующее условию, применяется к ячейкам.
В этой демонстрации для создания результатов используется текущая дата, за 40 дней до текущей даты, за 70 дней до текущей даты и за 100 дней до текущей даты.
Проверьте даты 30 дней просроченных
На пустом листе Excel выделите ячейки от C1 до C4, чтобы выделить их. Это диапазон, к которому будут применяться правила условного форматирования.
- Выберите « Домой» > « Условное форматирование» > « Новое правило», чтобы открыть диалоговое окно « Новое правило форматирования ».
- Выберите « Использовать формулу», чтобы определить ячейки для форматирования .
- В текстовые поля Формат значения, где эта формула является истинной , введите формулу:
= СЕГОДНЯ () - С1> 30
Эта формула проверяет, не прошли ли даты в ячейках C1-C4 более 30 дней.
- Выберите « Формат», чтобы открыть диалоговое окно « Формат ячеек ».
- Выберите вкладку « Заливка », чтобы увидеть параметры цвета заливки фона.
- Выберите цвет заливки фона.
- Выберите вкладку « Шрифт », чтобы увидеть параметры формата шрифта.
- Установите цвет шрифта.
- Дважды нажмите кнопку ОК, чтобы закрыть диалоговые окна и вернуться к рабочему листу.
- Цвет фона ячеек C1-C4 меняется на выбранный цвет заливки, даже если в ячейках нет данных.
Добавить правило для дат более 60 дней просрочки
Вместо того, чтобы повторять все шаги, описанные выше, чтобы добавить следующие два правила, используйте опцию Manage Rules, чтобы добавить дополнительные правила сразу.
- Выделите ячейки C1-C4, если необходимо.
- Выберите Домашняя страница > Условное форматирование > Управление правилами, чтобы открыть диалоговое окно Диспетчер правил условного форматирования .
- Выберите Новое правило .
- Выберите « Использовать формулу», чтобы определить ячейки для форматирования .
- В текстовые поля Формат значения, где эта формула является истинной , введите формулу:
= СЕГОДНЯ () - С1> 60
Эта формула проверяет, не превышают ли даты в ячейках C1-C4 более 60 дней.
- Выберите « Формат», чтобы открыть диалоговое окно « Формат ячеек ».
- Выберите вкладку « Заливка », чтобы увидеть параметры цвета заливки фона.
- Выберите цвет заливки фона.
- Дважды нажмите кнопку ОК, чтобы закрыть диалоговое окно и вернуться в диалоговое окно диспетчера правил условного форматирования .
Добавить правило для дат более 90 дней, просроченных
- Выделите ячейки C1-C4, если необходимо.
- Выберите Домашняя страница > Условное форматирование > Управление правилами, чтобы открыть диалоговое окно Диспетчер правил условного форматирования .
- Выберите Новое правило .
- Выберите « Использовать формулу», чтобы определить ячейки для форматирования .
- В значениях формата, где эта формула верна , введите формулу:
= СЕГОДНЯ () - С1> 90
- Эта формула проверяет, не превышают ли даты в ячейках С1 по С4 более 90 дней.
- Выберите « Формат», чтобы открыть диалоговое окно « Формат ячеек ».
- Выберите вкладку « Заливка », чтобы увидеть параметры цвета заливки фона.
- Выберите цвет заливки фона.
- Дважды нажмите кнопку ОК, чтобы закрыть диалоговое окно и вернуться в диалоговое окно диспетчера правил условного форматирования .
- Выберите ОК, чтобы закрыть это диалоговое окно и вернуться к рабочему листу .
Цвет фона ячеек C1-C4 меняется на последний выбранный цвет заливки.
Проверьте правила условного форматирования
Проверьте правила условного форматирования в ячейках С1 по С4, введя следующие даты:
- Введите текущую дату в ячейку C1. Ячейка изменится на белый фон по умолчанию с черным текстом, поскольку ни одно из правил условного форматирования не применяется.
- Введите следующую формулу в ячейку C2:
= СЕГОДНЯ () - 40
Эта формула определяет, какая дата наступает за 40 дней до текущей даты. Ячейка заполняется цветом, выбранным для правила условного форматирования для дат, которые просрочены более чем на 30 дней.
- Введите следующую формулу в ячейку C3:
= СЕГОДНЯ () - 70
Эта формула определяет, какая дата наступает за 70 дней до текущей даты. Ячейка заполняется цветом, выбранным для правила условного форматирования для дат, которые просрочены более чем на 60 дней.
- Введите следующую формулу в ячейку C4:
= СЕГОДНЯ () - 100
Эта формула определяет, какая дата происходит за 100 дней до текущей даты. Цвет ячейки изменится на цвет, выбранный вами для правила условного форматирования для дат, которые просрочены более чем на 90 дней.