Условное форматирование в Google Sheets / Excel помогает лучше понимать и читать данные, часто используется для анализа и составления отчётов. В отличие от обычной заливки, функция динамическая: реагирует на изменения данных в ячейках в соответствии с заданными правилами.
Рассмотрим несколько примеров того, как можно применять условное форматирование в Google Таблицах и Exсel, на основе простой таблицы с результатами рассылок.
Все примеры есть на отдельных вкладках в этой таблице. Чтобы редактировать, нужно открыть в меню «Файл» и нажать «Создать копию».
Окрашивание данных градиентом — одна из самых полезных функций условного форматирования. Закрасим столбец с данными open rate в разные оттенки одного цвета — в зависимости от того, насколько показатель высокий. Чем open rate выше, тем ярче цвет.
Выбираем столбец с данными open rate. В главном меню нажимаем «Формат» — «Условное форматирование». Справа появится блок с правилами условного форматирования. Выбираем там вкладку «Градиент».
В поле «Применить к диапазону» будет указан наш столбец с данными open rate. При необходимости диапазон можно здесь изменить. Ниже будет цветная шкала — «Правила форматирования». По умолчанию выбран вариант от зеленого к белому. Можно нажать на него и выбрать другой вариант градиента. Или установить свои собственные цвета.
Все изменения сразу будут видны в таблице. Нажимаем кнопку «Готово», чтобы форматирование сохранилось.
Выделяем столбец со значениями open rate. В главном меню нажимаем «Условное форматирование» — «Цветовые шкалы» и выбираем цветовую шкалу «Зелёный-белый».
Данные в выбранных ячейках автоматически окрасятся в оттенки зелёного цвета. Если нужны другие цвета, можно выбрать кнопку «Другие правила» и самим определить, ячейки с какими значениями в какие цвета будут окрашены.
Чаще нам требуется выделить цветом не все ячейки, а лишь некоторые — те, которые отвечают заданным условиям. Например, выделим в нашем столбце все значения open rate меньше 10%.
Выделяем в таблице столбец с данными open rate. В главном меню нажимаем «Формат» — «Условное форматирование».
Справа в блоке «Правила условного форматирования» по умолчанию будет выбрана вкладка «Один цвет». На ней в выпадающем списке «Правила форматирования» выбираем «Меньше» и в поле ниже указываем интересующее нас значение: 10%. Если нужно, изменим цвет заливки, цвет и начертание шрифта. Нажимаем «Готово» для сохранения.
Выделяем в таблице столбец со значениями open rate без заголовка. В главном меню нажимаем «Условное форматирование» — «Правила выделения ячеек» — «Меньше».
Откроется окошко «Создание правила форматирования». В нём выбираем:
При необходимости в выпадающем списке «Форматировать с помощью» выбираем цвет для ячейки и шрифта. Нажимаем «ОК».
Другая частая ситуация — когда нам нужно выделить значения ячеек, которые зависят от данных в других ячейках. Например, выделим значения open rate только для получателей из Сегмента В.
Выделяем в таблице столбец со значениями open rate — который будем форматировать, и столбец «Получатели» — в котором находятся условия. В главном меню нажимаем «Формат» — «Условное форматирование».
В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле под ним пишем формулу:
=C:C=»Сегмент B»
При необходимости выбираем цвет для ячейки и шрифта. Нажимаем «Готово» для сохранения.
Выделяем диапазон: столбец «open rate», котором будет форматировать ячейки, и столбец «Получатели», в котором находятся условные значения. В главном меню нажимаем «Условное форматирование» — «Создать правило».
В появившемся окошке «Создание правила форматирования» выбираем «Начертание» — «Классическая», затем в выпадающем списке: «Использовать формулу для определения форматируемых ячеек». В поле ниже пишем саму формулу:
=ЕСЛИ($C2=»Сегмент B»;$D$2:$D$15)
Цветом можно выделить не просто одну ячейку, содержащую определенное значение, а всю строку. Например, закрасим красным строку, где в столбце «Получатели» указано «Сегмент В».
Выделяем всю таблицу без заголовков. В главном меню нажимаем «Формат» — «Условное форматирование».
В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле ниже пишем:
=$C:$C=»Сегмент B»
Обратите внимание на символ $ перед именем ячейки — он фиксирует, что мы проверяем только этот столбец (адрес не изменится, если протягивать или переносить формулу). Далее при необходимости выбираем цвет форматирования и нажимаем «Готово» для сохранения.
Выделяем значения всей таблицы. В главном меню нажимаем «Условное форматирование» — «Создать правило».
В окошке «Правила форматирования» в выпадающем списке «Начертание» выбираем «Классическая». Ниже в выпадающем списке — «Использовать формулу для определения форматируемых ячеек». В строке ниже пишем формулу:
=$C2=»Сегмент B»
В списке «Форматировать с помощью» задаём внешний вид выделяемых ячеек. Нажимаем «ОК».
Выделим в таблице все даты за прошлый месяц.
Выделяем столбец с датами рассылок. Важно: все значения в этом столбце должны быть в формате даты. В главном меню выбираем «Формат» — «Условное форматирование».
В блоке «Правила условного форматирования» (на вкладке «Один цвет» в выпадающем списке «Правила форматирования») выбираем «Дата после», а в следующем выпадающем списке — «Точная дата». Здесь мы указываем, например, первое число прошлого месяца и нажимаем «Готово» для сохранения.
Выделяем столбец с датами рассылок. Важно: все значения в этом столбце должны быть в формате даты. В главном меню нажимаем: «Условное форматирование» — «Правила выделения ячеек» — «Дата».
В появившемся окошке «Создание правила форматирования» в выпадающем списке выбираем «Форматировать только ячейки, которые содержат», затем выбираем параметр «Даты» и условие — например, «в прошлом месяце».
В пункте «Форматировать с помощью» при необходимости задаём цвет ячейки, начертание и цвет шрифта. Нажимаем «ОК».
Выделим цветом повторяющиеся значения — например, в столбце с результатами open rate.
Выделяем столбец с результатами open rate. В главном меню нажимаем «Формат» — «Условное форматирование».
В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле ниже добавляем:
=СЧЁТЕСЛИ($D$1:$D$15;D2)>1
При необходимости выбираем цвет форматирования. Нажимаем «Готово» для сохранения.
Выделяем столбец со значениями open rate. Нажимаем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».
Появится окошко «Создание правила форматирования», все настройки в нём можно оставить по умолчанию. Нажимаем «ОК».
К одним и тем же ячейкам можно применить несколько правил форматирования. Применяться они будут в порядке следования, но порядок можно изменить, переместив нужное правило выше или ниже.
Все условия, примененные к выделенным ячейкам, показываются справа в блоке «Правила условного форматирования». Чтобы его увидеть, нужно в главном меню выбрать «Формат» — «Условное форматирование». При этом показываться будут те правила условного форматирования, которые применены только к выделенным ячейкам.
Эти правила можно удалять и менять местами. А по двойному щелчку откроется возможность редактирования.
Также два и более правила форматирования можно задать в одной формуле.
В выпадающем списке «Правила форматирования» выбираем «Ваша формула» и пишем:
=И(условие1;условие2) — в случае, если должны выполняться оба условия.
=ИЛИ(условие1;условие2) — в случае, если должно выполняться хотя бы одно условие.
Все условия форматирования можно найти через пункт меню: «Условное форматирование» — «Управление правилами». Здесь можно удалять, добавлять, дублировать, изменять правила и диапазоны, к которым они будут применяться.
Условное форматирование —весьма полезный инструмент, при этом довольно несложный в использовании. И в Google Таблицах, и в Excel общий алгоритм такой:
1. Выбираем ячейки, которые будем форматировать. Это может быть столбец или диапазон данных.
2. Выбираем пункт «Условное форматирование» в главном меню.
3. Задаем условие для форматирования и внешний вид выбранных ячеек.
При этом в условиях форматирования можно использовать формулы, что дополнительно расширяет возможности для создания отчетов.
Шаблоны для каждого примера из статьи вы можете найти в этой таблице. Чтобы редактировать, нужно открыть в меню «Файл» и нажать «Создать копию».
Реклама. ООО «Юни Платформа», ИНН 9707003468
Делимся новостями и свежими статьями, рассказываем о новинках сервиса
Искренние письма о работе и жизни. Свежие статьи из блога. Эксклюзивные кейсы и интервью с экспертами диджитала.
