Изменение цвета ячейки в зависимости от значения Excel
Вам приходится работать с очень большими таблицами в Excel, которые содержат много данных? Тогда вам знакома ситуация, когда нужно найти какое-то значение, но на это уходит время. Ведь не легко это сделать в большом количестве цифр или других значений.
В такой ситуации вам поможет цветное оформление ячеек со значением. Большинство пользователей выделяют ячейки вручную с помощью функции «Цвет заливки». Такой способ удобен только в том случае, если данных немного. Если же в таблице много данных, и в процессе они могут меняться, такой способ не подойдет.
Для такого случая в табличном редакторе Эксель есть функция для визуального выделения важных данных в ячейках. При помощи этой функции вы не просто измените цвет ячейки, но и согласно значению, облегчив себе работу в поиске нужных данных, да и визуальное оформление таблицы станет лучше.
Как выбрать функцию, изменяющую цвет ячейки согласно значению
Для выбора функции заливки нужно определить тип таблицы и ее содержание:
- динамическая. В этой таблице данные могут меняться массово;
- статистическая таблица, в которой находятся постоянные данные.
Определившись с типом таблицы, выбираем функцию:
- «Условное форматирование» подойдет для таблиц с меняющимися данными, в итоге цвет ячейки изменится одновременно с корректировкой данных;
- «Найти и выделить» используется для таблиц с постоянной информацией, поэтому цвет ячейки будет постоянным.
И дополнительно рассмотрим процесс заливки пустых строк и с ошибками.
Справка! В статье мы работаем в Эксель версии 2016. Для версий 2007, 2010 и 2013 описываемые действия будут аналогичными, возможно незначительное отличие в названиях опций.
Работа с функцией «Условное форматирование»
Для одновременного изменения цвета ячейки согласно значению применим настройки функции «Условное форматирование».
Закрасим ячейки с цифрами:
- 39 и больше красным;
- 36,5 и меньше зеленым.
Шаг 1. Выделим с помощью левой кнопки мышки ячейки с данными. Не выделяются столбцы и строки, имеющие название.
Шаг 2. Во вкладке «Главная» ищем группу «Стили».
Шаг 3. Нажимаем левой кнопкой мышки по иконке «Условное форматирование».
Шаг 4. В раскрывшемся меню щелкаем по строке с названием «Создать правило».
Шаг 5. Выделим левым кликом мышки функцию «Форматировать только ячейки…».
Шаг 6. В части окна под названием «Измените описание правила» выставляем нужные параметры, согласно описанным выше условиям:
-
графу «Значение ячейки» оставляем по умолчанию;
Шаг 7. Нажимаем по кнопке «Формат».
Шаг 8. В открытом окне «Формат ячеек» активируем щелчком мышки вкладку «Заливка», если она не открылась по умолчанию.
Шаг 9. Левой кнопкой мышки нажимаем по подходящему цвету в палитре. В блоке «Образец» появится выбранный цвет.
Шаг 10. Если цвет устраивает, кликаем по «ОК».
Шаг 11. Проверяем выставленные параметры, кликаем по «ОК», чтобы настройки отобразились в таблице.
Шаг 12. Создаем другое правило, согласно второму условию, перейдя в «Условное форматирование».
Шаг 13. Выбираем второй тип правила.
Шаг 14. В графе «между» выставляем «меньше или равно», вводим цифру 36,5.
Шаг 15. Жмем по кнопке «Формат».
Шаг 16. Выбираем зеленый цвет, кликаем по «ОК».
Шаг 17. Проверяем все настройки, жмем по «ОК».
Таким образом мы создали в одной таблице два правила с разной заливкой.
Если мы подкорректируем в некоторых ячейках цифры, в итоге автоматически изменится и цвет.
Применим функцию заливки одним цветом разных оттенков.
-
С помощью левой клавиши мышки выделим все данные.
В итоге получаем цветные ячейки разного оттенка, темный оттенок окрашивает меньшие значения, чем больше значение, тем оттенок светлее.
Работа с функцией «Найти и выделить»
При помощи функции «Найти и выделить» мы определим постоянный цвет ячейкам согласно их значениям. Для примера рассмотрим работу с функцией на таблице с доходами компании за целый год. Окрасим все данные разными цветами, выбрав нужный диапазон. Итак, переходим к действиям:
-
Выделим ячейки с доходом.
-
от 40000 до 50000 печатаем в строке «Найти» комбинацию «4. », нажимаем «Найти все» и проделываем те же действия, как и для первого диапазона;
Важно! Перед каждой задачей не забывайте выделять данные в ячейках, так как функция «Найти» будет искать значения только в выделенном фрагменте.
В итоге данные в таблице окрашены в разные цвета согласно значениям.
Инструкция по закрашиванию пустых ячеек и с ошибками
Для закрашивания особых ячеек мы применим настройки функции «Условное форматирование». Следуем ниже описанным действиям:
-
Выделяем все строки с данными.
Пустые ячейки закрасятся в обозначенный цвет.
Таким же способом закрашиваются и ячейки с ошибками. Из списка необходимо выбрать «Ошибки», определится с цветом в блоке «Формат» и сохранить настройки нажатием по функции «ОК».
Как изменить созданные правила
Если вам вдруг необходимо изменить в созданных правилах условия, выполните описанные ниже действия:
-
Щелкните по иконке с названием «Условное форматирование».
Справка! В настройках окна «Диспетчер правил условного форматирования» вы также можете удалить правило или создать новое, выбрав соответствующую функцию.
Заключение
Теперь вы знаете несколько способов для закрашивания ячеек со значением. Дополнительно мы показали, как изменить цвет в пустых ячейках и с ошибками. Надеемся эти навыки пригодятся вам в работе с таблицами и значительно облегчат ее.
Выделение строк таблицы в 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
Для ячеек Е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 в зависимости от значения другой ячейки
Для того, чтобы настроить подкрашивание (или любое другое изменение формата) определенной ячейки, в зависимости от значения в ДРУГОЙ ячейке,
необходимо воспользоваться специальными настройками условного форматирования.
В примере – настроим автоматическое подкрашивание ячейки с цифрой фактического значения, если выполнение плана окажется ниже 80%.
-
Выделим ячейку, которую будем подкрашивать в зависимости от другой ячейки.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)