Excel VBA pivottabell

VBA Pivot Table hjelper deg med å oppsummere rapporter fra et stort datasett. Pivot kan betraktes som en liten form for hele datasettet. Den raske visningen av stort datasett er mulig gjennom en pivottabell. En pivottabell er en enkel måte å filtrere data deretter. Fra tilgjengelige data kan du fremheve data slik du ønsker. En pivottabell lar deg kombinere enorme data analysere dataene og produsere rapporter som oppfyller dine forretningskrav.

Excel tilbyr en innebygd pivottabell som er enkel å lage ved å bruke en pivottabell over et datablad. Det er mulig å generere en rapport automatisk i Excel når dataene er levert. VBA-koder vil hjelpe deg med å lage en automatisk pivottabell.

Hvordan lage en pivottabell ved hjelp av Excel VBA?

De vanlige trinnene for å sette inn en pivottabell er å først sette inn en pivottabell fra Sett inn-menyen og deretter velge tabellene du vil endre til en pivottabell. Den valgte tabellen blir kildedataene og pivottabellen opprettes deretter.

For å lage en pivot-rapport må du velge feltene til filtre, verdier osv. På lignende måte kan disse trinnene automatiseres ved hjelp av VBA-kodene. Vi trenger excel-arkdata for å fungere med en pivottabell. La oss lære hvordan du lager en pivottabell gjennom Excel VBA-kodene.

Du kan laste ned denne VBA Pivot Table Excel Mal her - VBA Pivot Table Excel Mal

Trinn 1 : Pivottabellen bør betraktes som et referanseobjekt. Lag et objekt for pivottabellen på ytterligere koder dette objektet vil bli brukt til å peke pivottabellen. Gi variabelen navn som pvtable. Angi et funksjons- og pivottabellobjekt.

Kode:

 Sub PivotTable () Dim pvtable Som PivotTable End Sub

Trinn 2: For å beholde datafilene trenger vi en pivotbuffer for å erklære datakilden. Før du oppretter en pivottabell, erklær en pivottkontantvariabel. Angi variabelen pvcache for pivot-cache.

Kode:

 Dim pvcache Som PivotCache

Trinn 3: Dataene er innenfor arbeidsarket og skal spesifisere et område for å treffe cellen du trenger. I pivottabellen er kildedataene spredt som rader og kolonner, for å peke på et bestemt område trenger vi en variabel. Definer pvrange som en rekkevariabel.

Kode:

 Dim pvrange As Range

Trinn 4: Et arbeidsark må sette inn pivottabellen du trenger å opprette. Deklarere en variabel som et arbeidsark. Definer pvsheet som et regneark.

Kode:

 Dim pvsheet Som regneark

Trinn 5: Du trenger en lignende variabel for å bruke databladet som skal inneholde dataene du vil plotte som en pivottabell. Så databladvariabelen er erklært som pdsheet.

Kode:

 Demp pdsheet som arbeidsark

Trinn 6: Du trenger to flere variabler som lang datatype for å indikere den sist brukte raden og kolonnen for å lage en pivottabell. Dette kan være en hvilken som helst rad eller kolonne, så det finnes sjanser til å være et antall rader og kolonner utover grensen for heltall datatypen. La oss nevne det plr og plc.

Kode:

 Dim plr Så lang Dim plc Så lang

Trinn 7: Neste trinn er å slette hvis det allerede er noen pivottabell. Dette vil bidra til å unngå forvirring på hvilken tabell kildedataene som skal plottes. Så slett forrige pivottabellark og lag et nytt ark for å sette inn pivottabellen.

Kode:

 Ved feil Fortsett neste applikasjon.DisplayAlerts = Falske applikasjoner.ScreenUpdating = Falske regneark ("pvsheet"). Slett 'for å slette den eksisterende pivottabellen i regnearket. Legg til etter: = ActiveSheet' for å legge til et nytt regneark ActiveSheet.Name = " pvsheet "'for å gi nytt navn til regnearket til" pvsheet "på feil GoTo 0

Trinn 8: Tildel en objektvariabel for henholdsvis det dreibare arket og databladet til variablene pvsheet og pdsheet. Dette vil bli brukt videre til å spesifisere arbeidsarkene.

Kode:

 Angi pvsheet = regneark ("pvsheet") Sett pdsheet = regneark ("pdsheet")

Trinn 9: Når arbeidsarkene er satt til neste element, trenger vi den sist brukte raden og kolonnen for å lage en pivot-rapport. Finn den siste brukte raden og kolonnen ved å bruke de deklarerte variablene plr og plc.

Kode:

 'to variabler for å finne sist brukte rad og kolonne i pdsheet plr = pdsheet.Cells (Rows.Count, 1). End (xlUp). Rad plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

Trinn 10: Som vi nevnte tidligere, blir dataene i regnearket spesifisert av cellene. Du må stille inn pivotområdet i neste trinn. Det er allerede erklært som variabel å bruke pivotområdet "pvrange".

Kode:

 initialisere pivottabeldataområdet Angi pvrange = pdsheet.Celler (1, 1). Endre størrelse (plr, plc)

Siden området er satt ved å bruke størrelse på egenskapene til celleområder, vil størrelsen på endringen endre pivotområdet tilsvarende. Så pvrange vil justeres er det noen tilføyelse eller sletting av radene eller kolonnen skjer.

Trinn 11: Det er på tide å stille inn pivot-cachen som er kilden til pivottabellen. Bruk objektet pvcache til å angi kildebufferen.

Kode:

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

Trinn 12: Her vil salgsrapporten for forskjellige produkter bli konvertert til en pivottabell gjennom dette. Lag en pivottabell som blank som du kan legge til datasettet ytterligere.

Kode:

 'ny tom pivottabell Sett pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Trinn 13: Når pivoten er satt inn, må du spesifisere de forskjellige feltene du vil sette inn i pivottabellen. Så sett inn det første radfeltet. Her starter den første raden med produktet.

Kode:

 'Sett inn produkt til rad arkivert med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Posisjon = 1 Slutt med

Trinn 14: Det neste er å spesifisere det andre feltet du vil sette inn i pivottabellen. Sett inn den andre raden feltgaten på pivottabellen på samme måte.

Kode:

 'Sett inn Street to Row Filed & position 2 With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street"). Orientering = xlRowField .Posisjon = 2 Slutt med

Trinn 15: Sett det neste feltet i pivottabellen, og det neste er en by. Gi koden for å sette inn byfeltet.

Kode:

 'Sett inn by til kolonne arkivert med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town"). Orientering = xlColumnField. Plassering = 1 Slutt med

Trinn 16: Inntil dette var dataene som ble lagt inn teksttype. Nå må vi sette inn antall salg til pivottabellen.

Kode:

 'Sett inn salgskolonne i datafeltet Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales"). Orientering = xlDataField .Posisjon = 1 Slutt med

Trinn 17: Du har satt inn feltene som trenger å lage en pivottabell. Og pivottabellen er nesten ferdig nå, du kan stille inn formatet til pivottabellen. Dette vil spesifisere typen bord gjennom tabellstil. Row Axis Layout er også satt slik du vil.

Kode:

 'angi formatet Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

Trinn 18: For å vise radene arkiverte verdier i tabellform, legg til koden nedenfor nederst.

Kode:

 'for å vise pivottabellen i tabellform pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

Trinn 19: Trykk på løpsknappen eller trykk på F5 for å kjøre koden. Dette vil produsere en pivottabell fra datakildearket. Dataene gitt i rader og kolonner vil bli endret til pivottabellen. Pivottabellen vil være synlig på pivottabell-regnearket.

Sjekk utdataene og du kan se datakilden blir konvertert til pivottabellen som nedenfor, de nevnte kolonnene blir konvertert til filterformene.

Pivotfelt er synlige på høyre side. Du kan gjøre endringer i henhold til behovet ditt for hvordan dataene må vises frem.

For din referanse har jeg gitt kode nedenfor.

Kode:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache Som PivotCache Dim pvrange Som Range Dim pvsheet Som Worksheet Dim pdsheet Som Worksheet Dim plr Som Long Dim plc As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet "). Slett 'for å slette den eksisterende pivottabellen i regnearket. Legg til etter: = ActiveSheet' for å legge til et nytt regneark ActiveSheet.Name =" pvsheet "'for å gi nytt navn til regnearket til" pvsheet "på feil GoTo 0 Sett pvsheet = Worksheets ("pvsheet") Sett pdsheet = Worksheets ("pdsheet") 'to variabel for å finne sist brukte rad og kolonne i pdsheet plr = pdsheet.Cells (Rows.Count, 1). End (xlUp) .Row plc = pdsheet. Celler (1, kolonner. Antall). End (xlToLeft) .Column 'initialisering av pivottabeldataområdet Sett pvrange = pdsheet.Celler (1, 1) .Resize (plr, plc)' pivot cahe Sett pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'ny blank pivottabell Sett pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = 'Sales_Report') 'Sett inn produkt til rad arkivert med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product"). Orientering = xlRowField .Position = 1 Slutt med "Sett inn Street to Rad arkivert og posisjon 2 Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With 'Insert town to Column Filed With pvsheet.PivotTables ("Sales_Report"). PivotFields (" By ") .Orientation = xlColumnField .Posisjon = 1 Slutt med 'Sett inn salgskolonne til datafeltet Med pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With" angi settet format Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'for å vise pivottabellen i tabellform pvsheet.PivotTables ("Sales_Rowlabx) .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Ting å huske

  • Spesifiser kildearket der dataene skal leveres for en pivottabell.
  • Startrad og kolonne skal nevnes ordentlig.
  • Bruk de nødvendige objektene for å finne pivotbufferen, rekkevidden, start og slutt på rader.
  • Ordne datakilden med riktig format, siden dette er automatiseringsprosessen.

Anbefalte artikler

Dette er en guide til VBA Pivot Table. Her diskuterer vi hvordan du lager en pivottabell ved hjelp av VBA-koder i excel sammen med et eksempel og nedlastbar excel-mal. Du kan også se på følgende artikler for å lære mer -

  1. Hvordan slette pivottabell i Excel?
  2. Bruker VBA i PowerPoint
  3. Pivottabell med flere ark
  4. Hvordan lage Pivot Table Slicer?
  5. Oppdater pivottabell i VBA
  6. VBA Ved feil CV igjen (eksempler)
  7. Excel VBA ScreenUpdating

Kategori:

VBA