VBA pöördtabel | Sammud PBA-tabeli loomiseks VBA-s
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
Selle VBA liigendtabeli malli saate alla laadida siit - VBA liigendtabeli mallPöördtabeli loomiseks on oluline andmete olemasolu. Selleks olen loonud mõned näivandmed. Võite töövihiku alla laadida, et minuga samu andmeid järgida.
1. samm: liigendtabel 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 andmete allika määratlemiseks looma pöördtabeli vahemälu.
Tavalises töölehe pöördtabelis, ilma et see meid häiriks, luuakse taustale pöördliikmete vahemälu. Kuid VBA-s peame looma.
Selle jaoks 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ööleht. Deklareerige muutuja töölehena.
Kood:
Hämarda PSheet töölehena
5. samm: sarnaselt töölehte sisaldavate andmete viitega deklareerige veel üks muutuja töölehena.
Kood:
Hämarda DSheet töölehena
6. samm: viimati kasutatud rea ja veeru leidmiseks määrake 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: määrake nüüd pöördliikumise vahemik, kasutades viimast rida ja viimast veergu.
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.
Samm 13: 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 teise positsiooni reaväljale veel ühe elemendi. Lisan reaväljale toote 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õtte osa, nüüd peame tabeli vormindama. Pöördtabeli vormindamiseks kasutage koodi all.
Reafailide väärtuste üksuste kuvamiseks tabelina lisage allpool olev kood.
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-hämardamine PCache kui PivotCache Dim PRange nagu Range Dim PSheet töölehena Dim DSheet töölehena Dim LR As Long Dim LC nii kaua vea korral Jätkake järgmist rakendust.DisplayAlerts = False Application.ScreenUpdating = Vale töölehed ("Pivot 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). Veerg 'Set Pivot table data range Set PRange = DSheet.Lahtrid (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.PivotTables2 "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
