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 cache.
VBA kan spara massor av tid för oss på vår arbetsplats. Även om det är så enkelt att behärska är det värt att spendera tid på att lära sig detta. Jag tog 6 månader 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
För att skapa en pivottabell är det viktigt att ha data. För detta har jag skapat några 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 och 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 de vanliga kalkylbladen skapar pivottabellen utan att bry oss om en pivotcache i bakgrunden. Men i VBA måste vi skapa.
För detta definierar du 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ör att deklarera 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: Efter att du har infogat pivottabellen måste vi först infoga radfältet. Så jag kommer att 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 sätter in 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 siffror 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.
Lägg till nedanstående kod längst ned om du vill visa de radade värdena i tabellform.
Okej, vi är klara om vi kör den här koden med F5-tangenten eller manuellt, vi borde 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 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 Worksheets ("Pivot Rad "). Ta bort 'Detta tar bort det befintliga pivottabellens kalkylblad. Lägg till efter: = ActiveSheet' Detta kommer att lägga till ett nytt kalkylblad ActiveSheet.Name =" Pivot Sheet "'Detta kommer att byta namn på kalkylbladet som" Pivot Sheet "vid fel Gå till 0 Ställ in PSheet = Kalkylblad ("Pivot Sheet") Ställ in DSheet = Worksheets ("Data Sheet") 'Sök senast använda rad och kolumn i datablad LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft). Kolumn 'Ställ in pivottabellens dataintervall Ange PRange = DSheet.Celler (1, 1). Ändra storlek (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") 'Infoga land till rad arkiverad med PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 Avsluta med' Infoga produkt till Rad arkiverad och position 2 med PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Avsluta med 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 Avsluta med 'Infoga försäljningskolumn i datafältet med PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Avsluta med 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Reportle2). "PivotStyleMedium14" 'Visa 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
