Come giostrare con le tabelle pivot di Excel

Nel post precedente abbiamo visto come creare tabelle pivot di Excel collegate a una base di dati e i relativi grafici pivot. In fondo nulla di così difficile, vero?

Cosa possiamo fare con le tabelle pivot di Excel?

Nel post precedente abbiamo visto che gli usi principali delle tabelle pivot riguardano:

Analisi: le tabelle pivot di Excel permettono di eseguire analisi relativamente semplici su valori numerici, ma non solo, e possono filtrare, ordinare, raggruppare secondo i valori di altri campi/parametri. Questo permette di eseguire calcoli e analisi soddisfacenti nella maggior parte degli ambiti aziendali/lavorativi: dalla produzione alla contabilità, dal controllo agli acquisti, eccetera.
Controllo dei dati: per loro stessa natura le pivot permettono di eseguire controlli sulla base dati a cui è agganciata.
Report veloci e dinamici: le tabelle pivot sono comode anche per realizzare report, perché si creano e strutturano velocemente, perché possono essere configurate agilmente come layout e aspetto, ma soprattutto per i grafici pivot. Con pochi gesti si può creare un grafico interattivo, altamente configurabile, agganciato alla pivot.
Dashboard: i pannelli di controllo sono pagine composte da tabelle e grafici, meglio se interattivi, che permettono di seguire l’andamento di indicatori (esempio i kpi per la gestione aziendale o della produzione). Possono essere realizzati con strumenti potenti e moderni, come Power View, o con strumenti più datati, ma non meno efficaci, tra cui le pivot.

Come si crea una tabella pivot?

Partendo da una base dati affidabile, più comodo se si crea in questo modo:

seleziona una cella o tutta la tabella dei dati;
vai al menù “inserisci” della barra principale;
se hai una versione di Excel recente ti consiglio di usare il comando “Tabelle pivot Consigliate” e scegliere uno dei layout proposti;
altrimenti clicca su “Tabella Pivot”; si aprirà la finestra “crea tabella pivot” impostata per essere creata in un nuovo foglio di lavoro;
clicca ok per aprire una nuova pagina con una tabella pivot vuota da configurare; sulla destra trovi la maschera di configurazione: in alto la lista dei campi dei dati e in basso quattro riquadri: valori da calcolare, filtri da applicare, colonne e righe;
scegli uno o più campi/parametri da calcolare e trascinali nel riquadro in basso a destra; il valore verrà o sommato o conteggiato, se non è quello che vuoi clicca sul triangolo a fianco dell’etichetta, seleziona dal menu a tendina “impostazioni campo valore” per aprire la relativa finestra dove impostare il calcolo che ti serve e le modalità di visualizzazione dei valori;
scegli uno o più campi per le righe e trascinalo nel riquadro in basso a sinistra per far comparire l’elenco dei valori del campo che diventeranno le categorie per cui saranno calcolati i valori definiti;
se necessario scegli in modo analogo uno o più campi per le colonne e trascinalo nel riquadro in alto a destra;
infine scegli i campi utili per impostare i filtri che ti interessano e trascinali nel riquadro in alto a sinistra; ora hai impostato la tua pivot.

Fin qui nulla di straordinario, vero?

Se vuoi vedere come si realizza una pivot e un grafico in pochi semplici passaggi ti invito a vedere questo video del tutorial dedicato alle pivot:

In questo video invece puoi vedere come si impostano le tabelle pivot di Excel:

In questo post facciamo un passo oltre, parliamo di:

Come usare le tabelle e i grafici pivot in pratica.

Pronto?

Ora che sai come farle è naturale chiedersi: quali sono i limiti? come è meglio importarle? quanti campi calcolati puoi metterci? quanti parametri impostare per righe e colonne? e i filtri?

Come strutturiamo le pivot?

In modo da ottenere quello che stiamo cercando, è naturale. Hai bisogno di sommare i costi di produzione e raggrupparli per centro di costo? Fai esattamente così, pensi a cosa stai cercando e poi lo metti in pratica con una pivot, letteralmente, è la migliore linea di condotta. Attenzione, però, a non farci prendere la mano. Per esempio, stiamo valutando il fatturato di prodotti/servizi per linea/tipologia e d’istinto cerchiamo di approfondire, valutando la distribuzione del fatturato anche se per settore e tipologia di cliente, ma poi perché non dare un’occhiata all’andamento nel tempo? Tipo per trimestre. E siamo già oltre, a un livello di complessità che non rende certamente la tabella “leggibile”.

Quello di cui ci si dimentica con le tabelle pivot di Excel è che sono tabelle di dati, comode e pratiche, ma sempre tabelle che devono essere comprensibili e utilizzabili. Più le tabelle sono complesse, più è difficile interpretarle. Sovraccaricarle di parametri, campi calcolati e compagnia danzante non è una buona idea.

Quanti valori posso calcolare in una tabella pivot?

Il numero ideale dei valori da calcolare è uno, solo uno, come se dovessi usarla in un report o come base per un grafico. Questo vale specialmente se imposti parametri sia di riga che di colonna. Nella pratica quotidiana, al di fuori dei report, ti sconsiglio di inserire più di tre valori da calcolare. Questo sempre per una questione di leggibilità e comprensione.

Per esempio, un’impostazione classica è la somma o il conteggio del parametro che ci interessa e lo stesso parametro visualizzato come “% del totale”, es: fatturato distribuito per mercato con a fianco la relativa %, evidenziata con una bella formattazione condizionale, “Barre dei dati”. Oppure la media di un parametro, la sua deviazione standard e il suo conteggio, per esempio per una preanalisi delle dimensioni di un prodotto finito. Se imposti parametri sia di riga che di colonna ti consiglio di limitarti a solo due parametri calcolati. Se non imposti parametri di riga e colonna allora puoi aggiungere più parametri calcolati (es: varianza, media ponderata, eccetera).

Con un minimo di esperienza imparerai a valutare a colpo d’occhio la complessità di una tabella pivot. Ti invito a fare delle prove e a chiedere ad amici o colleghi cosa ne pensano.

Quanti parametri posso inserire nelle righe e nelle colonne?

Come per i valori calcolati, anche il numero dei parametri di riga e di colonna consigliati è limitata dalla leggibilità della tabella. E non solo se la utilizziamo in un report. L’ideale sarebbe utilizzare non più di un paio di parametri o di riga o di colonna, nella pratica il mio consiglio è non usare più di tre parametri di riga.

Per esempio se vuoi analizzare il fatturato per articolo, oltre al campo “cliente”, puoi aggiungere ai parametri di riga anche la linea di prodotto e il mercato, il risultato è ancora comprensibile, soprattutto se aggiungi un paio di righe di spiegazione a fianco della tabella per te o per chiunque debba consultarla in futuro.

E se volessi vederne l’andamento? Forse è meglio scomporre il problema in più tabelle pivot. Fai una tabella pivot per visualizzare il fatturato per il periodo in esame per cliente, linea e mercato, ordinato in modo decrescente e una o più tabelle per visualizzare l’andamento del fatturato per i parametri sopracitati, uno alla volta, massimo due. Ricorda che molto non è meglio di poco, fai sempre finta di dover preparare un report, quindi le parole chiave sono chiaro e semplice.

Come usare i filtri al meglio?

Nella fase iniziale di un’analisi anch’io “carico” la pivot di tutti i filtri possibili, ma una volta che mi sono fatto un’idea dei parametri principali, alleggerisco la tabella dai filtri che non servono, soprattutto perché distraggono. Ti consiglio di fare sempre in questo modo, soprattutto quando usiamo la pivot in un report o in un pannello di controllo, in quel caso è tassativo usare solo i filtri strettamente necessari.

Inoltre presta la massima attenzione ai valori impostati nel filtro, in primo luogo perché tra gli errori più frequenti nella gestione delle pivot ci sono quelli relativi all’impostazioni dei filtri. Se riesci cerca di impostare un solo valore per filtro. In secondo luogo, per quanto efficaci siano i filtri nel focalizzare l’attenzione su una porzione della base dati, è importante non perdere di vista l’obiettivo della tabella e la rappresentatività della porzione dei dati prescelta.

Titoli e note delle tabelle pivot di Excel

Due parole sul contorno di una pivot: una volta impostata la tabella deve ricevere un titolo che spiega cosa mostra (es: andamento annuale vendite), se necessario un sottotitolo che esplica i particolari (es: periodo, fonte, filtri), inoltre se i titoli dei campi visualizzati non sono chiari devono essere riscritti, selezionali uno alla volta e digita il titolo alternativo. Infine è spesso opportuno aggiungere sopra o adiacente la tabella una nota con l’interpretazione dei dati mostrati o che evidenzia particolarità o anomalie.

Moltiplica le pivot

Una volta che hai impostato la prima pivot, copia la tabella ogni volta che serve. Non scherzo. Devi fare un’analisi? Prepara la prima pivot, poi fai 3-4 copie, quelle che servono per esaminare i diversi parametri che ti interessano, per osservare come variano, con che ordine, quali sono i valori significativi, come variano nel tempo, eccetera. Ogni qualvolta trovi valori/distribuzioni interessanti titola, aggiungi note, poi copia la tabella e prosegui. Ogni qualvolta la pivot diventa complessa o poco comprensibile, copiala e smontala in parti, eccetera. Non farti scrupoli, con i nuovi formati le pivot pesano poco sulle dimensioni del file.

Ordina i valori

O sei tu che ordini la pivot o lo fa lei al posto tuo. È comodo, ma in diverse situazioni non è una buona idea. Il consiglio è di verificare sempre l’ordinamento della pivot, una volta che l’hai strutturata. Excel tende nella maggior parte dei casi a ordinare in ordine crescente secondo il primo parametro di riga, se presente, ma spesso avrai bisogno di modificarlo. Per esempio per conoscere i clienti più importanti dovrai in ordine di importanza, dovrai ordinare in modo descrescente la somma del fatturato di ognuno. Chiaro, no? Ah, per ordinare basta selezionare il campo/parametro nella tabella pivot, o una cella/valore e poi usare il comando “ordina” sulla barra principale o dal menu.

Raggruppa i valori

L’obiettivo è diminuire i valori di un parametro di riga o colonna, raggruppando valori numerici per intervalli definiti, le date secondo intervalli maggiori (settimane, mesi, trimestri, anni, ecc), stringhe per secondo una selezione personalizzata e altro ancora. Seleziona una cella del parametro che vuoi raggruppare e clicca sulla voce “Raggruppa” del menu per aprire la finestra relativa (oppure vai sulla barra principale, menu “strumenti tabella pivot” e “raggruppa”). Raggruppa è uno strumento molto comodo quando hai bisogno di raggruppare il valore calcolato in modo diverso o più semplice o utile di quello che i campi della base dati permettono. Per esempio hai una data e vuoi visualizzare un andamento mensile o trimestrale, puoi aggiungere un campo calcolato nel foglio dati, o più semplicemente puoi raggruppare.

In questo video del tutorial puoi vedere quanto stiamo dicendo:

Come fare i controlli sui dati con le pivot

Le tabelle pivot di Excel sono delle sintesi della base dati, cioè strumenti che mostrano i dati in modo più compatto e facile da consultare, per questo sono comode per verificarne l’affidabilità e le caratteristiche. Prima di farlo devi comprendere natura e utilità dei campi da verificare e devi avere almeno una fonte diversa e affidabile a cui appoggiarti nei controlli. Come abbiamo già detto i controlli dipendono dal tipo di dato, ma di solito la completezza (che ci siano tutti i dati di cui abbiamo bisogno, non di meno, non di più) si verifica tramite conteggio dei record o somma di un campo numerico (es: fatturato), quindi basta impostare una pivot per fare la somma totale del parametro sullo stesso intervallo del valore di riferimento esterno.

La correttezza (che i dati siano corretti, senza errori di nessun tipo) si verifica impostando una pivot con una chiave primaria come parametro di riga (es: codice cliente), ordinando in modi diversi, filtrando per focalizzare su intervalli diversi della base dati, alla ricerca di errori nei campi principali (es: errori di valore, formato, segno, grandezza, ecc). Mentre la coerenza (che i dati siano coerenti tra di loro e con riferimenti affidabili) richiede almeno di impostare la pivot per visualizzare la distribuzione di uno o più parametri per individuare i valori fuori distribuzione.

Quando devo lavorare su una base dati, per prima cosa cerco una fonte alternativa. Se non c’è una fonte alternativa? Cerco un’estrazione diversa però verificata nel tempo come affidabilità (es: il report periodico della contabilità). Poi individuo nella base dati le chiavi primarie e secondarie (es: cod. cliente, cod articolo, ecc), individuo i valori numerici di riferimento, i campi secondari e i derivati e quali sono ridondanti. Scelgo i campi da esaminare, imposto i filtri, esamino un attimo la lista dei valori, in particolare testa e coda. Se necessario imposto nel foglio dati un campo calcolato per evidenziare/marcare i valori anomali o gli errori evidenti, poi creo una pivot in una pagina a parte e inizio il lavoro di controllo. Quando trovo problemi copio in un’altra pagina le chiavi dei record relativi per decidere se rimuoverli o correggerli o approfondire le cause degli errori.

Come fare un’analisi con le tabelle pivot di Excel?

Come ho accennato in questo post, le pivot sono un buon strumento di analisi nella maggior parte degli ambiti aziendali: controllo, logistica, produzione, eccetera, ma sono solo uno degli strumenti a disposizione. Ricordo anche che le tabelle pivot possono essere agganciate a basi di dati esterne e nelle ultime versioni a più di una base dati. Ma per adesso rimaniamo con la nostra base dati locale, per esempio estratta con una query dal server.

Le analisi possibili possono essere le più diverse a seconda di quello che si cerca e della base dati, ma in generale un’analisi mira a evidenziare informazioni contenute nella base dati allo scopo di prenderne coscienza e quindi prendere decisioni conseguenti. Con le pivot, l’analisi segue di solito i seguenti passi:

Prendere familiarità con i dati
Se non lo hai già fatto, la prima cosa è prendere familiarità con la base dei dati, cioè individuare i campi da esaminare/utilizzare, le chiavi primarie, quali sono i valori o gli intervalli dei valori dei campi da esaminare (es: da 0 a 100mila, si/no, da 0 a 1, ecc).
Creare la pivot e caricare i campi utili
Con campi utili intendo i campi individuati in precedenza. Per iniziare imposta un campo calcolato, uno solo, quello principale, un parametro di riga e il resto caricalo come filtri. Quando ti serviranno saranno a portata di mano.
Analizza il dato
Questo significa di solito calcolare il valore che ci interessa, valutarne la distribuzione secondo i 2–3 parametri principali, studiarne l’andamento nel tempo, e definire le classi più importanti. Che nella pratica significa moltiplicare le pivot per visualizzare le distribuzione e gli andamenti, ordinare i dati nel modo più efficace, aggiungere 1–2 campi calcolati per visualizzare percentuali, unità di misura diverse, deviazioni standard o altro, escludere eventuali valori di disturbo e creare i grafici relativi. Grafici? Sì, grafici. Perché la verifica visiva è molto spesso più efficace della decodifica di tabelle più o meno grandi.
Approfondire l’analisi (se necessario)
Non dovrebbe essere necessario, non nella pratica quotidiana. A meno che non siamo sicuri di quello che stiamo cercando, o che dobbiamo indagare su anomalie nei dati, o cerchiamo di comprendere le cause di risultati o andamenti. L’approfondimento in sé comporta un esame più profondo e attento dei valori e delle distribuzioni, usando i filtri per esaminare le porzioni dei dati significative o che presentano appunto anomalie, e se necessario una valutazione della correlazione tra variabili. Approfondiremo l’argomento in un post apposito.
Trasferire il risultato in un report
Non sempre è necessario, ma se dobbiamo condividere con altri i risultati della nostra analisi allora il report è necessario.

Come visualizzare gli andamenti nel tempo?

Gli andamenti sono utili e onnipresenti nella gestione aziendale, contabilità, produzione, logistica. Quando imposti una tabella pivot per mostrare l’andamento di uno o più parametri o valori nel tempo, usi una data per dare il “passo” della visualizzazione (mese, anno, ecc), di solito come parametro di riga, di solito partendo da una data raggruppata come ti è più utile. Il tipo di grafico per un andamento è di solito un grafico a linee, mostra in modo più immediato l’andamento e spesso anche le sue componenti (trend, stagionalità, ecc).

Le pivot degli andamenti devono sempre rispettare una regola: o visualizzano un valore calcolato nel tempo (es: giacenza a magazzino) o mostrano due andamenti a confronto (es: l’andamento delle vendite di quest’anno e dell’anno precedente). Non tre o più, due solo due, perché un andamento fornisce molte più informazioni della visualizzazione di un valore singolo e quando dobbiamo confrontare più di due andamenti a confronto lo sforzo diventa significativo come la possibilità di perdere parte del messaggio.

Come strutturare le frequenze?

Per mostrare la frequenza assoluta e percentuale di un valore/popolazione con una pivot, basta impostare il valore calcolato come conteggio e lo stesso valore come % del totale, poi impostare lo stesso campo come parametro di riga, se necessario ordinarlo in modo crescente e raggrupparlo in automatico o per l’intervallo che si desidera. La pivot è di solito accompagnata da un grafico pivot che meglio trasmette la distribuzione delle frequenze. Una frequenza è di solito molto utile in caso di analisi di una popolazione.

In questo video del tutorial puoi vedere l’utilizzo pratico delle pivot per ottenere distribuzioni, andamenti, frequenze e cosi via:

Potremmo continuare ancora a lungo, ma ci siamo dilungati molto. Per stasera ci fermiamo qui. Scriverò un terzo post sull’argomento. E forse varrebbe la pena preparare un quaderno sull’argomento. Cosa ne pensi?

A proposito, se non l’hai già fatto ti invito a registrarti a Excel Professionale per scaricare il minicorso “Usa Excel come un professionista”. Funziona. E anche gli altri contenuti gratuiti, macro, foglio e così via.

Grazie per avermi seguito fin qui. Ci vediamo al prossimo post.

A presto ;D

PS: puoi trovare il post precedente qui e quello successivo qui.

Questi post sono stati integrati e ampliati in questa Guida alle tabelle pivot di Excel. Ti invito a leggerla.

E ti invito anche a vedere il tutorial che ho preparato per mostrare gli aspetti pratici di creazione e gestione delle pivot.

PSS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito.
Grazie