În prezent, putem modifica sau actualiza dinamic tabelele pivot folosind tabele Excel sau game dinamice numite. Dar aceste tehnici nu sunt infailibile. Deoarece va trebui să reîmprospătați manual tabelul pivot manual. Dacă aveți date mari care conțin mii de rânduri și coloane, tabelele Excel nu vă vor ajuta prea mult. În schimb, va îngreuna fișierul. Deci, singura modalitate care rămâne este VBA.
În acest articol, vom afla cum putem face ca tabelul nostru pivot să schimbe automat sursa de date. Cu alte cuvinte, vom automatiza procesul manual de modificare a sursei de date pentru a include în mod dinamic rânduri și coloane noi adăugate la tabelele sursă și să reflectăm instantaneu modificarea tabelului pivot.
Scrieți codul în foaia de date sursă
Din moment ce dorim ca acest lucru să fie complet automat, vom folosi module de foi pentru a scrie cod în loc de un modul de bază. Acest lucru ne va permite să folosim evenimente din foaia de lucru.
Dacă datele sursă și tabelele pivot sunt în foi diferite, vom scrie codul VBA pentru a schimba sursa de date a tabelului pivot în obiectul foaie care conține datele sursă (nu care conține tabelul pivot).
Apăsați CTRL + F11 pentru a deschide editorul VB. Acum mergeți la exploratorul de proiecte și găsiți foaia care conține datele sursă. Faceți dublu clic pe el.
Se va deschide o nouă zonă de codificare. Este posibil să nu vedeți nicio modificare, dar acum aveți acces la evenimentele din foaia de lucru.
Faceți clic pe meniul derulant din stânga și selectați foaia de lucru. Din meniul derulant din stânga, selectați dezactivare. Veți vedea un sub gol scris pe numele zonei de cod worksheet_deativate. Codul nostru pentru schimbarea dinamică a datelor sursă și a tabelului pivot reîmprospătat va intra în acest bloc de cod. Acest cod va rula ori de câte ori veți trece de la foaia de date la orice altă foaie. Aici puteți citi despre toate evenimentele din foaia de lucru.
Acum suntem gata să implementăm codul.
Cod sursă pentru a actualiza dinamic tabelul pivot cu gama nouă
Pentru a explica cum funcționează, am un registru de lucru. Acest registru de lucru conține două foi. Foaia 1 conține datele sursă care se pot modifica. Sheet2 conține tabelul pivot care depinde de datele sursă ale sheet2.
Acum am scris acest cod în zona de codare a foii 1. Folosesc evenimentul Worksheet_Deactivate, astfel încât acest cod să ruleze pentru a actualiza tabelul pivot ori de câte ori trecem de la foaia de date sursă.
Private Sub Worksheet_Deactivate () Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Set de coloane source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache pc End Sub
Dacă aveți un registru de lucru similar, puteți copia direct aceste date. Am explicat că acest cod funcționează mai jos.
Puteți vedea efectul acestui cod în gif de mai jos.
Cum modifică acest cod automat datele sursă și actualizează tabelele pivot?
În primul rând am folosit un eveniment worksheet_deactivate. Acest eveniment se declanșează numai când foaia care conține codul este comutată sau dezactivată. Deci, astfel funcționează automat codul.
Acum, pentru a modifica datele sursă ale tabelului pivot, schimbăm datele din memoria pivot.
Un tabel pivot este creat folosind memoria cache pivot. Memoria cache pivot conține datele sursă vechi până când tabelul pivot nu este actualizat manual sau intervalul de date sursă este modificat manual.
Am creat referințe ale tabelelor pivot denumite pt, cache pivot numit pc și o gamă numită sursă_date. Datele sursă vor conține toate datele.
Pentru a obține dinamic întregul tabel ca interval de date, determinăm ultimul rând și ultima coloană.
lstrow = Celule (Rows.Count, 1). End (xlUp). Row
lstcol = Cells (1, Columns.Count). End (xlToLeft) .Column
Folosind aceste două numere definim datele_sursă. Suntem siguri că intervalul de date sursă va începe întotdeauna de la A1.
Setează sursă_date = Interval (celule (1, 1), celule (lstrow, lstcol))
Acum avem datele sursă dinamice. Trebuie doar să-l folosim în tabelul pivot.
Stocăm aceste date în cache pivot așa cum știm că cache pivot stochează toate datele.
Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Apoi definim tabelul pivot pe care dorim să îl actualizăm. Deoarece dorim să actualizăm PivotTable1 (numele tabelului pivot. Puteți verifica numele tabelului pivot în fila de analiză în timp ce selectați tabelul pivot.) În foaia 1, setăm pt așa cum se arată mai jos.
Set pt = Sheet2.PivotTables ("PivotTable1")
Acum folosim pur și simplu această cache pivot pentru a actualiza tabelul pivot. Folosim metoda changePivotCache a obiectului pt.
pt.ChangePivotCache pc
Și avem tabelul pivot automat. Aceasta va actualiza automat tabelul pivot. Dacă aveți mai multe tabele cu aceeași sursă de date, folosiți același cache în fiecare obiect de tabel pivot.
Deci, băieți, așa puteți schimba dinamic gama sursei de date în Excel. Sper că am fost suficient de explicativ. Dacă aveți întrebări cu privire la acest articol, anunțați-mă în secțiunea de comentarii de mai jos.
Cum se actualizează automat tabelele pivot folosind VBA: Pentru a reîmprospăta automat tabelele pivot puteți utiliza evenimente VBA. Utilizați această linie simplă de cod pentru a vă actualiza automat tabelul pivot. Puteți utiliza oricare dintre cele 3 metode de reîmprospătare automată a tabelelor pivot.
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.