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

Цель работы: приобретение навыков записи макроса, создающего сводную таблицу; исследования и изменения кода этого макроса в VBA.

1 Теоретические сведения

 

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

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

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

- встроенные средства фильтрации данных – сводные таблицы автоматически фильтруют информацию, отображая только заданный уровень детализации данных;

- динамически изменяемый макет сводной таблицы – макет можно изменить простым перетаскиванием поля из одной области таблицы в другую;

- автоматическое вычисление промежуточных итоговых значений – для этого достаточно указать поле, по которому необходимо выполнить вычисления, кроме того, легко изменить вычисляющую функцию;

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

На рисунке 7.2 показан пример сводной таблицы, созданной на основе таблицы, приведенной на рисунке 7.1.

Сводная таблица состоит из нескольких областей (рисунок 7.3):

- область Страница – представляет самый верхний уровень детализации данных;

- область Строка – элементы полей, помещенные в эту область, используются в качестве заголовков строк и представляют более низкий уровень детализации;

- область Столбец – элементы полей, помещенные в эту область, используются как заголовки столбцов сводной таблицы;

- область Данные – элементы полей, помещенные в эту область, используются для вычислений.

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

 

 

Рисунок 7.1

 

 

Рисунок 7.2

 

 

Рисунок 7.3

 

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

Код макроса, строящего сводные таблицы, будет иметь вид:

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

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

SourceData: = “Лист1! R1C1: R37C4”). CreatePivotTable _

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

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

End Sub

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

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