Поймёте, как работает условное форматирование Excel, настроите подсветку по числу, дате или значению другой ячейки и не запутаетесь в приоритетах правил.
Допустим, у вас таблица из 3 000 заказов. Просроченных всего 87, но искать их глазами — занятие на любителя (причём очень терпеливого). Excel может выделить нужные строки сам. Вы задаёте условие один раз, а цвет меняется вместе с данными. Это и есть условное форматирование. Оно не меняет число и не исправляет ошибку, а показывает, куда посмотреть первым делом.
Что это значит простыми словами
Правило состоит из трёх частей: диапазон, условие и оформление. Например: если дата оплаты меньше сегодняшней даты, а статус не равен «Оплачен», строка становится светло-красной. Для простых случаев подходят готовые правила «Больше», «Меньше», «Повторяющиеся значения», цветовые шкалы и наборы значков. Для бизнес-логики лучше правило по формуле: оно позволяет учитывать сразу несколько столбцов и подсвечивать всю строку.
Как работает логика
Формула условного форматирования должна возвращать ИСТИНА или ЛОЖЬ для первой ячейки выбранного диапазона. Относительные ссылки сдвигаются по строкам, абсолютные со знаком доллара остаются на месте. Для диапазона A2:F3000 правило просрочки может выглядеть так: =И($D2<СЕГОДНЯ();$E2<>"Оплачен"). Столбцы D и E закреплены, а номер строки меняется. Если написать $D$2, Excel будет проверять все строки только по одной ячейке — классическая маленькая ошибка с очень большим эффектом.
Подробный пример с числами
В таблице 1 200 счетов. В D хранится срок оплаты, в E — статус, в F — сумма. На 10 ноября есть 74 неоплаченных счёта с прошедшим сроком на общую сумму 2,8 млн рублей. Создаём правило по формуле =И($D2<СЕГОДНЯ();$E2="Не оплачен") и применяем к A2:F1201. Второе правило выделяет суммы выше 200 000 рублей более насыщенным цветом. После настройки видим 11 крупных просрочек на 1,6 млн. Значит, менеджеру не нужно начинать со всех 74 строк: первые звонки понятны сразу.
Цвет — это подсказка, а не контрольная сумма. Поэтому отфильтруйте таблицу по тем же условиям обычным фильтром и сравните количество: 74 строки должны совпасть с 74 подсвеченными. Затем измените одну тестовую дату на будущую и статус на «Оплачен». Подсветка обязана исчезнуть. После проверки удалите тестовое изменение и сохраните описание правила рядом с файлом, чтобы через месяц никто не гадал, почему строка стала красной.
Как использовать в бизнесе
В работе я использую условное форматирование для исключений: просрочка, отрицательная маржа, отклонение плана выше допустимого, незаполненный обязательный реквизит. Это ускоряет разбор таблицы, но не заменяет показатель. Рядом всё равно нужны количество проблемных строк и сумма эффекта. В примере выше важны не красные клетки сами по себе, а 1,6 млн рублей крупных просрочек, с которыми команда работает сегодня.
Когда применять этот подход
Готовое правило подходит, если условие основано на одном значении. Формула нужна, когда цвет зависит от другого столбца, нескольких условий или всей строки. Цветовая шкала полезна для распределения, но плохо подходит для статуса «норма/ошибка». Набор значков стоит применять только с понятными порогами. Если правил стало десять и они спорят друг с другом, логику лучше вынести в отдельный расчётный столбец.
Как проверить результат
Откройте «Управление правилами» и проверьте диапазон применения, порядок и флаг «Остановить, если истина». Правило выше в списке может перекрывать нижнее. Проверьте пустые ячейки, текст вместо даты, новые строки таблицы и вставку данных из внешней системы. Лучше преобразовать диапазон в таблицу Excel: тогда форматирование чаще корректно расширяется вместе с данными.
Порядок действий
1. Сформулируйте действие
Запишите, что человек должен сделать после сигнала: позвонить клиенту, проверить маржу или заполнить поле.
2. Выберите контрольный диапазон
Уберите заголовок и убедитесь, что первая строка диапазона совпадает с формулой.
3. Создайте правило
Начните с готового условия, а для нескольких признаков используйте формулу с правильными ссылками.
4. Задайте спокойное оформление
Один цвет — один смысл. Не превращайте рабочую таблицу в новогоднюю гирлянду.
5. Сверьте результат
Сравните количество выделенных строк с фильтром и проверьте несколько граничных значений.
Ограничения и типичные ошибки
- Закреплять номер строки и проверять весь диапазон по одной ячейке.
- Смешивать несколько смыслов в одном цвете.
- Не проверять порядок и диапазоны правил.
- Считать цвет доказательством корректности исходных данных.
После изменения источника или структуры таблицы повторите контрольный пример. Старое совпадение не подтверждает новую версию данных автоматически.
Как я делаю это в проектах
Сначала я прошу собственника процесса назвать одно отклонение, которое требует действия. Потом считаем его обычной формулой и только после этого добавляем цвет. Так правило можно проверить цифрой. Для отдела продаж это может быть просрочка больше семи дней и сумма выше 100 тысяч. В таблице появляется не просто «красиво», а очередь работы: ответственный, сумма, срок и следующий шаг. Вот тогда условное форматирование реально помогает бизнесу.
Что сделать прямо сейчас
Возьмите одну рабочую таблицу и настройте одно правило для самого дорогого исключения. Сверьте число подсвеченных строк обычным фильтром.
Если хотите связать расчёт с реальным управленческим решением, можно обсудить задачу с ФАНалитикой. Начнём с одного проверяемого сценария и понятной цифры.
