VBA-kääntötaulukko - Vaiheet pivot-taulukon luomiseen VBA: ssa

Excel VBA pivot -taulukko

Pivot-taulukot ovat ytimenä tiivistää raportti suuresta tietomäärästä. Voimme myös automatisoida pivot-taulukon luomisen VBA-koodauksen avulla. Ne ovat tärkeä osa mitä tahansa raporttia tai koontinäyttöä, Excelissä on helppo luoda taulukoita napilla, mutta VBA: ssa joudumme kirjoittamaan joitain koodeja pivot-taulukon automatisoimiseksi, ennen kuin Excel 2007: tä ja sen vanhempia versioita VBA: ssa ei tarvinnut luoda välimuisti pivot-taulukoille, mutta Excel 2010: ssä ja sen uudemmissa versioissa välimuistit vaaditaan.

VBA voi säästää meille paljon aikaa työpaikallamme. Vaikka hallitseminen ei ole niin helppoa, mutta kannattaa viettää aikaa oppia tämä. Kesti 6 kuukautta ymmärtääksesi pivot-taulukoiden luomisen prosessin VBA: n kautta. Tiedät, mitä nuo kuusi kuukautta ovat tehneet minulle ihmeitä, koska tein niin paljon virheitä yrittäessäni luoda pivot-taulukkoa.

Mutta itse asiassa olen oppinut virheistäni, ja kirjoitan nyt tämän artikkelin osoittaakseni, kuinka voit luoda pivot-taulukoita koodilla.

Yhdellä napin painalluksella voimme luoda raportteja.

Vaiheet pivot-taulukon luomiseen VBA: ssa

Pivot-taulukon luomiseksi on tärkeää, että sinulla on tietoja. Tätä varten olen luonut joitain nuken tietoja. Voit ladata työkirjan seurata kanssani samoilla tiedoilla.

Vaihe 1: Pivot-taulukko on objekti, johon viitataan pivot-taulukossa, joka ilmoittaa muuttujan pivot-taulukoiksi.

Koodi:

Sub PivotTable () Dim PTable kuin PivotTable End Sub

Vaihe 2: Ennen kuin luomme pivot-taulukon ensin, meidän on luotava pivot-välimuisti tietojen lähteen määrittelemiseksi.

Tavallisissa laskentataulukoissa pivot-taulukko, joka ei häiritse meitä, luo pivot-välimuistin taustalle. Mutta VBA: ssa meidän on luotava.

Määritä tätä varten muuttuja PivotCache.

Koodi:

Hämärä PCache-tiedosto PivotCache-välimuistina

Vaihe 3: Määritä muuttuja alueeksi määrittämään pivot-data-alue.

Koodi:

Hämärä alue Alue

Vaihe 4: Pivot-taulukon lisäämiseksi tarvitsemme erillisen taulukon, joka lisää taulukoita pivot-taulukolle muuttujan julistamiseksi laskentataulukoksi.

Koodi:

Himmennä PSheet laskentataulukoksi

Vaihe 5: Vastaavasti laskentataulukon sisältävien tietojen viittaamiseksi julista yksi muuttuja laskentataulukoksi.

Koodi:

Himmennä DSheet taulukkona

Vaihe 6: Viimeiseksi käytetyn rivin ja sarakkeen löytämiseksi määritä vielä kaksi muuttujaa Pitkäksi.

Koodi:

Dim LR yhtä pitkä Dim LC yhtä pitkä

Vaihe 7: Nyt meidän on lisättävä uusi taulukko pivot-taulukon luomiseksi. Ennen sitä, jos siellä on kääntötaulukko, meidän on poistettava se.

Vaihe 8: Aseta objektimuuttujalle PSheet ja DSheet Pivot Sheet ja Data Sheet vastaavasti.

Vaihe 9: Etsi viimeksi käytetty rivi ja viimeksi käytetty sarake lomakkeesta.

Vaihe 10: Aseta nyt kääntöalue käyttämällä viimeistä riviä ja viimeistä saraketta.

Tämä asettaa data-alueen täydellisesti. Se valitsee datasarjan automaattisesti, vaikka tietolomakkeeseen olisi lisätty tai poistettu tietoja.

Vaihe 11: Ennen kuin luot pivot-taulukon, meidän on luotava pivot-välimuisti. Aseta pivot-välimuistimuuttuja käyttämällä alla olevaa VBA-koodia.

Vaihe 12: Luo nyt tyhjä pivot-taulukko.

Vaihe 13: Kun pivot-taulukko on lisätty, meidän on ensin lisättävä rivikenttä. Joten lisätään rivikenttä Maa-sarakkeeksi.

Huomaa: Lataa työkirja tietosarakkeiden ymmärtämiseksi.

Vaihe 14: Nyt lisätään vielä yksi kohde rivikenttään toisena kohdekohtana. Lisään Tuotteen toisen rivikohdan rivikenttään.

Vaihe 15: Kun sarakkeet on lisätty rivikenttään, meidän on lisättävä arvot sarakekenttään. Lisään ”Segmentti” sarakekenttään.

Vaihe 16: Nyt meidän on lisättävä numeroita tietokenttään. Joten lisää "Myynti" tietokenttään.

Vaihe 17: Olemme valmis pivot-taulukon yhteenveto-osaan. Nyt meidän on muotoiltava taulukko. Alusta pivot-taulukko seuraavalla koodilla.

Huomaa: Jos haluat enemmän erilaisia ​​taulukotyylejä, tallenna ne makro ja hae taulukotyylit.

Jos haluat näyttää rivin arkistoidut arvot taulukkomuodossa, lisää alla oleva koodi alareunaan.

Ok, olemme valmiit, jos suoritamme tämän koodin F5-näppäimellä tai manuaalisesti, meidän pitäisi saada tällainen pivot-taulukko.

Tällä tavoin VBA-koodausta käyttämällä voimme automatisoida pivot-taulukon luomisen.

Viitteeksi olen antanut alla olevan koodin.

Sub PivotTable () Dim PTable kuin PivotTable Dim PCache as PivotCache Dim PRange as Range Dim PSheet As Sheheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Jatka seuraavaa Application.DisplayAlerts = False Application.ScreenUpdating = Vääriä laskentataulukoita ("Pivot Taulukko "). Poista 'Tämä poistaa aiemmin luodun pivot-taulukon laskentataulukon laskentataulukot.Lisää jälkeen: = ActiveSheet' Tämä lisää uuden laskentataulukon ActiveSheet.Name =" Pivot-taulukko "'Tämä nimeää laskentataulukon uudelleen" Pivot-taulukkona "On Go Go 0 Aseta PSheet = laskentataulukot ("Pivot-taulukko") Aseta DSheet = laskentataulukot ("tietolomake") 'Etsi viimeksi käytetty rivi ja sarake tietolomakkeesta LR = DSheet.Cells (Rivit.luku, 1) .End (xlUp) .Rivi LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Set pivot table data range Set PRange = DSheet.Cells (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Luo tyhjä pivot-taulukko Aseta PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Solut (1, 1), TableName: = "Myyntiraportti") 'Lisää maa riville, joka on arkistoitu PSheet.PivotTables ("Myynnin_raportti"). PivotFields ("Maa") .Orientation = xlRowField .Position = 1 Loppu "Lisää tuote kohteeseen" Rivi arkistoitu ja sijainti 2 PSheet.PivotTables ("Myyntiraportti"). PivotFields ("Tuote"). Orientation = xlRowField .Sijainti = 2 Loppu "Lisää segmentti sarakkeeseen arkistoida" ja sijainti 1 PSheet.PivotTables ("Myyntiraportti"). PivotFields ("Segmentti"). Orientation = xlColumnField .Position = 1 Lopeta 'Lisää myynti -sarakkeeseen tietokenttään PSheet.PivotTables ("Sales_Report"). PivotFields ("Sales"). Orientation = xlDataField .Position = 1 Loppu 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Report2"). "PivotStyleMedium14" 'Näytä taulukkomuodossa PSheet.PivotTables ("Myyntiraportti"). 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