Создание сводной таблицы с помощью мастера сводных таблиц

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

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

Создание и обработка сводных таблиц осуществляется с помощью программы Мастер сводных таблиц.

Запустить программу Мастер сводных таблиц можно путем выбора команды Сводная таблица меню Данные. На экране появится первое диалоговое окно Мастера сводных таблиц.

Рис. 37. Первое диалоговое окно Мастера сводных таблиц

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

Рис. 38. Второе диалоговое окно Мастера сводных таблиц

В этом диалоговом окне необходимо указать диапазон ячеек, данные из которого будут представлены в сводной таблице. Если указатель ячейки перед активизацией Мастера сводных таблиц находился внутри таблицы, то программа автоматически внесет в поле Диапазон диалогового окна Мастер сводных таблиц — шаг 2 из 4 адрес диапазона, охватывающего всю таблицу. В противном случае пользователь должен выделить диапазон ячеек в текущем или другом листе книги. Нажатие кнопки Обзор приво­дит к открытию диалогового окна выбора файла, в котором можно выбрать книгу, содержащую данные для сводной таблицы. После задания диапазона ячеек, нажав кнопку Далее, следует перейти в следующее диалоговое окно Мастера сводных таблиц;

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

Рис. 39. Третье диалоговое окно Мастера сводных таблиц

Таким образом мы задали представление данных для каждого продав­ца на "отдельной странице". Имена продавцов будут содержаться в поле списка. Для отображения на экране данных для конкретного продавца следует выбрать фамилию продавца из описка.

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

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

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

Рис. 40. Четвертое диалоговое окно Мастера сводных таблиц

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

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

Для группировки отдельных элементов в качестве первого критерия используется дата продажи (т.е. данные о продажах за один день объеди­нены в группу, и вычислена сумма оборота за день). Промежуточные итоги представлены под отдельными значениями группы.

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

Рис. 41. Созданная сводная таблица

9.3. Работа с данными при помощи панели инструментов Запрос и сводная таблица и фильтрация данных в сводной таблице.

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

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

Рис. 42. Панель инструментов Запрос и сводная таблица

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

Рис. 43. В рабочем листе представлены данные для конкретного продавца

Изменение ориентации данных выполняется с помощью перетаски­вания поля данных в новую позицию. Таким образом в области полей страницы можно представить несколько полей, содержимое которых будет использоваться в качестве критерия при "разбиении" данных на отдельные страницы. Переместите, например, поле данных Марка в позицию непосредственно под полем Продавец. При позиционировании курсора во время перетаскивания между ячейками А1 и А2 появляется символ, указывающий на то, что поле данных будет вставлено в область полей. Если в обоих полях списка выбраны элементы Все, то тогда в таблице будут представлены значения оборота по дням и годам выпуска. С помощью выбора элемента из поля списка Марка можно задать показ данных о продажах определенной марки. Если Вы переместите в область полей страницы еще и поле Год выпуска, то в таблице будут отображены только данные об объемах продаж по дням и общий итог.

Рис. 44. В качестве фильтра показа используется несколько полей

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

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

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

Рис. 45. Диалоговое окно Показать детали

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

Рис. 46. Таблица, в которой приведена "расшифровка" оборота за день для каждого конкретного продавца