Exceli VBA pöördetabel
VBA Pivot Table aitab teil koondada suurest andmekogumist aruandeid. Pivot võib pidada kogu andmekogumi pisikeseks vormiks. Suure andmekogu kiire kuvamine on võimalik pöördetabeli kaudu. Pöördtabel on lihtne viis andmete filtreerimiseks. Kättesaadavate andmete hulgast saate andmed soovitud viisil esile tõsta. Pöördtabel võimaldab teil ühendada tohutu hulga andmeid, analüüsida andmeid ja koostada aruandeid, mis vastavad teie ettevõtte nõuetele.
Excel pakub sisseehitatud pöördetabelit, mida on lihtne luua, rakendades pöörlemistabelit andmelehe kohal. Pärast andmete esitamist on võimalik genereerida aruanne automaatselt Excelis. VBA-koodid aitavad teil luua automaatse pöördetabeli.
Kuidas luua Pivot-tabelit Excel VBA abil?
Pöördtabeli lisamise tavalised sammud on kõigepealt sisestada pöördetabel menüüst Lisamine, seejärel valige tabelid, mida soovite pöördetabeliks muuta. Valitud tabel saab lähteandmeteks ja vastavalt sellele luuakse ka pöördetabel.
Pöördraporti koostamiseks peate valima filtrite, väärtuste jms väljad. Sarnasel viisil saab neid samme automatiseerida ka VBA-koodide abil. Pivot-tabeliga töötamiseks vajame Exceli lehe andmeid. Õppime, kuidas luua pöördetabelit Exceli VBA koodide kaudu.
Selle VBA Pivot Table Exceli malli saate alla laadida siit - VBA Pivot Table Exceli mall1. samm : pöördetabelit tuleks käsitleda võrdlusobjektina. Looge pivot-tabeli jaoks objekt järgmistel koodidel, seda objekti kasutatakse pivot-tabeli osutamiseks. Pange muutujale pvtable. Funktsiooni ja pöördetabeli objekti kuulutamine.
Kood:
Sub PivotTable () Tühjendatav kui PivotTable End Sub
2. samm: andmefailide hoidmiseks vajame andmete allika teatavaks tegemiseks vahemälu. Enne pöördetabeli loomist kuulutage pöördetabeli muutuja. Deklareerige muutuv pvcache pöörde vahemälu jaoks.
Kood:
Dim pvcache kui PivotCache
3. samm: andmed asuvad töölehel ja peaksid täpsustama vajaliku lahtri jõudmiseks vahemiku. Pöördtabelis jaotatakse lähteandmed ridade ja veergudena, nii et konkreetse vahemiku osutamiseks vajame muutujat. Määratlege pvrange vahemiku muutujana.
Kood:
Dim pvrange As Range
4. samm: töölehele tuleb sisestada pöördetabel, mille peate looma. Muutuja kuulutamine tööleheks. Määrake pvsheet töölehena.
Kood:
Pähkel pvsheet töölehena
5. samm: andmelehe kasutamiseks, mis peaks sisaldama andmeid, mida soovite pöördetabelina joonistada, on vaja sarnast muutujat. Nii et andmelehe muutuja deklareeritakse pdsheet-na.
Kood:
Tuhm pdsheet töölehena
6. samm: viimati kasutatud rea ja veeru tähistamiseks pöördetabeli loomiseks vajate veel kahte muutujat kui pikka andmetüüpi. Selleks võib olla ükskõik milline rida või veerg, nii et on tõenäoline, et ridade ja veergude arv ületab täisarvu andmetüübi limiidi. Nimetagem seda plr ja plc.
Kood:
Dim plr nii kaua Dim plc nii kaua
7. samm: järgmine samm on kustutamine, kui juba on mõni pöördetabel loodud. See aitab vältida segadusi selle kohta, millisele tabelile lähteandmed tuleb joonistada. Nii et kustutage eelmine pöördetabeli leht ja looge uus leht pöördetabeli lisamiseks.
Kood:
Vea korral Jätka järgmise rakendusega.DisplayAlerts = Vale rakendus.ScreenUpdating = Valed töölehed ("pvsheet"). Kustutage 'olemasoleva pöördetabeli kustutamiseks töölehelt töölehed.Lisa pärast: = ActiveSheet' uue töölehe lisamiseks ActiveSheet.Name = " pvsheet "', et tööleht ümber nimetada pvsheet" veaks GoTo 0
8. samm: määrake pöörde- ja andmelehe objektimuutujad vastavalt muutujatele pvsheet ja pdsheet. Seda kasutatakse täiendavalt töölehtede täpsustamiseks.
Kood:
Määra pvsheet = töölehed ("pvsheet") Seadista pdsheet = töölehed ("pdsheet")
9. samm: kui töölehed on seatud, on meil vaja pöördearuande loomiseks viimast kasutatud rida ja veergu. Leidke deklareeritud muutujate plr ja plc abil viimati kasutatud rida ja veerg.
Kood:
'kaks muutujat viimati kasutatud rea ja veeru leidmiseks pdsheet pr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Cumnumn
10. samm: Nagu me juba varem mainisime, on töölehe andmed täpsustatud lahtrite vahemiku järgi. Järgmises etapis peate määrama pöördevahemiku. Pöördvahemiku „pvrange” kasutamine on juba deklareeritud muutujaks.
Kood:
'pöördetabeli andmevahemiku lähtestamine Määrake pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Kuna vahemiku määramisel kasutatakse lahtrite vahemike muutmise omadust, kohandab pvrange suurust vastavalt pivoti vahemik. Nii et pvrange korrigeeritakse, kas ridade või veergude lisamist või kustutamist juhtub.
11. samm: on aeg määrata pivot-vahemälu, mis on pöördetabeli allikas. Allika vahemälu seadmiseks kasutage objekti pvcache.
Kood:
'pivot cahe Määrake pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
12. samm: siin teisendatakse erinevate toodete müügiaruanne selle kaudu pöördetabeliks. Looge tühi pöördetabel, millesse saate andmekogumit veelgi lisada.
Kood:
'uus tühi pöördetabel Määra pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
13. samm: Kui pöördtekst on sisestatud, peate määrama erinevad väljad, mille soovite pöördetabelisse lisada. Nii sisestage esimese rea väli. Esimene rida algab tootest.
Kood:
'Lisage toode reale, mis on täidetud pvsheet.PivotTables'iga ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Lõpp
14. samm: Järgmine on teine väli, mille soovite pöördetabelisse lisada. Samamoodi sisestage teise rea põllutänav pöördetabelisse.
Kood:
'Lisage tänav reale Filed & position 2 with pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with
15. samm: sisestage järgmine väli pöördetabelisse ja järgmine on linn. Andke linnavälja lisamiseks kood.
Kood:
'Lisage linn veergu, kuhu on lisatud pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End With
16. samm: kuni selleni sisestatud andmed olid teksti tüüpi. Nüüd peame sisestama pöördetabelisse müüginumbrite arvu.
Kood:
"Lisage müügi veerg andmeväljale pvsheet.PivotTables (" Sales_Report ") abil. PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With
17. samm: olete lisanud väljad, mis vajavad pöördetabeli loomiseks. Ja pöördetabel on peaaegu valmis. Nüüd saate seada pöördetabeli vormi. See täpsustab tabeli tüübi ja tabeli stiili. Ridatelje paigutus on seatud ka soovitud viisil.
Kood:
"määrake vorming Pivot Table pvsheet.PivotTables (" Sales_Report "). ShowTableStyleRowStripes = True pvsheet.PivotTables (" Sales_Report "). TableStyle2 =" PivotStyleMedium14 "
18. toiming: reale esitatud väärtuste üksuste kuvamiseks tabelina lisage allpool olev kood.
Kood:
'pöördetabeli kuvamiseks tabelina kujul pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
19. samm: koodi käivitamiseks vajutage käivitusnuppu või vajutage nuppu F5. See loob andmeallika lehelt pöördetabeli. Ridades ja veergudes esitatud andmed muudetakse pöördetabeliks. Pöördtabel on nähtav pöördetabeli töölehel.
Kontrollige väljundit ja näete, kuidas andmeallikas teisendatakse pöördetabelisse nagu allpool, nimetatud veerud teisendatakse filtrivormideks.
Pöördväljad on paremal küljel nähtavad. Saate teha muudatusi vastavalt vajadusele andmete esitusviisi järele.
Teie teadmiseks olen andnud allpool koodi.
Kood:
Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet as tööleht Dim plr nii pikk Dim plc nii kaua kui viga jätkub Järgmine taotlus.DisplayAlerts = Vale rakendus.ScreenUpdating = p. Tööleht ") .Kuvake olemasoleva pöördetabeli kustutamiseks töölehe Töölehed.Lisa pärast: = ActiveSheet 'uue töölehe lisamiseks ActiveSheet.Name =" pvsheet "', et nimetada tööleht ümber" pvsheet "Viga GoTo 0 Määra pvsheet = Töölehed ("pvsheet") Määrake pdsheet = töölehed ("pdsheet") 'kaks muutujat, et leida viimati kasutatud rida ja veerg pdsheet-is plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Lahtrid (1, veerud.arv) .Lõpp (xlToLeft). Veeru 'pöördetabeli andmevahemiku lähtestamine Määrake pvrange = pdsheet.Kellid (1, 1) .Palundage (plr, plc)' pöörde vahemälu Seadke pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'uus tühi pöördetabel Määra pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") "Sisestage pvsheet.PivotTables (" Sales_Report ") abil esitatav rida reale. PivotFields (" Product ") .Orientation = xlRowField .Position = 1 lõpeb sõnadega" Insert Street to Rida arhiveeritud ja positsioon 2, kasutades pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Lõpetage numbriga "Lisa linn veergu, mis on täidetud pvsheet.PivotTables (" Sales_Report "). PivotFields (" Linn ") .Orientation = xlColumnField .Position = 1 lõpeb veeru 'Lisage müügi veergu andmeväljale pvsheet.PivotTables'iga (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End with' määrake ' vormindage Pivot-tabel pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" "näitab pivot-tabelit tabelina kujul pvsheetApportTextileRayport.PivotTables. .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Asjad, mida meeles pidada
- Määrake lähteleht, kuhu pöördetabeli andmed tuleb edastada.
- Algrida ja veerg tuleks korralikult ära mainida.
- Kasutage vajalikke objekte, et täpsustada pöörde vahemälu, vahemik, ridade algus ja lõpp.
- Korraldage andmeallikas õige vorminguga, kuna see on automatiseerimisprotsess.
Soovitatavad artiklid
See on VBA Pivot Table'i juhend. Siin arutatakse, kuidas luua pöördetabelit, kasutades Excelis VBA koode, koos näite ja allalaaditava excelimalliga. Lisateabe saamiseks võite vaadata ka järgmisi artikleid -
- Kuidas kustutada Pivoti tabelit Excelis?
- VBA kasutamine PowerPointis
- Pöördlaud mitme lehega
- Kuidas luua Pivoti tabeli tükeldaja?
- Värskendage pöördetabelit VBA-s
- VBA Viga jätkub järgmine (näited)
- Exceli VBA ekraanivärskendus