Excel VBA pivottabell
Pivottabeller er hjertet i å oppsummere rapporten med en stor mengde data. Vi kan også automatisere prosessen med å lage en pivottabell gjennom VBA-koding. De er en viktig del av en hvilken som helst rapport eller et dashbord, i Excel er det enkelt å lage tabeller med en knapp, men i VBA må vi skrive noen koder for å automatisere pivottabellen, før excel 2007 og dens eldre versjoner i VBA trengte vi ikke lag en cache for pivottabeller, men i Excel 2010 og dens nyere versjoner er cacher påkrevd.
VBA kan spare mye tid for oss på arbeidsplassen vår. Selv om å mestre det ikke er så enkelt, men det er verdt å bruke tid på å lære dette. Jeg tok seks måneder på å forstå prosessen med å lage pivottabeller gjennom VBA. Du vet hva de 6 månedene har gjort underverker for meg fordi jeg gjorde så mange feil mens jeg prøvde å lage pivottabellen.
Men det faktiske er at jeg har lært av feilene mine, og nå skriver jeg denne artikkelen for å vise deg hvordan du lager pivottabeller ved hjelp av kode.
Med bare et klikk på en knapp kan vi lage rapporter.
Fremgangsmåte for å lage pivottabell i VBA
For å lage en pivottabell er det viktig å ha data. For dette har jeg laget noen dummy data. Du kan laste ned arbeidsboken for å følge med meg med de samme dataene.
Trinn 1: Pivottabell er et objekt som refererer til pivottabellen, og erklærer variabelen som pivottabeller.
Kode:
Sub PivotTable () Dim PTable Som PivotTable End Sub
Trinn 2: Før vi oppretter en pivottabell først, må vi opprette en pivotbuffer for å definere kilden til dataene.
I de vanlige regnearkene vil pivottabellen uten å bekymre oss skape en pivotbuffer i bakgrunnen. Men i VBA må vi lage.
For dette definerer du variabelen en PivotCache.
Kode:
Demp PCache som PivotCache
Trinn 3: For å bestemme pivotdataområdet definerer du variabelen som et område.
Kode:
Dim PRange As Range
Trinn 4: For å sette inn en pivottabell, trenger vi et eget ark for å legge til regneark for pivottabellen for å erklære variabelen som et regneark.
Kode:
Dim PSheet Som regneark
Trinn 5: Tilsvarende, for å referere til dataene som inneholder regnearket, erklær du en variabel til som regneark.
Kode:
Dim DSheet Som regneark
Trinn 6: Til slutt, for å finne den sist brukte raden og kolonnen, definer to ytterligere variabler som Lang.
Kode:
Dim LR Så Lang Dim LC Så Lang
Trinn 7: Nå må vi sette inn et nytt ark for å lage en pivottabell. Før det, hvis noe pivotark er der, må vi slette det.
Trinn 8: Sett nå objektvariabelen PSheet og DSheet til henholdsvis Pivot Sheet og Data Sheet.
Trinn 9: Finn den sist brukte raden og sist brukte kolonnen i databladet.
Trinn 10: Still nå pivotområdet ved å bruke den siste raden og den siste kolonnen.
Dette vil sette dataområdet perfekt. Det vil automatisk velge dataområdet, selv om det er noe tillegg eller sletting av data i databladet.
Trinn 11: Før vi oppretter en pivottabell, må vi opprette en pivotbuffer. Sett pivot-cache-variabelen ved å bruke VBA-koden nedenfor.
Trinn 12: Lag nå et tomt pivottabell.
Trinn 13: Etter å ha satt inn pivottabellen, må vi først sette inn radfeltet. Så jeg vil sette inn radfeltet som min Land-kolonne.
Merk: Last ned arbeidsboken for å forstå datakolonnene.
Trinn 14: Nå vil jeg legge til ett element til i radfeltet som det andre posisjonselementet. Jeg vil sette inn produktet som det andre ordrelinjen i radfeltet.
Trinn 15: Etter å ha satt inn kolonnene i radfeltet, må vi sette inn verdier i kolonnefeltet. Jeg vil sette inn "Segment" i kolonnefeltet.
Trinn 16: Nå må vi sette inn tall i datafeltet. Så sett inn “Salg” i datafeltet.
Trinn 17: Vi er ferdige med pivottabellens sammendragsdel. Nå må vi formatere tabellen. For å formatere pivottabellen, bruk koden nedenfor.
For å vise rad arkiverte verdier i tabellform, legg til koden nedenfor nederst.
Ok, vi er ferdige hvis vi kjører denne koden ved hjelp av F5-tasten eller manuelt, så skal vi få pivottabellen slik.
Slik kan vi, ved hjelp av VBA-koding, automatisere prosessen med å lage en pivottabell.
For din referanse har jeg gitt koden nedenfor.
Sub PivotTable () Dim PTable Som 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 regneark ("Pivot Ark "). Slett 'Dette vil slette det eksisterende pivottabell regneark Arbeidsark. Legg til etter: = ActiveSheet' Dette vil legge til et nytt regneark ActiveSheet.Name =" Pivotark "'Dette vil endre navn på regnearket som" Pivot Sheet "Ved feil Gå til 0 Sett PSheet = regneark ("Pivot Sheet") Sett DSheet = regneark ("Data Sheet") 'Finn sist brukte rad og kolonne i datablad LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Angi dataområdet for pivottabellen Set PRange = DSheet.Celler (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. Celler (1, 1), TableName: = "Sales_Report") 'Sett inn land til rad arkivert med PSheet.PivotTables ("Sales_Report"). PivotFields ("Country"). Orientering = xlRowField. Position = 1 Avslutt med' Sett inn produkt til Rad arkivert og posisjon 2 med PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Avslutt med 'Sett inn segment til kolonne arkivert og posisjon 1 Med PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 Avslutt med 'Sett inn salgskolonne i datafeltet med PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Avslutt med 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Reportle"). "PivotStyleMedium14" 'Vis i tabellform 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
