Сводная таблица Excel VBA
Сводная таблица VBA позволяет обобщать отчеты из большого набора данных. Pivot может рассматривать как крошечную форму всего набора данных. Быстрый просмотр большого набора данных возможен через сводную таблицу. Сводная таблица - это простой способ соответствующей фильтрации данных. Из доступных данных вы можете выделить данные так, как вы хотите. Сводная таблица позволяет объединять огромные данные, анализировать данные и создавать отчеты, соответствующие вашим бизнес-требованиям.
В Excel имеется встроенная сводная таблица, которую легко создать, применив сводную таблицу к таблице данных. После того, как данные предоставлены, можно автоматически создать отчет в Excel. Коды VBA помогут вам создать автоматическую сводную таблицу.
Как создать сводную таблицу с помощью Excel VBA?
Обычные шаги для вставки сводной таблицы - сначала вставить сводную таблицу из меню «Вставка», а затем выбрать таблицы, которые вы хотите изменить в сводную таблицу. Выбранная таблица станет исходными данными, и сводная таблица будет создана соответствующим образом.
Чтобы создать сводный отчет, необходимо выбрать поля для фильтров, значений и т. Д. Аналогичным образом эти шаги можно автоматизировать с помощью кодов VBA. Нам нужны данные листа Excel для работы с сводной таблицей. Давайте узнаем, как создать сводную таблицу с помощью кодов Excel VBA.
Вы можете скачать этот шаблон Excel для сводной таблицы VBA здесь - Шаблон Excel для сводной таблицы VBAШаг 1 : Сводная таблица должна рассматриваться как эталонный объект. Создайте объект для сводной таблицы на дальнейших кодах, этот объект будет использоваться для указания сводной таблицы. Назовите переменную как pvtable. Объявите функцию и объект сводной таблицы.
Код:
Sub PivotTable () Dim pvtable As PivotTable End Sub
Шаг 2: Чтобы сохранить файлы данных, нам нужен сводный кэш для объявления источника данных. Перед созданием сводной таблицы объявите переменную сводной наличности. Объявите переменную pvcache для сводного кэша.
Код:
Dim pvcache As PivotCache
Шаг 3: Данные находятся в рабочем листе и должны указывать диапазон для попадания в нужную ячейку. В сводной таблице исходные данные представлены в виде строк и столбцов, поэтому для указания определенного диапазона нам нужна переменная. Определите pvrange как переменную диапазона.
Код:
Тусклый pvrange As Range
Шаг 4: В рабочую таблицу необходимо вставить сводную таблицу, которую нужно создать. Объявите переменную как рабочий лист. Определите pvsheet как рабочий лист.
Код:
Dim pvsheet As Worksheet
Шаг 5: Вам нужна похожая переменная для использования таблицы данных, которая должна содержать данные, которые вы хотите построить в виде сводной таблицы. Таким образом, переменная таблицы данных объявлена как pdsheet.
Код:
Dim pdsheet As Worksheet
Шаг 6: Вам нужны еще две переменные в виде длинного типа данных, чтобы указать последнюю использованную строку и столбец для создания сводной таблицы. Это может быть любая строка или столбец, поэтому существует вероятность того, что число строк и столбцов превысит целочисленный предел типа данных. Давайте назовем это plr и plc.
Код:
Дим плр а лонг дим плс а лонг
Шаг 7: Следующий шаг - удалить, если какая-либо сводная таблица уже создана. Это поможет избежать путаницы, на какую таблицу будут наноситься исходные данные. Поэтому удалите предыдущий лист сводной таблицы и создайте новый лист для вставки сводной таблицы.
Код:
При ошибке Возобновить Следующее Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Удалить ', чтобы удалить существующую сводную таблицу на листе Worksheets.Add After: = ActiveSheet', чтобы добавить новый рабочий лист ActiveSheet.Name = " pvsheet "", чтобы переименовать лист в "pvsheet" при ошибке GoTo 0
Шаг 8: Назначьте переменную объекта для сводного листа и таблицы данных соответственно переменным pvsheet и pdsheet. Это будет использоваться в дальнейшем для определения рабочих листов.
Код:
Set pvsheet = Worksheets ("pvsheet") Set pdsheet = Worksheets ("pdsheet")
Шаг 9: После того, как рабочие листы настроены, следующим элементом является последняя строка и столбец, которые использовались для создания сводного отчета. Найдите последнюю использованную строку и столбец, используя объявленные переменные plr и plc.
Код:
'две переменные для поиска последней использованной строки и столбца в pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column
Шаг 10: Как мы уже упоминали ранее, данные в таблице определяются диапазоном ячеек. Вы должны установить диапазон разворота на следующем шаге. Он уже объявлен как переменная для использования диапазона разворота «pvrange».
Код:
'инициализация диапазона данных сводной таблицы Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Так как диапазон устанавливается с помощью свойства resize диапазонов ячеек, он изменит размер pvrange, соответственно, отрегулирует диапазон вращения. Таким образом, pvrange будет корректироваться при любом добавлении или удалении строк или столбцов.
Шаг 11: Пришло время установить сводный кеш, являющийся источником сводной таблицы. Используйте объект pvcache, чтобы установить исходный кеш.
Код:
'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Шаг 12: Здесь отчет о продажах для разных продуктов будет преобразован в сводную таблицу. Создайте сводную таблицу как пустую, к которой вы можете добавить набор данных.
Код:
'новая пустая сводная таблица Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Шаг 13: После того, как сводная таблица вставлена, вы должны указать различные поля, которые вы хотите вставить в сводную таблицу. Так что вставьте поле первого ряда. Здесь первый ряд начинается с продукта.
Код:
Вставить продукт в строку с pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Конец
Шаг 14: Далее необходимо указать второе поле, которое вы хотите вставить в сводную таблицу. Таким же образом вставьте улицу поля второго ряда в сводную таблицу.
Код:
Вставить улицу в строку строки и положение 2 с помощью pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Конец
Шаг 15: Вставьте следующее поле в сводную таблицу, а следующий - город. Дайте код для вставки поля города.
Код:
'Вставить город в столбец, заполненный pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 Конец
Шаг 16: До этого вставленные данные были текстового типа. Теперь нам нужно вставить количество продаж в сводную таблицу.
Код:
'Вставить столбец Sales в поле данных с помощью pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Окончание
Шаг 17: Вы вставили поля, необходимые для создания сводной таблицы. И сводная таблица почти завершена, теперь вы можете установить формат сводной таблицы. Это будет указывать тип таблицы через стиль таблицы. Расположение оси рядов также установлено так, как вы хотите.
Код:
'установить формат сводной таблицы pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Шаг 18: Чтобы показать строку значений полей в табличной форме, добавьте приведенный ниже код внизу.
Код:
'чтобы отобразить сводную таблицу в табличной форме pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Шаг 19: Нажмите кнопку запуска или нажмите F5, чтобы запустить код. Это создаст сводную таблицу из листа источника данных. Данные, приведенные в строках и столбцах, будут преобразованы в сводную таблицу. Сводная таблица будет видна на листе сводной таблицы.
Проверьте выходные данные, и вы увидите, что источник данных преобразуется в сводную таблицу, как показано ниже, указанные столбцы преобразуются в формы фильтра.
Сводные поля видны с правой стороны. Вы можете вносить изменения в соответствии с вашими потребностями в том, как данные должны быть представлены.
Для справки, я дал код ниже.
Код:
Sub PivotTable () Dim pvtable как PivotTable Dim pvcache как PivotCache Dim pvrange как диапазон Dim pvsheet как рабочий лист Dim pdsheet как рабочий лист Dim plr как длинный Dim plc как длинный при ошибке Возобновить Следующее Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets (False Worksheets) "). Удалите", чтобы удалить существующую сводную таблицу в листе Worksheets.Add After: = ActiveSheet ', чтобы добавить новый лист ActiveSheet.Name = "pvsheet" ", чтобы переименовать лист в" pvsheet ". При ошибке GoTo 0 Установить pvsheet = Worksheets ("pvsheet") Задайте две переменные pdsheet = Worksheets ("pdsheet") 'для поиска последней использованной строки и столбца в pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'инициализирующий диапазон данных сводной таблицы Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' сводная таблица Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'новая пустая сводная таблица Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Вставить продукт в строку, заполненную pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Закончить "Вставить улицу в Поданная строка и позиция 2 с pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Заканчивается 'Вставить город в столбец, заполненный pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 Конец« Вставить столбец Sales в поле данных с помощью pvsheet.PivotTables («Sales_Report»). PivotFields («Sales») .Orientation = xlDataField .Position = 1 End With »установить отформатировать сводную таблицу pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" ", чтобы показать сводную таблицу в табличной форме pvsheet.PivotLable_Report_Table_порта. .DisplayAlerts = True Application.ScreenUpdating = True End Sub
То, что нужно запомнить
- Укажите исходный лист, где данные должны быть представлены для сводной таблицы.
- Начальная строка и столбец должны быть указаны правильно.
- Используйте необходимые объекты, чтобы точно определить сводный кэш, диапазон, начало и конец строк.
- Расположите источник данных в правильном формате, поскольку это процесс автоматизации.
Рекомендуемые статьи
Это руководство к сводной таблице VBA. Здесь мы обсудим, как создать сводную таблицу, используя коды VBA в Excel, а также пример и загружаемый шаблон Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
- Как удалить сводную таблицу в Excel?
- Использование VBA в PowerPoint
- Сводная таблица с несколькими листами
- Как создать слайсер для сводных таблиц?
- Обновить сводную таблицу в VBA
- VBA On Error Resume Next (Примеры)
- Excel VBA ScreenUpdating