Come posso aggiornare automaticamente un intervallo di tabelle pivot?

Aggiornare una tabella pivot è una seccatura, non è vero? Se usi spesso le tabelle pivot nel tuo lavoro, sono sicuro che puoi identificarti.

Il fatto è che ogni volta che aggiungi nuovi dati al foglio di origine, devi aggiornare l’intervallo di origine della tabella pivot prima di aggiornare la tabella pivot.

Ora immagina che se aggiungi dati al tuo foglio di origine ogni giorno, devi aggiornare l’intervallo di origine ogni giorno.

E ogni volta che si cambia l’intervallo della tabella pivot è un disastro. Sì, è vero, più frequentemente aggiungi dati, più devi aggiornare l’intervallo di origine.

Quindi il punto è che hai bisogno di un metodo per aggiornare automaticamente l’intervallo di origine quando aggiungi nuovi dati.

NOTA : le tabelle pivot fanno parte delle COMPETENZE EXCEL INTERMEDIE .

Applica tabella per aggiornare automaticamente l’intervallo della tabella pivot

Qualche giorno fa ho chiesto a John Michaloudis del suo consiglio da un milione di dollari per la tabella pivot. Dice: inserisci i tuoi dati di origine in una tabella. Credimi, questa è una mancia da un milione di dollari.

Applicando una tabella nei dati di origine, non è necessario modificare più e più volte l’intervallo di origine della tabella pivot.

Ogni volta che aggiungi nuovi dati, aggiorna automaticamente l’intervallo della tabella pivot.

Converti i dati in tabella prima di creare una tabella pivot

Ogni volta prima di creare una tabella pivot, assicurati di applicare la tabella ai dati di origine utilizzando i seguenti passaggi.

1. Seleziona una delle celle nei tuoi dati.
2. Utilizzare il tasto di scelta rapida Ctrl + T o accedere a → Inserisci scheda → Tabelle → Tabella.
3. Verrà visualizzato un popup con l’intervallo di dati corrente.
   
4. Fare clic su OK.
5. Ora per creare una tabella pivot, seleziona qualsiasi cella nei tuoi dati. Vai a → scheda Progettazione → Strumenti → Riepiloga con tabella pivot.
   
6. Fare clic su OK.

Ora, ogni volta che aggiungi nuovi dati al tuo foglio dati, l’intervallo della tabella pivot viene aggiornato automaticamente e devi solo aggiornare la tabella pivot.

Converti i dati in tabella dopo aver creato una tabella pivot

Se hai già una tabella pivot nel tuo foglio di lavoro, puoi utilizzare i seguenti passaggi per convertire la tua origine dati in una tabella.

1. Seleziona una delle celle nell’origine dati.
2. Utilizzare il tasto di scelta rapida Ctrl + T o accedere a → Inserisci scheda → Tabelle → Tabella.
3. Verrà visualizzato un popup con l’intervallo di dati corrente.
4. Fare clic su OK.
5. Ora seleziona una delle celle nella tabella pivot e vai su → Analizza → Dati → Modifica origine dati → Modifica origine dati (menu a discesa).
6. Verrà visualizzato un popup per riselezionare l’origine dati oppure puoi anche inserire il nome della tabella nella voce dell’intervallo.
   
7. Fare clic su OK.

D’ora in poi, ogni volta che aggiungi nuovi dati nel tuo foglio di origine, l’intervallo della tabella pivot aumenterà per aggiornarlo automaticamente.

Crea un intervallo di tabelle pivot dinamiche con la funzione OFFSET

L’altro modo migliore per aggiornare automaticamente l’intervallo della tabella pivot è utilizzare un intervallo dinamico.

L’intervallo dinamico può espandersi automaticamente ogni volta che aggiungi nuovi dati al foglio di origine. Ecco i passaggi per creare una gamma dinamica.

1. Vai a → Scheda Formule → Nomi definiti → Gestione nomi.
2. Dopo aver fatto clic sul gestore dei nomi, verrà visualizzata una finestra pop-up.
   
3. Nella finestra Gestione nomi, fai clic su Nuovo per creare un intervallo denominato.
4. Nella finestra del nuovo nome, inserisci
   1. Un nome per la tua nuova gamma. Utilizzo il nome “SourceData”.
   2. Specificare l’estensione dell’intervallo. È possibile specificare tra il foglio di lavoro corrente o la cartella di lavoro.
   3. Aggiungi un commento per descrivere l’intervallo denominato. Inserisci la formula seguente nella barra di inserimento “Fare riferimento a”.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Alla fine, fare clic su OK.

Ora hai l’intervallo dinamico per creare una tabella pivot.

Tutto quello che devi fare è semplicemente creare una tabella pivot con i tuoi dati di origine e quindi modificare l’origine con l’intervallo denominato utilizzando lo stesso metodo che ho utilizzato nel metodo delle prime tabelle.

Dopo aver aggiunto nuovi dati al foglio di origine, aggiorna semplicemente la tabella pivot.

Come funziona questa formula?

Nella formula sopra, ho utilizzato la funzione offset per creare un intervallo dinamico.

Ho menzionato la cella A1 come punto di partenza, quindi, senza menzionare righe e colonne, ho specificato l’altezza e la larghezza dell’intervallo utilizzando COUNTA.

COUNTA conterà le celle con valori dalla colonna A e dalla riga 1 e dirà a offset di espandere di conseguenza la sua altezza e larghezza.

L’unica cosa a cui devi prestare attenzione è che non ci siano celle vuote tra la colonna A e la riga 1.

Aggiorna la tabella pivot utilizzando il codice VBA

Alla maggior parte delle persone piace usare i codici VBA. Quindi ecco il codice da utilizzare per aggiornare l’intervallo della tabella pivot con VBA.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Cose che devi cambiare prima di usarlo nella tua cartella di lavoro.

1. Riga 13: modifica il nome del foglio di lavoro di origine.
2. Riga 14: modifica il nome del foglio della tabella pivot.
3. Riga 17: modifica il nome della tabella pivot.

Se riscontri ancora problemi durante l’utilizzo di questo codice, scrivimi nella casella dei commenti. Ora lascia che ti mostri come funziona questo codice in modo che tu possa modificarlo facilmente in base alle tue esigenze.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Nella parte precedente del codice, abbiamo specificato la tabella pivot e le variabili del foglio di lavoro dei dati di origine. Puoi modificare il nome del foglio di calcolo da qui.

 PivotName = "PivotTable2"

Nella parte sopra del codice, inserisci il nome della tabella pivot su cui desideri utilizzare questo codice.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

La parte precedente del codice creerà un intervallo dinamico utilizzando la cella A1 del foglio di lavoro dell’origine dati.

Controllerà l’ultima colonna e l’ultima riga con i dati per creare un intervallo dinamico. Ogni volta che esegui questa macro, creerà una nuova gamma dinamica.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

La parte precedente del codice aggiornerà la tabella pivot e visualizzerà un messaggio all’utente che la tabella pivot è ora aggiornata.