Zaokretna tablica Excel VBA
Zaokretne tablice srce su sažimanja izvješća o velikoj količini podataka. Također možemo automatizirati postupak stvaranja zaokretne tablice putem VBA kodiranja. Oni su važan dio svakog izvješća ili nadzorne ploče, u Excelu je lako stvoriti tablice gumbom, ali u VBA moramo napisati neke kodove za automatizaciju naše stožerne tablice, prije Excela 2007 i njegovih starijih verzija u VBA-u nismo trebali stvoriti predmemoriju za pivot tablice, ali u Excelu 2010 i njenim novijim verzijama potrebne su predmemorije.
VBA može uštedjeti tone vremena za nas na našem radnom mjestu. Iako svladavanje nije tako lako, ali vrijedi potrošiti vrijeme da to naučite. Trebalo mi je 6 mjeseci da razumijem postupak stvaranja zaokretnih tablica putem VBA-a. Znate što mi je tih 6 mjeseci učinilo čuda jer sam napravio toliko grešaka pokušavajući stvoriti stožernu tablicu.
Ali stvar je stvar što sam naučio na svojim pogreškama i sada pišem ovaj članak da bih vam pokazao kako stvoriti zaokretne tablice pomoću koda.
Samo jednim klikom na gumb možemo stvoriti izvješća.
Koraci za izradu zaokretne tablice u VBA
Da biste stvorili zaokretnu tablicu, važno je imati podatke. Za to sam stvorio neke lažne podatke. Možete preuzeti radnu knjigu da biste je slijedili sa mnom s istim podacima.
Korak 1: Zaokretna tablica je objekt koji upućuje na to da pomična tablica deklarira varijablu kao zaokretne tablice.
Kodirati:
Pomoćna zaokretna tablica () Zatamni zaokretnu tablicu kao zaokretnu tablicu krajnja pod
Korak 2: Prije nego što prvo stvorimo zaokretnu tablicu, moramo stvoriti zaokretnu predmemoriju kako bismo definirali izvor podataka.
U redovitim radnim listovima, zaokretna tablica, bez da nas zabrinjava, stvorit će zaokretnu predmemoriju u pozadini. Ali u VBA moramo stvarati.
Za to definirajte varijablu PivotCache.
Kodirati:
Zatamni PCache kao PivotCache
Korak 3: Da biste odredili zaokretni raspon podataka, definirajte varijablu kao raspon.
Kodirati:
Dim PRange As Range
Korak 4: Da bismo umetnuli zaokretnu tablicu, potreban nam je zaseban list za dodavanje radnih listova za zaokretnu tablicu za deklariranje varijable kao radnog lista.
Kodirati:
Zatamni PSheet kao radni list
Korak 5: Slično tome, da biste uputili podatke koji sadrže radni list, još jednu varijablu prijavite kao Radni list.
Kodirati:
Zatamni DSheet kao radni list
Korak 6: Konačno, da biste pronašli zadnji upotrijebljeni redak i stupac, definirajte još dvije varijable kao Long.
Kodirati:
Dim LR as Long Dim LC As Long
Korak 7: Sada moramo umetnuti novi list da bismo stvorili zaokretnu tablicu. Prije toga, ako postoji bilo koji zaokretni list, to moramo izbrisati.
Korak 8: Sada postavite objektnu varijablu PSheet i DSheet na Pivot Sheet i Data Sheet.
Korak 9: Pronađite zadnji korišteni redak i zadnji upotrijebljeni stupac u podatkovnom listu.
Korak 10: Sada postavite raspon zakretanja pomoću zadnjeg retka i zadnjeg stupca.
To će savršeno postaviti raspon podataka. Automatski će odabrati raspon podataka, čak i ako postoji bilo kakvo dodavanje ili brisanje podataka u podatkovnom listu.
11. korak: Prije nego što stvorimo zaokretnu tablicu, moramo stvoriti zaokretnu predmemoriju. Postavite varijablu zaokretne predmemorije pomoću dolje navedenog VBA koda.
Korak 12: Sada stvorite praznu zaokretnu tablicu.
Korak 13: Nakon umetanja zaokretne tablice, prvo moramo umetnuti polje retka. Zato ću umetnuti polje retka kao svoj stupac Zemlja.
Napomena: Preuzmite radnu knjigu da biste razumjeli stupce podataka.
Korak 14: Sada ću još jednu stavku umetnuti u polje reda kao stavku druge pozicije. Umetnut ću Proizvod kao drugu stavku retka u polje retka.
Korak 15: Nakon umetanja stupaca u polje retka, trebamo umetnuti vrijednosti u polje stupca. U polje stupca umetnut ću "Segment".
Korak 16: Sada moramo umetnuti brojeve u podatkovno polje. Dakle, umetnite "Prodaja" u podatkovno polje.
Korak 17: Gotovi smo sa dijelom sažetka pivot tablice. Sada moramo oblikovati tablicu. Za formatiranje zaokretne tablice upotrijebite donji kod.
Da biste prikazali stavke vrijednosti u retku u tabličnom obliku, dodajte donji kod na dnu.
U redu, gotovi smo ako pokrenemo ovaj kod pomoću tipke F5 ili ručno, trebali bismo dobiti ovakvu pivot tablicu.
Na ovaj način, pomoću VBA kodiranja, možemo automatizirati postupak stvaranja zaokretne tablice.
Za vašu referencu dao sam donji kod.
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 List "). Delete" Ovo će izbrisati postojeće radne listove zaokretne tablice. Dodati nakon: = ActiveSheet 'To će dodati novi radni list ActiveSheet.Name = "Okretni list"' Ovim će se radni list preimenovati u "Zaokretni list" Pri pogrešci GoTo 0 Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Pronađi zadnji korišteni redak i stupac u podatkovnom listu LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Postavite raspon podataka zaokretne tablice Postavite PRange = DSheet.Cells (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Stvori praznu zaokretnu tablicu Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Ćelije (1, 1), TableName: = "Report_Report") 'Umetnite zemlju u redak arhiviranu pomoću PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 Završite s' Umetnite proizvod u Red stavljen i pozicija 2 s PSheet.PivotTables ("Report_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Završi s 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 Završi s 'Umetni stupac prodaje u podatkovno polje s PSheetom.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with 'Format Pivot Table PSheet.PivotTables ("Report_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_RetyTables2". "PivotStyleMedium14" 'Prikaži u tabličnom obliku PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
