Excel подписи данных в диаграмме

Многоуровневые подписи категорий на графиках в Excel

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

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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

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


Для построения гистограммы выделим диапазон данных B2:C13, и выберем в панели вкладок Вставка -> Диаграмма -> Гистограмма -> Гистограмма с группировкой:


Как мы видим, тут в качестве подписи данных используются данные из столбца B — как раз пример одноуровневой подписи.

Если же мы выделим диапазон данных A2:C13 (т.е. дополним данные из предыдущего примера столбцом A) и повторим построение, то получим:


В результате в подписи категорий данные расположились в несколько уровней: 1 уровень — город (Берлин, Мадрид и т.д.), 2 уровень — страна (Германия, Испания и т.д.). В данном случае в качестве подписи подставляются данные уже из двух столбцов: A и B.

Для включения или отключения многоуровневости нажмем правой кнопкой мыши по горизонтальной оси и выбираем Формат оси (или нажимаем комбинацию клавиш Ctrl + 1), а далее в разделе Параметры оси –> Подписи ставим или снимаем флажок напротив Многоуровневые подписи категорий:


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


Таким образом, в зависимости от ваших задач при построении графика, можно получить практически любой вид подписей в несколько уровней. При этом, количество уровней никак не ограничено, можно делать как 2, так 3 и больше:

Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.

Источник: tutorexcel.ru

Подписи данных диаграммы Excel

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

Любая диаграмма состоит из элементов, к которым относятся такие объекты, как область построения диаграммы, оси, координатная сетка, маркеры данных, заголовки. Щелчком мыши можно выделить любой элемент, а щелчком правой кнопки — раскрыть его контекстное меню. Кроме набора стандартных элементов, которые практически всегда присутствуют на диаграмме, Excel позволяет добавлять разные дополнительные компоненты, усиливающие наглядность диаграммы и повышающие ее информативность. К таким вспомогательным элементам относятся подписи рядов данных.

1.Щелкните правой кнопкой мыши на любом секторе диаграммы и выберите в контекстном меню команду Формат рядов данных.

2. Раскройте вкладку Подписи данных, показанную на рис. 11.11. В разделе Включить в подписи можно установить один или несколько флажков, чтобы определить, что именно будет содержаться в подписи:

* имена рядов;
* имена категорий — название соответствующей отметки оси X;
* значения — числа источника данных;
* доли — процентная доля данного элемента данных в общем объеме;
* размеры пузырьков — численное значение диаметра пузырька (доступно только для диаграмм типа Пузырьковые.

Рис. 11.11. Настройка подписей данных для диаграммы Excel

Так как секторные диаграммы не имеют осей, на них автоматически добавляются подписи данных, показывающие соответствие сектора тому или иному столбцу (строке) данных. В рассматриваемом примере подписями данных являются названия категорий Январь, Февраль и т..д.

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

4. Сбросьте флажок Линии выноски. Этот флажок выводит линии, связывающие подписи с соответствующими маркерами данных, но в данной ситуации и без них понятно, какому сектору соответствует та или иная надпись, а линии лишь загромождают диаграмму.

5. Щелкните на кнопке ОК. Теперь рядом с названиями месяцев появятся числа, характеризующие долю, занимаемую соответствующим сектором в целом круге и выраженную в процентах.

Источник: tepka.ru

Изменение формата подписей данных в диаграмме

Метки данных, содержащие сведения о рядах или отдельных точках данных, упрощают понимание диаграммы. Так, без меток данных на приведенной ниже круговой диаграмме было бы не совсем понятно, что объем продаж кофе составляет 38 % от общего объема продаж. Метки данных можно форматировать, чтобы отобразить специфические элементы, например процентные значения, имена рядов или категорий.

Существует множество параметров форматирования меток данных. Вы можете добавить линии выноски, настроить форму метки, а также изменить ее размер. Все нужные для этого команды содержатся в области задач Формат меток данных. Чтобы перейти в нее, добавьте метки данных, а затем выделите метку, которую требуется отформатировать, и выберите Элементы диаграммы > Метки данных > Дополнительные параметры.

Чтобы перейти в соответствующий раздел, щелкните один из четырех значков, показанных ниже ( Заливка и границы, Эффекты, Размер и свойства ( Макет и свойства в Outlook или Word) или Параметры подписи).

Совет: Убедитесь, что выбрана только одна метка данных. Чтобы быстро применить пользовательское форматирование к другим точкам ряда данных, выберите пункты Параметры подписи > Метка рядов данных > Создать копию текущей метки.

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

Читайте также:  Сводная таблица эксель

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

Выделите соединительные линии, которые требуется изменить.

Выберите пункт Заливка и границы > Линия, а затем внесите необходимые изменения.

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

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

Выберите пункт Заливка и границы > Граница, а затем внесите необходимые изменения.

Совет: С помощью эффектов можно сделать подпись действительно оригинальной. Для этого откройте вкладку Эффекты и выберите нужный эффект. Только смотрите не перестарайтесь.

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

Щелкните правой кнопкой мыши подпись данных, которую требуется изменить, а затем выберите команду Изменить формы меток данных.

Выберите нужную фигуру.

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

Совет: Вы можете настроить другие параметры размера (в Excel и PowerPoint) и выравнивания на вкладке Размер и свойства ( Макет и свойства в Outlook или Word). Для этого дважды щелкните подпись данных и выберите Размер и свойства.

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

Щелкните подпись данных правой кнопкой мыши и выберите команду Вставить поле метки данных.

Если выделен весь ряд данных, эта команда будет отсутствовать. Убедитесь, что выбрана только одна метка данных.

Выберите поле, которое вы хотите добавить к подписи данных.

Чтобы связать подпись данных с ссылкой на ячейку, щелкните [Ячейка] Выберите ячейку, а затем введите ссылку на ячейку.

Совет: Чтобы вернуть стандартную подпись данных, в группе Параметры подписи нажмите кнопку Сброс.

Чтобы отформатировать метки данных, выделите диаграмму и на вкладке Конструктор диаграмм последовательно выберите Добавить элемент диаграммы > Подписи данных > Дополнительные параметры подписей данных.

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

Источник: support.office.com

data_client

Как создать диаграмму с интерактивными подписями данных в Excel

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

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

  • Какая общая сумма ряда?
  • Какой процент у каждого сегмента в ряду?
  • Какое процентное изменение по сравнению с предыдущим периодом?

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

Динамические подписи данных на гистограмме

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

Как создать график с динамическими подписями данных

Настройка динамических подписей данных не слишком сложна, но требует немного труда. Нам понадобятся следующие компоненты для этой диаграммы:

  • Функция ТЕКСТ
  • Функция ВЫБОР
  • Одна сводная таблица
  • Один срез
  • Одна гистограмма

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

Загрузите файл в качестве примера, что бы следовать за моими пояснениями. Внимание: Этот файл работает правильно в Excel 2013 или 2016.

Шаг 1: Создайте гистограмму с итоговыми данными

Шаг 2: Расчет значений для подписей данных

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

Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.

Шаг 3: Используйте формулу ТЕКСТ для формирования подписей

Как правило, диаграмма отображает подписи данных на основе основного источника для диаграммы.

В Excel 2013 была введена новая возможность под названием “Значения из ячеек”. Эта возможность позволяет указать диапазон, который мы хотим использовать для подписей данных.

Так как наши подписи данных изменяются между финансовым ($) и процентным (%) форматом, нам нужен способ, чтобы также изменить форматирование чисел на графике. В противном случае Excel будет отображать десятичное число, вместо процентов.

К счастью, мы можем использовать функцию ТЕКСТ для этого. Функция ТЕКСТ позволяет Вам взять число и указать формат, в котором Вы хотели бы, что бы оно отобразилось.

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

Читайте также:  Как в excel сделать уровни

Шаг 4: Использование функции ВЫБОР

Используйте функцию ВЫБОР, для того что бы определить какие именно подписи данных необходимо отобразить на диаграмме.

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

Нам нужно будет использовать функцию поиска, чтобы возвратить правильную метрику по выбору среза. Функция ВЫБОР отлично подходит для этого.

Функция ВЫБОР позволяет нам указать индекс (1,2,3. ) и она возвратит то значение, которое соответствует индексу.

Сейчас мы просто добавим ячейку, которая содержит номер индекса, и указывает на три метрики для каждого значения в формуле ВЫБОР.

В итоге срез будет управлять индексом.

Шаг 5: Настройка подписей данных

Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию “Значения из ячеек”, которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку “Выбрать диапазон” в меню Параметры подписи.

Затем выберите диапазон, который содержит подписи данных для этого ряда.

Повторите этот шаг для каждого ряда на гистограмме.

Шаг 6: Настройка сводной таблицы и среза

Последний шаг позволит сделать наши подписи данных интерактивными. Мы сделаем это при помощи сводной таблицы и среза. Исходные данные для сводной таблицы – это таблица на рисунке ниже.

Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

Затем вставьте срез для поля Символ. Для этого кликните на сводную таблицу, зайдите в меню Конструктор и там нажмите кнопку Вставить срез.

Срез может быть отформатирован, чтобы соответствовать оформлению Вашего графика. Я изменил количество столбцов до 3, что бы показать кнопки среза по горизонтали.

Последний шаг состоит в том, что бы в ячейке С38 сослаться на значение индекса из сводной таблицы для корректной работы формулы ВЫБОР. Когда пользователь нажимает на кнопку среза, в ячейке С38 отразится индекс выбранного элемента сводной таблицы.

Формулы ВЫБОР автоматически отобразят подписи данных для выбранного среза.

Было проделано много шагов, что бы создать эту интерактивную гистограмму. К счастью, Вы можете скачать пример и модифицировать его для себя.

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

Спасибо за внимание. Какие другие подписи данных или виды диаграмм использовали бы Вы?
Пожалуйста, оставляйте свои комментарии ниже, а также вопросы и предложения.

Источник: www.excelguide.ru

Замена цифр на подписи в осях диаграммы Excel

Недавно у меня возникла задача – отразить на графике уровень компетенций сотрудников. Как вы, возможно, знаете, изменить текст подписей оси значений невозможно, поскольку они всегда генерируются из чисел, обозначающих шкалу ряда. Можно управлять форматированием подписей, однако их содержимое жестко определено правилами Excel. Я же хотел, чтобы вместо 20%, 40% и т.п. на графике выводились названия уровней владения компетенциями, что-то типа:

Рис. 1. Диаграмма уровня компетенций вместе с исходными данными для построения.

Метод решения подсказала мне идея, почерпнутая в книге Джона Уокенбаха «Диаграммы в Excel»:

Скачать заметку в формате Word, примеры в формате Excel

При создании диаграммы, показанной на рис. 1 использовались следующие принципы:

  • Диаграмма фактически является смешанной: в ней сочетаются график и точечная диаграмма.
  • «Настоящая» ось значений скрыта. Вместо нее выводится ряд точечной диаграммы, отформатированный таким образом, чтобы выглядеть как ось (фиктивная ось).
  • Данные точечной диаграммы находятся в диапазоне А12:В18. Ось Y для этого ряда представляет числовые оценки каждого уровня компетенций, например, 40% для «базового».
  • Подписи оси Y являются пользовательскими подписями данных ряда точечной диаграммы, а не подписями оси!

Чтобы лучше понять принцип действия фиктивной оси, посмотрите на рис. 2. Это стандартная точечная диаграмма, в которой точки данных соединены линиями, а маркеры ряда имитируют горизонтальные метки делений. В диаграмме используются точки данных, определенные в диапазоне А2:В5. Все значения Х одинаковы (равны нулю), поэтому ряд выводится как вертикальная линия. «Метки делений оси» имитируются пользовательскими подписями данных. Для того, чтобы вставить символы в подписи данных, нужно последовательно выделить каждую подпись по отдельности и применить вставку спецсимвола, пройдя по меню Вставка — Символ (рис. 3).

Рис. 2. Пример отформатированной точечной диаграммы.

Рис. 3. Вставка символа в подпись данных.

Давайте теперь рассмотрим шаги создания диаграммы, приведенной на рис. 1.

Выделите диапазон А1:С7 и постройте стандартную гистограмму с группировкой:

Разместите легенду сверху, добавьте заголовок, задайте фиксированные параметры оси значений: минимум (ноль), максимум (1), цену основных делений (0,2):

Выделите диапазон А12:В18 (рис. 1), скопируйте его в буфер памяти. Выделите диаграмму, перейдите на вкладку Главная и выберите команду Вставить->Специальная вставка.

Установите переключатели новые ряды и Значения (Y) в столбцах. Установите флажки Имена рядов в первой строке и Категории (подписи оси Х) в первом столбце.

Нажмите Ok. Вы добавили в диаграмму новый ряд

Выделите новый ряд и правой кнопкой мыши выберите Изменить тип диаграммы для ряда. Задайте тип диаграммы «Точечная с гладкими кривыми и маркерами»:

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

Выделите основную вертикальную ось и отформатируйте ее, указав для основных делений – нет, для подписей оси – нет.

Читайте также:  В excel не отображаются листы

Выделите ряд точечной диаграммы и отформатируйте его. Цвет линии – черный, маркер – похожий на деления оси (выберите размер маркера и задайте черный цвет), толщину линии подберите так, чтобы она не отличалась от горизонтальной линии, добавьте подписи данных (всё равно, какие). Отформатируйте подписи данных, указав на вкладке Параметры подписи – Слева.

Войдите в легенду, выделите и удалите описание ряда, относящегося к точечной диаграмме.

Выделяйте по очереди подписи данных ряда точечной диаграммы и (как показано на рис. 3) напечатайте в них те слова, которые хотели (область С13:С18 рис. 1).

4 комментария для “Замена цифр на подписи в осях диаграммы Excel”

Выделите диапазон А12:В18 (рис. 1), скопируйте его в буфер памяти. Выделите диаграмму и выберите команду Правка->Специальная вставка.

— нет такой команды в выпадающем меню по правой кнопке 🙁
Не могли бы вы добавить принтскрин с этой командой?

Денис, добавил соответствующую картинку в текст заметки.

подскажите пожалуйста. даны данные в разброс ( пример 7,7,0,13,64,5) и как ,не сортируя ячейки по увеличению или уменьшению, на гистограмме столбцы, отражающие эти значения по оси х поставить в диапазоне (к примеру от 0 до 10 войдут столбцы со значениями 0, 5,7), а по оси игриков частота появления этих значений, то есть 1, 2,3,4 и тд. буду очень благодарна)))

Лиляна, возможно, вам помогут идеи, содержащиеся в заметке Представление числовых данных в виде таблиц и диаграмм

Источник: baguzin.ru

Форматирование диаграмм Excel 2007

Автор: Индык Игорь Викторович
e-mail: exelentc@yandex.ru

Как добавить в диаграмме, например, в круговой, к каждому сегменту его название? А еще помню, оно как-то автоматически высчитало проценты и отображало их, как это сделать?

Во всех диаграммах Excel есть возможность добавить подписи к данным. Для этого необходимо в кликнув правой кнопки мышки на объекте (в данном случае непосредственно на круговой диаграмме) в контекстом меню выбрать Формат подписи данных…

В появившемся диалоговом окре вы можете выбрать, чтобы отображались:

  • Имя ряда – название столбца, из которого берутся данные.
  • Имена категорий – это имена данных, которые отображаются. Они же отображаются в легенде.
  • Значение – это значения категорий, на основании которых и построена данная диаграмма.
  • Доли – выводит процентное соотношение между всеми частями диаграммы.
  • Линии выноски – если вы выносите подписи данных за пределы рисунка диаграммы, то автоматически устанавливаются линии, которые связывают подпись с ее изображением на диаграмме.

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

Но в зависимости от типа диаграммы будут доступны различные типы данных для отображения.

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

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

Как мне укрупнить шкалу на диаграмме?

С помощью меню Работа с диаграммами – Макет в группе Оси нажмите на кнопку Оси. Здесь вы можете выбрать ось, на которой вам нужно изменить деление шкалы. Перейдя в меню вертикальной или горизонтально оси вам будут предложены варианты автоматического укрупнения оси до Тысячи, Милионы и т.д. Если они вас не устраивают, выберите пункт Дополнительные параметры основной вертикальной/горизонтальной оси…

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

Как на графике изменить маркер (например, я хочу не квадрат, а кружочек)?

Для изменения маркера выделите линию графика, на которой хотите изменить маркер. И с помощью контекстного меню перейдите в пункт Формат ряда данных…

Или же на вкладке Макет в группе Текущий фрагмент сначала выберите необходимый элемент из выпадающего списка Элементы диаграммы, а затем нажмите кнопку Формат выделенного фрагмента. Данный способ выбора элементов диаграммы очень удобный, если у вас много линий и они тесно переплетаются друг с другом.

В диалоговом окне Формат ряда данных в разделе Параметры маркера можно выбрать тип и размер маркера:

Как мне добавить еще одну ось на график? У меня один показатель значительно отличается от других, но все должны быть на одной диаграмме. 🙁

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

Или на вкладке Формат в группе Текущий фрагмент из выпадающего списка в поле Элементы диаграммы выберите ряд данных, который нужно отобразить на вспомогательной вертикальной оси. Затем кликните здесь же на кнопке Формат выделенного фрагмента…

В появившемся диалоговом окне Формат ряда данных в разделе Параметры оси выберите Вспомогательной оси. Нажмите кнопку Закрыть.

На вкладке Макет в группе Оси у вас появится пункт Вспомогательная вертикальная ось, с помощью которого вы сможете форматировать ее точно так же как и обычную ось.

Мне нужно, что бы одни данные были отображены в виде графика, а другие в виде гистограммы, и это все на одной диаграмме. Как это сделать в Эксель 2007?

Для этого кликните на диаграмме правой кнопки мыши по необходимому ряду данных. Из контекстного меню выберите пункт Изменить тип диаграммы ряда…

Или же с помощью вкладки Макет в группе Текущий фрагмент из выпадающего списка в поле Элементы диаграммы выберите ряд данных, который необходимо изменить. Затем перейдите на вкладку Конструктор и в группе Тип нажмите на кнопку Изменить тип диаграммы.

В диалоговом огне Изменения типа диаграммы выберите нужный вам тип.

В начало страницы

В начало страницы

Источник: on-line-teaching.com