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