Alarivi: Opi käyttämään yksinkertaista makroa pivot-taulukoiden päivittämiseen automaattisesti aina, kun lähdetietoihin tehdään muutoksia. Jaan myös ei-makron ratkaisun pivot-taulukoiden päivittämiseen, kun tiedosto avataan. Sisältää video-opetusohjelman ja Excel-tiedostojen lataamisen.
Taitotaso: Keskitasoinen
Lataa Excel-tiedosto
Jos opit parhaiten tekemällä sen itse, voit lataa tiedosto, jota käytän videossa seuratakseni. Tässä on Excel-tiedosto, joka sisältää VBA-koodin.
Päivitä pivot-taulukko automaattisesti.xlsm (41,5 kt)
Päivitä pivot-taulukot automaattisesti
Voivatko pivot-taulukot päivittää välittömästi ja automaattisesti, kun niiden lähdetiedot muuttuvat?
Ehdottomasti. Se vaatii käyttöä todella yksinkertaisen makron, jonka näytän sinulle, miten voit luoda alla.
Jos et ole liian perehtynyt makroihin ja VBA, suosittelen tutustumaan maksuttomaan 3-osalliseen videosarjaan, josta pääsen alkuun makrojen kanssa & VBA.
Jos olet uusi pivot-taulukoiden käyttäjä, Minulla on sarja, joka opettaa sinulle, mitä he ovat ja miten niitä käytetään. Katso sarjan ensimmäinen video Pivot-taulukoissa & Koontinäytöt
Päivitämme pivot-taulukot automaattisesti kirjoittamalla makron yhdellä yksinkertaisella käskyllä. Tässä ohjeessa sanotaan periaatteessa: kun teen muutoksia laskentataulukkooni, päivitä kaikki pivot-taulukot ja datayhteydet. Tässä on makron luomisen vaiheet.
Avaa Visual Basic Editor.
Voit tehdä tämän napsauttamalla Visual Basic -painiketta valintanauhan Kehittäjä-välilehdessä.
Visual Basic -editorin avaamisen pikanäppäin on Alt + F11.
Jos et näe Kehittäjä-välilehdessä voit tehdä siitä näkyvän tässä olevien ohjeiden avulla. Sinun on tehtävä tämä vain kerran, ja sitten Kehittäjä-välilehti on aina näkyvissä aina, kun avaat Excelin tulevaisuudessa.
Avaa lähdetietosi sisältävä taulukko-moduuli.
Etsi Visual Basic -editorin Project Explorer -ikkunasta muutettava työkirja. Kyseisen työkirjan alla on lueteltu työkirjan taulukot. Valitse arkki, joka sisältää lähdetiedot. Kaksoisnapsauta sitten sitä.
Jos Project Explorer -ikkunaa ei näy, voit ottaa sen käyttöön View-näkymässä. valikko (pikanäppäin: Ctrl + R).
Lisää uusi tapahtuma laskentataulukon muutoksiin.
Kaksoisnapsauttamalla taulukkoa avautuu kyseisen objektin koodimoduuli. Koodimoduulissa haluamme luoda tapahtumamakron. Tee näin valitsemalla laskentataulukko vasemmalla olevasta avattavasta Objekti-ruudusta.
Tämä lisää Worksheet_SelectionChange-tapahtuman moduuli, jota emme itse asiassa halua, joten poistamme sen hetkessä. Ennen kuin siirrymme, siirry oikealla olevaan avattavaan Valikko-valikkoon ja valitse Muuta.
Tämä lisää uuden tapahtuma ylhäällä nimeltä Worksheet_Change. Nyt korostamme ja poistamme tarpeettoman koodin sen alapuolelta.
Worksheet_Change -tapahtumakro suoritetaan aina, kun muutos tehdään kyseisen taulukon soluihin. Voimme lisätä VBA-koodin Worksheet_Change-tapahtumaan suorittamaan toimintoja, kun käyttäjä muokkaa soluja.
Huomaa: Oletusarvoisesti lisätty SelectionChange-tapahtuma suoritetaan aina, kun käyttäjä valitsee solun taulukossa. Koska haluamme, että koodi suoritetaan vain, kun käyttäjä muokkaa / vaihtaa soluja, käytämme Change-tapahtumaa. Katso artikkelini VBA-koodimoduuleista & Makrojen suorittaminen käyttäjän tapahtumien perusteella saadaksesi lisätietoja taulukon moduuleista ja tapahtumista.
Lisää VBA-koodi päivittääkseen kaikki pivot-taulukot.
Kirjoita seuraavaksi, suoraan taulukon_Muutos-rivin alle tämä ohje:
ThisWorkbook.RefreshAll
RefreshAll-menetelmä päivittää kaikki työkirjan pivot-taulukot, kyselyt ja datayhteydet. Tämä toiminto on sama kuin jos napsautat Tiedot-välilehden Päivitä-painiketta manuaalisesti.
Tämän koodirivin lisääminen Worksheet_Change-tapahtumaan päivittää työkirjan aina, kun taulukkoon tehdään muutoksia, että koodi on sisään.
Pivot-taulukko & Lähdetiedot samalla taulukolla
Aleksandrs kysyi upean kysymyksen YouTube-videokommenteista. Jos pivot-taulukko ja lähdetiedot ovat samassa taulukossa, sinun on lisättävä koodi, jotta tapahtumat voidaan poistaa käytöstä.
Päivitys asettaa tapahtuman rekursiiviseen silmukkaan ja voi kaataa Excelin. Tässä on koodi sen estämiseksi.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Tarkistamalla, että makro toimii
Yksi tapa tarkistaa, toimiiko makro on testata sitä. Tee muutos lähdetietoihin ja tarkista, näkyvätkö ne pivot-taulukossa.Jos muutostasi ei ole helppo havaita, koska sinulla on liikaa tietoja tai jostain muusta syystä, on toinen tapa tarkistaa, käynnistyykö makro.
Voit napsauttaa VB-editorissa harmaa sarake vain laskentataulukon_muutosmakron vasemmalla puolella. Tällöin näkyy punainen ympyrä. Se korostaa myös koodirivin punaisella.
Tätä kutsutaan pysäytys- tai katkaisupisteeksi.
Pikanäppäin katkaisupisteen kytkemiseksi päälle / pois: F9
Aina kun tapahtuu makron laukaiseva toiminto, Excel hyppää VB-editoriin ja keskeyttää makron, jotta voit tarkistaa koodin. Meidän tapauksessamme tämä toiminto on mikä tahansa muutos, joka tehdään laskentataulukossa.
Voit sitten siirtyä läpi painamalla F8 tai paina F5 juosta loppuun (tai seuraavaan katkaisupisteeseen).
Jos teet muutoksia laskentataulukkoon eikä Excel vie sinua VB-editoriin, tiedät, että makro ei ole käynnissä. Tässä tapauksessa on todennäköistä, ettet ole tallentanut tiedostoa makroja tukevana työkirjana (.xlsm) ja / tai käytössä olevina makroina. Sinun on ehkä tallennettava &, sulje tiedosto, avaa se sitten uudelleen ja ota makrot käyttöön.
Voit poistaa makroon asettamasi katkaisukohdan seuraavasti: napsauta vain punaista ympyrää, jotta se katoaa (pikanäppäin: F9).
Näppäimistön pikavalinta kaikkien katkaisupisteiden poistamiseksi on: Ctrl + Vaihto + F9
Pivot-taulukoiden päivittäminen ilman Makro
Yksi haitta tämän makron käytölle pivot-taulukoiden päivittämiseen on, että kaikki Kumoa-historia menetetään joka kerta, kun makro suoritetaan. Toisin sanoen, kun napsautat Kumoa-painiketta (tai painat Ctrl + Z), Excel ei muista viimeistä tekemäsi asiaa, joten se ei voi kumota sitä. Näin ollen mitään ei tapahdu, eikä viimeistä muutostasi kumota.
On olemassa vaihtoehto, jonka avulla voit pitää Kumoa-historian. Tämä vaihtoehto kuitenkin päivittää pivot-taulukon vasta, kun työkirja avataan, ei aina, kun muutos tehdään. Näin voit käyttää tätä vaihtoehtoa.
Alkaen mistä tahansa pivot-taulukon solusta:
- Siirry valintanauhan Analysoi-välilehteen.
- Valitse Asetukset-painike.
- Siirry avautuvan uuden ikkunan Tiedot-välilehdelle.
- Valitse ruutu, jossa lukee ”Päivitä tiedot tiedostoa avattaessa”.
Kun olet napsauttanut OK, saatat saada seuraavan varoitusviestin, jos sinulla on useita pivot-taulukoita, jotka on luotu samasta lähdetietoryhmästä. Napsauta vain OK päästäksesi läpi.
Jälleen vain vertailun vuoksi, Jos käytät tätä vaihtoehtoa, säilytät Kumoa-historian, mutta se päivittää pivot-taulukon vain, kun työkirja suljetaan ja avataan uudelleen.Jos käytät makro-asetusta, menetät Kumoa-historian, mutta pivot-taulukko päivittää automaattisesti tes aina, kun työkirjaan tehdään muutoksia.
Muunnelmat pivot-taulukoiden päivittämiseen
Tarkastelemamme makro ei vain päivitä pivot-taulukoita, mutta myös kaikki kyselyt. . Jos haluat päivittää vain pivot-taulukot, voit korvata ”ThisWorkbook.RefreshAll” -komennon tällä koodilla:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Jokainen pivot-taulukko on liitetty taustalla oleva pivot-välimuisti, joka on kytketty lähdetietoihin. Koodi silmukkaa kaikki työkirjan pivot-välimuistit ja päivittää ne kaikki. Saman lähdealueen pivot-taulukot voivat jakaa pivot-välimuistit, joten tämä menetelmä on nopeampi kuin kaikkien pivot-taulukoiden silmukointi .
Vastaavasti sanotaan, että haluat päivittää vain yhden tietyn pivot-taulukon. Siinä tapauksessa voit vaihtaa ”ThisWorkbook.RefreshAll” -koodin alla olevalla koodilla.
Ja lopuksi, jos käytät Power Queryä ja haluat poistaa taustan päivityksen käytöstä niin, että kyselyt päivitetään ENNEN pivot-taulukoita, olen kirjoittanut artikkelin, jossa kerrotaan, miten se tehdään poistamalla kyselyn taustapäivitys käytöstä.
Käytä Deaktivoi tapahtuma sen sijaan
Toinen vaihtoehto on käyttää Worksheet_Deactivate-tapahtumaa Worksheet_Change -vaihtoehdon sijaan. Worksheet_Deactivate-tapahtuma suoritetaan aina, kun käyttäjä poistuu taulukosta ja valitsee toisen taulukon. Lähdetiedot, pivot-taulukko päivitetään automaattisesti, kun ne menevät mihin tahansa muuhun taulukkoon, mukaan lukien pivot-taulukon sisältävät taulukot.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Tämä koodi sijoitettaisiin edelleen taulukkomoduuliin, joka sisältää lähdetiedot. Tämä on hyvä vaihtoehto, jos pivot-taulukot tai datayhteydet käyttävät muutama sekunti tai pidempi päivitys, etkä halua odottaa joka kerta, kun lähdetietoihin tehdään muutoksia.
Ainoa kerta, kun et halua käyttää tätä, on, jos pivot-taulukko ja lähde tiedot ovat samalla arkilla.Se on yleensä harvinainen tapaus, jota en yleensä suosittele.
Kiitos Tedin ehdotuksen tästä.
Säästä aikaa & Hämmennys
Toivon, että tämä artikkeli auttaa säästämään aikaa ja helpottaa tiedostojesi käyttäjiä. Se voi myös estää hämmennystä, kun unohdat päivittää pivot-taulukot ennen raporttien lähettämistä. Usko minä, olen tehnyt tämän virheen useammin kuin haluaisin myöntää … 🙂
