Изменение цвета или стиля диаграммы в Office
Возможно, вы создали диаграмму и считаете, что для нее нужно что-то еще, чтобы сделать ее более эффектной. Здесь удобно использовать стили диаграмм. Щелкните диаграмму, , расположенную рядом с диаграммой в правом верхнем углу, и выберите нужный вариант в коллекций Стиль или Цвет.
Изменение цвета диаграммы
Щелкните диаграмму, которую вы хотите изменить.
В верхнем правом углу рядом с диаграммой нажмите кнопку Стили диаграмм .
Щелкните Цвет и выберите нужную цветовую схему.
Совет: В стилях диаграммы (сочетаниях параметров форматирования и макетов диаграммы) используются цвета темы. Чтобы изменить цветовую схему, выберите другую тему. В Excel на вкладке Разметка страницы нажмите кнопку Цвета, а затем выберите схему или создайте собственные цвета темы.
Изменение стиля диаграммы
Щелкните диаграмму, которую вы хотите изменить.
В верхнем правом углу рядом с диаграммой нажмите кнопку Стили диаграмм .
Щелкните Стиль и выберите необходимый параметр.
При просмотре коллекции с помощью функции динамического просмотра вы сможете увидеть, как будут выглядеть данные диаграммы при применении определенного стиля.
Exceltip
Блог о программе Microsoft 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, выглядит следующим образом:
Формула для ячейки С4:
Данная формула отображает значение колонки B, если оно лежит в диапазоне от 4,5 до 6, в противном случае, возвращается пустая ячейка. Диапазон C4:G13 заполнен этой формулой.
Во время выделения диаграммы, мы увидим источник данных для графика
Нам необходимо изменить источник данных, убрав колонку B и добавив колонки C:G. Это делается просто, путем перетаскивания и изменения размеров выделенной области.
Теперь наша диаграмма имеет 5 наборов цветных столбцов, по одному цвету на каждый ряд данных. Это не совсем верно, но так как у нас кластерный тип диаграммы, мы видим определенный ряд данных с 4-мя пустыми значениями.
Ситуацию легко исправить, назначив 100%-ное перекрытие одного из столбцов. Это позволит перекрыть пустой столбик видимым.
Линейчатая диаграмма с условным форматированием
Отличием построения линейчатой диаграммы от гистограммы будет формула, определяющая попадание значения по оси Y в диапазон условий и возвращающая ошибку #Н/Д, если условие не соблюдается. Формула в диапазоне C4:G13 будет выглядеть следующим образом:
Нам необходимо будет расширить источник данных, оставив при этом колонку B, как линию соединяющую все точки, и добавив колонки C:G, как отдельно отформатированные ряды данных.
Теперь диаграмма состоит из маркеров и отрезков пяти цветов, по одному на каждый ряд данных.
Немного подправим формат нашей диаграммы. Удалим маркеры оригинального ряда данных (серый), удалим цветные отрезки и определим другие маркеры для добавочных рядов данных. Также удалим ненужные элементы легенды (для серого графика), для этого необходимо щелкнуть по легенде, затем по лишнему элементу и нажать клавишу Delete.
Гибкость условного форматирования
Этот простой пример форматирования определяемый формулами, основан на значениях шкалы Y. Нет никаких сложностей в определении форматирования на основе значений по шкале X или Y, или даже значений, находящихся в другом столбце, которые даже не отображены. Как и при работе с традиционным условным форматированием, ограничением станет только ваша возможность строить формулы.
Вам также могут быть интересны следующие статьи
2 комментария
хотел сделать то же самое с пузырьковой диаграммой — не получилось
надо, чтобы пузыри меньше 50% были красными, больше — желтыми, а 100% — зеленые
есть ограничения у способа?
при чем тут условное форматирование?
обычная группировка данных — делается сводной диаграммой без танцев с бубнами.
Условное форматирование в диаграммах Excel
Изучая статистику по коронавирусу (актуальному на момент написания статьи), я зашел на информационную страницу Яндекса с данными по заболеваниям и выздоровлениям и обнаружил там довольно интересную диаграмму:
Заинтересовало меня то, что цвет столбца изменяется в зависимости от соответствующего значения. И чем больше это значение, тем сильнее «краснеет» цвет столбца диаграммы. То есть, грубо говоря, это — условное форматирование столбцов диаграммы, где условие — принадлежность числового значения самого столбца к некоторому диапазону.
Мне захотелось реализовать что-то подобное в Excel, но я с удивлением обнаружил, что стандартные инструменты для реализации условного форматирования диаграммы в нем отсутствуют. Да, можно вручную закрасить нужный нам столбец в определенный цвет, но если у нас много условий закрашивания или много столбцов в диаграмме — это будет довольно проблематично. Именно поэтому, я решил немного углубиться в эту тему и найти возможные способы, с помощью которых можно реализовать хоть какое-то «Условное форматирование» в диаграммах Excel.
Условное форматирование с помощью дополнительных столбцов.
Один из вариантов создания такого условного форматирования — расширение основной таблицы. Мы добавляем несколько новых столбцов, в каждый из которых будут отбираться наши основные значения, в зависимости от выполнения того или иного условия. Далее, строим новую диаграмму, где источником данных будут являться все наши второстепенные столбцы. В настройках таблицы включаем стопроцентное перекрытие рядов и для каждого из ряда выбираем свой цвет.
На словах, возможно, звучит немного запутанно, но сейчас все покажу на примере.
Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:
- Значения менее 3000. Формула в ячейке C2: =ЕСЛИ( B2 <3000; B2 ;НД () )
- Значения более 3000, но менее 5000. Формула в ячейке D2: =ЕСЛИ(И( B2 >=3000; B2 <5000);B2;НД () )
- Значения более 5000, но менее 7000. Формула в ячейке E2: =ЕСЛИ(И( B2 >=5000; B2 <7000);B2;НД())
- Значения более 7000. Формула в ячейке F2: =ЕСЛИ( B2 >=7000; B2 ;НД () )
Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:
- Включить классическое условное форматирование для ячеек, которое будет изменять цвет шрифта ячеек с ошибками на белый. Таблица будет выглядеть опрятнее.
- После построения диаграммы, скрыть столбцы с дополнительной частью таблицы. Изначально, диаграмма не будет отображать скрытые данные, но это решается установкой галочки «Показывать данные в скрытых строках и столбцах» в настройках (при выборе источника данных для диаграммы).
После этого, вставляем новую диаграмму и источником выбираем всю нашу большую таблицу, исключая начальный столбец со значениями (чтобы не было задвоения данных на диаграмме). После этого, как я уже говорил, устанавливаем «Перекрытие столбцов» на 100% и получаем уже готовую таблицу, в которой уже реализовано условное форматирование.
Далее, если Вам необходимо, изменяете ширину столбцов и цвет каждой группы данных (цвет каждого столбца изменять не нужно). Такое условное форматирование динамично: не нужно каждый раз перестраивать дополнительную таблицу, достаточно просто заменить значения в исходной и столбцы все перестроятся и перекрасятся.
Условное форматирование диаграммы с помощью VBA.
Второй вариант — использование макросов VBA. Нравится этот способ мне гораздо больше: не нужно строить лишние таблицы, выбирать новые источники данных в настройках и настраивать «корректный» вывод ошибок с «#Н/Д». Достаточно один раз подготовить код и использовать его по необходимости.
Есть несколько алгоритмов для закрашивания столбцов диаграммы с помощью макросов на VBA , но ниже я приведу пример того, который я считаю наиболее оптимальным и удобным. В нем мы напрямую обращаемся к столбцам диаграммы и соответствующим значениям, не затрагивая сам источник данных (то есть нам не важно, где расположена сама таблица со значениями):
Все просто. Данный макрос подсвечивает диаграмму тремя цветами (по типу «Светофор»: красный, желтый и зеленый), в зависимости от принадлежности значения столбца диаграммы к определенному диапазону. Диапазонов, соответственно, тоже три и задаются они с помощью двух переменных: FirstValue и SecondValue (все значения меньше FirstValue, между FirstValue и SecondValue и больше SecondValue). Значения этих переменных задаются в макросе, точно так же, как и цвета.
При запуске данного макроса мы получим следующий результат:
Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.
Если очень хочется, условное форматирование диаграммы через макрос можно усовершенствовать:
- Добавить еще несколько условий
- Добавить под новые условия новые цвета
- Создать форму VBA, на которой можно будет самостоятельно выбирать цвета через палитру и задавать диапазоны условий, не изменяя код
Также, на лист с диаграммой можно добавить обработку события (автоматическое выполнение кода при определенном действии пользователя), которое будет автоматически запускать закрашивание диаграммы, при изменений значений в источнике данных. В таком случае, условное форматирование становится динамичным и отпадает необходимость каждый раз запускать макрос, достаточно просто изменить значение в исходной таблице. В примере, который можно скачать в конце статьи, оно реализовано следующим образом:
Сначала идет проверка на принадлежность изменяемой ячейки к нужному нам диапазону, затем выделяется диаграмма, а после — диаграмма закрашивается заново. В данном коде выделяется именно первая диаграмма листа, поэтому если диаграмм на листе несколько — в код нужно подставить порядковый номер искомой диаграммы.
Надстройка SHTEM для Excel.
Условное форматирование для диаграммы реализовано пока что только в тестовой версии надстройки SHTEM для Excel: с формой для ввода ограничений и несколькими заданными наборами цветов (в том числе и с градиентом). Сейчас функция тестируется на работоспособность в различных условиях и спустя некоторое время будет добавлена в основную версию надстройки.
Предварительный вариант инструмента условного форматирования диаграмм в тестовой версии надстройки выглядит следующим образом:
Диапазоны для градиента могут заполняться автоматически (числовой ряд будет делиться на 5 равных отрезков). Возможно, чуть изменю цвета и добавлю еще несколько вариантов градиента, а также добавлю возможность выбирать пользовательские цвета из палитры.
Напомню, что скачать надстройку может абсолютно любой желающий: изначально дается тестовый период на 30 дней, а затем, если необходимо, я могу выдать вам код для активации неограниченного доступа.
Условное форматирование: заключение.
Если Вы часто формируете диаграммы, где столбцы должны быть залиты разными цветами, в зависимости от их значения — перечисленные способы безусловно вам подойдут. «Гибкими» их, конечно, не назовешь, но в любом случае, это лучше, чем ручная заливка каждого из столбцов. Если Вам известны какие-нибудь другие способы условного форматирования диаграммы или просто хотите дополнить мою статью — пишите об этом в комментариях, обязательно все прочитаю. А скачать файл с реализацией перечисленных здесь способов заливки диаграммы, можно нажав на кнопочку ниже:
Цвет диаграммы из ячеек с ее данными
Хотелось бы, чтобы столбцы на гистограмме (или дольки на круговой и т.п.) автоматически имели тот цвет, который был использован для заливки соответствующих им ячеек с исходными данными:
Предвосхищая удивленно-возмущенные крики отдельных товарищей, надо отметить, что, конечно же, цвет заливки на диаграмме можно менять и вручную (правой кнопкой по столбцу — Формат точки/ряда данных (Format data point/series) и т.д. — никто не спорит. Но на практике случается куча ситуаций, когда проще и удобнее сделать это непосредственно в ячейках с данными, а диаграмма потом должна перекраситься уже автоматически. Попробуйте, например, задать заливку по регионам для столбцов на этой диаграмме:
Думаю, вы поняли идею, да?
Решение
Ничем, кроме как макросом, такое реализовать не получится. Поэтому открываем Редактор Visual Basic с вкладки Разработчик (Developer — Visual Basic Editor) или нажимаем сочетание клавиш Alt+F11, вставляем новый пустой модуль через меню Insert — Module и копируем туда текст вот такого макроса, который и будет делать всю работу:
Теперь можно закрыть Visual Basic и вернуться в Excel. Использовать созданный макрос очень просто. Выделите диаграмму (область диаграммы, а не область построения, сетку или столбцы!):
и запустите наш макрос с помощью кнопки Макросы на вкладке Разработчик (Developer — Macros) или с помощью сочетания клавиш Alt+F8. В том же окне можно, в случае частого использования, назначить макросу сочетание клавиш с помощью кнопки Параметры (Options) .
Единственной ложкой дегтя остается невозможность применения подобной функции для случаев, когда цвет ячейкам исходных данных назначается с помощью правил условного форматирования. К сожалению, Visual Basic не имеет встроенных средств для считывания таких цветов. Есть, конечно, определенные "костыли", но работают они не для все случаев и не во всех версиях.