Условное форматирование для продвинутых

Условное форматирование для продвинутых

Петр Кондауров www.pkondaurov.ru

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

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

С первого взгляда понятно, на что обратить внимание, что хорошо, а что тухло

Принцип работы этой функции следующий:

  • Определяем диапазон, в котором будет работать условное форматирование
  • Определяем тип правил, при котором условное форматирование будет срабатывать. Например: ячейка пустая, ячейка больше, текст в ячейке содержит и т.д.
  • Указываем значение, которое будет применяться в правиле. Например "ячейка больше 10" или "текст содержит "контракт заключен""
  • Указываем, какого цвета будет цвет фона ячейки и шрифт, если правило сработает
  • Для одного и того же диапазона может быть создано несколько правил, например, чтобы подсвечивать зеленым статусы успеха, а красным - критичные статусы


Следующей ступенью считаю использование градиентной подсветки. Это позволяет замечать минимальные и максимальные значения. В моей CRM таким образом я подсвечиваю дату последнего контакта, чтобы видеть тех, с кем я давно не связывался, а кого можно пока оставить в покое :-)

Принцип работы следующий:

  • Выбираем диапазон, на котором будет действовать условное форматирование
  • Выбираем тип градиента
  • Указываем минимальные и максимальные значения, вне которых градиент уже не будет меняться
  • Точка середины может быть числом (и даже если максимальная цифра в столбце будет миллион, а точка середины установлена 10, то с нуля до 10 будет показана половина цветов градиента, а вторая половина будет распределена между 10 и 1000000). Если у вас не числа, а даты, как в примере, то можно вычислять процент или процентиль. В моем случае процентиль показывает ситуацию нагляднее.

Высшим пилотажем считаю использование условного форматирования с применением формул и ссылкой на другие ячейки.

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

Таблица с результатами опроса с добавлением нашего условного форматирования и формулы подсчета результата

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

Достигается такое следующим образом:

  • Выделяем диапазон, в котором условное форматирование будет подсвечивать ячейки
  • Тип "Форматирование ячеек" выбираем "Ваша формула"
  • Формула должна начинаться с "=" а далее пишем любую формулу, которая отражает смысл вашего поиска значения и выдает ответ "истина" или "ложь"

Вот некоторые особенности, которые я выяснил экспериментальным путем (поскольку в русскоязычном интернете нет подробного описания, как работает условное форматирование в гугл-таблицах):

  • Сравниваемая ячейка должна быть закреплена знаком "$" (очень важно для работы с таблицами осознать принцип закрепленных и незакрепленных ячеек, при создании формул, масштабируемых на весь лист)
  • В качестве ячейки для сравнения с эталоном во всем диапазоне указывается самая верхняя ячейка (в примере: диапазон B3:B103 - правило форматирования указываем для ячейки B3).

Это крайне полезная для управленцев функция и с ее помощью можно:

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


Вопросы и предложения можно писать мне в личку t.me/pkondaurov

Подписывайтесь на канал и приводите друзей https://t.me/googlesheetmanagement