Az összes kimutató tábla adatforrásának dinamikus frissítése az Excelben

Egy előző cikkünkben megtudtuk, hogyan lehet dinamikusan megváltoztatni és frissíteni az egyes kimutató táblázatokat zsugorodó vagy bővülő adatforrásokkal.

Ebben a cikkben megtudhatja, hogyan tudjuk elérni, hogy egy munkafüzet összes pivot-táblája automatikusan megváltoztassa az adatforrást. Más szavakkal, ahelyett, hogy egyszerre egy pivot táblát cserélnénk, megpróbáljuk megváltoztatni a munkafüzet összes pivot táblájának adatforrását, hogy dinamikusan belefoglaljuk az új sorokat és oszlopokat a forrás táblákba, és azonnal tükrözzük a pivot táblák változását. / p>

Kód beírása a forrás adatlapba

Mivel azt akarjuk, hogy ez teljesen automatikus legyen, a core modul helyett a lapmodulokat használjuk a kód írásához. Ez lehetővé teszi számunkra a munkalap-események használatát.

Ha a forrásadatok és a pivot-táblák különböző lapokban vannak, akkor megírjuk a VBA-kódot a pivot-tábla adatforrásának megváltoztatásához a forrásadatokat tartalmazó lapobjektumban ( nem tartalmaz pivot táblákat).

A VB szerkesztő megnyitásához nyomja meg a CTRL + F11 billentyűkombinációt. Most keresse fel a projektfelfedezőt, és keresse meg a forrásadatokat tartalmazó lapot. Kattintson duplán rá.

Új kódolási terület nyílik meg. Lehet, hogy nem lát semmilyen változást, de most már hozzáfér a munkalap eseményeihez.

Kattintson a bal oldali legördülő menüre, és válassza ki a munkalapot. A bal oldali legördülő menüből válassza a deaktiválás lehetőséget. Látni fogja, hogy egy üres rész van írva a kód terület neve munkalap_deativate. A kód a dinamikusan változó forrásadatokhoz és a pivot-tábla frissítéséhez ebben a kódblokkban lesz. Ez a kód akkor fog futni, ha az adatlapról bármelyik másik lapra vált. Az összes munkalapeseményről itt olvashat.

Most már készen állunk a kód implementálására.

Forráskód a munkafüzet összes pivot-táblájának dinamikus frissítéséhez új hatótávolsággal

A működés magyarázatához van egy munkafüzetem. Ez a munkafüzet három lapot tartalmaz. A Sheet1 tartalmazza azokat a forrásadatokat, amelyek megváltozhatnak. A Sheet2 és a Sheet3 olyan pivot táblákat tartalmaz, amelyek a 2. lap forrásadataitól függenek.

Most ezt a kódot írtam az 1. lap kódolási területére. A Worksheet_Deactivate eseményt használom, hogy ez a kód a pivot tábla, amikor a forrás adatlapról váltunk.

Ha hasonló munkafüzete van, közvetlenül lemásolhatja ezeket az adatokat. Elmagyaráztam, hogy ez a kód az alábbiakban működik, hogy az igényeinek megfelelően módosítsa.

Az alábbi gif-ben láthatja ennek a kódnak a hatását.

Hogyan változik automatikusan ez a kód forrásadatok és a pivot táblák frissítése?

Először egy munkalap_deaktiválás eseményt használtunk. Ez az esemény csak akkor vált ki, ha a kódot tartalmazó lapot átkapcsolják vagy deaktiválják. Így a kód automatikusan fut.

Az egész tábla dinamikus lekéréséhez adattartományként meghatározzuk az utolsó sort és az utolsó oszlopot.

lstrow = Cellák (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Oszlop

E két szám felhasználásával meghatározzuk a forrás_adatokat. Biztosak vagyunk abban, hogy a forrásadatok tartománya mindig A1-től indul. Megadhatja saját kezdő cellahivatkozását.

Állítsa be a source_data = Tartományt (Cellák (1, 1), Cellák (lstrow, lstcol))

Most megkapjuk a forrásadatokat, amelyek: dinamikus. Csak a pivot táblában kell használnunk.

Mivel nem tudjuk, hogy egy munkafüzet egyidejűleg hány pivot táblázatot tartalmaz, végigvezetjük az egyes munkalapokat és az egyes munkalapok tábláit. hogy nem maradt pivot tábla. Ehhez beágyazott ciklusokat használunk.

Minden egyes w-hez ebben a munkafüzetben. Munkalapok

Minden pt-hez a ws.PivotTables-ben

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Következő pt

Következő ws

Az első hurok végigvonul az egyes munkalapokon. A második ciklus a munkalap minden egyes pivot-tábláján át ismétlődik.

A pivot-táblák hozzárendelve vannak változó pt-hez. A pt objektum ChangePivotCache metódusát használjuk. Dinamikusan létrehozunk egy pivot gyorsítótárat a ThisWorkbook.PivotCaches.Create

módszerrel. Ez a módszer két változót vesz igénybe: SourceType és SourceData. Forrás típusként deklaráljuk az xlDatabase és SourceData-ként átadjuk a korábban kiszámított source_data tartományt.

És hogy i s ez. Automatizáljuk a forgóasztalokat. Ez automatikusan frissíti a munkafüzet összes forgatótábláját.

Tehát igen, srácok, így dinamikusan megváltoztathatja az Excel egyik munkafüzet összes kimutató táblázatának adatforrás-tartományait. Remélem, elég magyarázó voltam. Ha bármilyen kérdése van a cikkel kapcsolatban, tudassa velem az alábbi megjegyzések részben.

Hogyan lehet dinamikusan frissíteni a kimutatási tábla adatforrás-tartományát az Excelben: A kimutató táblák forrásadat-tartományának dinamikus megváltoztatásához pivot-gyorsítótárakat használunk. Ez a néhány sor dinamikusan frissítheti az összes pivot-táblázatot a forrásadatok tartományának megváltoztatásával.

A pivot-táblák automatikus frissítése a VBA használatával: A pivot-táblák automatikus frissítéséhez használhatja a VBA-eseményeket. Ezzel az egyszerű kódsorral frissítheti a kimutatótáblát automatikusan. A forgatótáblák automatikus frissítésének három módszerének egyikét használhatja.

Makró futtatása, ha a lapon bármilyen változás történt a megadott tartományban: A VBA gyakorlatában felmerül a makrók futtatása, ha egy bizonyos tartomány vagy sejtváltozások. Ebben az esetben a makrók futtatásához, amikor a céltartományon változtatunk, a változás eseményt használjuk.

Makró futtatása, ha bármilyen változás történik a lapon | Tehát a makró futtatásához, amikor a munkalap frissül, a VBA munkalap eseményeit használjuk.

A legegyszerűbb VBA kód az aktuális sor és oszlop kiemelésére a | Használja ezt a kis VBA-részletet a lap aktuális sorának és oszlopának kiemeléséhez.

A munkalap eseményei az Excel VBA-ban | A munkalap esemény nagyon hasznos, ha azt akarja, hogy a makrói akkor fussanak, amikor egy meghatározott esemény történik a lapon.

Népszerű cikkek:

50 Excel parancsikon a termelékenység növelése érdekében | Gyorsabban végezze el feladatát. Ez az 50 parancsikon még gyorsabbá teszi az Excel munkáját. Az Excel VLOOKUP funkciója Ez az Excel egyik leggyakrabban használt és legnépszerűbb funkciója, amelyet a különböző tartományok és lapok értékének keresésére használnak.

COUNTIF az Excel 2016-ban | Számoljon értékeket feltételekkel ezzel a csodálatos funkcióval. Nem kell szűrnie az adatait az adott érték számításához. A Countif funkció elengedhetetlen az irányítópult előkészítéséhez.

A SUMIF függvény használata az Excel-ben | Ez egy másik irányítópult alapvető funkció. Ez segít összegezni az értékeket meghatározott feltételek mellett.