Используйте VBA в Excel для создания и восстановления сводной таблицы
Функция сводной таблицы – это мощная функция Excel, которая помогает быстро суммировать данные. Эта функция впервые появляется в Excel 5.
Я думаю, вы знакомы с созданием и восстановлением сводных таблиц вручную, и это руководство поможет вам использовать VBA для динамического создания и восстановления сводных таблиц. Статья использовалась для Excel 2000.
Предположим, что на листе 1 у меня есть блок данных для анализа, как показано на фигура 1 . Этот блок данных включает поля: SalesRep (торговый представитель), Region (регион), Month (месяц), Sales (продажи).
Перед созданием сводной таблицы, например фигура 2 , Я выбрал «Записать новый макрос». как показано в Рисунок 3 , чтобы увидеть, как записывается код.
Рисунок 3
Затем я перехожу на экран VBE, нажимая Alt + F11 . Я вошел Модуль1 , увидев следующий код:
Дополнительный макрос1 ()
Макрос1 Макрос1
Макро записано 17.03.2003 Дуйе
Диапазон (“A1: D13”). Выбирать
ActiveWorkbook.PivotCaches.Add (SourceType: = xlDatabase, SourceData: = _
«Лист1! R1C1: R13C4»). CreatePivotTable TableDestination: = Range (“A1”), _
TableName: = “PivotTable1”
ActiveSheet.PivotTables («Сводная таблица1»). SmallGrid = False
Примечание:
Область, край
Поле страницы в сводной таблице.
Торговый представитель
Поле строки в сводной таблице.
Месяц
Поле столбца в сводной таблице.
Продажи
Поле данных в сводной таблице с использованием функции Sum
ActiveSheet.PivotTables («Сводная таблица1»). AddFields RowFields: = “SalesRep”, _
ColumnFields: = “Месяц”, PageFields: = “Регион”
ActiveSheet.PivotTables («Сводная таблица1»). Сводные поля («Продажи»). Ориентация = _
xlDataField
Конец подписки
Изучите написанный код:
Чтобы исследовать приведенный выше код, вам необходимо знать некоторые связанные объекты. Все эти объекты описаны в интерактивной справке.
Сводные кеши
представляет собой набор объектов PivotCache в объекте Workbook
Сводные таблицы
представляет собой набор объектов сводной таблицы в объекте Workbook
Поля сводной таблицы
представляет собой набор полей в объекте сводной таблицы
Создать сводную таблицу
метод объекта PivotCache для создания сводной таблицы с использованием данных в PivotCache
Мы можем переписать описанную выше процедуру, используя CreatePivotTable процедура (обратите внимание, что вы вводите эту процедуру в module1), она может быть немного длинной, но ее будет легче понять, и вы можете запустить программу где угодно, нажав Alt + F8 комбинацию клавиш, затем выберите CreatePivotTable процедуру и выберите Запустить как показано в Рисунок 4 .
Подложка CreatePivotTable ()
Dim PTCache как PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
Потрите сводную таблицу около уха
При ошибке Возобновить Далее
Application.DisplayAlerts = False
Таблицы (“Сводная таблица”) Удалить
При ошибке GoTo 0
Tao Pivot Cache
Установите PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType: = xlDatabase, _
SourceData: = Sheets (“Sheet1”). Диапазон («А1»). CurrentRegion.Address)
Создать новый рабочий лист и каталог
Рабочие листы.Добавить
ActiveSheet.Name = “PivotSheet”
Tao Pivot Table tu Cache
Установите PT = PTCache.CreatePivotTable _
(TableDestination: = Sheets (“PivotSheet”). Диапазон (“A1”), _
TableName: = “PivotTable1”)
С ПТ
Их школы
.PivotFields (“Регион”) Orientation = xlPageField
.PivotFields («Месяц»). Ориентация = xlColumnField
.PivotFields (“SalesRep”) Ориентация = xlRowField
.PivotFields (“Продажи”) Orientation = xlRowField
Application.ScreenUpdating = True
Конец с
Конец подписки
Когда вы закончите описанную выше процедуру, вы получите сводную таблицу на листе 2, в этом случае лист называется сводной таблицей. ( Рисунок 5. )
Если вы обратите внимание, вы увидите разницу между двумя приведенными выше кодами. В Макрос1 при использовании Добавлять метод создания PivotCache , Источник данных является ” Sheet1! R1C1: R13C4 “и в коде я пишу Листы («Лист1»). Диапазон («А1»). CurrentRegion.Address . Здесь я использую Свойство Current Region , что означает, что данные, которые мы используем, основаны на текущей области вокруг ячейки A1. Это необходимо для того, чтобы CreatePivotTable процедура продолжает хорошо работать, когда мы добавляем данные.
Теперь предположим, что я добавил Цель поле в блоке данных, а в Сводная таблица Я добавлю целевое поле, а также добавлю Дисперсия поле расчета. Это поле (Дисперсия) будет равно Объект скидок . Мой новый блок данных показан в Рисунок 6 .
Код в CreatePivotTable Приведенная выше процедура будет добавлена следующим образом (я только что добавил в абзаце With PT. End With):
С ПТ
Их школы
.PivotFields (“Регион”) Orientation = xlPageField
.PivotFields («Месяц»). Ориентация = xlColumnField
.PivotFields (“SalesRep”) Ориентация = xlRowField
.PivotFields (“Продажи”) Orientation = xlDataField
.PivotFields (“Target”) Orientation = xlDataField
Отличные новости
.CalculatedFields.Add “Разница”, “= Продажи – Цель”
.PivotFields (“Разница”). Ориентация = xlDataField
Заменить заголовок doi
.PivotFields («Сумма продаж»). Заголовок = “Продажи ($)”
.PivotFields («Сумма цели»). Caption = “Цель ($)”
.PivotFields («Сумма дисперсии»). Заголовок = “Разница ($)”
Конец с
После повторного запуска вышеупомянутой процедуры я буду похож на Рисунок 7 .
Предположим, мои данные теперь составляют 6 месяцев ( Рисунок 8 ), Я хочу добавить итоговый столбец на 3 месяца. Мне нужно исправить свой код следующим образом:
С ПТ
Их школы
.PivotFields (“Регион”) Orientation = xlPageField
.PivotFields («Месяц»). Ориентация = xlColumnField
.PivotFields (“SalesRep”) Ориентация = xlRowField
.PivotFields (“Продажи”) Orientation = xlDataField
.PivotFields (“Target”) Orientation = xlDataField
Отличные новости
.CalculatedFields.Add “Разница”, “= Продажи – Цель”
.PivotFields (“Разница”). Ориентация = xlDataField
Полная учетная запись
.PivotFields («Месяц»). CalculatedItems.Add “Q1”, _
“= лестница 1 + лестница 2 + лестница 3”
.PivotFields («Месяц»). CalculatedItems.Add “Q2”, _
“= лестница 4 + лестница 5 + лестница 6”
Переместите файлы
.PivotFields («Месяц»). PivotItems («Q1»). Позиция = 4
.PivotFields («Месяц»). PivotItems («Q2»). Позиция = 8
Заменить заголовок doi
.PivotFields («Сумма продаж»). Заголовок = “Продажи ($)”
.PivotFields («Сумма цели»). Caption = “Цель ($)”
.PivotFields («Сумма дисперсии»). Заголовок = “Разница ($)”
Конец с
После повторного запуска процедуры CreatePivotTable я получу результат, как показано на Рисунок 9 .
Что ж, подойдите сюда, видите ли, если мы будем знать, как использовать VBA, анализ данных станет проще. Кроме того, мы также можем создать сводную таблицу из внешнего источника данных, такого как Access. Для хорошего программирования сводных таблиц вы должны прочитать раздел онлайн-справки Excel по объектам, методам и атрибутам, упомянутым выше.
Надеюсь, эта статья частично вам поможет.
Любые предложения присылайте по адресу levanduyet@pmail.vnn.vn.
Le Van Duyet
