Диаграмма по сводной таблице excel. Сводные диаграммы

Тема занятия: Сводные таблицы и диаграммы.

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

План занятия :

Создание сводной таблицы. Настройка макета сводной таблицы. Обновление данных в сводной таблице. Изменение формата полей в сводной таблице. Создание сводных диаграмм. Создание сводной таблицы.

Создайте новую рабочую книгу.

Переименуйте лист1 в Продажи.

На листе Продажи Создайте таблицу следующего вида:

Подсчитайте чему равна сумма продаж. Сумма продаж = Цена*Количество.

Выделите любую ячейку входящую в диапазон исходной таблицы.

Выполните Данные→Сводная таблица.

В первом окне Мастер сводных таблиц Нажмите Далее.

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

В следующем окне укажите поместить таблицу на новый лист и нажмите Готово.

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

Настройка макета сводной таблицы.

Перейдите на лист Анализ. И выделите одну из ячеек макета сводной таблицы.

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

С помощью мышки перетащите в область Строк Поле Наименование, В область столбцов поле Месяц, В область Элементы данных Перетащите поле Сумма продаж.

После выполнения вышеуказанных действий таблица должна выглядеть так:

Обновление данных в сводной таблице.

При обновлении данных в исходной таблице, данные в сводной таблице автоматически не обновляются.

Перейдите на лист Продажи И измените значение ячейки с какой либо ценой на 55555.

Перейдите на лист Анализ.

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

На панели инструментов Сводные таблицы Нажмите кнопку обновить поле

Проверьте обновились ли данные.

Примечание: Для изменения формата отчета сводной таблицы нажмите на кнопку находящуюся на панели инструментов Сводные таблицы.

Изменение формата полей в сводной таблице.

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

Щелкните по ней правой кнопкой мыши и в контекстном меню выберите Параметры поля.

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

Нажмите кнопку Формат.

В окне Формат ячеек Укажите денежный формат. Нажмите ОК.

Вернувшись в окно Вычисление поля сводной таблицы нажмите ОК.

Задание: В область строк добавьте поле Цена И установите Операция - Сумма;

Формат – денежный.

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

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

На панели инструментов Сводные таблицы Нажмите значок Мастер диаграмм.

Excel автоматически создает новый лист с уже готовой сводной диаграммой.

Перейдите на лист с сводной диаграммой.

На панели инструментов Диаграммы Нажмите кнопку Тип диаграммы.

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

Контрольные вопросы:

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

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

В этой статье мы рассмотрим методы создания и использования сводной диаграммы.

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

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

Для создания сводной таблицы на основании данных уже готовой диаграммы, выполните следующие действия:

1. Выберите необходимую Вам сводную таблицу, кликнув по ней;

2. На вкладке Вставка в группе Диаграммы выберите необходимый тип диаграммы.

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

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

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

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

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

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

1. Выделить нужный нам диапазон данных (или установить курсор на нужную нам таблицу - тогда Excel автоматически подставит всю таблицу в диапазон данных);

2. На вкладке Вставка в группе Таблицы выбрать раздел Сводная таблица , а затем пункт Сводная диаграмма .

Дата: 16 марта 2017 Категория:

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

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

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

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

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

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

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

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

В следующей статье ждите очень важную тему – . Если Вы с ним еще не знакомы – прочтите, не пожалеете. Я, например, пользуюсь им почти каждый день.

Как всегда, жду Ваших вопросов и комментариев!

Диаграммы

Диаграммы используются для представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов данных и взаимосвязей между различными рядами данных. Для построения графика (диаграммы) на основе имеющихся данных необходимо: а) Выбрать данные, которые будут участвовать в построении диаграммы (выбирать ячейки, которые будут названиями рядов и подписями по оси X не нужно) как показано на рис. 115; б) на вкладке ленты "Вставка" нажать на любой из представленных видов диаграмм в группе "Диаграммы" (рис. 116). После этого на листе появится диаграмма, построенная по выбранным данным (рис. 117).

Элементами диаграммы являются:

Область диаграммы (1);

Область построения диаграммы (2);

Элементы данных в рядах данных, которые используются для построения диаграммы (3);

Горизонтальная (ось категорий) и вертикальная (ось значений) оси, по которым выполняется построение диаграммы (4);

Легенда диаграммы (5);

Название диаграммы и названия осей (6);

По умолчанию название диаграммы и подписи осей не отображаются на диаграмме. Для их отображения нужно выделить диаграмму, после чего на вкладке ленты "Конструктор" в группе "Макеты диаграмм" выбрать макет, содержащий те или иные подписи (рис. 118).

Для изменения названий рядов и подписей по горизонтальной оси (оси категорий) нужно нажать правой кнопкой мыши на области построения диаграммы и в открывшемся меню выбрать пункт "Выбрать данные…". После этого откроется окно выбора данных "Выбор источника данных". В левой части этого окна можно изменить названия рядов. Для этого выбрать нужный ряд и нажать кнопку "Изменить" в левой части окна. В открывшемся окне "Изменение ряда" (рис. 119) в поле имя ряда ввести требуемое название ряда, или выделить ячейку, содержащую название ряда. В нашем случае - это ячейка A2 (исходные данные представлены на рис. 115). Для изменения подписей по горизонтальной оси (оси категорий) нужно нажать кнопку "Изменить" в правой части окна "Выбор источника данных" и в открывшемся окне "Подписи оси" выбрать диапазон, содержащий подписи. В нашем случае - это ячейки с B1 по F1 (исходные данные представлены на рис. 115).

Для изменения типа диаграммы после ее построения достаточно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт "Изменить тип диаграммы…". Альтернативный вариант: Выделить диаграмму и на ленте, на вкладке "Конструктор" нажать на кнопку "Изменить тип диаграммы".

Для перемещения диаграммы на отдельный лист нужно нажать правой кнопкой мыши на диаграмме и выбрать в открывшемся меню пункт "Переместить диаграмму…". В открывшемся окне "Перемещение диаграммы" выбрать пункт "На отдельном листе", при желании изменить название этого листа и нажать кнопку "Ок" (рис. 122).

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

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

В приведенном ниже примере за основу взята таблица, содержащая расходы на разные типы канцтоваров у разных отделов за период с 2009 по 2013 годы.

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

Важно: Выбранный диапазон не должен содержать пустых строк или пустых ячеек в первой строке.

На рис. 123 показаны исходные данные, по которым будет создана сводная таблица. Для создания сводной таблицы нужно выделить диапазон данных (в данном случае это диапазон A1:G10). После этого на вкладке ленты "Вставка" нужно нажать на кнопку "Сводная таблица" (рис. 124)

Будет создана пустая сводная таблица и открыт "Список полей сводной таблицы" (рис.126). В верхней части этого списка расположены все доступные поля сводной таблицы, а в нижней - макет таблицы, в который эти поля можно добавлять. Для добавления поля в макет таблицы нужно нажать левой кнопкой мыши на названии поля в списке полей, и перетащить в соответствующий контейнер макета. Второй вариант: нажать правой кнопкой мыши на названии нужного поля в списке полей и в открывшемся меню выбрать команду " Добавить в фильтр отчета", " Добавить в названия столбцов", " Добавить в названия строк"или "Добавить в значения". Логично добавлять поля, содержащие текстовые значения или даты в контейнеры "Названия строк" или "Названия столбцов", а числовые данные - в "Значения".

Пример №1. Если нужно отобразить в сводной таблице затраты каждого отдела на канцтовары по годам, то в контейнер "Названия строк" нужно добавить поле "Номер отдела", в контейнер "Названия столбцов" - поле "Год" (или наоборот - от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер "Значения" нужно добавить поле "Сумма затрат" (рис. 127). В результате получится сводная таблица, представленная на рис. 128.

Рис. 128

Пример №2. Если нужно отобразить в сводной таблице общие затраты каждого отдела на каждый вид товара, то в контейнер "Названия строк" нужно добавить поле "Номер отдела", в контейнер "Названия столбцов" - поле "Наименование товара" (или наоборот - от этого зависит только то, что будет отображаться в названиях строк или столбцов соответственно). В контейнер "Значения" нужно добавить поле "Сумма затрат" (рис. 129). В результате получится сводная таблица, представленная на рис. 130.

Тип операции, применяющейся в контейнере "Значения" может быть не только суммой. Для выбора типа операции необходимо нажать левой кнопкой мыши на стрелку в названии поля в контейнере "Значения" и в открывшемся меню выбрать пункт "Параметры полей значений…" (рис. 131). В открывшемся окне "Параметры поля значений" выбрать нужный тип операции и нажать "Ок" (рис. 132)

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

Как видно из рисунка 134, из исходных данных были выбраны все данные, номер отдела которых равен "Отдел 2" (название строки в сводной таблице) и наименование товара равно "Карандаши" (название столбца в сводной таблице).

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

Процесс создания сводной диаграммы практически полностью повторяет процесс создания сводной таблицы. Вначале выбирается диапазон данных, по которым будет построена сводная диаграмма, потом на вкладке ленты "Вставка" нужно нажать на стрелку в углу кнопки "Сводная таблица" и выбрать пункт "Сводная диаграмма" (рис. 135). В диалоговом окне " Создать сводной таблицы и сводной диаграммы"выбрать вариант " Выбрать таблицу или диапазон"и проверить правильность диапазона ячеек в поле " Таблица или диапазон". Также в зависимости от того, куда следует поместить создаваемую таблицу нужно выбрать "На новый лист" или "На существующий лист" и нажать кнопку "Ок" (рис. 136).

Будет создана пустая сводная таблица и пустая сводная диаграмма, а также открыт "Список полей сводной таблицы". Единственное отличие этого списка от аналогичного при создании сводной таблицы - это названия контейнеров в макете. Так, вместо контейнера "Названия строк" теперь "Поля осей (категорий)", а вместо контейнера Названия столбцов" теперь "Поля легенды (ряды)" (рис.137). Остальной функционал остался прежним: названия полей добавляются в контейнеры макета и на основании их формируются сводная таблица и диаграмма.

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

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

Если в один контейнер (например, "Поля осей (категорий)") добавить оба поля "Год" и "Номер отдела", то в результате получится следующая сводная таблица (рис. 141) и сводная диаграмма (рис. 142).

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

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

Как и сводная таблица, она имеет поле фильтра - "Страна" (Country), которое отображается в левом верхнем углу экрана, поля строк и столбцов, которые здесь отображаются справа и снизу. Эта сводная диаграмма тесно связана с таблицей. Если вы переключитесь в режим сводной таблицы и измените ее структуру, это изменение будет отображено и на сводной диаграмме, и наоборот, если сейчас изменить структуру сводной диаграммы, то это изменение появится и на сводной таблице, когда вы вновь переключитесь в тот режим.

Рис. 8.50.

Но можно создать диаграмму и непосредственно на базе запроса или таблицы. При этом одновременно будет создаваться и сводная таблица. Сводная таблица и сводная диаграмма - это две формы представления одних и тех же данных.

В качестве примера предлагается построить сводную диаграмму для запроса "Продажи по сотрудникам и странам" (Employee Sales by Country).

Откройте этот запрос в режиме Конструктора.
Запрос имеет два параметра: [Начальная дата] и [Конечная дата], которые используются для фильтрации данных. Для сводной диаграммы эти параметры не нужны, поэтому сначала удалите выражение из строки Условие отбора (Criteria), затем откройте диалоговое окно Параметры (Query Parameters) (см. разд. "Запросы с параметрами" гл. 4) и удалите оба параметра.
Щелкните по стрелке на кнопке Вид (View) панели инструментов и выберите из меню пункт Сводная диаграмма (PivotChart View). Появится окно, основную часть которого занимает область отображения диаграммы (рис. 8.51), ограниченная осями координат и размеченная линиями сетки. Кроме этого, видны область фильтра, которая играет ту же роль и расположена так же, как и в сводной таблице, область категорий и область рядов, которые соответствуют строкам и столбцам сводной таблицы. В область категорий переносятся поля, значения которых должны откладываться по оси X (горизонтальной), а в область рядов - поля, каждое значение которых соответствует одной серии точек или столбцов на диаграмме (в зависимости от типа диаграммы). Эти поля соответствуют полям столбцов на сводной диаграмме. В область данных помещаются поля, значения которых будут отображаться по оси Y (вертикальной) диаграммы.

Рис. 8.51.

Перетащите из списка полей в область фильтра поле "Страна" (Country), в область категорий - поля "Фамилия" (Last Name) и "Имя" (First Name), в область рядов - поле "Дата исполнения по месяцам" (Shipped Date By Month). Следите, как будет меняться область диаграммы.[ Если вы не видите диалоговое окно со списком полей, щелкните по кнопке Список полей (Fields List) панели инструментов. ]
Перенесите поле "СуммаПродаж" (Sale Amount) в область данных - и диаграмма готова. Нажмите кнопку Добавить легенду (Show Legend), чтобы отобразить легенду, после чего вы получите диаграмму, представленную на рис. 8.52.
Можно еще ввести надписи у осей диаграммы. Щелкните по надписи Название оси (Axis Title) под осью X. Выведите на экран окно Свойства (Properties) и раскройте вкладку Формат (Format). Введите в поле Заголовок (Caption): Сотрудники. Аналогично введите надпись Объем продаж для оси Y.