Условное форматирование в Google Sheets

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

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

В этой статье вы найдёте как базовые шаги, так и продвинутые приёмы: кастомные формулы, цветовые шкалы, часто встречающиеся ошибки и готовые шаблоны для рабочих сценариев.

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

Условное форматирование полезно почти в любом рабочем процессе, где вы регулярно просматриваете табличные данные. Основные причины использовать его:

Сэкономить время на ручном оформлении и обеспечить единообразие вида таблиц.
Выделять проблемные или приоритетные значения (например, отрицательная прибыль или запасы, заканчивающиеся ниже порога).
Группировать и отличать разные типы данных (департаменты, статусы, категории).
Подавлять/затемнять устаревшие записи (например, задачи с датой в прошлом).

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

Быстрый пример: создание простого правила

Ниже — минимальные шаги, чтобы создать правило выделения значений в Google Sheets.

Вставьте несколько примеров данных в строки.
Выделите диапазон ячеек, к которому будете применять форматирование.
В меню выберите Формат → Условное форматирование.
В выпадающем списке Форматировать, если… выберите «Равно».
В поле Значение или формула введите одно из ваших значений.
Выберите стиль (например, цвет заливки).
Нажмите Добавить ещё одно правило и повторите для других значений.
По завершении нажмите Готово и просмотрите список созданных правил.

Совет: если вы часто создаёте однотипные правила, сделайте шаблон листа с заранее подготовленными диапазонами и стилями.

Типы условий, поддерживаемые Google Sheets

Google Sheets группирует условия вокруг трёх основных типов данных: текст, даты и числа.

Текст

empty — проверяет, пуста ли ячейка.
contains — ищет вхождение текста. Поддерживает шаблоны: ? заменяет один символ, * заменяет любую последовательность символов.
starts with / ends with / exactly — строгие проверки начала, конца или точного равенства строки.

Пример: условие contains “ошибка” выделит все ячейки, где встречается слово “ошибка”.

Даты

Для дат доступны сравнения before, after, equal и также относительные значения: tomorrow, today, next week, past year и т.п. Можно сравнивать с конкретной датой.

Пример практического использования: выделять задачи со сроком завтра или просроченные задачи (дата < today()).

Числа

Доступны сравнения: равно, больше, меньше, между. Это удобно для контроля запасов, финансовых показателей и KPI.

Кастомные формулы для условного форматирования

Кастомные (Custom formula is) формулы делают правила более гибкими: с их помощью можно подсвечивать целые строки, ссылаться на другие столбцы и комбинировать логические проверки.

Пример: у нас есть таблица запасов с двумя столбцами — Фрукт и Остаток. Таблица с фруктами и количеством на складе

Чтобы подсветить все строки, где остаток > 4:

Выделите диапазон, например A2:B5.
Откройте панель правил Условного форматирования.
В качестве условия выберите Custom formula is.
Введите формулу:

=$B2>4

Пример условного форматирования с кастомной формулой

Пояснение формулы: знак равенства обязателен. $ перед буквой столбца делает ссылку абсолютной по колонке (всегда B), а номер строки относителен к верхней строке диапазона (2 означает первую строку диапазона). При применении к каждой строке сравнение берёт значение из столбца B текущей строки.

Другие полезные примеры формул:

Подсветить строку, если статус = “Просрочено”:

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

Подсветить, если дата меньше сегодняшней:

=$D2

Подсветить главную колонку, если в другой колонке значение пустое:

=AND($E2<>"", $F2="")

Замечание: используйте функции ISBLANK() аккуратно — она возвращает TRUE для полностью пустых ячеек, но не для ячеек с формулой, возвращающей пустую строку “”.

`Какие стили можно применять`

Google Sheets предлагает ограниченный, но достаточный набор стилей.

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

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

`Цветовая шкала (Color scale)`

Color scale визуализирует числовые диапазоны, присваивая оттенки в зависимости от относительного положения значения.

Выделите диапазон чисел.
В панели условного форматирования переключитесь с Single color на Color scale.
Настройте Minimum, Midpoint, Maximum (можно выбрать числа, процентили или формулы) и цвета для каждой точки.

Цветовые шкалы удобны, когда нужно быстро оценить распределение показателей: тёмный цвет — низкое значение, яркий — высокое (или наоборот, в зависимости от настроек).

`Практические рекомендации и ментальные модели`

Ментальные модели помогают быстрее принимать решения о форматировании:

Принцип «Thresholds and Alerts»: выделяйте границы, а не отдельные числа. Например, порог запаса 10 — все ниже выделяются красным.
«One Visual Per Insight»: используйте одно правило на один тип смысла (например, только для просрочек). Если вы накладываете много правил, визуализация становится шумной.
«Proximity Principle»: для строк, представляющих единый объект (например, задача), выделяйте всю строку целиком, чтобы глаз фиксировал объект, а не отдельную ячейку.

Когда условное форматирование не подходит:

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

`Руководство по созданию и тестированию правил (SOP)`

Определите цель правила: что именно вы хотите визуально подчеркнуть?
Выберите целевой диапазон (целые строки или конкретные столбцы).
Напишите простую формулу и протестируйте её на небольшом наборе данных.
Установите стиль (цвет, зачёркивание) и проверьте контрастность для печати и экрана.
Протестируйте на сценариях: граничные случаи, пустые значения, нечисловые символы.
Документируйте правило в отдельной вкладке («Правила условного форматирования») — укажите цель и формулу.
При необходимости — добавьте вспомогательную колонку для упрощения логики.
Пересмотрите порядок правил: правила применяются сверху вниз; при конфликте действует первое.

`Чек‑лист перед публикацией листа`

Проверить все правила на диапазоне тестовых данных.
Убедиться, что важные правила находятся выше в списке.
Проверить, что цветовые схемы доступны для дальтоников (контрастность).
Документировать назначение каждого правила в комментарии или отдельной вкладке.
Убедиться, что при копировании/вставке форматирование кантролируется (при необходимости — использовать Paste Special → Values).

`Критерии приёмки`

Все заданные сценарии подсвечиваются корректно (включая граничные значения).
Порядок правил не приводит к неожиданным перезаписям форматов.
Никакие критичные данные не становятся нечитаемыми из‑за цвета текста/фона.
Документация содержит ответственного за обслуживание листа.

`Частые ошибки и способы их исправления`

Правило не срабатывает: проверьте относительные/абсолютные ссылки ($) в формуле.
Неправильный порядок правил: перетащите правило выше/ниже в панели.
Формула работает в одной строке, но не в диапазоне: убедитесь, что вы используете относительные номера строк (например, $B2), где 2 — первая строка диапазона.
Копирование форматирования вместе с данными: используйте Вставить значения, если хотите перенести только данные.

`Совместимость, миграция и ограничения`

Правила условного форматирования, созданные в Google Sheets, переносятся в Excel не всегда корректно — проверяйте при экспорте.
В Google Apps Script можно читать и менять правила программно (для крупных автоматизаций).
Максимум правил и производительность: большое количество сложных правил на больших диапазонах может замедлять лист. В таких случаях используйте вспомогательные колонки и более простые формулы.

`Приватность и соблюдение GDPR`

Если в таблице содержатся персональные данные, будьте осторожны с визуализацией и обменом листом:

Не делайте условное форматирование, которое явно выделяет личную информацию при совместном доступе.
При автоматическом обмене листом документируйте, кто и почему видит выделенные записи.
Для статей/публикаций заранее анонимизируйте данные.

`Шпаргалка формул и часто используемые конструкции`

Сравнение чисел:

=$B2>100

Проверка текста (регистр важен):

=EXACT($A2,"VIP")

Поиск подстроки (регистр не важен):

=REGEXMATCH(LOWER($A2),"urgent|now")

Комбинация условий:

=AND($B2>10, $C2<5)

Проверка на пустоту, учитывая формулы:

=LEN($D2)>0

`Примеры сценариев по ролям`

Аналитик:

Визуализировать выбросы в данных (color scale).
Подсветить строки с отсутствующими значениями в ключевых полях.

Менеджер проектов:

Подсветить просроченные задачи и задачи со статусом «На грани».
Раз в неделю проверять правила на новых данных.

HR:

Отмечать сотрудников по отделам разными цветами.
Затемнять уволенных сотрудников (зачёркивание).

`Пример playbook: подсветка просроченных задач`

Создайте колонку “Срок” (столбец D) и колонку “Статус” (столбец E).
Диапазон: A2:E100 (или больше по потребности).
Правило 1 (Просрочено):

=AND($D2<>"", $D2"Завершено")

Стиль: фон — красный, текст — белый, полужирный.

Правило 2 (Срок завтра):

=$D2=TODAY()+1

Стиль: фон — жёлтый.

Документируйте правила и добавьте контактное лицо для поддержки.

`Decision tree: какой тип условного форматирования выбрать`

flowchart TD
  A[Нужно выделить одну ячейку или строку?] -->|Ячейку| B[Использовать простое правило 'равно, >, <, между']
  A -->|Строку| C[Использовать кастомную формулу и абсолютную колонку]
  C --> D{Данные числовые?}
  D -->|Да| E[Рассмотрите Color scale]
  D -->|Нет| F[Используйте текстовые проверки: contains, starts with]
  B --> G{Нужны сложные условия?}
  G -->|Да| C
  G -->|Нет| H[Завершить]

`Тестовые случаи и приёмочные критерии`

Граничные значения: проверки на равенство должны срабатывать на точных граничных числах.
Пустые ячейки: правила с ISBLANK и LEN должны отличать пустые ячейки от ячеек с “”.
Форматирование при копировании: при вставке нового блока с данными правила применяются корректно.
Конфликт правил: при двух противоречивых правилах применяется верхнее.

`Советы по доступности и печати`

Избегайте сочетания тёмно‑синего текста на чёрном фоне и других низко контрастных комбинаций.
Для печати используйте дополнительные текстовые метки или символы (например, ✓/✗) в отдельных колонках.

`Практика: как автоматизировать управление правилами`

Для комплексов листов с большим количеством правил используйте Google Apps Script для:

экспорта и проверки правил в виде JSON,
массового изменения цвета/порядка правил,
применения шаблонов условного форматирования ко многим листам.

Пример функции Apps Script (псевдо):

// Получить правила листа
var rules = sheet.getConditionalFormatRules();
// Изменить цвет первого правила
rules[0].getBooleanCondition().setBackground('#FF0000');
sheet.setConditionalFormatRules(rules);

`Пригодные альтернативы`

Вспомогательные колонки с формулами и фильтрами — проще для отладки и совместной работы.
Использование диаграмм/панелей Power BI или Data Studio для сложной визуализации, когда нужен более богатый набор визуальных инструментов.

`Краткое резюме`

Условное форматирование — мощный инструмент для быстрой визуализации важной информации в Google Sheets. Оно помогает экономить время, поддерживать единообразие и акцентировать внимание на исключениях. Для масштабных и сложных сценариев сочетайте правила с вспомогательными колонками и автоматизацией через Apps Script.

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

`Полезные шаблоны и чек‑листы (копируйте в отдельную вкладку)`

Таблица шаблона правил (колонки):

Название правила
Диапазон
Условие/Формула
Стиль
Ответственный
Дата последней проверки

Короткий чек‑лист поддержки:

Проверка раз в месяц
Тестирование после массовой вставки данных
Контроль порядка правил