Створення зведених таблиць за допомогою VBA

• Використання DisplayAlerts разом із перемикачем .Delete і DisplayAlerts = False — це безпечний шаблон "видалити, якщо існує": спроба видалити аркуш, який не існує, зазвичай викликає помилку і зупиняє макрос, але On Error Resume Next наказує VBA тихо ігнорувати цю помилку; Error GoTo 0 вимикає власне спливаюче вікно Excel із підтвердженням "чи дійсно ви хочете видалити цей аркуш?";
• On Error Resume Next одразу після цього повертає стандартну обробку помилок — якщо залишити On Error Resume Next активним до кінця Sub, це призведе до того, що всі наступні, не пов’язані помилки також будуть проігноровані;
• ThisWorkbook.PivotCaches.Create створює знімок даних таблиці — PivotCache, а не саму PivotTable — саме з цього об’єкта будується кожна зведена таблиця;
• pc.CreatePivotTable перетворює знімок на видиму зведену таблицю, розміщену в комірці A3 на новому аркуші Pivot і названу ptProfitByRegion, щоб подальший код міг знайти її за ім’ям;
• PivotFields("Region").Orientation = xlRowField і рядок із Month нижче — це еквівалент перетягування Region у рядки й Month у стовпці списку полів;
• AddDataField заповнює область значень — другий аргумент ("Sum of Profit") — це підпис стовпця, а xlSum вказує додати значення.

Ось код для додавання шостого регіону як нового рядка за березень у tblReports, використовуючи ListRows.Add замість ручного введення:

Sub AddSixthRegion()
    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set newRow = tbl.ListRows.Add

    newRow.Range(1, 1).Value = "March"        ' Month
    newRow.Range(1, 2).Value = "Southwest"    ' Region
    newRow.Range(1, 3).Value = 41500          ' Sales
    newRow.Range(1, 4).Value = 28200          ' Expenses
    newRow.Range(1, 5).Value = 13300          ' Profit
    newRow.Range(1, 6).Value = 39000          ' Target
End Sub

Запустіть цей код один раз, а потім виконайте підпрограму RefreshProfitPivot, як раніше — тепер у зведеній таблиці має з’явитися "Southwest" як новий рядок поряд із North, South, East, West і Central, без жодних змін у макросі створення зведеної таблиці.

1. Виконання BuildProfitPivot без змін

• Скопіюйте підпрограму (Sub) точно так, як вона наведена в розділі, у свій модуль і виконайте її один раз.
• Перевірте Провідник проекту або вкладки аркушів — має з’явитися новий аркуш із назвою "Pivot", де регіони розташовані по рядках, а місяці — по стовпцях, із підсумком прибутку.

2. Додавання шостого регіону та лише оновлення

• Введіть новий рядок безпосередньо у робочий аркуш (не через код) — перейдіть у кінець tblReports і додайте рядок за березень для вигаданого регіону, наприклад "Southwest".
• Не запускайте повторно BuildProfitPivot — це видалить і повністю перебудує аркуш Pivot, що суперечить меті цієї вправи.
• Замість цього виконайте лише однорядковий оператор RefreshTable із розділу — потрібно звернутися до наявної зведеної таблиці за іменем, так само, як у прикладі оновлення з розділу.

3. Заміна полів і зміна підсумкового значення

• Потрібно змінити три рядки всередині блоку With pt: яке поле є xlRowField, яке — xlColumnField, і на яке поле вказує AddDataField.
• Дайте цій зміненій версії іншу назву підпрограми (Sub) і іншу TableName — використання тих самих імен, що й в оригіналі, призведе до помилки або тихого перезапису першої зведеної таблиці.
• Мітка, яку передаєте в AddDataField (другий аргумент, наприклад "Sum of Profit"), — це лише текст для відображення; змініть її відповідно до того, що саме ви зараз підсумовуєте.

Пункт 2 — після ручного додавання рядка для шостого регіону виконайте лише це:

Sub RefreshProfitPivot()
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
End Sub

Пункт 3 — окрема, змінена версія:

Sub BuildSalesPivotByMonth()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("SalesPivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "SalesPivot"

    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptSalesByMonth")

    With pt
        .PivotFields("Month").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
    End With
End Sub

Виконайте BuildSalesPivotByMonth, і ви отримаєте новий аркуш "SalesPivot" із місяцями по рядках, регіонами по стовпцях і підсумками продажів у тілі таблиці — дзеркальне відображення початкового макета.