Tällä hetkellä voimme muuttaa tai päivittää pivot -taulukoita dynaamisesti Excel -taulukoiden tai dynaamisten nimettyjen alueiden avulla. Mutta nämä tekniikat eivät ole idioottivarmoja. Koska pivot -taulukko on vielä päivitettävä manuaalisesti. Jos sinulla on suuria tietoja, jotka sisältävät tuhansia rivejä ja sarakkeita, Excel -taulukot eivät auta sinua paljon. Sen sijaan se tekee tiedostostasi raskaan. Ainoa keino on siis VBA.
Tässä artikkelissa opimme, miten saamme pivot -taulukon muuttamaan tietolähteen automaattisesti. Toisin sanoen automatisoimme manuaalisen prosessin tietolähteen vaihtamisesta siten, että se sisältää dynaamisesti uusia rivejä ja sarakkeita, jotka on lisätty lähdetaulukoihin, ja heijastavat pivot -taulukon muutoksen välittömästi.
Kirjoita koodi lähdetietolomakkeeseen
Koska haluamme tämän olevan täysin automaattinen, käytämme arkkimoduuleja koodin kirjoittamiseen ydinmoduulin sijasta. Näin voimme käyttää laskentataulukon tapahtumia.
Jos lähdetiedot ja pivot -taulukot ovat eri arkeissa, kirjoitamme VBA -koodin pivot -taulukon tietolähteen muuttamiseksi taulukko -objektissa, joka sisältää lähdetiedot (ei pivot -taulukkoa).
Avaa VB -editori painamalla CTRL+F11. Siirry nyt Project Exploreriin ja etsi taulukko, joka sisältää lähdetiedot. Kaksoisnapsauta sitä.
Uusi koodausalue avautuu. Et ehkä näe muutoksia, mutta nyt sinulla on pääsy laskentataulukon tapahtumiin.
Napsauta vasemmanpuoleista avattavaa valikkoa ja valitse laskentataulukko. Valitse vasemmasta avattavasta valikosta deaktivoi. Näet tyhjän alikoodin, joka on kirjoitettu koodialueen nimen worksheet_deativate. Koodimme lähdetietojen dynaamiseen muuttamiseen ja pivot -taulukon päivittämiseen sisältyy tähän koodilohkoon. Tämä koodi suoritetaan aina, kun vaihdat tietolomakkeesta toiseen. Voit lukea kaikista laskentataulukon tapahtumista täältä.
Nyt olemme valmiita ottamaan koodin käyttöön.
Lähdekoodi päivittää pivot -taulukon dynaamisesti uudella alueella
Selittääkseni, miten se toimii, minulla on työkirja. Tämä työkirja sisältää kaksi arkkia. Taulukko1 sisältää lähdetiedot, jotka voivat muuttua. Taulukko2 sisältää pivot -taulukon, joka riippuu taulukon 2 lähdetiedoista.
Nyt olen kirjoittanut tämän koodin sheet1: n koodausalueelle. Käytän Worksheet_Deactivate -tapahtumaa, jotta tämä koodi päivittää pivot -taulukon aina, kun vaihdamme lähdetietolomakkeesta.
Yksityinen alityöarkki_Deactivate () Dim pt Pivot -taulukkona Dim pc PivotCache Dim source_data As Range lstrow = Solut (Rivien lukumäärä, 1) .End (xlUp) .Row lstcol = Solut (1, Columns.Count) .End (xlToLeft). Sarakejoukko source_data = Range (Solut (1, 1), Solut (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivot pc End Sub
Jos sinulla on samanlainen työkirja, voit kopioida nämä tiedot suoraan.Olen selittänyt, että tämä koodi toimii alla.
Voit nähdä tämän koodin vaikutuksen alla olevasta gifistä.
Miten tämä koodi muuttaa automaattisesti lähdetietoja ja päivittää pivot -taulukoita?
Ensinnäkin käytimme worksheet_deactivate -tapahtumaa. Tämä tapahtuma käynnistyy vain, kun koodia sisältävä taulukko vaihdetaan tai poistetaan käytöstä. Joten koodi toimii automaattisesti.
Jos haluat muuttaa pivot -taulukon lähdetietoja, muutamme tietoja pivot -välimuistissa.
Pivot -taulukko luodaan käyttämällä pivot -välimuistia. Pivot -välimuisti sisältää vanhat lähdetiedot, kunnes pivot -taulukkoa ei päivitetä manuaalisesti tai lähdetietoaluetta muutetaan manuaalisesti.
Olemme luoneet viittauksia pivot -taulukoiden nimiin pt, pivot -välimuistiin nimeltä pc ja alueeseen, jonka nimi on source_data. Lähdetiedot sisältävät kaikki tiedot.
Jotta koko taulukko saadaan dynaamisesti tietoalueeksi, määritämme viimeisen rivin ja viimeisen sarakkeen.
lstrow = Solut (Rivien lukumäärä, 1). Loppu (xlUp)
lstcol = Solut (1, Columns.Count) .End (xlToLeft) .Column
Näitä kahta numeroa käyttämällä määritellään lähdetiedot. Olemme varmoja, että lähdetietoalue alkaa aina A1: stä.
Aseta lähdetiedot = alue (solut (1, 1), solut (lstrow, lstcol))
Nyt meillä on dynaaminen lähdetieto. Meidän tarvitsee vain käyttää sitä pivot -taulukossa.
Tallennamme nämä tiedot pivot -välimuistiin, koska tiedämme, että pivot -välimuisti tallentaa kaikki tiedot.
Aseta pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Seuraavaksi määritämme pivot -taulukon, jonka haluamme päivittää. Koska haluamme päivittää pivot -taulukon1 (pivot -taulukon nimi. Voit tarkistaa pivot -taulukon nimen analysointi -välilehdestä samalla kun valitset pivot -taulukon.) Taulukolla 1, asetamme pt alla olevan kuvan mukaisesti.
Aseta pt = Sheet2.PivotTables ("PivotTable1")
Nyt yksinkertaisesti käytämme tätä pivot -välimuistia pivot -taulukon päivittämiseen. Käytämme pt -objektin changePivotCache -menetelmää.
pt.ChangePivotCache PC
Ja meillä on kääntöpöytämme automatisoitu. Tämä päivittää pivot -taulukon automaattisesti. Jos sinulla on useita taulukoita, joilla on sama tietolähde, käytä samaa välimuistia kussakin pivot -taulukko -objektissa.
Joten kyllä kaverit, tällä tavalla voit muuttaa dynaamisesti tietolähdealuetta Excelissä. Toivottavasti olin tarpeeksi selittävä. Jos sinulla on kysyttävää tästä artikkelista, kerro siitä minulle alla olevassa kommenttiosiossa.
Pivot -taulukoiden automaattinen päivittäminen VBA: n avulla: Voit päivittää pivot -taulukot automaattisesti käyttämällä VBA -tapahtumia. Päivitä pivot -taulukko automaattisesti tällä yksinkertaisella koodirivillä. Voit käyttää jompaakumpaa kolmesta automaattisen pivot -taulukoiden päivitysmenetelmästä.
Suorita makro, jos taulukkoon tehdään muutoksia määritetyllä alueella: VBA -käytännöissäsi sinun on suoritettava makroja tietyn alueen tai solun muuttuessa. Siinä tapauksessa käytämme muutostapahtumaa suorittaaksemme makroja, kun kohdealueelle tehdään muutos.
Suorita makro, kun taulukkoon tehdään muutoksia | Joten makron suorittamiseen aina, kun taulukko päivittyy, käytämme VBA: n laskentataulukkotapahtumia.
Yksinkertaisin VBA -koodi nykyisen rivin ja sarakkeen korostamiseen | Käytä tätä pientä VBA -katkelmaa korostamaan taulukon nykyinen rivi ja sarake.
Laskentataulukon tapahtumat Excel VBA: ssa | Laskentataulukkotapahtuma on todella hyödyllinen, kun haluat makrojesi suoritettavan, kun taulukossa tapahtuu tietty tapahtuma.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.VLOOKUP -toiminto Excelissä | Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietojasi tietyn arvon laskemiseksi. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
