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