photosdematures.com

Taula dinàmica d'actualització d'Excel VBA

Quan inserim un taula dinàmica al full, un cop que les dades canvien les dades de la taula dinàmica no canvien per si soles, hem de fer-ho manualment, però a VBA hi ha una sentència per actualitzar la taula dinàmica que és expression.refreshtable, amb això, podem actualitzar la taula dinàmica fent referència al full de treball que la compon o podem referir-nos a les taules dinàmiques senceres dels fulls de treball i actualitzar-les totes alhora.

La taula dinàmica és vital per analitzar la gran quantitat de dades. Ajuda des de l’anàlisi, el resum i la interpretació de dades útils. Tanmateix, un dels problemes d’aquesta taula dinàmica és que no s’actualitzarà automàticament si hi ha algun canvi en les dades d’origen, l’usuari ha d’actualitzar la taula dinàmica anant a la taula dinàmica particular cada vegada que hi hagi un canvi. Però acomiadeu-vos del procés manual perquè aquí tenim el mètode per actualitzar la taula dinàmica tan aviat com feu un canvi a la taula dinàmica.

Com s'actualitza automàticament el codi VBA de les dades de la taula dinàmica?

L'única vegada que cal actualitzar la taula dinàmica és quan hi ha algun canvi en les dades d'origen de la taula dinàmica a què ens referim.

Per exemple, mireu les dades i la taula dinàmica següents.

Ara canviaré els números de les dades d'origen, és a dir, d'A1 a B17.

A la cel·la B9 he de canviar el valor de 499 a 1499, és a dir, augment de 1000 dades, però si mireu el pivot encara es mostra el resultat com 4295 en lloc de 5295. He d’actualitzar manualment la taula dinàmica per actualitzar la taula dinàmica.

Per superar aquest problema, hem d’escriure un codi de macro excel simple per actualitzar la taula dinàmica sempre que hi hagi algun canvi a les dades d’origen.

Podeu descarregar aquesta plantilla Excel de la taula dinàmica VBA Refresh aquí: plantilla Excel de la taula dinàmica VBA Refresh

# 1: macro simple per actualitzar tota la taula

Pas 1: Canvieu l'esdeveniment del full de dades

Hem d’activar l’esdeveniment de canvi del full de dades. A l'editor visual bàsic feu doble clic al full de dades.

Un cop feu doble clic sobre el full seleccioneu "Full de treball" i seleccioneu l'esdeveniment com a "Canviar".

Veureu un subprocediment automàtic obert com Worksheet_Change (objectiu ByVal com a interval)

Pas 2: utilitzeu l'objecte del full de treball

Consulteu el full de dades mitjançant l’objecte Fulls de treball.

Pas 3: consulteu la taula dinàmica per nom

Consulteu el nom de la taula dinàmica pel nom de la taula dinàmica.

Pas 4: utilitzeu el mètode d'actualització de la taula

Seleccioneu el mètode com a "Actualitza la taula".

Ara, aquest codi actualitzarà la taula dinàmica "Taula dinàmica1" sempre que hi hagi algun canvi al full de dades d'origen. Podeu utilitzar el codi següent, només heu de canviar el nom de la taula dinàmica.

Codi:

 Full de treball Private Sub Worksheet_Change (ByVal Target As Range) Full de treball ("Full de dades"). Taules dinàmiques ("Taula dinàmica1"). Actualització de la taula final 

# 2 - Actualitza totes les taules dinàmiques del mateix full de treball

Si teniu moltes taules dinàmiques al mateix full de càlcul, podeu actualitzar totes les taules dinàmiques amb un sol clic. Utilitzeu el codi següent per actualitzar totes les taules dinàmiques del full.

Codi:

 Sub Refresh_Pivot_Tables_Example1 () Fulls de treball ("Full de dades"). Seleccioneu amb ActiveSheet. Taules dinàmiques ("Taula1"). RefreshTable. Taules dinàmiques ("Taula2"). RefreshTable .PivotTables ("Taula3"). RefreshTable .PivotTables ("Taula4"). RefreshTable. Taules dinàmiques ("Taula5"). RefreshTable finalitza amb End Sub 

Heu de canviar el nom del full de càlcul i els noms de la taula dinàmica segons els detalls del full de càlcul.

# 3 - Actualitza totes les taules del llibre

És molt poc probable que tinguem totes les taules dinàmiques al mateix full de càlcul. Normalment, per a cada informe, intentem afegir taules dinàmiques separades en fulls separats. En aquests casos, no podem continuar escrivint el codi de cada taula dinàmica que es vol actualitzar.

Per tant, el que podem fer és amb un únic codi mitjançant bucles que podem recórrer totes les taules dinàmiques del llibre i actualitzar-les amb un sol clic del botó.

El codi següent farà un bucle a través de cada taula dinàmica i els actualitzarà.

Codi 1:

 Sub Refresh_Pivot_Tables_Example2 () Atenua PT com a taula dinàmica per a cada PT a ActiveWorkbook.PivotTables PT.RefreshTable Següent PT End Sub 

Codi 2:

 Sub Refresh_Pivot_Tables_Example3 () Atenua el PC com a PivotCache per a cada PC d'ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub 

Tots dos codis permetran refrescar les taules dinàmiques.

Si voleu que la taula dinàmica s’actualitzi tan bon punt hi hagi algun canvi al full de dades del full dinàmic, heu de copiar i enganxar els codis anteriors a l’esdeveniment Change Sheetheet del llibre de treball.

# 4 - Eviteu el temps de càrrega mitjançant l'esdeveniment de desactivació del full de treball

Quan fem servir l'esdeveniment "Canvi del full de treball", es continua actualitzant fins i tot quan no hi ha cap canvi a la font de dades, però si es produeix algun canvi al full de treball.

Fins i tot si introduïu un punt únic al full de treball, intenta actualitzar la taula dinàmica. Per evitar-ho, podem utilitzar el mètode "Desactivar el full de treball" en lloc del mètode "Canviar el full de treball".

Desactiveu les actualitzacions d'esdeveniments a la taula dinàmica en passar d'un full a un altre.