Условное форматирование в Excel и Google Таблицах

Что делает условное форматирование

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

Где найти:

• Excel: Главная → Условное форматирование.
• Google Таблицы: Формат → Условное форматирование (откроется боковая панель).

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

Встроенные правила, которые стоит знать

• Правила выделения ячеек: больше, меньше, между, текст содержит, дата. Подходят для «сумма выше лимита» или «статус содержит Ошибка».
• Правила отбора первых и последних значений (Excel): первые 10 элементов, выше среднего.
• Гистограммы и наборы значков (Excel): мини-график или стрелки прямо в ячейке.
• Цветовые шкалы (в обеих программах): градиент от меньших значений к большим.

В Google Таблицах на панели две вкладки: Один цвет (все типы условий, включая свою формулу) и Градиент.

Правила со своей формулой

Формула должна возвращать ИСТИНА или ЛОЖЬ и пишется для левой верхней ячейки выделенного диапазона. Дальше программа сдвигает её для остальных ячеек так же, как при копировании формулы. Поэтому так важны знаки $.

Пример: подсветить всю строку задачи, если в столбце F стоит «Просрочено».

1. Выделите диапазон данных, например A2:F200, начиная с первой строки данных, а не с заголовка.
2. Excel: Создать правило → «Использовать формулу для определения форматируемых ячеек». Таблицы: Правила форматирования → «Ваша формула».
3. Введите формулу и выберите заливку:

=$F2="Просрочено"

$F закрепляет столбец, и каждая ячейка строки смотрит в столбец F. Цифра 2 без доллара позволяет строке меняться. Если написать $F$2, все строки будут проверять только вторую, и это самая частая ошибка.

Важно для русского Excel: функции там локализованы, а аргументы разделяются точкой с запятой. COUNTIF пишется как СЧЁТЕСЛИ, AND как И, TODAY как СЕГОДНЯ. Google Таблицы понимают английские названия в любой локали.

Дубликаты и просроченные даты

Дубликаты. В Excel есть готовое правило: Правила выделения ячеек → Повторяющиеся значения. Для большего контроля или в Google Таблицах примените к A2:A200 формулу:

=COUNTIF($A$2:$A$200,$A2)>1

Чтобы отметить только второе и следующие вхождения, считайте до текущей строки: =COUNTIF($A$2:$A2,$A2)>1.

Просроченные даты. Срок в столбце D, статус в столбце F, диапазон A2:F200:

=AND($D2<>"",$D2<TODAY(),$F2<>"Готово")

Часть $D2<>"" не даёт пустым ячейкам считаться просроченными. Для «срок в ближайшие 7 дней» добавьте второе правило другим цветом:

=AND($D2>=TODAY(),$D2<=TODAY()+7)

Проверьте, что в столбце настоящие даты, а не текст, похожий на дату, иначе сравнение тихо не сработает.

Цветовые шкалы

Шкала помогает быстро просматривать числа: маржу, время ответа, продажи по менеджерам.

• Двухцветная шкала подходит для «чем больше, тем лучше», трёхцветная с серединой нужна, когда есть осмысленная средняя точка, например плановое значение.
• Задавайте минимум и максимум числом или процентилем, а не автоматически, иначе один выброс обесцветит весь диапазон.
• Не смешивайте шкалу и правила с заливкой на одних и тех же ячейках: читатель не поймёт, что означает цвет.

Как держать правила в порядке

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

• Регулярно открывайте список. Excel: Условное форматирование → Управление правилами, затем «Этот лист». Таблицы: боковая панель показывает правила выделения или всего листа.
• Одно правило на диапазон. Объединяйте фрагменты, редактируя поле «Применяется к».
• Порядок имеет значение. В Google Таблицах при совпадении нескольких правил побеждает верхнее. В Excel непротиворечивые форматы суммируются, конфликты решаются порядком, а «Остановить, если истина» отключает правила ниже.
• Вставляйте только значения (Специальная вставка → Значения), чтобы не тащить форматирование вместе с данными.
• Добавьте легенду рядом с таблицей: что означает каждый цвет.
• Ограничивайте диапазоны данными. Пересчитываемые функции вроде TODAY() на целых столбцах замедляют большие файлы.

Частые ошибки

• Неправильные якоря: $A$2 вместо $A2.
• Формула ссылается не на первую строку выделенного диапазона.
• Сравнение текста с числом: "100" и 100 — разные значения.
• В Google Таблицах прямая ссылка на другой лист в своей формуле. Используйте INDIRECT("Лист2!A2:A200").