Alsó sor: Ismerje meg, hogyan használhat egyszerű makrót a pivot-táblák automatikus frissítésére, amikor a forrásadatok módosulnak. Emellett megosztok egy nem makrós megoldást a pivot táblák frissítésére a fájl megnyitásakor. Tartalmazza a videó bemutatót és az Excel fájlok letöltését.
Képzettségi szint: Középszintű
Töltse le az Excel fájlt
Ha egyedül tanulva tanul a legjobban, akkor töltse le azt a fájlt, amelyet a videóban használok a követéshez. Itt van az Excel fájl, amely a VBA kódot tartalmazza.
A kimutató táblázat automatikus frissítése.xlsm (41,5 KB)
A kimutató táblázatok automatikus frissítése
Frissíthetők-e a kimutató táblázatok azonnal és automatikusan, amikor a forrásadataik megváltoznak?
Egyáltalán. Használatot igényel egy igazán egyszerű makróból, amelyet az alábbiakban bemutatok, hogyan hozhat létre.
Ha nem ismeri túl a makrókat és VBA, azt javaslom, hogy nézze meg az ingyenes 3 részes videósorozatomat a Makrók használatának megkezdéséről & VBA.
Ha még nem ismeri a pivot táblákat, Van egy sorozatom, amellyel végigvezetek benneteket abban, hogy mik ők és hogyan kell használni őket. Nézze meg a sorozat első videóját a forgó táblákon & Irányítópultok
A pivot-táblázataink automatikus frissítéséhez makrót fogunk írni egy egyszerű utasítással. Ez az utasítás alapvetően azt mondja: amikor módosítom a munkalapomat, frissítse az összes forgatótáblát és adatkapcsolatot. A makró létrehozásának lépései:
Nyissa meg a Visual Basic Editor alkalmazást.
Ezt a szalag Fejlesztő fülén található Visual Basic gombra kattintva teheti meg.
A Visual Basic szerkesztő megnyitásához a billentyűparancs az Alt + F11.
Ha nem látja a Fejlesztő lapon, az itt található utasítások segítségével láthatóvá teheti. Ezt csak egyszer kell megtennie, és akkor a Fejlesztő fül mindig látható lesz minden alkalommal, amikor a jövőben megnyitja az Excel programot.
Nyissa meg a forrásadatokat tartalmazó Lapmodult.
A Visual Basic szerkesztő Project Explorer ablakában keresse meg a módosítani kívánt munkafüzetet. A munkafüzet alatt fel vannak sorolva a munkafüzet lapjai. Válassza ki a forrásadatokat tartalmazó lapot. Ezután kattintson duplán rá.
Ha nem látja a Project Explorer ablakot, engedélyezheti a Nézetből menü (billentyűparancs: Ctrl + R).
Új esemény hozzáadása a munkalap módosításához.
A lapra duplán kattintva megnyílik az adott objektum kódmodulja. A kód modulon belül egy esemény makrót szeretnénk létrehozni. Ehhez válassza a Munkalap lehetőséget a bal oldali Objektum legördülő mezőben.
Ez hozzáad egy Worksheet_SelectionChange eseményt a modult, amelyet valójában nem szeretnénk, ezért csak egy pillanat alatt töröljük. Mielőtt ezt megtennénk, menjünk a jobb oldali Eljárás legördülő menübe, és válasszuk a Módosítás lehetőséget.
Ez új elemet ad hozzá esemény tetején Worksheet_Change néven. Most kiemeljük és töröljük az alatta lévő felesleges kódot.
A Worksheet_Change esemény makró a módosítás végrehajtásakor futtatható a munkalap celláira. Hozzáadhatunk VBA-kódot a Worksheet_Change eseményhez, hogy műveleteket hajtsunk végre, amikor a felhasználó cellákat szerkeszt.
Megjegyzés: Az alapértelmezés szerint hozzáadott SelectionChange esemény akkor fut, amikor a felhasználó cellát választ a cellában. Mivel csak azt akarjuk, hogy a kód akkor fusson, amikor a felhasználó cellákat szerkeszt / változtat, a Change eseményt használjuk. Tekintse meg a VBA kódmodulokról szóló cikkemet & A makrók futtatása felhasználói események alapján, hogy többet megtudjon a lapmodulokról és eseményekről.
Adja hozzá a VBA kódot a frissítéshez minden pivot tábla.
Ezután, közvetlenül a Munkalap_változtatás sor alá írja be ezt az utasítást:
ThisWorkbook.RefreshAll
A RefreshAll metódus frissíti a munkafüzet összes kimutatótábláját, lekérdezését és adatkapcsolatát. Ez a művelet ugyanaz, mintha manuálisan kattintana az Adatok fül Frissítés gombra.
Ha hozzáadja ezt a kódsort a Worksheet_Change eseményhez, akkor a munkafüzet frissül, ha a munkalapon módosítást hajtanak végre, amely a kód be.
Pivot Table & Forrásadatok ugyanazon a lapon
Aleksandrs nagyszerű kérdést tett fel a YouTube-videomegjegyzésekben. Ha a pivot tábla és a forrásadatok ugyanazon a lapon vannak, akkor hozzá kell adnia egy kódot az események letiltásához.
A frissítés az eseményt rekurzív ciklusba helyezi, és az Excel összeomlik. Itt van a kód, amely megakadályozza ezt.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Ellenőrizze, hogy a makró fut-e
Az egyik módja annak ellenőrzésére, hogy a makró működik-e tesztelni. Változtasson a forrásadatokon, és nézze meg, hogy ezek tükröződnek-e a kimutató táblázatban.Ha a változtatást nem könnyű észrevenni, mert túl sok az adata, vagy valamilyen más okból kifolyólag, van egy másik módja annak ellenőrzésére, hogy a makrója elindul-e.
A VB szerkesztőben kattintson a szürke oszlop a munkalap_változtatása makró bal oldalán. Ezáltal egy piros kör jelenik meg. Ez a kódsor piros színnel is kiemeli.
Ezt stopnak vagy töréspontnak hívják.
A töréspont be- és kikapcsolásához a billentyűparancs a következő: F9
Most, amikor olyan művelet történik, amely kiváltja a makrót, az Excel a VB Szerkesztőhöz ugrik, és szünetelteti a makrót, hogy ellenőrizhesse a kódot. Esetünkben ez a művelet a munkalapon végrehajtott bármilyen változás.
Ezután az F8 billentyűkombinációval lépegethet sort, vagy nyomja meg az F5 billentyűt a végéig (vagy a következő töréspontig) történő futtatáshoz.
Ha változtat a munkalapon, és az Excel nem vonja be a VB szerkesztőbe, akkor tudja, hogy probléma van a makró nem fut. Ebben az esetben valószínűleg nem mentette a fájlt makróképes munkafüzetként (.xlsm) és / vagy engedélyezett makróként. Lehet, hogy el kell mentenie & zárja be a fájlt, majd nyissa meg újra és engedélyezze a makrókat.
A makrón elhelyezett töréspont eltávolításához: csak kattintson a piros körre, hogy eltűnjön (billentyűparancs: F9).
Az összes töréspont törléséhez a billentyűparancs a következő: Ctrl + Shift + F9
Pivot-táblák frissítése Makró
Ennek a makrónak a pivot-táblák frissítésére történő felhasználásának egyik hátránya, hogy minden visszavonási előzmény elvész minden egyes makró futtatásakor. Más szavakkal, amikor a Visszavonás gombra kattint (vagy megnyomja a Ctrl + Z billentyűkombinációt), az Excel nem emlékszik az utoljára tettekre, ezért nem tudja visszavonni. Következésképpen semmi sem fog történni, és az utolsó változtatást nem vonják vissza.
Van egy alternatíva, amely lehetővé teszi a Visszavonási előzmények megőrzését. Ez az alternatíva azonban csak akkor frissíti a forgatótáblát, amikor a munkafüzet megnyílik, nem minden alkalommal, amikor módosításra kerül sor. Így használhatja ezt a lehetőséget.
A pivot tábla bármely cellájából kiindulva:
- Nyissa meg a szalag Elemzés lapját.
- Válassza az Opciók gombot.
- Nyissa meg a megnyíló új ablakban az Adatok lapot.
- Jelölje be az “Adatok frissítése a fájl megnyitásakor” jelölőnégyzetet.
Az OK gombra kattintás után a következő figyelmeztető üzenetet kaphatja meg, ha több pivot-táblát hoz létre ugyanazon forrásadattartományból. Csak kattintson az OK gombra a továbbjutáshoz.
Ismét, csak összehasonlításképpen, Ha ezt az opciót használja, megőrzi az előzmények visszavonását, de csak akkor frissíti a kimutatási táblázatot, ha a munkafüzet bezárult és újra megnyílt. Ha a makró opciót használja, akkor elveszíti az előzmények visszavonását, de a kimutatási táblázat automatikusan frissíti tes, ha bármilyen változás történik a munkafüzetben.
Változatok a pivot-táblák frissítéséhez
Az általunk megtekintett makró nemcsak a pivot-táblákat, hanem az összes kérdést is frissíti. . Ha csak a pivot táblákat szeretné frissíteni, akkor a “ThisWorkbook.RefreshAll” parancsot ezzel a kóddal cserélheti le:
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
Minden egyes kimutató tábla egy mögöttes pivot-gyorsítótár, amely a forrásadatokhoz kapcsolódik. A kód a munkafüzet összes pivot-gyorsítótárát végigkíséri, és mindegyiket frissíti. Az azonos forrástartományból származó pivot-táblák megoszthatják a pivot-cache-eket, így ez a módszer gyorsabb, mint az összes pivot-tábla hurkolása .
Hasonlóképpen mondjuk azt is, hogy csak egy adott pivot táblát szeretne frissíteni. Ebben az esetben kicserélheti a “ThisWorkbook.RefreshAll” kódot az alábbi kóddal.
És végül, ha ha a Power Query programot használja, és le szeretné tiltani a háttérfrissítést, hogy a lekérdezések frissüljenek az elfordulási táblázatok ELŐTT, írtam egy cikket, amely elmagyarázza, hogyan lehet ezt megtenni a háttérfrissítés letiltásával a lekérdezéseken.
Használja a Esemény deaktiválása helyett
Egy másik lehetőség a Worksheet_Deactivate esemény használata a Worksheet_Change helyett. A Worksheet_Deactivate esemény minden alkalommal lefut, amikor a felhasználó elhagyja a lapot, és másik lapot választ. Ez lehetővé teszi a felhasználónak, hogy minden változtatást elvégezzen a forrásadatokat, akkor a pivot tábla automatikusan frissül, amikor más lapra kerülnek, beleértve azokat is, amelyek tartalmazzák a pivot táblázatot.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Ez a kód továbbra is a forrásadatokat tartalmazó lapmodulba kerül. Ez jó lehetőség, ha a kimutató táblák vagy az adatkapcsolatok néhány másodpercig vagy hosszabb ideig a frissítéshez, és nem akarsz megvárni minden alkalommal, amikor a forrásadatok módosulnak.
Ezt csak akkor érdemes használni, ha a pivot tábla és a forrás az adatok ugyanazon a lapon találhatók.Ez általában ritka eset, és olyasmi, amit általában nem ajánlok.
A Ted javaslatának köszönhetően.
Időt takaríthat meg & Zavarban
Remélem, hogy ez a cikk segít időt spórolni és megkönnyíti a fájljainak felhasználói számára. Segíthet a zavarban tartás megelőzésében is, amikor a jelentések küldése előtt elfelejt frissíteni a kimutató táblákat. én, már többször elkövettem ezt a hibát, mint amennyit el szeretnék ismerni … 🙂
