Условное форматирование в Google Таблицах
В таблице на 300 строк глаз не заметит просроченный шаг или клиента, который записан дважды. Условное форматирование делает это за вас: ячейка или вся строка сама окрашивается, когда выполняется условие. Разбираем, где его найти, как устроены правила, когда нужна своя формула и зачем знак $, — и даём 10 готовых формул и файл с примерами на сделках.
Где найти условное форматирование
- 01
Выделите ячейки
Диапазон, который будет окрашиваться
- 02
Формат
«Формат» → «Условное форматирование»: справа откроется панель
- 03
Правило
Условие — в «Форматирование ячеек», цвет и шрифт — в «Формат»
- 04
Готово
Правило работает сразу и при каждом изменении данных
На одном диапазоне может быть несколько правил. Чтобы удалить правило, нажмите значок «Удалить» рядом с ним в панели. А если скопировать ячейки, правила форматирования скопируются вместе с данными.
Один цвет или градиент
Пример из справки Google: преподаватель выделяет красным результаты теста ниже 80 баллов — условие «Меньше», значение 80, заливка красная. Так же подсвечивают сделки дороже 500 000 ₽ или остатки ниже минимального.
В условиях «Текст содержит» и «Текст не содержит» работают подстановочные знаки: «?» заменяет один символ, «*» — любое количество символов. Чтобы найти сам знак вопроса или звёздочку, поставьте перед ним тильду: «к~?т».
Своя формула: выделить всю строку
Условие «Ваша формула» — самое полезное: правило смотрит на любые ячейки, а не только на ту, которую красит. Формулу пишут для первой строки выделенного диапазона, и она должна возвращать ИСТИНА или ЛОЖЬ, — дальше таблица применяет её к остальным строкам.
Пример из справки: выделите столбцы A–E и введите =$B1="Да" — строка станет зелёной, если в столбце B написано «Да». Знак $ перед B закрепляет столбец: каждая ячейка строки смотрит в B. Без него ячейка в столбце A проверит B, в столбце B — C, в столбце C — D, и правило «поедет».
Знак $ и ссылки в правилах
В русском интерфейсе аргументы разделяет точка с запятой, в английском — запятая, и функции называются по-разному: СЧЁТЕСЛИ и COUNTIF, СЕГОДНЯ и TODAY, И и AND. В файле к статье каждая формула есть в обоих вариантах.
Семь правил на одной таблице сделок
В файле к статье — 12 сделок и семь правил. Выигранные — зелёные, сделки с просроченным следующим шагом — красные, пустой шаг у незакрытой сделки — оранжевый, клиент с несколькими сделками — жёлтый, суммы больше 500 000 ₽ — жирным синим, дни на этапе — шкалой от белого к красному, слово «Тендер» в названии — фиолетовым.
Взгляните на картинку: сразу видно три сделки с просроченным шагом на 1 660 000 ₽, сделку без следующего шага, которая стоит на этапе 21 день, и трёх клиентов, у которых по две сделки. Без подсветки на это ушла бы планёрка.
Порядок правил: какое сработает
Если на одну ячейку действует несколько правил, Google Таблицы применяют их по порядку, и первое выполненное определяет формат. Порядок меняют, перетаскивая правило в панели.
Поэтому главное ставят выше. Правило «Сделка выиграна» стоит первым: выигранная сделка со старой датой шага останется зелёной, а не станет красной как просроченная. Если правило «не работает», первым делом проверьте, не перебивает ли его то, что стоит выше.
10 готовых формул
Формулы — для русского интерфейса; диапазон начинается со второй строки, в первой — заголовки. Вставьте формулу в поле «Значение или формула» при условии «Ваша формула».
Сослаться на другой лист в правиле можно только через ДВССЫЛ (INDIRECT) — так написано в справке Google. Как найти и убрать повторы, подробно — в статье «Повторяющиеся значения в Google Таблице».
Файл с примерами
Три листа: «Как пользоваться»; «Сделки» — семь правил и итоги: 3 шага в просрочке на 1 660 000 ₽, 2 выигранные сделки; «Формулы правил» — 10 формул на русском и английском с пояснением к каждой.
Файл — в формате Excel: правила листа «Сделки» видно в «Управлении правилами». Вместо СЕГОДНЯ() в нём стоит дата отчёта в ячейке B2 — чтобы картинка совпадала со статьёй; в своей таблице замените её на СЕГОДНЯ().
Скачать файл с примерами · xlsx, 12 КБ
Таблица, которая обновляется сама
Подсветка работает, пока данные свежие. Если таблицу сделок заполняют руками, просроченный шаг окрасится только после того, как кто-то перенесёт дату. Виджет «Экспорт в Google Таблицы» выгружает сделки из amoCRM в Google Таблицу и обновляет её сам: изменённые строки — на месте, новые дописывает снизу, и они наследуют формат.
Ваши сортировка и фильтры при обновлении не ломаются, а правила на столбцах продолжают работать: сделка перешла в «Успешно» — строка позеленела сама. 1 998 ₽ в месяц за аккаунт; пробный период — 14 дней или 70 строк.
Условное форматирование — «Формат» → «Условное форматирование»: один цвет по условию или градиент. Для всей строки — «Ваша формула» со знаком $ перед столбцом, формула пишется для первой строки диапазона. Правила применяются по порядку, первое выполненное определяет формат; другой лист — только через ДВССЫЛ.
Частые вопросы
Converssion CRM — официальный партнёр amoCRM с 2017 года. Внедряем и сопровождаем amoCRM, делаем собственные виджеты для амоМаркета, чат-ботов, ИИ-агентов, сайты и рекламу. Услуги · Кейсы клиентов · Виджеты
