Edellisessä artikkelissa opimme, kuinka voit muuttaa ja päivittää yksittäisiä pivot -taulukoita dynaamisesti pienentyvillä tai laajenevilla tietolähteillä.
Tässä artikkelissa opimme, kuinka voimme saada kaikki työkirjan pivot -taulukot muuttamaan tietolähteen automaattisesti. Toisin sanoen yhden pivot -taulukon muuttamisen sijaan yritämme muuttaa työkirjan kaikkien pivot -taulukoiden tietolähdettä sisältämään dynaamisesti uudet rivit ja sarakkeet, jotka on lisätty lähdetaulukoihin, ja heijastamaan pivot -taulukoiden muutosta 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 -taulukoita).
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ää dynaamisesti kaikki työkirjan pivot -taulukot uudella alueella
Selittääkseni, 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 arkin 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.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Viimeisen rivin ja sarakkeen numeron määrittäminen lstrow = Solut (Rows.Count, 1) .End (xlUp) .Row lstcol = Solut (1, Columns.Count) .End (xlToLeft) .Column 'Uuden alueen asettaminen Aseta source_data = Range (Solut (1, 1), Solut (lstrow, lstcol))' Kiertämään jokaisen taulukon ja pivot -taulukon jokaisen tämän työkirjan ws: n kohdalle. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Seuraava pt Seuraava ws End Sub
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.
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.
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ä. Voit määrittää oman alkavan soluviittauksen.
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.
Koska emme tiedä kuinka monta pivot -taulukkoa työkirja sisältää kerrallaan, selaamme jokaisen taulukon jokaisen taulukon ja pivot -taulukot. Joten pivot -taulukkoa ei ole jäljellä. Tätä varten käytämme sisäkkäisiä silmukoita.
Jokaiselle ws: lle tässä työkirjassa
Jokaiselle pt: lle ws.Pivot -taulukot
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Lähdetyyppi: = xlTietokanta, _
Lähdetiedot: = lähdetiedot)
Seuraava pt
Seuraava ws
Ensimmäinen silmukka kulkee jokaisen arkin läpi. Toinen silmukka iteroi jokaisen taulukon pivot -taulukon yli.
Pivot -taulukot on määritetty muuttujalle pt. Käytämme pt -objektin ChangePivotCache -menetelmää. Luomme pivot -välimuistin dynaamisesti ThisWorkbook.PivotCaches.Create -palvelun avulla
Menetelmä. Tämä menetelmä käyttää kahta muuttujaa SourceType ja SourceData. Lähdetyypiksi ilmoitamme xlDatabase ja lähdetiedona välitämme aikaisemmin lasketun source_data -alueen.
Ja se on siinä. Meillä on kääntöpöydät automatisoitu. Tämä päivittää automaattisesti kaikki työkirjan pivot -taulukot.
Joten kyllä kaverit, tällä tavalla voit muuttaa dynaamisesti Excelin työkirjan kaikkien pivot -taulukoiden tietolähdealueita. Toivottavasti olin tarpeeksi selittävä. Jos sinulla on kysyttävää tästä artikkelista, kerro siitä minulle alla olevassa kommenttiosiossa.
Pivot -taulukon tietolähdealueen päivittäminen dynaamisesti Excelissä: Pivot -taulukoiden lähdetietoalueen muuttamiseksi dynaamisesti käytämme pivot -välimuisteja. Nämä muutamat rivit voivat päivittää dynaamisesti minkä tahansa pivot -taulukon muuttamalla lähdetietoaluetta.
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.