В начало

Приложение. Ответы

19-й час. Сводные таблицы

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

В этом часе будут рассмотрены следующие вопросы.

• Для чего применяются сводные таблицы.
• Основные принципы построения сводных таблиц.
• Запись макроса построения сводной таблицы.
• Модификация кода макроса построения сводной таблицы.

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

Вероятно, сводные таблицы - одно из наиболее интересных средств анализа данных в Excel. Сводные таблицы используются для обобщения больших массивов данных, представляя данные в трехмерном формате. Такой формат позволяет фильтровать данные, отображая различные уровни детализации. На рис. 19.1 показана таблица данных (список), а на рис. 19.2 - сводная таблица, построенная на основе этих данных.

Сводные таблицы обладают свойствами, эффективными при анализе данных.

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

Рис. 19.1. Таблица данных, на основе которых построена сводная таблица

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

Основы сводных таблиц

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

• Область Страница - представляет самый верхний уровень детализации данных. В сводной таблице на рис. 19.2 в эту область помещено поле Год.
• Область Строка - элементы полей, помешенные в эту область, используются в качестве заголовков строк и представляют более низкий уровень детализации (на рис. 19.2 в эту область помешено поле Район).
• Область Столбец - элементы полей, помещенные в эту область, используются как заголовки столбцов сводной таблицы (в сводной таблице на рис. 19.2 в эту область помешено поле Категория товара).
• Область Данные - элементы полей, помещенные в эту область, используются для вычислений. В сводной таблице на рис. 19.2 суммируются значения из поля Объем продаж.

Рис. 19.3. Макет сводной таблицы, представленный в окне мастера сводных таблиц

Вернемся к таблице данных, показанной на рис. 19.1. Любое из полей Год, Район или Категория товара можно поместить в любую из областей Страница, Строка или Столбец. Поле Объем продаж следует поместить в область Данные, так как в данной таблице это единственное поле с числовыми данными. (Можно поместить в область Данные и поле с нечисловыми данными, но тогда над данными можно проводить только очень ограниченный круг вычислений, например подсчитать количество определенных элементов.) При разных вариантах размещения полей списка по областям сводной таблицы данные будут группироваться по-разному, и можно получить уровни детализации по различным критериям. Сводная таблица, у которой поле Район помешено в область Страница, а поле Год - в область Строка, показана на рис. 19.4.

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

Рис. 19.4. Измененная сводная таблица

Запись макроса для создания сводной таблицы

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

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

Если вы хотите, чтобы таблица данных на рабочем листе воспринималась Excel как список, позаботьтесь о том, чтобы заголовки столбцов были выделены шрифтом, отличным оттого, каким набраны строки сданными.

Выделите таблицу данных и начните запись макроса. Выполните команду Данные > Сводная таблица. В первом диалоговом окне мастера сводных таблиц установите переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щелкните на кнопке Далее. В следующем диалоговом окне удостоверьтесь, что выбран весь диапазон таблицы данных, и щелкните на кнопке Далее. В третьем диалоговом окне мастера сводных таблиц установите переключатель новый лист и щелкните на кнопке Готово. В рабочую книгу будет добавлен новый лист, содержащий пустую сводную таблицу (рис. 19.5).

Рис. 19.5. После завершения работы мастера сводных таблиц на новом рабочем листе отобразится пустой макет сводной таблицы

Рис. 19.6. Законченная сводная таблица

Перетащите из панели Сводные таблицы поле Год в область сводной таблицы, помеченную словами Перетащите сюда поля страниц. Перетащите поле Район в область, помеченную словами Перетащите сюда поля строк. Далее перетащите в область столбцов сводной таблицы поле Категория товара, а в область данных - поле Объем продаж. Законченная сводная таблица показана на рис. 19.6. Остановите запись макроса.

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

Исследование кода макроса

Перейдите в редактор Visual Basic и в окне проектов найдите текущую книгу. Откройте папку Modules и затем Module1 - вы увидите код макроса в виде процедуры МояСводнаяТаблица (если такое имя вы дали своему макросу). Полный код этой процедуры приведен в листинге 19.1.

Листинг 19.1. Процедура МояСводнаяТаблица

l:Sub МояСводнаяТаблица()

2: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDataBase, _

3:  SourceData:= "Лист1!R1C1:R37C4").CreatePivotTable _

    TableDestination:="", TableName:"= "СводнаяТаблица1"

4: ActiveSheet.PivotTableWizard _

5:  TableDestination:=ActiveSheet.Cells(3,1)

6: ActiveSheet.Cells(3,1).Select

7: ActiveSheet.PivotTables("СводнаяТаблица1").SmallGrid = False

8: With ActiveSheet.PivotTables("СводнаяТаблица1"). _

    PivotFields("Год")

9:  .Orientation = xlPageField

10:  .Position = 1

11: End With

12: With ActiveSheet.PivotTables("СводнаяТаблица1"). _

     PivotFields("Район")

13:  .Orientation = xlRowField

14:  .Position = 1

15: End With

16: With ActiveSheet.PivotTables("СводнаяТаблица1"). _

     PivotFields("Категория товара")

17:  .Orientation = xlColumnField

18:  .Position = 1

19: End With

20: With ActiveSheet.PivotTables("СводнаяТаблица1"]. _

     PivotFields("Объем продаж")

21:  .Orientation = xlDataField

22:  .Position = 1

23:  End With

24:End Sub

Первый оператор в этой процедуре выполняет метод Add (Добавить) применительно к коллекции PivotCaches (Основные кэши). На самом деле в этом операторе выполняются два метода. В дополнение к метолу Add, создающему элемент коллекции PivotCaches, исполняется еще метод CreatePivotTable (Создать сводную таблицу), название которого говорит само за себя:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDataBase, SourceData:= _

 "Лист1!R1C1:R37C4").CreatePivotTable TableDestination:="", _

 TableName:= "СводнаяТаблица1"

Обратите внимание, что в методе CreatePivotTable аргумент TableDestination принимает значение пустой строки - для Excel это является указанием создать сводную таблицу на новом рабочем листе. После создания нового рабочего листа и сводной таблицы задается ряд свойств:

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. _

 Cells(3, 1)

ActiveSheet.Cells(3,1).Select

ActiveSheet.PivotTables("СводнаяТаблица1").SmallGrid = False

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

With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Год"]

 .Orientation = xlPageField

 .Position = 1

End With

With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Район")

 .Orientation = xlRowField

 .Position = 1

End With

With ActiveSheet.PivotTables("СводнаяТаблица1"). _

 PivotFields("Категория товара")

 .Orientation = xlColumnField

 .Position = 1

End With

With ActiveSheet.PivotTables("СводнаяТаблица1"). _

 PivotFields"Объем продаж")

 .Orientation = xlDataField

 .Position = 1

End With

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

Листинг 19.2. Измененная процедура МояСводнаяТаблица

1:Sub МояСводнаяТаблица()

2: Dim ptSales As PivotTable

3:

4: Set ptSales = ActiveWorkbook.PivotCaches.Add _

    (SourceType:=xlDataBase, SourceData:=

5:  "Лист1!R1C1:R37C4").CreatePivotTable(TableDestination:="")

6: ActiveSheet.PivotTableWizard _

    TableDestination:=ActiveSheet.Cells(3,1)

7: ActiveSheet.Cells(3,1).Select

8: ptSales.SmallGrid = False

9: With ptSales.PivotFields("Год")

10:  .Orientation = xlPageField

11:  .Position = 1

12: End With

13: With ptSales.PivotFields("Район")

14:  .Orientation = xlRowField

15:  .Position = 1

16: End With

17: With ptSales.PivotFields("Категория товара")

13:  .Orientation = xlColumnField

19:  .Position = 1

20: End With

21: With ptSales.PivotFields("Объем продаж")

22:  .Orientation = xlDataField

23:  .Position = 1

24: End With

25:End Sub

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

Резюме

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

Вопросы и ответы

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

Ответ. Нет, для обновления сводной таблицы надо щелкнуть на кнопке Обновить данные панели Сводные таблицы или выполнить метод RefreshTable (Обновить таблицу).

Вопрос. Можно ли применить метод Add к коллекции PivotTables для создания сводной таблицы и не использовать метод CreatePivotTable в связке с методом Add, применяемым к коллекции PivotCaches?

Ответ. Да, можно. Сначала примените метод Add к коллекции PivotCaches, а затем полученный объект используйте как значение аргумента PivotCacheв методе Add, применяемом к коллекции PivotTables.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Истинно или ложно следующее утверждение: сводную таблицу можно создать только на основе данных, которые записаны на рабочем листе Excel?
2. Назовите имена двух коллекций, которые необходимы для создания сводных таблиц.
3. Какое свойство объекта PivotField назначает поля данных областям сводной таблицы (т.е. областям страницы, строк, столбцов и данных)?
4. Истинно или ложно следующее утверждение: в область Строка сводной таблицы можно поместить только одно поле данных?
5. Назовите четыре области сводной таблицы.
6. Какой метод создает отчет сводной таблицы?
7. Истинно или ложно следующее утверждение: для создания сводной таблицы можно записать соответствующий макрос?

Упражнение

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

Рис. 19.7. Расширенная таблица данных

Рис. 19.8. Сводная таблица Анализ_продаж