Kaikkien pivot-taulukoiden tietolähteen dynaaminen päivittäminen Excelissä

Edellisessä artikkelissa opimme, kuinka voit muuttaa ja päivittää yksittäisiä pivot-taulukoita kutistuvilla tai laajenevilla tietolähteillä.

Tässä artikkelissa opi, kuinka voimme saada kaikki työkirjan pivot-taulukot muuttamaan tietolähdettä automaattisesti. Toisin sanoen, sen sijaan, että vaihdat yhtä pivot-taulukkoa kerrallaan, yritämme muuttaa kaikkien työkirjan pivot-taulukoiden tietolähdettä sisällyttämään dynaamisesti lähdetaulukkoihin lisätyt uudet rivit ja sarakkeet ja heijastamaan pivot-taulukoiden muutosta välittömästi. / p>

Kirjoita koodi lähdetietolomakkeeseen

Koska haluamme tämän olevan täysin automaattinen, käytämme taulukon moduuleja koodin kirjoittamiseen ydinmoduulin sijaan. Tämä antaa meille mahdollisuuden käyttää laskentataulukon tapahtumia.

Jos lähdetiedot ja pivot-taulukot ovat eri taulukoissa, kirjoitamme VBA-koodin pivot-taulukon tietolähteen muuttamiseksi taulukkobjektissa, joka sisältää lähdetiedot ( ei sisällä pivot-taulukoita).

Avaa VB-editori painamalla CTRL + F11. Siirry nyt projektinhallintaan ja etsi lähdetiedot sisältävä taulukko. Kaksoisnapsauta sitä.

Uusi koodausalue avautuu. Et ehkä näe muutoksia, mutta nyt sinulla on pääsy laskentataulukon tapahtumiin.

Napsauta vasenta pudotusvalikkoa ja valitse laskentataulukko. Valitse vasemmasta avattavasta valikosta deaktivointi. Näet tyhjän alikoodin, joka on kirjoitettu koodialueen nimelle workheet_deativate. Koodimme dynaamisesti muuttaa lähdetietoja ja päivittää kääntötaulukko menee tähän koodilohkoon. Tämä koodi toimii aina, kun vaihdat tietolomakkeesta mihin tahansa muuhun. Voit lukea kaikista laskentataulukon tapahtumista täältä.

Nyt olemme valmiit toteuttamaan koodin.

Lähdekoodi päivittää dynaamisesti kaikki työkirjan pivot-taulukot uudella alueella

Selitän, miten se toimii, minulla on työkirja. Tämä työkirja sisältää kolme arkkia. Taulukko1 sisältää lähdetiedot, jotka voivat muuttua. Taulukot2 ja Taulukko3 sisältävät pivot-taulukoita, jotka riippuvat taulukon2 lähdetiedoista.

Nyt olen kirjoittanut tämän koodin arkin1 koodausalueelle. Käytän Worksheet_Deactivate-tapahtumaa, jotta tämä koodi suoritetaan päivittämään pivot-taulukko aina, kun siirrymme lähdetiedostosta.

Jos sinulla on samanlainen työkirja, voit kopioida nämä tiedot suoraan. Olen selittänyt, että tämä koodi toimii alla, jotta voit muokata sitä tarpeidesi mukaan.

Näet tämän koodin vaikutuksen alla olevassa gifissä.

Kuinka tämä koodi muuttuu automaattisesti lähdetiedot ja päivitätkö pivot-taulukot?

Ensinnäkin käytimme workheet_deactivate-tapahtumaa. Tämä tapahtuma käynnistyy vain, kun koodin sisältävä taulukko vaihdetaan tai deaktivoidaan. Koodi toimii siis automaattisesti.

Saadaksesi koko taulukon dynaamisesti tietoalueeksi, määritämme viimeisen rivin ja viimeisen sarakkeen.

lstrow = Solut (Rivit.Määrä, 1) .End (xlUp) .Rivi

lstcol = Solut (1, Columns.Count) .End (xlToL eft) .Sarake

Näillä kahdella luvulla määritämme lähteen_tiedot. Olemme varmoja, että lähdetietojen alue alkaa aina A1: stä. Voit määrittää oman soluviitteen.

Aseta source_data = Alue (Solut (1, 1), Solut (lstrow, lstcol))

Nyt meillä on lähdetiedot, jotka ovat dynaaminen. Meidän on vain käytettävä sitä pivot-taulukossa.

Koska emme tiedä, kuinka monta pivot-taulukkoa työkirja sisältää kerrallaan, käymme läpi jokaisen taulukon ja pivot-taulukot. että yhtään pivot-taulukkoa ei ole jäljellä. Tätä varten käytämme sisäkkäisiä silmukoita.

Jokaiselle w: lle ThisWorkbook.Worksheets

Jokaiselle p: lle ws.PivotTable-taulukossa

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Seuraava pt

Seuraava ws

Ensimmäinen silmukka silmukkaa jokaisen taulukon läpi. Toinen silmukka toistaa jokaisen taulukon pivot-taulukon.

Pivot-taulukot määritetään Käytämme pt-objektin ChangePivotCache-menetelmää. Luodaan pivot-välimuisti dynaamisesti ThisWorkbook.PivotCaches.Create

-menetelmällä. Tämä menetelmä vaatii kaksi muuttujaa SourceType ja SourceData. Lähdetyypiksi julistetaan xlDatabase ja Lähdetietoina välitämme aiemmin laskemamme lähde_data-alueen.

Ja että minä se se. Kääntöpöydämme ovat automatisoituja. Tämä päivittää kaikki työkirjan pivot-taulukot automaattisesti.

Joten joo, kaverit, näin voit muuttaa dynaamisesti kaikkien työkirjan pivot-taulukoiden tietolähde-alueita Excelissä. Toivon, että olin riittävän selittävä. Jos sinulla on kysyttävää tästä artikkelista, ilmoita siitä minulle alla olevassa kommenttiosassa.

Kuinka pivot-taulukon tietolähdealuetta päivitetään dynaamisesti Excelissä: Pivot-välimuistit muuttavat dynaamisesti pivot-taulukoiden lähdetietoja. Nämä muutama rivi voivat päivittää dynaamisesti minkä tahansa pivot-taulukon muuttamalla lähdetietojen aluetta.

Pivot-taulukoiden automaattinen päivitys VBA: n avulla: Voit päivittää pivot-taulukot automaattisesti VBA-tapahtumien avulla. Käytä tätä yksinkertaista koodiriviä pivot-taulukon päivittämiseen automaattisesti. Voit käyttää jompaa kumpaa kolmesta tapasta päivittää pivot-taulukot automaattisesti.

Suorita makro, jos taulukkoon tehdään muutoksia määritetyllä alueella: VBA-käytännöissäsi tarvitsisi suorittaa makroja, kun tietty alue tai solumuutokset. Tällöin makrojen suorittamiseksi, kun kohdealueeseen tehdään muutos, käytämme muutostapahtumaa.

Suorita makro, kun arkille tehdään muutoksia | Joten makron suorittamiseksi aina, kun taulukko päivittyy, käytämme VBA: n laskentataulukon tapahtumia.

Yksinkertaisin VBA-koodi korostaaksesi nykyisen rivin ja sarakkeen | Käytä tätä pientä VBA-katkelmaa korostaaksesi taulukon nykyisen rivin ja sarakkeen.

Taulukon tapahtumat Excel VBA: ssa | Laskentataulukotapahtuma on todella hyödyllinen, kun haluat, että makrosi suoritetaan, kun taulukossa on tietty tapahtuma.

Suosittuja artikkeleita:

50 Excel-pikakuvaketta tuottavuuden parantamiseksi | Hanki nopeampi tehtäväsi. Nämä 50 pikanäppäintä saavat sinut entistä nopeammin työskentelemään Excelissä. VLOOKUP-toiminto Excelissä | Tämä on yksi käytetyimmistä ja suosituimmista Excelin toiminnoista, jota käytetään hakemaan arvoa eri alueilta ja taulukoilta.

COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietojasi tietyn arvon laskemiseksi. Countif-toiminto on välttämätön koontinäytön valmistelemiseksi.

Kuinka käyttää SUMIF-toimintoa Excelissä | Tämä on toinen olennainen hallintapaneelin toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.