При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.
Процедура изменения цвета ячеек в зависимости от содержимого
Конечно, всегда приятно иметь хорошо оформленную таблицу, в которой ячейки в зависимости от содержимого, окрашиваются в разные цвета. Но особенно актуальна данная возможность для больших таблиц, содержащих значительный массив данных. В этом случае заливка цветом ячеек значительно облегчит пользователям ориентирование в этом огромном количестве информации, так как она, можно сказать, будет уже структурированной.
Элементы листа можно попытаться раскрасить вручную, но опять же, если таблица большая, то это займет значительное количество времени. К тому же, в таком массиве данных человеческий фактор может сыграть свою роль и будут допущены ошибки. Не говоря уже о том, что таблица может быть динамической и данные в ней периодически изменяются, причем массово. В этом случае вручную менять цвет вообще становится нереально.
Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент .
Способ 1: условное форматирование
С помощью условного форматирования можно задать определенные границы значений, при которых ячейки будут окрашиваться в тот или иной цвет. Окрашивание будет проводиться автоматически. В случае, если значение ячейки, вследствие изменения выйдет за пределы границы, то автоматически произойдет перекрашивание данного элемента листа.
Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее рублей, от до рублей и превышает рублей.
- Выделяем столбец, в котором находится информация по доходам предприятия. Затем перемещаемся во вкладку . Щелкаем по кнопке , которая располагается на ленте в блоке инструментов . В открывшемся списке выбираем пункт .
- Запускается окошко управления правилами условного форматирования. В поле должно быть установлено значение . По умолчанию именно оно и должно быть там указано, но на всякий случай проверьте и в случае несоответствия измените настройки согласно вышеуказанным рекомендациям. После этого следует нажать на кнопку .
- Открывается окно создания правила форматирования. В списке типов правил выбираем позицию . В блоке описания правила в первом поле переключатель должен стоять в позиции . Во втором поле устанавливаем переключатель в позицию . В третьем поле указываем значение, элементы листа, содержащие величину меньше которого, будут окрашены определенным цветом. В нашем случае это значение будет . После этого жмем на кнопку .
- Открывается окно формата ячеек. Перемещаемся во вкладку . Выбираем тот цвет заливки, которым желаем, чтобы выделялись ячейки, содержащие величину менее . После этого жмем на кнопку в нижней части окна.
- Возвращаемся в окно создания правила форматирования и там тоже жмем на кнопку .
- После этого действия мы снова будем перенаправлены в . Как видим, одно правило уже добавлено, но нам предстоит добавить ещё два. Поэтому снова жмем на кнопку .
- И опять мы попадаем в окно создания правила. Перемещаемся в раздел . В первом поле данного раздела оставляем параметр , а во втором выставляем переключатель в позицию . В третьем поле нужно указать начальное значение диапазона, в котором будут форматироваться элементы листа. В нашем случае это число . В четвертом указываем конечное значение данного диапазона. Оно составит . После этого щелкаем по кнопке .
- В окне форматирования снова перемещаемся во вкладку , но на этот раз уже выбираем другой цвет, после чего жмем на кнопку .
- После возврата в окно создания правила тоже жмем на кнопку .
- Как видим, в у нас создано уже два правила. Таким образом, осталось создать третье. Щелкаем по кнопке .
- В окне создания правила опять перемещаемся в раздел . В первом поле оставляем вариант . Во втором поле устанавливаем переключатель в полицию . В третьем поле вбиваем число . Затем, как и в предыдущих случаях, жмем на кнопку .
- В окне опять перемещаемся во вкладку . На этот раз выбираем цвет, который отличается от двух предыдущих случаев. Выполняем щелчок по кнопке .
- В окне создания правил повторяем нажатие на кнопку .
- Открывается . Как видим, все три правила созданы, поэтому жмем на кнопку .
- Теперь элементы таблицы окрашены согласно заданным условиям и границам в настройках условного форматирования.
- Если мы изменим содержимое в одной из ячеек, выходя при этом за границы одного из заданных правил, то при этом данный элемент листа автоматически сменит цвет.
Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.
- Для этого после того, как из мы переходим в окно создания форматирования, то остаемся в разделе . В поле можно выбрать тот цвет, оттенками которого будут заливаться элементы листа. Затем следует нажать на кнопку .
- В тоже жмем на кнопку .
- Как видим, после этого ячейки в колонке окрашиваются различными оттенками одного цвета. Чем значение, которое содержит элемент листа больше, тем оттенок светлее, чем меньше – тем темнее.
Способ 2: использование инструмента «Найти и выделить»
Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.
Посмотрим, как это работает на конкретном примере, для которого возьмем все ту же таблицу дохода предприятия.
- Выделяем столбец с данными, которые следует отформатировать цветом. Затем переходим во вкладку и жмем на кнопку , которая размещена на ленте в блоке инструментов . В открывшемся списке кликаем по пункту .
- Запускается окно во вкладке . Прежде всего, найдем значения до рублей. Так как у нас нет ни одной ячейки, где содержалось бы значение менее рублей, то, по сути, нам нужно выделить все элементы, в которых содержатся числа в диапазоне от до . К сожалению, прямо указать данный диапазон, как в случае применения условного форматирования, в данном способе нельзя.
Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры . То есть, в выдачу поиска попадут значения в диапазоне , что нам и требуется. Если бы в таблице были числа меньше или меньше , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.
Вводим выражение в поле и жмем на кнопку ».
- После этого в нижней части окошка открываются результаты поисковой выдачи. Кликаем левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш . После этого выделяются все результаты поисковой выдачи и одновременно выделяются элементы в столбце, на которые данные результаты ссылаются.
- После того, как элементы в столбце выделены, не спешим закрывать окно . Находясь во вкладке в которую мы переместились ранее, переходим на ленту к блоку инструментов . Кликаем по треугольнику справа от кнопки . Открывается выбор различных цветов заливки. Выбираем тот цвет, который мы желаем применить к элементам листа, содержащим величины менее рублей.
- Как видим, все ячейки столбца, в которых находятся значения менее рублей, выделены выбранным цветом.
- Теперь нам нужно окрасить элементы, в которых располагаются величины в диапазоне от до рублей. В этот диапазон входят числа, которые соответствуют шаблону . Вбиваем его в поле поиска и щелкаем по кнопке , предварительно выделив нужный нам столбец.
- Аналогично с предыдущим разом в поисковой выдаче производим выделение всего полученного результата нажатием комбинации горячих клавиш . После этого перемещаемся к значку выбора цвета заливки. Кликаем по нему и жмем на пиктограмму нужного нам оттенка, который будет окрашивать элементы листа, где находятся величины в диапазоне от до .
- Как видим, после этого действия все элементы таблицы с данными в интервале с по выделены выбранным цветом.
- Теперь нам осталось выделить последний интервал величин – более . Тут нам тоже повезло, так как все числа более находятся в интервале от до . Поэтому в поле поиска вводим выражение и жмем на кнопку . Если бы были величины, превышающие , то нам бы пришлось дополнительно производить поиск для выражения и т.д.
- Опять выделяем результаты поиска при помощи комбинации . Далее, воспользовавшись кнопкой на ленте, выбираем новый цвет для заливки интервала, превышающего по той же аналогии, как мы это делали ранее.
- Как видим, после этого действия все элементы столбца будут закрашены, согласно тому числовому значению, которое в них размещено. Теперь можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной.
- Но если мы заменим число на другое, выходящее за границы, которые установлены для конкретного цвета, то цвет не поменяется, как это было в предыдущем способе. Это свидетельствует о том, что данный вариант будет надежно работать только в тех таблицах, в которых данные не изменяются.
Как видим, существует два способа окрасить ячейки в зависимости от числовых значений, которые в них находятся: с помощью условного форматирования и с использованием инструмента . Первый способ более прогрессивный, так как позволяет более четко задать условия, по которым будут выделяться элементы листа. К тому же, при условном форматировании цвет элемента автоматически меняется, в случае изменения содержимого в ней, чего второй способ делать не может. Впрочем, заливку ячеек в зависимости от значения путем применения инструмента тоже вполне можно использовать, но только в статических таблицах.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.