Vrtilna tabela Excel VBA

Zbirna tabela VBA vam pomaga povzeti poročila iz velikega nabora podatkov. Pivot lahko šteje kot drobna oblika celotnega nabora podatkov. Hiter pregled velikega niza podatkov je možen prek vrtilne tabele. Zbirna tabela je preprost način za ustrezno filtriranje podatkov. Iz razpoložljivih podatkov lahko označite podatke tako, kot želite. Z vrtilna tabela lahko združite ogromno podatkov, ki analizirajo podatke in izdelajo poročila, ki ustrezajo vašim poslovnim zahtevam.

Excel ponuja vgrajeno vrtilno tabelo, ki jo je enostavno ustvariti z uporabo vrtilne tabele preko podatkovnega lista. Po predložitvi podatkov je možno samodejno ustvariti poročilo v excelu. Kode VBA vam bodo pomagale ustvariti samodejno vrtilno tabelo.

Kako ustvariti vrtilno tabelo z uporabo Excela VBA?

Pogosti koraki za vstavljanje vrtilne tabele so najprej vstaviti vrtilno tabelo iz menija Vstavljanje in nato izberite tabele, ki jih želite spremeniti v vrtilno tabelo. Izbrana tabela bo postala izvorni podatek in ustrezno bo izdelana vrtilna tabela.

Če želite sestaviti vrtilno poročilo, morate izbrati polja za filtre, vrednosti itd. Na podoben način je te korake mogoče avtomatizirati z uporabo kod VBA. Za delovanje s vrtilno tabelo potrebujemo izjemne podatke. Naučimo se ustvariti vrtilno tabelo s pomočjo kod Excel VBA.

To predlogo Excel z vrtilne tabele VBA lahko prenesete tukaj - Predloga VBA Pivot Tabela Excel

1. korak : vrtilna tabela je treba upoštevati kot referenčni objekt. Ustvarite objekt za vrtilno tabelo na nadaljnjih kodah, s katerim bo predmet usmerjen v vrtilno tabelo. Poimenujemo spremenljivko kot pvtable. Objavite funkcijo in predmet vrtenja tabele.

Koda:

 Sub PivotTable () Dim pvtable As PivotTable End Sub

Korak 2: Za hranjenje podatkovnih datotek potrebujemo vrtilni predpomnilnik, da razglasimo vir podatkov. Preden ustvarite vrtilno tabelo, prijavite vrtilno spremenljivko gotovine. Izjavite spremenljivko pvcache za vrtilni predpomnilnik.

Koda:

 Zatemnite pvcache kot PivotCache

3. korak: Podatki so znotraj delovnega lista in morajo določiti obseg, da zadenete celico, ki jo potrebujete. V vrtilni tabeli se izvorni podatki razširijo v vrstice in stolpce, tako da za prikaz določenega obsega potrebujemo spremenljivko. Določite pvrange kot spremenljivko obsega.

Koda:

 Dim pvrange As Range

4. korak: v delovni list morate vstaviti vrtilno tabelo, ki jo morate ustvariti. Označite spremenljivko kot delovni list. Določite pvsheet kot delovni list.

Koda:

 Dim pvsheet kot delovni list

5. korak: Za uporabo podatkovnega lista, ki naj vsebuje podatke, ki jih želite oblikovati kot vrtilno tabelo, potrebujete podobno spremenljivko. Torej spremenljivka podatkovnega lista je deklarirana kot pdsheet.

Koda:

 Dim pdsheet kot delovni list

6. korak: Za dolžino vrste podatkov potrebujete še dve spremenljivki, da označite zadnjo uporabljeno vrstico in stolpec za izdelavo vrtilne tabele. To je lahko katera koli vrstica ali stolpec, tako da obstaja veliko število vrstic in stolpcev, ki presegajo celo število podatkovnih omejitev. Poimenujmo ga plr in plc.

Koda:

 Dim plr Kot dolga Dim plc Kot dolga

7. korak: Naslednji korak je brisanje, če je že ustvarjena vrtilna tabela. To bo pomagalo preprečiti nejasnosti, na katerih tabelah naj bodo narisani izvorni podatki. Zato izbrišite prejšnji list vrtilne tabele in ustvarite nov list, da vstavite vrtilno tabelo.

Koda:

 Ob napaki Nadaljujte z naslednjo aplikacijo.DisplayAlerts = Lažna aplikacija.ScreenUpdating = Lažni delovni listi ("pvsheet"). Izbrišite ', da izbrišete obstoječo vrtilno tabelo v delovnem listu Delovni listi. Dodajte po: = ActiveSheet' in dodajte nov delovni list ActiveSheet.Name = " pvsheet "" za preimenovanje delovnega lista v "pvsheet" Napaka GoTo 0

Korak 8: spremenljivkama pvsheet in pdsheet dodelite spremenljivko predmeta za vrtilni list in podatkovni list. To bomo uporabili za določitev delovnih listov.

Koda:

 Nastavite pvsheet = Delovni listi ("pvsheet") Nastavite pdsheet = Delovni listi ("pdsheet")

Korak 9: Ko so delovni listi nastavljeni naslednji element, potrebujemo zadnjo uporabljeno vrstico in stolpec za izdelavo vrtilnega poročila. Poiščite zadnjo uporabljeno vrstico in stolpec s pomočjo deklariranih spremenljivk plr in plc.

Koda:

 'dve spremenljivki za iskanje Zadnje uporabljene vrstice in stolpca v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Column.Count) .End (xlToLeft) .Column

Korak 10: Kot smo že omenili, so podatki v delovnem listu določeni z obsegom celic. V naslednjem koraku morate nastaviti območje vrtenja. Za spremenljivko vrtenja "pvrange" je že razglašena kot spremenljivka.

Koda:

 'inicializiranje podatkovnega obsega vrtilne tabele Nastavite pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)

Ker je območje nastavljeno z lastnostjo spremenljivosti velikosti obsegov celic, bo velikost pvrange ustrezno prilagodila obseg vrtenja. Torej se bo pvrange prilagodil, če pride do dodatka ali brisanja vrstic ali stolpcev.

11. korak: Čas je, da nastavite vrtilni predpomnilnik, ki je vir vrtilne tabele. Z objektom pvcache nastavite predpomnilnik vira.

Koda:

 'pivot cahe Nastavi pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)

12. korak: Tu se bo prodajno poročilo za različne izdelke pretvorilo v vrtilno tabelo. Ustvarite vrtilno tabelo kot prazno, kamor lahko dodate nabor podatkov še naprej.

Koda:

 'nova prazna vrtilna tabela Nastavite pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Korak 13: Ko je vrtišče vstavljeno, morate določiti različna polja, ki jih želite vstaviti v vrtilno tabelo. Torej vstavite polje prve vrstice. Tukaj se prva vrstica začne z izdelkom.

Koda:

 "Vstavite izdelek v vrstico, vloženo s pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product ") .Orientation = xlRowField .Position = 1 konec z

14. korak: Naslednje določite drugo polje, ki ga želite vstaviti v vrtilno tabelo. Na enak način vstavite poljsko ulico druge vrstice v vrtilno tabelo.

Koda:

 'Vstavite ulico v vrstico in vstavite položaj 2 S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with

15. korak: V vrtilno tabelo vstavite naslednje polje in naslednje je mesto. Dajte kodo za vnos mestnega polja.

Koda:

 "Vstavite mesto v stolpec vloženo s pvsheet.PivotTables (" Sales_Report "). PivotFields (" Town ") .Orientacija = xlColumnField .Position = 1 konec s

16. korak: Do tega so bili vstavljeni podatki vrsta besedila. Zdaj moramo v vrtilno tabelo vstaviti število prodaje.

Koda:

 'Vstavite stolpec prodaje v podatkovno polje S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 konec s

Korak 17: Vstavili ste polja, ki morajo ustvariti vrtilno tabelo. In vrtilna tabela je skoraj končana, zdaj lahko nastavite obliko vrtilne tabele. To bo določilo vrsto tabele s slogom tabele. Postavitev vrstice osi je prav tako nastavljena na način, ki ga želite.

Koda:

 "nastavite obliko vrtilne tabele pvsheet.PivotTables (" Sales_Report "). ShowTableStyleRowStripes = True pvsheet.PivotTables (" Sales_Report "). TableStyle2 =" PivotStyleMedium14 "

18. korak: Za prikaz postavk z vrednostmi, vloženih v vrstico, v tabeli, na spodnji strani dodajte kodo.

Koda:

 'za prikaz vrtilne tabele v obliki tabele pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

19. korak: Pritisnite gumb za zagon ali pritisnite F5, da zaženete kodo. To bo izdelalo vrtilno tabelo iz podatkovnega lista. Podatki v vrsticah in stolpcih se spremenijo v vrtilno tabelo. Zgibna tabela bo vidna na delovnem listu vrtljive tabele.

Preverite izhod in lahko vidite, da se vir podatkov pretvori v vrtilno tabelo, kot je spodaj, omenjeni stolpci se pretvorijo v filtrirne obrazce.

Pivotna polja so vidna na desni strani. Spremembe lahko spremenite glede na vaše potrebe po prikazu podatkov.

Za vašo referenco sem spodaj navedel kodo.

Koda:

 Pod 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 as Long On Error Nadaljuj Next Application.DisplayAlerts = False Application.ScreenUpdating = False delovne liste ") .Delete ', da izbrišete obstoječo vrtilno tabelo v delovnem listu Worksheets.Add After: = ActiveSheet', da dodate nov delovni list ActiveSheet.Name =" pvsheet "', da delovni list preimenujete v" pvsheet "On Error GoTo 0 Set pvsheet = Delovni listi ("pvsheet") Nastavite pdsheet = Delovni listi ("pdsheet") "dve spremenljivki za iskanje Zadnje uporabljene vrstice in stolpca v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Celice (1, Stolpci. Število). Končaj (xlToLeft). Stolpec "inicializira območje podatkovne vrtilne tabele Nastavi pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)" vrtilni pomnilnik Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nova prazna vrtilna tabela Nastavite pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") "Vstavite izdelek v vrstico, vložen s pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product ") .Orientation = xlRowField .Position = 1 konec z 'Vstavite ulico v Izpolnjeno vrstico & položaj 2 S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientacija = xlRowField .Position = 2 konec z 'Vstavi mesto v stolpec vloženo s pvsheet.PivotTables ("Sales_Report"). PivotFields (" Mesto "). formatu vrtilna tabela pvsheet.PivotTables ( "Sales_Report"). ShowTableStyleRowStripes = true pvsheet.PivotTables ( "Sales_Report"). TableStyle2 = "PivotStyleMedium14 '" za prikaz vrtilni tabeli v obliki tabele pvsheet.PivotTables ( "Sales_Report"). RowAxisLayout xlTabularRow Uporaba .DisplayAlerts = Resnično Application.ScreenUpdating = True End Sub

Stvari, ki jih je treba zapomniti

  • Določite izvorni list, kje naj bodo podatki podani za vrtilno tabelo.
  • Začetno vrstico in stolpec je treba pravilno omeniti.
  • S pomočjo potrebnih predmetov določite vrtilni predpomnilnik, obseg, začetek in konec vrstic.
  • Vir podatkov razporedite s pravilno obliko, saj je to postopek avtomatizacije.

Priporočeni članki

To je vodnik po vrtilni tabeli VBA. Tukaj razpravljamo o tem, kako ustvariti vrtilno tabelo z uporabo kod VBA v excelu skupaj s primerom in naloženo predlogo excela. Če želite izvedeti več, si oglejte tudi naslednje članke -

  1. Kako izbrisati vrtilno tabelo v Excelu?
  2. Uporaba VBA v PowerPointu
  3. Zgibna miza z več listi
  4. Kako ustvariti rezalnik vrtilne tabele?
  5. Osveži vrtilno tabelo v VBA
  6. VBA o napaki Nadaljuj naprej (primeri)
  7. Posodobitev zaslona VBA Excel

Kategorija:

VBA