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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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