Come tutti sappiamo, ogni volta che apportiamo modifiche ai dati di origine di una tabella pivot, non si riflette immediatamente nella tabella pivot. Dobbiamo aggiornare le tabelle pivot per vedere le modifiche. E se invii un file aggiornato senza aggiornare le tabelle pivot, potresti provare imbarazzo.
Quindi, in questo articolo, impareremo come aggiornare automaticamente una tabella pivot utilizzando VBA. In questo modo è più facile di quanto immaginassi.
Questa è la semplice sintassi per aggiornare automaticamente le tabelle pivot nella cartella di lavoro.
'Codice nell'oggetto foglio dati di origine Private Sub Worksheet_Deactivate() sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh End Sub
Cosa sono le cache pivot?
Ogni tabella pivot memorizza i dati nella cache pivot. Questo è il motivo per cui pivot è in grado di mostrare i dati precedenti. Quando aggiorniamo le tabelle pivot, aggiorna la cache con nuovi dati di origine per riflettere le modifiche sulla tabella pivot.
Quindi abbiamo solo bisogno di una macro per aggiornare la cache delle tabelle pivot. Lo faremo utilizzando un evento del foglio di lavoro in modo da non dover eseguire la macro manualmente.
Dove codificare per aggiornare automaticamente le tabelle pivot?
Se i dati di origine e le tabelle pivot si trovano in fogli diversi, il codice VBA dovrebbe essere inserito nel foglio dati di origine.
Qui useremo l'evento Worksheet_SelectionChange. Questo farà eseguire il codice ogni volta che passiamo dal foglio dati di origine a un altro foglio. Spiegherò più avanti perché ho usato questo evento.
Qui, ho i dati di origine in sheet2 e tabelle pivot in sheet1.
Apri VBE usando il tasto CTRL+F11. In Esplora progetti, puoi vedere tre oggetti, Foglio1, Foglio2 e Cartella di lavoro.
Poiché Sheet2 contiene i dati di origine, fare doppio clic sull'oggetto sheet2.
Ora puoi vedere due menu a discesa nella parte superiore dell'area del codice. Dal primo menu a discesa, seleziona il foglio di lavoro. E dal secondo menu a discesa seleziona Disattiva. Questo inserirà un sottonome vuoto Worksheet_Deactivate. Il nostro codice sarà scritto in questo sub. Qualsiasi riga scritta in questo sottotitolo, viene eseguita non appena l'utente passa da questo foglio a qualsiasi altro foglio.
Su sheet1 ho due tabelle pivot. Voglio aggiornare solo una tabella pivot. Per questo, ho bisogno di conoscere il nome della tabella pivot. Per conoscere il nome di qualsiasi tabella pivot, seleziona qualsiasi cella in quella tabella pivot e vai alla scheda di analisi della tabella pivot. Sul lato sinistro, vedrai il nome della tabella pivot. Puoi anche cambiare il nome della tabella pivot qui.
Ora che conosciamo il nome della tabella pivot, possiamo scrivere una semplice riga per aggiornare la tabella pivot.
Private Sub Worksheet_Deactivate() Sheet1.PivotTables("PivotTable1").PivotCache.Refresh End Sub
Ed è fatto.
Ora ogni volta che passerai dai dati di origine, questo codice vba verrà eseguito per aggiornare la tabella pivot1. Come puoi vedere nella gif qui sotto.
Come aggiornare tutte le tabelle pivot nella cartella di lavoro?
Nell'esempio sopra, volevamo solo aggiornare una tabella pivot specifica. Ma se vuoi aggiornare tutte le tabelle pivot in una cartella di lavoro, devi solo apportare lievi modifiche al tuo codice.
Private Sub Worksheet_Deactivate() 'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh Per ogni pc in ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
In questo codice utilizziamo un ciclo For per eseguire il ciclo di ciascuna cache pivot nella cartella di lavoro. L'oggetto ThisWorkbook contiene tutte le cache pivot. Per accedervi utilizziamo ThisWorkbook.PivotCaches.
Perché utilizzare l'evento Worksheet_Deactivate?
Se si desidera aggiornare la tabella pivot non appena viene apportata una modifica ai dati di origine, è necessario utilizzare l'evento Worksheet_Change. Ma non lo consiglio. Farà in modo che la tua cartella di lavoro esegua il codice ogni volta che apporti modifiche al foglio. Potrebbe essere necessario apportare centinaia di modifiche prima di voler vedere il risultato. Ma Excel aggiornerà la tabella pivot ad ogni modifica. Ciò comporterà uno spreco di tempo e risorse di elaborazione. Quindi, se hai tabelle e dati pivot in fogli diversi, è meglio usare Evento di disattivazione del foglio di lavoro. Ti permette di finire il tuo lavoro. Una volta che si passa ai fogli della tabella pivot per vedere le modifiche, le modifiche vengono modificate.
Se si dispone di tabelle pivot e dati di origine sullo stesso foglio e si desidera che le tabelle pivot si aggiornino automaticamente, è possibile utilizzare Worksheet_Change Event.
Private Sub Worksheet_Change(ByVal Target As Range) Sheet1.PivotTables("PivotTable1").PivotCache.Refresh End Sub
Come aggiornare tutto nelle cartelle di lavoro quando viene apportata una modifica ai dati di origine?
Se desideri aggiornare tutto su una cartella di lavoro (grafici, tabelle pivot, formule, ecc.) puoi utilizzare il comando ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Si prega di notare che questo codice non cambia l'origine dati. Quindi, se aggiungi dati sotto i dati di origine, questo codice non includerà quei dati automaticamente. Puoi utilizzare le tabelle di Excel per archiviare i dati di origine. Se non vuoi usare le tabelle, possiamo usare VBA anche per includere nuovi dati. Lo impareremo nel prossimo tutorial.
Quindi sì amico, ecco come puoi aggiornare automaticamente le tabelle pivot in Excel. Spero di essere stato abbastanza esplicativo e che questo articolo ti sia servito bene. Se hai domande su questo argomento, puoi chiedermelo nella sezione commenti qui sotto.
Come aggiornare dinamicamente l'intervallo di origine dati della tabella pivot in Excel: Per modificare dinamicamente l'intervallo di dati di origine delle tabelle pivot, utilizziamo le cache pivot. Queste poche righe possono aggiornare dinamicamente qualsiasi tabella pivot modificando l'intervallo di dati di origine. In VBA usa gli oggetti delle tabelle pivot come mostrato di seguito…
Esegui macro se vengono apportate modifiche al foglio nell'intervallo specificato: Nelle tue pratiche VBA, avresti la necessità di eseguire macro quando un determinato intervallo o cella cambia. In tal caso, per eseguire macro quando viene apportata una modifica a un intervallo di destinazione, utilizziamo l'evento di modifica.
Esegui macro quando vengono apportate modifiche al foglio | Quindi, per eseguire la tua macro ogni volta che il foglio si aggiorna, utilizziamo gli eventi del foglio di lavoro di VBA.
Il codice VBA più semplice per evidenziare la riga e la colonna correnti utilizzando | Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.
Gli eventi del foglio di lavoro in Excel VBA | L'evento del foglio di lavoro è davvero utile quando si desidera che le macro vengano eseguite quando si verifica un evento specificato sul foglio.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
La funzione CERCA.VERT in Excel | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
CONTA.SE in Excel 2016 | Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare un valore specifico. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.