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

В качестве примера рассмотрим создание сводной таблицы, позволяющей на основе таблиц с исходными данными выполнить анализ продажи определенных товаров в различных городах России. В книге, приведенной на рис. 18.16, показана продажа нескольких моделей автомобилей: Волга, Жигули, Ока в разных городах России: в Москве, Саратове и Туле. Каждый город показан на отдельном листе. Предполагается, что сводные таблицы составляются по четырем месяцам: январь, февраль, март и апрель. Таблицы отформатированы с использованием команды Автоформат (AutoFormat) в меню Формат (Format) . Выбран образец с подписью Простой (Simple) .

Рис. 18.16 Исходный список для составления сводной таблицы

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

Положением переключателя в группе Создать таблицу на основе данных, находящихся: (Create Pivot table from data in:) установите переключатель в положение: в нескольких диапазонах консолидации (Multiple consolidation ranges) , так как источники данных для создания сводной таблицы, расположены на разных листе Excel.

Назначение других положений переключателя:

в списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or Database ) — позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных на одном листе Excel;

во внешнем источнике данных (External data source) — позволяет создать отчет сводной таблицы или сводной диаграммы по данным внешнего файла или базы данных, например, Microsoft Access, SQL Server, Paradox;.

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

в другой сводной таблице или сводной диаграмме (Another PivotTable report or Pivot Chart report) — используется для создания отчета сводной таблицы или сводной диаграммы по данным другого отчета сводной таблицы в активной книге.

Рис. 18. 17 Окно мастера сводных таблиц

В разделе Вид создаваемого отчета (What kind of report do you want to create?) поставьте переключатель в положение сводная таблица (PivotTable) для создания только сводной таблицы и нажмите кнопку Далее (Next).

Если исходные данные расположены на нескольких листах, то в следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 (PivotTable and Pivot Chart Wizard — Step 2a of3) поставьте переключатель в положение Создать одно поле страницы (Create a single page field for те) , так как все листы аналогичны и отличаются только городом, в котором реализовывалась продукция. Нажмите кнопку Далее (Next) .

На экране отобразится диалоговое окно Мастер сводных таблиц и диаграмм — шаг 26 из 3 (PivotTable and PivotChart Wizard — Step 2b of 3) . Щелкните мышью в поле Диапазон (Range) (рис. 18.18). Выделите поочередно на всех листах ячейки с А1 по Е4, и нажмите кнопку Добавить (Add) после каждого выделения для добавления диапазона к списку исходных диапазонов. Ссылка на исходную область будет добавлена в список Все ссылки (All references). Кнопка свертывания окна справа от поля позволяет свертывать диалоговое окно для выделения каждого диапазона. Повторное нажатие на эту кнопку восстанавливает окно.

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

Рис. 18.18 Выделение диапазонов таблиц, подлежащих консолидации

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

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

новый лист (New worksheet) — отчет сводной таблицы будет размещен на новом листе той же книги;

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

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

Кнопку Готово (Finish) целесообразно нажать прежде, чем кнопку Макет (Layout) в следующих случаях:

используются внешние данные с низкой скоростью загрузки и обновления;

планируется создание поля страницы и требуется задать параметр запроса для каждого элемента по отдельности;

Рис. 18.19 Выбор места расположения сводной таблицы

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

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

Рис. 18.20 Задание места расположения полей сводной таблицы

Содержание раздела