Условное форматирование в Google Таблицах и Excel: 7 примеров использования функции

Условное форматирование в Google Sheets / Excel помогает лучше понимать и читать данные, часто используется для анализа и составления отчётов. В отличие от обычной заливки, функция динамическая: реагирует на изменения данных в ячейках в соответствии с заданными правилами.

Рассмотрим несколько примеров того, как можно применять условное форматирование в Google Таблицах и Exсel, на основе простой таблицы с результатами рассылок.

Все примеры есть на отдельных вкладках в этой таблице. Чтобы редактировать, нужно открыть в меню «Файл» и нажать «Создать копию».

Окрашивание диапазона данных градиентом

Окрашивание данных градиентом — одна из самых полезных функций условного форматирования. Закрасим столбец с данными open rate в разные оттенки одного цвета — в зависимости от того, насколько показатель высокий. Чем open rate выше, тем ярче цвет.

В Google Таблицах

Выбираем столбец с данными open rate. В главном меню нажимаем «Формат» — «Условное форматирование». Справа появится блок с правилами условного форматирования. Выбираем там вкладку «Градиент».

В поле «Применить к диапазону» будет указан наш столбец с данными open rate. При необходимости диапазон можно здесь изменить. Ниже будет цветная шкала — «Правила форматирования». По умолчанию выбран вариант от зеленого к белому. Можно нажать на него и выбрать другой вариант градиента. Или установить свои собственные цвета.

Все изменения сразу будут видны в таблице. Нажимаем кнопку «Готово», чтобы форматирование сохранилось.

В Excel

Выделяем столбец со значениями open rate. В главном меню нажимаем «Условное форматирование» — «Цветовые шкалы» и выбираем цветовую шкалу «Зелёный-белый».

Данные в выбранных ячейках автоматически окрасятся в оттенки зелёного цвета. Если нужны другие цвета, можно выбрать кнопку «Другие правила» и самим определить, ячейки с какими значениями в какие цвета будут окрашены.

Окрашивание цветом ячеек с данными

Чаще нам требуется выделить цветом не все ячейки, а лишь некоторые — те, которые отвечают заданным условиям. Например, выделим в нашем столбце все значения open rate меньше 10%.

В Google Таблицах

Выделяем в таблице столбец с данными open rate. В главном меню нажимаем «Формат» — «Условное форматирование».

Справа в блоке «Правила условного форматирования» по умолчанию будет выбрана вкладка «Один цвет». На ней в выпадающем списке «Правила форматирования» выбираем «Меньше» и в поле ниже указываем интересующее нас значение: 10%. Если нужно, изменим цвет заливки, цвет и начертание шрифта. Нажимаем «Готово» для сохранения.

В Excel

Выделяем в таблице столбец со значениями open rate без заголовка. В главном меню нажимаем «Условное форматирование» — «Правила выделения ячеек» — «Меньше».

Откроется окошко «Создание правила форматирования». В нём выбираем: 

• «Начертание» — «Классическая», 
• «Форматировать только ячейки, которые содержат», 
• «Значение ячейки» — «Меньше» — «10%» (указать вручную или выбрать).

При необходимости в выпадающем списке «Форматировать с помощью» выбираем цвет для ячейки и шрифта. Нажимаем «ОК».

Форматирование ячейки по значению другой ячейки

Другая частая ситуация — когда нам нужно выделить значения ячеек, которые зависят от данных в других ячейках. Например, выделим значения open rate только для получателей из Сегмента В.

В Google Таблице

Выделяем в таблице столбец со значениями open rate — который будем форматировать, и столбец «Получатели» — в котором находятся условия. В главном меню нажимаем «Формат» — «Условное форматирование».

В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле под ним пишем формулу: 

=C:C=»Сегмент B»

При необходимости выбираем цвет для ячейки и шрифта. Нажимаем «Готово» для сохранения.

В Excel

Выделяем диапазон: столбец «open rate», котором будет форматировать ячейки, и столбец «Получатели», в котором находятся условные значения. В главном меню нажимаем «Условное форматирование» — «Создать правило».

В появившемся окошке «Создание правила форматирования» выбираем «Начертание» — «Классическая», затем в выпадающем списке: «Использовать формулу для определения форматируемых ячеек». В поле ниже пишем саму формулу:

=ЕСЛИ($C2=»Сегмент B»;$D$2:$D$15)

Форматирование строки по значению ячейки

Цветом можно выделить не просто одну ячейку, содержащую определенное значение, а всю строку. Например, закрасим красным строку, где в столбце «Получатели» указано «Сегмент В».

В Google Таблицах

Выделяем всю таблицу без заголовков. В главном меню нажимаем «Формат» — «Условное форматирование».

В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле ниже пишем:

=$C:$C=»Сегмент B»

Обратите внимание на символ $ перед именем ячейки — он фиксирует, что мы проверяем только этот столбец (адрес не изменится, если протягивать или переносить формулу). Далее при необходимости выбираем цвет форматирования и нажимаем «Готово» для сохранения.

В Excel

Выделяем значения всей таблицы. В главном меню нажимаем «Условное форматирование» — «Создать правило».

В окошке «Правила форматирования» в выпадающем списке «Начертание» выбираем «Классическая». Ниже в выпадающем списке — «Использовать формулу для определения форматируемых ячеек». В строке ниже пишем формулу:

=$C2=»Сегмент B»

В списке «Форматировать с помощью» задаём внешний вид выделяемых ячеек. Нажимаем «ОК».

Форматирование ячеек с датами

Выделим в таблице все даты за прошлый месяц.

В Google Таблице

Выделяем столбец с датами рассылок. Важно: все значения в этом столбце должны быть в формате даты. В главном меню выбираем «Формат» — «Условное форматирование».

В блоке «Правила условного форматирования» (на вкладке «Один цвет» в выпадающем списке «Правила форматирования») выбираем «Дата после», а в следующем выпадающем списке — «Точная дата». Здесь мы указываем, например, первое число прошлого месяца и нажимаем «Готово» для сохранения.

В Excel

Выделяем столбец с датами рассылок. Важно: все значения в этом столбце должны быть в формате даты. В главном меню нажимаем: «Условное форматирование» — «Правила выделения ячеек» — «Дата».

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

В пункте «Форматировать с помощью» при необходимости задаём цвет ячейки, начертание и цвет шрифта. Нажимаем «ОК».

Выделение повторяющихся значений

Выделим цветом повторяющиеся значения — например, в столбце с результатами open rate.

В Google Таблицах

Выделяем столбец с результатами open rate. В главном меню нажимаем «Формат» — «Условное форматирование».

В блоке «Правила условного форматирования» на вкладке «Один цвет» в выпадающем списке «Правила форматирования» выбираем «Ваша формула» и в поле ниже добавляем:

=СЧЁТЕСЛИ($D$1:$D$15;D2)>1 

При необходимости выбираем цвет форматирования. Нажимаем «Готово» для сохранения.

В Excel

Выделяем столбец со значениями open rate. Нажимаем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».

Появится окошко «Создание правила форматирования», все настройки в нём можно оставить по умолчанию. Нажимаем «ОК».

Применение нескольких условий

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

В Google Таблице

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

Эти правила можно удалять и менять местами. А по двойному щелчку откроется возможность редактирования.

Также два и более правила форматирования можно задать в одной формуле. 

В выпадающем списке «Правила форматирования» выбираем «Ваша формула» и пишем:

=И(условие1;условие2) — в случае, если должны выполняться оба условия.

=ИЛИ(условие1;условие2) — в случае, если должно выполняться хотя бы одно условие.

В Exсel

Все условия форматирования можно найти через пункт меню: «Условное форматирование» — «Управление правилами». Здесь можно удалять, добавлять, дублировать, изменять правила и диапазоны, к которым они будут применяться.

Коротко о главном

Условное форматирование —весьма полезный инструмент, при этом довольно несложный в использовании. И в Google Таблицах, и в Excel общий алгоритм такой:

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

2. Выбираем пункт «Условное форматирование» в главном меню.

3. Задаем условие для форматирования и внешний вид выбранных ячеек.

При этом в условиях форматирования можно использовать формулы, что дополнительно расширяет возможности для создания отчетов.

Шаблоны для каждого примера из статьи вы можете найти в этой таблице. Чтобы редактировать, нужно открыть в меню «Файл» и нажать «Создать копию».