Условное форматирование в Google Таблицы (Google Sheets)
Условное форматирование в Google Таблицы - это функционал, который позволяет автоматически изменять внешний вид ячеек в зависимости от их значения или выполнения определенных условий. Это позволяет пользователю выделять определенные данные, делая их более заметными или выделяя определенные паттерны (в этом плане паттерн понимается, как повторяющийся шаблон или образец).
Чтобы использовать условное форматирование в Google Таблицы, необходимо в меню выбрать:
Формат > Условное форматирование
Справа появится боковая панель, где мы можем добавлять и настраивать правила условного форматирования.
Так как в ячейке у нас нет ни одного правила, у нас тут ничего нет, если у вас будут добавенные правила, то они все будут здесь отображены.
После того, как мы нажмём на добавить правило, у нас появляются две вкладки:
- Один цвет
- Градиент
В начале рассмотрим возможности вкладки Один цвет. Правила форматирования данной вкладки разбита на четрые блока типов данных:
- Текстовые
- Даты
- Числа
- Формулы
Правила условного форматирования текстовых данных в Google Таблицы
Для начала нам необходимо подготовить данные на которых мы будем использовать условное форматирование. Пусть это будет список с названием товаров для дома, что-то связанное с подушками.
У нас тут необходимо:
- Выбрать диапозон (любой диапозон ячеек)
- Выбрать правило из выпадающего списка
- Настроить формат (жирный, курсивный, подчеркнутый, зачеркнутый, цвет текста, цвет заливки)
Давайте пройдемся по каждому правилу форматирования и посмотрим, как они работают.
Правила форматирования: Не содержит данных
Делаем настройки:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Не содержит данных
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас не содержат данные последние 4 ячейки и они отформатированы в соответствии с нашим форматом.
Правила форматирования: Содержит данные
Делаем настройки:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Содержит данные
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас содержат данные последние 15 ячеек и они отформатированы в соответствии с нашим форматом. Последние 4 остались неотформатированными, так как в них нет данных.
Правила форматирования: Текст содержит
Тут у нас появляется дополнительное поле, где необходимо указать значние или формулу. В начале сделаем настройку с указанием значения:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Текст содержит
- Значние: Аскона
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас содержат данные 1 ячейка и они отформатированы в соответствии с нашим форматом.
Если нам необходимо выделить ячейки в которых содержаться значения: Аскона или Guten Morgen, то тут можно создать два правила:
- Первое правило, форматировать ячейки если текст содержит Аскона,
- Второе правило, форматировать ячейки если текст содержит Guten Morgen
Но более правильно будет, написать формулу, которая объединяет два наших условия. Формула выглядит следующим образом:
=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())
Согласно выбранному диапозону и регулярному выражению, у нас данные содержат 2 ячейки и они отформатированы в соответствии с нашим форматом.
Давайте подробнее разберем, нашу формулу, что мы тут написала. Если перевести её на простой язык, то она звучит следующий образом:
=ЕСЛИ(ЗНАЧЕНИЕ В ЯЧЕЙКЕ A2 СООТВЕТСТВУЕЮТ РЕГУЛЯРНОМУ ВЫРАЖЕНИЮ "Аскона|Guten Morgen", ТО ВЗЯТЬ ЗНАЧНИЕ ЯЧЕЙКИ A2 ИНАЧЕ ВЕРНУТЬ ЛОЖЬ/НИЧЕГО)
Ячейка А2 в нашей формуле это начало диапозона, где мы хотим применить условное форматирование.
Важно запомнить, что когда мы пишем формулы для правила форматирования текстовых данных в Google Таблицы, формула должна возвращать текстовые значения.
Правила форматирования: Текст не содержит
Данное правило аналогично правилу текст содержит, но тут мы применяем форматирования к альтернативным записям. В сделаем настройку с указанием аналогичными значения, что и в примере выше:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Текст не содержит
- Значние: Аскона
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас не содержат данные все, кроме одной ячейки и они отформатированы в соответствии с нашим форматом.
Если нам необходимо выделить ячейки в которых не содержаться значения: Аскона или Guten Morgen, тут повторяем нашу формулу выше, которая объединяет два наших условия. Формула выглядит следующим образом:
=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())
Согласно нашему выбранному диапозону, у нас не содержат данные все, кроме двух ячейки, где используются слова Аскона или Guten Morgen и они отформатированы в соответствии с нашим форматом.
В оставщихся примерах мы не будем и делать пример с использованием формул, так как логика одна, главное, чтобы формула возвращала текстовое значение.
Правила форматирования: Текст начинается с
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Текст начинается с
- Значние: Подушка
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас текст начинается со слова Подушка в восьми ячейках и они отформатированы в соответствии с нашим форматом.
Правила форматирования: Текст заканчивается на
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Текст заканчивается на
- Значние: 2 шт
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас текст заканчивается на слово 2 шт в трёх ячейках и они отформатированы в соответствии с нашим форматом.
Правила форматирования: Текст в точности
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Текст в точности
- Значние: Комплект подушек Blue Sleep Mix 50х68 см, 2 шт
- Формат оставим: По умолчанию
Согласно нашему выбранному диапозону, у нас текст в точности с Комплект подушек Blue Sleep Mix 50х68 см, 2 шт в одной ячейки и она отформатированы в соответствии с нашим форматом.
Правила условного форматирования Даты в Google Таблицы
Для применения правил условного форматирования дат в Google Таблицы, добавим к нашей таблице дополнительный столбец с датами. Представим, что это будут даты закозов наших товаров.
Правила форматирования: Дата
При выборе данного правила, Google Таблицы на предлагают выбрать одно из дополнительных представленных значений:
- сегодня
- завтра
- вчера
- на прошлой недели
- в прошлом месяце
- в прошлом году
- точная дата
Принцип работы у них у всех одинаковый, для нашего примера предлагаю выбрать значение точная дата и поставим значние 04.06.2023. Настройка с указанием значений будет выглядеть следующим образом:
- Применить к диапозону, выбираем: B2:B20
- Правило форматирования, выбираем: Дата
- Дополнительное значение: точная дата
- Значние: 04.06.2023
- Формат оставим: По умолчанию
Мы видим на картинке выше, что в нашей таблице условное форматирование применилось к двум ячейкам.
Правила форматирования: Дата до
При выборе данного правила форматирования дополнительные значения такие же, как и в правиле форматирования Дата:
- сегодня
- завтра
- вчера
- на прошлой недели
- в прошлом месяце
- в прошлом году
- точная дата
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: B2:B20
- Правило форматирования, выбираем: Дата до
- Дополнительное значение: точная дата
- Значние: 01.04.2023
- Формат оставим: По умолчанию
У нас получилось, что количество ячеек соответсвующих нашему условному форматирования получилось 9 штук, все они стали залиты цветом согласно нашему формату.
Правила форматирования: Дата после
При выборе данного правила форматирования количество дополнительных значения у нас изменилось их осталось всего 4:
- сегодня
- завтра
- вчера
- точная дата
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: B2:B20
- Правило форматирования, выбираем: Дата после
- Дополнительное значение: точная дата
- Значние: 01.04.2023
- Формат оставим: По умолчанию
Количество ячеек соответсвующих нашему условному форматирования получилось 6 штук, все они залиты цветом согласно нашему формату.
Как вы могли обратить внимание в поле, где мы указывали значение, также можно указать формулу, тут все делается по аналоги, как мы писали формулу с текстовыми значниями, но надо помнить, что наша формула должна возвращать дату.
На вскидку сложно что-то придумать, но для примера давайте пропишем формулу, чтобы у нас применялось условное форматирование к дате после 01.06.2023. Наша формула будет выглядеть следующим образом:
=DATE(YEAR(B2);6;1)
Хороший пример с формулой будет, если вам при открытии таблицы необходимы, чтобы в таблице форматирование применялось к ячейкам дата которых меньше текущей даты, для этого можно использовать функции TODAY (СЕГОДНЯ). Пример настроек ниже:
- Применить к диапозону, выбираем: B2:B20
- Правило форматирования, выбираем: Дата до
- Дополнительное значение: точная дата
- Значние:
=TODAY()
- Формат оставим: По умолчанию
Ко всем ячейкам, где есть дата применилось наше условное форматирование, так как на момент написания у нас сегодня 05.01.2024, все даты заказов до текущей даты, которую мы имеем на данный момент.
Правила условного форматирования Чисел в Google Таблицы
Добавим к нашей таблице ещё один дополнительный столбец с количеством, что мы могли посмотреть, как работают правила условного форматирования в Google Таблицы с числами.
Тут у нас 8 основных правил форматирования, которые предлагают в Google Таблицы:
- Больше
- Больше или равно
- Меньше
- Меньше или равно
- Равно
- Не равно
- Между
- Не между
Пример мы зделаем на одном правиле форматирования Больше, а при переключение правил вы сможите посмотреть, как меняется наше форматирования, при выборе того или иного правила.
Правила форматирования: Больше
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: C2:C20
- Правило форматирования, выбираем: Больше
- Значние: 40
- Формат оставим: По умолчанию
Значнеия больше 40 у нас содержат 5 ячеек, все они залиты согласно нашему формату.
Очень часто необходимо применить условное форматирования к ячейкам значения которых больше среднего. В нашей таблицы среднее значение 44, но если в какой-то ячейке произойдет изменение 44 будет уже неактуально и нам заново надо искать среднее. Давайте сделаем чтобы среднее значние было динамическим и правила условного форматирования применялись ко всем значениям больше среднего.
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: C2:C20
- Правило форматирования, выбираем: Больше
- Значние:
=AVERAGE($C$2:$C$20)
- Формат оставим: По умолчанию
Значнеия больше 44 у нас содержат 4 ячеек, все они залиты согласно нашему формату.
Обратите внимание на два последних правила Между и Не между, тут нам необходимо указать два значения, но логика такая же, как в примере выше.
Правила условного форматирования с использованием формулы в Google Таблицы
Для использования формул в правилах условного форматирования в Google Таблицы, самое главное обращать внимание на формат данные:
- Если в ячейка текст, то наша формула должна возвращать текстовое значние
- Если в ячейка дату, то наша формула должна возвращать дату
- Если в ячейка число, то наша формула должна возвращать числовое значние
Если вы читали статью, с самого начала, то вы видели примеры с использованием формул по каждому типу данных. В правили использования формулы наши формулы, которые мы использовали выше необходимо немного скорректировать, указать первую ячейку диапозона. Пример скорректированных формул ниже.
Правила форматирования для текстовых значений: Ваша формула
Необходимо применить условное форматирование к ячейкам, где в тексте встречаются словосочетания: Аскона и Guten Morgen.
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: A2:A20
- Правило форматирования, выбираем: Ваша формула
- Значние:
=A2=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())
- Формат оставим: По умолчанию
Правила форматирования для даты: Ваша формула
Необходимо применить условное форматирование к ячейкам, где дата меньше сегодня.
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: B2:B20
- Правило форматирования, выбираем: Ваша формула
- Значние:
=B2<TODAY()
- Формат оставим: По умолчанию
Правила форматирования для чисел: Ваша формула
Необходимо применить условное форматирование, где значения выше среднего.
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: C2:C20
- Правило форматирования, выбираем: Ваша формула
- Значние:
=C2>AVERAGE($C$2:$C$20)
- Формат оставим: По умолчанию
Мы добрались до рассмотрения второй вкладки Градиент. Градиент это переход между двумя или более цветами.
Правила условного форматирования вкладка Градиент
Делаем настройку с указанием значения:
- Применить к диапозону, выбираем: C2:C20
- Правило форматирования, выбираем: Любой понравившийся градиент(для примера выбрали от красного к зеленому)
- Остальные настройки оставили по умолчанию, так как они заточены под конкретные случаи.
В нашем с вами примере красным цветом выделено минимальное значение, зеленым цветом максимальное с плавны переходом через белый цвет.
Условное форматирование в Google таблицы это крутой инструмент с помощью которого, можно подсвечивать данные по определенным правила.