Условное форматирование в диаграмме Excel

На листе Excel условное форматирование достаточно легко реализовать. Данная встроенная возможность находится  на вкладке Главная ленты Excel. Условное форматирование для диаграмм – это совсем другая история.

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

Диаграмма без форматирования

Ниже приведен простой пример данных для построения диаграммы с условным форматированием …

Исходные данные

…  которые построят простую неотформатированную гистограмму …

неотформатированная диаграмма excel

… или простую  линейчатую диаграмму

неотформатированная линейчатая диаграмма

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

За исключением некоторых простых встроенных форматов, условное форматирование рабочего листа требует наличие формулы для определения того, какие клетки будут отформатированы. Таким же образом мы поступим для определения форматирования в диаграммах.

Мы заменим оригинальный график  линии или гистограммы несколькими рядами данных, по одному для каждого услвия. Так как наши данные находятся в диапазоне от 0 до 5,07, мы создадим ряд для диапазонов 0-0,5; 0,5-1,5; 1,5-3; 3-4,5 и 4,5-6.

Диаграмма с условным форматированием

Ниже показаны данные для диаграммы с условным форматированием. Диапазон условий форматирования находится в строках 1 и 2, формулы для заголовка находятся в диапазоне C3:G3. К примеру, формула, находящаяся в ячейке С3, выглядит следующим образом:

=C1&»<Y<=»&C2

Формула для ячейки С4:

=ЕСЛИ(И(C$1<$B4;$B4<=C$2);$B4;»»)

Данная формула отображает значение колонки B, если оно лежит в диапазоне от 4,5 до 6, в противном случае, возвращается пустая ячейка. Диапазон C4:G13 заполнен этой формулой.

исходные данные

Во время выделения диаграммы, мы увидим источник данных для графика

исходные данные для диаграммы с условным форматированием

Нам необходимо изменить источник данных, убрав колонку B и добавив колонки C:G. Это делается просто, путем перетаскивания и изменения размеров выделенной области.

диаграммы с условным форматированием расширенные

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

диаграмма с условным форматированием excel

Ситуацию легко исправить, назначив 100%-ное перекрытие одного из столбцов. Это позволит перекрыть пустой столбик видимым.

диаграмма с условным форматированием excel

Линейчатая диаграмма с условным форматированием

Отличием построения линейчатой диаграммы от гистограммы будет формула, определяющая попадание значения по оси Y в диапазон условий и возвращающая ошибку #Н/Д, если условие не соблюдается. Формула в диапазоне C4:G13 будет выглядеть следующим образом:

=ЕСЛИ(И(C$1<$B4;$B4<=C$2);$B4;НД())

исходные данные для линейчатой диаграммы с условным форматированием

Нам необходимо будет расширить источник данных, оставив при этом колонку B, как линию соединяющую все точки, и добавив колонки C:G, как отдельно отформатированные ряды данных.

исходные данные для линейчатой диаграммы с условным форматированием

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

Линейчатая диаграмма с условным форматированием

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

Линейчатая диаграмма с условным форматированием

Гибкость условного форматирования

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


2 комментария

  1. хотел сделать то же самое с пузырьковой диаграммой — не получилось
    надо, чтобы пузыри меньше 50% были красными, больше — желтыми, а 100% — зеленые
    есть ограничения у способа?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *