Excel VBA pivot-tábla frissítése
Amikor beillesztünk egy pivot táblázatot a lapba, ha az adatok megváltoznak, a pivot tábla adatai nem változnak önmagában, akkor ezt manuálisan kell elvégeznünk, de a VBA-ban van egy utasítás a pivot tábla frissítésére, amely kifejezés.refreshtable , ennek használatával frissíthetjük a pivot tábla az abból álló munkalapra hivatkozva, vagy hivatkozhatunk a munkalapok teljes pivot tábláira, és egyszerre frissíthetjük őket.
A forgatótábla létfontosságú a hatalmas adatmennyiség elemzéséhez. Segít az elemzésben, az összesítésben, valamint a hasznos adatok értelmezésében. Ennek a pivot táblának azonban az egyik problémája, hogy nem frissül automatikusan, ha bármilyen változás történik a forrásadatokban. A felhasználónak frissítenie kell a pivot táblázatot úgy, hogy minden alkalommal az adott pivot táblára megy, ha változás történik. De köszönj el a kézi eljárástól, mert itt megvan a módszer a pivot tábla frissítésére, amint bármilyen változtatást végrehajtasz a pivot táblában.
Hogyan frissítsük a kimutató táblázat adatainak VBA kódját automatikusan?
A kimutatási táblát csak akkor kell frissíteni, ha bármilyen változás történik a hivatkozott pivot-tábla forrásadatában.
Nézze meg például az alábbi adatokat és pivot táblázatot.
Most megváltoztatom a forrásadatok számát, azaz A1-ről B17-re.
A B9 cellában meg kell változtatnom az értéket 499-ről 1499-re, azaz 1000 növekedést az adatokban, de ha megnézzük a pivotot, akkor is az eredmény 4295 helyett 5295 lesz. Manuálisan frissítenem kell a pivot táblát a Pivot tábla.
A probléma kiküszöbölése érdekében egy egyszerű excel makrókódot kell írnunk a pivot tábla frissítéséhez, ha a forrásadatokban bármilyen változás történik.
# 1 - Egyszerű makró az összes táblázat frissítéséhez
1. lépés: Módosítsa az adatlap eseményét
Ki kell váltanunk az adatlap változás eseményét. A visual basic szerkesztőben kattintson duplán az adatlapra.
Miután duplán kattintott a munkalapra, válassza a „Munkalap” lehetőséget, és válassza az eseményt „Módosítás” lehetőségre.
Megjelenik egy automatikus alfolyamat, amely a Worksheet_Change (ByVal Target As Range) néven nyílik meg.
2. lépés: Használja a Munkalap objektumot
A Munkalapok objektum használatával tekintse meg az adatlapot.
3. lépés: Tekintse meg a kimutatási táblázatot név szerint
Lásd a pivot tábla nevét a pivot tábla nevével.
4. lépés: Használja a Táblázat frissítése módszert
Válassza ki a módszert „Táblázat frissítése” lehetőségként.
Ez a kód frissíti a „PivotTable1” kimutatási táblázatot, ha bármilyen változás történik a forrás adatlapján. Használhatja az alábbi kódot. Csak meg kell változtatnia a kimutató tábla nevét.
Kód:
Private Sub Worksheet_Change (ByVal Target as Range) munkalap ("Adatlap"). PivotTables ("PivotTable1"). RefreshTable End Sub
# 2 - Frissítse ugyanazon munkalap összes forgatótábláját
Ha sok pivot tábla van ugyanabban a munkalapon, akkor az összes pivot táblát egyetlen kattintással frissítheti. Az alábbi kód használatával frissítse a lap összes pivot-táblázatát.
Kód:
Sub Refresh_Pivot_Tables_Example1 () munkalapok ("Adatlap"). Válassza ki az ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub
Meg kell változtatnia a munkalap és a pivot tábla nevét a munkalap részleteinek megfelelően.
# 3 - Frissítse az összes táblázatot a munkafüzetben
Nagyon valószínűtlen, hogy az összes pivot tábla ugyanazon a munkalapon legyen. Általában minden jelentéshez külön pivot táblákat próbálunk külön lapokba felvenni. Ezekben az esetekben nem tudjuk tovább írni az egyes frissítendő pivot táblák kódját.
Tehát azt tehetjük, hogy egyetlen kóddal hurkokat használunk, áttekinthetjük a munkafüzet összes pivot táblázatát, és egyetlen gombnyomással frissíthetjük őket.
Az alábbi kód végighúzza az egyes pivot táblákat, és frissíti azokat.
1. kód:
Sub Refresh_Pivot_Tables_Example2 () Dim PT mint pivotTable minden PT-hez az ActiveWorkbookban. PivotTables PT.RefreshTable Next PT End Sub
2. kód:
Sub Refresh_Pivot_Tables_Example3 () Tompítsa a PC-t PivotCache-ként minden számítógéphez az ActiveWorkbookban. PivotCache PC. Frissítse a következő PT végét
Mindkét kód elvégzi a pivot táblák frissítését.
Ha azt szeretné, hogy a kimutatási tábla frissüljön, amint változás történik az elforduló lap adatlapján, át kell másolnia és be kell illesztenie a fenti kódokat az adott munkafüzet Munkalap módosítása eseményébe.
# 4 - Kerülje a betöltési időt a munkalap deaktiválásával
Amikor a „Munkalap változtatása” eseményt használjuk, akkor is frissül, ha az adatforrásban nincs változás, de ha a munkalapon bármilyen változás történik.
Még ha egyetlen pontot is beír a munkalapba, az megpróbálja frissíteni a kimutató táblázatot. Tehát ennek elkerülése érdekében használhatjuk a „Munkalap megváltoztatása” módszer helyett a „Munkalap letiltása” módszert.
Deaktiválja az eseményfrissítéseket a forgatótáblán, amikor egyik munkalapról a másikra mozog.
