Într-un articol anterior, am aflat cum puteți schimba și actualiza dinamic tabelele pivot individuale prin reducerea sau extinderea surselor de date.
În acest articol, vom afla cum putem face ca toate tabelele pivot dintr-un registru de lucru să schimbe automat sursa de date. Cu alte cuvinte, în loc să schimbăm câte un tabel pivot la un moment dat, vom încerca să schimbăm sursa de date a tuturor tabelelor pivot din registrul de lucru pentru a include dinamic rânduri și coloane noi adăugate tabelelor sursă și să reflectăm instantaneu modificarea tabelelor 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 tabele 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 actualizarea dinamică a tuturor tabelelor pivot din registrul de lucru cu o gamă nouă
Pentru a explica cum funcționează, am un registru de lucru. Acest registru de lucru conține trei foi. Foaia 1 conține datele sursă care se pot modifica. Sheet2 și Sheet3 conțin tabele pivot care depind de datele sursă ale foii2.
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 source_data As Range 'Determinarea ultimului rând și număr coloană lstrow = Cells (Rows.Count, 1). End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Setarea noului interval Setați date sursă = Interval (celule (1, 1), celule (lstrow, lstcol))' Cod pentru a parcurge fiecare foaie și tabel pivot pentru fiecare ws din acest manual de lucru. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Punctul următor Următorul ws 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, astfel încât să îl puteți modifica conform nevoilor dvs.
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.
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. Puteți defini propria referință de celulă de început.
Setează sursă_date = Interval (celule (1, 1), celule (lstrow, lstcol))
Acum avem datele sursă dinamice. Trebuie doar să-l folosim în tabelul pivot.
Deoarece nu știm câte tabele pivot va conține un registru de lucru la un moment dat, vom parcurge fiecare foaie și tabele pivot ale fiecărei foi. Astfel încât să nu rămână niciun tabel pivot. Pentru aceasta folosim imbricate pentru bucle.
Pentru fiecare ws din ThisWorkbook.Worksheets
Pentru fiecare pct În tabelele pivot
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Următorul pct
Următorul ws
Prima buclă parcurge fiecare foaie. A doua buclă iterează peste fiecare tabel pivot dintr-o foaie.
Tabelele pivot sunt atribuite variabilei pt. Folosim metoda ChangePivotCache a obiectului pt. Creăm dinamic un cache pivot folosind ThisWorkbook.PivotCaches.Create
Metodă. Această metodă ia două variabile SourceType și SourceData. Ca tip sursă declarăm xlDatabase și ca SourceData trecem intervalul source_data pe care l-am calculat mai devreme.
Și asta este. Avem tabelele noastre pivot automatizate. Aceasta va actualiza automat toate tabelele pivot din registrul de lucru.
Deci, băieți, așa puteți schimba dinamic intervalele de surse de date ale tuturor tabelelor pivot dintr-un registru de lucru î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ă dinamic intervalul de surse de date din tabelul 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ă.
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.