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

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

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

Как изменить внешний вид строки на основе числа в определенной ячейке

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для этого случая есть функция =SEARCH("Срок поступления", $E2)>0, где:

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

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

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

Например, столбец «Доставка» может содержать текст «Срочно, срок выполнения через 6 часов», и соответствующая ячейка по-прежнему будет иметь правильный формат.

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

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

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

Как и в случае со строкой, описанные выше шаги можно применить к одной ячейке или диапазону значений. В этом примере форматирование применяется только к ячейкам столбца «Номер заказа»:

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

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

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

=ИЛИ($F2=»Срок погашения через 1 день», $F2=»Срок погашения через 3 дня»)

=ИЛИ($F2=»Срок погашения через 5 день», $F2=»Срок погашения через 7 дня»)

И формула =И можно использовать, например, для проверки наличия числа в столбце Кол-во. больше или равно 5 и меньше или равно 10.

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

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