Conclusione: scopri come utilizzare una semplice macro per aggiornare automaticamente le tabelle pivot ogni volta che vengono apportate modifiche ai dati di origine. Condivido anche una soluzione non macro per aggiornare le tabelle pivot quando il file viene aperto. Include video tutorial e download di file Excel.
Livello di abilità: intermedio
Scarica il file Excel
Se impari meglio facendolo da solo, puoi scarica il file che sto utilizzando nel video per seguirlo. Ecco il file Excel che contiene il codice VBA.
Aggiorna automaticamente la tabella pivot.xlsm (41,5 KB)
Aggiorna automaticamente le tabelle pivot
Le tabelle pivot possono essere aggiornate immediatamente e automaticamente quando cambiano i dati di origine?
Assolutamente. Richiede l’uso di una macro molto semplice che ti mostrerò come creare di seguito.
Se non hai molta familiarità con le macro e VBA, suggerisco di dare un’occhiata alla mia serie di video gratuiti in 3 parti su come iniziare con le macro & VBA.
Inoltre, se non conosci le tabelle pivot, Ho una serie per illustrarti cosa sono e come utilizzarli. Guarda il primo video di quella serie sulle tabelle pivot & Dashboard
Per aggiornare automaticamente le nostre tabelle pivot, scriveremo una macro con una semplice istruzione. Quell’istruzione dice fondamentalmente: quando apporto una modifica al mio foglio di lavoro, aggiorno tutte le tabelle pivot e le connessioni dati. Di seguito sono riportati i passaggi per creare la macro.
Apri Visual Basic Editor.
Puoi farlo facendo clic sul pulsante Visual Basic nella scheda Sviluppo della barra multifunzione.
La scorciatoia da tastiera per aprire l’editor di Visual Basic è Alt + F11.
Se non vedi nella scheda Sviluppatore, puoi renderlo visibile utilizzando le istruzioni qui. Devi farlo solo una volta, quindi la scheda Sviluppatore sarà sempre visibile ogni volta che apri Excel in futuro.
Apri il modulo del foglio che contiene i tuoi dati di origine.
Nella finestra Esplora progetti dell’editor di Visual Basic, individuare la cartella di lavoro che si desidera modificare. Sotto quella cartella di lavoro sono elencati i fogli all’interno della cartella di lavoro. Seleziona il foglio che contiene i dati di origine. Quindi fai doppio clic su di esso.
Se non vedi la finestra Project Explorer puoi abilitarla dalla Vista menu (scorciatoia da tastiera: Ctrl + R).
Aggiungi un nuovo evento per le modifiche al foglio di lavoro.
Facendo doppio clic sul foglio si apre il modulo di codice per quell’oggetto. All’interno del modulo del codice, vogliamo creare una macro di eventi. A tale scopo, scegli Foglio di lavoro nella casella a discesa Oggetto a sinistra.
Ciò aggiungerà un evento Worksheet_SelectionChange a il modulo, che in realtà non vogliamo, quindi lo elimineremo tra un momento. Prima di farlo, andiamo al menu a discesa Procedura sulla destra e scegli Cambia.
Questo aggiunge un nuovo evento in alto chiamato Worksheet_Change. Ora evidenzieremo ed elimineremo il codice non necessario sottostante.
La macro dell’evento Worksheet_Change verrà eseguita ogni volta che viene apportata una modifica alle celle in quel foglio di lavoro. Possiamo aggiungere codice VBA all’evento Worksheet_Change per eseguire azioni quando l’utente modifica le celle.
Nota: l’evento SelectionChange aggiunto per impostazione predefinita verrà eseguito ogni volta che l’utente seleziona una cella nel foglio. Poiché vogliamo che il codice venga eseguito solo quando l’utente modifica / cambia le celle, utilizziamo l’evento Change. Consulta il mio articolo sui moduli di codice VBA & Come eseguire macro in base agli eventi utente per saperne di più sui moduli e gli eventi del foglio.
Aggiungi il codice VBA per aggiornare tutte le tabelle pivot.
Successivamente, appena sotto la riga Worksheet_Change, digita questa istruzione:
ThisWorkbook.RefreshAll
Il metodo RefreshAll aggiornerà tutte le tabelle pivot, le query e le connessioni dati nella cartella di lavoro. Questa azione equivale a fare clic manualmente sul pulsante Aggiorna nella scheda Dati.
Aggiungi questa riga di codice all’evento Worksheet_Change aggiornerà la cartella di lavoro ogni volta che viene apportata una modifica al foglio di lavoro che il codice è in.
Tabella pivot & Dati di origine sullo stesso foglio
Aleksandrs ha posto una grande domanda sui commenti ai video di YouTube. Se la tabella pivot e i dati di origine si trovano sullo stesso foglio, sarà necessario aggiungere codice per disabilitare gli eventi.
L’aggiornamento inserisce l’evento in un ciclo ricorsivo e può finire per bloccare Excel. Ecco il codice per impedirlo.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Verifica per assicurarsi che la macro sia in esecuzione
Un modo per verificare se la macro funziona è testarlo. Apporta una modifica ai dati di origine e verifica se si riflettono nella tabella pivot.Se la tua modifica non è facile da individuare perché hai troppi dati o per qualche altro motivo, c’è un altro modo per vedere se la tua macro si sta attivando.
Nell’editor VB, puoi fare clic sul colonna grigia appena a sinistra della macro Worksheet_Change. Questo farà apparire un cerchio rosso. Evidenzia anche quella riga di codice in rosso.
Questo è chiamato arresto o punto di interruzione.
La scorciatoia da tastiera per attivare / disattivare un punto di interruzione è: F9
Ora, ogni volta che si verifica un’azione che attiva la macro, Excel salterà all’editor VB e metterà in pausa la macro in modo da poter controllare il codice. Nel nostro caso, tale azione è una qualsiasi modifica apportata al foglio di lavoro.
Puoi quindi premere F8 per scorrere ciascuna o premi F5 per correre fino alla fine (o al punto di interruzione successivo).
Se apporti una modifica al foglio di lavoro ed Excel non ti porta nell’Editor VB, sai che c’è un problema con la macro non è in esecuzione. In questo caso, è probabile che tu non abbia salvato il file come cartella di lavoro abilitata per le macro (.xlsm) e / o macro abilitate. Potrebbe essere necessario salvare & chiudere il file, quindi riaprirlo e abilitare le macro.
Per rimuovere il punto di interruzione che hai inserito nella macro, fai semplicemente clic sul cerchio rosso per farlo scomparire (scorciatoia da tastiera: F9).
La scorciatoia da tastiera per cancellare tutti i punti di interruzione è: Ctrl + Maiusc + F9
Aggiornamento delle tabelle pivot senza un Macro
Uno svantaggio dell’utilizzo di questa macro per aggiornare le tabelle pivot è che la cronologia degli annullamenti viene persa ogni volta che viene eseguita la macro. In altre parole, quando fai clic sul pulsante Annulla (o premi Ctrl + Z), Excel non ricorda l’ultima cosa che hai fatto, quindi non può annullarla. Di conseguenza, non accadrà nulla e la tua ultima modifica non verrà annullata.
Esiste un’alternativa che ti consente di mantenere la cronologia degli annullamenti. Tuttavia, questa alternativa aggiorna la tabella pivot solo quando la cartella di lavoro viene aperta, non ogni volta che viene apportata una modifica. Ecco come puoi utilizzare questa opzione.
Partendo da qualsiasi cella della tabella pivot:
- Vai alla scheda Analizza nella barra multifunzione.
- Scegli il pulsante Opzioni.
- Vai alla scheda Dati nella nuova finestra che si apre.
- Seleziona la casella “Aggiorna i dati all’apertura del file”.
Dopo aver fatto clic su OK, potresti ricevere il seguente messaggio di avviso se hai più tabelle pivot create dallo stesso intervallo di dati di origine. Fai clic su OK per esaminarlo.
Ancora una volta, solo a titolo di confronto, se usi questa opzione mantieni la cronologia degli annullamenti, ma aggiorna la tabella pivot solo quando la cartella di lavoro viene chiusa e riaperta. Se usi l’opzione macro, perdi la cronologia degli annullamenti, ma la tabella pivot si aggiorna automaticamente tes ogni volta che viene apportata una modifica alla cartella di lavoro.
Variazioni per l’aggiornamento delle tabelle pivot
La macro che abbiamo esaminato non solo aggiornerà le tabelle pivot, ma aggiornerà anche tutte le query . Se desideri aggiornare solo le tabelle pivot, puoi sostituire il comando “ThisWorkbook.RefreshAll” con questo codice:
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
Ogni tabella pivot è collegata a un cache pivot sottostante, che è connessa ai dati di origine. Il codice scorre attraverso tutte le cache pivot nella cartella di lavoro e le aggiorna. Le tabelle pivot dello stesso intervallo di origine possono condividere cache pivot, quindi questo metodo è più veloce del ciclo di tutte le tabelle pivot .
Allo stesso modo, supponiamo che tu voglia aggiornare solo una particolare tabella pivot. In tal caso, puoi sostituire il codice “ThisWorkbook.RefreshAll” con il codice seguente.
E infine, se stai utilizzando Power Query e desideri disabilitare l’aggiornamento in background in modo che le query vengano aggiornate PRIMA delle tabelle pivot, ho scritto un articolo per spiegare come farlo disabilitando l’aggiornamento in background sulle query.
Usa il Disattiva invece l’evento
Un’altra opzione è usare l’evento Worksheet_Deactivate invece di Worksheet_Change. L’evento Worksheet_Deactivate verrà eseguito ogni volta che l’utente lascia il foglio e seleziona un foglio diverso. Ciò consente all’utente di apportare tutte le modifiche a i dati di origine, quindi la tabella pivot verrà automaticamente aggiornata quando passano a qualsiasi altro foglio, inclusi i fogli che contengono la tabella pivot.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Questo codice verrebbe comunque inserito nel modulo del foglio che contiene i dati di origine. Questa è una buona opzione se le tabelle pivot o le connessioni dati richiedono pochi secondi o più per l’aggiornamento e non vuoi aspettare ogni volta che viene apportata una modifica ai dati di origine.
L’unica volta che potresti non voler utilizzare questo è se la tabella pivot e la fonte i dati sono sullo stesso foglio.Questo di solito è un caso raro e qualcosa che generalmente non consiglio.
Grazie al suggerimento di Ted su questo.
Risparmia tempo & Imbarazzo
Spero che questo articolo ti aiuti a risparmiare tempo e renda più facile agli utenti i tuoi file. Può anche aiutare a prevenire l’imbarazzo quando dimentichi di aggiornare le tabelle pivot prima di inviare rapporti. Credi io “ho commesso questo errore più volte di quanto vorrei” ammettere … 🙂
