НТВ плюс 

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

СМОТРЕТЬ УКРАИНОЯЗЫЧНЫЙ ВАРИАНТ СТАТЬИ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel создаст новую сводную таблицу и сводную диаграмму:

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

В начало страницы

В начало страницы

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

Как создать сводную диаграмму в Excel из нескольких таблиц

На разных листах одной книги находятся отчеты по продажам в нескольких магазинах:

Они имеют одинаковую структуру и одинаковые способы представления данных.

Прежде чем делать сводную диаграмму в Экселе, добавим на панель быстрого доступа кнопку «Мастера сводных таблиц и диаграмм». Через «Офис» заходим в «Параметры Excel» - «Настройка». Выбираем «Все команды». Находим инструмент «Мастер сводных таблиц и диаграмм». Жмем «Добавить».

На панели появится такой значок:

Переходим на новый лист. Вызываем «Мастера»:

  1. Первый шаг. Указываем, что будем создавать «сводную диаграмму со сводной таблицей» на основе данных, которые находятся «в нескольких диапазонах консолидации».
  2. Второй шаг. Меняем настройку по умолчанию – «создать поля страницы». Если оставить параметр «создать одно поле страницы», данные отобразятся путано.
  3. Третий шаг. Формируем список диапазонов, на основании которых будет построена сводная диаграмма. Сначала переходим на лист с первой таблицей – выделяем ее – жмем добавить. Переходим на лист со второй таблицей – повторяем все те же процедуры. Указываем количество полей – 1. Становится активным окошко «Первое поле». Даем ему название – «2013». Повторяем эту же операцию для каждого диапазона – названия «2014», «2015».
  4. Четвертый шаг. Переходим на лист, где будет находиться сводная диаграмма. Ставим курсор в первую ячейку диапазона для сводной таблицы (весь диапазон Excel сформирует самостоятельно).

После нажатия кнопки «Готово» получаем сводную диаграмму и таблицу следующего вида:

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

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

Выбираем сводную таблицу, щелкнув в любом месте мышкой. После чего переходим на вкладку «Вставка-Гистограмма».

Выбираем простую гистограмму. В результате появляется диаграммы с данными из сводной таблицы и область фильтра. Зададим фильтр для значений в столбцах – отобразить продажи за 1 квартал 2013, 2014, 2015 гг.

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

С помощью окна «Область фильтра» можно управлять сводной таблицей и диаграммой.

Сравнение продаж по кварталам за анализируемый период:

Анализ продаж в магазине «Серебрянка»:

Если для удобства необходимо переместить диаграмму на новый лист, щелкаем правой кнопкой по области построения и нажимаем «Переместить диаграмму».

В первом примере мы объединяли в одну диаграмму сразу несколько диапазонов. Но можно построить график и на основе одной базовой таблицы. Выделить ее – перейти на вкладку «Вставка».

Программа создаст одновременно сводную таблицу и диаграмму. Настраивайте поля – и анализируйте.

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

Решение: Нужно построить сводную диаграмму. Графическое представление информации, которая собрана в сводной таблице, может пригодиться для презентации в PowerPoint, использования на собрании, в отчёте или для быстрого анализа. Сводная диаграмма даёт снимок интересующих данных (как и обычная диаграмма), но вдобавок она снабжена интерактивными фильтрами непосредственно из сводной таблицы, которые позволяют быстро анализировать различные срезы данных.

Создаём сводную диаграмму

В Excel 2013 можно создать сводную диаграмму двумя способами. В первом случае мы используем достоинства инструмента «Рекомендуемые диаграммы » в Excel. Работая с этим инструментом, нам не потребуется сначала создавать сводную таблицу, чтобы далее из неё построить сводную диаграмму.

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

Вариант 1: Создаём сводную диаграмму при помощи инструмента «Рекомендуемые диаграммы»

  1. Выделите данные, которые нужно показать на диаграмме.
  2. На вкладке Вставка (Insert) в разделе Диаграммы (Charts) нажмите кнопку Рекомендуемые диаграммы (Recommended Charts), чтобы открыть диалоговое окно Вставка диаграммы (Insert Chart).
  3. Диалоговое окно откроется на вкладке Рекомендуемые диаграммы (Recommended Charts), где в меню слева приведён список подходящих шаблонов диаграмм. В правом верхнем углу миниатюры каждого шаблона есть иконка сводной диаграммы:
  4. Кликните по любой диаграмме из списка рекомендованных, чтобы увидеть результат в области предварительного просмотра.
  5. Выберите, подходящий (или почти подходящий) тип диаграммы и нажмите ОК .

Слева от листа с данными будет вставлен новый лист, на котором будет создана сводная диаграмма (и сопутствующая сводная таблица).

Вариант 2: Создаём сводную диаграмму из существующей сводной таблицы

  1. Кликните в любом месте сводной таблицы, чтобы на Ленте меню появилась группа вкладок Работа со сводными таблицами (PivotTable Tools).
  2. На вкладке Анализ (Analyze) нажмите кнопку Сводная диаграмма (Pivot Chart), при этом откроется диалоговое окно Вставка диаграммы (Insert Chart).
  3. В левой части диалогового окна выберите подходящий тип диаграммы. Далее выберите подтип диаграммы в верхней части окна. Будущая сводная диаграмма будет показана в области предварительного просмотра.
  4. Нажмите ОК , чтобы вставить сводную диаграмму на тот же лист, где находится исходная сводная таблица.
  5. После того, как сводная диаграмма создана, можно заняться настройкой её элементов и цветов при помощи списка полей на Ленте меню или иконок Элементы диаграммы (Chart Elements) и Стили диаграмм (Chart Styles).
  6. Посмотрите на получившуюся сводную диаграмму. Вы можете управлять фильтрами прямо на диаграмме, чтобы увидеть различные срезы данных. Здорово, правда!

Урок подготовлен для Вас командой сайта office-guru.ru

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

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

Рис. 8.50.

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

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

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

Рис. 8.51.

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

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

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

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

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

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

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

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

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

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

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

Сортировка и группировка элементов сводных таблиц.

Форматирование сводных таблиц.

Вычисления в сводных таблицах.

Работа со сводной таблицей.

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

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

Создадим сводную таблицу на основе списка Заказы товаров книги Продажа товаров (см. рис. 4.1).

Рис. 4.1. Список Заказы товаров

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

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

2. С помощью меню Данные , команды Сводные таблицы вызвать мастер создания сводных таблиц и диаграмм (см. рис. 4.2).

Рис. 4.2. Первый шаг мастера сводных таблиц и диаграмм

3. На первом шаге необходимо указать тип источника данных – Создать таблицу на основе данных, находящихся в списке или базе данныхExcel, и вид создаваемого отчета – сводная таблица .

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

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

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

Безопасней всего создавать таблицу на новом листе, для чего установить переключатель «Помещать таблицу в…» на «Новый лист» . В противном случае данный переключатель нужно установить на «Существующий лист» и указать диапазон (или абсолютный адрес первой ячейки, создаваемой таблицы) текущего листа или любого другого существующего листа.

6. По окончании работы мастера на рабочем листе отобразится:

§ пустой макет таблицы,

§ список возможных полей сводной таблицы,

§ панель инструментов Сводные таблицы (см. рис. 4.3).

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

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

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

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



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

Продолжая работу с нашим примером (см. рис. 4.1), перетащив поле Страна получателя на ось столбцов, поле Категория на ось строк и поле Стоимость заказов в область элементов данных ,получим сводную таблицу, изображенную на рисунке 4.4.

После того как начальная структура таблицы создана, можно ее реорганизовывать исходя из насущной необходимости анализа данных. Реорганизовать сводную таблицу – это значит изменить ориентацию или положение одного или нескольких полей. Изменение положения таблицы используется для просмотра данных под другим углом зрения. В оригинале сводная таблица называется Pivot Table (вращающаяся таблица). Именно возможность изменения ориентации таблицы, например транспонирование заголовков столбцов в заголовки строк (см. рис. 4.5) и наоборот, сделали сводные таблицы мощным аналитическим средством.

Рис. 4.4. Сводная таблица на основе данных списка Заказы товаров


Рис. 4.5. Транспонирование поля Страна получателя в заголовки столбцов

Перемещать поля сводной таблицы можно перетаскиванием заголовков столбцов с помощью мыши. Кроме того, полностью изменить весь макет сводной таблицы можно, используя команду Мастер , меню Сводная таблица панели инструментов Сводные таблицы и клавишу Макет . Перемещать заголовки полей в открывшемся диалоговом окне (см. рис. 4.6) можно так же с помощью мыши.

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

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

Рис. 4.6. Окно изменения макета мастера сводной таблицы

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

Значения поля на оси страниц можно отобразить на отдельных листах. Даже если сводная таблица содержит ось страниц, вся таблица хранится на одном листе книги. Командой Отобразить страницы на панели инструментов Сводные таблицы (кнопка Сводная таблица ) создает на отдельных листах ряд связанных таблиц, в каждой из которых будет отображаться один элемент поля страниц. Каждому листу присваивается имя отображаемого элемента поля.

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

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

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

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

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

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

1. Поставить указатель ячейки на любую ячейку области данных.

2. С помощью команды Параметры поля на панели инструментов Сводные таблицы (кнопка Сводная таблица ), открыть диалоговое окно (см. рис. 4.7).

3. В поле Операция открытого диалогового окна выбрать необходимую функцию.

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

1. Перетащить нужное число раз кнопку данного поля с панели Списка полей сводной таблицы (см. рис. 4.3) в область данных. Если вышеназванная панель отсутствует на экране, используется клавиша Отобразить список полей панели инструментов Сводные таблицы .

Рис. 4.7. Диалоговое окно Вычисление поля сводной таблицы

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

3. Повторить второй пункт нужное число раз.

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


Рис. 4.8. Использование итоговых функций Сумма и Количество
для поля Стоимость заказа

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

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

Рис. 4.9. Использование дополнительных вычислений в сводных таблицах

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

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

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

2. С помощью команды Формулы – Вычисляемое поле на панели инструментов Сводные таблицы (кнопка Сводная таблица ) открыть диалоговое окно Вставка вычисляемого поля (см. рис. 4.10).

3. В поле Имя ввести имя создаваемого поля.

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

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

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

Рис. 4.10. Диалоговое окно Вставка вычисляемого поля

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

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

1. Поставить указатель ячейки на имя поля или любой элемент этого поля на оси строк либо на оси столбцов.

2. С помощью команды Формулы – Вычисляемый объект на панели инструментов Сводные таблицы (кнопка Сводная таблица ), открыть диалоговое окно Вставка вычисляемого элемента в… (см. рис. 4.11).

3. В поле Имя вышеназванного диалогового окна добавляется имя нового элемента, а в поле Формула – указать формулу его расчета. Элементы добавляются в формулу двойным щелчком мыши в поле Элементы или нажатием кнопки Добавить элемент .

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

Рис. 4.11. Добавление вычисляемого элемента в поле Категория

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

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

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

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

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

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

3. В диалоговом окне Вычисление поля сводной таблицы (см. рис. 4.7) нажать клавишу Формат .

4. В открывшемся окне Формат ячеек установить необходимый числовой формат.

Надписи внешних полей можно центрировать относительно надписей внутренних полей. Например на рисунке 4.12 заголовки поля Страна Получателя центрированы по вертикали относительно заголовков поля Категория , надписи Австрия Итог, Бразилия Итог отцентрированы по горизонтали. Для того чтобы отформатировать подобным образом сводную таблицу, необходимо в диалоговом окне Параметры сводной таблицы (команда Параметры таблицы меню Сводная таблица , панель инструментов Сводные таблицы ), включить флажок Объединять ячейки заголовков .

Рис. 4.12. Центрирование надписей внешних полей по отношению
к надписям внутренних полей

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

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

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

2. В открывшемся меню структурного выделения выбрать элементы выделения: Выделить – Только заголовки, Только данные, Данные и заголовки .

Например, на рисунке 4.12 выделены данные и заголовки итогов по стране (Австрия Итог , Бразилия Итог и т. д.).

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

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

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

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

1. Выделить ячейку любого элемента поля или кнопку поля, которое необходимо сортировать (в рассматриваемом примере рисунка 4.13 – поле Категория ).

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

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

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

Рис. 4.14. Установка автосортировки с помощью диалогового окна
Дополнительные параметры поля сводной таблицы

В рассматриваемом примере основанием для сортировки поля Категория буду служить значения поля Сумма по полю Стоимость заказа .

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

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

Кроме того, в случае необходимости Excel позволяет осуществлять группировку:

§ выбранных элементов полей на оси строк или столбцов;

§ числовых элементов полей, размещенных на оси строк или столбцов;

§ по временным диапазонам поля даты, помещенного на оси строк или столбцов.

Для того чтобы сгруппировать выбранные элементы, необходимо:

1) выделить элементы полей, находящихся на осях, которые нужно группировать;

2) выбрать команду Группировать в подменю Группа и структура , меню Данные .

В результате будет создано новое поле, в котором выделенные элементы будут сгруппированы в группу с именем Группа1 (см. рис. 4.15).

Рис. 4.15. Создание группы элементов Кондитерские изделия ,
Напитки и Фрукты

Можно скрыть элементы группы, дважды щелкнув мышью на имени группы (Группа1 ); чтобы снова вывести элементы группы на экран, необходимо дважды щелкнуть мышью по заголовку группы еще раз. Кроме того, для скрытия или отображения элементов группы используются команды Скрыть детали и Отобразить детали меню Данные , подменю Группа и структура .

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

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

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

Рис. 4.16. Группировка числовых элементов поля

Группировка элементов по временным диапазонам производится в том случае, если поле на оси строк или страниц содержит данные типа дата, время . Такой тип данных группируется аналогично числовым элементам, только в выводимом на экран, в результате применение команды Группировать , диалоговом окне Группирование (см. рис. 4.17) необходимо вы- брать временные интервалы группировки от секунды до года.

Рис. 4.17. Группировка поля даты

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

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

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

При построении сводной диаграммы на основе списка необходимо, выделив любую ячейку списка, вызвать мастер построения сводных таблиц и диаграмм (см. рис. 4.2). На первом шаге мастера в группе Вид создаваемого отчета поставить переключатель на поле Сводная диаграмма (со сводной таблицей) . Благодаря работе мастера будет создана сводная диаграмма и связанная с ней сводная таблица. Например, на рисунке 4.18 показана сводная диаграмма, связанная с таблицей рисунка 4.4.

Кроме таких элементов обычных диаграмм Microsoft Excel, как ряд, значение, оси, сводные диаграммы имеют специализированные элементы:

§ поле страниц используется для фильтрации данных по конкретному элементу, соответствует оси страниц сводной таблицы;

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

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

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

Рис. 4.18. Сводная диаграмма

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

Вопросы для самоконтроля.

1. Дать определение сводной таблице.

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

3. Особенности работы с элементами поля, помещенного на ось страниц.

4. Что понимают под реорганизацией сводной таблицы, каковы способы ее осуществления?

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

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

7. Изменение итоговых функций полей, помещенных в область данных.

8. Создание вычисляемых полей и вычисляемых элементов.

9. Особенности форматирования сводных таблиц.

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

11. Автосортировка элементов поля сводной таблицы.

12. Отображение нескольких наибольших или наименьших элементов поля сводной таблицы.

13. Группировка выбранных элементов полей на оси строк или на оси столбцов.

14. Группировка полей сводных таблиц по временным диапазонам.

15. Особенности построения сводных диаграмм.

16. Специальные элементы сводных диаграмм и их соответствие элементам связанных сводных таблиц.

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

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

2. Разместить поле Страна получателя в область строк, а поле Категория – в область столбцов.

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

4. Разместить информацию о заказах по различным категориям товаров на отдельных листах книги.

5. В список Заказы товаров книги Продажа товаров добавить 20 000 000 в первый заказ по кондитерским изделиям, заказанным в Австрию.

Вернитесь в сводную таблицу и обновите данные. Проверьте изменения.

Удалить 20 000 000, добавленные по первому заказу в Австрию (кондитерские изделия) и вновь обновить сводную таблицу.

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

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

8. Используя свернутый список Данные, отобразить только суммупо полю Стоимость заказов .

9. Добавить в сводную таблицу вычисляемое поле Стоимость со скидкой , рассчитываемое уменьшением суммарной стоимости на 15%.

11. Для полученной сводной таблицы установить, используя команды автоформата, формат Таблица 10 , а затем Классическая сводная таблица .

12. Добавить в существующую сводную таблицу поле, в котором подсчитывается общая стоимость товара Цена*Количество*(1–Скидка) . Установить для этого поля денежный формат, грн, только целая часть.Поле Сумма по количеству единиц товара удалить из области данных.

13. По списку Продажа товаров создать сводную таблицу, поместив на ось строк поле Страна получателя , на ось столбцов – поле Дата размещения заказов , в область данных – поле Стоимость заказа . Элементы поля Дата размещения заказов сгруппировать по годам и кварталам.

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

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

16. В созданной диаграмме перетащить поле Дата размещения заказа в поле страниц и посмотреть зависимости по отдельным месяцам и годам.

Задания лабораторной работы 4.1.

1. Скопировать к себе в папку файл Лабораторная работа 4 . Все последующие задания лабораторной работы выполнить на основании списка Отправка товаров книги файла Лабораторная работа 4.xls , в каждом задании создавая независимый отчет.

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

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

4. Лист, на котором отражена сводная таблица по всем способам доставки, переименовать во Все способы доставки .

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

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

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

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

9. В поле Категория товаров добавить вычисляемый элемент Вегетарианская продукция , в котором будет подсчитано количество заказов по всем категориям товаров кроме Рыбопродуктов , Молокопродуктов , Мяса и птицы .

Задания лабораторной работы 4.2.

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

2. Лист, на котором создалась связанная с диаграммой сводная таблица, переименовать в Сводная таблица_1 .

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

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

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

6. Лист переименовать в Менеджеры.

7. Всех менеджеров выделить в отдельную группу Менеджеры . Надписи внешних полей центрировать относительно внутренних полей.

8. Удалить из таблицы поле, содержащее элементы группы Менеджеры.

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

10. На новом листе Кварталы создать сводную диаграмму (создавать диаграмму не на основе существующего отчета, а независимую), показывающую количество заказов на каждую категорию товара по квартально, в диаграмме показать только информацию 2003 года.

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

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

Для начала расположите указатель в любом месте сводной таблицы и перейдите на вкладку ленты Вставка (Insert). В группе Диаграммы (Charts) этой вкладки ознакомьтесь с типами диаграмм, которые можно создавать на основе данных сводной таблицы. Выберите необходимый тип. Например, щелкните на кнопке Гистограмма (Column chart) и выберите первый двухмерный вариант гистограммы (рис. 6.2).

Как видно на рис. 6.3, после выбора типа диаграммы она появляется в окне программы.

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

Кстати, для изменения расположения уже созданной сводной диаграммы щелкните на ней правой кнопкой мыши (в области построения) и в контекстном меню выберите команду Переместить диаграмму (Move Chart). На экране появится диалоговое окно, в котором нужно указать новое расположение объекта диаграммы.

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

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

В сводные диаграммы можно также добавлять срезы. Для выполнения этой операции щелкните в области сводной диаграммы, выберите контекстную вкладку Анализировать (Analyze) и найдите кнопку Вставить срез (Insert Slicer). Щелкните на этой кнопке, чтобы ощутить все преимущества использования срезов в сводной диаграмме. Для получения справочных сведений по работе со срезами обратитесь к разделу .

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

Создание сводной диаграммы «с нуля»

Вам вовсе не обязательно создавать сводную диаграмму на основе сводной таблицы. Можно построить сводную диаграмму сразу на основе необработанных исходных данных. Щелкните на любой из ячеек таблицы с исходными данными и перейдите на вкладку Вставка (Insert). В группе Таблицы (Tables) щелкните на кнопке Сводная таблица (PivotTable) и выберите в раскрывающемся меню команду Сводная диаграмма (PivotChart). На экране появится диалоговое окно создания сводной диаграммы. В нем задаются такие же настройки, что и при создании сводной таблицы.

Знакомство с панелью области фильтра сводной таблицы

В Excel 2003 на сводных диаграммах выводились все доступные в сводной таблице поля. На основе представленных полей пользователи могли изменять вид диаграммы и фильтровать данные в ней. В Excel 2007 список полей сводной таблицы был успешно удален из области сводной диаграммы. Таким образом, в этой версии программы сводные диаграммы перестали загромождаться сомнительными элементами интерфейса, а их вид мало чем отличается от вида обычных диаграмм. Вместо полей сводной таблицы в Excel 2007 для управления сводной диаграммой используется панель Область фильтра сводной таблицы (PivotChart Filter Pane). Эта панель применяется для фильтрации данных, на основе которых выводится сводная диаграмма.

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