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

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

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

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

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

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

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

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

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

Также рассмотрим процесс заполнения пустых строк ошибками.

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

Чтобы одновременно изменить цвет ячейки на основе значения, примените настройки функции «Условное форматирование».

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

Заполним ячейки числами:

• 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. Проверьте все настройки, нажмите «ОК».

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

Поэтому мы создали два правила с разными дополнениями в одной таблице.

Создана таблица с двумя разными правилами заполнения

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

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

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

1. Выделите все данные левой кнопкой мыши.

   Выделите ячейки с данными левой кнопкой мыши

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

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

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

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

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

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

5. Щелкните «ОК».

   Нажмите «ОК»

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

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

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

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

1. Подбираем ячейки с доходом.

   Выбрать ячейки с доходом

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

   Найдите группу «Редактировать» на главной вкладке, щелкните левой кнопкой мыши значок «Найти и выбрать»

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

   Нажмите на строку с названием «Найти»

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

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

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

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

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

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

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

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

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

   Определите цвет и щелкните его значок в палитре

9. То же самое делаем для всех данных, выбирая правильный диапазон и указывая правильную комбинацию для поиска:

•  от 40 000 до 50 000 введите комбинацию «4 ?????» в строке «Найти» нажмите «Найти все» и сделайте то же, что и для первого интервала;

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

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

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

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

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

Ячейки со значением окрашены по-разному

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

Мы будем использовать настройки условного форматирования для рисования специальных ячеек. Следуйте инструкциям, описанным ниже:

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

   Выбрать все строки данных

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

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

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

   Выберите левой кнопкой мыши «Форматировать только ячейки…»

4. Находим блок с названием «Редактировать описание правила», раскрываем список, нажав на стрелку вниз в столбце с названием «Значение ячейки». Выбираем опцию «Пусто».

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

5. Щелкните кнопку «Форматировать».

   Нажмите кнопку «Форматировать»

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

   Определите цвет, нажмите «ОК»

7. Если вас все устраивает, нажмите «ОК».

   Нажмите «ОК»

Пустые ячейки будут заполнены указанным цветом.

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

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

Выбираем «Ошибки», определяем цвет в блоке «Форматировать», нажимаем на функцию «ОК»

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

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

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

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

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

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

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

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

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

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

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

   Нажмите на опции «Применить» и «ОК»

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

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

Заключение

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

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