Сводная диаграмма в excel как сделать

Добавил пользователь Алексей Ф.
Обновлено: 06.09.2024

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

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

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

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

Строим сводную таблицу, которая будет источником данных для диаграммы
Выделяем любую ячейку таблицы и жмем на ленте: Работа со сводными таблицами – Анализ – Сервис – Сводная диаграмма
В открывшемся окне выбираем тип диаграммы и нажимаем Ок
При необходимости, настраиваем внешний вид диаграммы

Кстати, если у Вас версия Microsoft Office 2013 и выше, первый пункт можно пропустить. Просто нажмите на ленте Вставка – Диаграммы – Сводная диаграмма . Процесс создания будет напоминать компоновку сводной таблицы, однако, таблица не будет отображена. В более ранних версиях, все же, придется предварительно строить сводную таблицу.

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

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

Как создать сводную таблицу в Excel

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

Процедура создания сводной таблицы следующая:

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

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

Читать: Как изменить цвет вкладки листа Excel.

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

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

Выберите диапазон ячеек в Сводная таблица.

Идти к Вставлять и в разделе для Диаграммы, Выбрать Сводная диаграмма.

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

wikiHow работает по принципу вики, а это значит, что многие наши статьи написаны несколькими авторами. При создании этой статьи над ее редактированием и улучшением работали авторы-волонтеры.

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

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

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

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

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

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

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

В статье подробно написано о создании такого отчета, как на рисунке ниже.

Прежде чем начать читать, скачайте отчет и данные.

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

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

Итак, нам нужен отчет в виде таблицы с такими столбцами:

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

Проанализируем продажи, представив все показатели отчета в понятном виде и на одном листе.

Структура отчета

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

Элементы управления

Чтобы управлять данными отчета, сверху разместим элементы управления – срезы и временную шкалу.

Карточки с показателями

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

Графики и диаграммы

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

Сводная таблица с деталями

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

Итак, в интерактивном отчете четыре блока:

А теперь подробно, по шагам. Начнем создавать отчет со сводной таблицы.

Сводная таблица

Если вы раньше никогда не создавали сводные таблицы в Excel, то прочитать об этом можно здесь: “Как построить сводную таблицу в Excel”.

Откройте отчет о валовой прибыли в Excel и удалите из него сроку с итогами – мы рассчитаем их с помощью сводных таблиц. Еще лучше будет, если использовать форматированные (smart – умные) таблицы Excel. Для этого выделите мышкой любую ячейку отчета и перейдите в меню Главная -> Форматировать как таблицу -> выберите любой понравившийся вид таблицы.

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

Отформатируйте сводную таблицу. Щелкните правой кнопкой мышки по любой ячейке с цифрами таблицы, в появившемся меню выберите пункты:

После всех манипуляций должна получиться такая таблица:

Сводные диаграммы и графики

Кстати, из Power Pivot сводные диаграммы можно добавить без сводных таблиц.

Всего в отчете будет пять графиков и диаграмм:

1. График с динамикой продаж по периодам

Измените тип диаграммы: щелкните по графику мышкой и перейдите на вкладку Конструктор -> Изменить тип диаграммы -> выберите График.

После выполнения этих шагов должен получиться такой график:

Кстати, чтобы подписи в оси Y были с разделителями разрядов, нужно задать числовой формат в самой сводной таблице.

2. Гистограмма продаж по покупателям

Создаем следующий график. Вставьте сводную диаграмму: выделите отчет с данными из 1С (или любую его ячейку), перейдите в меню Вставка -> Сводная диаграмма. На самом деле, способов добавить сводную диаграмму несколько. Мы с вами рассматриваем самый очевидный – аналогичный добавлению простой (не сводной) диаграммы.

Отформатируйте цифры в таблице — задайте числовой формат (выделить ячейки правой кнопкой мышки -> Числовой формат). Должна получиться такая диаграмма:

Задайте параметры ряда – перекрытие столбцов 100%, боковой зазор 40%.

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

Читайте также: