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