Excel VBA Pivot Table
VBA Pivot Table hjälper dig att sammanfatta rapporter från en stor datamängd. Pivot kan betraktas som en liten form av hela datauppsättningen. Snabbvy av stora datamängder är möjlig genom en pivottabell. En pivottabell är ett enkelt sätt att filtrera data i enlighet därmed. Från tillgängliga data kan du markera data som du vill. En pivottabell låter dig kombinera den enorma datan analysera data och producera rapporter som uppfyller dina affärsbehov.
Excel erbjuder en inbyggd pivottabell som är lätt att skapa genom att applicera en pivottabell över ett datablad. Det är möjligt att generera en rapport automatiskt i Excel när data har levererats. VBA-koder hjälper dig att skapa en automatisk pivottabell.
Hur skapar du en pivottabell med hjälp av Excel VBA?
De vanliga stegen för att infoga en pivottabell är att först infoga en pivottabell från Infoga-menyn och sedan välja tabellerna som du vill ändra till en pivottabell. Den valda tabellen blir källdata och pivottabellen skapas i enlighet därmed.
För att skapa en pivotrapport måste du välja fälten till filter, värden etc. På liknande sätt kan dessa steg automatiseras med VBA-koderna. Vi behöver en excel-arkdata för att fungera med en pivottabell. Låt oss lära dig att skapa en pivottabell genom Excel VBA-koder.
Du kan ladda ner denna VBA Pivot Table Excel-mall här - VBA Pivot Table Excel MallSteg 1 : Pivottabellen ska betraktas som ett referensobjekt. Skapa ett objekt för pivottabellen på ytterligare koder som detta objekt kommer att användas för att peka pivottabellen. Namnge variabeln som pvtable. Förklara ett funktions- och svängbordstabellobjekt.
Koda:
Sub PivotTable () Dim pvtable Som PivotTable End Sub
Steg 2: För att behålla datafilerna behöver vi en pivotcache för att deklarera datakällan. Innan du skapar en pivottabell förklarar du en pivottkassavariabel. Förklara variabeln pvcache för pivotcache.
Koda:
Dim pvcache som PivotCache
Steg 3: Uppgifterna finns i kalkylbladet och bör ange ett intervall för att träffa den cell du behöver. I pivottabellen sprids kildedata som rader och kolumner för att peka på ett visst intervall behöver vi en variabel. Definiera pvrange som en intervallvariabel.
Koda:
Dim pvrange As Range
Steg 4: Ett kalkylblad måste infoga pivottabellen som du behöver skapa. Förklara en variabel som ett kalkylblad. Definiera pvsheet som ett kalkylblad.
Koda:
Dim pvsheet som arbetsblad
Steg 5: Du behöver en liknande variabel för att använda databladet som ska innehålla de data du vill plotta som en pivottabell. Så databladvariabeln deklareras som pdsheet.
Koda:
Dim pdsheet som arbetsblad
Steg 6: Du behöver ytterligare två variabler som lång datatyp för att ange den senast använda raden och kolumnen för att skapa en pivottabell. Detta kan vara vilken rad eller kolumn som helst så det finns chanser att vara ett antal rader och kolumner utöver gränsen för heltaldatatyp. Låt oss namnge det plr och plc.
Koda:
Dim plr Långt Dim plc Långt
Steg 7: Nästa steg är att ta bort om det redan finns någon pivottabell. Detta hjälper till att undvika förvirringar på vilken tabell källdata som ska planeras. Så radera föregående pivottabellark och skapa ett nytt ark för att infoga pivottabellen.
Koda:
Vid fel Återuppta nästa applikation.DisplayAlerts = False Application.ScreenUpdating = Falsa arbetsblad ("pvsheet"). Radera för att ta bort den befintliga pivottabellen i kalkylbladet. Lägg till efter: = ActiveSheet 'för att lägga till ett nytt kalkylblad ActiveSheet.Name = " pvsheet "'för att byta namn på kalkylbladet till" pvsheet "vid fel GoTo 0
Steg 8: Tilldela en objektvariabel för det svängande arket respektive databladet till variablerna pvsheet och pdsheet. Detta kommer att användas vidare för att specificera kalkylbladet.
Koda:
Ställ in pvsheet = kalkylblad ("pvsheet") Ställ in pdsheet = kalkylblad ("pdsheet")
Steg 9: När kalkylbladet har ställts in är nästa objekt den senaste raden och kolumnen som behövs för att skapa en pivotrapport. Hitta den senast använda raden och kolumnen med de deklarerade variablerna plr och plc.
Koda:
'två variabler för att hitta Senast använda rad och kolumn i pdsheet plr = pdsheet.Cells (Rader.Count, 1) .End (xlUp). Rad plc = pdsheet.Cells (1, Column.Count) .End (xlToLeft) .Column
Steg 10: Som vi nämnde tidigare specificeras data i kalkylbladet av cellerna. Du måste ställa in pivotområdet i nästa steg. Det har redan förklarats som variabel att använda pivotområdet "pvrange".
Koda:
'initiera pivottabeldataområdet Ställ in pvrange = pdsheet.Celler (1, 1). Storlek (plr, plc)
Eftersom intervallet ställs in med hjälp av storleksegenskap för cellområden kommer det att ändra storlek på pvrange kommer att justera pivotområdet i enlighet därmed. Så pvrange kommer att justeras är det någon tillsats eller radering av raderna eller kolumnen inträffar.
Steg 11: Det är dags att ställa in pivotcachen som är källan till pivottabellen. Använd objektet pvcache för att ställa in källcachen.
Koda:
'pivot cahe Ställ in pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Steg 12: Här kommer försäljningsrapporten för olika produkter att konverteras till en pivottabell genom detta. Skapa en pivottabell som tom som du kan lägga till datauppsättningen ytterligare.
Koda:
'ny tom pivottabell Ställ in pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Steg 13: När pivoten är införd måste du ange de olika fälten du vill infoga i pivottabellen. Så sätt in det första radfältet. Här börjar den första raden med produkten.
Koda:
'Infoga produkt till rad arkiverad med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 slut med
Steg 14: Nästa är att ange det andra fältet du vill infoga i pivottabellen. På samma sätt sätter du in den andra radens fältgata i pivottabellen.
Koda:
'Sätt in Street to Row Filed & position 2 Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With
Steg 15: Sätt nästa fält i pivottabellen och nästa är en stad. Ge koden för att infoga stadsfältet.
Koda:
'Infoga stad till kolumn arkiverad med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town"). Orientering = xlColumnField. Position = 1 slut med
Steg 16: Till dess var de infogade texttypen. Nu måste vi infoga antalet försäljningar i pivottabellen.
Koda:
'Sätt in försäljningskolumn i datafältet Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Slut med
Steg 17: Du har lagt in fälten som behöver skapa en pivottabell. Och pivottabellen är nästan färdig nu kan du ställa in pivottabellens format. Detta anger typ av tabell till tabellstil. Radaxellayout är också inställt på det sätt du vill.
Koda:
'ställa in formatet Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Steg 18: För att visa raden arkiverade värden i tabellform lägg till koden nedan längst ner.
Koda:
'för att visa pivottabellen i tabellform pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Steg 19: Tryck på körknappen eller tryck på F5 för att köra koden. Detta ger en pivottabell från datakällarket. Uppgifterna i rader och kolumner kommer att ändras till pivottabellen. Pivottabellen kommer att synas på pivottabellen.
Kontrollera utgången och du kan se datakällan konverteras till pivottabellen enligt nedan, de nämnda kolumnerna konverteras till filterformerna.
Pivotfält är synliga på höger sida. Du kan göra ändringar enligt ditt behov för hur uppgifterna måste visa upp.
Som referens har jag gett kod nedan.
Koda:
Sub PivotTable () Dim pvtable As PivotTable Dim pvcache Som PivotCache Dim pvrange Som Range Dim pvsheet Som Arbetsblad Dim pdsheet Som Arbetsblad Dim plr Långt Dim plc Så länge på fel Återuppta Nästa applikation.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet "). Radera" för att ta bort den befintliga pivottabellen i kalkylbladets kalkylblad. Lägg till efter: = ActiveSheet 'för att lägga till ett nytt kalkylblad ActiveSheet.Name = "pvsheet"' för att byta namn på kalkylbladet till "pvsheet" på fel GoTo 0 Ställ pvsheet = Kalkylblad ("pvsheet") Ställ in pdsheet = Kalkylblad ("pdsheet") 'två variabel för att hitta Senast använda rad och kolumn i pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Celler (1, kolumner. Antal). Änd (xlToLeft) .Column 'initierar pivottabeldataområdet Ställ in pvrange = pdsheet.Celler (1, 1). Ändra (plr, plc)' pivot cahe Ställ in pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'ny tom pivottabell Ställ in pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), Tabellnamn: = "Sales_Report") 'Infoga produkt till rad arkiverad med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Slut med "Insert Street to to Rad arkiverad & position 2 Med pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Avsluta med 'Insert town to Column Filed With pvsheet.PivotTables ("Sales_Report"). PivotFields (" Stad ") .Orientation = xlColumnField .Position = 1 slut med 'Sätt in försäljningskolumn i datafältet Med pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With" ställer in format Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'för att visa pivottabellen i tabellform pvsheet.PivotTables ("Sales_Rowlabx) .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Saker att komma ihåg
- Ange källarket där data ska levereras för en pivottabell.
- Startraden och kolumnen bör nämnas ordentligt.
- Använd de nödvändiga objekten för att fastställa pivotcachen, räckvidden, början och slutet av raderna.
- Ordna datakällan med rätt format eftersom det här är automatiseringsprocessen.
Rekommenderade artiklar
Detta är en guide till VBA Pivot Table. Här diskuterar vi hur man skapar en pivottabell med hjälp av VBA-koder i Excel tillsammans med ett exempel och en nedladdningsbar Excel-mall. Du kan också titta på följande artiklar för att lära dig mer -
- Hur tar jag bort pivottabell i Excel?
- Använda VBA i PowerPoint
- Pivottabell med flera ark
- Hur man skapar Pivot Table Slicer?
- Uppdatera pivottabellen i VBA
- VBA vid felupptagning nästa (exempel)
- Excel VBA ScreenUpdating