Узнайте, как быстро изменить цвет всей строки на листах Excel на основе значения ячейки. Откройте для себя хитрости и примеры формул для числовых и текстовых значений.
В предыдущей статье мы обсуждали, как изменить цвет ячейки в зависимости от ее значения. На этот раз мы поговорим о том, как выделить в Excel 2010 и 2013 всю строку в зависимости от значения ячейки, а также раскроем некоторые хитрости и покажем примеры формул для работы с числовыми и текстовыми значениями.
- Измените цвет строки на основе числового значения одной из ячеек
- Создаем несколько правил форматирования и определяем приоритет для каждого
- Измените цвет линии на основе текстового значения одной из ячеек
- Изменить цвет ячейки в зависимости от значения другой ячейки
- Измените цвет линии в зависимости от различных условий
Как изменить цвет строки на основании числового значения одной из ячеек
Допустим, у нас есть такая таблица бизнес-заказов:
Мы хотим раскрасить строки в разные цвета в зависимости от заказанного количества продукта (значение в столбце Кол-во), чтобы выделить наиболее важные заказы. Инструмент Excel — «Условное форматирование» поможет нам справиться с этой задачей».
- Прежде всего, выделите все ячейки, цвет заливки которых мы хотим изменить.
- Чтобы создать новое правило форматирования, нажмите «Главная»> «Условное форматирование»> «Новое правило).
- В появившемся диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу», чтобы определить, какие ячейки следует форматировать, и, ниже, в поле «Значения формата, где эта формула истинна», мы вводим следующее выражение:
= $ C2> 4
Вместо C2 вы можете вставить ссылку на другую ячейку в таблице, значение которой необходимо использовать для проверки условия, а вместо 4 вы можете указать любое число, которое хотите. Очевидно, что в зависимости от выполняемого действия можно использовать операторы сравнения меньше (
= $ C2 = $ C2 = 4
Обратите внимание на знак доллара $ перед адресом ячейки — он нужен, чтобы буква столбца не менялась при копировании формулы в остальные ячейки строки. Фактически, это секрет фокуса, поэтому форматирование всей строки меняется в зависимости от значения данной ячейки.
- Нажмите кнопку «Формат» и перейдите на вкладку «Заливка», чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку «Другие цвета», выберите подходящий и дважды нажмите «ОК.
Аналогичным образом на остальных вкладках диалогового окна «Формат ячеек» можно настроить другие параметры форматирования, например цвет шрифта или границы ячеек. - В поле Предварительный просмотр отображается результат выполнения созданного правила условного форматирования:
- Если все прошло, как ожидалось, и выбранный цвет подходит, нажмите OK, чтобы увидеть созданное правило в действии. Теперь, если значение в Qty больше 4, вся соответствующая строка таблицы станет синей.
Как видите, изменить цвет всей строки в Excel на основе числового значения одной из ячеек совсем несложно. Далее мы рассмотрим еще несколько примеров формул и пару уловок для решения более сложных задач.
Как создать несколько правил условного форматирования с заданным приоритетом
В приведенной выше таблице в качестве примера, вероятно, было бы удобнее использовать разные цвета заливки, чтобы выделить строки, содержащие столбец Qty, с разными значениями. Например, создайте другое правило условного форматирования для строк, содержащих значение 10 или более и выделенных розовым цветом. Для этого нам понадобится формула:
= $ C2> 9
Чтобы оба правила, которые мы создали, работали одновременно, вам необходимо расставить приоритеты.
- На вкладке Главная в разделе Стили щелкните Условное форматирование> Управление правилами
- В раскрывающемся списке «Показать правила форматирования для» выберите «Этот лист». Если вы хотите изменить настройки только для правил выбора, выберите параметр «Текущий выбор).
- Выберите правило форматирования, которое нужно применить первым, и с помощью стрелок переместите его в верхнюю часть списка. Должно получиться так:
Нажмите ОК, и линии в указанном фрагменте сразу поменяют цвет в соответствии с формулами обоих правил.
Как изменить цвет строки на основании текстового значения одной из ячеек
Чтобы упростить контроль выполнения заказа, мы можем выделить в нашей таблице разными цветами строки заказов с разным статусом доставки, информация о которых содержится в столбце Доставка:
- Если срок доставки заказа является будущим (значение Просрочено через X дней), заполнение этих ячеек должно быть оранжевым;
- Если заказ доставлен (значение «Доставлено»), заполнение этих ячеек должно быть зеленым;
- Если время доставки заказа в прошлом (прошлое значение), заполнение этих ячеек должно быть красным.
И, конечно же, цвет заливки ячеек должен измениться при изменении статуса заказа.
Формула для значений «Доставлено» и «Просрочено» понятна, она будет похожа на формулу в нашем первом примере:
= $ E2 = «Доставлено»
= $ E2 = «Срок действия истек»
Проблема кажется более сложной для заказов, которые необходимо доставить в течение X дней (срок действия истекает через X дней). Мы видим, что время доставки для различных заказов составляет 1, 3, 5 или более дней, что означает, что приведенная выше формула здесь не применяется, поскольку она нацелена на точное значение.
В этом случае лучше использовать функцию ПОИСК и для поиска частичного совпадения написать следующую формулу:
= ПОИСК («Срок действия в течение»; $ E2)> 0
= ПОИСК («Срок сдачи», $ E2)> 0
В этой формуле E2 — это адрес ячейки, на основании которого мы будем применять правило условного форматирования; знак доллара $ требуется для применения формулы ко всей строке; условие «> 0» означает, что правило форматирования будет применено, если указанный текст будет найден (в нашем случае это «Срок действия истекает через.
Совет: Если в формуле используется условие «> 0», строка будет выделена в любом случае, когда указанный текст будет найден в ключевой ячейке, независимо от того, где он находится в ячейке. В примере таблицы на рисунке ниже столбец «Доставка» (столбец F) может содержать текст «Срочно, срок действия истекает через 6 часов» (что означает — срочно, доставка в течение 6 часов), и эта строка также будет окрашена.
Чтобы выделить цветом те строки, в которых содержимое ключевой ячейки начинается с указанного текста или символов, формулу необходимо записать следующим образом:
= ПОИСК («Срок сдачи»; $ E2) = 1
= ПОИСК («Срок сдачи», $ E2) = 1
при использовании такой формулы нужно быть очень осторожным, чтобы проверить, нет ли пробелов в ячейках столбца ключей данных. Иначе можно долго ломать голову, пытаясь разобраться, почему формула не работает.
Итак, следуя тем же шагам, что и в первом примере, мы создали три правила форматирования, и наша таблица стала выглядеть так:
Как изменить цвет ячейки на основании значения другой ячейки
Действительно, это частный случай проблемы изменения цвета линии. Вместо всей таблицы выберите столбец или диапазон, в котором вы хотите изменить цвет ячейки, и используйте формулы, описанные выше.
Например, мы можем настроить три наших правила, чтобы выделять только ячейки, содержащие номер заказа (столбец Номер заказа), на основе значения другой ячейки в этой строке (используя значения из столбца Доставка).
Как задать несколько условий для изменения цвета строки
Если вам нужно выделить строки одним цветом при отображении одного из нескольких значений, вместо создания нескольких правил форматирования вы можете использовать функции И, ИЛИ (ИЛИ), а затем объединить несколько условий в одно правило.
Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, — желтым. Формулы будут выглядеть так:
= ИЛИ ($ F2 = «Срок оплаты через 1 день»; $ F2 = «Срок оплаты через 3 дня»)
= ИЛИ ($ F2 = «Срок оплаты через 1 день», $ F2 = «Срок оплаты через 3 дня»)
= ИЛИ ($ F2 = «Срок сдачи 5 дней»; $ F2 = «Срок сдачи 7 дней»)
= ИЛИ ($ F2 = «Срок сдачи 5 дней», $ F2 = «Срок сдачи 7 дней»)
Для отбора заказов с количеством товаров не менее 5, но не более 10 (значение в графе Кол-во) запишем формулу с функцией И):
= И ($ D2> = 5, $ D2 = И ($ D2> = 5, $ D2
Конечно, в своих формулах можно использовать не только два, но и все необходимые условия. Например:
= ИЛИ ($ F2 = «Срок оплаты через 1 день»; $ F2 = «Срок оплаты через 3 дня»; $ F2 = «Срок оплаты через 5 дней»)
= ИЛИ ($ F2 = «Срок сдачи 1 день», $ F2 = «Срок сдачи 3 дня», $ F2 = «Срок сдачи 5 дней»)
Совет: теперь, когда вы узнали, как окрашивать ячейки в разные цвета, в зависимости от значений, которые они содержат, вы можете узнать, сколько ячеек выделено определенным цветом, и вычислить сумму значений в этих ячейках клетки. Хочу вас обрадовать, это действие тоже можно делать в автоматическом режиме, а решение этой проблемы мы покажем в статье, посвященной вопросу Как посчитать количество, сумму в Excel и установить фильтр для ячеек определенного цвета.
Мы показали лишь некоторые из возможных способов сделать таблицу похожей на полосатую зебру, цвет которой зависит от значений в ячейках и может меняться вместе с изменением этих значений. Если вы ищете что-то еще для своих данных, просто дайте нам знать, и вместе мы обязательно что-то найдем.
