Как сделать так, чтобы он выглядел по-разному в зависимости от содержимого таблицы? И при этом чтобы это происходило автоматически? В этом нам поможет условное форматирование Google Таблиц. Мы узнаем, как правильно спроектировать условия и изменить внешний вид не только отдельных ячеек, но и всей строки таблицы.
Итак, рассмотрим несколько вариантов изменения формата данных:
- По формуле.
- На основе текстового значения другой ячейки
- Мы используем подстановочные знаки.
- На основе числового значения другой ячейки
- Как удалить условное форматирование в Google Таблицах?
- В разных условиях
- Как раскрасить всю строку в таблице Google?
- Одно состояние
По одному условию
Предположим, у нас есть данные о продажах шоколада, записанные в электронной таблице. Каждая строка в этой таблице представляет собой заказ, полученный от конкретного клиента. В столбце G с помощью выпадающего списка (ссылка) отмечаем, выполнен заказ или нет.
Что нас может заинтересовать в этих заказах? Начнем с выделения тех заказов, которые стоят более 10 000.
У нас эти данные записаны в столбце F. Выделите мышкой диапазон значений значения ордера F2: F22.
Затем нажмите Меню -> Формат -> Условное форматирование.
Нажмите «Форматировать ячейки» и в появившемся окне выберите «Больше или равно», а затем в поле чуть ниже укажите значение 200. Это означает, что все ячейки в выбранном нами диапазоне, значение которых больше или равно 200, он будет выделен в формате, который мы создали здесь, например: жирным красным шрифтом на желтом фоне.
Сразу видим результат: изменилось расположение отдельных ячеек.
Вы можете настроить формат в Google Таблицах, используя более одного цвета, но вы также можете использовать диапазон цветов. Для этого выберите в окне редактирования «Градиент» и воспользуйтесь готовыми наборами цветов. Или вы можете указать значения цвета для минимальной и максимальной точек, а также для средней точки (при необходимости).
В результате получается цветовая шкала: чем светлее цвет, тем ниже значение порядка, а чем темнее цвет, тем выше порядок.
По нескольким условиям
Если цветовая схема кажется вам слишком разнообразной, в разделе «Один цвет» вы можете добавить несколько условий и определить свой вариант для каждого из них. Для этого нажмите «Добавить правило”.
Давайте закрасим заказы от 10 000 до 3 000 цветов.
Как видите, здесь есть 2 правила. Первый предназначен для значений больше 10 000, а второй — для значений меньше 3000.
Вы можете добавить столько правил, сколько захотите.
На основе формулы.
Список условий, которые нам предлагают и которые мы можем применить к нашему диапазону данных, обширен. Однако этого, конечно, все равно будет недостаточно. Поэтому в Google Таблицах вы можете ввести свою формулу в качестве условия. В нем он описывает выполнение определенного условия. Другими словами, результат этой формулы должен быть «Истина» или «Ложь».
Чтобы ввести свою формулу, используйте последний пункт в раскрывающемся списке условий: «Ваша формула”.
Посмотрим, как это работает.
Допустим, мы хотим знать, какие из наших существующих заказов были выполнены за выходные. Здесь нам не поможет ни один из предложенных стандартных условий.
Выберите диапазон дат A2: A22, затем вызовите меню как обычно.
В раскрывающемся меню выберите пункт «Ваша формула» и в появившемся окне введите формулу, которая поможет нам определить номер дня недели по дате.
= ДЕНЬ НЕДЕЛИ (A2: A22,2)> 5
Если число больше 5, значит суббота или воскресенье. Затем к этой ячейке в таблице Google будет применен формат, который мы сами определим чуть ниже в том же окне конфигурации.
Как видите, теперь выделены выходные.
Еще один пример. Выбирайте заказы из темного шоколада особого цвета. Для этого делаем все так же, выбираем диапазон данных с названиями шоколада D2: D22, а затем используем условие
= REGEXMATCH (D2: D22; «Черный”)
Эта функция вернет True, если в названии шоколада появится слово «Black”.
Посмотрите, что у нас получилось: выделялись заказы «Горький шоколад» и «Супер горький шоколад». Теперь нам не нужно долго искать их глазами, особенно если наша таблица содержит десятки и сотни строк.
Используем подстановочные знаки.
Если мы хотим задать условие для текстовых значений, то очень удобно использовать формат ячейки с условием «Текст содержит”.
Для большей гибкости условного поиска можно использовать специальные символы подстановки. Подстановочные знаки можно использовать в полях «Текст содержит» или «Текст не содержит».
Два наиболее часто используемых символа — это вопросительный знак (?) И звездочка (*).
Знак вопроса соответствует любому символу. Например, как вы можете видеть на рисунке, текстовое правило, содержащее «? O», будет выделять ячейки со значениями «Pink», «Cyan», «Gold», но не «Orange”.
«? O» означает, что буква «o» должна быть второй буквой с начала слова.
Чтобы пропустить любое количество символов (включая ноль (0)), используйте звездочку (*). Например, текстовое правило, содержащее «* p *», выделит черный, серый, серебристый, оранжевый, красный и розовый”.
Чтобы знак вопроса или звездочка в тексте не воспринимались как символы подстановки, перед ними обычно ставят тильду (~). Например, текстовое правило, содержащее «Красный *», в нашем примере выбирает ячейки с «Красным», а правило «Красный ~ *» не найдет никаких ячеек, поскольку будет искать значение «Красный*».
Как раскрасить цветом всю строку в Google таблице?
В примерах, которые мы обсуждали ранее, мы применяем правила к отдельным ячейкам в одном столбце. Вы, наверное, подумали: «Было бы хорошо, если бы это можно было проделать со всей таблицей!» И это возможно!
Попробуем выделить линии с отложенными ордерами особым цветом.
Для этого условие будет применено к данным в столбце G, и мы отформатируем всю электронную таблицу Google.
Обратите внимание, что мы включаем заголовок таблицы в область форматирования!
Обратите внимание, что мы применили формулу ко всей таблице A1: G22.
Далее воспользуемся собственной формулой, в которой указываем
= $ G1 = «Да”
Важно! Букве столбца должен предшествовать знак $. Это означает абсолютную ссылку на столбец, то есть для любого действия с таблицей формула всегда будет ссылаться на этот конкретный столбец, но номер строки может измениться.
То есть приказываем пройти по столбцу, начиная с первой строки вниз, и найти все ячейки со значением «Нет”.
Как видите, в результате были выбраны не только ячейки в столбце, которые мы проверили на предмет нашего условия.
Итак, вспомним 3 основных правила условного форматирования строк в таблице:
- Ассортимент — это вся таблица,
- В формуле не забудьте поставить знак $ перед буквой $ столбца $.
- Мы используем собственную формулу
На основе числового значения другой ячейки
Часто возникает вопрос: «Как применить условное форматирование, чтобы условие можно было легко изменить? В конце концов, чтобы каждый раз исправлять правило, требуется много времени».
Это совсем не сложно. Просто используйте свою формулу со ссылкой на ячейку, в которой вы указываете необходимое условие.
Вернемся к нашему примеру с заказами на продажу шоколада. Допустим, нас интересуют заказы, в которых количество товаров меньше 50, а также больше 100.
Рядом с нашей таблицей в столбце H мы указываем эти два условия.
Создадим правила для форматирования таблицы заказов.
Мы установим диапазон применения A2: G22, чтобы не менять заголовок таблицы.
Затем мы действуем уже знакомым нам образом, используя нашу собственную формулу.
Вот как выглядит формула для заказов более 100:
= $ E2> = $ H $ 3
Обратите внимание, что здесь вы также должны использовать абсолютные ссылки.
Напоминаем, что знак $ перед буквой столбца обозначает абсолютную ссылку на этот столбец. А если перед номером строки стоит знак $, то на строку устанавливается абсолютная ссылка.
В нашем примере $ H $ 3 означает абсолютную ссылку на ячейку, то есть при любых манипуляциях с таблицей она всегда будет ссылаться на эту ячейку.
Важно! Мы используем абсолютную ссылку на столбец E и абсолютную ссылку на ячейку H3, которая содержит наш предел: 100. Если вы этого не сделаете, формула не будет работать!
Теперь добавим второе условие. Выберите заказы, в которых количество меньше 50. Нажмите «Добавить правило» и добавьте второе условие, как на предыдущем шаге.
Обратите внимание, что формула условия теперь выглядит так:
= $ E2
Самый крупный и самый мелкий заказы выделены цветом. Миссия выполнена. Однако не очень хорошо, что на листе с таблицей у нас есть лишние числа, которые могут нам мешать и портить внешний вид таблицы.
Все вспомогательные данные правильнее вынести на отдельный лист.
Перейдем к листу 2 и обозначим эти новые условия.
Затем мы создаем правила, относящиеся к этим ограничениям.
И вот здесь возникает проблема. Если мы просто подставим адрес ячейки из листа 2 в формулу, мы получим ошибку. Прямые ссылки на ячейки в формуле возможны только из текущего листа. Делать?
Нам помогает функция ДВССЫЛ, которая позволяет вернуть ссылку на ячейку, адрес которой записан в виде текста.
Вот как будет выглядеть ссылка на ячейку:
= $ E2> = КОСВЕННО («2! B2”)
И вторая формула
= $ E2
В результате мы получаем тот же результат, что и раньше, но наш лист не загроможден дополнительными данными.
Теперь мы можем изменять условия без изменения настроек. Вам просто нужно изменить данные в ячейках, и у вас будет новая таблица.
На основе текстового значения другой ячейки
Мы узнали, как применять условное форматирование, используя числовые данные определенной ячейки. Что, если в условии используются текстовые данные? Посмотрим, как это сделать.
Попробуем выделить заказы из темного шоколада.
В ячейке B5 листа 2 пишем условие: «Черный”.
Затем возвращаемся к нашему листу с таблицей и снова выбираем область A2: G22.
Затем зайдите в меню. В поле «Ваша формула» пишем
= REGEXMATCH ($ D2: $ D22, INDIRECT («2! $ По 5 долларов”))
Важно! Не забываем, что необходимо указать абсолютные ссылки на диапазон D2: D22, в котором мы будем искать слово «Черный”.
Функция ДВССЫЛ («2! $ G $ 5») позволит нам получить значение, записанное в ячейке B5 листа 2, то есть слово «Черный”.
Итак, мы выделили те заказы, в названии которых есть слово, указанное в ячейке B5 листа 2.
Конечно, могло быть и проще. Наша формула может выглядеть так:
= REGEXMATCH ($ D2: $ D22, «Черный”)
Но тогда, если бы нам пришлось выбрать другой продукт, нам пришлось бы отредактировать настройки. А это намного сложнее и требует много времени, чем просто изменить значение в ячейке B5.
Как удалить условное форматирование в Google таблице
Для этого сначала выберите диапазон ячеек, к которому вы его применили.
Затем нажмите Меню -> Формат-> Условное форматирование. Или просто щелкните одну из ячеек, к которой он относится.
В открывшемся справа окне вы увидите все созданные вами условия.
Наведите указатель мыши на условие, которое хотите удалить, и щелкните значок корзины.
Созданный ранее макет будет удален.
Если вы не помните, какой именно диапазон ячеек нарисовали, или если вы просто хотите сделать все быстрее, выберите область ячеек большего размера и нажмите