Tabella pivot Excel VBA
La tabella pivot VBA consente di riepilogare i report da un set di dati di grandi dimensioni. Pivot può essere considerato una piccola forma dell'intero set di dati. La visualizzazione rapida di set di dati di grandi dimensioni è possibile tramite una tabella pivot. Una tabella pivot è un modo semplice per filtrare i dati di conseguenza. Dai dati disponibili, è possibile evidenziare i dati nel modo desiderato. Una tabella pivot consente di combinare i dati enormi per analizzare i dati e produrre report che soddisfino i requisiti aziendali.
Excel offre una tabella pivot integrata che è facile da creare applicando una tabella pivot su un foglio dati. È possibile generare automaticamente un report in Excel una volta forniti i dati. I codici VBA ti aiuteranno a creare una tabella pivot automatica.
Come creare una tabella pivot utilizzando Excel VBA?
I passaggi comuni per inserire una tabella pivot è innanzitutto inserire una tabella pivot dal menu Inserisci, quindi selezionare le tabelle che si desidera modificare in una tabella pivot. La tabella selezionata diventerà i dati di origine e la tabella pivot verrà creata di conseguenza.
Per creare un rapporto pivot devi selezionare i campi per i filtri, i valori, ecc. Allo stesso modo, questi passaggi possono essere automatizzati usando i codici VBA. Abbiamo bisogno di un foglio Excel per funzionare con una tabella pivot. Impariamo come creare una tabella pivot tramite i codici VBA di Excel.
È possibile scaricare questo modello Excel tabella pivot VBA qui - Modello Excel tabella pivot VBAPassaggio 1 : la tabella pivot deve essere considerata come oggetto di riferimento. Creare un oggetto per la tabella pivot su ulteriori codici questo oggetto verrà utilizzato per puntare la tabella pivot. Denominare la variabile come pvtable. Dichiarare una funzione e un oggetto tabella pivot.
Codice:
Sub PivotTable () Dim pvtable As PivotTable End Sub
Passaggio 2: per conservare i file di dati è necessaria una cache pivot per dichiarare l'origine dei dati. Prima di creare una tabella pivot dichiarare una variabile di cassa pivot. Dichiarare la variabile pvcache per la cache pivot.
Codice:
Dim pvcache Come PivotCache
Passaggio 3: i dati si trovano all'interno del foglio di lavoro e devono specificare un intervallo per colpire la cella di cui hai bisogno. Nella tabella pivot, i dati di origine sono distribuiti come righe e colonne, quindi per indicare un intervallo particolare abbiamo bisogno di una variabile. Definire pvrange come variabile di intervallo.
Codice:
Dim pvrange As Range
Passaggio 4: un foglio di lavoro deve inserire la tabella pivot che è necessario creare. Dichiarare una variabile come foglio di lavoro. Definire pvsheet come foglio di lavoro.
Codice:
Dim pvsheet As Worksheet
Passaggio 5: è necessaria una variabile simile per utilizzare il foglio dati che dovrebbe contenere i dati che si desidera tracciare come tabella pivot. Quindi la variabile del foglio dati è dichiarata come pdsheet.
Codice:
Dim pdsheet come foglio di lavoro
Passaggio 6: sono necessarie altre due variabili come tipo di dati lungo per indicare l'ultima riga e colonna utilizzate per la creazione della tabella pivot. Può trattarsi di qualsiasi riga o colonna, pertanto esiste la possibilità che un numero di righe e colonne superi il limite del tipo di dati intero. Chiamiamolo plr e plc.
Codice:
Dim plr As Long Dim plc As Long
Passaggio 7: il passaggio successivo consiste nell'eliminare se è già stata creata una tabella pivot. Ciò contribuirà ad evitare confusioni su quale tabella tracciare i dati di origine. Quindi eliminare il foglio della tabella pivot precedente e creare un nuovo foglio per inserire la tabella pivot.
Codice:
In caso di errore Riprendi Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Elimina "per eliminare la tabella pivot esistente nel foglio di lavoro. Aggiungi dopo: = ActiveSheet" per aggiungere un nuovo foglio di lavoro ActiveSheet.Name = " pvsheet "'per rinominare il foglio di lavoro in" pvsheet "In caso di errore Vai a 0
Passaggio 8: assegnare una variabile oggetto per il foglio pivotante e il foglio dati rispettivamente alle variabili pvsheet e pdsheet. Questo verrà utilizzato ulteriormente per specificare i fogli di lavoro.
Codice:
Imposta pvsheet = fogli di lavoro ("pvsheet") Imposta pdsheet = fogli di lavoro ("pdsheet")
Passaggio 9: una volta impostati i fogli di lavoro, l'elemento successivo è l'ultima riga e colonna utilizzata per creare un rapporto pivot. Trova l'ultima riga e colonna utilizzate utilizzando le variabili dichiarate plr e plc.
Codice:
'due variabili per trovare l'ultima riga e colonna utilizzate in pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column
Passaggio 10: Come abbiamo menzionato in precedenza, i dati nel foglio di lavoro sono specificati dall'intervallo di celle. Devi impostare l'intervallo di rotazione nel passaggio successivo. È già stato dichiarato come variabile per utilizzare l'intervallo pivot "pvrange".
Codice:
'inizializzazione dell'intervallo di dati della tabella pivot Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Poiché l'intervallo viene impostato utilizzando la proprietà di ridimensionamento degli intervalli di celle, ridimensionerà il pvrange regolando di conseguenza l'intervallo di pivot. Quindi il pvrange si adatterà se si verificano aggiunte o eliminazioni di righe o colonne.
Passaggio 11: è tempo di impostare la cache pivot che è l'origine della tabella pivot. Utilizzare l'oggetto pvcache per impostare la cache di origine.
Codice:
'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Passo 12: Qui il rapporto sulle vendite per diversi prodotti verrà convertito in una tabella pivot attraverso questo. Creare una tabella pivot come vuota alla quale è possibile aggiungere ulteriormente il set di dati.
Codice:
'nuova tabella pivot vuota Imposta pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Passaggio 13: una volta inserito il pivot, è necessario specificare i diversi campi che si desidera inserire nella tabella pivot. Quindi inserire il campo della prima riga. Qui la prima riga inizia con il prodotto.
Codice:
"Inserisci prodotto nella riga archiviato con pvsheet.PivotTables (" Sales_Report "). PivotFields (" Product ") .Orientation = xlRowField .Position = 1 End With
Passaggio 14: il prossimo è specificare il secondo campo che si desidera inserire nella tabella pivot. Allo stesso modo, inserire la street field della seconda riga nella tabella pivot.
Codice:
'Inserisci Street to Row Archiviato e posiziona 2 con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With
Passaggio 15: inserire il campo successivo nella tabella pivot e il prossimo è una città. Dai il codice per inserire il campo della città.
Codice:
"Inserisci la città nella colonna archiviata con pvsheet.PivotTables (" Sales_Report "). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 End With
Passo 16: Fino a questo, i dati inseriti erano di tipo testo. Ora dobbiamo inserire il numero di vendite nella tabella pivot.
Codice:
'Inserisci colonna Vendite nel campo dati Con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Termina con
Passaggio 17: sono stati inseriti i campi che devono creare una tabella pivot. E la tabella pivot è quasi terminata ora puoi impostare il formato della tabella pivot. Questo specificherà il tipo di tabella attraverso lo stile della tabella. Anche il layout dell'asse delle file è impostato nel modo desiderato.
Codice:
"imposta il formato Tabella pivot pvsheet.PivotTables (" Sales_Report "). ShowTableStyleRowStripes = True pvsheet.PivotTables (" Sales_Report "). TableStyle2 =" PivotStyleMedium14 "
Passaggio 18: per mostrare gli elementi dei valori della riga archiviati in forma tabulare, aggiungere il codice seguente in basso.
Codice:
"per mostrare la tabella pivot in formato tabulare pvsheet.PivotTables (" Sales_Report "). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Passaggio 19: premere il pulsante Esegui o premere F5 per eseguire il codice. Ciò produrrà una tabella pivot dal foglio dell'origine dati. I dati forniti in righe e colonne verranno modificati nella tabella pivot. La tabella pivot sarà visibile sul foglio di lavoro della tabella pivot.
Controlla l'output e puoi vedere l'origine dati convertita nella tabella pivot come di seguito, le colonne menzionate vengono convertite nei moduli di filtro.
I campi pivot sono visibili sul lato destro. Puoi apportare modifiche in base alle tue esigenze in merito alla modalità di visualizzazione dei dati.
Per riferimento, ho fornito il codice di seguito.
Codice:
Tabella pivot secondaria () Dim pvtable come tabella pivot Dim pvcache come PivotCache Dim pvrange come intervallo Dim pvsheet come foglio di lavoro Dim pdsheet come foglio di lavoro Dim plr Come lungo Dim plc come Long On Error Riprendi Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet "). Elimina 'per eliminare la tabella pivot esistente nel foglio di lavoro. Aggiungi dopo: = Foglio attivo' per aggiungere un nuovo foglio di lavoro ActiveSheet.Name =" pvsheet "'per rinominare il foglio di lavoro in" pvsheet "In caso di errore GoTo 0 Imposta pvsheet = Fogli di lavoro ("pvsheet") Imposta pdsheet = Fogli di lavoro ("pdsheet") "due variabili per trovare l'ultima riga e colonna utilizzate in pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'inizializzazione intervallo dati tabella pivot Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nuova tabella pivot vuota Imposta pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Inserisci prodotto nella riga archiviata con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With' Insert Street to Riga archiviata e posizione 2 Con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Termina con 'Inserisci città nella colonna Archiviata con pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 Termina con 'Inserisci colonna vendite nel campo dati Con pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With' imposta formato Tabella pivot pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'per mostrare la tabella pivot in formato tabvRepTep () .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Cose da ricordare
- Specificare il foglio di origine in cui devono essere forniti i dati per una tabella pivot.
- Le righe e le colonne iniziali devono essere menzionate correttamente.
- Utilizzare gli oggetti necessari per individuare la cache pivot, l'intervallo, l'inizio e la fine delle righe.
- Disporre l'origine dati con il formato corretto poiché si tratta del processo di automazione.
Articoli consigliati
Questa è una guida alla tabella pivot VBA. Qui discutiamo come creare una tabella pivot usando i codici VBA in Excel insieme a un esempio e un modello di Excel scaricabile. Puoi anche consultare i seguenti articoli per saperne di più -
- Come eliminare la tabella pivot in Excel?
- Utilizzo di VBA in PowerPoint
- Tabella pivot con più fogli
- Come creare affettatrice della tabella pivot?
- Aggiorna tabella pivot in VBA
- VBA On Error Riprendi Successivo (Esempi)
- Excel VBA ScreenUpdating