Как создать сводную таблицу с помощью vba?

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

Я научился использовать VBA всего ШЕСТЬ лет назад. И первый раз, когда я написал макрокод для создания сводной таблицы, это был провал.

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

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

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

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

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

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

8 простых шагов по написанию макрокода на VBA для создания сводной таблицы в Excel

Для вашего удобства я разделил весь процесс на 8 простых шагов. Выполнив эти шаги, вы сможете автоматизировать все свои сводные таблицы.

Обязательно загрузите этот файл отсюда, чтобы следовать инструкциям.

1. Объявить переменные

Первый шаг — объявить переменные, которые нам нужно использовать в нашем коде для определения различных вещей.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

В приведенном выше коде мы объявили:

1. PSheet: создать лист для новой сводной таблицы.
2. DSheet: для использования в качестве технического листа.
3. PChache: используйте в качестве имени для кэша сводной таблицы.
4. PTable: используйте в качестве имени нашей сводной таблицы.
5. PRange: установить диапазон исходных данных.
6. LastRow и LastCol: чтобы получить последнюю строку и столбец нашего диапазона данных.

2. Вставьте новую таблицу.

Перед созданием сводной таблицы Excel вставляет пустой лист, а затем создает на нем новую сводную таблицу.

И код ниже сделает то же самое для вас.

Он вставит новый лист с именем «Сводная таблица» перед активным листом, и если лист с таким же именем уже существует, он сначала удалит его.

После вставки нового листа этот код установит значение переменной PSheet на листе сводной таблицы и DSheet на листе исходных данных.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Обязательно замените имена листов в коде именами, которые есть в ваших данных.

3. Установите диапазон данных.

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

Вам нужен код, который сможет идентифицировать все данные в исходной таблице. И ниже код:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

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

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

4. Создайте сводный кэш

В Excel 2000 и более поздних версиях перед созданием сводной таблицы необходимо создать сводный кэш для установки источника данных.

Обычно, когда вы создаете сводную таблицу, Excel автоматически создает сводный кеш, не спрашивая вас, но когда вам нужно использовать VBA, вам придется написать для него некоторый код.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Этот код работает двумя способами: во-первых, установка сводного кэша с использованием источника данных, а во-вторых, установка адреса ячейки на вновь вставленном листе для вставки сводной таблицы.

Вы можете изменить положение сводной таблицы, изменив этот код.

5. Вставьте пустую сводную таблицу.

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

Этот код сделает то же самое:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Этот код создает пустую сводную таблицу и называет ее «SalesPivotTable». Вы можете изменить это имя из самого кода.

6. Вставьте поля строк и столбцов.

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

Для каждого поля строки и столбца необходимо написать код. Здесь мы хотим добавить годы и месяцы в поле строки и области в поле столбца.

Вот код:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

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

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

7. Вставьте поле данных

Главное — определить поле значения в сводной таблице.

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

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Вы можете добавить сумму в качестве поля значения с помощью приведенного выше кода. И этот код отформатирует значения как число с разделителем (,).

Мы используем xlsum для сложения значений, но вы также можете использовать xlcount и другие функции.

8. Отформатируйте сводную таблицу

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

С помощью VBA вы можете установить стиль форматирования в коде.

Код:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

В приведенном выше коде будут применены полоски линий и стиль «Pivot Style Medium 9», но вы также можете использовать другой стиль по этой ссылке .

Наконец, ваш код готов к использованию.

[ПОЛНЫЙ КОД] Используйте VBA для создания сводной таблицы в Excel – копирование и вставка макроса

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Сводная таблица на существующем листе

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

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

Не волнуйтесь; Я покажу тебе.

Сначала вам нужно указать лист (уже в книге), куда вы хотите вставить сводную таблицу.

И для этого вы должны использовать код ниже:

Вместо вставки нового листа вы должны указать имя листа в переменной PSheet.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

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

Когда вы вставляете сводную таблицу в существующий лист, возможно, там уже есть сводная таблица с таким же именем.

Я говорю о том, что сначала вам нужно удалить этот стержень.

Для этого нужно добавить код, который должен удалять одноименный стержень с рабочего листа (если он там есть) перед вставкой нового.

Вот код, который вам нужно добавить:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Позвольте мне рассказать вам, что делает этот код.

Во-первых, он просто устанавливает PSheet в качестве листа, на который вы хотите вставить сводную таблицу, уже в вашей книге, а рабочие листы данных задает как DSheet.

После этого он активирует рабочий лист и удаляет из него «Сводную таблицу продаж».

Важно: Если имена листов в вашей книге различаются, вы можете изменить их из кода. Я выделил код, где вам нужно это сделать.

В конце,

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

Вы когда-нибудь использовали код VBA для создания сводной таблицы?

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

Похожие статьи:

• Добавить или удалить общий итог в сводной таблице в Excel
• Добавить промежуточную сумму в сводную таблицу в Excel
• Автоматически обновлять сводную таблицу в Excel
• Добавить вычисляемое поле и элемент
• Удалить сводную таблицу в Excel