Просроченные платежи теряются среди сотен строк, а раскрашивать их вручную бессмысленно: завтра будет новый список. Условное форматирование красит ячейки автоматически: задали правило один раз, и Excel сам меняет цвет при изменении данных.

Ниже — как выделить значения по условию, подсветить целую строку, отметить даты и повторы, а также как поменять или удалить правило.

Коротко
  • Инструмент находится здесь: Главная → Условное форматирование.
  • Для простых условий есть готовые пункты в Правилах выделения ячеек.
  • Чтобы красить всю строку, создайте правило с формулой вроде =$C2="Оплачено".
  • Просроченные даты: =$D2<СЕГОДНЯ().
  • Посмотреть и удалить правила можно в окне Управление правилами.

Как выделить ячейки по простому условию

Допустим, в столбце с остатками надо отметить товары, которых меньше десяти штук.

  1. Выделите диапазон с данными.
  2. Откройте Главная → Условное форматирование → Правила выделения ячеек.
  3. Выберите Меньше.
  4. В поле слева введите 10, а справа выберите цвет, например Светло-красная заливка и тёмно-красный текст.
  5. Нажмите ОК.

Подсветка будет меняться вместе с данными. Введите в ячейку 15, и заливка исчезнет. Значение условия лучше хранить в отдельной ячейке: если указать там ссылку на неё, порог можно менять в одном месте.

В том же меню есть другие готовые правила: Больше, Между, Равно, Текст содержит, Дата и Повторяющиеся значения.

Как найти дубликаты цветом

Чтобы увидеть повторы до удаления, выделите столбец и выберите Правила выделения ячеек → Повторяющиеся значения. Excel подсветит все значения, встречающиеся больше одного раза. Такой способ ничего не удаляет и не меняет порядок, поэтому его удобно использовать для проверки. Если нужно, можно отсортировать таблицу по цвету: Данные → Сортировка → Цвет ячейки.

Таблица правил условного форматирования Excel: формулы для статуса, просроченных дат и значений выше порога

Как закрасить всю строку

Готовые пункты красят только саму ячейку. Чтобы цвет распространялся на всю строку, понадобится правило с формулой. Пусть в столбце C записан статус платежа, а данные занимают столбцы A–E со 2-й по 500-ю строку.

  1. Выделите весь диапазон A2:E500. Начните с левой верхней ячейки.
  2. Откройте Условное форматирование → Создать правило.
  3. Выберите Использовать формулу для определения форматируемых ячеек.
  4. Введите формулу: =$C2="Оплачено".
  5. Нажмите Формат, откройте вкладку Заливка и выберите цвет.
  6. Нажмите ОК дважды.

Знак доллара в формуле важен. $C2 означает, что столбец всегда C, а строка меняется. Без него Excel стал бы смотреть на соседний столбец для каждой следующей ячейки, и закрашивание пошло бы неправильно. Формула пишется для верхней левой ячейки диапазона, остальное Excel подставляет сам.

Полезные формулы для правил

Задача Формула
Статус равен «Оплачено» =$C2="Оплачено"
Дата в столбце D просрочена =$D2<СЕГОДНЯ()
Дата наступит в ближайшие 7 дней =И($D2>=СЕГОДНЯ();$D2<=СЕГОДНЯ()+7)
Значение выше порога в ячейке H1 =$B2>$H$1
Пустая ячейка в обязательном столбце =ЕПУСТО($E2)
В тексте есть слово «срочно» =ЕЧИСЛО(ПОИСК("срочно";$A2))

Для даты есть нюанс. Пустые ячейки считаются меньше сегодняшней даты, и строки без даты тоже окрасятся. Чтобы этого не было, расширьте условие: =И($D2<>"";$D2<СЕГОДНЯ()). Такая формула отмечает просрочку только там, где дата указана.

Гистограммы, шкалы и значки

Кроме заливки по условию есть ещё три типа оформления. Они полезны, когда важна величина значения.

  • Гистограммы — полоска внутри ячейки по размеру значения. Удобно для сравнения в одном столбце.
  • Цветовые шкалы — плавный цвет от малых значений к большим. Подходит для графиков работ, показателей по дням и тепловых карт.
  • Наборы значков — стрелки или светофоры рядом с числом.

Такие варианты применяются двумя щелчками: выделите диапазон и выберите вариант в меню Условное форматирование. Если полоски и значки перекрывают число, откройте Управление правилами → Изменить правило и настройте границы или отображение только значка.

Как поменять или удалить правило

  1. Откройте Условное форматирование → Управление правилами.
  2. В верхнем списке выберите Этот лист, чтобы увидеть все правила.
  3. Выделите правило и нажмите Изменить правило или Удалить правило.
  4. Стрелками поменяйте порядок. Правила наверху выполняются первыми.
  5. Поставьте флажок Остановить, если истина, если остальные правила не должны срабатывать для этой ячейки.

Удалить все правила с диапазона или листа можно через Условное форматирование → Удалить правила. В пункте Применяется к в этом окне видно, на какой диапазон распространено правило. Его можно расширить без пересоздания, чтобы новые строки тоже подсвечивались. Если данные оформлены как умная таблица, он обычно расширяется сам.

Копирование и вставка ячеек могут создавать много дублей правил, и файл начинает работать медленнее. Иногда стоит очистить их и задать заново одним правилом на весь диапазон.

Начните с одного условия для важного столбца, а всю строку красьте, когда убедитесь, что формула работает. Сверьте результат на нескольких строках, чтобы не ошибиться с диапазоном.

Частые вопросы

Почему правило закрашивает не те строки?

Чаще всего ошибка в ссылке: формула написана для другой строки, а не для верхней ячейки диапазона. Если данные начинаются со 2-й строки, в формуле должно быть $C2. Проверьте также пробелы в словах: «Оплачено » с пробелом не равно «Оплачено».

Можно ли использовать данные с другого листа?

Да, в современных версиях Excel в формуле можно указать другой лист, например =$B2>Настройки!$A$1. В старых версиях для этого использовали имя диапазона.

Как посчитать выделенные цветом ячейки?

Функции счёта не видят цвет. Считайте по самому условию: формула =СЧЁТЕСЛИ(C2:C500;"Оплачено") покажет количество оплаченных строк.

© kak2.ru