Как сделать так, чтобы в зависимости от содержания таблицы изменялось её оформление? И при этом чтобы это происходило автоматически? В этом нам поможет условное форматирование Google таблицы. Мы научимся правильно составлять условия и узнаем, как изменять оформление не только отдельных ячеек, но и целиком строки таблицы.
Итак, мы рассмотрим различные варианты изменения формата данных:
Условный формат по одному условию
Предположим, у нас имеются данные о продажах шоколада, записанные в таблицу Google. Каждая строка этой таблицы – это полученный заказ от определённого покупателя. В столбце G при помощи раскрывающегося списка (ссылка) мы отметили, выполнен заказ или нет.
Что нас может интересовать в этих заказах? Давайте для начала выделим те заказы, стоимость которых превышает 10000.
Эти данные у нас записаны в столбце F. Выделяем мышкой диапазон значений стоимости заказа F2:F22.
Затем нажимаем Меню -> Формат -> Условное форматирование.
Нажмите “Форматирование ячеек” и в появившемся окне выберите “Greater than or equal to” и затем в поле чуть ниже укажите значение 200. Это означает, что все ячейки в выбранном нами диапазоне, значение которых больше или равно 200, будут выделены форматом, который мы для примера создали здесь же – жирный красный шрифт на желтом фоне.
Результат мы тут же видим – оформление отдельных ячеек изменилось.
Форматирование по условию в Google таблицах можно настроить не только при помощи одного цвета, но и использовать цветовую гамму. Для этого в окне редактирования выберите “Градиент” и используйте готовые цветовые наборы. А можете сами указать цветовые значения для минимальной и максимальной точки, а также для точки середины (при необходимости).
Получилась цветовая шкала – чем светлее цвет, тем ниже стоимость заказа, а чем темнее цвет, тем больше заказ.
Форматирование по нескольким условиям
Если же цветовая гамма вам кажется слишком пёстрой, то в разделе “Один цвет” вы можете добавить несколько условий и для каждого из них определить свой вариант. Для этого нажмите на “Добавить правило”.
Давайте выделим цветом заказы больше 10000 и меньше 3000.
Как видите, здесь у нас 2 правила. Первое – для значений больше 10000. Второе – для значений, которые меньше 3000.
Вы можете добавить столько правил сколько посчитаете нужным.
Условное форматирование на основе формулы.
Список предлагаемых нам условий, которые мы можем применить к нашему диапазону данных, большой. Однако его, конечно, всё равно будет недостаточно. Поэтому в Google таблицах предусмотрено, что в качестве условия форматирования вы можете ввести свою собственную формулу. В ней вы опишете выполнение определённого условия. Иначе говоря, результатом этой формулы должны быть либо «Истина», либо «Ложь».
Для ввода собственной формулы форматирования в Google таблице используется самый последний пункт в раскрывающемся списке условий - “Ваша формула”.
Давайте посмотрим, как это работает.
Предположим, мы хотим знать, какие заказы из имеющихся у нас оформлялись в выходные дни. Ни одно из предлагаемых стандартных условий здесь нам не поможет.
Выделяем диапазон дат А2:А22, затем как обычно вызываем меню.
В раскрывающемся меню выбираем пункт “Ваша формула” и в появившемся окошке вписываем формулу, которая поможет нам определить номер дня недели по дате.
=WEEKDAY(A2:A22,2)>5
Если номер будет больше 5, значит это суббота или воскресенье. И тогда к такой ячейке Гугл таблицы будет применён формат, который мы сами определим чуть ниже в этом же окне настройки.
Как видите, выходные дни теперь выделены цветом.
Ещё один пример. Выделим при помощи особого цвета заказы черного шоколада. Для этого всё делаем аналогичным образом, выделяем диапазон данных с названиями шоколада D2:D22, и затем используем условие
=REGEXMATCH(D2:D22;"Черный")
Эта функция возвратит значение «Истина», если в названии шоколада будет встречаться слово “Черный”.
Посмотрите, что у нас получилось – выделенными оказались заказы "Черный шоколад" и "Супер Черный шоколад". Нам теперь не нужно долго искать их глазами – особенно если наша таблица содержит десятки и сотни строк.
Используем подстановочные знаки.
Если мы хотим установить условие для текстовых значений, то очень удобно при этом пользоваться форматом ячейки с условием “Текст содержит”.
Чтобы придать поиску условия дополнительную гибкость, можно использовать специальные подстановочные знаки. Подстановочные знаки могут использоваться с полями «Текст содержит» или «Текст не содержит».
Чаще всего используют два символа – знак вопроса (?) и звездочку (*).
Знак вопроса соответствует любому одиночному символу. Например, как вы видите на рисунке, текстовое правило, содержащее «?о», выделяет ячейки со значением «Розовый», "Голубой", "Золотой", но не “Оранжевый”.
«?о» означает, что буква “о” должна быть второй с начала слова.
Чтобы пропустить любое количество символов (в том числе и ноль (0)), используйте звездочку (*). Например, текстовое правило, содержащее «*р*», должно выделить "Черный", "Серый", "Серебряный", "Оранжевый", "Красный" и "Розовый".
Чтобы знак вопроса или звездочка в тексте не воспринимались как знаки подстановки, перед ними принято добавлять тильду (~). Например, текстовое правило, содержащее «Красный*», в нашем примере выделяет ячейки с «Красный», а правило «Красный~*» не найдет ни одной ячейки, так как будет искать значение «Красный*».
Как раскрасить цветом всю строку в Google таблице?
В примерах, которые мы разобрали выше, мы применяли правила к отдельным ячейкам одного столбца. Наверное, вы подумали: «Вот было бы хорошо, если бы так можно было бы делать со всей таблицей!». И это возможно!
Давайте попробуем выделить особым цветом строки с невыполненными заказами.
Для этого условие применим к данным столбца G, а форматировать будем всю Google таблицу.
Обратите внимание - в область форматирования включаем и заголовок таблицы!
Обратите внимание, что формулу мы применили ко всей таблице A1:G22.
Далее мы использовали собственную формулу, в которой указали
=$G1="Да"
Важно! Перед буквой столбца обязательно нужно поставить знак $. Это означает абсолютную ссылку на столбец, то есть при любых действиях с таблицей формула всегда будет ссылаться именно на этот столбец, а вот номер строки может меняться.
То есть, мы как бы приказываем перемещаться по столбцу начиная с первой строки вниз и искать все ячейки со значением “No”.
Как видите, в результате выделенными оказались не только ячейки столбца, в котором мы проверяли наше условие.
Итак, запомним 3 основных правила для условного оформления строк в таблице:
- Диапазон – вся таблица,
- Используем собственную формулу
- В формуле обязательно ставим перед буквой столбца знак $.
Как закрасить цветом на основе числового значения другой ячейки
Часто встречается вопрос: «Как применить условное форматирование в Google таблице, чтобы условие можно было бы легко изменять? Ведь каждый раз корректировать правило - это очень трудоёмко».
Это совсем несложно. Просто используйте собственную формулу со ссылкой на ячейку, в которой вы укажете необходимое вам условие.
Вернёмся к нашему примеру с заказами на продажу шоколада. Предположим, нас интересуют заказы, где количество товара меньше, чем 50, а также больше чем 100.
Рядом с нашей таблицей в столбце H укажем эти два условия.
Создадим правила форматирования таблицы заказов.
Диапазон применения установим A2:G22, чтобы не изменять заголовок таблицы.
Далее действуем уже знакомым нам способом, используя собственную формулу.
Вот как выглядит формула для заказов, количество в которых больше 100:
=$E2>=$H$3
Обратите внимание, что здесь также обязательно нужно использовать абсолютные ссылки.
Напомню, что знак $, стоящий перед буквой столбца, означает абсолютную ссылку на этот столбец. А если знак $ находится перед номером строки, то абсолютная ссылка установлена на строку.
В нашем примере $H$3 означает абсолютную ссылку на ячейку, то есть при любых действиях с таблицей она всегда будет ссылаться на эту ячейку.
Важно! Используем абсолютную ссылку на столбец E и абсолютную ссылку на ячейку H3, в которой записано наше ограничение – 100. Если это не сделать, формула работать не будет!
Теперь добавим второе условие. Выделим заказы, где количество меньше 50. Нажимаем “Добавить правило” и аналогично предыдущему действию добавляем второе условие.
Обратите внимание, формула условия теперь выглядит так:
=$E2<=$H$2
Самые крупные и самые мелкие заказы выделены цветом. Задача выполнена. Однако, не очень хорошо то, что у нас на листе с таблицей появились лишние цифры, которые могут нам мешать и портить вид таблицы.
Более правильно все вспомогательные данные размещать на отдельном листе.
Перейдём на лист 2 и укажем эти новые условия.
Далее создаём правила ссылаясь на эти ограничения.
И тут возникает проблема. Если мы просто подставим в формулу адрес ячейки из листа 2, то получим ошибку. Прямые ссылки на ячейки в формуле возможны только с текущего листа. Что же делать?
На помощь нам приходим функция ДВССЫЛ (англ. - INDIRECT), которая позволяет возвратить ссылку на ячейку, адрес которой написан как текст.
Вот так будет выглядеть ссылка на ячейку:
=$E2>=ДВССЫЛ("2!B2")
И вторая формула –
=$E2<=ДВССЫЛ("2!В1")
В результате получаем тот же результат, что и ранее, но наш лист не захламлён дополнительными данными.
Теперь мы можем менять условия, не занимаясь изменением настроек. Достаточно просто изменить данные в ячейках, и вы получите новую таблицу.
На основе текстового значения другой ячейки
Мы с вами научились применять условное форматирование, используя числовые данные определённой ячейки. А что, если условие будет использовать текстовые данные? Давайте посмотрим, как это можно сделать.
Давайте попробуем выделить заказы на черный шоколад.
В ячейке В5 листа 2 пишем условие: “Черный”.
Затем возвращаемся на наш лист с таблицей и вновь выделяем область A2:G22.
Затем переходим в меню. В поле “Ваша формула” пишем
=REGEXMATCH($D2:$D22,INDIRECT("2!$В$5"))
Важно! Не забывайте о том, что необходимо указывать абсолютные ссылки на диапазон D2:D22, в котором мы будем искать слово “Черный”.
Функция INDIRECT("2!$G$5") позволит нам получить значение, записанное в ячейке В5 листа 2, то есть слово “Черный”.
Итак, мы выделили цветом те заказы, где в названии товара встречается слово, указанное в ячейке В5 листа 2.
Конечно, можно было бы сделать проще. Наша формула могла бы выглядеть так:
=REGEXMATCH($D2:$D22,”Черный”)
Но тогда в том случае, если бы нам необходимо было бы выделить другой товар, пришлось бы редактировать настройки. А это намного сложнее и требует больше времени, чем просто изменить значение ячейки В5.
Как удалить условное форматирование в Google таблице?
Для этого сначала выделите диапазон ячеек, к которому вы применяли его.
Затем нажмите Меню -> Формат-> Условное форматирование. Либо просто кликните мышкой по одной из ячеек, в которой оно применено.
В открывшемся справа окне вы увидите все созданные вами условия.
Укажите мышкой на условие, которое нужно удалить, и нажмите значок корзины.
Созданное ранее оформление будет удалено.
Если же вы не помните, какой точно диапазон ячеек оформляли либо просто хотите сделать всё побыстрее, то выделите область ячеек побольше, затем нажмите
Меню -> Формат -> Очистить форматирование
Или же используйте комбинацию клавиш Ctrl + \
Важно! Помните, что при этом будет удалено не только условное, но всё прочее оформление вашей таблицы.
Надеемся, что применение условного форматирования в Google таблицах упростит вашу работу и сделает её результаты более наглядными.
