Как изменить цвет строки в Excel по условию, в зависимости от условий

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

Ранее мы обсуждали методы изменения цвета фона ячейки на основе текста или числового значения в ней. Здесь же будут представлены рекомендации, как выделять необходимые строки в последних версиях Excel, основываясь на содержимом одной ячейки. Кроме этого, здесь вы найдете примеры формул, которые одинаково хорошо работают для всех возможных форматов ячеек.

Как поменять внешний вид строки, исходя из цифры в конкретной ячейке

Например, у вас открыт документ с таблицей сделок организации наподобие такой.

Предположим, вам нужно выделить строки различными оттенками, ориентируясь на то, что написано в ячейке в колонке Qty, чтобы наглядно понимать, какие из сделок наиболее выгодные. Для достижения этого результата, необходимо воспользоваться функцией «Условное форматирование». Следуйте пошаговой инструкции:

1. Выберите ячейки, которые необходимо отформатировать.
2. Создайте новое правило форматирования, нажав соответствующий пункт в контекстном меню, которое появится после нажатия на кнопку «Условное форматирование» на вкладке «Главная».

1. После этого появится диалоговое окно, где надо выбрать настройку «использовать формулу для определения форматируемых ячеек». Далее следует прописать такую формулу: =$C2>4 в поле ниже. Естественно, можно вставить свой адрес ячейки и собственный текст, а также заменять знак > на< или =. Кроме этого, важно не забыть поставить значок $ перед ссылкой на ячейку, чтобы закрепить ее при копировании. Это и дает возможность привязать цвет строчки к значению ячейки. Иначе при копировании адрес будет «съезжать».
2. Нажмите на «Формат» и переключитесь на последнюю вкладку, чтобы указать нужный оттенок. Если оттенки, предложенные программой, вам не понравились, всегда можно нажать на «More Colors» и выбрать такой оттенок, который нужен.
3. После выполнения всех операций необходимо дважды нажать на кнопку «ОК». Можно также выставить другие виды форматирования (тип шрифта или определенный стиль рамок клетки) на других вкладках этого окна.
4. Внизу окна находится панель предварительного просмотра, где можно увидеть, какой будет клетка после форматирования.
5. Если все полностью устраивает, нажмите на кнопку «ОК», чтобы применить изменения. Все, после выполнения этих действий все строки, в которых клетки содержат число больше 4, будут голубого цвета.

Как вы можете увидеть, изменение оттенка строки, на основе значения какой-то конкретной клетки, находящейся в ней, – не самая сложная задача. Можно применять и более сложные формулы, чтобы более гибко использовать условное форматирование для достижения этой цели.

Применение нескольких правил, согласно их приоритетности

В прошлом примере приводился вариант использования одного правила условного форматирования, но у вас может появиться желание применить сразу несколько. Что же делать в таком случае? Например, можно добавить правило, согласно которому будут подсвечиваться строки с числом 10 и больше, розовым цветом. Здесь необходимо дополнительно прописать формулу =$C2>9, а затем выставить приоритеты, чтобы все правила могли применяться без конфликтов между собой.

1. На вкладке «Главная» в группе «Стили» нужно кликнуть на «Условное форматирование» и в появившемся меню выбрать «Управление правилами» в самом конце списка.
2. Далее следует отобразить все правила, характерные для этого документа. Для этого необходимо найти вверху список «Показать правила форматирования для», и там выбрать пункт «Этот лист». Также через это меню можно настроить правила форматирования для конкретных выбранных ячеек. В описываемом нами случае необходимо управлять правилами для всего документа.
3. Далее необходимо выбрать правило, которое нужно применить в первую очередь и передвинуть его на верх списка, используя стрелочки. Получится такой результат.
4. После выставления приоритетов необходимо кликнуть на «ОК», и мы увидим, как соответствующие строчки поменяли свой цвет, согласно приоритету. Сначала программа проверила, больше ли значение в колонке Qty чем 10, и если нет, то больше ли оно, чем 4.

Изменение цвета целой строчки, исходя из текста, прописанного в ячейке

Предположим, что во время работы с таблицей возникнут сложности с быстрым отслеживанием того, какие товары уже доставлены, а какие — нет . А может, какие-то оказались в просрочке. Чтобы упростить эту задачу, можно попробовать выделять строки, исходя из текста, который находится в ячейке «Delivery». Предположим, нам необходимо задать следующие правила:

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

Простыми словами, цвет строки будет изменяться в зависимости от статуса заказа.

В целом, логика действий для доставленных и просроченных заказов будет такой же, как и в описанном выше примере. Необходимо в окне условного форматирования прописывать формулы =$E2=»Delivered» и =$E2=»Past Due» соответственно. Немного посложнее задача для сделок, которые будут истекать в течение нескольких дней.

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

Для этого случая существует функция =ПОИСК(«Due in», $E2)>0, где:

• первый аргумент в скобках – текст, имеющийся во всех описанных клетках,
• а второй аргумент – это адрес ячейки, на значение которой необходимо ориентироваться.

В англоязычной версии она известна, как =SEARCH. Она предназначена для поиска ячеек, имеющих частичное соответствие вводимому запросу.

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

Например, колонка «Delivery» может содержать текст «Urgent, Due in 6 Hours», и соответствующая ячейка в любом случае будет отформатирована правильным образом.

Если же необходимо применить правила форматирования к строкам, где ключевая ячейка начинается с нужной фразы, то необходимо прописать =1 в формуле вместо >0. 

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

Как изменить цвет ячейки, основываясь на значении в другой ячейке?

Точно так же, как и к строке, описанные выше шаги можно применить к отдельной ячейке или диапазону значений. В этом примере форматирование применяется лишь к ячейкам, находящимся в колонке «Order number»:

Как применить несколько условий для форматирования

Если необходимо применить несколько правил условного форматирования к строкам, то вместо прописывания отдельных правил, надо создать одно с формулами =ИЛИ или =И. Первая обозначает «одно из этих правил истинно», а вторая – «оба этих правила истинны».

В нашем случае мы прописываем следующие формулы:

=ИЛИ($F2=»Due in 1 Days», $F2=»Due in 3 Days»)

=ИЛИ($F2=»Due in 5 Days», $F2=»Due in 7 Days»)

А формулу =И можно использовать, например, для того, чтобы проверить, является число в колонке Qty. больше или равно 5, и при этом меньше или равно 10.

Пользователь может использовать больше одного условия в формулах.

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