Creare una tabella pivot in Microsoft Excel è una qualità importante da avere. Queste tabelle sono facili da creare e possono riassumere una grande mole di dati senza aver necessità di utilizzare formule, quindi ottime anche per chi stia iniziando ad utilizzare Excel.
Fondamentali per sommare, contare valori o trovare velocemente informazioni che possano essere utili al management, le pivot risultano un'importante arma per qualsiasi utente di Excel.
In questo articolo utilizzeremo un database per fare alcuni esempi. Non è fondamentale, ma se vuoi fare qualche esperimento è un buon punto di partenza.
Se invece preferisci visualizzare un file con una pivot già completata, dai un'occhiata qui.
Le pivot sono un ottimo punto di partenza per numerose analisi complesse - ad esempio, clicca qui se vuoi scoprire di più su come collegare fra loro diverse tabelle pivot per creare una dashboard dinamica su Excel!
Come preparare i dati per creare una tabella pivot su Excel
I dati ideali per una tabella pivot su Excel sono inseriti all'interno di una tabella. C'è un modo semplice per capire se i tuoi dati siano inseriti all'interno di una tabella o meno, puoi scoprire che cosa succede quando clicchi all'interno dell'intervallo. Se in alto a destra ti appare una nuova voce del menu chiamata Struttura tabella, allora vuol dire che i dati sono inseriti in tabella. Se questa voce non appare, ciò significa che stai lavorando con dati che non sono inseriti in una tabella.
Clicchiamo all'interno dell'intervallo che ci interessa e clicchiamo su Inserisci > Tabella pivot:
A questo punto, se i dati non sono inseriti in tabella, ci apparirà una schermata come questa. La tabella pivot prenderà esclusivamente i dati da A1 a Q7501, offrendoci un recap veloce dei nostri dati.
NB: Il problema principale di questo approccio, utilizzando intervalli anzichè tabelle, è la scalabilità e la manutenzione del nostro processo. Se aggiornassimo l'intervallo di partenza inserendo nuove righe, queste non sarebbero poi incluse nella pivot. Questo approccio rischia di generare errori di ogni tipo, dato che rischiamo di avere pivot non aggiornate e offrire suggerimenti in base a dati errati.
Il suggerimento è quello invece di lavorare con tabelle pivot che nascono da tabelle, anche perchè il procedimento per crearle è decisamente rapido e intuitivo.
Ci basta infatti cliccare all'interno del nostro intervallo e cliccare poi su Inserisci > Tabella. In questo modo, potremo creare una tabella che ha come intestazioni la prima riga per ogni colonna. I vantaggi di utilizzare le tabelle sono molteplici, dato che le nostre formule saranno più semplici da comprendere e la nostra base dati sarà coerente anche nel caso ci siano degli aggiornamenti o sviluppi futuri.
A questo punto, avremo la nostra tabella Excel pronta per l'uso. Possiamo anche modificarne il nome andando su Struttura tabella, che è un menu accessibile cliccando all'interno della tabella stessa. In questo esempio, la tabella si chiamerà TBL_VENDITE.
Come creare una tabella pivot su Excel
Per creare una tabella pivot su Excel basta selezionare una cella qualsiasi all'interno di un foglio di lavoro strutturato, come una tabella o un database.
Ti suggerisco caldamente di creare prima una tabella cliccando sulla cella > CTRL+T (o Inserisci > Tabella) per avere un foglio più pulito. Se i dati aumenteranno, la pivot li includerà nelle statistiche, una volta aggiornata. Viceversa, la pivot considererà esclusivamente i dati che hai inserito in questa fase.
Cliccando all'interno dell'intervallo che ci interessa, vedremo che nella schermata Inserisci troveremo sia Tabella pivot sia Tabelle pivot consigliate. La prima selezione riguarda la tabella pivot classica, dove decidiamo noi in autonomia che cosa inserire nei singoli riquadri. Il secondo caso è una scelta guidata creata da Excel, dove i campi sono già inseriti direttamente all'interno delle singole sezioni - questa può essere una buona soluzione se stai provando a scoprire alcune evidenze sui dati, senza avere però uno schema preciso in testa.
Una volta selezionata una cella parte dell'intervallo, selezioniamo Inserisci > Tabella Pivot (primo elemento sulla sinistra).
Come vedi, in questo caso abbiamo preso le celle del foglio DB_VENDITE da A1 a AA7501. Come dicevamo sopra, bisogna sempre fare attenzione in questi casi perchè se avessimo delle nuove righe con dei dati più recenti, questi non sarebbero poi considerati nel calcolo complessivo fatto con pivot.
Ti consiglio di creare la pivot sempre su un altro foglio rispetto all'origine dei dati, in modo da avere un file più pulito e comprensibile anche per altri utenti o collaboratori. Di default, Excel ti suggerisce proprio questa opzione. Il caso di Aggiungi questi dati al modello di dati è un caso particolare che ti interessa solo se stai lavorando con Power Pivot e devi collegare fra di loro diverse fonti dati, quindi puoi anche ignorarlo.
Di default, la pivot sarà creata su un nuovo foglio di lavoro (Foglio1).
A questo punto, possiamo utilizzare la nostra tabella pivot su Excel. Come vediamo, c'è un piccolo pannello che può aiutarci per la realizzazione delle statistiche che desideriamo - vediamole nel dettaglio.
Quali sono gli elementi della tabella pivot su Excel
La tabella pivot su Excel ha 4 sezioni fondamentali: filtri, colonne, righe e valori. Tutte queste sezioni si popolano a partire dall'intervallo che abbiamo inserito in precedenza - quindi è molto importante avere delle intestazioni "parlanti" nella nostra tabella, che ci indicano chiaramente quali siano i dati inseriti per ogni singola colonna. Un conto è avere "Nome", "Regione", "Film" - un altro è "X", "Y", "ProvaX". Prima di partire con la creazione della pivot, assicurati che la tabella di partenza sia comprensibile. Questo passaggio ti velocizzerà enormemente nella parte successiva di creazione.
Questo piccolo pannello ha diverse visualizzazioni che sono possibili dalla rotellina delle impostazioni che trovi subito sotto la X dell'immagine - tuttavia, la stragrande maggioranza degli utenti Excel non modifica questa visualizzazione.
Vediamo nello specifico che cosa succede nella pivot:
Gli elementi della tabella pivot su Excel: campi da aggiungere al rapporto
Questi campi non sono che le colonne della tabella che vogliamo riassumere con una pivot su Excel. Per inserire un campo, basta spostare il nome del campo in uno dei 4 riquadri sottostanti. Per cancellare il campo, basta selezionarlo e spostarlo fuori dallo spazio. Se creiamo un nuovo campo, questo apparirà sulla destra rispetto alla nostra base dati
Come indicato, i campi partono sempre dalla tabella di partenza e sarà poi nostra cura spostarli nella sezione che più ci interessa. I campi possono sempre essere spostati selezionandoli e tenendo premuto con il tasto sinistro del mouse, anche per definire quale debba andare sotto l'altro.
Quando si crea una pivot, l'ordine è leggermente diverso da quello che vedi sotto ai campi, dato che partiamo prima dalle righe, poi andiamo a vedere i valori, poi le colonne e solo alla fine i filtri. Vediamo perchè.
Gli elementi della tabella pivot su Excel: righe
Le righe sono le righe della tabella pivot che andremo a creare sul nostro Excel. Quindi se indichiamo un campo (una colonna) avremo disposti in verticale tutti gli elementi univoci del campo selezionato. Se hai dimestichezza con Excel saprai che è molto più probabile che i file si sviluppino in verticale (tante righe) anzichè in orizzontale (tante colonne). Per questo motivo, ti consiglio di considerare per prime le righe, nella creazione della tua pivot: quali sono i campi fondamentali per l'analisi che non possono mancare, in prima battuta? In questo caso, Excel riassumerà tutti i valori univoci che trova sulla colonna che hai considerato.
Se selezioni due diversi campi, ci sarà una priorità: quello più in alto è quello più importante. Devi fare attenzione perchè inserire Film e poi Regione vuol dire che stai facendo un'analisi di vendita basata prima sui titoli e poi sulla base territoriale. Se invece inserissi Regione e poi Film, staresti prima valutando le regioni più importanti per il tuo fatturato per poi esploderle per singolo titolo.
Gli elementi della tabella pivot su Excel: valori
La sezione valori comprende i valori che vogliamo inserire all'interno della pivot su Excel. Quindi possiamo selezionare, ad esempio, se ci interessa sommare alcuni valori, contarli o fare una media. La scelta dipende molto dalla tua domanda di business, ma è un campo piuttosto flessibile.
A volte si rischia di fare confusione fra valori e la sezione colonne. La sezione valori ci permette di compiere delle operazioni che saranno restituite sulla destra rispetto alle nostre righe, dividendosi quindi su varie colonne. Se calcoliamo la media, avremo una nuova colonna chiamata media. Se calcoliamo la somma, avremo una nuova colonna chiamata somma.
Il caso delle colonne è diverso. In questo caso, vuol dire che vogliamo vedere come intestazione un valore preciso. Ad esempio, se vogliamo fare un match fra clienti e prodotti, metteremo i prodotti in colonne, non in valori. Questo perchè avremo i clienti sulle righe e, avendo i prodotti sulle colonne, potremo poi inserire la somma in valori - che ci restituirà la somma di fatturato per una certa combinazione fra un prodotto e un cliente specifico.
Gli elementi della tabella pivot su Excel: filtri
I filtri delle tabelle pivot funzionano esattamente come i filtri delle tabelle normal su Excel. Questi filtri ci aiutano a selezionare i dati che ci interessano secondo alcuni parametri da noi definiti, riducendo il campo complessivo dei dati che ci servono.
Bisogna fare attenzione perchè i filtri appaiono sopra rispetto alla pivot, quindi dovremo lasciare qualche riga di spazio per evitare problemi. Se togliamo un filtro, ciò significa che la selezione non sarà più delimitata e staremo considerando l'intero intervallo. Sbagliarsi è facile, quindi bisogna sempre tenere a vista i filtri per essere sicuri di non sbagliare.
Questa parte è molto flessibile dato che possiamo anche selezionare più filtri per uno stesso campo, ad esempio tre clienti specifici, oppure aggiungere diversi filtri, ad esempio per un cliente e una regione definita. Il rischio diventa quello di non bloccare la struttura della pivot, dato che ogni volta che aggiungiamo un filtro, cambieranno i valori sottostanti. Se devi fare statistiche su base settimanale, ti conviene bloccare la struttura con i filtri che ti servono e, piuttosto, creare una seconda pivot piuttosto che intervenire continuamente sulla stessa pivot. Questo perchè avrai la certezza di lavorare con la stessa base dati, aiutando il processo di analisi successivo.
Gli elementi della tabella pivot su Excel: colonne
Le colonne indicano le intestazioni delle colonne della tabella pivot che andremo a creare sul nostro file Excel. Le colonne sono utili se vogliamo fare una tabella pivot che riassuma i dati per righe e per colonne, aggiungendo un'altra dimensione di analisi. Questo è il caso tipico di tabelle finali di recap, dove inseriremo due diverse coordinate per righe e per colonne.
Tuttavia, non è sempre fondamentale utilizzare le colonne. Se stai facendo un'analisi specifica sui prodotti, potrebbe essere sufficiente lavorare facendo dei drilldown con i valori senza aggiungere un'ulteriore dimensione di analisi. Come indicavamo in precedenza, bisogna fare attenzione perchè le colonne sono semplicemente le intestazioni, non generano di per sè nuovi calcoli. Se hai molti dati, ti sconsiglio di inserirli nelle colonne dato che il file risulterebbe praticamente incomprensibile.
Come visualizzare i campi di una tabella pivot su Excel
Se non vedi i campi della tabella pivot nel tuo Excel, basta cliccare all'interno della tabella pivot > Analizza > Mostra > Elenco campi. Se vogliamo vedere esclusivamente i risultati della pivot, possiamo anche nasconderlo ma è molto utile tenerlo a portata di mano.
Questa indicazione è importante dato che l'elenco dei campi potrebbe essere nascosto automaticamente da Excel - in questo modo, potrai accedere e modificare la pivot come preferisci.
Le tabelle pivot aggiungono due nuovi voci sul nostro menu in Excel: vediamoli entrambi nello specifico, dato che torneranno molto utili quando lavoreremo sulle pivot per migliorare la comprensione dei nostri dati.
Il primo menu delle tabelle pivot su Excel è quello chiamato Analisi tabella pivot. Fra i due menu, questo è quello che utilizzerai di più dato che la maggior parte delle funzionalità necessarie per personalizzare le tabelle pivot. Le sezioni di questo menu sono infatti fra le più varie:
- Campo attivo: la parte chiamata Tabella pivot è comoda perchè ci permette di cambiare il nome della pivot e, cliccando all'interno del menu contestuale, potremo anche cambiare le tempistiche di aggiornamento della pivot e visualizzeremo altre opzioni particolari. Potremo anche vedere il campo attivo, ovvero quello che abbiamo selezionato, oltre ad altre operazioni quali il drill-down sui nostri dati
- Gruppo: questa sezione ci indica se abbiamo utilizzato un gruppo o se vogliamo separarlo. Questa opzione è particolarmente comoda se dobbiamo aggregare vari elementi sulla nostra pivot, ad esempio se volessimo vedere Sardegna e Sicilia come isole. Volendo creare un campo chiamato Isole, potremmo raggruppare queste due regioni e visualizzeremmo così il totale complessivo sulla nostra pivot
- Filtra: questa sezione è decisamente interessante. Potremo inserire un filtro dei dati (slicer in inglese), una sequenza temporale (timeline) e verificare le connessioni dei singoli filtri. Questo aspetto è la chiave per collegare le pivot fra di loro, creando vere e proprie dashboard dinamiche!
- Dati: questa sezione permette principalmente di effettuare due operazioni. La prima è di aggiornare la pivot, comprendendo anche i nuovi dati della nostra tabella di partenza. Questa operazione è fondamentale dato che la pivot non si aggiorna in automatico, quando lavoriamo con dei dati su pivot dobbiamo sempre ricordarci di aggiornarla. La seconda è di cambiare l'origine dei dati: in questo caso potremo modificare la nostra sorgente dati, prendendo ad esempio una nuova tabella di riferimento
- Azioni: questa sezione può esserci utile quando vogliamo copiare e incollare la pivot mantenendo tutti i filtri e altri calcoli particolari. Cliccando su Seleziona > Intera tabella pivot, potremo copiarla e incollarla anche altrove sul nostro file Excel. Potremo anche spostarla cliccando direttamente su Sposta tabella pivot
- Calcoli: questa è un'altra sezione particolarmente flessibile e interessante. Nella parte di Campi, elementi e set potremo ad esempio creare dei campi calcolati. I campi calcolati non sono altro che calcoli che non esistono nella tabella di partenza, ma che potrebbero servirci: un esempio tipico è calcolare la differenza fra due colonne in termini percentuali, molto utile se stiamo valutando dati di anni diversi. Nella definizione dei campi calcolati potremo utilizzare i singoli campi come parametri del nostro calcolo, aiutandoci nella parte di definizione finale
- Strumenti: questa sezione è utilizzata principalmente per creare dei grafici a partire dalle tabelle pivot, anche se lo stesso risultato si può ottenere cliccando sulla pivot per poi selezionare Inserisci > Grafico. Attenzione perchè le tabelle pivot non possono essere riassunte con tutti i tipi di grafico presenti su Excel, anche se quelle più diffuse sono disponibili senza particolari problemi
- Mostra: questa sezione è puramente di supporto, dato che ci permette di visualizzare i singoli campi, i pulsanti +/- e le intestazioni dei singoli campi. Potrebbe capitarti di utilizzare queste opzioni solo per motivi di layout, anche se la visualizzazione dei singoli campi è fondamentale in fase di creazione della singola pivot
Il secondo menu dedicato alle tabelle pivot è il menu chiamato Progettazione, utilizzato principalmente per modificare i layout su Excel. Vediamolo più nel dettaglio:
- Layout: in questa sezione possiamo decidere se inserire alcuni valori utili come i subtotali o i totali complessivi. Questi possono essere particolarmente comodi quando stiamo realizzando una pivot ampia con numerose sottosezioni. Il layout del singolo report può essere modificato da qui, scegliendo se preferiamo un layout compatto, struttura o a tabella. Questi tre casi si differenziano perchè il primo è quello nel quale le righe della pivot sono inserite una sotto l'altra, mentre gli altri due hanno delle nuove colonne per ognuno dei risultati. Se il layout della tua pivot non ti convince, ti consiglio di dare un'occhiata qui dato che puoi anche scegliere se ripetere le etichette degli elementi: questa impostazione crea tabelle pivot che sembrano proprio delle tabelle. Infine, è sempre da qui che puoi gestire le righe vuote, ad esempio puoi inserirle fra le diverse sezioni per una maggiore leggibilità
- Opzioni stile tabella pivot: in questa sezione puoi selezionare alcune opzioni ulteriori relative allo stile della tabella pivot. Da qui puoi definire se vuoi visualizzare le intestazioni di righe e colonne, oltre a decidere se vuoi vedere i dati con dei colori alternati. Questa è la visualizzazione tipica anche delle tabelle standard, che spesso può dare fastidio - ma è tutta una questione di gusti, io in genere non utilizzo l'alternanza perchè mi pare disturbi l'analisi ad esempio
- Stili veloci tabella pivot: in questa sezione sono presenti numerosi stili standard per la visualizzazione della pivot. Ad esempio puoi scegliere se le celle debbano avere un certo colore di sfondo, dei bordi spessi e così via. Puoi anche creare il tuo stile personalizzato per le pivot, in modo da riutilizzarlo sugli altri file di lavoro
Come creare una tabella pivot su Excel: un esempio pratico per te
Utilizziamo il database iniziale, allegato alla guida, per fare qualche prova e vedere se siamo in grado di creare una tabella pivot su Excel. Non ti spaventare dato che sono dei semplici passaggi manuali, che non richiedono una competenza particolarmente elevata nell'utilizzo di Excel.
Puoi seguire la guida passo passo scaricando il file iniziale da qui - altrimenti puoi sfruttare anche uno dei tuoi file di lavoro, dato che la struttura di ragionamento rimarrà pressochè la stessa.
La domanda è: "Vogliamo scoprire quale sia il margine per ogni singolo film venduto in Lombardia nei nostri anni di attività. Il totale deve essere diviso sia per anno sia per singolo film".
La risposta a questa domanda sarebbe possibile anche senza le pivot, creando una struttura ad hoc con formule quali FILTRO, UNICI e SOMMA.PIÙ.SE. In questo caso però utilizziamo le pivot e vediamo come sarà il procedimento, andando passo passo.
RIGHE DELLA TABELLA PIVOT
Prima di tutto, pensiamo ai dati da inserire nelle righe. In questo caso, quello che ci interessa spostare sono i film inseriti nella colonna "FILM". Possiamo quindi cliccare sulla nostra pivot di partenza e spostare il campo FILM nella sezione Righe.
Al momento la nostra pivot non è particolarmente interessante, dato che abbiamo un semplice elenco in ordine alfabetico dei film venduti. Quando inserisci un campo nelle righe, puoi o tenere premuto cliccando sul campo e trascinarlo nelle righe - oppure puoi semplicemente cliccare sulla spunta che trovi sulla sinistra, dove apparirà una V verde.
Se un campo non ti interessa più, puoi selezionarlo e, tenendo premuto, spostarlo fuori dalla pivot. Il campo sarà cancellato dalla pivot e non sarà più considerato nella nostra analisi.
VALORI DELLA TABELLA PIVOT
Terminata la parte delle righe, torniamo alla domanda di business che si focalizzava sul margine. Spostiamo quindi MARGINE nella sezione Valori, dato che vogliamo eseguire una sommatoria di questi valori.
La pivot è ora decisamente più interessante. Abbiamo infatti una suddivisione di ogni film e del rispettivo margine per ognuno, che tiene conto di tutte le transazioni della nostra tabella di partenza. Cliccando su Somma di MARGINE, che trovi nella sezione Valori, puoi anche modificare questo calcolo e scegliere se visualizzare altri dati statistici quali la media o il conteggio.
Il caso del conteggio è curioso: se abbiamo dei testi, sarà questa l'opzione di default. Qui abbiamo dei numeri e quindi Excel, correttamente, somma i valori e ci restituisce il totale del margine che desideriamo.
Per un'analisi statistica rapida, potremmo anche fermarci qui. Ti segnalo che cliccando su Etichette di riga, dove trovi la freccia verso il basso, potresti ordinare questi dati in diversi modi. Un caso tipico è quello di ordinare i film a seconda del loro margine, anzichè in ordine alfabetico.
COLONNE DELLA TABELLA PIVOT
Abbiamo scelto una domanda più complessa, quindi dobbiamo inserire DATA_VENDITA nella sezione delle Colonne per studiare più nel dettaglio l'andamento delle nostre vendite nei diversi anni di analisi. Ricordiamoci che DATA_VENDITA è in un formato data, quindi ci permette alcune analisi particolarmente interessanti.
La nostra pivot è ora ancora più ricca di risultati e dati per la nostra analisi. Excel riconosce correttamente le date e quindi ci esplode il campo DATA_VENDITA per inserire anche un totale per Anni e per Trimestri. Questo passaggio automatico ci evita numerosi passaggi di calcolo più complessi, ma funziona correttamente solo se i dati, come dicevamo, sono correttamente formattati in formato data.
Cliccando sui singoli valori, ad esempio su 2023, potremo vedere il totale del 2023 e confrontarlo con quello degli anni successivi - continuando, nel contempo, a visualizzare anche i singoli trimestri. Questo tipo di ginnastica mentale è fondamentale se stiamo esplorando i dati per la prima volta e vogliamo capirci di più, anche per meglio definire i campi di analisi successiva.
FILTRI DELLA TABELLA PIVOT
La nostra domanda di business aveva anche una dimensione locale, che non dobbiamo dimenticare. Inseriamo quindi, nella sezione Filtri, il campo REGIONE. Potremo così selezionare unicamente i film venduti in Lombardia e concludere la nostra analisi.
Inizialmente, non c'è nessuna differenza. La nostra tabella pivot è esattamente come quella precedente, salvo che visualizziamo REGIONE nella sezione filtri e vediamo che, sopra alla nostra tabella pivot, appare proprio il nome del campo che abbiamo selezionato come filtro.
A questo punto, non ci resta che cliccare proprio in questa sezione sopra alla nostra pivot attuale. Da notare che lo stesso risultato si otterrebbe anche cliccando su REGIONE all'interno della sezione filtri: abbiamo un nuovo menu dove potremo selezionare le singole regioni e, se lo vogliamo, selezionarne più di una cliccando su Seleziona più elementi. Scegliamo la Lombardia e clicchiamo su OK.
A questo punto, abbiamo terminato la nostra pivot. Possiamo anche rinominare le intestazioni, dato che Etichetta di riga e Etichetta di colonna non sono particolarmente parlanti nè professionali.
La nostra pivot è finalmente completata!
Come aggiornare una tabella pivot su Excel
Aggiornare una tabella pivot su Excel è piuttosto semplice, ma dobbiamo ricordarci di questa operazione fondamentale altrimenti abbiamo sempre il problema dei dati inaffidabili.
Il caso più semplice è cliccare sulla pivot > Analisi tabella pivot > Aggiorna. Se però abbiamo numerose pivot, un trucco è cliccare invece su Aggiorna > Aggiorna tutti che aggiornerà tutte le pivot del nostro file.
Un'alternativa è quella di utilizzare le opzioni avanzate della tabelle pivot, disponibili quando clicchiamo sulla prima sezione di Analisi tabella pivot e andiamo poi su Opzioni.
Qui abbiamo numerose opzioni aggiuntive per la visualizzazione e l'utilizzo delle nostre tabelle pivot. Cliccando su Dati, possiamo selezionare Aggiorna dati all'apertura del file per essere sicuri che i dati siano sempre aggiornati.
Conclusione
Spero che la guida ti sia stata utile! Il tema è enorme e probabilmente aggiornerò questa guida con altre informazioni più specifiche per le ultime edizioni di Excel. Se ormai le pivot non hanno più segreti per te, scopri come collegare le tabelle pivot su Excel per creare una dashboard dinamica cliccando su questo link!
Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel.
Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, Sicuritalia e SISAL, oltre a PMI e startup di diverso genere.
Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.
Vuoi saperne di più? Prenota una call gratuita di 15′!
