In un articolo precedente, abbiamo appreso come è possibile modificare e aggiornare dinamicamente singole tabelle pivot con origini dati che si riducono o si espandono.
In questo articolo, impareremo come possiamo fare in modo che tutte le tabelle pivot in una cartella di lavoro cambino automaticamente l'origine dati. In altre parole, invece di modificare una tabella pivot alla volta, proveremo a modificare l'origine dati di tutte le tabelle pivot nella cartella di lavoro per includere dinamicamente nuove righe e colonne aggiunte alle tabelle di origine e riflettere istantaneamente la modifica nelle tabelle pivot.
Scrivi codice nel foglio dati sorgente
Poiché vogliamo che questo sia completamente automatico, utilizzeremo i moduli del foglio per scrivere il codice invece di un modulo principale. Questo ci consentirà di utilizzare gli eventi del foglio di lavoro.
Se i dati di origine e le tabelle pivot si trovano in fogli diversi, scriveremo il codice VBA per modificare l'origine dati della tabella pivot nell'oggetto foglio che contiene i dati di origine (non che contiene tabelle pivot).
Premi CTRL+F11 per aprire l'editor VB. Ora vai in Esplora progetti e trova il foglio che contiene i dati di origine. Fare doppio clic su di esso.
Si aprirà una nuova area di codifica. Potresti non vedere alcun cambiamento, ma ora hai accesso agli eventi del foglio di lavoro.
Fare clic sul menu a discesa a sinistra e selezionare il foglio di lavoro. Dal menu a discesa a sinistra, seleziona disattiva. Vedrai un sottotitolo vuoto scritto sul nome dell'area del codice worksheet_deativate. Il nostro codice per la modifica dinamica dei dati di origine e l'aggiornamento della tabella pivot andrà in questo blocco di codice. Questo codice verrà eseguito ogni volta che passerai dal foglio dati a qualsiasi altro foglio. Puoi leggere tutti gli eventi del foglio di lavoro qui.
Ora siamo pronti per implementare il codice.
Codice sorgente per aggiornare dinamicamente tutte le tabelle pivot nella cartella di lavoro con un nuovo intervallo
Per spiegare come funziona, ho una cartella di lavoro. Questa cartella di lavoro contiene tre fogli. Foglio1 contiene i dati di origine che possono cambiare. Sheet2 e Sheet3 contengono tabelle pivot che dipendono dai dati di origine di sheet2.
Ora ho scritto questo codice nell'area di codifica di sheet1. Sto usando l'evento Worksheet_Deactivate, in modo che questo codice venga eseguito per aggiornare la tabella pivot ogni volta che passiamo dal foglio dati di origine.
Private Sub Worksheet_Deactivate() Dim source_data As Range 'Determina il numero dell'ultima riga e colonna lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Impostazione del nuovo intervallo Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Codice per scorrere ogni foglio e tabella pivot per ogni ws In ThisWorkbook.Worksheets Per ogni pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=source_data) Next pt Next ws End Sub
Se hai una cartella di lavoro simile, puoi copiare direttamente questi dati. Ho spiegato che questo codice funziona di seguito in modo che tu possa modificarlo secondo le tue esigenze.
Puoi vedere l'effetto di questo codice nella gif qui sotto.
In che modo questo codice modifica automaticamente i dati di origine e aggiorna le tabelle pivot?
Prima di tutto abbiamo usato un evento worksheet_deactivate. Questo evento si attiva solo quando il foglio contenente il codice viene commutato o disattivato. Quindi questo è il modo in cui il codice viene eseguito automaticamente.
Per ottenere dinamicamente l'intera tabella come intervallo di dati, determiniamo l'ultima riga e l'ultima colonna.
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Usando questi due numeri definiamo source_data. Siamo certi che l'intervallo di dati di origine inizierà sempre da A1. Puoi definire il tuo riferimento di cella iniziale.
Imposta source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Ora abbiamo i dati di origine che sono dinamici. Abbiamo solo bisogno di usarlo nella tabella pivot.
Poiché non sappiamo quante tabelle pivot conterrà una cartella di lavoro alla volta, eseguiremo il ciclo di ciascun foglio e delle tabelle pivot di ciascun foglio. In modo che non rimanga alcuna tabella pivot. Per questo usiamo i cicli for annidati.
Per ogni ws In ThisWorkbook.Worksheets
Per ogni punto in ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=source_data)
Punto successivo
Avanti w
Il primo ciclo passa attraverso ogni foglio. Il secondo ciclo scorre su ogni tabella pivot in un foglio.
Le tabelle pivot sono assegnate a variabile pt. Usiamo il metodo ChangePivotCache dell'oggetto pt. Creiamo dinamicamente una cache pivot utilizzando ThisWorkbook.PivotCaches.Create
Metodo. Questo metodo accetta due variabili SourceType e SourceData. Come tipo di origine dichiariamo xlDatabase e come SourceData passiamo l'intervallo source_data che abbiamo calcolato in precedenza.
E questo è tutto. Abbiamo automatizzato le nostre tabelle pivot. Questo aggiornerà automaticamente tutte le tabelle pivot nella cartella di lavoro.
Quindi sì ragazzi, è così che è possibile modificare dinamicamente gli intervalli di origini dati di tutte le tabelle pivot in una cartella di lavoro in Excel. Spero di essere stato abbastanza esplicativo. Se hai domande su questo articolo, faccelo sapere nella sezione commenti qui sotto.
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.
Come aggiornare automaticamente le tabelle pivot utilizzando VBA: Per aggiornare automaticamente le tue tabelle pivot puoi utilizzare gli eventi VBA. Usa questa semplice riga di codice per aggiornare automaticamente la tua tabella pivot. Puoi utilizzare uno dei 3 metodi di aggiornamento automatico delle tabelle pivot.
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.