Мастер сводных таблиц

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ №2

Анализ данных на основе сводных таблиц

Теоретический материал

1 Понятие о сводных таблицах

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

Мастер сводных таблиц

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

Построение СТ выполняется в четыре этапа (шага):

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

а) в списке или базе данных какого-либо листа Microsoft Excel.

б) во внешнем источнике данных, которым может быть база данных, текстовый файл или любой другой источник, кроме книги Microsoft Excel.

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

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

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

В общем случае полное имя диапазона задается в виде: [имя_ книги]имя_листа!интервал;

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

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

Размещение полей в определенную область макета выполняется путем их “перетаскивания” при нажатой левой кнопки мыши. Чтобы удалить поле из области макета, его перетаскивают за пределы макета. Удаление поля приведет к скрытию в СТ всех зависимых от него величин, но не повлияет на исходные данные.

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

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

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

1. Создайте на первом листе книги таблицу продаж:

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

1. Щелкните курсором на ячейку A1, чтобы Excel понял с какими данными мы собираемся работать.

2. Выберите в меню Данные → Сводная таблица... Далее появится серия диалогов, для настройки сводной таблицы.

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

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

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

Теперь на нашем листе появилась форма сводной таблицы и список полей.

Форма сводной таблицы содержит три области для перетаскивания в них полей: для столбцов, для строк и для данных.

Из диалога Список полей сводной таблицы перетащите поле Фамилия в область Перетащите сюда поля строк. Форма сводной таблицы немного изменилась.

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

Теперь перетащите поле Сумма в область данных.

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

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

Отчет получился слишком подробным. Сгруппируем его по месяцам.

1. Щелкните правой кнопкой по полю отчета Дата, в появившемся меню выберите Группа и структура, затем Группировать ...

2. В следующем диалоге Excel просит нас указать, как именно группировать и сразу предлагает по месяцам (выделено). Ничего не меняем и нажимаем ОК.

Теперь дата продажи в отчете сгруппирована по месяцам.

Чтобы изменить отчет, например, заменить поле строк, просто перетяните это поле из отчета за его пределы. А затем перенесите туда другое поле, например, поле Продукт.