- Utilizzo di GetPivotData per ottenere un valore
- Creazione di una tabella pivot su un foglio
- Creazione di una tabella pivot su un nuovo foglio
- Aggiunta di campi alla tabella pivot
- Modifica del layout del report della tabella pivot
- Eliminazione di una tabella pivot
- Formattare tutte le tabelle pivot in una cartella di lavoro
- Rimozione dei campi di una tabella pivot
- Creazione di un filtro
- Aggiornare la tua tabella pivot
Questo tutorial dimostrerà come lavorare con le tabelle pivot utilizzando VBA.
Le tabelle pivot sono strumenti di riepilogo dei dati che puoi utilizzare per trarre informazioni e riepiloghi chiave dai tuoi dati. Facciamo un esempio: abbiamo un set di dati sorgente nelle celle A1:D21 contenente i dettagli dei prodotti venduti, mostrati di seguito:
Utilizzo di GetPivotData per ottenere un valore
Si supponga di avere una tabella pivot denominata Tabella pivot1 con Sales nel campo Valori/Dati, Prodotto come campo Righe e Regione come campo Colonne. È possibile utilizzare il metodo PivotTable.GetPivotData per restituire valori dalle tabelle pivot.
Il codice seguente restituirà $ 1.130,00 (le vendite totali per la regione orientale) dalla tabella pivot:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData("Vendite", "Regione", "Est") |
In questo caso, Sales è "DataField", "Field1" è la regione e "Item1" è East.
Il codice seguente restituirà $ 980 (le vendite totali per il prodotto ABC nella regione nord) dalla tabella pivot:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData("Vendite", "Prodotto", "ABC", "Regione", "Nord") |
In questo caso, Sales è "DataField", "Field1" è Product, "Item1" è ABC, "Field2" è Region e "Item2" è North.
Puoi anche includere più di 2 campi.
La sintassi per GetPivotData è:
Ottieni dati pivot (Campo dati, Campo1, Articolo1, Campo2, Articolo2… ) dove:
| Parametro | Descrizione |
|---|---|
| Campo dati | Campo dati come vendite, quantità ecc. che contiene numeri. |
| Campo 1 | Nome di una colonna o di un campo riga nella tabella. |
| Articolo 1 | Nome di un elemento nel campo 1 (facoltativo). |
| Campo 2 | Nome di una colonna o di un campo riga nella tabella (facoltativo). |
| Articolo 2 | Nome di un elemento nel campo 2 (facoltativo). |
Creazione di una tabella pivot su un foglio
Per creare una tabella pivot basata sull'intervallo di dati sopra, sulla cella J2 su Sheet1 della cartella di lavoro attiva, utilizzeremmo il seguente codice:
| 1234567891011 | Fogli di lavoro("Foglio1").Celle(1, 1).SelezionaActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Foglio1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _:=xlVersione tabella pivot15Fogli("Foglio1").Seleziona |
Il risultato è:
Creazione di una tabella pivot su un nuovo foglio
Per creare una tabella pivot basata sull'intervallo di dati sopra, su un nuovo foglio, della cartella di lavoro attiva, utilizzeremmo il seguente codice:
| 12345678910111213 | Fogli di lavoro("Foglio1").Celle(1, 1).SelezionaFogli.AggiungiActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Foglio1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _:=xlVersione tabella pivot15Fogli("Foglio2").Seleziona |
Aggiunta di campi alla tabella pivot
È possibile aggiungere campi alla tabella pivot appena creata denominata tabella pivot1 in base all'intervallo di dati sopra. Nota: il foglio contenente la tabella pivot deve essere il foglio attivo.
Per aggiungere Prodotto al campo Righe, utilizzerai il seguente codice:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = xlRowFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Position = 1 |
Per aggiungere Regione al campo Colonne, utilizzerai il seguente codice:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlColumnFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
Per aggiungere Vendite alla sezione Valori con il formato del numero di valuta, utilizzerai il seguente codice:
| 123456789 | ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"Tabella pivot1").PivotFields("Sales"), "Somma delle vendite", xlSumCon ActiveSheet.PivotTables("PivotTable1").PivotFields("Somma delle vendite").NumberFormat = "$#,##0.00"Termina con |
Il risultato è:
Modifica del layout del report della tabella pivot
Puoi modificare il layout del rapporto della tua tabella pivot. Il codice seguente cambierà il layout del report della tabella pivot in formato tabulare:
| 1 | ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight18" |
Eliminazione di una tabella pivot
Puoi eliminare una tabella pivot utilizzando VBA. Il codice seguente eliminerà la tabella pivot denominata tabella pivot1 sul foglio attivo:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, TrueSelezione.Cancella contenuto |
Formattare tutte le tabelle pivot in una cartella di lavoro
Puoi formattare tutte le tabelle pivot in una cartella di lavoro utilizzando VBA. Il codice seguente utilizza una struttura di ciclo per scorrere tutti i fogli di una cartella di lavoro ed eliminare tutte le tabelle pivot nella cartella di lavoro:
| 12345678910111213 | Sub FormattingAllThePivotTablesInAWorkbook()Sett deboli Come foglio di lavoroDim wb come cartella di lavoroImposta wb = ActiveWorkbookDim pt come tabella pivotPer ogni sett. in fogliPer ogni punto in wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Punto successivoSettimane successiveFine sottotitolo |
Per saperne di più su come utilizzare i loop in VBA, fai clic qui.
Rimozione dei campi di una tabella pivot
Puoi rimuovere i campi in una tabella pivot utilizzando VBA. Il codice seguente rimuoverà il campo Prodotto nella sezione Righe da una tabella pivot denominata Tabella pivot1 nel foglio attivo:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Prodotto").Orientation = _xlNascosto |
Creazione di un filtro
È stata creata una tabella pivot denominata Tabella pivot1 con Prodotto nella sezione Righe e Vendite nella sezione Valori. Puoi anche creare un filtro per la tua tabella pivot utilizzando VBA. Il codice seguente creerà un filtro basato su Regione nella sezione Filtri:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
Per filtrare la tabella pivot in base a un singolo elemento del report, in questo caso la regione orientale, utilizzare il codice seguente:
| 12345 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").ClearAllFiltersActiveSheet.PivotTables("PivotTable1").PivotFields("Region").CurrentPage = _"Est" |
Supponiamo che tu voglia filtrare la tua tabella pivot in base a più regioni, in questo caso Est e Nord, utilizzerai il seguente codice:
| 1234567891011121314 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1ActiveSheet.PivotTables("PivotTable1").PivotFields("Regione"). _EnableMultiplePageItems = TrueCon ActiveSheet.PivotTables("PivotTable1").PivotFields("Regione").PivotItems("Sud").Visible = False.PivotItems("West").Visible = FalseTermina con |
Aggiornare la tua tabella pivot
Puoi aggiornare la tua tabella pivot in VBA. Utilizzerai il seguente codice per aggiornare una tabella specifica chiamata PivotTable1 in VBA:
| 1 | ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh |
