На листе 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-мя пустыми значениями.
Ситуацию легко исправить, назначив 100%-ное перекрытие одного из столбцов. Это позволит перекрыть пустой столбик видимым.
Линейчатая диаграмма с условным форматированием
Отличием построения линейчатой диаграммы от гистограммы будет формула, определяющая попадание значения по оси Y в диапазон условий и возвращающая ошибку #Н/Д, если условие не соблюдается. Формула в диапазоне C4:G13 будет выглядеть следующим образом:
=ЕСЛИ(И(C$1<$B4;$B4<=C$2);$B4;НД())
Нам необходимо будет расширить источник данных, оставив при этом колонку B, как линию соединяющую все точки, и добавив колонки C:G, как отдельно отформатированные ряды данных.
Теперь диаграмма состоит из маркеров и отрезков пяти цветов, по одному на каждый ряд данных.
Немного подправим формат нашей диаграммы. Удалим маркеры оригинального ряда данных (серый), удалим цветные отрезки и определим другие маркеры для добавочных рядов данных. Также удалим ненужные элементы легенды (для серого графика), для этого необходимо щелкнуть по легенде, затем по лишнему элементу и нажать клавишу Delete.
Гибкость условного форматирования
Этот простой пример форматирования определяемый формулами, основан на значениях шкалы Y. Нет никаких сложностей в определении форматирования на основе значений по шкале X или Y, или даже значений, находящихся в другом столбце, которые даже не отображены. Как и при работе с традиционным условным форматированием, ограничением станет только ваша возможность строить формулы.
хотел сделать то же самое с пузырьковой диаграммой — не получилось
надо, чтобы пузыри меньше 50% были красными, больше — желтыми, а 100% — зеленые
есть ограничения у способа?
при чем тут условное форматирование?
обычная группировка данных — делается сводной диаграммой без танцев с бубнами.