Условное форматирование в Google Таблицах: правила и формулы

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

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

• Выделите диапазон и выберите «Формат → Условное форматирование»; справа откроется панель правил.
• Правило «Один цвет» красит по условию, «Градиент» — шкалой от минимума к максимуму.
• Самое гибкое условие — «Ваша формула»: формула пишется для левой верхней ячейки диапазона и должна вернуть ИСТИНА или ЛОЖЬ.
• Знак $ перед буквой столбца — главный приём: =$G2="Отменён" красит всю строку по значению в G.
• На ячейку применяется первое подходящее правило из списка; порядок меняется перетаскиванием.

Учебные данные: лист «Заказы»

Все правила статьи настраиваются на листе «Заказы» серии «Google Таблицы для аналитика»: пятнадцать заказов за сентябрь и октябрь 2026 года, данные в A2:G16. Числа учебные. Ниже — столбцы, которые понадобятся; полный лист приведён в статье о функции QUERY. Номер строки листа указан, потому что формулы правил опираются именно на него.

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

Выделите столбец сумм F2:F16 и откройте «Формат → Условное форматирование». Справа появится панель: в поле «Применить к диапазону» уже стоит выделение, ниже — «Правила форматирования» со списком условий и блок стиля. Выберите условие «Больше», впишите 5000, задайте заливку и нажмите «Готово».

Подсветятся семь ячеек: 6 500, 9 400, 5 200, 7 300, 8 600, 9 900 и 5 100. Измените сумму любого заказа — цвет пересчитается сам. Готовые условия покрывают простые случаи: пусто и не пусто, текст содержит, начинается или точно равен, дата до или после, больше, меньше, между. Всё остальное делается правилом «Ваша формула», о нём ниже.

Чтобы добавить второе правило к тому же диапазону, нажмите «Добавить правило» в панели. Чтобы удалить — значок корзины рядом с правилом в списке. Скопировать оформление с правилами на другой диапазон можно кистью «Копировать форматирование» на панели инструментов.

Правило «Ваша формула» и знак доллара

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

Задача — подсветить всю строку отменённого заказа. Применяем правило к A2:G16 и пишем =$G2="Отменён". Доллар перед G запрещает столбцу сдвигаться: каждая ячейка строки смотрит в свой статус в G. Без доллара перед 2 строка сдвигается: строка 4 смотрит в G4, строка 12 — в G12. Подсветятся строки 4 и 12.

Если написать =G2="Отменён" без доллара, ячейка B2 будет проверять H2, C2 — I2, и подсветится только столбец A. Если написать =$G$2="Отменён", все ячейки будут смотреть в G2, где «Оплачен», и не подсветится ничего. Формула с двумя условиями: =ИЛИ($G2="Отменён"; $G2="Возврат") — строки 4, 7 и 12.

Повторяющиеся значения: повторный заказ и клиенты с несколькими заказами

Повторы подсвечивают формулой СЧЁТЕСЛИ, и от того, где стоит доллар, зависит смысл. Правило на C2:C16 с формулой =СЧЁТЕСЛИ($C$2:$C$16; $C2)>1 красит клиента, если он встречается в столбце больше одного раза. На нашем листе это четырнадцать ячеек из пятнадцати: заказ один только у Новикова. Для поиска дублей в выгрузке такая подсветка годится, для анализа клиентов — почти бесполезна.

Аналитику интереснее отметить сам повторный заказ. Формула =СЧЁТЕСЛИ($C$2:$C2; $C2)>1 считает вхождения от начала столбца до текущей строки: диапазон растёт вниз вместе с правилом. Первая покупка клиента остаётся без цвета, вторая и следующие подсвечиваются. Это восемь строк — 5, 7, 9, 11, 12, 13, 14 и 15. Восемь повторных заказов из пятнадцати — уже метрика, которую можно посчитать формулой и следить за ней по месяцам.

Если нужно не подсветить, а удалить дубли, цвет — только первый шаг. Как убрать повторы, не потеряв данные, и чем дубль строки отличается от повторяющегося значения, разобрано в статье о дублях в Excel: логика СЧЁТЕСЛИ в Google Таблицах та же.

Даты: месяц и выходные

Для дат готовые условия «Дата до» и «Дата после» понимают сегодняшний день, вчера, прошлую неделю или точную дату. Своя формула гибче. =$A2<ДАТА(2026;10;1) на A2:G16 подсветит сентябрьские заказы, строки 2–8. С СЕГОДНЯ() правило становится скользящим: =$A2<СЕГОДНЯ()-30 красит заказы старше тридцати дней и каждый день пересчитывается.

Выходные: =ДЕНЬНЕД($A2; 2)>5. С типом 2 функция нумерует дни с понедельника, суббота — 6, воскресенье — 7. Подсветятся четыре строки: 4, 6, 10 и 12. Две из них — отменённые заказы, и других отмен на листе нет. На пятнадцати заказах это не вывод, а повод выгрузить больше данных и проверить, не теряются ли выходные заказы, например из-за того, что менеджеры не перезванивают. Условное форматирование хорошо именно этим: оно показывает, куда смотреть, а проверка — уже дело расчёта.

Данные с другого листа: клиенты не из справочника

Правило может сверять данные со справочником, но с оговоркой. Справка Google говорит прямо: «Чтобы формула указывала на ячейки с другого листа, примените функцию ДВССЫЛ». Обычная ссылка Клиенты!A2:A7 в правило не примется, а через ДВССЫЛ — да: =СЧЁТЕСЛИ(ДВССЫЛ("Клиенты!A2:A7"); $C2)=0.

Правило на A2:G16 со справочником клиентов из статьи о ВПР в Google Таблицах подсветит строку 16: Новикова в справочнике нет. Это та же проверка, что #Н/Д у ВПР, только видна она прямо на листе с заказами. Адрес в ДВССЫЛ — текст, поэтому при переименовании листа «Клиенты» правило не обновится само, а перестанет находить всех.

Шкала цвета и топ значений

Вкладка «Градиент» раскрашивает диапазон по величине: минимальное значение получает один цвет, максимальное — другой, промежуточные — смесь. На столбце сумм светлее всего окажется 1 500 ₽ в строке 10, насыщеннее всего — 9 900 ₽ в строке 13. Точки шкалы можно задать числом, процентом или процентилем: процентиль спасает, когда один выброс забирает всю шкалу и остальные ячейки выглядят одинаковыми.

Градиент особенно полезен на сводной таблице: разрез «город × месяц» с градиентом читается как тепловая карта. Сводную в Google Таблицах и такой разрез собирает статья о сводной таблице. Шкала из двух цветов — для «больше или меньше», из трёх с серединой — когда есть естественный ноль, например отклонение от плана.

Топ значений — своей формулой: =$F2>=НАИБОЛЬШИЙ($F$2:$F$16; 3) подсветит три крупнейших заказа: 9 900, 9 400 и 8 600 ₽. Третий в этом списке отменён. Правило не знает про статусы, и топ по «сумме заказов» и топ по выручке — разные списки. Проверяйте, по каким строкам считается порог. Выше среднего: =$F2>СРЗНАЧ($F$2:$F$16) — среднее 5 086,67 ₽, подсветятся те же семь ячеек, что у правила «больше 5000».

Порядок правил: какое применится

Если на одну ячейку действуют несколько правил, Google Таблицы применяют первое подходящее по списку в панели. Остальные для этой ячейки не срабатывают. Порядок меняется перетаскиванием правила за точки слева.

Пример. Правило 1 на F2:F16 — зелёная заливка для сумм больше 5 000. Правило 2 на A2:G16 — красная строка для отменённых. В ячейке F4 сумма 6 500 и статус «Отменён»: подходят оба. Если зелёное правило стоит первым, строка 4 будет красной везде, кроме F4, которая останется зелёной, — и отменённый заказ визуально выглядит как крупная продажа. Поднимите правило отмен наверх: исключения и ошибки всегда ставят первыми, оценочные раскраски — после них.

Цвет — не данные

Подсветка помогает глазу, но для расчётов её нет. Посчитать сумму по зелёным ячейкам стандартной функцией нельзя, и если отчёт держится на цвете, ответ на вопрос «сколько повторных заказов» придётся искать заново. Каждое правило — это формула, и её же можно поставить в столбец: =СЧЁТЕСЛИ($C$2:$C2; $C2)>1 в H2 и ARRAYFORMULA на весь столбец дадут признак, который суммируется, фильтруется и попадает в сводную.

Отфильтровать по цвету в Google Таблицах всё же можно: в фильтре столбца есть пункт по цвету заливки и текста. Это удобно для разового просмотра, о фильтрах подробно — в статье о фильтре в Google Таблицах. Чередующиеся цвета строк для такого фильтра не работают.

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

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

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

Итог

Условное форматирование в Google Таблицах — способ показать глазу, где отмены, повторные покупки, выбросы и пропуски в справочнике. Почти всё делает правило «Ваша формула»: пишите её для первой строки, закрепляйте столбец долларом, ссылки на другой лист оборачивайте в ДВССЫЛ, а правила исключений ставьте первыми. И помните, что цвет не считается: признак, который нужен в отчёте, выносите формулой в столбец. Когда таких признаков становится много, их удобнее считать запросом: условия и CASE WHEN разбираются в курсе «SQL с нуля», первые 10 уроков бесплатно.