Осева таблица на VBA - Стъпки за създаване на обобщена таблица в VBA

Съдържание

Обобщена таблица на Excel VBA

Обобщена таблица на Excel VBA

Осевите таблици са сърцето на обобщаването на отчета за голямо количество данни. Също така можем да автоматизираме процеса на създаване на обобщена таблица чрез VBA кодиране. Те са важна част от всеки отчет или табло за управление, в Excel е лесно да създавате таблици с бутон, но във VBA трябва да напишем някои кодове за автоматизиране на нашата обобщена таблица, преди Excel 2007 и по-старите му версии във VBA не е трябвало създайте кеш за обобщени таблици, но в Excel 2010 и по-новите му версии са необходими кешове.

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

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

Само с едно щракване на бутон можем да създаваме отчети.

Стъпки за създаване на обобщена таблица в VBA

За да създадете обобщена таблица, е важно да имате данни. За това създадох някои фиктивни данни. Можете да изтеглите работната книга, за да я следвате със същите данни.

Стъпка 1: Обобщената таблица е обект за препратка към обобщената таблица, декларира променливата като обобщени таблици.

Код:

Sub PivotTable () Затъмнете PTable като PivotTable End Sub

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

В обикновените работни листове, обобщената таблица, без да ни безпокои, ще създаде обобщен кеш във фонов режим. Но във VBA трябва да създаваме.

За това дефинирайте променливата a PivotCache.

Код:

Затъмнете PCache като PivotCache

Стъпка 3: За да определите обхвата на основните данни, дефинирайте променливата като диапазон.

Код:

Dim PRange As Range

Стъпка 4: За да вмъкнем обобщена таблица, се нуждаем от отделен лист, за да добавим работни листове, за обобщената таблица да декларира променливата като работен лист.

Код:

Затъмнете PSheet като работен лист

Стъпка 5: По същия начин, за да препратите данните, съдържащи работен лист, декларирайте още една променлива като Работен лист.

Код:

Затъмнете DSheet като работен лист

Стъпка 6: И накрая, за да намерите последния използван ред и колона, дефинирайте още две променливи като Long.

Код:

Dim LR As Long Dim LC As Long

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

Стъпка 8: Сега задайте обектната променлива PSheet и DSheet съответно на Pivot Sheet и Data Sheet.

Стъпка 9: Намерете последния използван ред и последната използвана колона в листа с данни.

Стъпка 10: Сега задайте обхвата на ос, като използвате последния ред и последната колона.

Това ще зададе обхвата на данните перфектно. Той автоматично ще избере диапазона от данни, дори ако има някакво добавяне или изтриване на данни в листа с данни.

Стъпка 11: Преди да създадем обобщена таблица, трябва да създадем обобщен кеш. Задайте променливата на обобщения кеш, като използвате VBA кода по-долу.

Стъпка 12: Сега създайте празна обобщена таблица.

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

Забележка: Изтеглете работната книга, за да разберете колоните с данни.

Стъпка 14: Сега, още един елемент ще вмъкна в полето на реда като елемент от втората позиция. Ще вмъкна продукта като втори ред в полето на реда.

Стъпка 15: След като вмъкнем колоните в полето на реда, трябва да вмъкнем стойности в полето на колоната. Ще вмъкна „Сегмент“ в полето на колоната.

Стъпка 16: Сега трябва да вмъкнем числа в полето за данни. Затова вмъкнете „Продажби“ в полето за данни.

Стъпка 17: Приключихме с обобщената част на обобщената таблица. Сега трябва да форматираме таблицата. За да форматирате обобщената таблица, използвайте кода по-долу.

Забележка: За да имате повече различни стилове на таблици, запишете ги макро и вземете стиловете на таблицата.

За да покажете елементите на стойностите в реда в таблична форма, добавете кода по-долу в долната част.

Добре, приключихме, ако стартираме този код с помощта на клавиша F5 или ръчно, тогава трябва да получим обобщената таблица по този начин.

По този начин, използвайки VBA кодиране, можем да автоматизираме процеса на създаване на обобщена таблица.

За справка дадох кода по-долу.

Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub