Зворотна таблиця VBA - Як створити зведену таблицю в Excel за допомогою коду VBA?

• Зворотна таблиця Excel VBA

Зворотна таблиця Excel VBA

Сводна таблиця VBA допомагає узагальнити звіти з великого набору даних. Зведення може розглядатися як крихітна форма всього набору даних. Швидкий перегляд великого набору даних можливий за допомогою зведеної таблиці. Зведена таблиця - це простий спосіб фільтрації даних відповідно. З наявних даних ви можете виділити дані так, як вам потрібно. Зведена таблиця дозволяє поєднувати величезні дані, аналізувати дані та створювати звіти, які відповідають вашим бізнес-вимогам.

У програмі excel є вбудована зведена таблиця, яку легко створити, застосувавши поворотну таблицю над таблицею даних. Можливо, згенерувати звіт автоматично в excel, коли дані будуть надані. Коди VBA допоможуть вам створити автоматичну зведену таблицю.

Як створити зведену таблицю за допомогою Excel VBA?

Загальні кроки для вставки зведеної таблиці - це спочатку вставити зведену таблицю з меню Вставка, а потім вибрати таблиці, які ви хочете змінити, у зведені таблиці. Вибрана таблиця стане вихідними даними і відповідно буде створена зведена таблиця.

Щоб скласти звітний звіт, ви повинні вибрати поля для фільтрів, значень тощо. Аналогічним чином ці кроки можна автоматизувати за допомогою кодів VBA. Для роботи зі зведеною таблицею нам потрібні дані листа Excel. Давайте дізнаємось, як створити зведену таблицю за допомогою кодів VBA Excel.

Ви можете завантажити цей шаблон Excel для зведеної таблиці VBA тут - Шаблон Excel зведеної таблиці VBA

Крок 1 : Сводна таблиця повинна розглядатися як опорний об'єкт. Створіть об’єкт для зведеної таблиці за подальшими кодами, цей об’єкт буде використовуватися для вказівки на зведену таблицю. Назвіть змінну як pvtable. Оголосіть функцію та об’єкт зведеної таблиці.

Код:

 Sub PivotTable () Розмір dim pvtable як PivotTable End Sub 

Крок 2. Для збереження файлів даних нам потрібен кешований кеш, щоб оголосити джерело даних. Перед створенням зведеної таблиці оголосіть змінну грошової змінної. Оголосити змінну pvcache для зведеного кешу.

Код:

 Розм. Pvcache як PivotCache 

Крок 3: Дані знаходяться в робочому аркуші і повинні вказати діапазон для потрапляння на потрібну клітинку. У зведеній таблиці вихідні дані поширюються у вигляді рядків та стовпців, тому для вказівки на певний діапазон нам потрібна змінна. Визначте pvrange як змінну діапазону.

Код:

 Тьмяний пвранж у діапазоні 

Крок 4: в робочий аркуш потрібно вставити зведену таблицю, яку потрібно створити. Задекларуйте змінну як робочий аркуш. Визначте pvsheet як робочий аркуш.

Код:

 Dim pvsheet As Workheet 

Крок 5: Вам потрібна подібна змінна, щоб використовувати таблицю даних, яка повинна містити дані, які ви хочете побудувати у вигляді зведеної таблиці. Отже змінна таблиця даних оголошується як pdsheet.

Код:

 Розмір темного листка як робочий лист 

Крок 6: Вам потрібні ще дві змінні як довгий тип даних, щоб вказати останній рядок та стовпець для створення зведеної таблиці. Це може бути будь-який рядок або стовпець, тож існує ймовірність бути числом рядків і стовпців, що перевищують цілі ліміти типу даних. Назвемо його PLR та PLC.

Код:

 Dim plr As Long Dim plc As Long 

Крок 7: Наступним кроком є ​​видалення, якщо вже створена зведена таблиця. Це допоможе уникнути плутанини, на якій таблиці викладаються вихідні дані. Тому видаліть попередній аркуш таблиці зведеної таблиці та створіть новий аркуш, щоб вставити зведену таблицю.

Код:

 On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Видалити ", щоб видалити існуючу зведену таблицю на робочих аркушах. Addd: = ActiveSheet ', щоб додати новий аркуш ActiveSheet.Name =" pvsheet "'для перейменування робочого аркуша в" pvsheet "On Error GoTo 0 

Крок 8: Призначте змінну об'єкта для обертового листа та аркуша даних відповідно змінним pvsheet та pdsheet. Це буде використано далі для визначення робочих аркушів.

Код:

 Встановити pvsheet = Робочі листи ("pvsheet") Встановити pdsheet = Робочі листи ("pdsheet") 

Крок 9: Після встановлення робочих таблиць наступним пунктом нам потрібен останній рядок та стовпець для створення зведеного звіту. Знайдіть останній використаний рядок та стовпець за допомогою оголошених змінних plr та plc.

Код:

 'дві змінної, щоб знайти Останній використаний рядок і стовпець у pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Column.Count) .End (xlToLeft) .Column 

Крок 10: Як ми вже згадували раніше, дані в робочому аркуші визначаються діапазоном комірок. На наступному кроці потрібно встановити діапазон зрізу. Вже задекларовано як змінну для використання діапазону повороту “pvrange”.

Код:

 'Ініціалізація діапазону даних зведеної таблиці Встановити pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc) 

Оскільки діапазон задається за допомогою властивості зміни розміру діапазонів комірок, то його зміна змінить pvrange відповідно. Таким чином, pvrange буде коригуватися чи є додавання чи видалення рядків чи стовпців.

Крок 11: Настав час встановити кешовий кеш, який є джерелом зведеної таблиці. Використовуйте об'єкт pvcache для встановлення вихідного кешу.

Код:

 'pivot cahe Встановити pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 

Крок 12: Тут звіт про продаж для різних товарів буде перетворений у зведену таблицю через це. Створіть зведену таблицю як порожню, до якої можна додатково додавати набір даних.

Код:

 'нова порожня зведена таблиця Встановити pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report") 

Крок 13: Після вставки зведеного вам потрібно вказати різні поля, які ви хочете вставити до зведеної таблиці. Тому вставте поле першого ряду. Тут перший рядок починається з продукту.

Код:

 "Вставити продукт у рядок, поданий за допомогою pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product "). Орієнтація = xlRowField .Position = 1 Кінець з 

Крок 14: Далі слід вказати друге поле, яке ви хочете вставити до зведеної таблиці. Таким же чином вставте вулицю поля другого ряду до зведеної таблиці.

Код:

 'Вставити вулицю в рядок Подано в позицію 2 З допомогою pvsheet.PivotTables ("Sales_Report"). Зворотні поля ("Street"). Орієнтація = xlRowField .Position = 2 Кінець з 

Крок 15: Вставте наступне поле до зведеної таблиці, а наступне - місто. Дайте код, щоб вставити місто.

Код:

 "Вставити місто в стовпчик, поданий за допомогою pvsheet.PivotTables (" Sales_Report "). PivotFields (" Town "). Орієнтація = xlColumnField .Position = 1 Кінець з 

Крок 16: До цього вставлені дані були текстовими. Тепер нам потрібно вставити кількість продажів у зведену таблицю.

Код:

 "Вставити стовпець" Продажі "в поле даних З допомогою pvsheet.PivotTables (" Sales_Report "). PivotFields (" Продажі ") .Orientation = xlDataField .Position = 1 End with 

Крок 17: Ви вставили поля, необхідні для створення зведеної таблиці. І зведена таблиця майже закінчена, тепер ви можете встановити формат зведеної таблиці. Це визначить тип таблиці через стиль таблиці. Макет рядкової осі також встановлюється так, як вам потрібно.

Код:

 'встановіть формат зведеної таблиці pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = Істинний 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 As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet As Workheet Dim plr As Long Dim plc As Long on Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheet ") .Delete ', щоб видалити існуючу зведену таблицю на робочому аркуші WorkDets.Add After: = ActiveSheet', щоб додати новий аркуш ActiveSheet.Name =" pvsheet "'для перейменування робочого листа в" pvsheet "On Error GoTo 0 Set pvsheet = Робочі таблиці ("pvsheet") Встановити pdsheet = Робочі таблиці ("pdsheet") 'дві змінні, щоб знайти останній рядок та стовпець у pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Осередки (1, стовпці. Кількість). Закінчення (xlToLeft). Стовпець 'ініціалізація діапазону даних зведеної таблиці Встановити pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'нова порожня зведена таблиця Встановити pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") "Вставити продукт у рядок, поданий за допомогою pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product ") .Orientation = xlRowField .Position = 1 End with 'Insert Street to Поданий рядок і позиція 2 З pvsheet.PivotTables ("Sales_Report"). PivotFields ("Вулиця"). Орієнтація = xlRowField .Position = 2 Кінець "Вставити місто до стовпця, поданий з pvsheet.PivotTables (" Sales_Report "). PivotFields (" Місто ") .Orientation = xlColumnField .Position = 1 Кінець" Вставити стовпець "Продажі" в поле даних З pvsheet.PivotTables ("Sales_Report"). PivotFields ("Продажі") .Orientation = xlDataField .Position = 1 End with 'set the the Формат зведеної таблиці pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" ', щоб показати таблицю зведення в табличній формі .DisplayAlerts = True Application.ScreenUpdating = Істинний кінець Sub 

Що потрібно пам’ятати

• Вкажіть аркуш джерела, куди слід подати дані для зведеної таблиці.
• Початковий рядок і стовпець повинні бути зазначені належним чином.
• Використовуйте необхідні об'єкти, щоб точно визначити кешовий кеш, діапазон, починаючи та закінчуючи рядки.
• Впорядкуйте джерело даних у відповідному форматі, оскільки це процес автоматизації.

Рекомендовані статті

Це посібник зі зведеної таблиці VBA. Тут ми обговорюємо, як створити зведену таблицю за допомогою кодів VBA у excel разом із прикладом та завантажуваним шаблоном excel. Ви також можете переглянути наступні статті, щоб дізнатися більше -

1. Як видалити зведену таблицю в Excel?
2. Використання VBA в PowerPoint
3. Зведена таблиця з кількома листами
4. Як створити зріз зведеної таблиці?
5. Оновити зведену таблицю в VBA
6. Наступна версія VBA щодо помилок (приклади)
7. Оновлення Excel VBA