Cum se actualizează dinamic toate sursele de date ale tabelelor pivot în Excel

Într-un articol anterior, am aflat cum puteți schimba și actualiza dinamic tabele pivot individuale cu surse de date care se micșorează sau se extind.

În acest articol, vom aflați 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 foii 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 răcoritor 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. Puteți citi despre toate evenimentele din foaia de lucru aici.

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 sheet2.

Acum am scris acest cod în zona de codare a sheet1 „. Folosesc evenimentul Worksheet_Deactivate, astfel încât acest cod să ruleze pentru a actualiza tabel pivot ori de câte ori trecem de la foaia de date sursă.

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 se modifică automat acest cod date sursă și actualizare tabele pivot?

În primul rând am folosit un eveniment worksheet_deactivate. Acest eveniment se declanșează numai atunci când foaia care conține codul este comutată sau dezactivată. Deci, așa se execută automat codul.

Pentru a obține dinamic întregul tabel ca interval de date, determinăm ultimul rând și ultima coloană.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count). End (xlToL eft) .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.

Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Acum avem datele sursă care sunt dinamic. 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. că nu rămâne niciun tabel pivot. Pentru aceasta folosim imbricate pentru bucle.

Pentru fiecare ws din ThisWorkbook.Worksheets

Pentru fiecare pt din ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Next pt

Next ws

Prima buclă parcurge fiecare foaie. A doua buclă iterează peste fiecare tabel pivot dintr-o foaie.

Tabelele pivot sunt atribuite la variabilă pt. Utilizăm metoda ChangePivotCache a obiectului pt. Creăm dinamic o cache pivot utilizând metoda ThisWorkbook.PivotCaches.Create

Această metodă ia două variabile SourceType și SourceData. Ca tip de sursă declarăm xlDatabase și ca SourceData trecem intervalul source_data pe care l-am calculat mai devreme.

Și că i este. Avem tabelele noastre pivot automatizate. Aceasta va actualiza automat toate tabelele pivot din registrul de lucru.

Deci, da, băieți, așa puteți schimba dinamic intervalele surselor 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 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 în mod dinamic orice tabel pivot modificând intervalul de date sursă.

Cum să reîmprospătați automat tabelele pivot utilizând VBA: pentru a actualiza 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 macrocomanda dacă există modificări pe foaie în intervalul specificat: în practicile dvs. VBA, veți avea nevoie să rulați macrocomenzi atunci când un anumit interval sau schimbări celulare. În acest caz, pentru a rula macrocomenzi atunci când se face o modificare într-un interval țintă, folosim evenimentul de modificare.

Rulați macro când orice modificare se face pe foaie | Deci, pentru a rula macro-ul dvs. ori de câte ori foaia se actualizează, folosim Evenimentele foii 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.

Evenimentele foii de lucru î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 valorile în condiții specifice.