Как в Excel изменять цвет строчки в зависимости от значения в ячейке

Узнайте, как на листах Excel стремительно изменять цвет целой строчки в зависимости от значения одной ячейки. Поглядите приёмы и примеры формул для числовых и текстовых значений.

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

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

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

Мы желаем раскрасить разными цветами строчки в зависимости от заказанного количества продукта (значение в столбце Qty.), чтоб выделить самые принципиальные заказы. Совладать с данной нам задачей нам поможет инструмент Excel – «Условное форматирование».

1. Сперва, выделим все ячейки, цвет заливки которых мы желаем поменять.
2. Чтоб сделать новое правило форматирования, жмем Основная > Условное форматирование > Сделать правило (Home > Conditional Formatting > New rule).
3. В показавшемся диалоговом окне Создание правила форматирования (New Formatting Rule) избираем вариант Употреблять формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых последующая формула является настоящей (Format values where this formula is true), вводим такое выражение:

Заместо C2 Вы сможете ввести ссылку на другую ячейку Вашей таблицы, значение которой необходимо применять для проверки условия, а заместо 4 сможете указать хоть какое необходимое число. Очевидно, в зависимости от поставленной задачки, Вы сможете применять операторы сопоставления меньше (<) либо равно (=), другими словами записать формулы в таком виде:

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

Как сделать несколько правил условного форматирования с данным ценностью

В таблице из предшествующего примера, возможно, было бы удобнее применять различные цвета заливки, чтоб выделить строчки, содержащие в столбце Qty. разные значения. Например, сделать ещё одно правило условного форматирования для строк, содержащих значение 10 либо больше, и выделить их розовым цветом. Для этого нам пригодится формула:

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

1. На вкладке Основная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) > Управление правилами (Manage Rules)
2. В выпадающем перечне Показать правила форматирования для (Show formatting rules for) изберите Этот лист (This worksheet). Если необходимо поменять характеристики лишь для правил на выделенном фрагменте, изберите вариант Текущий фрагмент (Current Selection).
3. Изберите правило форматирования, которое обязано быть использовано первым, и с помощью стрелок переместите его ввысь перечня. Обязано получиться вот так:Нажмите ОК, и строчки в обозначенном фрагменте здесь же изменят цвет, в согласовании с формулами в обоих правилах.

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

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

• Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таковых ячеек обязана быть оранжевой;
• Если заказ доставлен (значение Delivered), то заливка таковых ячеек обязана быть зелёной;
• Если срок доставки заказа находится в прошедшем (значение Past Due), то заливка таковых ячеек обязана быть красноватой.

И, естественно же, цвет заливки ячеек должен изменяться, если меняется статус заказа.

С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

=$E2=»Delivered»
=$E2=»Past Due»

Труднее звучит задачка для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы лицезреем, что срок доставки для разных заказов составляет 1, 3, 5 либо наиболее дней, а это означает, что приведённая выше формула тут не применима, потому что она нацелена на четкое значение.

В этом случае комфортно применять функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

В данной формуле E2 – это адресок ячейки, на основании значения которой мы применим правило условного форматирования; символ бакса $ нужен для того, чтоб применить формулу к целой строке; условие “>0” значит, что правило форматирования будет использовано, если данный текст (в нашем случае это “Due in”) будет найден.

Подсказка: Если в формуле употребляется условие “>0“, то строчка будет выделена цветом в любом случае, когда в главный ячейке будет найден данный текст, вне зависимости от того, где конкретно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе значит – Срочно, доставить в течение 6 часов), и эта строчка также будет окрашена.

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

=ПОИСК(«Due in»;$E2)=1
=SEARCH(«Due in»,$E2)=1

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

Итак, выполнив те же шаги, что и в первом примере, мы сделали три правила форматирования, и наша таблица стала смотреться вот так:

Как поменять цвет ячейки на основании значения иной ячейки

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

К примеру, мы можем настроить три наших правила таковым образом, чтоб выделять цветом лишь ячейки, содержащие номер заказа (столбец Order number) на основании значения иной ячейки данной нам строчки (используем значения из столбца Delivery).

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

Если необходимо выделить строчки одним и этим же цветом при возникновении 1-го из нескольких разных значений, то заместо сотворения нескольких правил форматирования можно применять функции И (AND), ИЛИ (OR) и соединить таковым образом нескольких критерий в одном правиле.

К примеру, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут смотреться так:

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

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

Для того, чтоб выделить заказы с количеством продукта не наименее 5, но не наиболее 10 (значение в столбце Qty.), запишем формулу с функцией И (AND):

Естественно же, в собственных формулах Вы сможете применять не непременно два, а столько критерий, сколько требуется. К примеру:

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

Подсказка: Сейчас, когда Вы научились раскрашивать ячейки в различные цвета, в зависимости от содержащихся в их значений, может быть, Вы возжелаете выяснить, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Желаю повеселить Вас, это действие тоже можно создать автоматом, и решение данной нам задачки мы покажем в статье, посвящённой вопросцу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета.

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

Сложение значений в зависимости от цвета ячеек в EXCEL

Функции для суммирования значений по цвету ячеек в EXCEL не существует (по последней мере, в EXCEL 2016 и в наиболее ранешних версиях). Возможно, подавляющему большинству юзеров это не требуется.

Пусть дан спектр ячеек в столбце А. Юзер выделил цветом ячейки, чтоб разбить значения по группам.

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

Это можно создать различными методами, приведем 3 из их: при помощи Автофильтра , Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.

При помощи Автофильтра (ручной способ)

• Добавьте справа очередной столбец с заголовком Код цвета .
• Выделите заглавия и нажмите CTRL+SHIFT+L, т.е. вызовите Автофильтр ( подробнее тут )
• Вызовите меню Автофильтра , изберите зеленоватый цвет

• Будут отображены лишь строчки с зеленоватыми ячейками
• Введите напротив всякого «зеленоватого» значения число 1

• Сделайте тоже для всех цветов

Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано в файле примера (лист Фильтр) .

Для подсчета значений используйте функцию СЧЕТЕСЛИ() .

При помощи Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

Сходу предупрежу, что начинающему юзеру EXCEL будет трудно разобраться с сиим и последующим разделом.

Мысль заключается в том, чтоб автоматом вывести в примыкающем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответственный числовой код). Для этого нам будет нужно функция, которая может возвратить этот код. Ни одна рядовая функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам будет нужно лишь это ее свойство).

Примечание: Макрофункции — это набор функций к EXCEL 4-й версии, которые недозволено впрямую применять на листе EXCEL современных версий, а можно применять лишь в качестве Именованной формулы . Макрофункции — промежный вариант меж обыкновенными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm

• Сделайте активной ячейку В7 (это принципиально, т.к. мы будем применять относительную адресацию в формуле)
• В Диспетчере имен введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)
• Назовите ее Цвет

• Закройте Диспетчер имен
• Введите в ячейку В7 формулу =Цвет и скопируйте ее вниз.

Сложение значений скооперировано так же как и в прошлом разделе.

Макрофункция работает кривовато:

• если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого необходимо снова скопировать формулу из В7 вниз либо выделить ячейку, надавить кнопку F2 и потом ENTER )
• функция возвращает лишь 56 цветов (так именуемая гамма EXCEL), т.е. если цвета близки, к примеру, зеленоватый и светло зеленоватый, то коды этих цветов могут совпасть. Подробнее о этом см. лист файла примера Colors . Как следствие, будут сложены значения из ячеек с различными цветами.

При помощи VBA

В файле примера на листе VBA приведено решение при помощи VBA. Решений быть может огромное количество:

Закрасить ячейку по условию либо формуле

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

{Инструкция} для Excel 2010

ВКЛЮЧИТЕ СУБТИТРЫ!

Как это создать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с клавишей «Условное форматирование», выберем «Сделать правило».

Выберем 4-ый пункт, позволяющий ассоциировать текущие значения со средним. Нас заинтересовывают значения выше среднего. Нажав клавишу «Формат», зададим цвет ячеек.

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

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

Ну и создадим очередное правило, окрашивающее просроченные заказы в красноватый цвет. Выделяем даты выполнения заказов. При разработке правила опять избираем 2-ой пункт, но сейчас задаем «Значение ячейки», «меньше», а в последующем поле вводим функцию, возвращающую нынешнюю дату.

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

Направили внимание, что статусы задаются выбором из выпадающего перечня значений? Как созодать такие списки, мы ведали в аннотации «Как в Excel создать выпадающий перечень».

Как это создать в Excel 2003

ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Здесь пригодится незначительно больше ручной работы. Вот так будут смотреться опции для нашей первой задачки – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор меж скобками, надавить на кнопку рядом и мышкой указать подходящий спектр.
Но принцип действий этот же самый.
Покоряйте Excel и до новейших встреч!

Комменты:

1. Svetlana — 27.06.2015 21:28

наконец выяснила, как это можно создать!

Здрасти, а можно создать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Как задать цвет определенному значению в одной ячейки, к примеру — вожу 5 — она будет красноватым цветом, вожу 4 — она станет зелёным цветом

подскажите, как заливать в политре 1-го цвета с различными цветами в столбике, если напр., если 100% — зеленоватый, 95- зеленоватый но светлее, 75 — еще светлее и т.д. заблаговременно спасибо

как создать зависимость заливки ячейки из «срок выполнения» от статуса? к примеру, срок меньше сейчас, но он выполнен, не нужно подкрашивать. чтоб излишние красноватые сроки не отвлекали. HELP! весьма нужно (((

Ну здесь всё максимально ясно. А вот, если пойти от назад?! Чтоб подставлялось определённое значение (текст), если окрасить ячейку в определённый цвет?! Как задать такое условие либо записать формулу.

Как создать чтоб ячейка меняла цвет

Microsoft Excel — хорошая программка для работы с электрическими таблицами, владеющая огромным набором функций. При помощи Excel можно создавать документы, созодать разные вычисления, строить графики и почти все другое. Также, при помощи Excel можно создать так, чтоб цвет ячейки автоматом изменялся в зависимости от того либо другого значения. Но как это создать на практике?

Цвет ячейки в Excel

Excel цвет ячейки в зависимости от

Фактически, для того, чтоб решить делему, пригодится сам документ Excel. Есть определенная ячейка, значения в которой плавающие, повсевременно изменяются в зависимости от той либо другой ситуации. Для того, чтоб excel менял цвет ячейки без помощи других, воспользуемся функцией Условное форматирование, расположенной на вкладке Основная в разделе Стили.

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

Excel цвет ячейки по условию

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

Опосля чего же раскроется окно Диспетчер правил условного форматирования.

Excel правила форматирования

Но поглядим, как добавить новое правило. Жмем Сделать правило.

Перед нами раскроется окно Создание правила форматирования, в котором избираем Форматировать лишь ячейки, которые содержат.

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

Как создать, чтоб ячейка меняла цвет?

К примеру, нам нужно, чтоб при достижении значения в ячейке равного 3,50, необходимо, чтоб ячейка изменила цвет на оранжевый, при всем этом цвет текста в данной нам ячейке стал черным. И в то же время, если значение в ячейке еще не достигнуло 3,50, она (ячейка) обязана быть окрашена в красноватый цвет, а текст должен быть белоснежным.

Может быть, есть различные варианты достигнуть того, чтоб ячейка в excel меняла цвет, но мы поступили таковым образом:

Во-1-х, мы создаем 1-ое правило, которое гласит нам, что все, что будет меньше 3,50, обязано окрашиваться в красноватый цвет и иметь белоснежный цвет шрифта (текста). Для этого во 2-ой колонке избираем значение Меньше, а в третьей ставим число 3,50.

С числом обусловились, сейчас избираем нужные цвета. Жмем Формат, во вкладке Заливка избираем подходящий цвет фона ячейки.

А во вкладке Шрифт ставим подходящий цвет — белоснежный.

Когда цвета и значения готовы, закрываем окна, нажав в обоих вариантах Ок. В Диспетчере правил условного форматирования жмем Применить. Сейчас, все, что меньше 3,50 будет иметь соответственный вид. Но потому что нам нужно, чтоб при достижении 3,50, ячейка приняла иной вид, создаем очередное правило, в котором прописываем подобные значения, лишь заместо пт Меньше избираем или Больше, или Больше либо равно, либо же, если существует последующий, так именуемый, предел, ставим то же значение Меньше, но число выставляем согласно плану.

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

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