În acest articol, vom afla cum să reîmprospătați automat datele din tabelul pivot în Excel.
Scenariu:
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 ori de câte ori deschidem un registru de lucru Excel. Și dacă trimiteți un fișier actualizat fără a actualiza tabelele pivot, este posibil să vă simțiți jenat. Deci, aflați aici cum să găsiți opțiunea de reîmprospătare atunci când utilizați tabelul pivot
Actualizați datele la deschiderea unui fișier în Excel
Mai întâi Creați un tabel pivot și apoi faceți clic dreapta pe orice celulă a tabelului pivot.
Accesați Opțiunile tabelului pivot> fila Date> Bifați caseta care scrie Actualizați datele la deschiderea unui fișier
Aceasta va activa datele de actualizare automată ori de câte ori fișierul este deschis.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Să înțelegem cum să folosim funcția folosind un exemplu. Aici avem câteva date și trebuie să creăm mai întâi un tabel pivot și apoi să găsim opțiunile pentru a activa actualizarea automată a tabelelor pivot.
Creați un tabel pivot și apoi faceți clic dreapta pe orice celulă din tabelul pivot așa cum se arată mai jos.
Selectați Opțiunile tabelului pivot și aceasta va deschide o casetă de dialog Opțiuni tabel PIvot.
Selectați fila de date și apoi bifați caseta care spune Reîmprospătare date la deschiderea fișierului. Puteți efectua acest lucru fără a deschide și închide fișierul folosind VBA.
Folosind VBA
Deci, aici, 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 Sub Worksheet_Deactivate () tablename_of_pivot_table.Tabele pivot("pivot_table_name") .PivotCache.Refresh Sfârșitul 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 Sfârșitul 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 ThisWorkbook.PivotCaches pc.Refresh Următorul computer Sfârșitul Sub |
Î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.
| Foaie de lucru sub privat_Change (țintă ByVal ca interval)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Sfârșitul 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.
| Foaie de lucru sub privat_Change (țintă ByVal ca interval)
ThisWorkbook.RefreshAll Sfârșitul Sub |
Notă : Codul 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.
Sper că acest articol despre Cum reîmprospătați automat datele din tabelul pivot în Excel este explicativ. Găsiți mai multe articole despre calcularea valorilor și formulele Excel conexe aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
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 : Obțineți mai rapid sarcinile dvs. în Excel. Aceste comenzi rapide vă vor ajuta să vă sporiți eficiența de lucru în Excel.
Cum se utilizează 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.
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
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.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.