VBA-kääntötaulukko - Kuinka luoda Pivot-taulukko Excelissä käyttämällä VBA-koodia?

• Excel VBA -taulukko

Excel VBA -taulukko

VBA Pivot Table auttaa sinua tiivistämään raportit suuresta tietojoukosta. Pivot-yksikköä voidaan pitää pienenä muotona koko tietokokonaisuudesta. Nopea näkymä suurelle tietojoukolle on mahdollista kääntötaulun kautta. Kääntötaulukko on helppo tapa suodattaa tietoja vastaavasti. Saatavilla olevista tiedoista voit korostaa tietoja haluamallasi tavalla. Kääntötaulukon avulla voit yhdistää valtavan datan analysoida tietoja ja tuottaa raportteja, jotka täyttävät yrityksesi vaatimukset.

Excel tarjoaa sisäänrakennetun kääntötaulun, joka on helppo luoda käyttämällä kääntötaulua teknisen taulukon päälle. Raportti on mahdollista luoda automaattisesti Excel-muodossa, kun tiedot on toimitettu. VBA-koodit auttavat sinua luomaan automaattisen kääntötaulun.

Kuinka luoda pivot-taulukko Excel VBA: n avulla?

Yleiset vaiheet kääntötaulun lisäämiseksi on asettaa ensin kääntötaulukko Lisää-valikosta ja sitten valita taulukot, jotka haluat muuttaa kääntötauluksi. Valitusta taulukosta tulee lähdetiedot ja kääntötaulu luodaan vastaavasti.

Pivot-raportin luomiseksi sinun on valittava suodattimien, arvojen jne. Kentät. Samalla tavalla nämä vaiheet voidaan automatisoida VBA-koodeilla. Tarvitsemme excel-taulukkotietoja toimiaksemme kääntötaulukon kanssa. Oppitaan kuinka luoda kääntötaulukko Excel VBA -koodien avulla.

Voit ladata tämän VBA Pivot Table Excel -mallin täältä - VBA Pivot Table Excel Template

Vaihe 1 : Pivot-taulukon tulisi olla viiteobjekti. Luo objekti kääntötaululle lisäkoodeilla, tätä objektia käytetään osoittamaan kääntötaulukkoa. Nimeä muuttuja pvtable-muodossa. Ilmoita toiminto ja kääntötaulukko-objekti.

Koodi:

 Sub PivotTable () himmennettävissä PivotTable-loppuna Sub 

Vaihe 2: Tietotiedostojen säilyttämiseksi tarvitsemme kääntövälimuistin tietolähteen ilmoittamiseksi. Ennen kääntötaulun luomista ilmoita kääntökassamuuttuja. Ilmoita muuttuva pvcache kääntövälimuistille.

Koodi:

 Himmennä pvcache nimellä PivotCache 

Vaihe 3: Tiedot ovat laskentataulukon sisällä, ja niiden tulisi määritellä alue, jolla pääset tarvitsemasi soluun. Pivot-taulukossa lähdetiedot leviävät riveinä ja sarakkeina, jotta tietyn alueen osoittamiseksi tarvitsemme muuttujan. Määritä pvrange aluemuuttujana.

Koodi:

 Dim pvrange As Range 

Vaihe 4: Laskentataulukko on asetettava luotava kääntötaulukko. Ilmoita muuttuja taulukkona. Määritä pvsheet taulukkona.

Koodi:

 Himmennä pvsheet taulukkona 

Vaihe 5: Tarvitset samanlaisen muuttujan käyttääksesi teknistä taulukkoa, jonka tulisi sisältää tiedot, jotka haluat piirtää pivot-taulukkoksi. Joten tekninen muuttuja julistetaan pdsheet.

Koodi:

 Himmennä pdsheet taulukkona 

Vaihe 6: Tarvitset kaksi muuta muuttujaa pitkällä tietotyypillä osoittaaksesi viimeksi käytetyn rivin ja sarakkeen kääntötaulun luomiseen. Tämä voi olla mikä tahansa rivi tai sarake, joten on olemassa mahdollisuuksia olla useita rivejä ja sarakkeita kokonaisluvun tietotyyppirajoituksen ulkopuolella. Nimeämme se plr ja plc.

Koodi:

 Dim plr niin kauan Dim plc niin kauan 

Vaihe 7: Seuraava vaihe on poistaa, jos pivot-taulukko on jo luotu. Tämä auttaa välttämään sekaannuksia, mihin taulukkoon piirrettävä lähdetieto on. Joten poista edellinen kääntötaulukko ja luo uusi taulukko kääntääksesi taulukko.

Koodi:

 Kun virhe Jatka seuraavaa sovellusta.DisplayAlerts = Väärä sovellus.ScreenUpdating = Väärä laskentataulukko ("pvsheet"). Poista 'poistaa nykyinen kääntötaulukko laskentataulukon taulukot taulukosta.Lisää jälkeen: = ActiveSheet' lisätäksesi uuden taulukon ActiveSheet.Name = " pvsheet "'nimeää laskentataulukko uudelleen" pvsheet "virheeksi GoTo 0 

Vaihe 8: Määritä kääntö- ja tietolomakeobjektimuuttujat muuttujille pvsheet ja pdsheet. Tätä käytetään edelleen laskentataulukoiden määrittämiseen.

Koodi:

 Aseta pvsheet = Taulukoita ("pvsheet") Aseta pdsheet = Taulukoita ("pdsheet") 

Vaihe 9: Kun taulukot on asetettu, tarvitsemme viimeisen käytetyn rivin ja sarakkeen kääntöraportin luomiseksi seuraava kohta. Etsi viimeisin käytetty rivi ja sarake ilmoitettujen muuttujien plr ja plc avulla.

Koodi:

 'kaksi muuttujaa viimeksi käytetyn rivin ja sarakkeen löytämiseksi pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Saarna 

Vaihe 10: Kuten aiemmin mainitsimme, laskentataulukon tiedot määritellään solualueella. Sinun on asetettava kääntöalue seuraavassa vaiheessa. Se on jo ilmoitettu muuttuvaksi kääntöalueen “pvrange” käyttämiseksi.

Koodi:

 'pivot-taulukon data-alueen alustaminen Aseta pvrange = pdsheet.Kellot (1, 1) .Alaa (plr, plc) 

Koska alue on asetettu käyttämällä solualueiden koon muuttamisominaisuutta, pvrange muuttaa kokoa ja säätää kääntöaluetta vastaavasti. Joten pvrange muuttuu, onko rivejä tai sarakkeita lisätty tai poistettu.

Vaihe 11: On aika asettaa nivelvälimuisti, joka on niveltaulun lähde. Aseta lähdevälimuisti objektin pvcache avulla.

Koodi:

 'pivot-välimuisti Aseta pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 

Vaihe 12: Tässä eri tuotteiden myyntiraportti muunnetaan kääntötauluksi tämän kautta. Luo tyhjä kääntötaulu, johon voit lisätä tietojoukon edelleen.

Koodi:

 'uusi tyhjä niveltaulukko Aseta pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report") 

Vaihe 13: Kun nivel on lisätty, sinun on määritettävä eri kentät, jotka haluat lisätä pivot-taulukkoon. Lisää siis ensimmäisen rivin kenttä. Tässä ensimmäinen rivi alkaa tuotteella.

Koodi:

 'Lisää tuote riville, joka on täytetty pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Lopeta 

Vaihe 14: Seuraavaksi määritetään toinen kenttä, jonka haluat lisätä kääntötauluun. Aseta samalla tavalla toisen rivin kenttäkatu niveltaulukkoon.

Koodi:

 'Lisää katu riville täytetty & sijainti 2 pvsheet.PivotTables -sovelluksella ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with 

Vaihe 15: Aseta seuraava kenttä kääntötauluun ja seuraava on kaupunki. Anna koodi lisätäksesi kaupungin kentän.

Koodi:

 'Lisää kaupunki sarakkeeseen, joka on täytetty pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End with 

Vaihe 16: Tähän asti lisätyt tiedot olivat tekstityyppejä. Nyt meidän on lisättävä myyntimäärät niveltaulukkoon.

Koodi:

 'Lisää myynti-sarake tietokenttään pvsheet.PivotTables -sovelluksella ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with 

Vaihe 17: Olet lisännyt kentät, jotka tarvitaan kääntötaulun luomiseen. Ja kääntötaulukko on melkein valmis, nyt voit asettaa kääntötaulun muodon. Tämä määrittelee taulukon tyypin taulukkotyylin mukaan. Riviakselin asettelu on myös asetettu haluamaasi tapaan.

Koodi:

 'aseta muoto Pivot-taulukko pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 

Vaihe 18: Jos haluat näyttää riville arkistoidut arvoerät taulukkomuodossa, lisää alla oleva koodi alareunaan.

Koodi:

 "Näyttää kääntötaulukon taulukkomuodossa pvsheet.PivotTables (" Sales_Report "). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True 

Vaihe 19: Paina suorituspainiketta tai paina F5 suorittaaksesi koodi. Tämä tuottaa kääntötaulukon tietolähteestä. Rivien ja sarakkeiden tiedot muuttuvat kääntötaulukkoon. Kääntötaulukko näkyy kääntötaulukon laskentataululla.

Tarkista lähtö ja näet tietolähteen muuntuvan kääntötaulukkoon kuten alla, mainitut sarakkeet muunnetaan suodatinlomakkeiksi.

Kääntökentät ovat näkyvissä oikealla puolella. Voit tehdä muutoksia tarpeen mukaan, miten tietoja on esitelty.

Tietosi, olen antanut koodin alla.

Koodi:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet Kuten Worksheet Dim pdsheet Workslapina Dim plr niin pitkä Dim plc niin kauan kuin virhe Jatka seuraavaa sovellusta.DisplayAlerts = Väärä sovellus.ScreenUpdating = p. Worksheet ") .Poista 'poistaaksesi olemassa olevan kääntötaulukon laskentataulukon laskentataulukoista.Lisä jälkeen: = ActiveSheet' lisätäksesi uuden laskentataulukon ActiveSheet.Name =" pvsheet "'nimetäksesi taulukon uudelleen" pvsheet "virheeksi GoTo 0 Aseta pvsheet = Laskentataulut ("pvsheet") Aseta pdsheet = Laskentataulukot ("pdsheet") 'kaksi muuttujaa löytääksesi Viimeksi käytetty rivi ja sarake pdsheet-muodossa plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Solut (1, Sarakkeet.Lukumäärä) .Lopu (xlToLeft) .Sarakkeen 'alustava pivot-taulukon data-alue Aseta pvrange = pdsheet.Solut (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'uusi tyhjä kääntötaulukko Aseta pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Lisää tuote riville, joka on täytetty pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 päättyy' Insert Street to Rivi täytetty ja sijainti 2 Pvsheet.PivotTables -sovelluksella ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Lopeta kohdalla 'Lisää kaupunki sarakkeeseen, joka on täytetty pvsheet.PivotTables ("Sales_Report"). PivotFields (" Kaupunki ") .Orientation = xlColumnField .Position = 1 päättyy 'Lisää myynti-sarake tietokenttään pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End with' aseta ' muotoa Pivot-taulukko pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" "näyttää pivot-taulukon taulukkomuodossa. .DisplayAlerts = True Application.ScreenUpdating = True End Sub 

Muistettavat asiat

• Määritä lähdearkki, johon tiedot on toimitettava kääntötaululle.
• Aloitusrivi ja sarake on mainittava oikein.
• Käytä tarvittavia objekteja osoittaaksesi kääntyvä välimuisti, etäisyys, rivien alkaminen ja lopetus.
• Järjestä tietolähde oikeassa muodossa, koska tämä on automaatioprosessi.

Suositellut artikkelit

Tämä on VBA Pivot -taulukon opas. Tässä keskustellaan siitä, kuinka kääntötaulukko luodaan käyttämällä VBA-koodeja excel-sovelluksessa sekä esimerkki ja ladattava excel-malli. Voit myös katsoa seuraavia artikkeleita saadaksesi lisätietoja -

1. Kuinka poistaa Pivot-taulukko Excelistä?
2. VBA: n käyttö PowerPointissa
3. Pivot-taulukko, jossa useita arkkeja
4. Kuinka luoda Pivot-taulukkoleikkuri?
5. Päivitä Pivot-taulukko VBA: ssa
6. VBA on virhe jatkaa seuraavaksi (esimerkkejä)
7. Excel VBA -näytön päivitys