Сегодня мы поговорим о продвинутом условном форматировании данных
Что делать, когда ним нужно выделить либо всю строку, либо весь столбец в зависимости от определенных данных в определенной ячейки
Выделяем всю строку, содержащую определенные данные
Давайте рассмотрим пример, в котором нам нужно выделить строки таблицы, которые относятся к заказчику, фамилия которого введена (выбрана) в определенной ячейке.
У нас есть таблица, а в ячейке F1 у нас выпадающий список с фамилиями.
Как делать выпадающие списки смотрите в моем уроке, посвященном проверке данных
Давайте настраивать форматирование.
Выделим нужный нам диапазон (тот диапазон, который мы будем форматировать. В нашем случае это А2:D15.
Выполняем команду меню "Формат" - "Условное форматирование", после чего попадаем в меню настройки условного форматирования
Проверяем, что в поле диапазон верно указан диапазон нашей таблицы. Если в дальнейшем наша таблица будет увеличиваться, то имеет смысл указать вместо А2:D15, просто А2:D (без указания номера строки, т.е. весь столбец d, до конца)
А вот в правилах форматирования нужно выбрать правило "Ваша формула". После этого можно вводить формулу в появившееся окно.
Важно! Любая формула вводится начиная с символа "=" (равно). Иначе формула не будет работать.
Для того, чтобы составить формулу, посмотрите на свой выделенный диапазон и представьте, что вы составляете для его первого элемента (самая верхняя и самая левая ячейка - левый верхний угол). В нашем случае это ячейка А2.
Мы должны составить формулу, при выполнении которой будет закрашиваться наша ячейка.
Что нам нужно проверить для ячейки А2? Нам нужно поверить, что ячейка С2 (с фамилией заказчика) равна ячейке F1 (где указана требуемая фамилия)
Получим формулу следующего вида:
=С2=F1
Если фамилии в С2 и F1 равны, то ячейка закрасится нужным нам цветом.
Мы не вводим формулы для каждой ячейки. Гугл таблицы автоматически трансформируют нашу формулу для всех остальных ячеек. Вопрос только в том, как именно он это сделает. А сделает он это автоматически - смещая все аргументы на один шаг.
Соответственно для ячейки B2 наша формула =С2=F1 будет отрабатываться следующим образом - мы со столбца А перешли на столбец B, соответственно и формула изменится - каждый аргумент в формуле сместится на один столбец, получим
=D2=G1
Естественно они не равны в столбце D у нас вообще не фамилии и мы не хотим это ни с чем сравнивать. В столбце G у нас вообще нет данных. Формула не сработает и В2 выделено не будет. Как этого избежать? Достаточно лишь подсказать Google таблицам, что ничего сдвигать и пересчитывать не нужно. Как это сделать? Очень просто - поставить знаки "$" (доллар).
Соответственно мы должны вводить формулу не =С2=F1, а =$С2=$F1
Дальше еще интереснее. Мы переходим ко второй строке соответственно смещаемся на одну строку вниз и соответственно формула будет восприниматься системой иначе, а именно: =$С3=$F2
Т.е. к каждому номеру строки будет добавлена единица. Касаемо С3 всё верно, нам так и нужно, а вот с F2 получится казус. В F2 у нас нет данных. Именно с этим аргументом у нас получилась нежелательная активность системы. Здесь не нужно ничего пересчитывать. Соответственно мы должны "заморозить" номер строки во втором аргументе
У нас получится финальный вариант формулы:
=$C2=$F$1
Давайте рассмотрим другой пример
У нас есть таблица в которой имеется столбец со статусами заказов. Выделим строки с заказами, которые имеют статус "Отменен", например красным цветом.
Выделяем диапазон и заходим в настройки условного форматирования. Правило - "Ваша формула"
Для первой ячейки нашего диапазона (а2) составим формулу:
=e2="Отменен"
Т.е. если в E2 стоит значение "Отменен", то ячейка A2 будет выделена
Учитывая знания, которые мы получили в предыдущем варианте трансформируем формулу
=$e2="Отменен"
Я поставил знак "$" перед названием столбца. Т.к. мы для каждой ячейки нашего выделенного диапазона проверяем значения только в столбце E. А вот строки пусть себе спокойно модифицируются (ползут).
Вот, что получилось:
Можно добавить еще одно правило, которое будет выделять, например зеленым, все строки с заказами у которых статус "Выполнен"
По аналогии получим формулу
=$e2="Выполнен"
Можно добавлять столько правил, сколько нужно.
Для того, чтобы посмотреть какие правила действуют на ту или иную ячейку достаточно ее выделить при этом во вкладке с правилами высвятятся все применяемые правила.
Щелкнув по правилу можно перейти в режим редактирования правил.
Вывести на экран вкладку с правилами можно через меню "Формат" - "Условное форматирование"
Так же в этой вкладке можно удалять правила
Помните, что если на одну ячейку воздействует несколько правил, то вы увидите отображение (выделение) только первого сработавшего правила
Дорогие друзья, я надеюсь что моя статья оказалась полезной. Поставьте пожалуйста лай, подпишитесь на канал и поделитесь со своими знакомыми.
Другие уроки по Гугл таблицам Вы можете почитать, перейдя к содержанию: