După cum știm cu toții, ori de câte ori facem modificări într-o sursă de date a unui tabel pivot, acesta nu se reflectă imediat în tabelul pivot. Trebuie să reîmprospătăm tabelele pivot pentru a vedea modificările. Și dacă trimiteți un fișier actualizat fără a actualiza tabelele pivot, este posibil să vă simțiți jenat.
Deci, în acest articol, vom învăța cum să reîmprospătăm automat un tabel pivot folosind VBA. În acest fel este mai ușor decât ți-ai imaginat.
Aceasta este simpla sintaxă pentru reîmprospătarea automată a tabelelor pivot din registrul de lucru.
'Cod în obiectul fișei de date sursă Private Private Worksheet_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name"). PivotCache.Refresh End Sub
Ce sunt cache-urile pivot?
Fiecare tabel pivot stochează datele în memoria cache pivot. Acesta este motivul pentru care pivotul poate afișa datele anterioare. Când actualizăm tabelele pivot, acesta actualizează memoria cache cu date sursă noi pentru a reflecta modificările din tabelul pivot.
Deci, avem nevoie doar de o macro pentru a reîmprospăta memoria cache a tabelelor pivot. Vom face acest lucru folosind un eveniment de foaie de lucru, astfel încât să nu trebuiască să rulăm macro manual.
Unde se codifică actualizarea automată a tabelelor pivot?
Dacă datele sursă și tabelele pivot sunt în foi diferite, atunci codul VBA ar trebui să intre în foaia de date sursă.
Aici vom folosi Evenimentul Worksheet_SelectionChange. Acest lucru va face codul să ruleze ori de câte ori trecem de la foaia de date sursă la o altă foaie. Voi explica mai târziu de ce am folosit acest eveniment.
Aici, am date sursă în sheet2 și tabele pivot în sheet1.
Deschideți VBE folosind tasta CTRL + F11. În Project Explorer, puteți vedea trei obiecte, Sheet1, Sheet2 și Workbook.
Deoarece Sheet2 conține datele sursă, faceți dublu clic pe obiect sheet2.
Acum puteți vedea două drop-down-uri în partea de sus a zonei de cod. Din prima listă derulantă, selectați foaia de lucru. Și din al doilea meniu derulant selectați Dezactivare. Aceasta va insera un subnume gol Worksheet_Deactivate. Codul nostru va fi scris în acest sub. Orice rând scris în acest sub, este executat imediat ce utilizatorul trece de la această foaie la orice altă foaie.
Pe foaia 1 am două tabele pivot. Vreau să reîmprospătăm un singur tabel pivot. Pentru asta, trebuie să știu numele tabelului pivot. Pentru a cunoaște numele oricărui tabel pivot, selectați orice celulă din acel tabel pivot accesați fila analiză tabel pivot. În partea stângă, veți vedea numele tabelului pivot. De asemenea, puteți schimba numele tabelului pivot aici.
Acum știm numele tabelului pivot, putem scrie o linie simplă pentru a reîmprospăta tabelul pivot.
Private Sub Worksheet_Deactivate () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Și s-a făcut.
Acum, ori de câte ori veți trece de la datele sursă, acest cod vba va rula pentru a reîmprospăta tabelul pivot1. După cum puteți vedea în gif de mai jos.
Cum să reîmprospătați toate tabelele pivot din registrul de lucru?
În exemplul de mai sus, am vrut doar să reîmprospătăm un tabel pivot specific. Dar dacă doriți să reîmprospătați toate tabelele pivot dintr-un registru de lucru, trebuie doar să faceți ușoare modificări ale codului.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh pentru fiecare computer din acest manual de lucru.
În acest cod, folosim o buclă For pentru a parcurge fiecare cache pivot din registrul de lucru. Obiectul ThisWorkbook conține toate cache-urile pivot. Pentru a le accesa folosim ThisWorkbook.PivotCaches.
De ce să folosești Foaia de lucru_Deactivează evenimentul?
Dacă doriți să reîmprospătați tabelul pivot imediat ce se fac modificări în datele sursă, ar trebui să utilizați evenimentul Worksheet_Change. Dar nu o recomand. Acesta va face ca registrul dvs. de lucru să ruleze codul de fiecare dată când faceți orice modificare în foaie. Este posibil să trebuiască să faceți sute de modificări înainte de a dori să vedeți rezultatul. Dar Excel va reîmprospăta tabelul pivot la fiecare modificare. Acest lucru va duce la pierderea timpului și a resurselor de procesare. Deci, dacă aveți tabele pivot și date în diferite foi, este mai bine să utilizați Foaia de lucru Dezactivare eveniment. Vă permite să vă finlandați munca. După ce treceți la foile din tabelul pivot pentru a vedea modificările, acesta modifică modificările.
Dacă aveți tabele pivot și date sursă pe aceeași foaie și doriți ca tabelele pivot să se reîmprospăteze automat, poate doriți să utilizați Evenimentul Foaie de lucru_Cambiere.
Private Sub Worksheet_Change (ByVal Target As Range) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Cum să reîmprospătați totul din registrele de lucru atunci când se efectuează o modificare a datelor sursă?
Dacă doriți să reîmprospătați totul dintr-un registru de lucru (diagrame, tabele pivot, formule etc.) puteți utiliza comanda ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Vă rugăm să rețineți că acest cod nu modifică sursa de date. Deci, dacă adăugați date sub datele sursă, acest cod nu va include automat aceste date. Puteți utiliza Tabelele Excel pentru a stoca datele sursă. Dacă nu doriți să utilizați tabele, putem folosi VBA pentru a include și noi date. O vom învăța în următorul tutorial.
Deci da prietene, așa puteți actualiza automat tabelele pivot în Excel. Sper că am fost suficient de explicativ și acest articol v-a servit bine. Dacă aveți întrebări cu privire la acest subiect, mă puteți întreba în secțiunea de comentarii de mai jos.
Cum se actualizează dinamic gama sursei de date a tabelului pivot în Excel: Pentru a schimba dinamic gama de date sursă a tabelelor pivot, folosim cache-uri pivot. Aceste câteva linii pot actualiza dinamic orice tabel pivot schimbând intervalul de date sursă. În VBA utilizați tabele pivot obiecte așa cum se arată mai jos …
Executați macro dacă există modificări efectuate pe foaie în intervalul specificat: În practicile dvs. VBA, veți avea nevoie să rulați macrocomenzi atunci când un anumit interval sau celulă se schimbă. În acest caz, pentru a rula macrocomenzi atunci când se face o modificare a unui interval țintă, folosim evenimentul de modificare.
Executați macrocomanda atunci când orice modificare se face pe foaie | Deci, pentru a rula macro-ul dvs. ori de câte ori se actualizează foaia, folosim Evenimentele Foaiei de lucru ale VBA.
Cel mai simplu cod VBA pentru a evidenția rândul curent și coloana folosind | Utilizați acest mic fragment VBA pentru a evidenția rândul și coloana curentă a foii.
Foaia de lucru Evenimente în Excel VBA | Evenimentul foii de lucru este foarte util atunci când doriți ca macro-urile dvs. să ruleze atunci când apare un eveniment specificat pe foaie.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Funcția VLOOKUP în Excel | Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
COUNTIF în Excel 2016 | Numărați valorile cu condiții folosind această funcție uimitoare. Nu este nevoie să vă filtrați datele pentru a număra o anumită valoare. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.