Excel VBA-pivottabell
Pivottabeller är hjärtat i att sammanfatta rapporten med en stor mängd data. Vi kan också automatisera processen för att skapa en pivottabell genom VBA-kodning. De är en viktig del av någon rapport eller instrumentpanel, i Excel är det enkelt att skapa tabeller med en knapp men i VBA måste vi skriva några koder för att automatisera vår pivottabell, innan excel 2007 och dess äldre versioner i VBA behövde vi inte skapa en cache för pivottabeller men i Excel 2010 och dess nyare versioner krävs cacheminnet.
VBA kan spara massor av tid för oss på vår arbetsplats, även om det inte är så lätt men att värda att spendera tid på att lära sig detta. Jag tog sex månader på att förstå processen för att skapa pivottabeller genom VBA. Du vet vad de 6 månaderna har gjort underverk för mig eftersom jag gjorde så många misstag när jag försökte skapa pivottabellen.
Men det faktiska är att jag har lärt mig av mina misstag och nu skriver jag den här artikeln för att visa dig hur du skapar pivottabeller med kod.
Med bara ett klick på en knapp kan vi skapa rapporter.
Steg för att skapa pivottabell i VBA
Du kan ladda ner den här VBA-pivottabellmallen här - VBA-pivottabellmallenFör att skapa en pivottabell är det viktigt att ha data. För detta har jag skapat en del dummydata, du kan ladda ner arbetsboken för att följa med mig med samma data.
Steg 1: Pivottabell är ett objekt som refererar till pivottabellen, deklarerar variabeln som pivottabeller.
Koda:
Sub PivotTable () Dim PTable Som PivotTable End Sub
Steg 2: Innan vi först skapar en pivottabell måste vi skapa en pivotcache för att definiera källan till data.
I det vanliga arbetsbladets pivottabell skapar vi en pivotcache i bakgrunden utan att oroa oss. Men i VBA måste vi skapa.
För detta definiera variabeln en PivotCache.
Koda:
Dim PCache som PivotCache
Steg 3: För att bestämma pivotdataområdet definierar du variabeln som ett intervall.
Koda:
Dim PRange As Range
Steg 4: För att infoga en pivottabell behöver vi ett separat ark för att lägga till kalkylblad för pivottabellen förklara variabeln som ett kalkylblad.
Koda:
Dim PSheet som arbetsblad
Steg 5: På samma sätt för att referera till data som innehåller kalkylblad förklara en ytterligare variabel som kalkylblad.
Koda:
Dim DSheet som arbetsblad
Steg 6: Slutligen, för att hitta den senast använda raden och kolumnen, definiera ytterligare två variabler som Lång.
Koda:
Dim LR Så Lång Dim LC Så Lång
Steg 7: Nu måste vi infoga ett nytt ark för att skapa en pivottabell. Innan det, om något pivotark finns där, måste vi ta bort det.
Steg 8: Ställ nu in objektvariabeln PSheet och DSheet till Pivot Sheet respektive Data Sheet.
Steg 9: Hitta den senast använda raden och senast använda kolumnen i databladet.
Steg 10: Ställ nu in pivotintervallet med den sista raden och den sista kolumnen.
Detta ställer in dataområdet perfekt. Det väljer automatiskt dataområdet även om det finns någon tillägg eller radering av data i databladet.
Steg 11: Innan vi skapar en pivottabell måste vi skapa en pivotcache. Ställ in pivotcache-variabeln med hjälp av VBA-koden nedan.
Steg 12: Skapa nu en tom pivottabell.
Steg 13: När du har infogat pivottabellen måste vi först infoga radfältet. Så jag ska infoga radfältet som min landskolumn.
Obs: Ladda ner arbetsboken för att förstå datakolumnerna.
Steg 14: Nu ska jag lägga till ytterligare ett objekt i radfältet som det andra positionsposten. Jag kommer att infoga produkten som den andra raden i radfältet.
Steg 15: Efter att ha infogat kolumnerna i radfältet måste vi infoga värden i kolumnfältet. Jag sätter in "Segment" i kolumnfältet.
Steg 16: Nu måste vi infoga nummer i datafältet. Så sätt in ”Försäljning” i datafältet.
Steg 17: Vi är klara med pivottabellens sammanfattningsdel, nu måste vi formatera tabellen. Använd nedanstående kod för att formatera pivottabellen.
För att visa de raderade värdena i tabellform lägg till nedanstående kod längst ner.
Okej, vi är klara om vi kör den här koden med F5-tangenten eller manuellt, då borde vi få pivottabellen så här.
Så här med hjälp av VBA-kodning kan vi automatisera processen för att skapa en pivottabell.
Som referens har jag gett koden nedan.
Sub PivotTable() Dim PTable As 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 Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(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.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
