Come Aggiornamento automatico tabella pivot utilizzazione del VBA in Microsoft Excel
In questo articolo, impareremo come aggiornare automaticamente la tabella pivot utilizzando VBA in Microsoft Excel 2010.
Perché dobbiamo aggiornare il grafico della tabella pivot?
Come tutti sappiamo, ogni volta che apportiamo modifiche ai dati di origine di una tabella pivot, non si riflettono 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.
Questa è la semplice sintassi per aggiornare automaticamente le tabelle pivot nella cartella di lavoro.
'Code in Source Data Sheet Object
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 i 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 usando un link: / events-in-vba-the-worksheet-events-in-excel-vba [evento 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 andare nel foglio dati di origine.
Qui useremo Worksheet_SelectionChange Event. In questo modo il codice verrà eseguito ogni volta che si passa dal foglio dati sorgente a un altro foglio.
Spiegherò più tardi perché ho utilizzato questo evento.
Qui, ho i dati di origine in sheet2 e tabelle pivot in sheet1.
Apri VBE usando CTRL + F11. In Esplora progetti, puoi vedere tre oggetti, Foglio1, Foglio2 e la 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 sotto nome vuoto Worksheet_Deactivate. Il nostro codice sarà scritto in questo sottotitolo. 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 vai alla scheda di analisi della tabella pivot. Sul lato sinistro, vedrai il nome della tabella pivot. Puoi anche modificare 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 precedente, volevamo aggiornare solo 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
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
In questo codice stiamo usando un link: / vba-for-loops-with-7-examples [For loop] per scorrere ogni cache pivot nella cartella di lavoro. L’oggetto ThisWorkbook contiene tutte le cache pivot. Per accedervi utilizziamo ThisWorkbook.PivotCaches.
Perché utilizzare Worksheet_Deactivate Event?
Se desideri aggiornare la tabella pivot non appena vengono apportate modifiche ai dati di origine, dovresti utilizzare l’evento Worksheet_Change. Ma io non lo consiglio. Farà in modo che la tua cartella di lavoro esegua il codice ogni volta che apporti modifiche al foglio. Potresti dover fare 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 per l’elaborazione. Quindi, se hai tabelle pivot e dati in fogli diversi, è meglio usare Evento Disattivazione 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 hai tabelle pivot e dati di origine sullo stesso foglio e desideri che le tabelle pivot si aggiornino automaticamente, potresti utilizzare `link: / tips-using-worksheet-change-event-to-run-macro-when- any-change-is-made [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
Tieni presente che questo codice non modifica l’origine dati. Quindi, se aggiungi dati sotto i dati di origine, questo codice non includerà quei dati automaticamente. Puoi usare link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] per memorizzare i dati di origine. Se non vuoi usare le tabelle, possiamo usare VBA anche per includere nuovi dati. Lo impareremo nel prossimo tutorial.
Spero che questo articolo su Come aggiornare automaticamente la tabella pivot utilizzando VBA in Microsoft Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e sulle formule di Excel correlate qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected].
Articoli correlati:
link: / excel-pivot-tables-how-to-dinamically-update-pivot-table-data-source-range-in-excel [How to Dynamically Update Pivot Table Data Source Range in Excel]: Per modificare dinamicamente il intervallo di dati di origine delle tabelle pivot, utilizziamo cache pivot. In VBA usa oggetti tabelle pivot come mostrato sotto …
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Run Macro If Any Change Made on Sheet in Specified Range]: Nelle tue pratiche VBA, otterrai la necessità di eseguire macro quando un determinato intervallo o cella cambia. In tal caso, per eseguire le macro quando viene apportata una modifica a un intervallo di destinazione, utilizziamo l’evento di modifica.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Run Macro When any Change is Made On Sheet]: Quindi per eseguire la tua macro ogni volta che il foglio aggiornamenti, utilizziamo gli eventi del foglio di lavoro di VBA.
link: / events-in-vba-semplicest-vba-code-to-highlight-current-row-and-column-using [Codice VBA più semplice per evidenziare riga e colonna correnti utilizzando]: Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.
link: / events-in-vba-the-worksheet-events-in-excel-vba [The Worksheet Events in Excel VBA]: Gli eventi del foglio di lavoro sono davvero utili quando si desidera che le macro vengano eseguite quando si verifica un evento specificato il foglio.
Articoli popolari:
link: / tips-if-condition-in-excel [Come usare la funzione IF in Excel]: L’istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è TRUE o restituisce un altro valore specifico se FALSE .
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.