Условное форматирование: правила, повторы, даты

Как выделить строку по условию в Excel

Чтобы окрасить всю строку A:C, если сумма в столбце C меньше 10000, выделите A2:C20. Откройте Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек, введите формулу и выберите заливку:

=$C2<10000

Знак $ закрепляет столбец C, а номер строки меняется для каждой строки диапазона. При C2 = 9000 выделится строка 2; при C3 = 12000 строка 3 не выделится. Проверьте поле «Применяется к»: =$A$2:$C$20. Формула должна соответствовать верхней левой ячейке выбранного диапазона.

Для окраски только числовых ячеек можно выбрать встроенное правило «Меньше» и задать порог без формулы. Подробности обоих способов — ниже.

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

Встроенные правила «Больше», «Меньше», «Между»: порог, формат, диапазон

Допустим, у вас столбец с суммами продаж, и вы хотите сразу видеть все строки, где сумма упала ниже 10 000. Вместо того чтобы сортировать или фильтровать, можно задать правило: ячейки с нужными значениями сами загорятся красным.

Чтобы это сделать, выделите числовой диапазон — например, C2:C20. Затем: вкладка Главная → Условное форматирование → Правила выделения ячеек → Меньше…

Откроется небольшой диалог с двумя полями: пороговое значение и формат. Введите 10000 в поле значения, выберите формат из выпадающего списка (например, «Светло-красная заливка с тёмно-красным текстом») и нажмите OK. Всё — Excel немедленно подсветит ячейки, которые подпадают под условие.

Аналогично работают «Больше…» и «Между…». Разница только в количестве порогов: «Больше» и «Меньше» принимают одно число, «Между» — два, и подсвечивает ячейки, значение которых попадает в этот диапазон включительно.

Важная деталь: правило проверяет каждую ячейку выделенного диапазона независимо от остальных. Excel не сравнивает ячейки между собой — он сравнивает каждую с заданным порогом. Поэтому если вы выделили C2:C20 и задали правило «больше 50 000», то подсветится каждая ячейка, где число превышает 50 000, вне зависимости от того, что стоит в соседних строках.

Формат можно не только выбрать из готового списка, но и настроить вручную: в том же выпадающем меню есть пункт «Пользовательский формат…», где открывается стандартное окно форматирования ячейки — там доступны любой цвет заливки, шрифт и границы.

Правило «Повторяющиеся значения»: что считается повтором и ограничения поиска

Если нужно найти в столбце повторяющиеся значения — например, один и тот же артикул товара, введённый дважды — для этого есть отдельное встроенное правило.

Выделите столбец или диапазон, который хотите проверить. Затем: Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения…

В диалоге можно выбрать, что именно подсвечивать: повторяющиеся значения или, наоборот, уникальные. Выберите «Повторяющиеся», задайте цвет и нажмите OK. Теперь каждая ячейка, значение которой встречается в диапазоне больше одного раза, получит выбранный формат.

Здесь важно понимать одно ограничение: правило работает с отдельными ячейками, а не со строками целиком. Возьмём таблицу с колонками «Имя» и «Город». Если в столбце «Город» Москва встречается пять раз — все пять ячеек подсветятся. Но это не значит, что строки одинаковые: у каждой строки может быть своё имя, и это будут разные люди из одного города.

Другими словами, совпадение значения в одном столбце ≠ дублирование всей строки. Правило «Повторяющиеся значения» отвечает только на вопрос «встречается ли это конкретное значение в диапазоне ещё раз?». Если вам нужно искать строки-дубликаты по сочетанию нескольких полей — это уже задача для формульного подхода, но это отдельная история.

СЕГОДНЯ() в формульном правиле: подсветка просроченных дат

Встроенные правила хорошо работают с числами, но для дат часто нужно условие «дата уже прошла», а это зависит от текущего дня. Здесь пригодится функция СЕГОДНЯ().

СЕГОДНЯ() не принимает никаких аргументов — скобки всегда пустые. Она возвращает сегодняшнюю дату как числовое значение Excel. Excel заново вычисляет СЕГОДНЯ() при открытии книги или пересчёте; результат изменится, когда изменится текущая системная дата.

Для сравнения: =A2<СЕГОДНЯ() вернёт ИСТИНА, если дата в A2 уже в прошлом. На этом и строится правило для просроченных дат.

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

Допустим, в столбце B (B2:B10) хранятся дедлайны задач, и вы хотите, чтобы просроченные строки подсвечивались красным.

Выделите диапазон всей таблицы A2:D10. Затем: Главная → Условное форматирование → Создать правило → Использовать формулу для определения форматируемых ячеек.

В поле формулы введите:

=$B2<СЕГОДНЯ()

Выберите формат (например, красная заливка) и нажмите OK.

Эта формула будет применяться ко всем ячейкам каждой строки диапазона, поэтому красным подсветится вся просроченная строка. Для строки 2 Excel проверит =$B2<СЕГОДНЯ(), для строки 3 — =$B3<СЕГОДНЯ() и так далее. Строка в ссылке относительная, поэтому и сдвигается вниз автоматически.

Почему знак $ перед буквой столбца

Знак $ перед B фиксирует столбец. Правило применяется к диапазону A2:D10 и всегда должно смотреть в столбец B, поэтому без $ перед буквой не обойтись: без него ссылка смещалась бы вместе с каждой форматируемой колонкой: для области A2:D10 формула =B2 проверяла бы B2, C2, D2 и E2 соответственно.

Строка в ссылке должна оставаться без знака $. Если написать =$B$2<СЕГОДНЯ(), Excel будет сравнивать каждую строку диапазона с одной и той же ячейкой B2 — это ошибка: правило перестанет реагировать на содержимое остальных строк.

«Управление правилами»: редактирование, приоритет и удаление

Все правила, которые вы создали для диапазона, собраны в одном месте: Главная → Условное форматирование → Управление правилами. Чтобы увидеть правила диапазона, сначала выделите его, откройте диспетчер и в списке показа правил выберите «Текущий фрагмент». Чтобы увидеть все правила листа, выберите «Этот лист». Подписи могут немного отличаться между версиями Excel.

Редактирование. Дважды щёлкните по правилу (или выберите его и нажмите «Изменить правило…») — откроется тот же диалог, где вы его создавали. Можно поменять порог, формулу или формат. После нажатия OK изменения применяются сразу.

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

Есть ещё галочка «Остановить, если истина» рядом с каждым правилом. Если она включена у правила, которое сработало, Excel не будет проверять правила, стоящие ниже, для этой ячейки. Это полезно, когда нижние правила не должны перекрывать эффект более приоритетного.

Удаление. Выделите ненужное правило и нажмите кнопку «Удалить правило». Формат, заданный удалённым правилом, перестанет применяться. Если те же ячейки форматирует другое правило, часть подсветки может остаться; остальные правила продолжают работать.

Если хотите убрать все правила разом с конкретного диапазона или со всего листа — это быстрее сделать через Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек (или со всего листа), не заходя в «Управление правилами».

Почему условное форматирование не работает

Если правило сохранено, но окрашивается не та строка, сначала сравните поле «Применяется к» с формулой. Для диапазона =$A$2:$C$20 формула должна описывать первую строку этого диапазона. В =$C2<10000 столбец C закреплён, строка меняется. Запись =$C$2<10000 проверяет одну и ту же C2 для всех строк.

Пустые ячейки могут попасть под условие «меньше 10000». Если нужны только числа, используйте проверку типа:

=И(ЕЧИСЛО($C2);$C2<10000)

Теперь строка с числом 9000 выделится, с 12000 — нет, а пустая ячейка и текст «9000» не пройдут проверку ЕЧИСЛО. Если число пришло из выгрузки как текст, преобразуйте его в число, затем повторите проверку.

Чтобы отделить ошибку формулы от ошибки настройки, введите ту же формулу в свободный столбец рядом с первой строкой и протяните вниз. ИСТИНА означает, что условие выполнено. Если формула верна, проверьте диапазон и порядок правил в диспетчере. Для сложного случая временно оставьте в копии листа одно правило и сравните результат на двух строках с заранее известными значениями.