Guida alle tabelle pivot

Questa è una guida introduttiva alle tabelle pivot, vedremo insieme quanto possono essere potenti, utili e comode. Dietro l’apparenza le pivot sono semplici da creare e usare, ma è difficile padroneggiarle al meglio. Voglio mostrarti il modo più efficace per iniziare a usarle e quello che devi sapere per usarle con soddisfazione.

Cosa sono le tabelle pivot?

Sono tabelle dinamiche e interattive, veloci da realizzare e da gestire, con le quali è possibile organizzare, analizzare, filtrare e comparare grandi quantità di dati. Esse richiedono una base di dati da analizzare (ma nelle ultime versioni di Excel possono attingere da più basi dati contemporaneamente), locale o remota, e possono essere equipaggiate di grafici interattivi, detti grafici pivot.

Perché si chiamano pivot?

Perché possono scambiare velocemente righe e colonne, attività un tempo complessa da realizzare. Il primo programma informatico a introdurre le tabelle pivot fu Lotus, in Excel si sono evolute fino a diventare straordinarie, ma oggi sono molti gli applicativi che supportano funzionalità equivalenti.

Perché imparare le tabelle pivot?

Consiglio sempre ai principianti in possesso delle competenze minime, di imparare a usare le tabelle pivot, anche se sembra un argomento complesso. In realtà è un argomento che si impara in fretta se spiegato nel modo giusto. E poi si vola. Perché le tabelle pivot ti fanno risparmiare ore di tempo nella maggior parte delle tue attività su Excel. E quando dico ore intendo decine di ore l’anno e più.

Cosa possiamo fare con una tabella pivot?

Con una tabella pivot si possono fare davvero cose notevoli:

1. Analisi

Le tabelle pivot 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.

2. Controllo

Per loro stessa natura le pivot permettono di eseguire controlli diretti e indiretti sulla base dati a cui sono agganciate, come per esempio individuare errori, valori anomali e fuori range, valutare completezza e coerenza della base dati, individuarne le lacune e altro ancora. In caso di affidabilità incerta o messa in discussione di una base dati, si possono usare per eseguire le verifiche necessarie.

3. Report dinamici

Le tabelle pivot sono ottimi strumenti anche per realizzare report, perché si creano e strutturano velocemente, perché possono essere configurate agilmente, perché sono dinamiche e per i grafici pivot. Con pochi gesti si può creare un grafico interattivo, altamente configurabile, agganciato alla tabella.

4. 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 BI o Power View, o con strumenti più datati, ma non meno efficaci, tra cui le tabelle pivot.

Premesse alla creazione delle tabelle pivot

Prima di spiegare come si creano le pivot, alcune necessarie premesse:

1. le tabelle pivot richiedono almeno una base di dati standard su cui lavorare, che può essere interna al foglio di lavoro o una fonte esterna (es: server sql aziendale);
2. si presuppone che la base dati a cui agganciamo la tabella pivot sia affidabile e di buona qualità e quindi non richiede controlli e interventi; in caso contrario, come sempre prima di avviare qualsiasi attività di analisi o elaborazione, è necessario verificare l’affidabilità dei dati;
3. le seguenti spiegazioni presuppongono che la base dati sia unica e presente sul foglio Excel, ma in modo analogo è possibile collegare le tabelle pivot a un’ampia varietà di fonti esterne, dal database locale al server Sql, ed è possibile collegare le tabelle pivot a più fonti dati contemporaneamente; questi aspetti saranno trattati in un manuale più avanzato di questa guida introduttiva alle pivot;
4. affinché la tabella pivot lavori correttamente, la base dati deve essere standard, cioè deve essere strutturata per campi e record, una tabella in un cui ogni riga è una registrazione (record), con almeno una chiave primaria univoca, e ogni colonna è un parametro/variabile (campo), con la sua intestazione univoca, come questa:

Inoltre quando la base dati è interna al foglio di lavoro, quindi realizzata tramite caricamento/trasferimento dei dati da fonte esterna, o inserimento semiautomatico o manuale dei dati, allora è comodo e opportuno trasformarla in una “tabella Excel”.

Questo perché una tabella di Excel possiede molte caratteristiche utili e comode, tra cui quella di essere uno strumento dinamico, cioè capace di adattarsi alle modifiche successive (es: aggiunta o rimozione di record). Come conseguenza quando collegheremo la tabella pivot a una tabella Excel, la prima si aggiornerà in automatico a ogni variazione della tabella dati.

Come trasformo una tabella, un intervallo di dati, un’area di celle piena di dati, in una tabella di excel?

In questo modo:

1. seleziona una cella della tabella;
2. premi Ctrl+T (il tasto control insieme al tasto T), per far comparire la finestra di dialogo crea tabella, con il range della tabella già preimpostato;
3. clicca su OK per confermare il comando di creazione della tabella (o in alternativa premi Tab 2 volte per selezionare il pulsante e premi Invio).

Se preferisci puoi creare la tabella in modo analogo dalla barra multifunzione, scheda “Inserisci”, cliccando sul comando “Tabella”.

Il risultato è una tabella formattata con un layout colorato come questa:

Oltre a essere dinamica una tabella Excel offre numerosi vantaggi, tra cui: formattare istantaneamente la tabella con i filtri impostati e i titoli fissati, la possibilità di rimuovere facilmente i duplicati, creare facilmente campi/colonne calcolate, riorganizzare velocemente i campi, aggiungere facilmente nuove righe e colonne e altro ancora. Per questo consiglio di usare sempre tabelle Excel (naturalmente ci sono alcune eccezioni che non spiegherò in questa guida).

Come si crea tabella pivot e grafico in pochi semplici passaggi

Per creare subito la tua tabella pivot segui questi semplici passi:

1. seleziona una cella della tabella dei dati
   
2. clicca sul comando “Tabelle pivot consigliate” della scheda “inserisci” della barra multifunzione, oppure premi Alt+C+B; si aprirà la finestra di dialogo che ti offrirà tabelle già strutturate, elaborate dai dati d’origine, tra cui puoi scegliere quella che più si avvicina alla tabella che avevi in mente;
   
3. seleziona il migliore e clicca su OK, per creare la tabella pivot in una nuova pagina.

Ora hai la tua tabella pivot e puoi usarla per i tuoi obiettivi.

Se vuoi vedere come si possono creare pivot in 3 semplici passaggi ti invito a vedere questo video del tutorial sulle tabelle pivot:

E quando hai bisogno di un grafico per visualizzare i dati in modo più efficace?

Il modo più veloce per creare un grafico pivot è questo:

1. seleziona una cella della tabella pivot,
2. e premi F11.

Il risultato è un grafico in una nuova pagina (nominata Grafico1, Grafico2, ecc) e di solito è un istogramma, ma dipende dai dati della tabella. Il grafico viene deciso da Excel a seconda delle caratteristiche della tabella e dei dati. Naturalmente bisogna revisionare e impostare il grafico come vuoi tu, dargli un titolo, una legenda, un layout adatto e via dicendo, ma non ne parleremo in questa guida introduttiva dedicata alle tabelle pivot.

Se preferisci poter scegliere fin da subito, il metodo per creare un grafico è il seguente:

1. seleziona una cella della tabella pivot,
2. clicca sul comando “grafico pivot”, scheda “Analizza” della barra multifunzione, per aprire la finestra “Inserisci grafico”; oppure nello stesso modo puoi usare il comando “Grafici consigliati” della scheda “Inserisci”;
3. seleziona il grafico che preferisci (es: a linee) e premi OK.

Il risultato è la comparsa del grafico che hai scelto, di solito nella stessa pagina della tabella pivot. Anche in questo caso devi comunque aggiungere al grafico titolo e personalizzazioni.

Facile vero. Per approfondimenti sui grafici ti invito a guardare il tutorial sulle pivot e in particolare il questo video:

Ora vediamo il metodo di creazione non assistito.

Come si crea una tabella pivot?

Ora vediamo il metodo tradizionale di creazione delle tabelle pivot, non assistito:

1. clicca una cella della tabella pivot, o selezionala tutta premendo Ctrl+Maiusc+Spazio;

   

2. clicca sul comando “Tabelle pivot” della scheda “inserisci” della barra multifunzione, oppure premi Alt+C+T+A;

   si aprirà la finestra “crea tabella pivot” linkata ai dati che hai selezionato e preimpostata per creare una tabella pivot “vuota” in una nuova pagina;
   

3. se necessario modifica le impostazioni della finestra, per esempio puoi impostare l’origine dei dati,

   anche esterna, anche a fonti multiple, puoi anche usare modelli di dati predefiniti e infine puoi decidere dove collocare la tabella pivot nel foglio di lavoro;

4. clicca su OK per creare la tabella pivot non impostata e da configurare,

   di solito in una nuova pagina; sulla destra dello schermo dovresti trovare la maschera di configurazione “campi tabella pivot”; in alto vedi la lista dei campi dei dati e in basso quattro riquadri: valori da calcolare, filtri da applicare, colonne e righe, che corrispondono alle quattro aree di una tabella pivot;
   

5. scegli il campo da assegnare al campo “Valori”,

   cioè da elaborare, e trascinalo nel riquadro in basso a destra “valori da calcolare”; il valore verrà o sommato o conteggiato a seconda del tipo di dato, ma puoi cambiare il calcolo cliccando con il pulsante destro su un valore calcolato per aprire il menu contestuale, cliccare su “Riepiloga valori per” e scegliere una delle opzioni disponibili; puoi calcolare più campi diversi, o lo stesso campo visualizzandolo in modo diverso (es: come valore e % del totale); per modificare la visualizzazione clicca con il pulsante destro del mouse su un valore calcolato per aprire il menu contestuale, clicca su “Mostra valori come” e scegli una delle opzioni disponibili;
   

6. se somma o conteggio non è l’elaborazione che vuoi, cambia il tipo di calcolo

   clicca sul triangolo a fianco dell’etichetta del campo calcolato, 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;
   

7. scegli uno o più campi da assegnare alle righe

   e trascinalo nel riquadro “Righe” in basso a sinistra nella finestra di configurazione per far comparire nella tabella l’elenco dei valori del campo che diventeranno le categorie per cui saranno calcolati i valori;
   

8. se necessario scegli uno o più campi da assegnare alle colonne

   e trascinalo nel riquadro “Colonne” della finestra di configurazione in alto a destra; questo farà comparire nella tabella l’elenco dei valori del campo assegnato
   

9. se necessario ordina i valori di uno o più dei campi riga o colonna che hai aggiunto alla tabella

   per rendere più leggibile la tabella; lo puoi fare cliccando con il pulsante destro del mouse su una cella dei valori dei campi, riga o colonna, oppure dei campi calcolati, o dei totali di riga o colonna, a seconda di quali valori vuoi ordinare, per far comparire il menù contestuale, e poi cliccare sul comando “Ordina” e poi il tipo di ordinamento che vuoi applicare (es: “ordina dal più grande al più piccolo”);

10. infine scegli i campi da destinare ai filtri che vuoi impostare sopra la tabella pivot,

    trascinali nel riquadro “Filtro” della finestra di configurazione in alto a sinistra.
    

Finito, ora hai impostato la tua pivot. Non è difficile, vero?

Guarda il tutorial

Per vedere come si impostano le pivot in pratica, ti invito a vedere questo video del tutorial sulle tabelle pivot:

Naturalmente l’argomento pivot non si esaurisce qui, ci sono decine di comandi, funzioni straordinarie come i campi calcolati da padroneggiare, e mille trucchi e usi da apprendere. Per imparare a usarle bene, devi esercitarti, provare e riprovare, finché non diventano familiari come le tue tasche.

E puoi iniziare subito, esplora i comandi delle schede intelligenti degli “Strumenti tabella pivot”, la scheda “Progettazione” con i comandi per cambiare il layout della tabella, e la scheda “Analizza” con i comandi per la gestione della tabella e dei grafici.

Appunti importanti sulle tabelle pivot

Ecco alcuni aspetti delle pivot che devi conoscere:

1. La maschera di configurazione dovrebbe comparire ogni volta che selezioni la tabella pivot (tutta, una cella o un elemento), in caso contrario clicca con il pulsante destro sulla tabella per aprire il menu contestuale e clicca sul comando “mostra elenco campi”, oppure seleziona il menù “Strumenti tabelle pivot” sulla barra principale in alto e clicca sul comando “Elenco campi”.
2. Per rimuovere i campi che hai aggiunto alla tabella devi selezionare i campi da uno dei riquadri della finestra di configurazione e semplicemente trascinarli nella lista dei campi nella metà superiore della finestra, oppure fuori dalla finestra.
3. Puoi modificare la struttura della tabella pivot come descritto, cioè lavorando solo nella finestra di configurazione compare sul lato destro della finestra, ma hai la possibilità di passare alla modalità classica che, tra l’altro, ti permette di modificarla agendo direttamente sui titoli dei campi, selezionando e trascinando i diversi elementi nelle diverse posizioni, o da/verso la maschera di configurazione per aggiungere o togliere altri campi/elementi.

Visualizzazione classica delle tabelle pivot

Per accedere alla modalità classica, procedi come segue:

1. clicca con il pulsante destro del mouse sulla tabella pivot per aprire il menù contestuale,
2. poi clicca sul comando “opzioni tabella pivot”, per aprire la finestra omonima,
3. clicca sulla scheda “Visualizzazione”,
4. tagga la voce “layout classico tabella pivot”.

E già che ci sei esplora con attenzione tutte le schede dalla finestra opzioni, riserva più di una sorpresa.

4. Al momento della creazione della tabella pivot, Excel assegna un nome standard, del tipo “Tabella pivot12”, diverso da tutti gli altri. Ebbene ti invito a cambiarlo con un nome parlante che identifichi in modo chiaro e immediato la tabella. Naturalmente non subito, ma solo una volta che hai impostato la pivot e solo se il foglio avrà una vita e un ruolo significativo. In generale consiglio sempre di dare nomi parlanti agli oggetti dei propri fogli di lavoro che ne facilitano la gestione.
5. I titoli dei campi della tabella pivot ricalcano quelli dei dati d’origine, ma possono essere sostituiti nella tabella pivot con altri titoli, con etichette diverse che puoi inserire direttamente sulla tabella pivot cliccando sul nome dei campi, perfino sui titoli “Etichette di riga” e colonna, i totali e sul titolo dell’operazione (es: Somma di fatturato). Se vuoi puoi cambiare anche i nomi delle categorie dei campi di riga e colonna, sostanzialmente quasi tutto quello che non sono i valori calcolati. Te lo dico perché spesso, per esempio nei report, vale la pena rendere i nomi dei campi più comprensibili e parlanti.
6. Infine uno degli strumenti più utili delle pivot sono i filtri, li puoi settare facilmente attraverso i menù dei campi riga, colonna e filtri, cliccando sul riquadro con il triangolo o selezionando il titolo e premendo Alt+Giù. I filtri sono intelligenti e facilmente settabili attraverso selezione diretta, filtri preimpostati e inserimenti di valore. Studiali con attenzione per comprenderne i limiti e le possibilità.

E ora andiamo oltre, parliamo di come usare le tabelle e i grafici pivot in pratica. Ora che sai come crearle è naturale chiedersi: qual è il modo migliore per usarle? Quali sono i limiti? Come le gestisco?

Come strutturiamo le tabelle pivot?

Strutturiamo le pivot in modo da ottenere quello che cerchi: ti servono i costi di produzione per centro di costo? Sommi i costi di produzione e li raggruppi per centro di costo. Pensi a cosa stai cercando, lo esprimi in poche frasi dirette e poi lo metti in pratica con una pivot, letteralmente. Ma senza farsi 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”.

Le pivot 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 calcolo nelle tabelle pivot?

Il numero ideale è uno, solo uno, 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, 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, ma sarebbe meglio uno solo. Se non imposti parametri di riga e colonna allora puoi aggiungere più campi (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?

L’ideale sarebbe utilizzare non più di un paio di campi 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 il mercato e il risultato è ancora comprensibile, soprattutto se aggiungi qualche spiegazione a fianco della tabella per chiunque debba consultarla in futuro.

E se volessi vederne l’andamento nei mesi? Forse è meglio scomporre il problema in più tabelle pivot. Fai una tabella pivot per visualizzare il fatturato per il periodo in esame per articolo, cliente e mercato, ordinato in modo decrescente, e più tabelle per visualizzare l’andamento del fatturato per gli articoli e i clienti più significativi. Ricorda che molto non è meglio di poco, fai sempre finta di dover preparare un report, 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 precisa dei campi principali e di quelli utili, alleggerisco la tabella dai filtri che non servono, perché distraggono, soprattutto quando usiamo la pivot in un report o in un pannello di controllo.

Inoltre presta la massima attenzione ai valori impostati nel filtro, non a caso tra gli errori più frequenti nella gestione delle pivot ci sono quelli relativi ai filtri, e cerca di impostare un solo valore per filtro. Infine attenzione a non esagerare con la focalizzazione, non perdere di vista la rappresentatività della base dati.

Titoli e note delle tabelle pivot

Una tabella serve a elaborare dati per ottenere dei risultati, ma dove deve anche trasmetterli, quasi sempre, allora è necessario che la tabella venga realizzata in modo da comunicare in modo chiaro e immediato le sue informazioni. Per questo una tabella deve ricevere un titolo che spiega cosa mostra (es: andamento annuale vendite) e se necessario un sottotitolo che esplica i particolari (es: periodo, fonte, filtri).

Questi devono essere disposti su pagina, sopra alla tabella pivot, con dimensioni e font adeguati. Inoltre i titoli dei campi visualizzati nella tabella pivot devono essere parlanti, chiari e immediati, se non lo sono devono essere rinominati. Spesso è opportuno aggiungere accanto o sopra la pivot una nota con l’interpretazione dei dati mostrati o che evidenzia particolarità o anomalie. Non sottovalutare il problema della comunicazione dei risultati, è primario.

Moltiplica le tabelle pivot

Specialmente nelle fasi iniziali dell’attività che stai svolgendo, soprattutto nelle analisi, una volta che hai impostato la prima pivot, fai copie della tabella pivot, quelle che servono per esaminare i diversi parametri che ti interessano, per osservare come variano, in che ordine, quali sono i valori significativi, 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. E via dicendo.

Ordina i valori

O sei tu che ordini la pivot o lo fa lei al posto tuo. Lasciar fare è comodo, ma in diverse situazioni non è una buona idea. Verifica sempre l’ordinamento della pivot, una volta che l’hai strutturata, e quando è necessario imposta l’ordinamento più utile per te. 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 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

Per diminuire i valori visualizzati di un parametro di riga o colonna, raggruppa i valori numerici per intervalli definiti: i numeri per intervalli significativi, le date come settimane, mesi, anni, le stringhe per secondo una selezione personalizzata e altro ancora. Per raggruppare i valori di un campo riga o colonna, 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 comodo e veloce, più semplice da gestire dei campi calcolati nella base dati. Esempio classico: hai una data e vuoi visualizzare un andamento mensile o trimestrale, puoi aggiungere un campo calcolato nel foglio dati, o più semplicemente puoi raggruppare il dato nella pivot.

Se vuoi vedere una sintesi pratica su questi aspetti dell’utilizzo delle pivot, ti invito a vedere questo video del tutorial:

Controlla i dati con le tabelle pivot

Le tabelle pivot sono delle sintesi della base dati, cioè strumenti che mostrano i dati in modo più compatto e facile da consultare, per questo sono efficaci e comode per verificarne l’affidabilità e le caratteristiche. Il primo passo è comprendere natura e utilità dei campi della base dati da verificare, secondo passo è ottenere l’accesso a una fonte dati diversa e affidabile a cui appoggiarti nei controlli.

Come opero io? Operativamente 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 la lista dei valori, in particolare testa e coda del range. Se necessario imposto nel foglio dati alcuni campi calcolati per marcare i valori anomali o gli errori non evidenti, poi creo una tabella pivot in una pagina a parte e inizio i controlli. Quando trovo problemi clicco 2 volte sul dato calcolato o sui totali per estrarre in una nuova pagina i record relativi, per esaminarli meglio e per decidere se rimuoverli, correggerli o approfondire le cause degli errori.

Controlli dei dati con le pivot

Con le pivot possiamo fare buona parte dei controlli che possiamo fare sulla pagina dati, con formule e funzioni. Tra i controlli tipici ci sono molte delle verifiche della correttezza della base dati, della completezza e della coerenza. Per esempio la correttezza dei dati (che i dati siano esenti da errori) si può verificare 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). La completezza dei dati (che ci siano tutti i dati di cui abbiamo bisogno e 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 del parametro sullo stesso intervallo del valore di riferimento esterno.

Il discorso sulla qualità dei dati, la pulizia dei dati e come realizzare controlli su base dati è un argomento troppo ampio per esaurirlo qui. Lo riprenderemo in altri post e guide.

Come fare un’analisi preliminare con le tabelle pivot

Un’analisi mira a evidenziare informazioni contenute nella base dati per prendere decisioni corrette su un problema. Con le pivot l’analisi segue di solito i seguenti passi:

1. Prendi familiarità coi dati: cioè individua i campi da esaminare, le chiavi primarie, quali sono i valori e gli intervalli dei campi da esaminare.
2. Se rilevi problemi di qualità dei dati, è necessario verificare e ripulire la base dati, o sostituire la base con una più affidabile, se possibile.
3. Crea la pivot e carica i campi utili: per iniziare imposta un campo calcolato, uno solo, poi un parametro di riga e il resto caricalo come filtri.
4. Analizza il dato: significa di solito calcolare il valore che ci interessa (es: costi, fatturato, media, quantità prodotte, rese, indici e kpi varie), valutarne la distribuzione secondo i 2–3 parametri principali, studiarne l’andamento nel tempo, e definire intervalli e classi significativi. 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.
5. Approfondisci l’analisi: necessario quando non siamo sicuri di quello che stiamo cercando, o dobbiamo indagare su anomalie nei dati, o cerchiamo di comprendere le cause di risultati o andamenti. 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.
6. Trasferisci il risultato in un report: necessario se dobbiamo condividere con altri i risultati della nostra analisi.

Come già detto le tabelle 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, sia interni che esterni a Excel.

Come visualizzi gli andamenti nel tempo?

Quando imposti una tabella pivot per mostrare l’andamento di uno o più parametri o valori nel tempo, usa un intervallo di tempo per dare il “passo” della visualizzazione (mese, anno, ecc), di solito come parametro di riga. Il grafico tipico per un andamento è un grafico a linee in quanto mostra in modo più immediato l’andamento nel tempo e le sue componenti (trend, stagionalità, ecc). Imposta un solo valore calcolato nel tempo o al massimo due a confronto (es: l’andamento delle vendite di quest’anno e dell’anno precedente).

Se vuoi vedere come si impostano le pivot per ottenere le diverse visualizzazioni (andamenti, distribuzioni, frequenza, ecc) ti invito a guardare questo video del tutorial:

Come visualizzare le frequenze in una tabella pivot

Per mostrare la frequenza assoluta e percentuale di un valore/popolazione con una pivot si procede come segue:

1. imposta il valore calcolato come “conteggio” visualizzato come “% del totale”,
2. aggiungi lo stesso campo come parametro di riga,
3. ordinalo in modo decrescente,
4. e raggrupparlo per l’intervallo più significativo.

La tabella pivot è di solito accompagnata da un grafico pivot per comunicare meglio la distribuzione.

Come impostare le tabelle pivot per i report?

Le pivot permettono di costruire velocemente report interattivi, cioè in grado di interagire con l’utente che può modificare filtri e impostazioni con pochi click. Come? Una volta che hai terminato l’analisi dei dati, scegli le pivot con i risultati più significativi e copiale in pagina in cui le preparerai per essere esportate in powerpoint o word. Nei rari casi in cui il report è realizzato su Excel allora quella sarà la pagina del report. Prima di continuare ti invito a leggere con attenzione il post “Come fare report con Excel”.

Tieni sempre a mente che lo scopo dei report è comunicare i dati a chi legge, quindi tutti gli elementi, tabelle e grafici, tutto deve essere pensato o ripensato per essere facilmente comprensibile e trasmettere precise informazioni. Quindi prendi una tabella pivot alla volta e riesaminala in quest’ottica. Se necessario scartala per alternative più significative o comprensibili, oppure modificala per renderla tale.

Se vuoi usare principalmente delle tabelle, il consiglio è di semplificarle al massimo, di visualizzare un solo valore calcolato per tabella, come distribuzione, andamento, correlazione, eccetera, oppure pochi dati precisi a confronto (es: fatturato dei clienti più significativi), o pochi dati rappresentativi. Ogni tabella deve avere un titolo e una descrizione sintetica, eventuali note esplicative e, dove utile o necessario, un’interpretazione dei dati.

Una volta scelte le tabelle e aggiunti titoli e note, ti invito a formattare la tabella in modo efficace. Le parole chiave da seguire sono “evidenziare il dato”, quindi scegli uno stile omogeneo che non “ingombri” l’attenzione di chi legge, pochi colori e non sgargianti, niente bordi, font particolari, titoli giganti o altro. Usa il grassetto, la sottolineatura se proprio necessario, usa un colore o al massimo due per evidenziare quello che serve.

Se invece il report che vuoi preparare si basa principalmente su grafici?

Allora quanto visto finora deve essere applicato ai grafici. Prima prepara le tabelle pivot con i dati che vuoi visualizzare, poi crea il grafico pivot (metodo veloce: seleziona una cella e premi F11) e lavora su quest’ultimo. Nei grafici come nei report è importante “inserire quello che serve e niente di più” ed “evidenziare solo ciò che serve”, ma a queste regole si aggiunge anche “sii gradevole”. Gradevole non spettacolare, non ridondante, o spumeggiante.

I grafici devono essere formattati e configurati per essere gradevoli senza distrarre, ma portare l’attenzione al dato. Quindi è importante impostare un titolo esplicativo, una legenda chiara, titoli degli assi se serve, grandezza adeguata degli elementi importanti, evidenziare i valori significativi usando il grassetto o colori adeguati, aggiungere note esplicative direttamente sul grafico.

Evita invece griglie troppo fitte o griglie secondarie, sono maschere che nascondono le cose importanti, e nello stesso modo evita di inserire troppi numeri troppo grandi, note non chiare, troppi colori stridenti, legenda piccola e poco comprensibile, titolo criptico. Usa stili leggeri e colori tenui ed evidenzia solo il dato che vuoi comunicare. Naturalmente stile, layout e colori devono armonizzarsi con il report in cui verranno inseriti grafici e tabelle.

Infine, una volta realizzato, prima di presentare il report mostralo a un collega fidato o un amico per ricevere una valutazione su comprensibilità ed efficacia. Naturalmente scegli qualcuno di obiettivo e schietto, che ti dica in faccia quello che pensa.

Qual è la struttura di un report su Excel?

A questo riguardo ti invito di nuovo a leggere con attenzione il post “Come fare report con Excel”. In breve un report normalmente non viene realizzato su Excel, sul foglio di calcolo vengono elaborati i dati e realizzate le tabelle e i grafici con i risultati che poi vengono inseriti nel report. Talvolta però ci sono ragioni per fare report su Excel, in tal caso grafici e tabelle comunicano tutte le informazioni importanti e il poco testo necessario per accompagnarli può essere integrato nei grafici o nelle pagine di Excel.

Esempi classici di report su Excel sono alcuni report aziendali, per esempio i report periodici della produzione e della qualità, che mostrano gli andamenti degli indicatori (es: produttività e rese). Essendo ripetuti nel tempo la parte discorsiva non viene ripetuta e la comunicazione delle informazioni viene lasciata principalmente ai grafici.

Idealmente un report dovrebbe stare in una sola pagina, stampata o a video. Non è impossibile. In una pagina A4 ci puoi far stare comodamente 2 grafici o 3-4 tabelle, ma se lavori con precisione puoi entrare anche 4 grafici semplici.

Devi proprio fare report di una sola pagina?

Naturalmente la mia è una provocazione, un modo per spingerti a essere sintetico, ad avere ben chiaro quali sono le informazioni che vuoi trasmettere con il tuo report e a considerare il punto di vista del lettore che leggerà quello che hai preparato.

Quindi quanto deve essere grande un report?

Grande quanto è necessario per trasmettere le informazioni che tu vuoi trasmettere. Se ti servono 30 schermate allora usa 30 schermate, ma sii consapevole che l’impegno che chiedi al tuo lettore è maggiore di quello che tu pensi. Personalmente nella preparazione di un report ti consiglio di non superare mai i 10 elementi, grafici o tabelle, e di cercare di non superare i 5 grafici o tabelle.

Crea un pannello di controllo con i grafici pivot

Una dashboard o cruscotto o pannello di controllo è uno strumento con cui si tengono sotto controllo indicatori e parametri chiave dei diversi aspetti delle attività aziendali, dalla gestione alla contabilità, dalla logistica alla produzione. Un cruscotto di solito è composto da una serie di grafici e tabelle tutte in un’unica schermata che mostrano appunto valori, andamenti e distribuzioni dei parametri che ci interessano. Queste sono collegate a una o più fonti di dati, che sia una semplice pagina “dati” da aggiornare inserendo o importando i dati o un server esterno o più fonti diverse.

Lo scopo di una dashboard è quello di dare in un colpo d’occhio le informazioni importanti su un argomento preciso. Come sta andando il progetto di revisione della gestione della produzione? Tac! Gant, %, tempi, costi, eccetera. Come procede il nuovo impianto? Zac! Prove, quantità lavorate, rese, produttività, manutenzione, tempi, costi, eccetera.

In realtà ci sono ottimi strumenti di Business Intelligence esterni a Excel che offrono soluzioni comode, ma costose. Se non si hanno gli strumenti o il costo è un problema, allora le ultime versioni di Excel e Powerpoint offrono notevoli soluzioni. Si elaborano i dati su Excel e poi si visualizzano con Powerpoint. Non è difficile e ne parleremo in un post o una guida apposita. Ma se vogliamo possiamo fare tutto con Excel, con un poco di impegno e pazienza.

Come realizzare una dashboard

1. Il primo passo è quello di avere ben chiaro in mente quello che vuoi visualizzare e tenere sotto controllo con la dashboard. Quindi bisogna partire ponendosi alcune semplici domande: qual è lo scopo della dashboard? Quali indicatori voglio tenere sotto controllo? A chi serve la dashboard? Chi la consulterà? Qual è la base dati? Ogni quanto dovrà essere aggiornata?
2. Dopo aver risposto con cura alle domande, il secondo passo è quello di preparare le tabelle pivot a partire dalla base dati.
3. Quando hai finito di preparare le tabelle, crea i grafici pivot e formattali nel modo migliore. Scegli stile, set di colori, dimensioni, font, e così via che applicherai a tutti gli elementi del cruscotto.
4. Crea una pagina apposita e rinominala “Dashboard” o come preferisci. Seleziona l’intera pagina e colora le celle con il colore bianco per far sparire bordi e celle. Se preferisci nascondi la barra principale premendo i tasti Ctrl+F1.
5. Trasferisci i grafici e le tabelle che hai scelto nella pagina creata e raggruppali in una sola schermata. Verifica che siano leggibili e dove necessario intervieni sulla dimensione o sulla formattazione. Dove necessario aggiungi note e spiegazioni in basso o vicino a ogni elemento e filtri esterni, più semplici da gestire (usa il comando “Inserisci filtro” presente nelle schede intelligenti delle tabelle e dei grafici).

Fatto.

E poi?

Una volta che hai impostato il cruscotto devi solo aggiornarlo quando serve. Se sei agganciato a una fonte esterna, ti basta aggiornare le tabelle pivot, cliccando sul pulsante “aggiorna tutti” del menù “Dati” della barra principale, o più semplicemente premendo F9 o i tasti Ctrl+Alt+F5.

Se invece c’è un foglio dati a cui devi aggiungere manualmente i dati, allora estrai i dati dalla fonte e copiali nella pagina “dati”, oppure inseriscili tu manualmente. Dopo averlo fatto aggiorna le pivot e verifica come sono cambiati i parametri sotto controllo della dashboard. Infine se devi stampare la dashboard, in teoria non dovrebbe servire, seleziona l’intero cruscotto e imposta l’area di stampa.

È veramente tutto qui? Sì, se hai fatto tutto per bene, hai una dashboard funzionante. Aggiorni i dati, aggiorni le pivot, aggiorni grafici e dashboard.

Conclusioni

Abbiamo visto che le pivot sono strumenti dinamici, potenti e interattivi con cui possiamo fare elaborazione, analisi, report, controllo e dashboard. Abbiamo visto come crearle e che in fondo è piuttosto semplice, meno semplice invece è comprendere come strutturarle per mostrare quello che ci interessa.

Il modo migliore per imparare a usarle e gestirle al meglio è l’esperienza e l’esperienza la fai con l’esercizio, lavorandoci abbastanza a lungo per comprenderne i limiti e le possibilità. Perché come avrai già intuito, le pivot permettono di elaborare dati a velocità sorprendenti, soprattutto in ambito aziendale sono soluzioni efficaci, ma non per tutto.

Quando l’elaborazione esula dai calcoli elementari (somma, conteggio, media, eccetera), quando si parla si parla di personalizzazioni, di equazioni, di tabelle strutturate, allora sono altri gli strumenti e le soluzioni a cui dobbiamo rivolgerci.

L’argomento pivot naturalmente non si esaurisce con questa piccola guida introduttiva, anzi ne scalfisce solo la superficie. Non a caso si scrivono libri interi sull’uso delle pivot.

Ti invito a guardare il tutorial sulle tabelle pivot che trovi sul canale di Excel Professionale.

Per comodità tua e mia puoi scaricare il PDF della guida tramite Gumroad

Devi solo cliccare sul pulsante sottostante e seguire le istruzioni

Scarica la guida

NB: non devi pagare, non è necessario lasciare la tua mail

Iscriviti a ExcelProfessionale per scoprire tutto quello che Excel può fare per te

Excel può fare per te più di quello che immagini.
Se vuoi scoprire e apprendere cosa puoi fare realmente con Excel, iscriviti a Excel Professionale usando la maschera seguente.

Riceverai la newsletter bimestrale, accederai ai contenuti gratuiti condivisi (fogli dimostrativi, macro, eccetera), potrai iscriverti gratuitamente ai video corsi introduttivi e ai webinar formativi, riceverai offerte dedicate ai corsi avanzati e molto altro.

Se questa guida ti è piaciuta o ti è stato utile, condividila con gli amici sui social. Grazie.
Puoi farlo attraverso il pulsanti qui di seguito.
Grazie!

PS: Qui puoi trovare le altre guide di Excel Professionale