В этом практическом уроке рассказывается о том, что такое разворотная диаграмма, как ее создать и настроить. Мы также увидим разницу между разворотной диаграммой и таблицей:
Диаграммы считаются одним из лучших способов представления отчета. Они помогают нам понять и проанализировать данные более простым способом. Диаграммы Pivot в Excel дают нам визуальное представление данных различными способами.
В этом учебнике мы узнаем все подробности, необходимые для работы с поворотными диаграммами в Excel: создание диаграмм различных типов, форматирование их макета, добавление фильтров, добавление пользовательских формул, а также использование формата одной диаграммы для другой диаграммы, принадлежащей различным поворотным таблицам.
Что такое поворотная диаграмма в Excel
Разворотная диаграмма в Excel - это визуальное представление данных. Она дает общую картину исходных данных. Она позволяет анализировать данные с помощью различных типов графиков и макетов. Считается, что это лучшая диаграмма во время бизнес-презентации, включающей огромные данные.
Развернутая диаграмма в сравнении с таблицей
Поворотная таблица позволяет обобщить большие данные в виде матрицы. Вы можете выбрать поля, которые вы хотите использовать в таблице для строк и столбцов. Диаграмма pivot предоставляет нам графическое представление таблицы pivot. Вы можете выбрать несколько макетов и типов диаграмм.
Эта диаграмма также обобщает данные. Вы можете создать как поворотную таблицу, так и диаграмму для источника данных и работать с ними одновременно. Это означает, что изменения, сделанные в поворотной таблице, отразятся на диаграмме, и наоборот.
Источник данных
Ниже приведен образец источника данных, который будет использоваться в этом учебнике. Нажмите на ссылку, чтобы загрузить образец диаграммы Sample_Data Pivot Chart
| Идентификатор заказа | Дата заказа | Название продукта | Регион | Город | Количество | Общая цена |
|---|---|---|---|---|---|---|
| 1 | 03-01-2020 | Обычное печенье | Север | Нью-Йорк | 33 | 444.66 |
| 2 | 04-02-2012 | Сахарное печенье | Юг | Лима | 432 | 346.33 |
| 3 | 05-04-2018 | Пластины | Восток | Бостон | 33 | 32.54 |
| 4 | 06-05-2019 | Шоколад | Запад | Дубовая земля | 245 | 543.43 |
| 5 | 07-07-2020 | Мороженое | Север | Чикаго | 324 | 223.56 |
| 7 | 09-09-2020 | Обычное печенье | Восток | Вашингтон | 32 | 34.4 |
| 8 | 10-11-2020 | Сахарное печенье | Запад | Сиэтл | 12 | 56.54 |
| 9 | 11-12-2017 | Пластины | Север | Торонто | 323 | 878.54 |
| 10 | 12-14-2020 | Шоколад | Юг | Лима | 232 | 864.74 |
| 11 | 01-15-2020 | Мороженое | Восток | Бостон | 445 | 457.54 |
| 13 | 03-18-2018 | Соленое печенье | Север | Нью-Йорк | 5454 | 34546 |
| 14 | 04-18-2017 | Сырное печенье | Юг | Лима | 5653 | 3456.34 |
| 15 | 05-19-2016 | Соленое печенье | Восток | Вашингтон | 4 | 74.4 |
| 16 | 06-20-2015 | Сырное печенье | Запад | Дубовая земля | 545 | 876.67 |
Создание разворотной диаграммы
Существует два способа создания разворотной диаграммы в Excel.
#1) Создать из источника данных
Мы можем создать график непосредственно из таблицы данных без использования таблицы pivot.
Для этого выполните следующие действия.
#1) Выберите любую ячейку в таблице.
#2) Перейти к Вставка -> Поворотная диаграмма
#3) Вы можете выбрать создание нового листа или упомянуть диапазон таблиц, в котором вы хотите разместить диаграмму, в разделе Существующий рабочий лист.
#4) Нажмите OK
Это создаст пустую диаграмму pivot и связанную с ней таблицу pivot. Вы можете добавить нужные поля для создания отчета и диаграммы.
#2) Создать из PivotTable
Если вы уже создали поворотную таблицу, вы можете использовать ее для создания поворотной диаграммы. Мы создали образец PivotTable, как показано ниже.
Чтобы создать график.
#1) Выберите любую ячейку в PivotTable.
#2) Перейти к Insert-> Разворотная диаграмма
#3) Появится список доступных графиков, выберите нужный график.
#4) Нажмите OK.
Это создаст диаграмму с данными, взятыми из таблицы pivot. Пример диаграммы pivot показан ниже.
Примечание: В качестве альтернативы можно использовать клавишу быстрого доступа F11. Щелкните на поворотной таблице и нажмите F11 на клавиатуре.
Настройка диаграммы
Вы можете настроить график с помощью значка + и значка краски, который находится справа от графика.
Кнопка + - позволяет добавлять или удалять элементы графика, такие как заголовки, линии сетки, легенды и т.д., а также определять их положение.
Вы можете добавить название графика, упомянуть названия осей и т.д. В качестве примера мы добавили название графика и название оси.
Стиль графика - Вы можете изменить стиль и цвет графика, нажав на значок кисти.
Вы также можете изменить цвет графика по своему желанию в разделе "Цвет".
Рекомендуемые диаграммы
Excel предоставляет нам рекомендуемые Pivot-диаграммы, которые позволяют быстро выбрать тип PivotChart, отвечающий требованиям вашего бизнеса.
#1) Выберите таблицу источника данных.
#2) Перейти к Вставка -> Рекомендуемые диаграммы .
#3) Нажмите кнопку Рекомендуемые диаграммы.
#4) Нажмите на нужный вам график.
#5) Нажмите OK
Результирующие поворотная таблица и диаграмма будут созданы на новом листе, и вы сможете настроить их по своему усмотрению.
Поля разворотной диаграммы
Он имеет 4 поля, как показано ниже.
1. Фильтры: Поля под ним дают нам возможность добавлять фильтры отчетов.
2. Легенды (серия) : Поля под этим представляют собой заголовки столбцов в сводной таблице.
3. Ось (категории): Это представляет строки в таблице Pivot Table. Эти поля отображаются на панели осей на графике.
4. Значения: Используется для отображения суммированных числовых значений.
Инструменты для построения разворотных диаграмм
Анализируйте: Существуют различные варианты, позволяющие сделать график более удобным для пользователя.
Название чарта: Это название диаграммы. Оно используется при написании кода VBA, а также находится в панели выбора. Оно доступно в Excel 2010 и более поздних версиях.
Опции: Появится диалоговое окно "Параметры PivotTable", в котором можно задать макет и формат, установить показ/скрытие общего итога, задать параметры сортировки, параметры отображения и т.д.
Активное поле: Вы можете изменить имя столбца в таблице. Например , От общей суммы до конечной суммы и т.д., и то же самое будет обновлено в таблице и диаграмме.
Расширить поле: Это используется для автоматического расширения всех значений.
Если у вас есть несколько полей, таких как годы, кварталы и даты, то вместо того, чтобы разворачивать их по отдельности, вы можете нажать на кнопку Развернуть поле.
Поле коллапса: Это противоположно Развернуть поле. Это свернет развернутые поля и представит компактную диаграмму.
Развернуть пример
Пример коллапса
Примечание: Предположим, у вас есть только одно поле в Строках, тогда, нажав на кнопку Развернуть поле, вы получите диалог со всеми полями и сможете выбрать нужное поле. Выбранное поле будет добавлено в раздел Строки, а диаграмма будет обновлена автоматически.
Вставной ломтерез
Вы можете вставить слайсер в диаграмму так же, как и в таблицу pivot.
Чтобы интегрировать слайсер с графиком, выполните следующие действия.
- Щелкните на поворотном графике.
- Перейти к Вкладка "Анализ" -> Вставить ломтик .
- В диалоговом окне Выбрать поля необходимо создать слайсеры.
- Нажмите OK
В результате появится окно слайсера, как показано ниже. Мы уже видели, как использовать слайсер в нашем предыдущем уроке.
Вставить временную шкалу
Временную шкалу можно вставить в график так же, как и сводную таблицу.
Чтобы интегрировать Timeline с графиком, выполните следующие действия.
- Щелкните на поворотном графике.
- Перейти к Вкладка "Анализ" -> Вставка временной шкалы.
- Выберите нужное поле Дата.
- Нажмите OK
Это позволит вставить временную шкалу, как показано ниже. Мы видели, как использовать временную шкалу в нашем предыдущем уроке.
Результат, основанный на временной шкале, обновляется как в таблице Pivot, так и на графике.
Подключение фильтра
Вы можете соединить слайсер или временную шкалу к нескольким поворотным диаграммам. Например, Мы создали 2 таблицы Pivot и 1 слайсер. Вы применяете слайсер к обоим графикам.
- Щелкните на поворотном графике, к которому в данный момент не подключен слайсер.
- Перейти к Анализ -> Фильтр соединения
- Выберите ломтерезку, которую необходимо подключить.
- Нажмите Ok
Теперь вы можете работать с обоими графиками с помощью одного слайсера.
Расчеты
Если вы хотите добавить какие-либо пользовательские формулы, вы можете сделать это с помощью поля расчета.
Пример:
#1) Выберите диаграмму Pivot, к которой нужно добавить пользовательские формулы.
#2) Перейти к Анализировать -> Поля ->Элементы -> Наборы
#3) Выберите Calculated Fields (Вычисляемые поля).
#4) В поле Имя введите желаемое имя.
#5) Если вы предоставляете скидку 10% от общей суммы, то вы можете добавить формулу, как показано ниже.
#6) Сводная таблица, сводные поля и график будут обновлены соответствующим образом.
Обновить
Каждый раз, когда вы изменяете значения в источнике данных, щелкните в любом месте поворотной диаграммы, щелкните правой кнопкой мыши и выберите Обновить или перейдите в раздел Анализ -> Обновить. Обновление поворотной таблицы также приведет к обновлению диаграммы.
Изменить источник данных
При добавлении дополнительных строк в источник данных диаграмма не будет воспринимать добавленные строки, поскольку мы определили диапазон при создании диаграммы.
Чтобы включить новые строки:
- Щелкните в любом месте разворотной диаграммы.
- Перейти к Анализировать -> Изменить источник данных
- Появится диалог Change PivotTable Data Source, в котором можно ввести новый диапазон данных.
- Нажмите Ok
Убедитесь, что вы выполнили описанные выше действия для всех графиков по отдельности.
Очистить
С помощью Очистить можно очистить всю диаграмму Pivot Chart. Это будет пустая диаграмма и таблица.
- Щелкните на разворотной диаграмме
- Анализировать -> Очистить -> Очистить все
Вы также можете очистить все примененные фильтры с помощью Анализировать -> Очистить-> Очистить фильтры
Диаграмма перемещений
После создания графика его можно переместить в нужное место.
Выполните следующие действия:
- Щелкните на поворотном графике.
- Перейти к Анализировать -> График движения
- Выберите нужную опцию в диалоговом окне:
- Новый лист: Лист будет создан автоматически, и на нем появится график.
- Объект в: Вы можете выбрать один из доступных листов, и график будет перемещен на выбранный лист.
Список полей: Вы можете показать/скрыть панель "Поля PivotChart".
Кнопки полей: Вы можете показать/скрыть поле легенды, поле оси, поле значения, фильтр отчета и т.д. на графике.
Дизайн
На этой вкладке доступно несколько вариантов оформления графика.
Добавить элемент диаграммы: Это дает нам те же опции, которые мы получили, когда нажали на кнопку + рядом с поворотной диаграммой. Они помогают нам добавить элементы к диаграмме, такие как заголовок, бард ошибок и т.д.
Быстрый макет: Вы можете изменить макет по умолчанию и выбрать один из доступных предопределенных макетов. Например, мы переместили макет региона на верхнюю, а не на правую сторону.
Меняйте цвета: Выберите различные цвета для вашей диаграммы.
Стиль диаграммы: Выберите стиль для своего графика из имеющихся.
Переключить строку/столбец: Вы можете легко переключать строки и столбцы одним щелчком мыши, и поворотная таблица и график будут обновлены автоматически.
Перед переключением строки/столбца
После переключения строки/столбца
Выберите Данные: Предположим, вы потратили много времени на форматирование сводной диаграммы в соответствии со стандартами вашей компании, и все ваши диаграммы должны быть в одном формате. Тогда эта опция пригодится. Вы не можете напрямую скопировать сводную диаграмму и изменить источник данных. Необходимо выполнить несколько шагов.
#1) Выберите нужный поворотный график и скопируйте область графика.
#2) Откройте новую рабочую книгу. Файл -> Новая рабочая тетрадь
#3) Вставьте скопированный график. Вы можете заметить, что в строке меню написано Chart Tools, а не PivotChart Tools.
#4) Теперь выделите область диаграммы и нажмите кнопку Вырезать.
#5) Перейдите в рабочую книгу, в которой вы хотите использовать эту диаграмму.
#6) Примечание: У вас уже должна быть создана сводная таблица.
#7) Вставьте диаграмму из шага 4.
#8) Перейдите в раздел Design present (Дизайн) в разделе Chart Tools (Инструменты диаграммы). Нажмите на вкладку Select Data (Выбор данных).
#9) Щелкните любую ячейку в сводной таблице.
Pivot Chart будет создана с данными, присутствующими в новой поворотной таблице, но формат останется таким же, как и раньше. Вы можете изменить ось и легенду, как это необходимо для новой таблицы.
Результирующий график для новой поворотной таблицы показан ниже.
Изменить тип диаграммы: Вы можете изменить тип столбчатой диаграммы по умолчанию на нужный тип, как показано ниже.
График будет автоматически обновляться в зависимости от выбора.
Круговая диаграмма
Гистограмма
Формат
В основном они используются для пользовательского форматирования текста внутри графика.
Текущий выбор: Это покажет все элементы, присутствующие в таблице, и вы можете выбрать тот, в котором вы хотите изменить стиль формата. Например, Выберем "Заголовок диаграммы" и изменим его стиль.
#1) Выберите "Название диаграммы" из выпадающего списка.
#2) Нажмите кнопку Форматировать выделение.
#3) Формат Заголовок диаграммы откроется на правой панели.
#4) Выберите цвет, стиль, границы и т.д. по своему желанию.
После нескольких основных форматирований заголовок диаграммы будет выглядеть следующим образом.
Сброс для соответствия стилю: Это приведет к сбросу всех изменений и установлению стиля по умолчанию.
Вставить фигуры: Вы можете вставлять фигуры, такие как линии, стрелки, а также текстовое поле для лучшего объяснения.
Стиль формы: Вы можете выбрать различные стили для области участка. Выберите область, стиль которой необходимо изменить, и нажмите на стиль.
После применения стилей ко всей диаграмме столбцы и строки выглядят следующим образом.
Организуйте: Если имеется несколько поворотных графиков и они накладываются друг на друга на этих опциях.
Вперед
- Выберите график, который вы хотите вывести на экран.
- Нажмите на опцию Bring Forward, чтобы перенести график на один шаг вперед.
Принесите на фронт: Эта опция выведет ваш график выше всех остальных графиков.
Отправить назад
- Выберите график, который вы хотите отправить обратно.
- Нажмите на опцию "Отправить назад", чтобы отправить график на один уровень назад.
Отправить на задний план: Это используется для передачи выбранного графика всем остальным графикам.
Панель выбора
Вы можете определить видимость графика с помощью панели выбора. На этой странице отображаются все доступные графики и срезы, и вы можете нажать на значок глаза, чтобы решить, должен ли данный элемент быть видимым на рабочем листе или нет.
Размер: Используется для настройки высоты, ширины, высоты шкалы, ширины шкалы и т.д. разворотной диаграммы.
Часто задаваемые вопросы
Вопрос №1) Как создать разворотную диаграмму в Excel?
Ответ: Существует два способа создания разворотных диаграмм.
#1) Создать из источника данных
- Выберите любую ячейку в таблице источника данных.
- Перейти к Вставка -> Поворотная диаграмма
- Выберите диапазон.
Это создаст пустую таблицу и диаграмму pivot.
#2) Создать из PivotTable
Если у вас уже есть сводная таблица:
- Выберите любую ячейку в сводной таблице.
- Перейти к Вставка -> Поворотная диаграмма
- Появится список доступных графиков, выберите нужный график.
Это позволит создать график с данными, относящимися к таблице pivot.
Вопрос № 2) Для чего в Excel используется разворотная диаграмма?
Ответ:
Существует множество преимуществ использования разворотных диаграмм:
- Это эффективный и простой способ графического представления данных.
- Вы можете легко обобщить данные, перетащив нужные поля в любой из 4 доступных разделов таблицы.
- Предоставляет эффективный способ изменения необработанных данных в организованный формат путем манипулирования ими с помощью простой фильтрации, выравнивания, настройки, вычислений и т.д.
Q #3) Как отформатировать разворотную диаграмму?
Ответ: Вы можете отформатировать диаграмму с помощью различных опций, представленных в разделе Инструменты поворотной диаграммы. Он предоставляет вам возможность добавить новые поля, изменить цвет, шрифт, фон и т.д., чтобы ваша диаграмма выглядела более интерактивно и презентабельно. Щелкните в любом месте поворотной диаграммы, чтобы открыть раздел Инструменты.
Вопрос # 4) Можно ли добавить слайсер к диаграммам Pivot?
Ответ: Да, слайсеры и временные шкалы могут быть добавлены в сводные диаграммы. Это поможет нам одновременно фильтровать и диаграмму, и связанную с ней сводную таблицу.
- Щелкните на поворотном графике.
- Перейти к Вкладка "Анализ" -> Вставить ломтик .
- В диалоговом окне Выберите поля для создания слайсеров.
- Нажмите OK
Затем можно добавить соединение Filter, чтобы связать один слайсер с несколькими графиками.
Заключение
В этом уроке мы узнали о диаграммах разворота Excel. Это визуальное представление таблицы разворота или источника данных. Оно помогает нам просматривать сводные данные в графическом формате с помощью различных типов диаграмм.
Имеется множество возможностей для фильтрации, форматирования, настройки диаграмм и добавления различных макетов по вашему желанию. Разворотная диаграмма в Excel полезна при работе с огромным количеством данных. Она очень полезна во время бизнес-презентации благодаря фильтрации одним щелчком мыши, фильтрации по времени, настраиваемым вычислениям и т.д.