Come aggiornare dinamicamente tabella pivot Gamma origine dati in Excel
Attualmente, possiamo modificare o aggiornare dinamicamente le tabelle pivot usando link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] o `link: / excel-range-name-dynamic- intervalli denominati in Excel [intervalli denominati dinamici]. Ma queste tecniche non sono infallibili. Poiché dovrai comunque aggiornare manualmente la tabella pivot. Se hai dati di grandi dimensioni che contengono migliaia di righe e colonne, le tabelle Excel non ti aiuteranno molto. Invece renderà il tuo file pesante. Quindi l’unico modo che rimane è VBA.
In questo articolo impareremo come fare in modo che la nostra tabella pivot cambi automaticamente l’origine dati. In altre parole, automatizzeremo il processo manuale di modifica dell’origine dati per includere dinamicamente nuove righe e colonne aggiunte alle tabelle di origine e riflettere immediatamente la modifica nella tabella 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 consentirà di utilizzare //events-in-vba/the-worksheet-events-in-excel-vba.html[worksheet events] `.
Se i dati di origine e le tabelle pivot sono 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 la tabella 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 la tabella pivot con un nuovo intervallo Per spiegare come funziona, ho una cartella di lavoro. Questa cartella di lavoro contiene due fogli. Sheet1 contiene i dati di origine che possono cambiare. Sheet2 contiene la tabella pivot che dipende 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 pt As PivotTable
Dim pc As PivotCache
Dim source_data As Range
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
pt.ChangePivotCache pc
End Sub
Se hai una cartella di lavoro simile, puoi copiare direttamente questi dati. Ho spiegato che questo codice funziona di seguito.
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.
Ora per modificare i dati di origine della tabella pivot cambiamo i dati nella cache pivot. Una tabella pivot viene creata utilizzando la cache pivot. La cache pivot contiene i vecchi dati di origine fino a quando la tabella pivot non viene aggiornata manualmente o l’intervallo di dati di origine viene modificato manualmente.
Abbiamo creato riferimenti di tabelle pivot nome pt, cache pivot denominata pc e un intervallo denominato source_data. I dati di origine conterranno tutti i dati. 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.
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.
Archiviamo questi dati nella cache pivot poiché sappiamo che la cache pivot memorizza tutti i dati.
Imposta pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Successivamente definiamo la tabella pivot che vogliamo aggiornare. Poiché vogliamo aggiornare la tabella pivot1 (nome della tabella pivot. Puoi controllare il nome della tabella pivot nella scheda di analisi mentre selezioni la tabella pivot.) Su sheet1, impostiamo pt come mostrato di seguito.
Imposta pt = Sheet2.PivotTables (“PivotTable1”)
Ora usiamo semplicemente questa cache pivot per aggiornare la tabella pivot. Usiamo il metodo changePivotCache dell’oggetto pt.
pt.ChangePivotCache pc
E abbiamo la nostra tabella pivot automatizzata. Questo aggiornerà automaticamente la tua tabella pivot. Se hai più tabelle con la stessa origine dati, usa semplicemente la stessa cache in ogni oggetto tabella pivot.
Quindi sì ragazzi, è così che puoi modificare dinamicamente l’intervallo di origini dati in Excel. Spero di essere stato abbastanza esplicativo. Se hai domande su questo articolo, fammelo sapere nella sezione commenti qui sotto.
Articoli correlati:
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.