Linia de fund: Aflați cum să utilizați o macrocomandă simplă pentru a reîmprospăta automat tabelele pivot de fiecare dată când se fac modificări la datele sursă. De asemenea, împărtășesc o soluție non-macro pentru a actualiza tabelele pivot la deschiderea fișierului. Include tutorial video și descărcare fișier Excel.
Nivel de abilități: intermediar
Descarcă fișierul Excel
Dacă înveți cel mai bine făcându-l singur, poți descărcați fișierul pe care îl folosesc în videoclip pentru a-l urmări. Iată fișierul Excel care conține codul VBA.
Reîmprospătare tabel pivot automat.xlsm (41,5 KB)
Actualizați automat tabelele pivot
Tabelele pivot pot fi actualizate imediat și automat atunci când se modifică datele sursă?
Absolut. Este necesară utilizarea dintr-o macrocomandă foarte simplă pe care o să vă arăt cum să creați mai jos.
Dacă nu sunteți prea familiarizați cu macrocomenzile și VBA, vă sugerez să vă uitați la serialul meu video în trei părți gratuit pentru a începe să folosiți Macros & VBA.
De asemenea, dacă sunteți nou în tabelele pivot, Am o serie care să vă prezinte ce sunt și cum să le folosiți. Urmăriți primul videoclip din seria respectivă pe tabelele pivot & Tablouri de bord
Pentru a actualiza automat tabelele noastre pivot, vom scrie o macro cu o singură instrucțiune simplă. Această instrucțiune spune în esență: când modific o foaie de lucru, reîmprospătați toate tabelele pivot și conexiunile de date. Iată pașii pentru a crea macro-ul.
Deschideți Editorul Visual Basic.
Puteți face acest lucru făcând clic pe butonul Visual Basic din fila Dezvoltator a panglicii.
Comanda rapidă de la tastatură pentru deschiderea editorului Visual Basic este Alt + F11.
Dacă nu vedeți fila Dezvoltator, o puteți face vizibilă folosind instrucțiunile de aici. Trebuie să faceți acest lucru o singură dată, iar apoi fila Dezvoltator va fi întotdeauna vizibilă de fiecare dată când deschideți Excel în viitor.
Deschideți modulul de foaie care conține datele sursă.
În fereastra Project Explorer a editorului Visual Basic, găsiți registrul de lucru pe care doriți să îl modificați. Sub acel registru de lucru sunt listate foile din registrul de lucru. Selectați foaia care conține datele sursă. Apoi faceți dublu clic pe ea.
Dacă nu vedeți fereastra Project Explorer o puteți activa din Vizualizare meniu (comandă rapidă de la tastatură: Ctrl + R).
Adăugați un nou eveniment pentru modificările foii de lucru.
Dând dublu clic pe foaie se deschide modulul de cod pentru acel obiect. În cadrul modulului de cod, dorim să creăm o macro eveniment. Pentru aceasta, alegeți Foaia de lucru în caseta derulantă Object din stânga.
Aceasta va adăuga un eveniment Worksheet_SelectionChange la modulul, pe care nu-l dorim de fapt, așa că îl vom șterge într-o clipă. Înainte de a face acest lucru, să mergem la meniul derulant Procedură din dreapta și să alegem Modificare.
Acest lucru adaugă un nou eveniment din partea de sus numit Worksheet_Change. Acum vom evidenția și șterge codul inutil de sub acesta.
Macro-ul evenimentului Worksheet_Change va rula oricând se va face o modificare către celulele din foaia de lucru respectivă. Putem adăuga cod VBA la evenimentul Worksheet_Change pentru a efectua acțiuni atunci când utilizatorul editează celule.
Notă: Evenimentul SelectionChange care este adăugat în mod implicit va rula de fiecare dată când utilizatorul selectează o celulă din foaie. Întrucât dorim ca codul să ruleze numai când utilizatorul editează / schimbă celule, folosim evenimentul Change. Verificați articolul meu despre modulele de cod VBA & Cum să rulați macrocomenzile bazate pe evenimente de utilizator pentru a afla mai multe despre modulele de foaie și evenimente.
Adăugați codul VBA pentru a actualiza toate tabelele pivot.
Apoi, chiar sub linia Worksheet_Change, tastați această instrucțiune:
ThisWorkbook.RefreshAll
Metoda RefreshAll va reîmprospăta toate tabelele pivot, interogările și conexiunile de date din registrul de lucru. Această acțiune este aceeași ca și când dați clic manual pe butonul Reîmprospătare din fila Date.
Adăugați această linie de cod la evenimentul Worksheet_Change va reîmprospăta registrul de lucru ori de câte ori se face o modificare a foii de lucru în care este în.
Tabelul pivot & Date sursă pe aceeași foaie
Aleksandrs a adresat o întrebare excelentă cu privire la comentariile videoclipului YouTube. Dacă tabelul pivot și datele sursă se află pe aceeași foaie, va trebui să adăugați cod pentru a dezactiva evenimentele.
Reîmprospătarea pune evenimentul într-o buclă recursivă și poate sfârși prin a prăbuși Excel. Iată codul pentru a preveni acest lucru.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Verificarea pentru a vă asigura că macro-ul dvs. rulează
O modalitate de a verifica dacă macro-ul funcționează este să-l testezi. Modificați datele sursă și vedeți dacă acestea sunt reflectate în tabelul pivot.Dacă modificarea dvs. nu este ușor de observat, deoarece aveți prea multe date sau, din alte motive, există un alt mod de a vedea dacă macro-ul dvs. se declanșează.
În editorul VB, puteți face clic pe coloană gri, chiar în stânga macrocomenzii dvs. Foaie de lucru_Change. Acest lucru va face să apară un cerc roșu. De asemenea, evidențiază acea linie de cod în roșu.
Aceasta se numește stop sau punct de întrerupere.
Comanda rapidă de la tastatură pentru a comuta / dezactiva un punct de întrerupere este: F9
Acum, ori de câte ori are loc o acțiune care declanșează macro-ul, Excel va sări în Editorul VB și va întrerupe macro-ul, astfel încât să puteți verifica codul. În cazul nostru, acțiunea respectivă este orice modificare efectuată în foaia de lucru.
Apoi puteți apăsa F8 pentru a parcurge fiecare sau apăsați F5 pentru a rula până la sfârșit (sau următorul punct de întrerupere).
Dacă modificați foaia de lucru și Excel nu vă atrage în Editorul VB, știți că există o problemă cu macro-ul nu rulează. Dacă acesta este cazul, este posibil să nu fi salvat fișierul ca registru de lucru activat pentru macro (.xlsm) și / sau macrocomenzi activate. Poate fi necesar să salvați & închideți fișierul, apoi redeschideți-l și activați macrocomenzile.
Pentru a elimina punctul de întrerupere pe care l-ați plasat pe macro, trebuie doar să faceți clic pe cercul roșu pentru al face să dispară (comandă rapidă de la tastatură: F9).
Comanda rapidă de la tastatură pentru a șterge toate punctele de întrerupere este: Ctrl + Shift + F9
Actualizarea tabelelor pivot fără Macro
Un dezavantaj al utilizării acestei macrocomenzi pentru a reîmprospăta tabelele pivot este că orice istoric Anulare se pierde de fiecare dată când rulează macrocomanda. Cu alte cuvinte, când faceți clic pe butonul Anulare (sau apăsați Ctrl + Z), Excel nu-și amintește ultimul lucru pe care l-ați făcut, deci nu îl poate anula. În consecință, nu se va întâmpla nimic, iar ultima dvs. modificare nu va fi anulată.
Există o alternativă care vă permite să păstrați istoricul Anulați. Cu toate acestea, această alternativă reîmprospătează tabelul pivot doar atunci când registrul de lucru este deschis, nu de fiecare dată când se face o modificare. Iată cum puteți utiliza această opțiune.
Pornind de la orice celulă din tabelul pivot:
- Accesați fila Analize din panglică.
- Alegeți butonul Opțiuni.
- Accesați fila Date din noua fereastră care se deschide.
- Bifați caseta care spune „Actualizați datele la deschiderea fișierului”.
După ce faceți clic pe OK, este posibil să primiți următorul mesaj de avertizare dacă aveți mai multe tabele pivot create din același interval de date sursă. Doar faceți clic pe OK pentru a trece prin el.
Din nou, doar prin comparație, dacă utilizați această opțiune, păstrați istoricul Anulare, dar reîmprospătează tabelul pivot numai atunci când registrul de lucru este închis și redeschis. Dacă utilizați opțiunea macro, pierdeți Anularea istoricului, dar tabelul pivot actualizează automat testează ori de câte ori se face orice modificare în registrul de lucru.
Variații pentru reîmprospătarea tabelelor pivot
Macro-ul pe care l-am analizat nu numai că va reîmprospăta tabelele pivot, dar va reîmprospăta și orice întrebări. . Dacă doriți să reîmprospătați numai tabelele pivot, puteți înlocui comanda „ThisWorkbook.RefreshAll” cu acest cod în schimb:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Fiecare tabel pivot este conectat la un cache pivot subiacent, care este conectat la datele sursă. Codul parcurge toate cache-urile pivot din registrul de lucru și le reîmprospătează. Tabelele pivot din același interval sursă pot partaja cache-urile pivot, astfel încât această metodă este mai rapidă decât în buclă prin toate tabelele pivot. .
În mod similar, să spunem că doriți doar să reîmprospătați un anumit tabel pivot. În acest caz, puteți schimba codul „ThisWorkbook.RefreshAll” cu codul de mai jos.
Și, în sfârșit, dacă folosiți Power Query și doriți să dezactivați reîmprospătarea fundalului, astfel încât interogările să fie reîmprospătate ÎNAINTE de tabele pivot, am scris un articol pentru a explica cum să faceți acest lucru dezactivând reîmprospătarea fundalului pentru interogări.
Utilizați Dezactivați evenimentul în schimb
O altă opțiune este să utilizați evenimentul Worksheet_Deactivate în loc de Worksheet_Change. Evenimentul Worksheet_Deactivate va rula de fiecare dată când utilizatorul părăsește foaia și selectează o foaie diferită. Aceasta îi permite utilizatorului să facă toate modificările la datele sursă, apoi tabelul pivot va fi reîmprospătat automat când vor merge la orice altă foaie, inclusiv foile care conțin tabelul pivot.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Acest cod ar fi în continuare plasat în modulul de foaie care conține datele sursă. Aceasta este o opțiune bună dacă tabelele pivot sau conexiunile de date iau câteva secunde sau mai mult pentru actualizare și nu doriți să așteptați de fiecare dată când se face o modificare a datelor sursă.
Singura dată când este posibil să nu doriți să utilizați acest lucru este dacă tabelul pivot și sursa dvs. datele sunt pe aceeași foaie.Acesta va fi, de obicei, un caz rar și ceva ce, în general, nu recomand.
Mulțumită sugestiei de la Ted în acest sens.
Economisiți timpul & Embarrassment
Sper că acest articol vă va ajuta să economisiți timp și să îl faceți mai ușor pentru utilizatorii fișierelor dvs. Vă poate ajuta și la prevenirea jenării atunci când uitați să reîmprospătați tabelele pivot înainte de a trimite rapoarte. Credeți eu, am făcut această greșeală de mai multe ori decât aș vrea să recunosc … 🙂
