Как сделать вторичную круговую диаграмму в excel?

Круговая диаграмма в MS EXCEL с суммой

Построим круговую диаграмму и выведем суммарное значение в центре.

Пусть необходимо построить в MS EXCEL круговую диаграмму и вывести суммарное значение в ее центре (см. файл примера ):

Исходная таблица содержит данные о прибыли 4-х филиалов и ее суммарное значение.

Проще всего такую диаграмму создать предварительно построив диаграмму из статьи Совмещаем 2 круговых диаграммы.

Затем, необходимо удалить первые 3 значения в столбце «Продажи», переименовать столбец и вместо 4-го значения ввести формулу, подсчитывающую суммарное значение прибыли.

СОВЕТ: Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL, в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм.

Связанные статьи

Диаграмма с выделенной областью (зеленая тема)

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

Диаграмма с выделенной областью (желто-бордовая тема)

Этот тип диаграммы подходит для презентации отчета о динамике объемов продаж компании как в денежном выражении, так и в процентном. Диаграмма создана стандартными средствами MS EXCEL. Темная граница в верхней части диаграммы выполнена с помощью диаграммы типа График.

Линейчатая диаграмма с подписями категорий

Особенность этой диаграммы состоит в том, что подписи, относящиеся к разным категориям, отображаются над данными. Это, с одной стороны, позволяет визуально выделить значения каждой категории, а с другой стороны — отобразить все данные на одной диаграмме для их сравнения. Диаграмма создана стандартными средствами MS EXCEL, а для отображения подписей использован дополнительный ряд данных.

Столбчатая диаграмма с % выполнения

Столбчатая диаграмма с процентом (%) выполнения, отображает как фактическое значение, так и его изменение относительно предыдущего значения. Диаграмма создана стандартными средствами MS EXCEL, для отображения % изменения использована вспомогательная горизонтальная ось данных.

Прогнозная диаграмма (5 сценариев)

Прогнозная диаграмма отображает 5 вариантов развития событий: базовый, умеренный, плановый, оптимистический и супероптимистический. Этот тип диаграммы можно использовать для визуализации разных вариантов прогноза: продаж, затрат и других показателей. Диаграмма создана стандартными средствами MS EXCEL.

Столбчатая диаграмма (план-факт)

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

Столбчатая диаграмма (отчет о продажах)

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

Линейчатая диаграмма (Экспорт-Импорт)

Линейчатая диаграмма, отображает ежегодные значения в разрезе 2-х взаимоисключающих категорий, например, Экспорт-Импорт; Успех-Не успех, Продано-На складе и т.д. Так же с помощью этой диаграммы можно отследить изменения по месяцам, кварталам, дням. Диаграмма создана стандартными средствами MS EXCEL, при настройке диаграммы использованы дополнительные ряды данных и вспомогательные оси.

Линейчатая диаграмма (Экспорт-Импорт, Тип2)

Линейчатая диаграмма, отображает ежегодные значения в разрезе 2-х взаимоисключающих категорий, например, Экспорт — Импорт; Успех — Не успех, Продано — На складе и т.д. Так же с помощью этой диаграммы можно отследить изменения по месяцам, кварталам, дням (7 периодов). Диаграмма создана стандартными средствами MS EXCEL, при настройке диаграммы использованы дополнительные ряды данных.

Прогнозная диаграмма2 (3 сценария: пессимистичный, базовый, оптимистичный)

Прогнозная диаграмма отображает 3 варианта развития событий: пессимистичный, базовый, оптимистичный. Этот тип диаграммы можно использовать для визуализации разных вариантов прогноза: продаж, затрат и других показателей. Диаграмма создана стандартными средствами MS EXCEL.

Столбчатая диаграмма (Факт-Прогноз)

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

Читать еще:  Как в excel сделать формат листа а2?

Диаграмма с областями (Факт-Прогноз)

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

Столбчатая диаграмма (в том числе)

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

Диаграммы в Excel

Для облегчения чтения отчетности, особенно ее анализа, данные лучше визуализировать. Согласитесь, что проще оценить динамику какого-либо процесса по графику, чем просматривать числа в таблице.

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

Вставка и построение

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

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

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

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

На упомянутом выше окне нажмите кнопку «Добавить» в поле «Элементы легенды». Появится форма «Изменение ряда», где нужно задать ссылку на имя ряда (не является обязательным) и значения. Можно указать все показатели вручную.

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

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

Посмотрите, что должно получиться:

Элементы диаграммы

По умолчанию диаграмма состоит из следующих элементов:

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

Помимо упомянутых выше объектов, могут быть добавлены такие как:

  • Названия диаграммы;
  • Линий проекции – нисходящие от рядов данных на горизонтальную ось линии;
  • Линия тренда;
  • Подписи данных – числовое значение для точки данных ряда;
  • И другие нечасто используемые элементы.

Изменение стиля

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

Часто имеющихся шаблонов достаточно, но если Вы хотите большего, то придется задать собственный стиль. Сделать это можно кликнув по изменяемому объекту диаграммы правой кнопкой мыши, в меню выбрать пункт «формат Имя_Элемента» и через диалоговое окно изменить его параметры.

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

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

Как и со стилями, каждый элемент можно добавить либо удалить по-отдельности. В версии Excel 2007 для этого предусмотрена дополнительная вкладка «Макет», а в версии Excel 2013 данный функционал перенесен на ленту вкладки «Конструктор», в область «Макеты диаграмм».

Типы диаграмм

Идеально подходить для отображения изменения объекта во времени и определения тенденций.
Пример отображения динамики затрат и общей выручки компании за год:

Гистограмма

Хорошо подходит для сравнения нескольких объектов и изменения их отношения со временем.
Пример сравнения показателя эффективности двух отделов поквартально:

Предназначения для сравнения пропорций объектов. Не может отображать динамику.
Пример доли продаж каждой категории товаров от общей реализации:

Диаграмма с областями

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

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

Так как для нас первостепенно видеть именно потенциал, то данный ряд отображается первым. Из ниже приведенной диаграммы видно, что с 11 часов до 16 часов отдел не справляет с потоком клиентов.

Читать еще:  Как сделать из текста дату excel?

Представляет собой систему координат, где положение каждой точки задается значениями по горизонтальной (X) и вертикальной (Y) осям. Хорошо подходить, когда значение (Y) объекта зависит от определенного параметра (X).

Пример отображения тригонометрических функций:

Поверхность

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

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

Обычно подобные диаграммы отображают коридор колебания (максимальное и минимальное значение) и конечное значение в определенных период.

Лепестковая

Особенность этого типа диаграмм заключается в том, что горизонтальная ось значений расположена по кругу. Таким образом, она позволяет более наглядно отобразить различия между объектами по нескольких категория.

На ниже приведенной диаграмме представлено сравнение 3-х организаций по 4-ем направлениям: Доступность; Ценовая политика; Качество продукции; Клиентоориентированность. Видно, что компания X лидирует по первому и последнему направлению, компания Y по качеству продукции, а компания Z предоставляет лучшие цены.

Также можно сказать, что компания Х является лидером, т.к. площадь ее фигуры на диаграмме является самой большой.

Смешанный тип диаграмм

Приложение Excel позволяет совмещать в одной диаграмме несколько типов. В качестве примера совместим тип графика и гистограммы.

Для начала все ряды строятся с применением одного вида, затем он меняется для каждого ряда отдельно. Кликнув по требуемому ряду правой кнопкой мыши, из списка выберите пункт «Изменить тип диаграммы для ряда…», затем «Гистограмма».

Иногда, из-за сильных различий значений рядов диаграммы, использование единой шкалы невозможно. Но можно добавить альтернативную шкалу. Перейдите в меню «Формат ряда данных…» и в разделе «Параметры ряда» переместите флажок на пункт «По вспомогательной оси».

Теперь диаграмма приобрела такой вид:

Тренд Excel

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

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

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

  • Экспоненциальный тренд. Если значения по вертикальной оси (Y) возрастают с каждым изменением по горизонтальной оси (X).
  • Линейный тренд используется, если значения по Y имеют приблизительно одинаковые изменения для каждого значения по X.
  • Логарифмический. Если изменение по оси Y замедляется с каждым изменениям по оси X.
  • Полиномиальный тренд применяется, если изменения по Y происходят как в сторону увеличения, так в уменьшения. Т.е. данные описывают цикл. Хорошо подходит для анализа большого набора данных. Степень тренда выбирается в зависимости от количества пиков циклов:
    • Степень 2 – один пик, т.е. половина цикла;
    • Степень 3 – один полный цикл;
    • Степень 4 – полтора цикла;
    • и т.д.
  • Степенной тренд. Если изменение по Y растет с примерно одинаковой скоростью при каждом изменением X.

Линейная фильтрация. Не применим для прогноза. Используется для сглаживания изменений Y. Усредняет изменение между точками. Если в настройках тренда параметру точки задать 2, то усреднение производится между соседними значениями оси X, если 3, то через одну, 4 через – две и т.д.

Сводная диаграмма

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

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

  • Выделите сводную таблицу;
  • Пройдите на вкладку «Анализ» (в Excel 2007 вкладка «Параметры»);
  • В группе «Сервис» щелкните по пиктограмме «Сводная диаграмма».

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

Пример №6. Определение долей малых компаний

Внимание:Задание выполнить в новой книги Excel с наименованием «Пример №6», скопировав лист «Пример №5 Анализ целей» из предыдущей книги Excel.

Рисунок 42. Вторичная круговая диаграмма в Excel 2010 и таблица с данными к примеру №6

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

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

Читать еще:  Функция истина ложь в excel как сделать

Решение

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

1) Наберите таблицу, показанную на рисунок 42;

2) Выберите Вставка ⟶ Круговая ( на панели Диаграммы)⟶ Вторичная круговая(она расположена третьей в списке круговых диаграмм);

3) Щелкните правой кнопкой мыши по области построения диаграммы, чтобы вызвать контекстное меню, и в появившемся списке выберите Выбрать данные;

4) В появившемся окне нажмите кнопу Добавить в разделеЭлементы легенды (ряда);

5)В появившемся окне выбора данных нажмите (напротив поля Значения) и выделите все данные об объемах продаж. После этого нажмите и кнопку ОК.

6) Нажмите кнопку Изменить в разделе Подписи горизонтальной оси (категории), нажмите , выделите столбец с названиями компаний, нажмите , после чего нажмите кнопку ОК.

7) В окне Выбор источникаданных нажмите кнопку ОК;

8) Добавьте подписи данных. Удалите из подписей значения, добавьте доли и имена категорий;

9) Щёлкните левой кнопкой мыши по диаграмме так, чтобы выделились сами круги.

После этого щёлкните по одному из кругов правой кнопкой, чтобы вызвать контекстное меню. В появившемся меню выберите Формат ряда данных, вы увидите окно настроек. В поле Разделить рядвыберитеПоложениеи в поле Вторая область содержит первую укажите значение 6. В окне настроек нажмите кнопку Закрыть.

Теперь поговорим о настройках параметров ряда для вторичной круговой диаграммы.

· Параметр Разделить ряд задаёт, как будут разделены данные для построения основного и вторичного круга. Данный параметр может принимать четыре значения – Положение, Значение, Процент и Другое. Выбрав значение Положение, вам будет необходимо указать, начиная с какого элемента по номеру (снизу) разделить данные. Все элементы, начиная с элемента под указанным номером (снизу), попадут во вторичный круг, выше указанного элемента – в первичный. Выберите Значение, если хотите во вторичный круг поместить значения, меньшие, указанного вами (Вторая область содержит значения меньшеи укажите нужное число). Если вы хотите во вторичный круг поместить значения, которые меньше указанного значения процентов от общего объёма, в качестве значения параметра Разделить ряд выберите Процент и укажите необходимое значение в процентах. Вы так же можете выбрать параметр Другое, в этом случае вы сможете выбрать любую точку на диаграмме ( кроме точки Другой) и указать, к какому кругу он будет относиться – это удобно, когда вам нужно во вторничную диаграмму поместить несмежные значения из таблицы (появится параметр Точка относится к, выбрав значение которого вы отнесёте выбранную точку к тому или иному кругу).

· Параметр Разрезанная круговая диаграмма. С этим параметром мы уже знакомы – с его помощью вы можете «разрезать» области построения (круги) на отдельные элементы.

· С помощью параметра Боковой зазор вы можете указать расстояние от первого до второго круга. Значение указывается в процентах (от 0 до 500). Данным параметром вы можете управлять с помощью ползунка или вводя конкретное числовое значение.

· Параметр Размер второй области построения. По умолчанию первая область построения больше второй, но вы можете сделать их равными или сделать так, чтобы вторая область построения была больше первой. Значения данного параметра измеряются в процентах и изменяются от 0 до 200. Если вы укажите значение 100 – обе области построения будут одинаковых размеров, если меньше 100 – первая область построения будет больше второй, если больше 100 – вторая область построения будет больше, чем первая.

Примечание.

Стоит отметить, что какой бы вариант вы не выбрали – в первичной области построения, кроме всех точек, указанных вами, будет ещё одна – Другой. Эта точка добавляется автоматически (её значение равно сумме значений всех точек во второй области построения).

В качестве альтернативы вторичной круговой диаграмме вы можете выбрать вторичную гистограмму – рисунок 43.

Рисунок 43. Вторичная гистограмма в Excel 2010

Внимание! Для вторичной диаграммы используйте:

Формат подписей данных Положение В центре.

Стоит отметить случаи, когда лучше использовать тот или иной тип диаграммы:

1) Используйте обычную круговую диаграмму, когда вам необходимо проанализировать от 2 до 5 значений;

2) Вторичную круговую диаграмму следует использовать, когда вы анализируете от 6 до 10 значений;

3) Вторичную гистограмму следует использовать, когда вы сравниваете от 6 до 15 значений;

4) Используйте кольцевую диаграмму, когда хотите сравнить данные за 2 периода ( например, данные о продажах за текущий и предыдущий год); в остальных случаях (когда периодов больше двух) лучше использовать одну гистограмму с накоплением.

Ссылка на основную публикацию
Adblock
detector