Заливка ячеек в зависимости от значения в Microsoft Excel

|

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

Процедура изменения цвета ячеек в зависимости от содержимого

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

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

Но выход есть. Для ячеек, содержащих динамические (изменяющиеся) значения, используется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить».

Способ 1: условное форматирование

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

Давайте посмотрим, как этот метод работает на конкретном примере. У нас есть таблица доходов компании, где данные разбиты по месяцам. Мы должны выделить разными цветами те статьи, где сумма дохода меньше 400 000 рублей, от 400 000 до 500 000 рублей и превышает 500 000 рублей.

1. Выбираем столбец, где размещена информация о доходах компании. Далее переходим на вкладку Главная. Нажмите кнопку «Условное форматирование», расположенную на ленте в панели инструментов «Стили». В открывшемся списке выберите пункт «Управление правилами…».



2. Откроется окно «Правила условного форматирования». Поле «Показать правила форматирования для» должно быть установлено на «Текущий фрагмент». По умолчанию он там должен быть указан, но на всякий случай проверьте и, в случае отклонения, измените настройки согласно приведенным выше рекомендациям. Затем нажмите на кнопку «Создать правило…».



3. Откроется окно «Создать правило форматирования». В списке типов правил выберите пункт «Форматировать только те ячейки, которые содержат». В блоке описания правила в первом поле переключатель должен стоять в положении «Значения». Во втором поле установите переключатель в положение «Меньше». В третьем поле укажите значение, элементы листа, содержащие значение меньше указанного, будут окрашены в определенный цвет. В нашем случае это значение будет 400 000. После этого нажмите на кнопку «Форматировать…».



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



5. Возвращаемся в окно создания правила форматирования и там же нажимаем кнопку «ОК».



6. После этого действия нас снова перенаправит в Менеджер правил условного форматирования. Как видите, одно правило уже добавлено, но нам нужно добавить еще два. Поэтому снова нажмите на кнопку «Создать правило…».



7. И снова мы приходим к окну создания правила. Перейдите в раздел «Форматировать только те ячейки, которые содержат». В первое поле этого раздела поставьте параметр «Значение ячейки», а во второе установите переключатель в положение «Между». В третьем поле необходимо указать начальное значение области форматирования элементов листа. В нашем случае это число 400 000. В четвертом вводим конечное значение для этого диапазона. Это будет 500 000. После этого нажмите кнопку «Форматировать…».



8. В окне форматирования снова переходим на вкладку «Заливка», но на этот раз уже выбираем другой цвет, а затем нажимаем кнопку «ОК».



9. Вернувшись в окно создания правила, также нажмите кнопку «ОК».



10. Как видите, мы уже создали два правила в Менеджере правил. Таким образом, осталось сделать третью. Нажмите на кнопку «Создать правило».



11. В окне создания правила снова перейдите к разделу «Форматировать только те ячейки, которые содержат». В первом поле оставьте опцию «Значение ячейки». Во втором поле установите переключатель на полицию «Еще». В третье поле вводим число 500000. Затем, как и в предыдущих случаях, нажимаем на кнопку «Форматировать…».



12. В окне «Формат ячеек» снова перейдите на вкладку «Заливка». На этот раз мы выбираем цвет, отличный от предыдущих двух случаев. Производим щелчок по кнопке «ОК».



13. В окне создания правила еще раз нажмите кнопку «ОК».



14. Откроется Диспетчер правил. Как видите, все три правила созданы, поэтому нажимаем кнопку «ОК».



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



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

Вы также можете использовать условное форматирование немного иначе, чем раскрашивание элементов листа.

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



2. В Диспетчере правил также нажимаете на кнопку «ОК».



3. Как видите, после этого ячейки в столбце окрашиваются разными оттенками одного цвета. Чем больше значение, содержащее элемент листа, тем светлее оттенок, чем меньше оттенок, тем темнее.

Урок: Условное форматирование в Excel

Способ 2: использование инструмента «Найти и выделить»

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

Посмотрим, как это работает на конкретном примере, для которого возьмем ту же таблицу доходов предприятия.

1. Выберите столбец с данными, которые нужно отформатировать цветом. Затем перейдите на вкладку «Главная» и нажмите кнопку «Найти и выбрать», которая находится на ленте в блоке инструментов «Редактирование». Нажмите на пункт «Найти» в открывшемся списке».



2. Откроется окно «Найти и заменить» во вкладке «Найти». Прежде всего, мы найдем значения до 400 000 рублей. Так как у нас нет ни одной ячейки, которая будет содержать значение меньше 300 000 рублей, нам фактически нужно выделить все элементы, содержащие числа в диапазоне от 300 000 до 400 000. К сожалению, этот диапазон приходится указывать напрямую, как и в случай использования условного форматирования в этом методе невозможен.

   Но есть возможность сделать что-то немного другое, что даст нам тот же результат. Вы можете ввести следующий шаблон в поле поиска «3??????». Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, начинающиеся с цифры «3». То есть в результатах поиска будут значения в диапазоне 300000 — 400000, что нам и нужно. Если в таблице были числа меньше 300 000 или меньше 200 000, каждый стотысячный диапазон нужно было искать отдельно.

   Введите фразу «3???????» в поле «Найти» и нажмите кнопку «Найти все».



3. После этого в нижней части окна открываются результаты поиска. Щелкните левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш Ctrl+A. После этого выбираются все результаты результатов поиска, и одновременно выбираются элементы столбца, к которым эти результаты относятся.



4. После того, как элементы в столбце выбраны, мы не спешим закрывать окно «Найти и заменить». Находясь во вкладке «Главная», на которую мы перебрались ранее, переходим на ленту блока инструментов «Шрифт». Щелкните треугольник справа от кнопки «Цвет заливки». Откроется выбор различных цветов заливки. Мы выбираем цвет, который мы хотим использовать на элементах листа, содержащих значения менее 400 000 рублей.



5. Как видите, выбранным цветом выделены все ячейки столбца, содержащие значения менее 400 000 рублей.



6. Теперь нам нужно раскрасить предметы, которые содержат значения в диапазоне от 400 000 до 500 000 рублей. Этот диапазон включает числа, соответствующие шаблону «4??????». Запускаем его в поле поиска и нажимаем кнопку «Найти все», предварительно выбрав нужный нам столбец.



7. Так же, как и в прошлый раз в результатах поиска, выбираем весь результат, нажав комбинацию горячих клавиш CTRL+A. После этого перейдите к значку выбора цвета заливки. Щелкаем по ней и нажимаем на иконку нужного нам оттенка, который будет окрашивать элементы листа, где значения находятся в диапазоне от 400 000 до 500 000.



8. Как видите, после этого действия все элементы таблицы с данными в диапазоне от 400 000 до 500 000 выделяются выбранным цветом.



9. Теперь нам осталось выбрать последний диапазон значений — больше 500000. Здесь нам тоже повезло, так как все числа выше 500000 находятся в диапазоне от 500000 до 600000. Поэтому вводим в поле поиска выражение «5??? ??» и нажмите кнопку «Найти все». Если бы были значения больше 600 000, пришлось бы дополнительно искать выражение «6?????» и так далее



10. Снова выберите результаты поиска с помощью комбинации Ctrl+A. Затем с помощью кнопки на ленте выберите новый цвет, чтобы заполнить интервал выше 500 000 так же, как мы делали ранее.



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



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

Урок: Как сделать поиск в Excel

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