Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как раскрасить диаграмму на основе цвета ячейки в Excel?

Как раскрасить диаграмму на основе цвета ячейки в Excel?

При создании однолинейной диаграммы все маркеры данных — точки данных на этой линейной диаграмме данных — отображаются одним и тем же цветом. Чтобы использовать разные цвета для каждого маркера данных, можно автоматически изменять цвета с помощью опции Изменить цвета для каждой точки или Изменить цвета для каждого среза.

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

Вы также можете изменить цвета, изменяя цвет каждого маркера данных вручную.

Важно: Для выполнения этих процедур необходимо иметь существующую схему. Дополнительные сведения о выборе и добавлении диаграмм см. в разделе Офис.

Автоматически изменять все цвета маркера данных по точкам или по срезам

Щелкните на графике, чтобы выбрать диапазон данных, для которого вы хотите изменить цвета.

На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат для выбранного фрагмента.

В области Формат ряда данных перейдите на вкладку Заливка & линияВкладка "Заливка и границы", разойдите на вкладку Заливка исделайте следующее:

Если вы хотите, чтобы маркеры данных в одном ряду диаграммы были окрашены по-разному, выберите Изменить цвета по точке.

Если вы хотите, чтобы все точки данных в серии данных имели одинаковый цвет на круговой диаграмме или диаграмме срезов, вы можете использовать этот флаг, чтобы на каждом срезе отображались разные цвета.

Изменять цвета отдельных маркеров данных вручную

Вы можете изменить отдельный маркер данных, выделив его на диаграмме.

На вкладке Формат в группе Стили фигуры выберите опцию Заливка фигуры.

Кнопка "Заливка фигуры" на вкладке "Формат"

Выполните одно из приведенных ниже действий.

В разделе Цвета темы или Стандартные цвета выберите нужный цвет заливки.

Чтобы быстро увидеть, как изменится диаграмма перед применением другого цвета, сначала щелкните на другом цвете. При наведении курсора на разные цвета на диаграмме цвет выделенного элемента изменяется соответствующим образом.

Чтобы удалить цвет из выбранного графического элемента, выберите опцию Нет заливки.

Чтобы использовать цвет заливки, которого нет в списке цветов темы или стандартных цветов, нажмите кнопку Другие цвета заливки. В диалоговом окне Цвета выберите нужный цвет на вкладке Стандартный или Пользовательский, затем нажмите OK.

Созданные пользовательские цвета заливки добавляются в раздел «Последние цвета», чтобы к ним можно было получить доступ снова.

Чтобы использовать изображение для заполнения формы, выберите элемент Изображение. В диалоговом окне «Вставка изображения» найдите и выберите нужное изображение, затем нажмите кнопку Вставить.

Если вам нужен эффект градиента для выбранного цвета заливки, перейдите в раздел Gradienti и выберите нужный стиль.

Чтобы использовать текстурную заливку, найдите пункт «Текстура» и выберите нужную текстуру.

Повторите эти шаги для каждого маркера данных, который нужно изменить.

Совет: Дополнительные сведения об изменении внешнего вида элементов диаграммы см. в разделе Office.

Читайте так же:
Как разбить содержимое ячейки на столбцы или строки на основе новой строки в листе Google?

Чтобы успешно выполнить эти процедуры, вам нужна уже готовая диаграмма. Подробную информацию о выборе и добавлении диаграмм см. в разделе Office.

Автоматически изменять все цвета маркера данных по точкам или по срезам

Щелкните на диаграмме, чтобы выбрать ряд данных, для которого нужно изменить цвета.

На вкладке Формат выберите кнопку Область форматирования рядом со строкой в списке Элементы диаграмм.

В области Формат ряда данных перейдите на вкладку Заливка & линия Вкладка "Заливка и границы", разведите кнопку Заливка исделайте следующее в зависимости от типа диаграммы:

Выберите Изменить цвета по точкам, чтобы изменить цвета маркеров данных в одной строке диаграммы.

Если вы хотите, чтобы все точки в серии данных отображались одним цветом на круговой диаграмме или диаграмме срезов, вы можете использовать этот флаг, чтобы отображать разные цвета в зависимости от среза.

Изменять цвета отдельных маркеров данных вручную

Выберите отдельную точку данных, которую нужно изменить на графике.

На вкладке Формат нажмите кнопку Заполнить форму.

Выполните одно из следующих действий

Чтобы изменить цвет заливки, в разделе Тематические цвета или Стандартные цвета выберите необходимый цвет.

Совет: Перед нанесением другого цвета можно быстро увидеть, как изменится график. При наведении курсора на разные цвета цвет выделенного элемента на диаграмме меняется соответствующим образом.

Чтобы удалить цвет выбранного элемента графика, выберите параметр Без заливки.

Выберите другие цвета заливки, если вы не видите ни одного из перечисленных в разделе «Цвета темы» или «Стандартные цвета». В диалоговом окне Цвета создайте пользовательский цвет заливки, используя доступные параметры, и нажмите OK.

Ваши пользовательские цвета заливки добавляются в раздел «Последние цвета», чтобы их можно было использовать повторно в будущем.

Выберите элемент Рисунок, чтобы заполнить фигуру изображением. В диалоговом окне Вставка рисунка выберите нужный рисунок и нажмите кнопку Вставить.

Чтобы применить эффект градиента к выбранному цвету заливки, найдите опцию Градиент и выберите нужный стиль градиента.

Текстурную заливку можно создать, выбрав элемент «Текстура» и нужную текстуру.

Повторите эти шаги для каждого маркера данных, который вы хотите изменить.

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

Exceltip

Условное форматирование довольно легко реализовать в листе 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. Например, формула в ячейке C3 выглядит следующим образом

Формула в ячейке C4:

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

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

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

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

Мы должны заменить столбец B на столбцы C:G, перетаскивая и изменяя размер выделения.

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

Каждый ряд данных на нашей диаграмме имеет соответствующий цвет. У нас тип диаграммы кластер, поэтому мы видим определенный ряд пустых значений.

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

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

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

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

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

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

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

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

Каждая строка данных представлена маркером и отрезком другого цвета.

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

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

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

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

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

Как изменить цвет графика в Excel?

Вы можете повторно создать график из исходной таблицы: «Вставка»-«Гистограмма»-«Сгруппированная гистограмма».

Чтобы изменить заливку первого столбца на градиент, выполните следующие действия:

  1. Щелкните один раз на первом ряду столбцов диаграммы. Все они будут выбраны автоматически. Снова щелкните на первом столбце диаграммы (который вы хотите изменить), и теперь будет выбран только один из них.
  2. Щелкните правой кнопкой мыши на первом столбце, чтобы открыть контекстное меню и выберите «Формат точки данных».
  3. В диалоговом окне «Формат точки данных» выберите слева «Заливка», а справа — «Градиентная заливка».

Теперь доступны инструменты для расширенного дизайна градиентной заливки в графике:

  • Название заготовки,
  • Тип,
  • Направление,
  • Угол,
  • Точки градиента,
  • Цвет,
  • Яркость,
  • Прозрачность
Читайте так же:
Как разбить содержимое одной ячейки на несколько столбцов в Excel?

Поэкспериментируйте с этими настройками, а затем нажмите «Закрыть». Обратите внимание, что в «Пустом заголовке» уже имеются предопределенные шаблоны: Пламя, Океан, Золото и т.д.

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

Во время поиска статистики по коронавирусу (обновленной на момент написания статьи) я зашел на информационную страницу Яндекса, посвященную данным о заболеваниях и выздоровлениях, и обнаружил довольно любопытный график:

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

Заинтересовало меня то, что цвет столбца изменяется в зависимости от соответствующего значения. И чем больше это значение, тем сильнее «краснеет» цвет столбца диаграммы. То есть, грубо говоря, это — условное форматирование столбцов диаграммы, где условие — принадлежность числового значения самого столбца к некоторому диапазону.

Мне захотелось реализовать что-то подобное в Excel, но я с удивлением обнаружил, что стандартные инструменты для реализации условного форматирования диаграммы в нем отсутствуют. Да, можно вручную закрасить нужный нам столбец в определенный цвет, но если у нас много условий закрашивания или много столбцов в диаграмме — это будет довольно проблематично. Именно поэтому, я решил немного углубиться в эту тему и найти возможные способы, с помощью которых можно реализовать хоть какое-то «Условное форматирование» в диаграммах Excel.

Условное форматирование с помощью дополнительных столбцов.

Один из вариантов создания такого условного форматирования — расширение основной таблицы. Мы добавляем несколько новых столбцов, в каждый из которых будут отбираться наши основные значения, в зависимости от выполнения того или иного условия. Далее, строим новую диаграмму, где источником данных будут являться все наши второстепенные столбцы. В настройках таблицы включаем стопроцентное перекрытие рядов и для каждого из ряда выбираем свой цвет.

На словах это может показаться немного запутанным, но я покажу вам на примере.

Условное форматирование: расширение источника данных

Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:

  • Значения меньше 3000. Формула в ячейке C2: =If( B2 <3000; B2 ;ND () )
  • Значения больше 3000, но меньше 5000. Формула в ячейке D2: =If(AND( B2 >=3000; B2 <5000);B2;ND () )
  • Значения больше 5000, но меньше 7000. Формула в ячейке E2: =If(AND( B2 >=5000; B2 <7000);B2;ND())
  • Значения больше 7000. Формула в ячейке F2: =If( B2 >=7000 ; B2 ;ND() )

Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:

  1. Включить классическое условное форматирование для ячеек, которое будет изменять цвет шрифта ячеек с ошибками на белый. Таблица будет выглядеть опрятнее.
  2. После построения диаграммы, скрыть столбцы с дополнительной частью таблицы. Изначально, диаграмма не будет отображать скрытые данные, но это решается установкой галочки «Показывать данные в скрытых строках и столбцах» в настройках (при выборе источника данных для диаграммы).

После этого, вставляем новую диаграмму и источником выбираем всю нашу большую таблицу, исключая начальный столбец со значениями (чтобы не было задвоения данных на диаграмме). После этого, как я уже говорил, устанавливаем «Перекрытие столбцов» на 100% и получаем уже готовую таблицу, в которой уже реализовано условное форматирование.

Читайте так же:
Как проверить или определить тип данных ячейки?

Условное форматирование диаграммы с помощью дополнительной таблицы

Затем, если необходимо, измените ширину столбцов и цвет каждой группы данных (не обязательно менять цвет каждого столбца). Это условное форматирование является динамическим: нет необходимости каждый раз перестраивать дополнительную таблицу, достаточно заменить значения в исходной таблице, и столбцы будут перестроены и изменены в цвете.

Условное форматирование диаграммы с помощью VBA.

Второй вариант — использование макросов VBA. Нравится этот способ мне гораздо больше: не нужно строить лишние таблицы, выбирать новые источники данных в настройках и настраивать «корректный» вывод ошибок с «#Н/Д». Достаточно один раз подготовить код и использовать его по необходимости.

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

Все просто. Данный макрос подсвечивает диаграмму тремя цветами (по типу «Светофор»: красный, желтый и зеленый), в зависимости от принадлежности значения столбца диаграммы к определенному диапазону. Диапазонов, соответственно, тоже три и задаются они с помощью двух переменных: FirstValue и SecondValue (все значения меньше FirstValue, между FirstValue и SecondValue и больше SecondValue). Значения этих переменных задаются в макросе, точно так же, как и цвета.

При выполнении данного макроса получается следующий результат

Условное форматирование: результат выполнения макроса VBA

Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.

Если очень хочется, можно улучшить условное форматирование графика с помощью макроса:

  • Добавить больше условий
  • Добавить новые цвета к новым условиям
  • Создать форму VBA, в которой можно выбирать цвета из палитры и устанавливать условия без изменения кода

Также есть возможность добавить на лист с диаграммой обработчик событий (автоматическое выполнение кода при выполнении действия пользователя), который будет автоматически затенять диаграмму при изменении значений в источнике данных. Благодаря такому решению условное форматирование становится динамическим, и для этого не нужно каждый раз запускать макрос. Достаточно просто изменить значение в таблице-источнике. Загрузите пример в конце статьи, чтобы увидеть, как это реализовано:

Сначала идет проверка на принадлежность изменяемой ячейки к нужному нам диапазону, затем выделяется диаграмма, а после — диаграмма закрашивается заново. В данном коде выделяется именно первая диаграмма листа, поэтому если диаграмм на листе несколько — в код нужно подставить порядковый номер искомой диаграммы.

Надстройка SHTEM для Excel.

На данный момент только в тестовой версии надстройки SHTEM для Excel есть примеры условного форматирования диаграмм: форма для ввода ограничений и несколько предопределенных цветовых схем (включая градиент). Через некоторое время эта функция будет добавлена в основную версию дополнения после того, как она будет протестирована в различных условиях.

Читайте так же:
Как ранжировать по двум столбцам в Excel?

Предварительная версия форматера условных диаграмм в тестовой версии плагина выглядит следующим образом:

Условное форматирование в надстройке для Excel

Градиентные диапазоны могут быть заполнены автоматически (числовой ряд будет разделен на 5 равных сегментов). Я мог бы немного изменить цвета и добавить больше вариантов градиента, а также возможность выбора пользовательских цветов из палитры.

Напоминаю, что скачать дополнение может любой желающий: первоначально предоставляется 30-дневный пробный период, а затем, при необходимости, я могу предоставить вам код для активации неограниченного доступа.

Условное форматирование: заключение.

Если Вы часто формируете диаграммы, где столбцы должны быть залиты разными цветами, в зависимости от их значения — перечисленные способы безусловно вам подойдут. «Гибкими» их, конечно, не назовешь, но в любом случае, это лучше, чем ручная заливка каждого из столбцов. Если Вам известны какие-нибудь другие способы условного форматирования диаграммы или просто хотите дополнить мою статью — пишите об этом в комментариях, обязательно все прочитаю. А скачать файл с реализацией перечисленных здесь способов заливки диаграммы, можно нажав на кнопочку ниже:

Изменение границы и фона ячеек таблицы

Вы можете изменить границы и фон любых выделенных ячеек таблицы, которые не отнесены к категориям.

Выберите ячейки, которые вы хотите изменить.

Чтобы изменить все ячейки таблицы, выделите таблицу.

В боковой панели «Формат» нажмите вкладку «Ячейка».

Сделайте одно из следующих действий

Изменение границ ячеек. В разделе Границы боковой панели нажмите кнопку макета границы под элементом Граница, чтобы выбрать границы ячеек, которые нужно отформатировать. Щелкните всплывающее меню Стили границ, а затем выберите стиль границ. Кроме того, вы можете выбрать свой собственный стиль из опций под меню.

Совет. Чтобы выбрать несколько границ, нажмите кнопку расположения границы, затем нажмите остальные границы, удерживая клавишу Command.

Элементы управления для выбора стиля границ ячеек.

Ограничения таблицы можно изменить в разделе «Таблица» на боковой панели.

Измените фон. В разделе Заливка на боковой панели щелкните в поле цвета и выберите вариант заливки. См. раздел Заливка объектов цветом или изображением в Numbers на Mac.

Можно создать правила для автоматического изменения внешнего вида ячеек в таблице, если они соответствуют заданным критериям. Условный выбор ячеек доступен в Numbers для Mac.

Первый шаг к сохранению графика, который можно использовать снова и снова, — это создание графика и настройка его внешнего вида. После этого выберите ее, откройте вкладку Инструменты диаграммы > Дизайн и нажмите Сохранить как шаблон. Нажмите Сохранить, чтобы сохранить шаблон.

Затем вы можете применить этот шаблон в процессе создания или к существующему графику, чтобы придать ему точно такой же вид. Для этого нажмите на график, затем в разделе Инструменты графика > Макет выберите Изменить тип графика, откройте раздел Шаблоны, выберите нужный шаблон и нажмите OK.

Диаграммы и графики в Excel

Эти советы и рекомендации помогут вам создавать лучшие диаграммы в Excel 2007 и 2010 быстрее и эффективнее.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector