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

Выделение строк таблицы в EXCEL в зависимости от условия в ячейке

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

Пусть в диапазоне А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 , а цвет заливки установите зеленый.

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

Примечание : Условное форматирование перекрывает обычный формат ячеек. Поэтому, если работа в статусе Завершена, то она будет выкрашена в зеленый цвет, не смотря на то, что ранее мы установили красный фон через меню Главная/ Цвет заливки .

Как это работает?

В файле примера для пояснения работы механизма выделения строк, создана дополнительная таблица с формулой =$C7=$E$9 из правила Условного форматирования для зеленого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и вправо.

Как видно из рисунка, в строках таблицы, которые выделены зеленым цветом, формула возвращает значение ИСТИНА.

В формуле использована относительная ссылка на строку ($C7, перед номером строки нет знака $). Отсутствие знака $ перед номером строки приводит к тому, что при копировании формулы вниз на 1 строку она изменяется на =$C8=$E$9 , затем на =$C9=$E$9 , потом на =$C10=$E$9 и т.д. до конца таблицы (см. ячейки G8 , G9 , G10 и т.д.). При копировании формулы вправо или влево по столбцам, изменения формулы не происходит, именно поэтому цветом выделяется вся строка.

В случае затруднений можно потренироваться на примерах, приведенных в статье Условное форматирование в MS EXCEL .

Прием с дополнительной таблицей можно применять для тестирования любых формул Условного форматирования .

Задача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

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

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

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

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

Как в Excel выделить ячейку цветом при определенном условии: примеры и методы

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.

как в экселе выделить ячейку цветом при условии

как в excel выделить ячейку цветом при определенном условии

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

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

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

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

выделяем столбцы с датами (в нашем случае за январь);
находим инструмент «Условное форматирование»;
в «Правилах выделения ячеек» выбираем пункт «Дата»;
в правой части форматирования открываем выпадающее окно с правилами;
выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
выборка окрасилась, жмем «ОК».

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

Формула присвоения цвета ячейки (условное форматирование) в Excel

Условное форматирование в Excel — это полезная функция, позволяющая выделять определенным цветом элементы в таблице, по заранее заданному правилу. Например, если данные ниже 1000, то выделять зеленым.

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

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

Пример файла где нужно закрасить ячейки по формуле в Эксель