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

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

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

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

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

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

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

  • динамическая. В этой таблице данные могут меняться массово;
  • статистическая таблица, в которой находятся постоянные данные.

Определившись с типом таблицы, выбираем функцию:

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

И дополнительно рассмотрим процесс заливки пустых строк и с ошибками.

Справка! В статье мы работаем в Эксель версии 2016. Для версий 2007, 2010 и 2013 описываемые действия будут аналогичными, возможно незначительное отличие в названиях опций. 

Работа с функцией «Условное форматирование»

Для одновременного изменения цвета ячейки согласно значению применим настройки функции «Условное форматирование».

Изменение цвета ячейки в зависимости от значения ExcelТаблица, в которой применим настройки функции «Условное форматирование»

Закрасим ячейки с цифрами:

  • 39 и больше красным;
  • 36,5 и меньше зеленым.

Начнем работу:

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

Изменение цвета ячейки в зависимости от значения ExcelВыделим с помощью левой кнопки мышки ячейки с данными

Шаг 2. Во вкладке «Главная» ищем группу «Стили».

Изменение цвета ячейки в зависимости от значения ExcelВо вкладке «Главная» находим группу «Стили»

Шаг 3. Нажимаем левой кнопкой мышки по иконке «Условное форматирование».

Изменение цвета ячейки в зависимости от значения ExcelНажимаем левой кнопкой мышки по иконке «Условное форматирование»

Шаг 4. В раскрывшемся меню щелкаем по строке с названием «Создать правило».

Изменение цвета ячейки в зависимости от значения ExcelЩелкаем по строке с названием «Создать правило»

Шаг 5. Выделим левым кликом мышки функцию «Форматировать только ячейки…».

Изменение цвета ячейки в зависимости от значения ExcelВыделим левым кликом мышки функцию «Форматировать только ячейки…»

Шаг 6. В части окна под названием «Измените описание правила» выставляем нужные параметры, согласно описанным выше условиям:

  • графу «Значение ячейки» оставляем по умолчанию;

    Изменение цвета ячейки в зависимости от значения ExcelГрафу «Значение ячейки» оставляем по умолчанию

  • в следующей графе «между» раскрываем список меню, кликнув по стрелочке вниз, и выбираем вариант «больше или равно»;

    Изменение цвета ячейки в зависимости от значения ExcelКликаем по стрелочке вниз, выбираем вариант «больше или равно»

  • в последней графе вводим цифру 39.

    Изменение цвета ячейки в зависимости от значения ExcelВ последней графе вводим цифру 39

Шаг 7. Нажимаем по кнопке «Формат».

Изменение цвета ячейки в зависимости от значения ExcelНажимаем по кнопке «Формат»

Шаг 8. В открытом окне «Формат ячеек» активируем щелчком мышки вкладку «Заливка», если она не открылась по умолчанию.

Изменение цвета ячейки в зависимости от значения ExcelАктивируем щелчком мышки вкладку «Заливка»

Шаг 9. Левой кнопкой мышки нажимаем по подходящему цвету в палитре. В блоке «Образец» появится выбранный цвет.

Изменение цвета ячейки в зависимости от значения ExcelЛевой кнопкой мышки нажимаем по подходящему цвету в палитре

Шаг 10. Если цвет устраивает, кликаем по «ОК».

Изменение цвета ячейки в зависимости от значения ExcelКликаем по «ОК»

Шаг 11. Проверяем выставленные параметры, кликаем по «ОК», чтобы настройки отобразились в таблице.

Изменение цвета ячейки в зависимости от значения ExcelПроверяем выставленные параметры, кликаем по «ОК»

Шаг 12. Создаем другое правило, согласно второму условию, перейдя в «Условное форматирование».

Изменение цвета ячейки в зависимости от значения ExcelКликаем по иконке «Условное форматирование», затем по строке с названием «Создать правило»

Шаг 13. Выбираем второй тип правила.

Изменение цвета ячейки в зависимости от значения ExcelВыбираем второй тип правила

Шаг 14. В графе «между» выставляем «меньше или равно», вводим цифру 36,5.

Изменение цвета ячейки в зависимости от значения ExcelВ графе «между» выставляем «меньше или равно», вводим цифру 36,5

Шаг 15. Жмем по кнопке «Формат».

Изменение цвета ячейки в зависимости от значения ExcelЖмем по кнопке «Формат»

Шаг 16. Выбираем зеленый цвет, кликаем по «ОК».

Изменение цвета ячейки в зависимости от значения ExcelВыбираем зеленый цвет, кликаем «ОК»

Шаг 17. Проверяем все настройки, жмем по «ОК».

Изменение цвета ячейки в зависимости от значения ExcelПроверяем все настройки, жмем по «ОК»

Таким образом мы создали в одной таблице два правила с разной заливкой.

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

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

Изменение цвета ячейки в зависимости от значения ExcelПри корректировке данных в ячейках автоматически изменится и их цвет

Применим функцию заливки одним цветом разных оттенков.

  1. С помощью левой клавиши мышки выделим все данные.

    Изменение цвета ячейки в зависимости от значения ExcelВыделим с помощью левой кнопки мышки ячейки с данными

  2. Раскроем меню функции «Условное форматирование», щелкнем «Создать правило».

    Изменение цвета ячейки в зависимости от значения ExcelРаскроем меню функции «Условное форматирование», щелкнем «Создать правило»

  3. Оставляем по умолчанию выделенным параметр «Форматировать все ячейки…».

    Изменение цвета ячейки в зависимости от значения ExcelОставляем по умолчанию выделенным параметр «Форматировать все ячейки…»

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

    Изменение цвета ячейки в зависимости от значения ExcelВ графе «Цвет» выбираем подходящий цвет, который будет окрашивать все ячейки с данными

  5. Нажимаем по «ОК».

    Изменение цвета ячейки в зависимости от значения ExcelНажимаем «ОК»

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

Изменение цвета ячейки в зависимости от значения ExcelЯчейки в таблице окрашены в один цвет только разного оттенка в зависимости от значения

Работа с функцией «Найти и выделить»

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

  1. Выделим ячейки с доходом.

    Изменение цвета ячейки в зависимости от значения ExcelВыделим ячейки с доходом

  2. Находим группу «Редактирование» в главной вкладке. Щелкаем левой кнопкой на иконке «Найти и выделить».

    Изменение цвета ячейки в зависимости от значения ExcelНаходим группу «Редактирование» в главной вкладке, щелкаем левой кнопкой на иконке «Найти и выделить»

  3. В раскрывшемся меню кликаем по строке с названием «Найти».

    Изменение цвета ячейки в зависимости от значения ExcelКликаем по строке с названием «Найти»

  4. Разделим условно данные на диапазоны. Начнем с меньшего, чтобы найти до 40000 печатаем в поле «Найти» такую комбинацию «3?????», кликаем по опции «Найти все». Задав такую комбинацию, функция найдет все значения от 30000 до 40000. Знаков «?» добавляется столько сколько нулей в значениях, так как конкретный диапазон в этом инструменте невозможно задать. Если бы у нас были данные менее 30000, тогда нужно было бы вводить комбинацию «2?????», и так для каждого диапазона.

    Изменение цвета ячейки в зависимости от значения ExcelПечатаем в поле «Найти» комбинацию «3?????», кликаем по опции «Найти все»

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

    Изменение цвета ячейки в зависимости от значения ExcelВнизу окна щелкаем по любому значению левой клавишей мышки

  6. Зажимаем одновременно на клавиатуре кнопки «Ctrl+A», для выделения всех строк в блоке найденных результатов. Одновременно выделятся те же ячейки в столбце.

    Изменение цвета ячейки в зависимости от значения ExcelЗажимаем одновременно на клавиатуре кнопки «Ctrl+A» для выделения всех строк в блоке найденных результатов

  7. Перемещаемся к группе инструментов «Шрифт», находим иконку «Цвет заливки» и щелкаем в ней по маленькому треугольнику левой кнопкой мышки.

    Изменение цвета ячейки в зависимости от значения ExcelПеремещаемся к группе инструментов «Шрифт», находим иконку «Цвет заливки» и щелкаем в ней по маленькому треугольнику левой кнопкой мышки

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

    Изменение цвета ячейки в зависимости от значения ExcelОпределяемся с цветом и кликаем по его значку в палитре

  9. Проделываем то же самое для всех данных, выбирая правильно диапазон, и задав правильную комбинацию для поиска:
  •  от 40000 до 50000 печатаем в строке «Найти» комбинацию «4?????», нажимаем «Найти все» и проделываем те же действия, как и для первого диапазона;

    Изменение цвета ячейки в зависимости от значения ExcelПечатаем в строке «Найти» комбинацию «4?????», нажимаем «Найти все»

  • от 50000 до 60000 печатаем комбинацию «5?????» далее все действия аналогичны вышеописанным.

    Изменение цвета ячейки в зависимости от значения ExcelПечатаем комбинацию «5?????», нажимаем «Найти все», выделяем все строки, выбираем цвет

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

В итоге данные в таблице окрашены в разные цвета согласно значениям.

Изменение цвета ячейки в зависимости от значения ExcelЯчейки со значением окрашены в разные цвета

Инструкция по закрашиванию пустых ячеек и с ошибками

Для закрашивания особых ячеек мы применим настройки функции «Условное форматирование». Следуем ниже описанным действиям:

  1. Выделяем все строки с данными.

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

  2. Открываем меню «Условное форматирование», жмем по строке с названием «Создать правило».

    Изменение цвета ячейки в зависимости от значения ExcelОткрываем меню «Условное форматирование», жмем по строке с названием «Создать правило»

  3. Выделяем левым кликом мышки «Форматировать только ячейки…».

    Изменение цвета ячейки в зависимости от значения ExcelВыделяем левым кликом мышки «Форматировать только ячейки…»

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

    Изменение цвета ячейки в зависимости от значения ExcelРаскрываем список, щелкнув по стрелочке вниз в графе с названием «Значение ячейки», выбираем вариант «Пустые»

  5. Жмем по кнопке «Формат».

    Изменение цвета ячейки в зависимости от значения ExcelЖмем на кнопку «Формат»

  6. Определяемся с цветом, нажимаем по «ОК».

    Изменение цвета ячейки в зависимости от значения ExcelОпределяемся с цветом, нажимаем по «ОК»

  7. Если все устраивает, щелкаем по «ОК».

    Изменение цвета ячейки в зависимости от значения ExcelЩелкаем по «ОК»

Пустые ячейки закрасятся в обозначенный цвет.

Изменение цвета ячейки в зависимости от значения ExcelПустые ячейки окрасились в обозначенный цвет

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

Изменение цвета ячейки в зависимости от значения ExcelВыбираем «Ошибки», определяемся с цветом в блоке «Формат», нажимаем по функции «ОК»

Как изменить созданные правила

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

  1. Щелкните по иконке с названием «Условное форматирование».

    Изменение цвета ячейки в зависимости от значения ExcelНажимаем левой кнопкой мышки по иконке «Условное форматирование»

  2. Щелкните левой кнопкой мышки по строке с названием «Управление правилами».

    Изменение цвета ячейки в зависимости от значения ExcelЩелкаем левой кнопкой мышки по строке с названием «Управление правилами»

  3. Отобразится окно с созданными ранее правилами, щелкните по правилу, которое нужно откорректировать левой кнопкой мышки, затем по функции «Изменить правило».

    Изменение цвета ячейки в зависимости от значения ExcelЩелкаем по правилу, которое нужно откорректировать левой кнопкой мышки, затем по функции «Изменить правило»

  4. Измените значения, по необходимости цвет в блоке «Формат». Щелкните по «ОК» для сохранения изменений.

    Изменение цвета ячейки в зависимости от значения ExcelМеняем значения, цвет по необходимости в блоке «Формат», нажимаем «ОК»

  5. В основном окне кликните по опциям «Применить» и «ОК».

    Изменение цвета ячейки в зависимости от значения ExcelКликаем по опциям «Применить» и «ОК»

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

Изменение цвета ячейки в зависимости от значения ExcelВ окне «Диспетчер правил условного форматирования» можно удалить созданное правило или добавить новое, щелкнув по соответствующим кнопкам

Заключение

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

Видео — Как изменить цвет в ячейках Excel в зависимости от значения

Читайте еще:  Как включить динамики на компьютере Виндовс 7
Понравилась статья? Поделиться с друзьями:
FileWord
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: