Меню

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



Как в Excel закрасить ячейки, строку или столбец по условию?

В этой статье вы узнаете, как закрасить ячейку по условию, выделять целые строки и столбцы в Excel 2016, 2013 и 2010 на основе какого-то критерия, а также найдете несколько советов и примеров формул, которые будут работать с числовыми значениями и значениями текстовых ячеек.

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

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

Выделение по условию целиком строки или столбца.

Выделение строки.

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

Прежде всего выделяем при помощи мыши весь диапазон интересующих нас данных – A2:D21. Шапку таблицы выделять не нужно. Затем действуем по уже отработанной схеме: вызываем меню функции и выбираем последний пункт – «Использовать формулу для определения форматируемых ячеек» (1). Далее записываем выражение (2):

Мы должны закрасить вторую строку таблицы в зависимости от значения в С2. Здесь есть маленькая хитрость.

Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только на столбец С. То есть, мы проверяем на условие «Бразилия» в выделенном нами диапазоне все позиции в этом столбце, то есть С2, С3, C4 и так далее. А вот закрашивать будем всю строку, так как ранее выделена была вся таблица. Для этого выбираем вариант оформления (3): цвет фона или шрифта, либо оба.

Напомню, что знак $, стоящий перед буквой столбца, означает абсолютную ссылку на этот столбец. А если знак $ находится перед цифрой, то абсолютная ссылка установлена на строку.

Вывод. Условное форматирование строки по значению ячейки основано на грамотном применении абсолютных и относительных ссылок в правиле форматирования. В используемой формуле должна быть абсолютная ссылка на столбец и относительная — на строку ($C2). При этом как область форматирования должна быть обозначена вся таблица (без шапки).

Выделение столбца.

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

Тем не менее, давайте рассмотрим пример с выделением по условию столбцов таблицы.

Итак, у нас есть табель рабочих смен. Нужно красным указать в нем на субботы и воскресенья.

Как и в предыдущем примере, определим для начала диапазон, который мы будем форматировать: =$B$3:$S$7. И вновь будем использовать формулу (2) для определения условия.

Функция ДЕНЬНЕД позволяет определить номер дня недели по указанной дате. Цифра 2 означает, что используется привычный нам порядок, когда первый день недели – это понедельник.

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

Все просто, но обратите внимание на одну важную деталь: знаком $ в ссылке мы фиксируем номер. Тем самым мы указываем программе, что надо последовательно перемещаться по второй строке в пределах указанного диапазона, и определять номер дня недели. И после этого применять формат к столбцу.

Выделение через строку.

Думаю, вам часто встречалось красивое оформление таблицы, когда строчки через одну были выделены. Конечно, такое оформление легко доступно, если преобразовать данные в «умную» таблицу. Но такое возможно только в Excel 2007 и более поздних версиях. Если же у вас старая версия программы, то наш способ вам очень даже может пригодиться.

Итак, возьмем для примера небольшую таблицу.

Выделим диапазон A1:D18.

Затем создаем новое правило при помощи формулы

В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.

В результате получилась «полосатая» таблица по принципу «четный-нечетный».

Закрасить группу строк.

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

Поясним эти вычисления.В качестве счетчика мы будем использовать номер текущей строки.Поскольку в квартале 3 месяца, то группировать будем тоже по три. Отсчет начинаем с А2.

  1. Счетчик в начале нужно установить в ноль. Для этого служит выражение (СТРОКА()-2). Поскольку начинаем со второй, то обнуляем счетчик, вычитая 2.
  2. Далее нужно определить, к какой по счету группе относится текущее местоположение курсора. Результат п.1 делим на 3.
  3. Отсекаем дробную часть при помощи функции ЦЕЛОЕ и получаем порядковый номер группы: ЦЕЛОЕ((СТРОКА()-2)/3).
  4. Добавляем 1, поскольку результатом для первой группы будет число меньше 1. А нужно, чтобы отсчет групп начинался с 1.
  5. Затем действуем по методике, отработанной в предыдущем примере: производим действия только с нечетными группами. Для этого используем функцию ОСТАТ с аргументом 2. То есть, находим остаток от деления на 2. Если число четное, то остаток будет равен нулю. Ноль равносилен результату ЛОЖЬ, поэтому с такими группами ничего не делаем. Если число нечетное, остаток от деления на 2 будет равен 1, что равноценно ИСТИНА. И вот тут-то мы и закрасим эту группу.
Читайте также:  Мужские ветровки синего цвета

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

Аналогично можно разбить на группы по 4 строки. Тогда в формуле выше просто замените цифру 3 на 4. И все будет работать.

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

Как видите, подход достаточно универсальный. Надеюсь, вам пригодится.

Вставляем отделяющие линии между группами строк.

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

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

Для этого сначала выделим весь наш диапазон с данными.

Внимание! Первую шапку таблицы не выделяем, начинаем с данных!

В нашем случае, выделяем A3:G33.

Затем далее действуем по уже отработанной схеме. В меню условного форматирования выбираем использование формулы (1). Далее записываем само правило:

Иначе говоря, мы проверяем, равна ли наша текущая дата предыдущей. Если не равна, значит, мы перешли к новому дню. Соответственно наше текущее положение нужно выделить. Выбираем формат (3). Тип границы – линия (4). Она будет использоваться по верхней границе (5).

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

Условное форматирование для сравнения двух столбцов.

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

Как найти и закрасить совпадающие ячейки в столбцах.

Можно использовать специальный пункт вкладки «Условное форматирование» — «Повторяющиеся значения».

На рисунке вы видите, что дубликаты выделены зеленым. Думаю, здесь все довольно просто.

Выделение совпадений двух столбцов построчно.

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

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

Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил B3:B25. То есть, в первой таблице мы закрасим ячейки, которые дублируются во второй таблице.

Обратите внимание, что в формуле используется абсолютная адресация на колонку. Это необходимо для того, чтобы происходило последовательный перебор значений, двигаясь вниз начиная с B3 до B25.

Как найти и закрасить совпадения в нескольких столбцах.

Представим, что наша задача – найти и выделить в столбце таблицы те значения, которые совпадают хотя бы с одним столбцом второй таблицы. В нашем случае – мы последовательно будем брать данные из столбца B и определять, нет ли такого же значения в этой же строке в нескольких колонках второй таблицы.

Давайте закрасим цветом те ячейки в столбце B, которые хотя бы однажды встречаются в G,H и I.

Диапазон форматирования – B3:B25. Выделяем его и в меню – «Создать правило» выбираем «Использовать формулу…»

Запишем правило условного форматирования:

Последовательно двигаемся сверху вниз и сравниваем каждую ячейку колонки B с находящимися в той же горизонтали значениями в G,H и I.

То есть, необходимо, чтобы выполнялось хотя бы одно из условий, достаточно одного совпадения.

Но если столбцов будет не 3, а, предположим, 10? Формула станет слишком громоздкой. Ведь придется указать 10 критериев совпадения.

Есть более простой способ. Изменим правило форматирования и используем функцию СЧЁТЕСЛИ:

СЧЁТЕСЛИ определяет, как часто определенное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в G,H и I таблицы, то есть в $G3:$I3. Если будет более одного совпадения, то срабатывает правило.Функция возвращает 1. А 1 в логическом выражении соответствует ИСТИНА, 0 — ЛОЖЬ. То есть, если счет равен нулю, то в текущей позиции нашего столбца содержится уникальное значение, которое больше нигде в диапазоне поиска не встречается. Согласитесь, так гораздо удобнее, чем писать множество однотипных критериев.

Читайте также:  Что означает флаг лнр цвета

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

Вот это новое правило:

Теперь совпадения мы ищем во всех столбцах таблицы 2, а не только в одном из них. Возможно, такой пример вам также будет полезен.

Обратите еще раз внимание на то, как определены абсолютные ссылки. Суть в том, что должен меняться номер строки, но не номер столбца. Тогда все будет работать.

Как закрасить ячейки при помощи «Найти и выделить».

В предыдущих материалах, ссылки на которые вы можете найти в конце этой статьи, мы уже рассматривали, как в Excel закрасить ячейку по условию в зависимости от другой ячейки либо от ее собственного содержимого. Условное форматирование позволяет отслеживать изменения в таблице и в соответствии с имеющимися значениями закрасить ячейку в определённый цвет. Но что если изменений в таблице больше не будет и в соответствии с определённым условием нужно закрасить значения «раз и навсегда»? Иначе говоря, речь идет о статичной таблице.

Возможно, вам пригодится более простой способ условного форматирования — использование инструмента «Найти и выделить».

Давайте вновь рассмотрим наш пример с продажами шоколада. Выделим цветом продажи меньше и больше 100 единиц, как показано ниже. К сожалению, никакие формулы мы здесь применить не можем, поэтому возможности отбора нужных значений сильно ограничены. Однако, можно использовать уже знакомые нам знаки подстановки — вопросительный знак ? и звездочку *. Напомню, что «?» позволяет заменить собой любой одиночный символ, а «*» — любую последовательность знаков. Как это применить? К примеру, 8? будет означать два символа, первый из которых — 8, а второй — любой. ?? означает два любых символа и т.д.

Итак, выделяем при помощи мышки область значений, которые мы хотим закрасить по условию, а затем используем инструмент «Найти и выделить». В окне поиска пишем . что означает в нашем случае любое двузначное число в диапазоне Е5:Е24. Обратите внимание, что если вы предварительно не укажете диапазон форматирования, то поиск будет произведен по всей таблице, что нам совершенно не нужно.

Нажимаем «Найти все» и в открывшемся внизу окошке тыкаем мышкой на любое из найденных значений. Затем нажимаем комбинацию клавиш CTRL+A, чтобы выделить все результаты, соответствующие условию. После этого закрываем окно поиска и видим, что все нужные цифры оказались выделены. Остается только во вкладке «Шрифт» выбрать нужный цвет заливки, или другой вариант оформления по вашему желанию.

Повторим все те же действия, только теперь в поиске укажем . , то есть искать будем трехзначные числа. Либо можно было указать . *, то есть отбирать все числа с разрядностью 3 и выше. Как видите, возможности у этого инструмента невелики, но с помощью подобных ухищрений можно получить вполне приемлемые результаты.

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

Источник

Как изменить цвет строки в 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.

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

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

Источник