Excel VBA Reîmprospătare tabel pivot
Când inserăm un tabel pivot în foaie, odată ce datele se modifică, datele din tabelul pivot nu se schimbă singure, trebuie să o facem manual, dar în VBA există o declarație pentru reîmprospătarea tabelului pivot care este expression.refreshtable , folosind aceasta putem actualiza tabelul pivot făcând referire la foaia de lucru care îl compune sau putem face referire la întregul tabel pivot din foile de lucru și le putem actualiza simultan.
Tabelul pivot este vital în analiza cantității uriașe de date. Ajută chiar din analiza, rezumarea și interpretarea utilă a datelor. Cu toate acestea, una dintre problemele cu acest tabel pivot este că nu va fi reîmprospătată automat dacă există vreo modificare a datelor sursă. Utilizatorul trebuie să reîmprospăteze tabelul pivot accesând tabelul pivot special de fiecare dată când există o modificare. Dar spuneți adio procesului manual, deoarece aici avem metoda de a reîmprospăta tabelul pivot imediat ce faceți orice modificare în tabelul pivot.
Cum se actualizează automat codul VBA al datelor din tabelul pivot?
Singura dată când trebuie actualizat tabelul pivot este ori de câte ori există vreo modificare în datele sursă ale tabelului pivot la care ne referim.
De exemplu, priviți datele de mai jos și tabelul pivot.
Acum voi schimba numerele din datele sursă, adică de la A1 la B17.
În celula B9, trebuie să schimb valoarea de la 499 la 1499, adică o creștere de 1000 de date, dar dacă te uiți la pivot, totuși rezultatul este 4295 în loc de 5295. Trebuie să îmi reîmprospătez manual tabelul pivot pentru a actualiza masă rotativă.
Pentru a depăși această problemă, trebuie să scriem un cod macro simplu Excel pentru a reîmprospăta tabelul pivot ori de câte ori există orice modificare a datelor sursă.
# 1 - Macro simplu pentru reîmprospătarea tuturor tabelului
Pasul 1: Schimbați evenimentul din foaia de date
Trebuie să declanșăm evenimentul de modificare al fișei tehnice. În editorul de bază vizual, faceți dublu clic pe foaia de date.
După ce faceți dublu clic pe foaie, selectați „Foaie de lucru” și selectați evenimentul ca „Modificare”.
Veți vedea o sub-procedură automată deschisă ca Worksheet_Change (ByVal Target As Range)
Pasul 2: Utilizați obiectul Foaie de lucru
Consultați foaia tehnică utilizând obiectul Worksheets.
Pasul 3: consultați tabelul pivot după nume
Consultați numele tabelului pivot după numele tabelului pivot.
Pasul 4: utilizați metoda Reîmprospătare tabel
Selectați metoda ca „Actualizați tabelul”.
Acum, acest cod va reîmprospăta tabelul pivot „PivotTable1” ori de câte ori există modificări în foaia de date sursă. Puteți utiliza codul de mai jos. Trebuie doar să schimbați numele tabelului pivot.
Cod:
Foaie de lucru Private Sub Worksheet_Change (ByVal Target As Range) Foaie de lucru ("Foaie de date"). PivotTables ("PivotTable1"). RefreshTable End Sub
# 2 - Actualizați toate tabelele pivot ale aceleiași foi de lucru
Dacă aveți multe tabele pivot în aceeași foaie de lucru, puteți reîmprospăta toate tabelele pivot cu un singur clic. Utilizați codul de mai jos pentru a reîmprospăta toate tabelele pivot din foaie.
Cod:
Sub Refresh_Pivot_Tables_Example1 () Foi de lucru ("Foaie de date"). Selectați cu ActiveSheet. PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4". RefreshTable .PivotTables ("Tabelul 5"). RefreshTable End cu End Sub
Trebuie să schimbați numele foii de lucru și numele tabelului pivot conform detaliilor foii de lucru.
# 3 - Actualizați toate tabelele din registrul de lucru
Este foarte puțin probabil să avem toate tabelele pivot pe aceeași foaie de lucru. De obicei, pentru fiecare raport, încercăm să adăugăm tabele pivot separate în foi separate. În aceste cazuri, nu putem continua să scriem codul pentru fiecare tabel pivot care trebuie actualizat.
Deci, ceea ce putem face este cu un singur cod folosind bucle, putem parcurge toate tabelele pivot din registrul de lucru și le putem reîmprospăta printr-un singur clic al butonului.
Codul de mai jos va parcurge fiecare tabel pivot și le va reîmprospăta.
Codul 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT ca PivotTable pentru fiecare PT din ActiveWorkbook.PivotTables PT.RefreshTable Următorul PT End Sub
Codul 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC ca PivotCache pentru fiecare PC din ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Ambele coduri vor face reîmprospătarea tabelelor pivot.
Dacă doriți ca tabelul pivot să fie reîmprospătat de îndată ce există vreo modificare în foaia de date a foii de pivotare, trebuie să copiați și să lipiți codurile de mai sus în evenimentul Change Sheet Change din acel registru de lucru.
# 4 - Evitați timpul de încărcare utilizând Foaia de lucru Dezactivare eveniment
Când folosim evenimentul „Schimbare foaie de lucru”, acesta rămâne reîmprospătat chiar și atunci când nu există nicio modificare în sursa de date, dar dacă se întâmplă orice modificare în foaia de lucru.
Chiar dacă introduceți un singur punct în foaia de lucru, acesta încearcă să reîmprospăteze tabelul pivot. Deci, pentru a evita acest lucru, putem folosi metoda „Foaie de lucru Dezactivare” în loc de metoda „Schimbare foaie de lucru”.
Dezactivați actualizările evenimentului de pe tabelul pivot atunci când vă deplasați de la o foaie la alta.
