Макрос для создания сводной таблицы в Excel

Обновлено: 21.11.2024

Дж.А. Гомес

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

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

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

Оглавление

Похожие руководства по VBA и макросам

Следующие учебные пособия по VBA и макросам могут помочь вам лучше понять и реализовать приведенное ниже содержание:

Общие конструкции и структуры VBA:
- Подробнее о часто используемых терминах VBA можно узнать здесь.
- Узнайте больше об объектной модели Excel VBA здесь.
- Узнайте, как работать с переменными, здесь.
- Подробнее о типах данных здесь.
- Подробнее об эталонном стиле R1C1 можно узнать здесь.
- Узнайте, как создать новую книгу здесь.
- Узнайте, как найти последний столбец с данными здесь.
- Узнайте больше о работе с листами здесь.
Дополнительные учебные пособия по VBA и макросам можно найти в архивах.

Код VBA для создания сводной таблицы на существующем листе

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA
Используйте такие свойства, как Application.Workbooks, Application.ThisWorkbook и Application.ActiveWorkbook, чтобы вернуть этот объект Workbook.
Используйте константы в перечислении xlPivotTableSourceType для указания другого источника данных. Тем не менее установка для SourceType значения xlPivotTable (представляющего тот же источник данных, что и другая сводная таблица) или xlScenario (представляющего сценарии, созданные с помощью диспетчера сценариев) обычно приводит к ошибке во время выполнения.
Если вы используете структуру операторов, указанную в этом руководстве по VBA, и явно объявляете переменные для представления SourceWorksheetName и SourceDataAddress, используйте тип данных String. В этой структуре SourceData указывается следующим образом:
При необходимости используйте свойство Worksheet.Name, чтобы вернуть строку, представляющую имя рабочего листа.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.

SourceData имеет тип данных Variant. Однако в документации Microsoft рекомендуется следующее:
Если вы используете структуру оператора, указанную в этом руководстве по VBA, и явно объявляете переменные для представления DestinationWorksheetName и DestinationRangeAddress, используйте тип данных String. В этой структуре TableDestination указывается следующим образом:
При необходимости используйте свойство Worksheet.Name, чтобы вернуть строку, представляющую имя рабочего листа.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.
Пример макроса

Следующий макрос создает новую сводную таблицу на существующем листе (сводная таблица).

Эффекты выполнения примера макроса

Следующий GIF иллюстрирует результаты выполнения этого примера макроса. Как и ожидалось, макрос создает сводную таблицу на листе «Сводная таблица».

Код VBA для создания сводной таблицы на новом листе

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA
1. Элемент: Dim DestinationWorksheet как рабочий лист.
Используйте такие свойства, как Application.Workbooks, Application.ThisWorkbook и Application.ActiveWorkbook, чтобы вернуть этот объект Workbook.
Используйте константы в перечислении xlPivotTableSourceType для указания другого источника данных. Тем не менее установка для SourceType значения xlPivotTable (представляющего тот же источник данных, что и другая сводная таблица) или xlScenario (представляющего сценарии, созданные с помощью диспетчера сценариев) обычно приводит к ошибке во время выполнения.
Если вы используете структуру операторов, указанную в этом руководстве по VBA, и явно объявляете переменные для представления SourceWorksheetName и SourceDataAddress, используйте тип данных String. В этой структуре SourceData указывается следующим образом:
При необходимости используйте свойство Worksheet.Name, чтобы вернуть строку, представляющую имя рабочего листа.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.

SourceData имеет тип данных Variant. Однако в документации Microsoft рекомендуется следующее:
Если вы используете структуру оператора, указанную в этом руководстве по VBA, и явно объявляете переменную для представления DestinationRangeAddress, используйте тип данных String. В этой структуре TableDestination указывается следующим образом:
Возвращает строку, представляющую имя DestinationWorksheet. DestinationWorksheet — это новый рабочий лист, на котором находится созданная вами сводная таблица.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.
Пример макроса

Следующий макрос создает новую сводную таблицу на новом листе.

Эффекты выполнения примера макроса

Следующий GIF иллюстрирует результаты выполнения этого примера макроса. Как и ожидалось, макрос создает сводную таблицу на новом листе (Лист4).

Код VBA для создания сводной таблицы в новой книге

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA
1. Элемент: Dim DestinationWorkbook как Workbook.
Используйте константы в перечислении xlPivotTableSourceType для указания другого источника данных. Тем не менее установка для SourceType значения xlPivotTable (представляющего тот же источник данных, что и другая сводная таблица) или xlScenario (представляющего сценарии, созданные с помощью диспетчера сценариев) обычно приводит к ошибке во время выполнения.
Если вы используете структуру оператора, указанную в этом руководстве по VBA, и явно объявляете переменные для представления SourceWorkbookName, SourceWorksheetName и SourceDataAddress, используйте тип данных String. В этой структуре SourceData указывается следующим образом:
При необходимости используйте свойство Workbook.Name, чтобы вернуть строку, представляющую имя книги.

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

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.

SourceData имеет тип данных Variant. Однако в документации Microsoft рекомендуется следующее:
Если вы используете структуру оператора, указанную в этом руководстве по VBA, и явно объявляете переменную для представления DestinationRangeAddress, используйте тип данных String. В этой структуре TableDestination указывается следующим образом:
Возвращает строку, представляющую имя DestinationWorkbook. DestinationWorkbook — это новая рабочая книга, в которой находится созданная вами сводная таблица.

Свойство Workbook.Worksheets (DestinationWorkbook.Worksheets(1)) возвращает объект Worksheet, представляющий первый рабочий лист (Worksheets(1)) DestinationWorkbook. Свойство Worksheet.Name возвращает строку, представляющую имя этого рабочего листа, на котором находится созданная вами сводная таблица.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.
Пример макроса

Следующий макрос создает новую сводную таблицу в новой книге.

Эффекты выполнения примера макроса

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

Код VBA для создания сводной таблицы из динамического диапазона

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA
1. Элемент: затемнить LastColumn As Long.
1. Элемент: Dim SourceDataAddress As String.
LastColumn содержит номер последнего столбца с данными в SourceWorksheet.
Используйте такие свойства, как Application.Workbooks, Application.ThisWorkbook и Application.ActiveWorkbook, чтобы вернуть этот объект Workbook.
Используйте константы в перечислении xlPivotTableSourceType для указания другого источника данных. Тем не менее установка для SourceType значения xlPivotTable (представляющего тот же источник данных, что и другая сводная таблица) или xlScenario (представляющего сценарии, созданные с помощью диспетчера сценариев) обычно приводит к ошибке во время выполнения.
Если вы используете структуру операторов, указанную в этом руководстве по VBA, и явно объявляете переменные для представления SourceWorksheetName и SourceDataAddress, используйте тип данных String. В этой структуре SourceData указывается следующим образом:
При необходимости используйте свойство Worksheet.Name, чтобы вернуть строку, представляющую имя рабочего листа.

SourceDataAddress содержит адрес диапазона ячеек, содержащего исходные данные.
Если вы используете структуру оператора, указанную в этом руководстве по VBA, и явно объявляете переменные для представления DestinationWorksheetName и DestinationRangeAddress, используйте тип данных String. В этой структуре TableDestination указывается следующим образом:
При необходимости используйте свойство Worksheet.Name, чтобы вернуть строку, представляющую имя рабочего листа.

При необходимости используйте свойство Range.Address, чтобы вернуть строку, представляющую ссылку на диапазон ячеек.
Пример макроса

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

Эффекты выполнения примера макроса

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

Блог, ориентированный в основном на Microsoft Excel, PowerPoint и Word, со статьями, призванными вывести ваши навыки анализа данных и работы с электронными таблицами на новый уровень. Научитесь всему: от создания информационных панелей до автоматизации задач с помощью кода VBA!

Все о сводных таблицах!

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

Создать сводную таблицу

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'Определите диапазон данных, который вы хотите свести
SrcData = ActiveSheet.Name & "!" & Диапазон("A1:R100").Адрес(ReferenceStyle:=xlR1C1)

'Создать новый лист
Set sht = Sheets.Add

'С чего вы хотите начать работу со сводной таблицей?
StartPvt = шт.Имя & "!" & шт.Диапазон("A3").Адрес(ReferenceStyle:=xlR1C1)

'Создать сводной кэш из исходных данных
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'Создать сводную таблицу из сводного кэша
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")

Удалить определенную сводную таблицу

'Удалить сводную таблицу по имени
ActiveSheet.PivotTables("PivotTable1").TableRange2.Clear

Удалить все сводные таблицы

Размерить как рабочий лист
Размерить как сводную таблицу

'Цикл по каждой сводной таблице в текущей просматриваемой книге
Для каждого листа в ActiveWorkbook.Worksheets
Для каждого pvt в файле sht.PivotTables
pvt.TableRange2.Clear
Следующий pvt
Следующее

Добавить сводные поля

Затемнить pvt как сводную таблицу

Установите pvt = ActiveSheet.PivotTables("Сводная таблица1")

'Добавить элемент в фильтр отчета
pvt.PivotFields("Year").Orientation = xlPageField

'Добавить элемент в метки столбцов
pvt.PivotFields("Месяц").Orientation = xlColumnField

'Добавить элемент в метки строк
pvt.PivotFields("Account").Orientation = xlRowField

'Позиция элемента в списке
pvt.PivotFields("Year").Position = 1

'Включить автоматические обновления/расчеты -- например, обновление экрана для ускорения кода
pvt.ManualUpdate = False

Добавить вычисляемые сводные поля

Затемнить pvt как сводную таблицу
Затемнить pf как сводное поле

'Установить переменную в желаемую сводную таблицу
Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Установить переменную, равную желаемому вычисляемому полю сводной таблицы
Для каждого pf в pvt.PivotFields
Если pf.SourceName = "Inflation", то выйти для
Next

'Добавить вычисляемое поле в сводную таблицу
pvt.AddDataField pf

Добавить поле значений

Dim pvt As PivotTable
Dim pf As String
Dim pf_Name As String

pf = "Зарплаты"
pf_Name = "Сумма зарплат"

Установите pvt = ActiveSheet.PivotTables("Сводная таблица1")

pvt.AddDataField pvt.PivotFields("Зарплата"), pf_Name, xlSum

Удалить сводные поля

'Удаление фильтра, столбцов, строк
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").Orientation = xlHidden

'Удаление значений
ActiveSheet.PivotTables("PivotTable1").PivotFields("Сумма зарплат").Orientation = xlHidden

Удалить вычисляемые сводные поля

Dim pvt As PivotTable
Dim pf As PivotField
Dim pvt As PivotItem

'Установить переменную в желаемую сводную таблицу
Set pvt = ActiveSheet.PivotTables("PivotTable1")

'Установить переменную, равную требуемому полю вычисляемых данных
Для каждого pf в pvt.DataFields
Если pf.SourceName = "Inflation", то выйти для
Next

'Скрыть/удалить вычисляемое поле
pf.DataRange.Cells(1, 1).PivotItem.Visible = False

Фильтр отчетов по одному элементу

Затемнить pf как PivotField

Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")

'Очистить все предыдущие фильтры
pf.ClearAllFilters

'Фильтровать элементы 2014 года
pf.CurrentPage = "2014"

Фильтр отчетов по нескольким элементам

Затемнить pf как PivotField

Установите pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Variance_Level_1")

'Очистить все предыдущие фильтры
pf.ClearAllFilters

'Включить фильтрацию нескольких элементов
pf.EnableMultiplePageItems = True

'Необходимо отключить элементы, которые вы не хотите показывать
pf.PivotItems("Янв").Visible = False
pf.PivotItems("Фев").Visible = False
pf .PivotItems("Мар").Visible = False

Очистить фильтр отчета

Затемнить pf как PivotField

Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")

'Вариант 1: очистить все предыдущие фильтры
pf.ClearAllFilters

'Вариант 2: Показать все (удалить фильтрацию)
pf.CurrentPage = "(All)"

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

'Обновить одну сводную таблицу
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

'Обновить все сводные таблицы
ActiveWorkbook.RefreshAll

Изменить диапазон источников данных сводной таблицы

Dim sht As Worksheet
Dim SrcData As String
Dim pvtCache As PivotCache

'Определите диапазон данных, который вы хотите свести.
Set sht = ThisWorkbook.Worksheets("Sheet1")
SrcData = sht.Name & "!" & Диапазон("A1:R100").Адрес(ReferenceStyle:=xlR1C1)

'Создать новый сводной кэш из исходных данных
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

Общие итоги

Затемнить pvt как сводную таблицу

Установите pvt = ActiveSheet.PivotTables("Сводная таблица1")

'Выключено для строк и столбцов
pvt.ColumnGrand = False
pvt.RowGrand = False

'Включено для строк и столбцов
pvt.ColumnGrand = True
pvt.RowGrand = True

'Включено только для строк
pvt.ColumnGrand = False
pvt.RowGrand = True

'Включено только для столбцов
pvt.ColumnGrand = True
pvt.RowGrand = False

Макет отчета

Затемнить pvt как сводную таблицу

Установите pvt = ActiveSheet.PivotTables("Сводная таблица1")

'Показать в компактной форме
pvt.RowAxisLayout xlCompactRow

'Показать в форме схемы
pvt.RowAxisLayout xlOutlineRow

'Показать в табличной форме
pvt.RowAxisLayout xlTabularRow

Форматирование данных сводной таблицы

Затемнить pvt как сводную таблицу

Установите pvt = ActiveSheet.PivotTables("Сводная таблица1")

'Изменить цвет заливки данных
pvt.DataBodyRange.Интерьер.Цвет = RGB(0, 0, 0)

'Изменить тип шрифта данных
pvt.DataBodyRange.Font.FontStyle = "Arial"

Форматирование данных сводного поля

Затемнить pf как PivotField

Установите pf = ActiveSheet.PivotTables("Сводная таблица1").PivotFields("Месяцы")

'Изменить цвет заливки данных
pf.DataRange.Interior.Color = RGB(219, 229, 241)

'Изменить тип шрифта данных
pf.DataRange.Font.FontStyle = "Arial"

Развернуть/свернуть все сведения о поле

Затемнить pf как PivotField

Установите pf = ActiveSheet.PivotTables("Сводная таблица1").PivotFields("Месяц")

'Свернуть сводное поле
pf.ShowDetail = False

'Развернуть сводное поле
pf.ShowDetail = True

Больше замечательных сообщений о сводных таблицах VBA
- Быстро изменить расчет поля сводной таблицы с количества на сумму
- Динамическое изменение диапазона источников данных сводной таблицы
- Динамическое изменение каждого диапазона источников данных сводной таблицы внутри книги
- 5 различных способов найти последнюю строку или последний столбец с помощью VBA
- Фильтрация разворотов на основе внешних диапазонов (DailyDoseOfExcel)
Есть ли другие функции, которые вы хотели бы видеть?

Я считаю, что смог охватить в этой статье все основные функции сводных таблиц VBA, но со сводными таблицами можно сделать так много! Оставьте комментарий ниже, если вы хотели бы увидеть что-то еще, описанное в этом руководстве.

Познакомьтесь с моей новой надстройкой Excel, которая позволяет автоматизировать создание ежемесячных слайдов PowerPoint!

Как изменить это в соответствии со своими потребностями?

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

Я настоятельно рекомендую вам ознакомиться с этим руководством, прежде чем просить меня или кого-либо еще в разделе комментариев решить вашу конкретную проблему. Я могу гарантировать, что в 9 случаях из 10 одна из моих стратегий даст вам ответы, которые вам нужны, быстрее, чем мне потребуется, чтобы вернуться к вам с возможным решением. Я изо всех сил стараюсь помочь всем, но иногда у меня нет времени ответить на все вопросы (кажется, часов в сутках никогда не бывает достаточно!).

Желаю вам удачи и надеюсь, что это руководство направит вас в правильном направлении!

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

Рисунок 1. Как создать сводную таблицу с помощью VBA

Преобразование данных в таблицу
- Мы преобразуем наши данные в таблицу, нажав «Вставить», а затем «Таблица».
Рисунок 2. Нажмите на таблицу

Рисунок 3. Диалоговое окно "Создать таблицу"
- Мы нажмем "ОК".
- Далее мы назовем нашу таблицу как SalesPivotTable в поле имени под файлом, как показано ниже.
Рисунок 4. Таблица данных
- Мы переименуем наш лист в «Данные».
Этапы написания кода VBA

Объявление переменных

Нам нужно объявить переменные в коде, чтобы определить различные аспекты:
- PSheet: это означает, что мы хотим создать лист для новой сводной таблицы.
- DSheet: применение таблицы данных.
- PChache: применяется как имя для кеша сводной таблицы.
- PTable: присвойте имя нашей сводной таблице.
- PRange: определяет диапазон исходных данных (диапазон нашей таблицы, A3:F61)
- LastRow и LastCol: мы используем их для получения последней строки и столбца нашего диапазона данных.
Рисунок 5. Объявление всех переменных

Вставить новый рабочий лист

Мы создадим код для Excel, чтобы разместить нашу сводную таблицу на чистом листе. С помощью этого кода мы вставим новый рабочий лист под названием «Сводная таблица». Наши значения будут установлены как переменная PSheet на Рабочий лист сводной таблицы и DSheet на Рабочий лист исходных данных . Всякий раз, когда мы хотим изменить имя рабочего листа, мы можем сделать это из кода. Мы должны отметить, что если есть рабочий лист с именем PivotTable, код удалит его перед созданием сводной таблицы с кодом VBA.

Рисунок 6. Вставка нового рабочего листа

Определение диапазона данных

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

Рисунок 7. Определение диапазона данных

Создать сводную кэш-память

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

Создать пустую сводную таблицу

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

Рисунок 9. Создание пустой сводной таблицы

Вставить строки и столбцы

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

Рисунок 10. Вставка строк и столбцов

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

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

Рисунок 11. Вставка поля данных

Формат сводной таблицы

Наконец, нам также понадобится код для форматирования нашей сводной таблицы, особенно когда мы хотим изменить стиль форматирования сводной таблицы в коде. Для нашего примера мы применим полосы строк и «Стиль среднего 9».

Рисунок 12. Формат сводной таблицы

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

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

Рисунок 13. Запуск кода макроса
- Когда мы нажимаем RUN , нам сразу же открывается поле сводной таблицы, здесь мы выбираем «Другие таблицы», затем «Да».
Рисунок 14. Выбор рабочего листа
- Наконец, мы выбираем «SalesPivotTable» и нажимаем «ОК».
Рисунок 15. Готовая сводная таблица с кодом макроса

Сводные таблицы — это ключевой инструмент для многих пользователей Excel для анализа данных. Они гибкие и простые в использовании.Объедините мощь сводных таблиц с автоматизацией VBA, и мы сможем анализировать данные еще быстрее. Этот пост содержит необходимый код для управления сводными таблицами с помощью VBA.

Загрузить файл примера

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

Загрузите файл: 0047 Excel VBA для сводных таблиц.zip

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

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

Обновить сводную таблицу

Следующий код обновляет одну сводную таблицу с именем PivotTable1. Измените значение переменной pvtName на имя вашей сводной таблицы.

Обновить все сводные таблицы в активной книге

Следующий фрагмент кода обновляет все сводные таблицы в активной книге.

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

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

Создать сводную таблицу
- Лист, содержащий исходные данные
- Диапазон, содержащий исходные данные.
- Лист, на котором размещается сводная таблица.
- Ссылка на ячейку для размещения сводной таблицы.
В приведенном выше примере используются стандартные диапазоны Excel. Чтобы использовать таблицу Excel в качестве источника, мы можем использовать имя таблицы без ссылки на лист.

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

В этом разделе представлены два варианта удаления сводных таблиц.

Удалить одну сводную таблицу

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

Удалить все сводные таблицы в книге

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

Изменить источник сводной таблицы

Этот макрос изменяет исходные данные для сводной таблицы.

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

Отключить автоподбор ширины столбца во всех сводных таблицах

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

Добавление и удаление столбцов, строк и значений в сводной таблице

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

Добавить поля в сводную таблицу

Добавить поле в столбцы сводной таблицы

Поле положения в фильтрах сводной таблицы

Удалить поле из значений сводной таблицы

Положение поля в сводной таблице

Добавить поле в раздел значений сводной таблицы

Удалить поле из раздела значений сводной таблицы

Очистить фильтры сводных таблиц

Применить фильтр к сводной таблице

Применить несколько фильтров к сводной таблице

Добавить вычисляемое поле

Удалить все поля из сводной таблицы

Получите БЕСПЛАТНУЮ электронную книгу VBA с 30 наиболее полезными макросами Excel VBA.

Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.

Не забывайте:

Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.

Вам нужна помощь в адаптации этого к вашим потребностям?

Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.
1. Читайте другие блоги или смотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, найдя собственные решения.
2. Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
3. Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
4. Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.
Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:

Читайте также:

Макрос для создания сводной таблицы в Excel

Похожие руководства по VBA и макросам

Код VBA для создания сводной таблицы на существующем листе

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA

Пример макроса

Эффекты выполнения примера макроса

Код VBA для создания сводной таблицы на новом листе

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA

Пример макроса

Эффекты выполнения примера макроса

Код VBA для создания сводной таблицы в новой книге

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA

Пример макроса

Эффекты выполнения примера макроса

Код VBA для создания сводной таблицы из динамического диапазона

Процесс, сопровождаемый кодом VBA

Объяснение оператора VBA

Пример макроса

Эффекты выполнения примера макроса

Все о сводных таблицах!

Создать сводную таблицу

Удалить определенную сводную таблицу

Удалить все сводные таблицы

Добавить сводные поля

Добавить вычисляемые сводные поля

Добавить поле значений

Удалить сводные поля

Удалить вычисляемые сводные поля

Фильтр отчетов по одному элементу

Фильтр отчетов по нескольким элементам

Очистить фильтр отчета

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

Изменить диапазон источников данных сводной таблицы

Общие итоги

Макет отчета

Форматирование данных сводной таблицы

Форматирование данных сводного поля

Развернуть/свернуть все сведения о поле

Больше замечательных сообщений о сводных таблицах VBA

Есть ли другие функции, которые вы хотели бы видеть?

Как изменить это в соответствии со своими потребностями?

Преобразование данных в таблицу

Этапы написания кода VBA

Объявление переменных

Вставить новый рабочий лист

Определение диапазона данных

Создать сводную кэш-память

Создать пустую сводную таблицу

Вставить строки и столбцы

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

Формат сводной таблицы

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

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

Обновить сводную таблицу

Обновить все сводные таблицы в активной книге

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

Создать сводную таблицу

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

Удалить одну сводную таблицу

Удалить все сводные таблицы в книге

Изменить источник сводной таблицы

Отключить автоподбор ширины столбца во всех сводных таблицах

Добавление и удаление столбцов, строк и значений в сводной таблице

Добавить поля в сводную таблицу

Добавить поле в столбцы сводной таблицы

Поле положения в фильтрах сводной таблицы

Удалить поле из значений сводной таблицы

Положение поля в сводной таблице

Добавить поле в раздел значений сводной таблицы

Удалить поле из раздела значений сводной таблицы

Очистить фильтры сводных таблиц

Применить фильтр к сводной таблице

Применить несколько фильтров к сводной таблице

Добавить вычисляемое поле

Удалить все поля из сводной таблицы