VBA Pivot Table - Sådan oprettes pivottabel i Excel ved hjælp af VBA-kode?

• Excel VBA drejetabel

Excel VBA drejetabel

VBA Pivot Table hjælper dig med at opsummere rapporter fra et stort datasæt. Pivot kan betragtes som en lille form af hele datasættet. Den hurtige visning af stort datasæt er muligt gennem en pivottabel. En pivottabel er en nem måde at filtrere data i overensstemmelse hermed. Fra de tilgængelige data kan du fremhæve data, som du ønsker. En pivottabel giver dig mulighed for at kombinere de enorme data analysere dataene og fremstille rapporter, der opfylder dine forretningskrav.

Excel tilbyder en indbygget pivottabel, som er let at oprette ved at anvende en pivottabel over et datablad. Det er muligt at generere en rapport automatisk i Excel, når dataene er leveret. VBA-koder hjælper dig med at oprette en automatisk pivottabel.

Sådan opretter du en pivottabel ved hjælp af Excel VBA?

De almindelige trin for at indsætte en pivottabel er først at indsætte en pivottabel fra menuen Indsæt og derefter vælge de tabeller, du vil ændre til en pivottabel. Den valgte tabel bliver kildedataene, og pivottabellen oprettes i overensstemmelse hermed.

For at oprette en pivotrapport skal du vælge felterne til filtre, værdier osv. På lignende måde kan disse trin automatiseres ved hjælp af VBA-koder. Vi har brug for excel-arkdata for at fungere med en pivottabel. Lad os lære at oprette en pivottabel gennem Excel VBA-koder.

Du kan downloade denne VBA Pivot Table Excel-skabelon her - VBA Pivot Table Excel Template

Trin 1 : Pivottabel skal betragtes som et referenceobjekt. Opret et objekt til pivottabellen på yderligere koder, dette objekt vil blive brugt til at pege pivottabellen. Navnér variablen som pvtable. Angiv en funktion og drej tabelobjektet.

Kode:

 Sub PivotTable () Dim pvtable Som PivotTable End Sub 

Trin 2: For at bevare datafilerne har vi brug for en drejecache for at angive datakilden. Før du opretter en pivottabel, angiv en pivot-kontantvariabel. Angiv variablen pvcache for pivot-cache.

Kode:

 Dim pvcache som PivotCache 

Trin 3: Dataene findes i regnearket og skal specificere et interval, der skal ramme den celle, du har brug for. I pivottabellen spredes kildedataene som rækker og kolonner, så vi har brug for en variabel for at pege på et bestemt interval. Definer pvrange som en rækkevariabel.

Kode:

 Dim pvrange As Range 

Trin 4: Et regneark skal indsætte pivottabellen, som du skal oprette. Angiv en variabel som et regneark. Definer pvsheet som et regneark.

Kode:

 Dim pvsheet som arbejdsark 

Trin 5: Du har brug for en lignende variabel for at bruge databladet, der skal indeholde de data, du vil plot som en pivottabel. Så databladvariablen erklæres som pdsheet.

Kode:

 Dim pdsheet som arbejdsark 

Trin 6: Du har brug for to flere variabler som lang datatype for at indikere den sidst anvendte række og kolonne til oprettelse af pivottabel. Dette kan være en række eller kolonner, så der findes chancer for at være et antal rækker og kolonner ud over grænsen for heltal datatypen. Lad os navngive det plr og plc.

Kode:

 Dim plr så lang Dim plc så lang 

Trin 7: Det næste trin er at slette, hvis der allerede er oprettet en pivottabel. Dette vil hjælpe med at undgå forvirring på hvilken tabel kildedataene, der skal afbildes. Så slet det forrige omdrejningstabelark, og opret et nyt ark til indsættelse af pivottabellen.

Kode:

 Ved fejl Genoptag næste applikation.DisplayAlerts = Falsk Application.ScreenUpdating = Falske regneark ("pvsheet"). Slet 'for at slette den eksisterende pivottabel i regnearket Arbejdsark.Tilføj efter: = ActiveSheet' for at tilføje et nyt regneark ActiveSheet.Name = " pvsheet "'for at omdøbe regnearket til" pvsheet "ved fejl GoTo 0 

Trin 8: Tildel en objektvariabel til henholdsvis det drejelige ark og databladet til variablerne pvsheet og pdsheet. Dette vil blive brugt yderligere til at specificere regnearkene.

Kode:

 Angiv pvsheet = Arbejdsark ("pvsheet") Indstil pdsheet = Arbejdsark ("pdsheet") 

Trin 9: Når arbejdsarkene er indstillet til det næste punkt, har vi brug for den sidst anvendte række og kolonne til oprettelse af en pivotrapport. Find den sidst anvendte række og kolonne ved hjælp af de deklarerede variabler plr og plc.

Kode:

 'to variabler for at finde Sidste brugte række og kolonne i pdsheet plr = pdsheet.Cells (Rækker.Tælling, 1) .End (xlUp) .Row plc = pdsheet.Celler (1, Kolonner. Antal). End (xlToLeft) .Column 

Trin 10: Som vi tidligere har nævnt, er dataene i regnearket specificeret af cellernes rækkevidde. Du skal indstille pivotområdet i det næste trin. Det er allerede erklæret som variabel at bruge pivotområdet "pvrange".

Kode:

 'initialisering af pivottabeldataområde Indstil pvrange = pdsheet.Celler (1, 1) .Resize (plr, plc) 

Da intervallet er indstillet ved hjælp af størrelsesegenskab for celleområder, vil det ændre størrelsen på pvrangen, justere pivotområdet i overensstemmelse hermed. Så pvrange vil justeres, hvis der er tilføjelse eller sletning af rækker eller kolonne, der sker.

Trin 11: Det er tid til at indstille pivot-cachen, som er kilden til pivottabellen. Brug objektets pvcache til at indstille kildecachen.

Kode:

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

Trin 12: Her konverteres salgsrapporten for forskellige produkter til en pivottabel gennem denne. Opret en pivottabel som tom, hvortil du kan tilføje datasættet yderligere.

Kode:

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

Trin 13: Når pivoten er indsat, skal du specificere de forskellige felter, du vil indsætte til pivottabellen. Så indsæt det første række-felt. Her starter den første række med produktet.

Kode:

 'Indsæt produkt til række arkiveret med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Ende med 

Trin 14: Det næste er at specificere det andet felt, du vil indsætte til pivottabellen. Indsæt på samme måde feltgaden på anden række til pivottabellen.

Kode:

 'Indsæt gade til række arkiveret & placering 2 med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Ende med 

Trin 15: Indsæt det næste felt til pivottabellen, og det næste er en by. Giv koden for at indsætte byfeltet.

Kode:

 'Indsæt by til kolonne arkiveret med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField. Position = 1 Ende med 

Trin 16: Indtil dette var de indsatte data teksttype. Nu skal vi indsætte antallet af salg til pivottabellen.

Kode:

 'Indsæt salgskolonne til datafeltet Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales"). Orientering = xlDataField .Position = 1 Ende med 

Trin 17: Du har indsat de felter, der skal oprettes en pivottabel. Og pivottabellen er næsten færdig nu kan du indstille formatet på pivottabellen. Dette specificerer typen af ​​tabel gennem bordstil. Row Axis Layout er også indstillet på den måde, du ønsker.

Kode:

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

Trin 18: For at vise de arkiverede værdiposter i tabelform skal du tilføje nedenstående kode i bunden.

Kode:

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

Trin 19: Tryk på startknappen eller tryk på F5 for at køre koden. Dette frembringer en pivottabel fra datakildearket. Dataene, der er givet i rækker og kolonner, ændres til pivottabellen. Pivottabellen vil være synlig på pivottabellens regneark.

Kontroller output, og du kan se datakilden konverteres til pivottabellen, som nedenfor, de nævnte kolonner konverteres til filterformer.

Drejefelter er synlige på højre side. Du kan foretage ændringer i henhold til dit behov for, hvordan dataene skal vises.

Til din henvisning har jeg givet kode nedenfor.

Kode:

 Sub PivotTable () Dim pvtable Som PivotTable Dim pvcache Som PivotCache Dim pvrange Som Range Dim pvsheet Som Worksheet Dim pdsheet Som regneark Dim plr Så lang Dim plc Så længe Fortsæt næste applikation.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet "). Slet for at slette den eksisterende pivottabel i regnearkets regneark. Tilføj efter: = ActiveSheet 'for at tilføje et nyt regneark ActiveSheet.Name =" pvsheet "' for at omdøbe regnearket til" pvsheet "On Error GoTo 0 Set pvsheet = Arbejdsark ("pvsheet") Indstil pdsheet = Arbejdsark ("pdsheet") 'to variabel for at finde Sidste brugte række og kolonne i pdsheet plr = pdsheet.Cells (Rækker.Count, 1). Ende (xlUp) .Row plc = pdsheet. Celler (1, kolonner. Antal). Ende (xlToLeft) .Column 'initialisering af pivottabeldataområde Indstil pvrange = pdsheet.Celler (1, 1) .Resize (plr, plc)' drej cahe Sæt pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'ny blank pivottabel Indstil pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), Tabelnavn: = 'Sales_Report') 'Indsæt produkt til række arkiveret med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Ende med' Indsæt gade til Row Filed & position 2 With 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 .Position = 1 Slut med 'Indsæt salgskolonne til datafeltet Med pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With" sæt sæt format Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'for at vise pivot-tabellen i tabellform pvsheet.PivotTables ("Sales_Rowlabx) .DisplayAlerts = True Application.ScreenUpdating = True End Sub 

Ting at huske

• Angiv kildearket, hvor dataene skal leveres til en pivottabel.
• Start række og kolonne skal nævnes korrekt.
• Brug de nødvendige objekter til at fastlægge drejecachen, rækkevidde, start og slutning af rækker.
• Arranger datakilden med det rigtige format, da dette er automatiseringsprocessen.

Anbefalede artikler

Dette er en guide til VBA Pivot Table. Her diskuterer vi, hvordan man opretter en pivottabel ved hjælp af VBA-koder i excel sammen med et eksempel og en downloadbar excel-skabelon. Du kan også se på de følgende artikler for at lære mere -

1. Sådan slettes pivottabel i Excel?
2. Brug af VBA i PowerPoint
3. Drejebord med flere ark
4. Sådan opretter du drejebordskive?
5. Opdater pivottabel i VBA
6. VBA ved fejlfinding igen (eksempler)
7. Excel VBA-skærmopdatering