Сложные диаграммы в excel: Построение диаграмм с несколькими рядами данных в EXCEL. Примеры и описание

Комбинированные диаграммы в Excel и способы их построения

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

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

Как построить комбинированную диаграмму в Excel

Способы построения комбинированной диаграммы в Excel:

• преобразование имеющейся диаграммы в комбинированную;
• добавление вспомогательной оси.

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

Выделим столбцы диапазона, включая заголовки. На вкладке «Вставка» в группе «Диаграммы» выберем обычный «График с маркерами».

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

Каким образом можно комбинировать разные типы диаграмм? Щелкнем правой кнопкой мыши «К-во, шт.». В открывшемся окне выберем «Изменить тип для ряда».

Откроется меню с типами диаграмм. В разделе «Гистограмма» выберем плоскую столбчатую «Гистограмму с группировкой».

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

Выделим гистограмму, щелкнув по ней мышкой. Перейдем на вкладку «Макет». Группа «Текущий фрагмент» — инструмент «Формат выделенного фрагмента».

Откроется окно «Формат ряда данных». На вкладке «Параметры ряда» поставим галочку напротив «Построить ряд по вспомогательной оси».

Нажимаем кнопку «Закрыть».

Поработаем над внешним видом комбинированной диаграммы. Выделим область построения и перейдем на вкладку «Конструктор». Поменяем стиль. Удалим легенду (выделить – Delete). Добавим название и подписи вертикальных осей.

Для основной и вспомогательной оси выбираем вариант расположения (отдельно для каждой) и вводим подпись. Жмем Enter.

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

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



Изменение для одного ряда данных типа диаграммы

Создадим смешанную диаграмму путем изменения для одного из рядов типа.

Таблица с исходными данными:

Построим обычную гистограмму на основе двух рядов данных:

Выделим столбики гистограммы, отображающие плановые показатели. На вкладке «Конструктор» в группе «Тип» нажмем кнопку «Изменить тип диаграммы». Выберем из предложенных вариантов «С областями».

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

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

Скачать пример комбинированных диаграмм

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

Создание сводной диаграммы — Служба поддержки Майкрософт

Сводные таблицы

• Создание сводной таблицы для анализа данных листа
  Статья
• Упорядочение полей сводной таблицы с помощью списка полей
  Статья
• Группировка и отмена группировки данных в сводной таблице
  Статья
• Фильтрация данных в сводной таблице
  Статья
• Создание сводной диаграммы
  Статья

Далее: Общий доступ и совместное редактирование

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

Создание сводной диаграммы

1. Выделите ячейку в таблице.

2. Выберите элементы Вставка > Сводная диаграмма .

3. Выберите, где вы хотите, чтобы сводная диаграмма отображалась.

4. org/ListItem»>

   Нажмите кнопку ОК.

5. Выберите поля для отображения в меню.

Данные о расходах семьи	Соответствующая сводная диаграмма

Создание диаграммы на основе сводной таблицы

org/ItemList»>

1. Выделите ячейку в таблице.

2. Выберите элементы Работа со сводными таблицами > Анализ > Сводная диаграмма.

3. Выберите диаграмму.

4. Нажмите кнопку ОК.

Чтобы создать сводную диаграмму на Mac, необходимо сначала создать сводную таблицу, а затем вставить диаграмму.

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

1. Создайте сводную таблицу.  

2. Выберите любую ячейку в сводной таблице.

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

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

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

Чтобы создать сводную диаграмму в Excel в Интернете, вам сначала нужно создать сводную таблицу. Для этого см. раздел «Создание сводной таблицы для анализа данных листа».

1. Выделите ячейку в сводной таблице.

2. На вкладке Вставка выберите Вставить диаграмму из раскрывающегося менюи затем выберите любой вариант диаграммы.

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

   Диаграмма, чтобы изменить диаграмму.

См. также

Добавление названий осей на диаграмму

Изменение подписей осей на диаграмме

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Создание сводной таблицы

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

Использование срезов для фильтрации данных

Создание временной шкалы сводной таблицы для фильтрации дат

Столбчатая диаграмма, отображающая процентное изменение или отклонение

Итог: Узнайте, как создать столбчатую диаграмму, отображающую процентное изменение или отклонение между столбцами.

Уровень мастерства: Средний

Видео №2 – Динамические цветные полосы

Видео №3 – Макро + комбинированная XY точечная диаграмма

Загрузить

Загрузить файл Excel, чтобы следовать или использовать в ваши собственные проекты. В файл добавлен график из видео №2.

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

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

Источник: visualcapitalist.com

Мне нравится, как они отображают различия между годами, и я решил воссоздать это в Excel.

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

Итерации диаграммы

Обновлено 19 ноября 2018 г.

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

Итерация #1

Изначально мы получили отличные отзывы от Конора Фоули и Уэйна Эдмондсона.

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

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

Итерация #2

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

Я создал эту альтернативу на основе предложений Конора и Уэйна в видео на YouTube. Я включил его в пример файла, который вы можете скачать выше на листе «Условный формат». Думаю, это будет самый популярный выбор. Вам НЕ нужно менять положение метки для отрицательных полос для этого решения.

Второе видео выше объясняет больше об этом решении.

Итерация #3

Третье решение пришло от Wayne Edmondson и использует макрос для перемещения меток данных выше/ниже полос положительных/отрицательных ошибок. Макрос также изменяет цвет шрифта меток данных. Этот макрос можно вызывать с событием Worksheet_Change, чтобы обновлять диаграмму каждый раз при изменении исходных данных.

Итерация #4

Четвертое решение пришло от моего хорошего друга и мастера построения графиков, 9 лет.0003 Джон Пельтье . В этом решении используется комбинированная диаграмма с XY Scatter для планок погрешностей.

Это еще одно отличное решение, потому что нам не нужны невидимые полосы ошибок. Вместо этого он использовал невидимую точку данных для разброса по осям XY и шаг 0,5 для данных по оси X. Это помещает точки данных и их вертикальные полосы ошибок между столбцами данных для дохода.

Полную статью Джона об этом решении для комбинированных диаграмм можно прочитать в блоге PeltierTech.

Продолжайте улучшать свои диаграммы

Я показываю все итерации диаграммы, чтобы помочь вам увидеть процесс улучшения наших диаграмм. У нас не всегда все получается идеально с первого раза, когда мы создаем диаграмму, и это нормально!

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

Создание (исходной) диаграммы

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

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

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

Вот шаги для создания диаграммы.

1. Формулы исходных данных

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

Вот краткое объяснение каждой формулы, начинающейся в столбце C:

• Метка года: Используется функция ПОВТОР, чтобы соединить год в столбце А с повторяющимся числом пробелов. Это делает метку длиннее и перемещает текст влево, чтобы выровнять ее с видимой полосой суммы. В противном случае метка будет выровнена по центру видимой и невидимой полос.
• Доход $K: Ссылка на сумму в столбце B с примененным форматированием тысяч ($#,###,K).
• Невидимый бар: Ссылка на сумму следующего периода (года). Это используется для невидимых баров, которые являются верхом/основанием баров погрешностей.
• Разница: Разница между текущим и следующим годом.
• Переменная +: Использует функцию ЕСЛИ для возврата дисперсии, если она положительна. Используется для полос ошибок. Возвращает отрицательное число, чтобы начать с наверх невидимой полосы и вниз .
• Var -: Использует функцию ЕСЛИ для возврата отклонения, если оно отрицательное. Используется для полос ошибок. Возвращает отрицательное число, чтобы начать с нижней полосы ошибок и подняться на вверх .
• Отклонение, %: Используется для меток в невидимом столбце под планками погрешностей.

Я более подробно объясняю каждую из формул в видео выше.

2.

Создайте столбчатую диаграмму

Первым шагом является создание гистограммы:

1. Выберите данные в столбцах C:E, включая строку заголовка.
2. На вкладке «Вставка» выберите столбчатую диаграмму с кластерами из раскрывающегося списка столбцов или гистограмм.
   
3. Диаграмма будет вставлена ​​на лист и должна выглядеть так, как показано на следующем снимке экрана.
   

3. Удалить лишние элементы

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

Вы можете щелкнуть левой кнопкой мыши каждый из следующих элементов и нажать клавишу Delete.

• Легенда
• Горизонтальные линии сетки — необязательно. Вы можете сохранить линии сетки или сделать их более светлыми оттенками серого.

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

4. Добавить планки погрешностей

Панели погрешностей соединяют верхние части столбцов сумм между каждой полосой.

Вот инструкции по вставке полос ошибок:

1. Щелкните левой кнопкой мыши один из столбцов в серии невидимых полос (оранжевые столбцы).
2. В меню «Элементы» выберите «Панели ошибок» > «Дополнительные параметры».
   
3. В области задач отобразятся параметры форматирования полос ошибок.
   
4. Направление должно быть установлено на Оба.
5. Для параметра «Сумма ошибки» выберите переключатель «Пользовательский», затем нажмите кнопку «Указать значение».
6. Выберите диапазоны значений положительной и отрицательной ошибки.
   1. G5:G11 для положительных значений
   2. H5:h21 для отрицательных значений
7. Нажмите OK, чтобы добавить на диаграмму планки погрешностей.

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

5. Изменение перекрытия серий и ширины зазора

Следующим шагом будет расширение полос и удаление перекрытий.

1. Щелкните левой кнопкой мыши на любом столбце суммы (синем), чтобы выбрать всю серию.
2. Щелкните правой кнопкой мыши > Форматировать серию
3. Изменить ширину перекрытия и зазора серии на 0 %.

6. Отформатируйте невидимые столбцы без заливки

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

1. Щелкните левой кнопкой мыши любую оранжевую полосу в невидимой серии, чтобы выбрать всю серию.
2. На вкладке «Формат» выберите «Без заливки» в раскрывающемся меню «Заливка фигуры».

7. Добавить метки данных

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

1. Щелкните левой кнопкой мыши любую из невидимых полос (оранжевых), чтобы выбрать все полосы.
   Примечание. Если у вас возникают проблемы с нажатием на полоски. Выберите диаграмму, перейдите на вкладку «Формат» на ленте и выберите серию «Невидимая полоса» в раскрывающемся списке слева.
2. Выберите «Метки данных» > «Дополнительные параметры» в меню «Элементы»
3. Выберите подменю «Параметры метки» на панели задач «Формат меток данных».
4. Установите флажок Значение из ячеек.
5. Выберите диапазон I5:I11 и нажмите OK.
6. Снимите флажок «Значение» и «Показать линии выноски».
7. Положение метки по умолчанию должно быть установлено на Внешний конец.
8. Для любых отрицательных отклонений выберите каждую метку данных и измените положение на Внутренний конец. Подробности смотрите в видео выше.
   

Теперь метки будут отображаться как для суммы, так и для планок ошибок.

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

Excel 2010 и более ранние версии

Если вы используете Excel 2010 или более раннюю версию, у вас не будет параметра «Значение из ячеек» для меток данных. Однако вы можете использовать бесплатную надстройку XY Labeler от AppsPro для создания меток. Это сэкономит вам много времени. Вот ссылка для загрузки надстройки.

http://www.appspro.com/Utilities/ChartLabeler.htm

Альтернативные решения

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

Ознакомьтесь со статьей «Отклонение в кластеризованном столбце или гистограмме: бюджет и факт».

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

И оформить мой бесплатно  Надстройка для выравнивания диаграмм, помогающая идеально выровнять все элементы на диаграммах.

Заключение

Я думаю, что эта диаграмма является хорошим способом отображения процентного изменения/расхождения между столбцами.

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

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

Спасибо! 😊

График средних значений за месяц в сравнении с текущим годом и информационная панель Excel

Итог: Узнайте, как создать интерактивную диаграмму, отображающую среднемесячные значения в сравнении с текущим годом.

Уровень мастерства: Средний

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

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

Получение данных

Mammoth Mountain публикует свои исторические данные о снегопаде на своем веб-сайте. У них есть таблица, показывающая ежемесячный снегопад (в дюймах) за последние 40 лет. Вот ссылка на веб-страницу.

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

После того, как это настроено в Power Query, вы можете просто нажимать кнопку обновления каждый месяц, чтобы получать новые данные непосредственно с веб-сайта, а также обновлять свои сводные таблицы и диаграммы. Это действительно потрясающе!

Среднемесячная диаграмма по сравнению с текущим периодом

Я не совсем уверен, как назвать эту диаграмму, но мой основной вопрос был… «Каково историческое среднемесячное количество снегопадов по сравнению с месячным количеством снегопадов в этом году?»

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

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

Загрузите файл

Загрузите файл, чтобы следовать дальше.

Настройка исходных данных

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

Я использовал формулу СРЗНАЧЕСЛИМН для расчета среднего количества снегопадов в столбце C.

Формула СРЗНАЧСЛИМН содержит три критерия:

1. Месяц в таблице данных равен месяцу в столбце B.
2. Сезон/год в таблице данных больше или равен начальному сезону.
3. Сезон/Год в таблице данных меньше или равен Конечному Сезону.

Это позволяет нам изменить начальный и конечный сезоны для среднего расчета. Итак, если бы мы хотели увидеть средний снегопад за последние 20 лет, мы могли бы изменить ячейку C3 на 19.95. Или используйте формулу =C4-20.

Вы также заметите, что формула содержит имена таблиц и столбцов (tblData[Month]) вместо адресов ячеек (Data!$B$2:$B$507). Это связано с тем, что выходные данные Power Query находятся в таблице Excel, которую я назвал tblData .

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

Кстати, эту формулу СРЗНАЧЕСЛИМН можно было бы легко вычислить и с помощью сводной таблицы.

Сводная таблица и слайсер для выбранного года

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

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

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

Название серии динамических диаграмм

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

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

Объединение диаграммы и информационной панели

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

Выберите диапазон данных и выберите «Столбчатая диаграмма с кластерами» в меню «Вставить диаграмму». Теперь нам просто нужно очистить форматирование по умолчанию и добавить диаграмму на панель инструментов.

На приборной панели также есть столбчатая диаграмма, отображающая тенденцию сезонных снегопадов с 1970 по 2015 год. Эта диаграмма на самом деле содержит два ряда данных для отображения выбранного года оранжевым цветом. Я объясню больше об использовании вторичной оси в этой статье о каскадных диаграммах.

На следующем скриншоте показана панель управления в действии.

 Анализ данных

Что ж, сезон 2014–2015 годов был одним из худших в истории снегопада в Калифорнии. Наши зимние месяцы (январь, февраль, март) были чрезвычайно низкими по сравнению со средним показателем. Но мы с оптимизмом смотрим на то, что он восстановится, как это было в 2007-2011 годах (рекордно высокий год).