Come aggiornare dinamicamente Tutti tabelle pivot origine dati in Excel
In un articolo precedente, abbiamo imparato come modificare e aggiornare dinamicamente singole tabelle pivot con origini dati che si restringono o si espandono.
In questo articolo impareremo come 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 immediatamente la modifica nelle tabelle pivot.
Scrivi codice nel foglio dati sorgente Dato che vogliamo che questo sia completamente automatico, useremo moduli foglio per scrivere codice invece di un modulo principale. Questo ci permetterà di usare link: / events-in-vba-the-worksheet-events-in-excel-vba [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 di lavoro che contiene i dati di origine (non che contiene tabelle pivot). Premere CTRL + F11 per aprire l’editor di VB. Ora vai a Esplora progetto 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 modificare dinamicamente i dati di origine e aggiornare la 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 informazioni su tutti gli eventi del foglio di lavoro link: / events-in-vba-the-worksheet-events-in-excel-vba [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. Sheet1 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 si passa dal foglio dati di origine.
Private Sub Worksheet_Deactivate() Dim source_data As Range 'Determining last row and column number lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Setting the new range Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Code to loop through each sheet and pivot table For Each ws In ThisWorkbook.Worksheets For Each 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 da poterlo modificare 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 utilizzato 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. Dobbiamo solo usarlo nella tabella pivot.
Poiché non sappiamo quante tabelle pivot conterrà una cartella di lavoro alla volta, eseguiremo un ciclo attraverso ogni foglio e le tabelle pivot di ogni 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 ws
Il primo ciclo scorre attraverso ogni foglio. Il secondo ciclo itera su ciascuna tabella pivot in un foglio. Le tabelle pivot sono assegnate alla variabile pt. * Usiamo il metodo ChangePivotCache dell’oggetto pt. Creiamo dinamicamente una cache pivot utilizzando il metodo ThisWorkbook.PivotCaches.Create. 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 le nostre tabelle pivot automatizzate. Questo aggiornerà automaticamente tutte le tabelle pivot nella cartella di lavoro.
Quindi sì ragazzi, è così che puoi 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, fammelo sapere nella sezione commenti qui sotto.
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. Queste poche righe possono aggiornare dinamicamente qualsiasi tabella pivot modificando l’intervallo di dati di origine.
link: / custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [How to Auto Refresh Pivot Tables Using VBA]: Per aggiornare automaticamente le tue tabelle pivot puoi usare Eventi VBA. Usa questa semplice riga di codice per aggiornare automaticamente la tua tabella pivot. È possibile utilizzare uno dei 3 metodi di aggiornamento automatico delle tabelle pivot.
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, lo faresti ottenere 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 macro ogni volta che il foglio viene aggiornato, utilizziamo gli eventi del foglio di lavoro di VBA.
link: / events-in-vba-semplice-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 [Gli eventi del foglio di lavoro 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 sul foglio.
Articoli popolari:
50 scorciatoie 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 valori da diversi intervalli e fogli. COUNTIF in Excel 2016 | Contare i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare un valore specifico.
La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.