Excel VBA-pivottabel
Pivottabeller er kernen i at opsummere rapporten om en stor mængde data. Vi kan også automatisere processen med at oprette en pivottabel gennem VBA-kodning. De er en vigtig del af enhver rapport eller dashboard, i Excel er det let at oprette tabeller med en knap, men i VBA er vi nødt til at skrive nogle koder for at automatisere vores pivottabel, før excel 2007 og dens ældre versioner i VBA behøvede vi ikke Opret en cache til pivottabeller, men i Excel 2010 og dens nyere versioner kræves cache.
VBA kan spare masser af tid for os på vores arbejdsplads. Selvom det ikke er så nemt at mestre, men det er værd at bruge tid på at lære dette. Jeg tog 6 måneder på at forstå processen med at oprette pivottabeller gennem VBA. Du ved, hvad disse 6 måneder har gjort vidundere for mig, fordi jeg lavede så mange fejl, mens jeg forsøgte at oprette drejetabellen.
Men den egentlige ting er, at jeg har lært af mine fejl, og nu skriver jeg denne artikel for at vise dig, hvordan du opretter drejetabeller ved hjælp af kode.
Med blot et klik på en knap kan vi oprette rapporter.
Trin til at oprette drejetabel i VBA
For at oprette en pivottabel er det vigtigt at have data. Til dette har jeg oprettet nogle dummy data. Du kan downloade projektmappen for at følge med mig med de samme data.
Trin 1: Pivottabel er et objekt, der skal henvise til pivottabellen, erklærer variablen som pivottabeller.
Kode:
Sub PivotTable () Dim PTable Som PivotTable End Sub
Trin 2: Før vi opretter en pivottabel først, skal vi oprette en pivotcache for at definere kilden til dataene.
I de almindelige regneark opretter pivottabellen uden at bekymre os at skabe en pivotcache i baggrunden. Men i VBA er vi nødt til at skabe.
Til dette definerer du variablen en PivotCache.
Kode:
Dæmp PCache som PivotCache
Trin 3: For at bestemme pivotdataområdet definerer du variablen som et interval.
Kode:
Dim PRange As Range
Trin 4: For at indsætte en pivottabel har vi brug for et separat ark for at tilføje regneark til pivottabellen for at erklære variablen som et regneark.
Kode:
Dæmp PSheet som regneark
Trin 5: Tilsvarende, for at henvise til data, der indeholder regneark, erklæres en yderligere variabel som regneark.
Kode:
Dim DSheet som regneark
Trin 6: Endelig, for at finde den sidst anvendte række og kolonne, definer to yderligere variabler som Lang.
Kode:
Dim LR så lang Dim LC så lang
Trin 7: Nu skal vi indsætte et nyt ark for at oprette en drejetabel. Før det, hvis der findes et drejeark, skal vi slette det.
Trin 8: Indstil nu objektvariablen PSheet og DSheet til henholdsvis Pivot Sheet og Data Sheet.
Trin 9: Find den sidst anvendte række og sidst anvendte kolonne i databladet.
Trin 10: Indstil nu drejningsområdet ved at bruge den sidste række og sidste kolonne.
Dette indstiller dataområdet perfekt. Det vælger automatisk dataområdet, selvom der er tilføjelse eller sletning af data i databladet.
Trin 11: Før vi opretter en pivottabel, skal vi oprette en pivotcache. Indstil pivot-cache-variablen ved hjælp af nedenstående VBA-kode.
Trin 12: Opret nu en tom drejetabel.
Trin 13: Efter at have indsat pivottabellen, skal vi først indsætte rækkefeltet. Så jeg vil indsætte rækkefeltet som min landekolonne.
Bemærk: Download projektmappen for at forstå datakolonnerne.
Trin 14: Nu vil jeg endnu et element indsætte i rækkefeltet som det andet positionselement. Jeg vil indsætte produktet som den anden linjepost i rækkefeltet.
Trin 15: Efter at have indsat kolonnerne i rækkefeltet, skal vi indsætte værdier i kolonnefeltet. Jeg indsætter "Segmentet" i kolonnefeltet.
Trin 16: Nu skal vi indsætte tal i datafeltet. Så indsæt "Salg" i datafeltet.
Trin 17: Vi er færdige med pivottabeloversigtsdelen. Nu skal vi formatere tabellen. Brug nedenstående kode til at formatere pivottabellen.
For at vise elementerne i række arkiverede værdier i tabelform skal du tilføje nedenstående kode i bunden.
Ok, vi er færdige, hvis vi kører denne kode ved hjælp af F5-tasten eller manuelt, så skal vi få drejetabellen sådan.
Som dette ved hjælp af VBA-kodning kan vi automatisere processen med at oprette en pivottabel.
Til din reference har jeg givet koden nedenfor.
Under pivottabel () Dim PTable som pivottabel Dim PCache Som PivotCache Dim PRange Som rækkevidde Dim PSheet Som regneark Dim DSheet som regneark Dim LR så lang dim LC Så længe ved Fejl Genoptag næste anvendelse.DisplayAlerts = False Application.ScreenUpdating = False regneark ("Pivot Ark "). Slet 'Dette sletter det eksisterende pivottabel regneark Arbejdsark.Tilføj efter: = ActiveSheet' Dette tilføjer nyt regneark ActiveSheet.Name =" Pivotark "'Dette omdøber regnearket til" Pivotark "On Error GoTo 0 Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Find sidst anvendte række og kolonne i datablad LR = DSheet.Cells (Rows.Count, 1). End (xlUp). Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Indstil dataområdet for pivottabellen Indstil PRange = DSheet.Celler (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Opret tom drejetabel Indstil PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Celler (1, 1), TableName: = "Sales_Report") 'Indsæt land til række arkiveret med PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 Afslut med' Indsæt produkt til Række arkiveret og position 2 med PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Afslut med 'Indsæt segment til kolonne arkiveret og position 1 Med PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 Afslut med 'Indsæt salgskolonne i datafeltet med PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientering = xlDataField. Position = 1 Afslut med 'Format Pivot Table PSheet. "PivotStyleMedium14" 'Vis i tabelform 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
