Условное форматирование в Google Таблицы (Google Sheets)

Условное форматирование в Google Таблицы (Google Sheets)

Условное форматирование в Google Таблицы - это функционал, который позволяет автоматически изменять внешний вид ячеек в зависимости от их значения или выполнения определенных условий. Это позволяет пользователю выделять определенные данные, делая их более заметными или выделяя определенные паттерны (в этом плане паттерн понимается, как повторяющийся шаблон или образец).

Чтобы использовать условное форматирование в Google Таблицы, необходимо в меню выбрать:

Формат > Условное форматирование

Где расположно в меню условное форматирование в Google Таблицы

Справа появится боковая панель, где мы можем добавлять и настраивать правила условного форматирования.

Боковая панель правил условного форматирования в Google Таблицы

Так как в ячейке у нас нет ни одного правила, у нас тут ничего нет, если у вас будут добавенные правила, то они все будут здесь отображены.

После того, как мы нажмём на добавить правило, у нас появляются две вкладки:

  • Один цвет
  • Градиент

В начале рассмотрим возможности вкладки Один цвет. Правила форматирования данной вкладки разбита на четрые блока типов данных:

  • Текстовые
  • Даты
  • Числа
  • Формулы

Правила условного форматирования текстовых данных в Google Таблицы

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

Таблица со списком названий товаров для применения условного форматирования в Google Таблицы

У нас тут необходимо:

  1. Выбрать диапозон (любой диапозон ячеек)
  2. Выбрать правило из выпадающего списка
  3. Настроить формат (жирный, курсивный, подчеркнутый, зачеркнутый, цвет текста, цвет заливки)

Давайте пройдемся по каждому правилу форматирования и посмотрим, как они работают.

Правила форматирования: Не содержит данных

Делаем настройки:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Не содержит данных
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Не содержит данных

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

Правила форматирования: Содержит данные

Делаем настройки:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Содержит данные
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Содержит данные

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

Правила форматирования: Текст содержит

Тут у нас появляется дополнительное поле, где необходимо указать значние или формулу. В начале сделаем настройку с указанием значения:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Текст содержит
  • Значние: Аскона
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Текст содержит, в дополнительном поле указали значение

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

Если нам необходимо выделить ячейки в которых содержаться значения: Аскона или Guten Morgen, то тут можно создать два правила:

  • Первое правило, форматировать ячейки если текст содержит Аскона,
  • Второе правило, форматировать ячейки если текст содержит Guten Morgen

Но более правильно будет, написать формулу, которая объединяет два наших условия. Формула выглядит следующим образом:

=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())

Условное форматирование в Google Таблицы, Правило форматирования: Текст содержит, в дополнительном поле указали формулу с регулярным выражением

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

Давайте подробнее разберем, нашу формулу, что мы тут написала. Если перевести её на простой язык, то она звучит следующий образом:

=ЕСЛИ(ЗНАЧЕНИЕ В ЯЧЕЙКЕ A2 СООТВЕТСТВУЕЮТ РЕГУЛЯРНОМУ ВЫРАЖЕНИЮ "Аскона|Guten Morgen", ТО ВЗЯТЬ ЗНАЧНИЕ ЯЧЕЙКИ A2 ИНАЧЕ ВЕРНУТЬ ЛОЖЬ/НИЧЕГО)

Ячейка А2 в нашей формуле это начало диапозона, где мы хотим применить условное форматирование.

Важно запомнить, что когда мы пишем формулы для правила форматирования текстовых данных в Google Таблицы, формула должна возвращать текстовые значения.

Правила форматирования: Текст не содержит

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

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Текст не содержит
  • Значние: Аскона
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Текст не содержит, в дополнительном поле указали значение, которое не должно находиться в тексте

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

Если нам необходимо выделить ячейки в которых не содержаться значения: Аскона или Guten Morgen, тут повторяем нашу формулу выше, которая объединяет два наших условия. Формула выглядит следующим образом:

=IF(REGEXMATCH(A2;"Аскона|Guten Morgen")=TRUE();A2;FALSE())

Условное форматирование в Google Таблицы, Правило форматирования: Текст не содержит, в дополнительном поле указали формулу с регулярным выражением

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

В оставщихся примерах мы не будем и делать пример с использованием формул, так как логика одна, главное, чтобы формула возвращала текстовое значение.

Правила форматирования: Текст начинается с

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Текст начинается с
  • Значние: Подушка
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Текст начинается с, в дополнительном поле указали значение, с которого должен начинаться текст

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

Правила форматирования: Текст заканчивается на

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Текст заканчивается на
  • Значние: 2 шт
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Текст заканчивается на, в дополнительном поле указали значение, на которое должен заканчиваться текст

Согласно нашему выбранному диапозону, у нас текст заканчивается на слово 2 шт в трёх ячейках и они отформатированы в соответствии с нашим форматом.

Правила форматирования: Текст в точности

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: A2:A20
  • Правило форматирования, выбираем: Текст в точности
  • Значние: Комплект подушек Blue Sleep Mix 50х68 см, 2 шт
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Текст в точности, в дополнительном поле указали значение, которому должен соответствовать текст

Согласно нашему выбранному диапозону, у нас текст в точности с Комплект подушек Blue Sleep Mix 50х68 см, 2 шт в одной ячейки и она отформатированы в соответствии с нашим форматом.

Правила условного форматирования Даты в Google Таблицы

Для применения правил условного форматирования дат в Google Таблицы, добавим к нашей таблице дополнительный столбец с датами. Представим, что это будут даты закозов наших товаров.

Таблица со списком названий товаров и датой заказа для применения условного форматирования для даты в Google Таблицы

Правила форматирования: Дата

При выборе данного правила, Google Таблицы на предлагают выбрать одно из дополнительных представленных значений:

  • сегодня
  • завтра
  • вчера
  • на прошлой недели
  • в прошлом месяце
  • в прошлом году
  • точная дата

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

  • Применить к диапозону, выбираем: B2:B20
  • Правило форматирования, выбираем: Дата
  • Дополнительное значение: точная дата
  • Значние: 04.06.2023
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Дата, в дополнительном поле указали значение, которому должна соответствовать дата

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

Правила форматирования: Дата до

При выборе данного правила форматирования дополнительные значения такие же, как и в правиле форматирования Дата:

  • сегодня
  • завтра
  • вчера
  • на прошлой недели
  • в прошлом месяце
  • в прошлом году
  • точная дата

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: B2:B20
  • Правило форматирования, выбираем: Дата до
  • Дополнительное значение: точная дата
  • Значние: 01.04.2023
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Дата до, в дополнительном поле указали значение, которому должна соответствовать дата

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

Правила форматирования: Дата после

При выборе данного правила форматирования количество дополнительных значения у нас изменилось их осталось всего 4:

  • сегодня
  • завтра
  • вчера
  • точная дата

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: B2:B20
  • Правило форматирования, выбираем: Дата после
  • Дополнительное значение: точная дата
  • Значние: 01.04.2023
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Дата после, в дополнительном поле указали значение, которому должна соответствовать дата

Количество ячеек соответсвующих нашему условному форматирования получилось 6 штук, все они залиты цветом согласно нашему формату.

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

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

=DATE(YEAR(B2);6;1)

Условное форматирование в Google Таблицы, Правило форматирования: Дата после, в дополнительном поле указали формулу, которому должна соответствовать дата возвращаемой формулой

Хороший пример с формулой будет, если вам при открытии таблицы необходимы, чтобы в таблице форматирование применялось к ячейкам дата которых меньше текущей даты, для этого можно использовать функции TODAY (СЕГОДНЯ). Пример настроек ниже:

  • Применить к диапозону, выбираем: B2:B20
  • Правило форматирования, выбираем: Дата до
  • Дополнительное значение: точная дата
  • Значние: =TODAY()
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Дата до, в дополнительном поле указали формулу, которому должна соответствовать дата возвращаемой формулой

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

Правила условного форматирования Чисел в Google Таблицы

Добавим к нашей таблице ещё один дополнительный столбец с количеством, что мы могли посмотреть, как работают правила условного форматирования в Google Таблицы с числами.

Таблица со списком названий товаров, датой заказа и количеством для применения условного форматирования для чисел в Google Таблицы

Тут у нас 8 основных правил форматирования, которые предлагают в Google Таблицы:

  • Больше
  • Больше или равно
  • Меньше
  • Меньше или равно
  • Равно
  • Не равно
  • Между
  • Не между

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

Правила форматирования: Больше

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: C2:C20
  • Правило форматирования, выбираем: Больше
  • Значние: 40
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Больше, в дополнительном поле указали значение, которому должна соответствовать наше правило форматирования

Значнеия больше 40 у нас содержат 5 ячеек, все они залиты согласно нашему формату.

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

Делаем настройку с указанием значения:

  • Применить к диапозону, выбираем: C2:C20
  • Правило форматирования, выбираем: Больше
  • Значние: =AVERAGE($C$2:$C$20)
  • Формат оставим: По умолчанию
Условное форматирование в Google Таблицы, Правило форматирования: Больше, в дополнительном поле указали формулу, которому должны соответствовать значнеия

Значнеия больше 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 Таблицы, красным цветом выделено минимальное значение, зеленым цветом максимальное

В нашем с вами примере красным цветом выделено минимальное значение, зеленым цветом максимальное с плавны переходом через белый цвет.

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

Warning: Undefined variable $previous in /home/k/katasojm/katasonoff.ru/public_html/wp-content/themes/katasonoff/page-google-sheets-single.php on line 275 Warning: Attempt to read property "ID" on null in /home/k/katasojm/katasonoff.ru/public_html/wp-content/themes/katasonoff/page-google-sheets-single.php on line 275 Warning: Undefined variable $next in /home/k/katasojm/katasonoff.ru/public_html/wp-content/themes/katasonoff/page-google-sheets-single.php on line 275 Warning: Attempt to read property "ID" on null in /home/k/katasojm/katasonoff.ru/public_html/wp-content/themes/katasonoff/page-google-sheets-single.php on line 275

Функция GETPIVOTDATA в Google Таблицы используется для получения данных из сводной таблицы. Она позволяет вам получать конкретные значения из сводной таблицы, используя определенные критерии. GETPIVOTDATA полезна при создания динамических отчетов и анализа данных, основанных на сводных таблицах. Она позволяет вам получать значения из сводных таблиц, используя определенные условия. Синтаксис =GETPIVOTDATA(value_name; any_pivot_table_cell; [original_column; …]; [pivot_item; …]) Список аргументов value_name […]

Функция HLOOKUP (ГПР) в Google Таблицы представляет собой инструмент поиска и извлечения данных из горизонтального диапазона ячеек. Она используется для нахождения значения в верхнем ряду (строке) таблицы и возвращения соответствующего значения из указанной строки ниже. Основное назначение HLOOKUP (ГПР) заключается в облегчении поиска данных в горизонтальных таблицах, когда необходимо найти значение в заголовке строки и […]

Функция COLUMNS (ЧИСЛСТОЛБ) в Google Таблицы используется для подсчета количества столбцов в заданном диапазоне ячеек. Это может быть полезно, при создании динамических формул, адаптированных к изменениям в структуре данных. COLUMNS (ЧИСЛСТОЛБ) облегчает подсчет столбцов в больших таблицах или диапазонах данных, предоставляя автоматизированный способ определения их числа. Это может быть полезным при создании формул, зависящих от […]

Функция COLUMN (СТОЛБЕЦ) в Google Таблицы используется для получения номера столбца для определенной ячейки. Она возвращает числовое значение, представляющее порядковый номер столбца, в котором находится указанная ячейка. COLUMN (СТОЛБЕЦ) упрощает работу в случаях, когда вы хотите автоматизировать процессы ваших таблиц, особенно если вы работаете с большим количеством данных. Она может использоваться в комбинации с другими […]