Сводная диаграмма в excel как сделать
Здравствуйте, друзья. Этот короткий пост я хочу посвятить сводным диаграммам 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%.
Итак, все продажи на диаграмме отсортированы в порядке убывания, показано соотношение валовой прибыли и выручки.
Читайте также: