В таблицах Excel часто находятся сухие цифры и перечень других данных, которые обычному человеку очень сложны для восприятия. Однако с помощью графической визуализации данные в таблице можно сделать более читаемыми, обратив внимание на важные пункты. Специально для этого в Excel был встроен инструмент «Условное форматирование». Неопытные пользователи могут спутать его с инструментом создания диаграмм и иллюстраций, но это не совсем то, так как установка обозначений происходит непосредственно в ячейках, а не выносится в отдельный блок.
Варианты условного форматирования
Всего предусмотрено три варианта форматирования:
- Гистограмма,
- Цветовая шкала,
- Значки.
Работа с каждым из них будет рассмотрена далее. Во всех случаях вам придется выполнить некоторые действия, чтобы получить доступ к условному форматированию:
- Для начала выделите ячейки с данными, которые собираетесь оформить для акцентирования внимания.
- Теперь переместитесь во вкладку «Главная», если она не открыта у вас по умолчанию.
- Здесь, в блоке «Стили» кликните по кнопке «Условное форматирование». Появится выпадающее меню, где вам нужно будет обратить внимание на пункты: «Гистограммы», «Цветовые шкалы» и «Наборы значков». О них мы писали выше.
Перейдем к рассмотрению каждого из данных пунктов подробнее.
Настройка гистограммы
Выделив ячейки с данными откройте контекстное меню «Условное форматирование» и выберите там пункт «Гистограммы». Обратите внимание, что вам предлагается несколько дизайн-вариантов оформления. Они разделены на две большие категории: с градиентной заливкой и сплошной. Выберите среди них тот вариант оформления, который вам больше нравится.
По умолчанию, чем больше числовое значение в ячейке, тем длиннее будет гистограмма. Примечательно, что если в выделенной части таблицы присутствуют отрицательные числа, то гистограмма тоже под них подстроится: появится разделение, которое продемонстрировано на скриншоте. К сожалению, Excel версии 2007 года и более ранние не способны корректно работать с отрицательными значениями.
Цветовая шкала
В отличии от гистограммы такое форматирование предполагает заливку ячеек цветом в зависимости от того, какое число расположено непосредственно в ячейке. Для этого в выпадающем меню «Условное форматирование» выберите вариант «Цветовые шкалы». Среди представленных вариантов вы можете указать наиболее подходящую вам цветовую гамму. Она применится к выделенным ячейкам. Цветовая шкала корректно работает и с отрицательными значениями в ячейках во всех версиях Excel.
Наборы значков
Это более интересный вариант внешнего оформления ячеек с данными в таблице, так как предоставляет большое количество вариантов значков и индикаторов для оформления ячеек в таблице. Всего их 4 группы:
- Направления,
- Фигуры,
- Индикаторы,
- Оценки.
Каждый выбранный пользователем вариант предполагает использование разных значков в зависимости от содержимого ячейки. Выделенная область анализируется встроенным алгоритмом, после чего к ней применяются выбранные значки. По умолчанию к наибольшим числовым значениям применяются элементы зеленого цвета, а к наименьшем красного. Ячейки с промежуточными значениями выделяются желтым цветом.
Исключения бывают в том случае, если вы выбрали вариант, где не предусмотрена градация по цвету. Тогда она происходит в соответствии с указанным шаблоном, например, частичное заполнение фигуры.
При выборе индикаторов из блока «Направления» помимо цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым.
В новых версиях Excel при выборе в качестве индикаторов геометрических фигур в основном используются круги, но есть и другие геометрический фигуры. В таком случае будет меняться не только цветовая заливка, но и сами фигурки в зависимости от числовых значений в ячейках. Например, круги для крупных значений, треугольники для средних и четырехугольники для наименьших.
Изменение правил выделения ячеек
Вы можете не только настроить отображение какого-то цвета или фигурки в ячейках в зависимости их значений, но и задать свои правила, если вам не нравятся те, что установлены по умолчанию. Чтобы это реализовать, сделайте следующее:
- Кликните по блоку инструментов «Условное форматирование». Из контекстного меню выберите «Правила выделения ячеек».
- Там будет отображено семь основных правил:
- «Больше». В случае выбора этого вариант открывается окно, где нужно установить, значения больше какого числа требуется выделять условным форматированием. Вы можете указать адрес ячейки, из которой требуется брать это минимальное число или прописать его вручную, если оно будет неизменным. В соседнем поле нужно выбрать, каким образом будет идти выделение подходящих ячеек: светло-красная заливка и темно-красный цвет (по умолчанию), желтая заливка и темно-желтый текст, красный текст, и т.д. Можно задать пользовательский формат.
- Очень похожий принцип работы у варианта «Меньше», но только здесь происходит наоборот – выделяются ячейки с числами меньше указанных.
- Правило «Между» создает интервал между чисел. Данные, выходящие в этот интервал выделяются по аналогии с предыдущими правилами.
- «Равно». Здесь вы задаете какое-то конкретное число и программа выделяет все ячейки, в которых оно содержится (в выделенной области).
- «Текст содержит». Это правило уже применяется в тех случаях, когда работа ведется с ячейками, где прописан текст, а не числа. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться. Выделение ячеек настраивается аналогичным образом, как и в предыдущих вариантах.
- «Дата». Правило применяется к ячейкам, которые содержат данные в формате дат. Вы можете задать как какую-то конкретную дату, так и выбрать готовые варианты дат: сегодня, вчера, завтра, за последние 7 дней, и т.д.
- «Повторяющиеся значения». Здесь выделение происходит по одному из критериев: повторяющиеся это данные или уникальные.
- Задав все необходимые параметры в окошке настройки выбранного правила, нажмите «Ок», чтобы оно применилось к выделенной области.
Правила отбора первых и последних значений
Это еще один пункт «Условного форматирования» в Excel. Здесь происходит настройка правил выделения только для максимальных или минимальных значений в выделенном диапазоне ячеек. Инструмент подразумевает возможность провести как отбор по порядковым величинам, так и по процентным.
Рассмотрим доступные критерии отбора подробнее:
- Первые 10 элементов. Выделяет только первые 10 элементов в указанном диапазоне,
- Первые 10%. Выделяет только первые 10% ячеек из указанного диапазона,
- Последние 10 элементов,
- Последние 10%,
- Выше среднего. Выделяет только первую половину диапазона,
- Ниже среднего. Выделяет нижнюю половину диапазона.
Примечательно, что вы можете настроить эти фильтры под себя. Например, сделать выделение не первых 10 элементов, а первых 5 или 3.
Создание пользовательских правил
Стоит обратить внимание, что в выпадающем меню «Условное форматирование» есть пункт «Создать правило». При нажатии на него открывается окошко из шести правил, которые вы можете менять на свое усмотрение:
- Форматировать все ячейки на основании их значений,
- Форматировать только ячейки, которые содержат,
- Форматировать только первые и последние значения,
- Форматировать только значения, которые находятся выше или ниже среднего,
- Форматировать только уникальные или повторяющиеся значения,
- Использовать формулу для определения форматируемых ячеек.
Выберите одно из представленных правил, чтобы провести его детальную настройку. В нижней части окна отобразятся возможные изменения: описание правил, установка величин, интервалов и других значений. Во многом это похоже на те шаблоны, которые мы уже рассматривали выше, только настройка здесь более гибкая и все параметры собраны в одном окне.
Во вкладке «Условное форматирование» так же есть и пункт «Управление правилами». С его помощью удобно применять и удалять дополнительные правила к выделенной области. Дело в том, что к ячейкам можно добавить сразу несколько правил, например, сделать гистограмму со значками и цветовыми выделениями на заднем плане. В окошке «Диспетчера правил условного форматирования» (открывается при нажатии на «Управление правилами»). Здесь можно вносить изменения в уже установленные правила, добавлять новые или удалять неактуальные.
Дополнительно правила можно менять местами – по умолчанию они занимают место в зависимости от того, какое из них было раньше добавлено. Изменение места правила происходит с помощью кнопок со стрелками в верхней части окна.
Удаление «Условного форматирования»
Если вам нужно удалить условное форматирование для ячеек, то здесь нет ничего сложного:
- Кликните в блоке с инструментами по пункту «Условное форматирование».
- Из контекстного меню выберите «Удалить правила».
- Здесь вы можете выбрать два варианта:
- «Удалить правила из выделенных ячеек». В таком случае очищается условное форматирование только в выделенных ячейках. Если на листе есть другие ячейки с ним, но они не выделены, то условное форматирование в них не пострадает.
- «Удалить правила со всего листа». В этом случае происходит удаление всего условного форматирования на листе, где вы сейчас находитесь.
Инструмент «Условное форматирование» является отличным решением для визуализации данных в таблицах Excel. Здесь есть как уже встроенные шаблоны, так и возможность создания собственных, в том числе и на основе имеющихся по умолчанию.