Закрасить ячейку по условию или формуле. Как в Excel изменять цвет строки в зависимости от значения в ячейке Если ячейка одного цвета то значение

Windows 7, XP

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А ведь он итак прекрасно умеет это делать — нам с вами остается только ему слегка помочь!

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

Сначала выделяем диапазон данных, то есть содержимое второго столбца таблицы MS Excel, а затем идем на вкладку «Главная «, где в группе «Стили» активируем инструмент «Условное форматирование «, и в раскрывшемся списке выбираем «Создать правило «.

В появившемся окне «Создание правила форматирования» выбираем Тип правила: «Форматировать только ячейки которые содержат», а в конструкторе ниже, устанавливаем параметры: «Значение ячейки», «Меньше» и вручную вписываем наш «край»: число 20.

Нажимаем кнопку «Формат» ниже, переходим на вкладку «Заливка» и выбираем красный цвет. Нажимаем «Ок».

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

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

Отлично, данные уже выделяются цветом!

Теперь, по аналогии, создадим ещё одно правило — только на этот раз с параметрами «Значение ячейки», «Больше», 20. В качестве заливки укажем зеленый цвет. Готово.

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

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

Теперь я изменил не только фон ячеек таблицы, но и цвет шрифта

Попробуем изменить «плохие» значения на «хорошие»? Раз и готово — цвет автоматически изменился, как только в соответствующих ячейках появились значения, попадающие под действие одного из правил.

Меняем в нашей excel-таблице значения… все работает!

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

Сделайте следующее:

Щелкните	Чтобы выделить
Примечания	Ячейки с примечаниями.
Константы
формулы	Примечание: Флажки под параметром формулы определяют тип формул.
Пустые	Пустые ячейки.
Текущую область	текущая область, например весь список.
Текущий массив	Весь массив, если активная ячейка содержится в массиве.
Объекты	Графические объекты (в том числе диаграммы и кнопки) на листе и в текстовых полях.
Отличия по строкам	Все ячейки, которые отличаются от активной ячейки в выбранной строке. В режиме выбора всегда найдется одной активной ячейки, является ли диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, вы можете изменить расположение активной ячейки, которые по умолчанию - первую ячейку в строке. Если выбран более одной строки, сравнение выполняется для каждого отдельного ряда выделенного фрагмента и ячейку, которая используется для сравнения для каждой дополнительной строки находится в том же столбце как активную ячейку.
Отличия по столбцам	Все ячейки, которые отличаются от активной ячейки в выделенном столбце. Всегда найдется одной активной ячейки в выделенном фрагменте, будет ли это диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, можно изменить расположение на активную ячейку - по умолчанию - первую ячейку в столбце. Если выбрать более одного столбца, сравнение выполняется для каждого отдельного столбца выделенного фрагмента. Ячейки, которая используется для сравнения для каждого дополнительного столбца находится в той же строке в активной ячейке.
Влияющие ячейки	Ячейки, на которые ссылается формула в активной ячейке. В разделе зависимые ячейки только непосредственно , чтобы найти только те ячейки, на которые формулы ссылаются непосредственно; на всех уровнях , чтобы найти все ячейки, на которые ячейки в выделенном диапазоне ссылаются непосредственно или косвенно.
Зависимые ячейки	Ячейки с формулами, которые ссылаются на активную ячейку. Выполните одно из указанных ниже действий. Выберите вариант только непосредственно , чтобы найти только ячейки с формулами, ссылающимися непосредственно на активную ячейку. Выберите вариант на всех уровнях , чтобы найти все ячейки, ссылающиеся на активную ячейку непосредственно или косвенно.
Последнюю ячейку	Последнюю ячейку на листе, содержащую данные или форматирование.
Только видимые ячейки	Только ячейки, видимые в диапазоне, который пересекается со скрытыми строками и столбцами.
Условные форматы	Только ячейки, которые применено условное форматирование. В разделе "Проверка данных" выполните одно из указанных ниже действий. все , чтобы найти все ячейки, к которым применено условное форматирование; этих же , чтобы найти ячейки с тем же условным форматированием, что и в выделенной ячейке.
Проверка данных	Только ячейки, которые применены правила проверки данных. Выполните одно из указанных ниже действий. Выберите вариант все , чтобы найти все ячейки, для которых включена проверка данных. Выберите вариант этих же , чтобы найти ячейки, к которым применены те же правила проверки данных, что и к выделенной ячейке.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

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

Пусть в диапазоне А6:С16 имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера ).

Задача1 - текстовые значения

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

Решение1

Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9 .

Выделим диапазон ячеек А7:С17 , содержащий перечень работ, и установим через меню Главная/ Цвет заливки фон заливки красный (предполагаем, что все работы изначально находятся в статусе Не начата ).

Убедимся, что выделен диапазон ячеек А7:С17 (А7 должна быть ). Вызовем команду меню Условное форматирование/ Создать правило / Использовать формулу для определения форматируемых ячеек .

в поле «Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Обратите внимание на использоване ;
нажать кнопку Формат ;
выбрать вкладку Заливка ;
выбрать серый цвет;
Нажать ОК.

ВНИМАНИЕ : Еще раз обращаю внимание на формулу =$C7=$E$8 . Обычно пользователи вводят =$C$7=$E$8 , т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе Завершена . Формула в этом случае будет выглядеть как =$C7=$E$9 , а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (А17:С17 ) и нажмите . Правила будут скопированы в строку 17 таблицы.

Задача2 - Даты

Предположим, что ведется журнал посещения сотрудниками научных конференций (см. файл примера лист Даты ).

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

Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

В столбце D создана =МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая определяет максимальную дату для определенного сотрудника.

Примечание: Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится =$B7=МАКС($B$7:$B$16) и формула массива не понадобится.

Теперь выделим все ячейки таблицы без заголовка и создадим правило . Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

Для этого используйте формулу =И($B23>$E$22;$B23<$E$23)

Для ячеек Е22 и Е23 с граничными датами (выделены желтым) использована $E$22 и $E$23. Т.к. ссылка на них не должна меняться в правилах УФ для всех ячеек таблицы.

Для ячейки В22 использована смешанная адресация $B23, т.е. ссылка на столбец В не должна меняться (для этого стоит перед В знак $), а вот ссылка на строку должна меняться в зависимости от строки таблицы (иначе все значения дат будут сравниваться с датой из В23 ).

Таким образом, правило УФ например для ячейки А27 будет выглядеть =И($B27>$E$22;$B27<$E$23) , т.е. А27 будет выделена, т.к. в этой строке дата из В27 попадает в указанный диапазон (для ячеек из столбца А выделение все равно будет производиться в зависимости от содержимого столбца В из той же строки - в этом и состоит "магия" смешанной адресации $B23).

А для ячейки В31 правило УФ будет выглядеть =И($B31>$E$22;$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из В31 не попадает в указанный диапазон.

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

Каждому известно, что для изменения цвета заливки одной ячейки или целого диапазона в Excel достаточно просто нажать кнопку Fill color (Цвет заливки). Но как быть, если необходимо изменить цвет заливки всех ячеек, содержащих определённое значение? Более того, что если Вы хотите, чтобы цвет заливки каждой ячейки изменялся автоматически вместе с изменением содержимого этой ячейки? Далее в статье Вы найдёте ответы на эти вопросы и получите пару полезных советов, которые помогут выбрать правильный метод для решения каждой конкретной задачи.

Как в Excel динамически изменять цвет ячейки, основываясь на её значении

Цвет заливки будет изменяться в зависимости от значения ячейки.

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

Решение: Используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.

Предположим, есть список цен на бензин в разных штатах, и Вы хотите, чтобы цены, превышающие $3.7 , были выделены красным, а меньшие или равные $3.45 – зелёным.

Замечание: Снимки экрана для этого примера были сделаны в Excel 2010, однако, в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут точно такие же или с незначительными отличиями.

Итак, вот, что нужно сделать по шагам:

Результат Ваших настроек форматирования будет выглядеть приблизительно так:

Так как нам нужно настроить ещё одно условие, позволяющее изменять цвет заливки на зелёный для ячеек со значениями меньшими или равными 3.45 , то снова нажимаем кнопку New Rule (Создать правило) и повторяем шаги с 3 по 6, устанавливая нужное правило. Ниже виден образец созданного нами второго правила условного форматирования:

Когда все будет готово – жмите ОК . Теперь у Вас имеется мило отформатированная таблица, которая даёт возможность с первого взгляда увидеть максимальные и минимальные цены на бензин в разных штатах. Хорошо им там, в Техасе!

Совет: Таким же способом Вы можете изменять цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку Font (Шрифт) в диалоговом окне Format Cells (Формат ячеек), как мы это делали на , и выберите желаемый цвет шрифта.

Как настроить постоянный цвет ячейки, основываясь на её текущем значении

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

Задача: Вы хотите настроить цвет ячейки, основываясь на её текущем значении, и хотите, чтобы цвет заливки оставался неизменным, даже когда значение ячейки изменяется.

Решение: Найти все ячейки с определённым значением (или значениями) при помощи инструмента Find All (Найти все), а затем изменить формат найденных ячеек, используя диалоговое окно Format Cells (Формат ячеек).

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

Найти и выделить все ячейки, удовлетворяющие заданному условию

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

Если Вы хотите раскрасить ячейки с конкретным значением, например, 50 , 100 или 3.4 – то на вкладке Home (Главная) в разделе Editing (Редактирование) нажмите Find Select (Найти и выделить) > Find (Найти).

Введите нужное значение и нажмите Find All (Найти все).

Совет: В правой части диалогового окна Find and Replace (Найти и заменить) есть кнопка Options (Параметры), нажав которую Вы получите доступ к ряду продвинутых настроек поиска, таких как Match Case (Учитывать регистр) и Match entire cell content (Ячейка целиком). Вы можете использовать символы подстановки, такие как звёздочка (*), чтобы найти любую строку символов, или знак вопроса (?), чтобы найти один любой символ.

Что касается предыдущего примера, если нам нужно найти все цены на бензин от 3.7 до 3.799 , то мы зададим такие критерии поиска:

Теперь кликните любой из найденных элементов в нижней части диалогового окна Find and Replace (Найти и заменить) и нажмите Ctrl+A , чтобы выделить все найденные записи. После этого нажмите кнопку Close (Закрыть).

Вот так можно выделить все ячейки с заданным значением (значениями) при помощи опции Find All (Найти все) в Excel.

Однако, в действительности нам нужно найти все цены на бензин, превышающие $3.7 . К сожалению, инструмент Find and Replace (Найти и заменить) в этом не сможет нам помочь.

Измененяем цвета заливки выделенных ячеек при помощи диалогового окна «Формат ячеек»

Теперь у Вас выделены все ячейки с заданным значением (или значениями), мы сделали это только что при помощи инструмента Find and Replace (Найти и заменить). Всё, что Вам осталось сделать, это задать цвет заливки выбранным ячейкам.

Откройте диалоговое окно Format Cells (Формат ячеек) любым из 3-х способов:

нажав Ctrl+1 .
кликнув по любой выделенной ячейке правой кнопкой мыши и выбрав в контекстном меню пункт Format Cells (Формат ячеек).
на вкладке Home (Главная) > Cells (Ячейки) > Format (Формат) > Format Cells (Формат ячеек).

Если Вы хотите изменить только цвет заливки, не касаясь остальных параметров форматирования, то можете просто нажать кнопку Fill color (Цвет заливки) и выбрать понравившийся цвет.

Вот результат наших изменений форматирования в Excel:

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

Изменяем цвет заливки для особых ячеек (пустые, с ошибкой в формуле)

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

Используем формулу для изменения цвета заливки особых ячеек в Excel

Цвет ячейки будет изменяться автоматически в зависимости от значения ячейки.

Этот способ решения задачи Вы, вероятнее всего, будете использовать в 99% случаев, то есть заливка ячеек будет изменяться в соответствии с заданным Вами условием.

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

Изменяем цвет заливки особых ячеек статически

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

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

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