seychellesartprojects.org

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 luo 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 sen hallitseminen ei olekaan niin helppoa, mutta kannattaa käyttää aikaa tämän oppimiseen. 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 kääntö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

Voit ladata tämän VBA-pivot-taulukon mallin täältä - VBA-pivot-taulukon malli

Pivot-taulukon luominen 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 pivot-taulukon luomista meidän on ensin luotava pivot-välimuisti tietojen lähteen määrittelemiseksi.

Tavallisessa laskentataulukon pivot-taulukossa, joka ei häiritse meitä, luodaan pivot-välimuisti taustalle. Mutta VBA: ssa meidän on luotava.

Tätä varten määritellään muuttuja PivotCache.

Koodi:

 Hämärä PCache-välimuisti 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, jotta voit lisätä pivot-taulukon laskentataulukon. Ilmoita muuttuja laskentataulukoksi.

Koodi:

 Himmennä PSheet laskentataulukoksi

Vaihe 5:  Ilmoita samalla tavalla kuin laskentataulukon sisältäviin tietoihin yksi muuttuja laskentataulukoksi.

Koodi:

 Himmennä DSheet taulukkona

Vaihe 6: Viimeisimmän 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 nyt vastaavasti Pivot Sheet ja Data Sheet.

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 tietolomakkeessa olisi tietoja tai lisäyksiä.

Vaihe 11: Ennen pivot-taulukon luomista 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 olet lisännyt sarakkeet 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 tehneet pivot-taulukon yhteenveto-osan, nyt meidän on muotoiltava taulukko. Alusta pivot-taulukko käyttämällä koodin alla.

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

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

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

Tällä tavoin VBA-koodauksen avulla 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 uudeksi" Pivot-taulukoksi "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 Rivi arkistoitu ja sijainti 2 PSheet.PivotTables ("Myyntiraportti"). PivotFields ("Tuote"). Orientation = xlRowField .Position = 2 Loppu "Lisää segmentti sarakkeeseen arkistoida" ja sijainti 1 PSheet.PivotTables-taulukon kanssa ("Myyntiraportti"). PivotFields ("Segmentti"). Orientation = xlColumnField .Position = 1 Loppu '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 ("Sales_Report"). 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