Excel VBA okretna tablica
VBA okretna tablica pomaže vam da sažete izvješća iz velikog skupa podataka. Pivot se može smatrati sitnim oblikom cijelog skupa podataka. Brzi pregled velikog skupa podataka moguć je putem okretne tablice. Zaokretna tablica jednostavan je način filtriranja podataka. Iz dostupnih podataka podatke možete istaknuti na način koji želite. Zaokretna tablica omogućuje vam kombiniranje ogromnih podataka koji analiziraju podatke i izrade izvještaja koji zadovoljavaju vaše poslovne zahtjeve.
Excel nudi ugrađenu okretnu tablicu koju je lako stvoriti primjenom stožerne tablice na tablici podataka. Izvješće je moguće automatski generirati u excel-u nakon dostavljanja podataka. VBA kodovi pomoći će vam da napravite automatsku okretnu tablicu.
Kako stvoriti okretnu tablicu pomoću Excel VBA?
Uobičajeni koraci za umetanje okretne tablice su da prvo umetnete okretnu tablicu iz izbornika Umetanje, a zatim odaberite tablice koje želite promijeniti u okretnu tablicu. Odabrana tablica postat će izvorni podaci i okretna tablica će se stvoriti u skladu s tim.
Za izradu stožernog izvješća morate odabrati polja za filtre, vrijednosti itd. Na sličan način, ovi koraci se mogu automatizirati pomoću VBA kodova. Za rad s okretnom tablicom potrebni su nam excel podaci s lista. Naučimo kako stvoriti okretnu tablicu putem Excel VBA kodova.
Ovdje možete preuzeti ovaj obrazac Excel predložaka VBA pivot tablice - VBA predložak Excel stožerne tabliceKorak 1 : Tablica stožera treba smatrati referentnim objektom. Napravite objekt za okretnu tablicu na daljnjim kodovima koji će se koristiti za usmjeravanje okretne tablice. Imenujte varijablu kao pvtable. Deklarirajte funkciju i objekt okretne tablice.
Kodirati:
Sub PivotTable () Dim pvtable Kao PivotTable End Sub
Korak 2: Da bismo zadržali podatkovne datoteke, potreban nam je okretni predmemorija za proglašavanje izvora podataka. Prije izrade stožerne tablice deklarirajte stožernu varijablu gotovine. Deklarirajte varijablu pvcache za stožerni predmemoriju.
Kodirati:
Dim pvcache kao PivotCache
Korak 3: Podaci se nalaze u radnom listu i trebaju odrediti raspon da biste pogodili ćeliju koja vam je potrebna. U stožernoj tablici izvorni se podaci šire kao redovi i stupci, tako da za upućivanje na određeni raspon potrebna nam je varijabla. Definirajte pvrange kao varijablu raspona.
Kodirati:
Dim pvrange Kao raspon
Korak 4: Radni list treba umetnuti stožernu tablicu koju trebate izraditi. Deklarirajte varijablu kao radni list. Definirajte pvsheet kao radni list.
Kodirati:
Dim pvsheet kao radni list
Korak 5: Potrebna vam je slična varijabla da biste koristili podatkovni list koji treba sadržavati podatke koje želite oblikovati kao okretnu tablicu. Tako je varijabla podatkovnog lista deklarirana kao pdsheet.
Kodirati:
Dim pdsheet kao radni list
Korak 6: Dvije su vam varijable potrebne kao dugačak podatkovni tip da biste naznačili zadnji upotrijebljeni redak i stupac za izradu stožerne tablice. To može biti bilo koji redak ili stupac, tako da postoji vjerojatnost da će biti niz redaka i stupaca koji prelaze ograničenje cijelog broja podataka. Nazovimo ga plr i plc.
Kodirati:
Dim plr As Long Dim plc As Long
Korak 7: Sljedeći korak je brisanje ako već postoji stvorena okretna tablica. Na taj ćete način izbjeći zabunu na kojoj će tablici biti izloženi izvorni podaci. Stoga izbrišite prethodni list okretne tablice i stvorite novi list za umetanje okretne tablice.
Kodirati:
Na pogrešci Nastavite sljedeću aplikaciju.DisplayAlerts = Lažna aplikacija.ScreenUpdating = Lažni radni listovi ("pvsheet"). Izbrišite "za brisanje postojeće stožerne tabele u radnom listu. Dodajte nakon: = ActiveSheet" za dodavanje novog lista ActiveSheet.Name = " pvsheet "" preimenovati radni list u "pvsheet" Na grešci GoTo 0
Korak 8: Dodijelite objektnu varijablu za okretni list i podatkovni list varijablama pvsheet i pdsheet. To će se dodatno koristiti za određivanje radnih listova.
Kodirati:
Postavi pvsheet = Radni listovi ("pvsheet") Postavi pdsheet = Radni listovi ("pdsheet")
Korak 9: Nakon postavljanja radnih listova sljedeća stavka potrebna nam je posljednja upotrijebljena linija i stupac za izradu stožernog izvješća. Pronađite zadnji upotrijebljeni redak i stupac pomoću deklariranih varijabli plr i plc.
Kodirati:
'dvije varijable za pronalaženje posljednjeg korištenog retka i stupca u pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Column.Count) .End (xlToLeft) .Column
Korak 10: Kao što smo ranije spomenuli, podaci na radnom listu su specificirani rasponom ćelija. U sljedećem koraku morate postaviti raspon okretnosti. Već se deklarira kao varijabla za upotrebu okretnog područja „pvrange“.
Kodirati:
'inicijalizacija raspona podataka stožerne tablice Postavite pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Budući da je raspon postavljen pomoću svojstva veličine veličine raspona ćelija, veličina pvrangea će u skladu s tim prilagoditi opseg okretnosti. Tako će se pvrange prilagoditi postoji li dodavanje ili brisanje redaka ili stupca.
Korak 11: Vrijeme je da postavite predmemoriju okreta koja je izvor okretne tablice. Pomoću objekta pvcache postavite izvornu predmemoriju.
Kodirati:
'pivot cahe Postavi pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
12. korak: Ovdje se izvješće o prodaji različitih proizvoda pretvara u zaokretnu tablicu. Stvorite zaokretnu tablicu kao praznu u koju možete dodatno dodati skup podataka.
Kodirati:
'nova prazna stožerna tablica Postavite pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Korak 13: Jednom kada je stožer umetnut, morate navesti različita polja koja želite umetnuti u stožernu okretnicu. Dakle, umetnite polje prvog reda. Ovdje prvi red započinje s proizvodom.
Kodirati:
'Umetanje proizvoda u red podneseno pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Kraj sa
14. korak: Sljedeće je odrediti drugo polje koje želite umetnuti u okretnu tablicu. Na isti način ubacite ulicu polja drugog reda u okretnu tablicu.
Kodirati:
'Umetanje ulice u red dovedeno i pozicija 2 Sa pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Kraj sa
Korak 15: Umetnite sljedeće polje u stožerni stožer, a sljedeće je grad. Dajte kod za umetanje gradskog polja.
Kodirati:
'Umetni grad u stupac podneseno pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town"). Orientation = xlColumnField .Position = 1 Kraj sa
Korak 16: Do ovog trenutka, umetnuti podaci bili su vrsta teksta. Sada moramo umetnuti broj prodaja u stožernu tablicu.
Kodirati:
'Umetnite stupac prodaje u podatkovno polje Sa pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Kraj sa
Korak 17: Ubacili ste polja koja trebaju stvoriti okretnu tablicu. I stožerna tablica je gotovo gotova, sada možete postaviti format stožerne tablice. Ovo će odrediti vrstu tablice kroz stil tablice. Izgled redove osi također je postavljen na način koji želite.
Kodirati:
'postavite format tablice okretne pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Korak 18: Za prikaz stavki vrijednosti stavki u tabelarnom obliku dodajte donji kôd na dnu.
Kodirati:
'za prikaz okretne tablice u tabličnom obliku pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
19. korak: Pritisnite gumb za pokretanje ili pritisnite F5 za pokretanje koda. Ovo će stvoriti okretnu tablicu s lista podataka. Podaci navedeni u redovima i stupcima bit će promijenjeni u okretnu tablicu. Okretna tablica bit će vidljiva na radnom listu stožerne tablice.
Provjerite izlaz i možete vidjeti kako se izvor podataka pretvara u okretnu tablicu kao dolje, spomenuti stupci se pretvaraju u oblike filtera.
Okretna polja su vidljiva na desnoj strani. Možete izmijeniti u skladu s vašim potrebama za prikazivanje podataka.
Za referencu sam u nastavku dao kôd.
Kodirati:
Sub PivotTable () Dim pvtable Kao PivotTable Dim pvcache Kao PivotCache Dim pvrange Kao raspon Dim pvsheet kao radni list Dim pdsheet Kao radni list Dim plr kao dug dim plc kao dug na pogrešku Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = lažni listovi ("False Worksheets") ") .Delete 'za brisanje postojeće stožerne tablice u radnom listu Radni listovi.Dodati nakon: = ActiveSheet' za dodavanje novog radnog lista ActiveSheet.Name =" pvsheet "'za preimenovanje radnog lista u" pvsheet "Na pogrešci GoTo 0 Postavi pvsheet = Radni listovi ("pvsheet") Postavite pdsheet = Radni listovi ("pdsheet") 'dvije varijable za pronalaženje posljednjeg korištenog retka i stupca u pdsheetu plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Ćelije (1, stupci.broj). Završava (xlToLeft). Stupac 'inicijaliziranje raspona podataka okretne tablice Postavite pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nova prazna stožerna tabela Postavi pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") "Umetanje proizvoda u red podvrgnuto pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product ") .Orientation = xlRowField .Position = 1 Kraj s 'Insert Street to Ispravljeno red i pozicija 2 Sa pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street"). Orijentacija = xlRowField .Position = 2 Kraj s 'Insert town to Column File with pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 Završi s" Umetni stupac prodaje u polje podataka s pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with 'set the format Pivot tablice pvsheet.PivotTables ( "Sales_Report"). ShowTableStyleRowStripes = true pvsheet.PivotTables ( "Sales_Report"). TableStyle2 = "PivotStyleMedium14" „pokazati stožerna tablica u tabličnom obliku pvsheet.PivotTables ( "Sales_Report"). RowAxisLayout xlTabularRow Prijava .DisplayAlerts = Istinska aplikacija.ScreenUpdating = Potpuni krajnji sub
Stvari koje treba zapamtiti
- Navedite izvorni list na kojem će se podaci isporučivati za okretnu tablicu.
- Početni red i stupac treba pravilno navesti.
- Upotrijebite potrebne predmete za označavanje predmemorije zaokreta, raspona, početka i kraja redaka.
- Rasporedite izvor podataka odgovarajućim formatom jer je to postupak automatizacije.
Preporučeni članci
Ovo je vodič za VBA okretnu tablicu. Ovdje smo razgovarali o tome kako stvoriti okretnu tablicu pomoću VBA kodova u excelu zajedno s primjerom i downloadabilnim predloškom Excela. Možete pogledati i sljedeće članke da biste saznali više -
- Kako izbrisati stožernu tablicu u Excelu?
- Korištenje VBA u programu PowerPoint
- Okretna tablica s više listova
- Kako stvoriti presjek stožerne tabele?
- Osvježi stožernu tablicu u VBA
- VBA o grešci Nastavi dalje (primjeri)
- Ažuriranje ekrana VBA