In questo articolo, impareremo come aggiornare automaticamente i dati della tabella pivot in Excel.
Scenario:
Come tutti sappiamo, ogni volta che apportiamo modifiche ai dati di origine di una tabella pivot, non si riflette immediatamente nella tabella pivot. Abbiamo bisogno di aggiornare le tabelle pivot per vedere le modifiche ogni volta che si apre una cartella di lavoro Excel. E se invii un file aggiornato senza aggiornare le tabelle pivot, potresti provare imbarazzo. Quindi impara qui come trovare l'opzione di aggiornamento quando usi la tabella pivot
Aggiorna i dati all'apertura di un file in Excel
Prima crea una tabella pivot, quindi fai clic con il pulsante destro del mouse su qualsiasi cella della tabella pivot.
Vai a Opzioni tabella pivot> scheda Dati> seleziona la casella che dice Aggiorna i dati all'apertura di un file
Ciò consentirà l'aggiornamento automatico dei dati ogni volta che il file viene aperto.
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Capiamo come utilizzare la funzione usando un esempio. Qui abbiamo alcuni dati e dobbiamo prima creare una tabella pivot e poi trovare le opzioni per abilitare l'aggiornamento automatico delle tabelle pivot.
Crea una tabella pivot e quindi fai clic con il pulsante destro del mouse su qualsiasi cella della tabella pivot come mostrato di seguito.
Seleziona le Opzioni tabella pivot e questo aprirà una finestra di dialogo Opzioni tabella PIvot.
Seleziona la scheda dati e quindi spunta la casella che dice Aggiorna dati all'apertura del file. Puoi farlo senza aprire e chiudere il file usando VBA.
Utilizzo di VBA
Quindi qui impareremo come aggiornare automaticamente una tabella pivot usando 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"
Foglio di lavoro secondario privato_Disattiva() nomefoglio_di_tabella_pivot.Tabelle pivot("pivot_table_name").PivotCache.Refresh Fine sottotitolo |
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.
| Foglio di lavoro secondario privato_Disattiva()
Foglio1.Tabelle pivot("Tabella pivot1").PivotCache.Aggiorna Fine sottotitolo |
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.
| Foglio di lavoro secondario privato_Disattiva()
'Foglio1.Tabelle pivot("Tabella pivot1").PivotCache.Aggiorna Per ogni pc in ThisWorkbook.PivotCaches pc.Aggiorna Pc successivo Fine sottotitolo |
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)
Foglio1.Tabelle pivot("Tabella pivot1").PivotCache.Aggiorna Fine sottotitolo |
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 Fine sottotitolo |
Nota : Il codice non modifica 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.
Spero che questo articolo su Come aggiornare automaticamente i dati della tabella pivot in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Come aggiornare dinamicamente l'intervallo di origini 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 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 Nota: gli eventi del foglio di lavoro sono davvero utili 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à : velocizza le tue attività in Excel. Queste scorciatoie ti aiuteranno ad aumentare l'efficienza del tuo lavoro in Excel.
Come utilizzare 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.
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
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.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.