Анализ данных при помощи сводных таблиц – ЧАСТЬ 1

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

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

Знакомьтесь: сводная таблица

На  рис.  30.1  показан  список,  содержащий  информацию  о  продажах  для  небольшой издательской  фирмы.  Список  упорядочен  по  годам,  кварталам,  номерам  в  каталоге, каналам  распространения,  количеству   проданных  книг  и  полученным  от  продажи средствам. В нем  приведены данные за восемь кварталов (2002 и 2003 годы), и фирма использует три канала распространения — внутри страны,  международный и заказ по почте. С помощью Мастера сводных таблиц и диаграмм мы можем быстро преобразовать этот «плоский» список в таблицу, которую легко просматривать и анализировать. Один из вариантов допустимого расположения данных в такой таблице продемонстрирован на рис. 30.2.

Рис. 30.1. Не так просто увидеть последнюю строку в плоском списке, подобном этому. Но Мастер сводных таблиц и диаграмм поможет исправить положение

Рис. 30.2. Эта сводная таблица представляет итоговый обзор информации исходного списка

Здесь поля Год и Квартал расположены по оси столбцов таблицы, а  поля Инв. № и Канал — по оси строк. В таблице выводятся итоги  продаж для каждого пересечения столбца  и  строки.  Ячейка  Нб,  например,  показывает  отсутствие  прибыли  в  первом квартале  2003  года  для  книги  с  регистрационным  номером  23534,  распространяемой внутри страны. Величина -488 означает, что возврат книг за этот  период времени по данному каналу распространения превысил объем продаж.

Excel дополнительно выводит окно, содержащее список полей сводной таблицы. На рис.

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

ПРИМЕЧАНИЕ

Если присутствие списка полей сводной таблицы на экране мешает вашей работе, закройте его, щелкнув на кнопке Закрыть. Для того чтобы восстановить список, щелкните на кнопке Отобразить список полей (Show Field List) на панели инструментов Сводные таблицы.

В строках 9, 13, 17 и 21 на рис. 30.2 отображаются промежуточные итоги для различных элементов  поля  Инв.  №.  Столбец  G  содержит   промежуточные  итоги  для  четырех кварталов 2002 года. За границами  рисунка находятся дополнительные промежуточные результаты, а самые  крайние строка и столбец сводной таблицы отведены под общие итоги. Так, если прокрутить таблицу с рис. 30.2 к нижней ячейке столбца С, мы узнаем итоги  продаж  за  первый  квартал  2002  года  по  всем  изданиям  и  для  всех  каналов распространения.      Мастер      сводных      таблиц      и      диаграмм      генерирует      эти

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

число проданных книг. При желании мы могли бы несколько усложнить таблицу, чтобы

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

Рис. 30.3. Сводная таблица также позволяет акцентировать внимание на конкретном срезе данных

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

Сводные таблицы создаются на основе данных четырех типов источников:

? списка Excel;

? внешнего источника данных;

? нескольких диапазонов консолидации (отдельных списков Excel, которые Мастер сводных таблиц и диаграмм объединяет при построении сводной таблицы);

? другой сводной таблицы.

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

Excel, показанного на рис. 30.1.

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

Чтобы приступить к созданию сводной таблицы, выделите ячейку в  списке, на основе которого вы хотите создать таблицу. Затем выберите  в  меню Данные (Data) команду Сводная  таблица  (PivotTable  And   PivotChart  Report).  Excel  запускает  Мастер сводных таблиц и диаграмм. Этот мастер будет сопровождать вас в процессе создания таблицы, подразделяемом на перечисленные ниже шаги.

1.    Задание типа источника данных и вида создаваемого отчета.

2.    Указание местонахождения исходных данных.

3.    Указание места размещения таблицы.

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

Шаг 1: задание типа источника данных

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

Рис. 30.4. Мастер сводных таблиц и диаграмм может работать с четырьмя типами источников данных

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

случайно не ошиблись, при выборе варианта Во внешнем источнике данных (External

Data Source) здесь появится изображение ЭВМ, передающей данные  на  терминал. А если вы установите переключатель в положение В другой сводной таблице или сводной диаграмме, вместо таблицы на рисунке появится изображение диаграммы.

Шаг 2: указание местонахождения исходных данных

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

Рис. 30.5. Если источником данных является список Excel, подтвердите или укажите его местонахождение в этом диалоговом окне

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

Шаг 3: указание места для размещения таблицы

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

Рис. 30.6. Чтобы поместить сводную таблицу на существующий рабочий лист,

введите имя диапазона или ссылку в текстовое поле

Чтобы поместить таблицу на новый рабочий лист (самый безопасный выбор), установите переключатель  в  положение  Новый  лист  (New  Worksheet).  В  противном  случае выберите вариант Существующий лист (Existing Worksheet) и введите имя диапазона или ссылку.

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

СОВЕТ  

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

РЕШЕНИЕ ПРОБЛЕМ

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

Если ошибка повторится, создайте сводную таблицу еще раз и на последнем шаге щелкните на кнопке Макет (Layout). Перетащите одно или несколько полей в область страниц, а затем дважды щелкните на  заголовке одного из полей. В диалоговом окне Вычисление поля сводной таблицы (PivotTable Field) нажмите кнопку Дополнительно (Advanced) и затем установите переключатель в положение Обновлять внешние данные при выборе каждого элемента (Query External Data  Source As You Select Each Page Field Item). Если и эти действия не приведут к успеху, упростите вашу таблицу, уменьшив число полей или задав

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий