Exceli VBA liigendtabel
Liigtabelid on suure hulga andmete aruande kokkuvõtte tegemise keskmes. Samuti saame VBA-kodeerimise abil automatiseerida pöördtabeli loomise protsessi. Need on mis tahes aruande või juhtpaneeli oluline osa, Excelis on tabelite loomine nupuga lihtne, kuid VBA-s peame oma pöördtabeli automatiseerimiseks kirjutama mõned koodid, enne kui Excel 2007 ja selle vanemad versioonid VBA-s ei olnud meil vaja luua pivot-tabelite vahemälu, kuid Excelis 2010 ja selle uuemates versioonides on vahemälud nõutavad.
VBA võib meie töökohal säästa palju aega. Kuigi selle valdamine pole nii lihtne, kuid selle õppimiseks tasub aega kulutada. Mul kulus 6 kuud, et VBA kaudu aru saada pöördtabelite loomise protsessist. Teate, mida need 6 kuud on minu jaoks imet teinud, sest ma tegin pöördtabeli loomisel nii palju vigu.
Kuid tegelikult olen õppinud oma vigadest ja kirjutan nüüd selle artikli, et näidata teile, kuidas koodi abil pivot-tabeleid luua.
Vaid ühe nupuvajutusega saame aruandeid luua.
Sammud PBA-tabeli loomiseks VBA-s
Pöördtabeli loomiseks on oluline andmete olemasolu. Selle jaoks olen loonud mõned näivandmed. Töövihiku saate alla laadida, et minuga samu andmeid järgida.
1. samm: pöördtabel on objekt, millele viidata pöördtabelis, mis deklareerib muutuja PivotTables-vormingus.
Kood:
PivotTable-liigendtabel () Dim PTable PivotTable-liigendtabeli lõpp-alamrubriigina
2. samm: Enne pöördtabeli loomist peame andmeallika määratlemiseks looma pöördtabeli vahemälu.
Tavalistes töölehtedes loob pöördtabel meile muret tekitades taustale pöördtabeli. Kuid VBA-s peame looma.
Selleks määrake muutuja PivotCache.
Kood:
Hämardada PCache kui PivotCache
3. samm: Pivotandmete vahemiku määramiseks määratlege muutuja vahemikuna.
Kood:
Dim PRange As Range
4. samm: pöördtabeli lisamiseks vajame eraldi lehte, et lisada pöördtabeli töölehed, et deklareerida muutuja töölehena.
Kood:
Hämarda PSheet töölehena
5. samm: sarnaselt deklareerige töölehte sisaldavatele andmetele veel üks muutuja töölehena.
Kood:
Hämarda DSheet töölehena
6. samm: Lõpuks määrake viimati kasutatud rea ja veeru leidmiseks veel kaks muutujat pikkana.
Kood:
Dim LR sama pikk Dim LC sama pikk
7. samm: nüüd peame pöördtabeli loomiseks sisestama uue lehe. Enne seda, kui mõni pöördeleht on olemas, peame selle kustutama.
8. samm: määrake nüüd objekti muutujale PSheet ja DSheet vastavalt Pivot Sheet ja Data Sheet.
9. samm: leidke andmelehelt viimati kasutatud rida ja viimati kasutatud veerg.
10. samm: seadistage nüüd viimase rea ja viimase veeru abil pöördepiirkond.
See määrab andmevahemiku ideaalselt. See valib andmevahemiku automaatselt, isegi kui andmelehel on andmeid lisatud või kustutatud.
11. samm: enne pöördtabeli loomist peame looma pöördtabeli. Pivoti vahemälu muutuja määrake alloleva VBA-koodi abil.
12. samm: looge nüüd tühi pöördetabel.
13. samm: pärast pöördtabeli sisestamist peame kõigepealt sisestama rea välja. Nii et sisestan rea välja oma veeruks Riik.
Märkus. Andmeveerude mõistmiseks laadige töövihik alla.
14. samm: nüüd lisan veel ühe elemendi rea väljale teise positsiooni elemendina. Lisan toote rea reale teise reaelemendina.
15. samm: pärast veergude sisestamist rea väljale peame lisama väärtused veeru väljale. Lisan veeru väljale „Segment”.
16. samm: nüüd peame andmeväljale sisestama numbrid. Nii sisestage andmeväljale “Müük”.
17. samm: oleme lõpetanud pöördtabeli kokkuvõtliku osa. Nüüd peame tabeli vormindama. Pöördtabeli vormindamiseks kasutage allolevat koodi.
Reale esitatud väärtuste üksuste kuvamiseks tabelina lisage allpool olev kood allpool.
Ok, oleme valmis, kui käivitame selle koodi F5-klahvi abil või käsitsi, siis peaksime saama sellise liigendtabeli.
Nii saame VBA kodeerimise abil automatiseerida pöördtabeli loomise protsessi.
Viitamiseks olen andnud allpool oleva koodi.
PivotTable-liigendtabel () Dim PTable kui PivotTable-liigend PCache kui PivotCache Leht "). Kustuta 'See kustutab senise pöördtabeli töölehe töölehed. Lisage pärast: = ActiveSheet' See lisab uue töölehe ActiveSheet.Name =" Pivot-leht "'See nimetab töölehe ümber" Pivot-leht "Vea GoTo 0 korral Määra PSheet = töölehed ("Pivot Sheet") Määra DSheet = töölehed ("Andmeleht") 'Leia viimati kasutatud rida ja veerg andmelehelt LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Rida LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Set Pivot table data range Set PRange = DSheetLahtrid (1, 1). Suuruse muutmine (LR, LC) "Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)" Looge tühi pöördtabel Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Lahtrid (1, 1), TableName: = "Müügiaruanne") 'Sisestage riik reale, mis on sisestatud PSheet.PivotTables'iga ("Müügiraport"). PivotFields ("Riik") .Orientation = xlRowField .Positsioon = 1 Lõpeta' Sisesta toode Rea esitamine ja positsioon 2 koos PSheet.PivotTables ("Müügiaruanne"). PivotFields ("Toode"). Orientation = xlRowField .Positsioon = 2 Lõpeta 'Lisa segment veergu Filed & positsioon 1 koos PSheet.PivotTables'iga ("Müügi_aruanne"). PivotFields ("Segment"). Orientation = xlColumnField .Position = 1 End With 'Sisesta veerg Müük andmeväljale PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales"). Orientation = xlDataField .Positsioon = 1 Lõpeta 'Pivot-tabeli vormindamise PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Report2"). "PivotStyleMedium14" 'Kuva tabelivormis PSheet.PivotTables ("Müügiaruanne"). 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
