Выделение строк таблицы в 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 .

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

Рекомендации

При вводе статуса работ важно не допустить опечатку. Если вместо слово Завершен а , например, пользователь введет Завершен о , то Условное форматирование не сработает.

Чтобы исключить некорректный ввод используйте идеи из статьи Ввод данных из списка значений. Часть1. Выпадающий список . В файле примера для ввода статусов работ использован аналогичный Выпадающий список .

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки ( А17:С17 ) и нажмите сочетание клавиш CTRL+D . Правила Условного форматирования будут скопированы в строку 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 не попадает в указанный диапазон.


Комментарии
Только для авторизованных пользователей

Аноним, 26 октября 2015 г.
Не могли бы Вы поподробней рассказать о физике процесса? Почему выделяется не весь диапазон, а только строка, т.е. почему форматирование срабатывает только для значений в той-же строке что и искомое значение?
Аноним, 10 марта 2016 г.
Здравствуйте. Подскажите пожалуйста как можно сделать. Мне нужно залить всю строку цветом, при условии что в выделенном диапазоне есть 4 пустые ячейки подряд.
Аноним, 11 июля 2016 г.
Добрый день! У меня при использовании этой формулы выделяется вся таблица, при чем в столбце с текстовым значением реагирует только ячейка на которую изначально делал, в вашем случаи это ячейка С7, если изначально выделяю ни всю даблицу а только строку то все работает нормально, но строк в таблице много и при продолжении таблицы новыми строками формула уже не работает.
Аноним, 11 июля 2016 г.
Пример
Михаил, 11 июля 2016 г.
шлите файл на creator@excel2.ru
Аноним, 13 июля 2016 г.
[id295933596|Михаил], отправил, спасибо
Михаил, 13 июля 2016 г.
Я тоже лайкнул ))) Статью обновлю ))
Михаил, 14 июля 2016 г.
Обновил, см. раздел Как это работает
Аноним, 21 июля 2016 г.
Добрый день. У меня такой вопросик - веду базу данных клиентов. Нужно выделить жёлтым цветом тех клиентов, которые не посещали нас месяц, красным цветом тех клиентов, которые не посещали нас 2 месяца и более. Естественно хочется, что-бы таблица сама выделяла нужным цветом, когда подходит нужная дата. Возможно-ли это? Спасибо большое.
Михаил, 27 июля 2016 г.
Аноним, 11 августа 2016 г.
Добрый день, Михаил. Во вложении пример таблицы. Требуется ячейку с именем клиента залить цветом, если в диапазоне сроков оплаты содержится определенная дата, и по строке этой даты есть задолженность оплаты. Допускается создание вспомогательной таблицы или ячейки. Возможно задать такое условие ?
Михаил, 13 августа 2016 г.
Вложения к комментариям Вам присоединить не удастся. Если нужно направить нам файл, то используйте лучше группу https://vk.com/excel2ru в Контакте. Также про выделение Условным форматированием дат см. статью http://excel2.ru/gruppy-statey/uslovnoe-formatirovanie-dat
Аноним, 13 сентября 2016 г.
Добрый день! с тем же вопросом, что и люди до этого. необходимо выделить заливкой имя клиента привязанного к дате последнего посещения и текущей. не получается сообразить во вроде бы простой задаче... по статьям все получается, но вот с выделением другой ячейки от двух других зависимых по датам нет. подскажите пожалуйста решение такой задачи. файл могу выслать. заранее благодарен!
Михаил, 13 сентября 2016 г.
Пожалуйста, шлите файл и вопрос в группу https://vk.com/excel2ru
Михаил, 21 сентября 2016 г.
В статью добавил задачу 2 про выделение на основе дат.
Аноним, 17 августа 2017 г.
[id295933596|Михаил], не вводится знак доллара перед названием ячейки, пишет ошибку в формуле, поэтому все условное форматирование во всех строках получается зависит от значения в одной ячейке. А мне надо, чтобы каждая строка выделялась цветом в зависимости от значения ячейки, которая находится в строке.
Аноним, 17 августа 2017 г.
[id295933596|Михаил],
Аноним, 5 декабря 2017 г.
Спасибо за подробную статью!!!! Очень помогли!!!
Аноним, 28 октября 2018 г.
А где файл примера?
Михаил, 1 ноября 2018 г.
Уууупс, забыл присоединить. Все, уже присоединил )))
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved