VBA Pivot Table - Как да създадете Pivot таблица в Excel с помощта на VBA код?

• Excel VBA Pivot Table

Excel VBA Pivot Table

Основната таблица на VBA ви помага да обобщавате отчети от голям набор от данни. Pivot може да се разглежда като мъничка форма на целия набор от данни. Бързият преглед на голям набор от данни е възможен чрез въртяща се таблица. Една въртяща се таблица е лесен начин за съответно филтриране на данните. От наличните данни можете да маркирате данните по желания от вас начин. Една въртяща се таблица ви позволява да комбинирате огромния анализ на данните и да изготвяте отчети, които отговарят на вашите бизнес изисквания.

Excel предлага вградена въртяща се таблица, която е лесно да се създаде чрез прилагане на въртяща се таблица върху лист с данни. Възможно е да се генерира отчет автоматично в excel, след като данните бъдат предоставени. VBA кодовете ще ви помогнат да създадете автоматична въртяща се таблица.

Как да създадете оборотна таблица с помощта на Excel VBA?

Общите стъпки за вмъкване на въртяща се таблица е първо да поставите въртяща се таблица от менюто „Вмъкване“, след това да изберете таблиците, които искате да промените, във въртяща се таблица. Избраната таблица ще се превърне в изходните данни и съответно ще се създаде въртящата се таблица.

За да създадете оборотен отчет, трябва да изберете полетата за филтрите, стойностите и т.н. По подобен начин тези стъпки могат да бъдат автоматизирани с помощта на кодовете VBA. Необходими са ни данни за екселски лист, за да работим с въртяща се таблица. Нека научим как да създадем въртяща се таблица чрез кодовете на VBA на Excel.

Можете да изтеглите този шаблон на VBA Pivot Table Excel тук - VBA Pivot Table Excel шаблон

Стъпка 1 : Таблицата за завъртане трябва да се разглежда като референтен обект. Създайте обект за въртящата се таблица на други кодове, този обект ще бъде използван за насочване на въртящата се таблица. Име на променливата като pvtable. Деклариране на обект на функция и въртяща се таблица.

Код:

 Sub PivotTable () Dim pvtable като PivotTable End Sub 

Стъпка 2: За да запазим файловете с данни, ни е необходим въртящ кеш, за да декларираме източника на данните. Преди да създадете въртяща се таблица, декларирайте въртяща се променлива пари. Декларирайте променливата pvcache за въртящ кеш.

Код:

 Dim pvcache като PivotCache 

Стъпка 3: Данните са в работния лист и трябва да посочват диапазон, за да се удари в клетката, която ви е необходима. В въртящата се таблица изходните данни се разпространяват като редове и колони, така че за да насочим към определен диапазон, се нуждаем от променлива. Определете pvrange като променлива на обхвата.

Код:

 Dim pvrange As Range 

Стъпка 4: Работен лист трябва да поставите въртящата се таблица, която трябва да създадете. Декларирайте променлива като работен лист. Определете pvsheet като работен лист.

Код:

 Dim pvsheet като работен лист 

Стъпка 5: Имате нужда от подобна променлива, за да използвате листа с данни, който трябва да съдържа данните, които искате да очертаете като въртяща се таблица. Така променливата на информационния лист е декларирана като pdsheet.

Код:

 Dim 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 "При грешка GoTo 0 

Стъпка 8: Задайте променлива на обекта за въртящия се лист и информационния лист съответно на променливите pvsheet и pdsheet. Това ще бъде използвано допълнително за уточняване на работните листове.

Код:

 Set 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 “) .Orientation = xlRowField .Position = 1 End with 

Стъпка 14: Следващото е да посочите второто поле, което искате да вмъкнете в въртящата се таблица. По същия начин вмъкнете полевата улица от втори ред към въртящата се таблица.

Код:

 „Вмъкване на улица в ред и файл 2 с pvsheet.PivotTables („ Sales_Report “). PivotFields („ Street “) .Orientation = xlRowField .Position = 2 End with 

Стъпка 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 = 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 As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Workheet Dim pdsheet As Worksheet Dim plr As Long Dim plc като Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = Фалшиви работни листове (" ") .Delete", за да изтриете съществуващата въртяща се таблица в работния лист. Добавете след: = ActiveSheet ", за да добавите нов работен лист ActiveSheet.Name =" pvsheet "', за да преименувате работния лист в" pvsheet "при грешка GoTo 0 Set pvsheet = Работни листове ("pvsheet") Задайте pdsheet = Работни листове ("pdsheet") "две променливи, за да намерите последния използван ред и колона в pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Клетки (1, Column.Count) .End (xlToLeft) .Column 'инициализиране на диапазона от данни на въртящата се таблица Set 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 ("Street"). Ориентация = xlRowField .Position = 2 Край с "Вмъкване на град в колона Filed With pvsheet.PivotTables (" Sales_Report "). PivotFields (" Град ") .Ориентация = xlColumnField .Position = 1 Завършете с" Вмъкване на графа Продажби в полето с данни с pvsheet.PivotTables ("Продажби_Репортаж"). PivotFields ("Продажби"). формат Pivot Table pvsheet.PivotTables ("Продажби_Репортиране"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" ', за да се покаже въртящата се таблица в таблична форма pvsheet.PivotTawTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTailTaiTaTaTaTaTaTaTaTaTavTaTaTavTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTaTa ""> "Показване на оборотна таблица в таблична форма. .DisplayAlerts = True Application.ScreenUpdating = True End Sub 

Неща, които трябва да запомните

• Посочете оригиналния лист, където данните трябва да се предоставят за въртяща се таблица.
• Началният ред и колоната трябва да бъдат споменати правилно.
• Използвайте необходимите обекти, за да определите въртящия кеш, обхвата, началото и края на редовете.
• Подредете източника на данни с подходящ формат, тъй като това е процесът на автоматизация.

Препоръчителни статии

Това е ръководство за VBA Pivot Table. Тук обсъждаме как да създадем въртяща се таблица, използвайки VBA кодове в excel, заедно с пример и свалящ се шаблон на excel. Можете също да разгледате следните статии, за да научите повече -

1. Как да изтриете въртящата се таблица в Excel?
2. Използване на VBA в PowerPoint
3. Въртяща се маса с множество листове
4. Как да създадете шлиц на въртяща се таблица?
5. Обновяване на оборотна таблица в VBA
6. VBA при грешка Възобновете следващата (Примери)
7. Обновяване на Excel VBA