Excel tabelle pivot, grafici pivot, PowerPivot

Campi calcolati. Raggruppamenti. Relazioni. Limiti. Power Pivot

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 14 Gennaio 2024
Categoria: Microsoft Office Excel Tabelle pivot

Il termine pivot (si pronuncia pàivot) in riferimento alle tabelle pivot deriva dall'inglese e significa perno. Il vocabolo si riferisce all'idea di ruotare o di pivotare i dati in una tabella per visualizzarli in modo diverso, in modo da poter esplorare e analizzare i dati in modo più efficace. Le tabelle pivot sono una delle funzioni più potenti presenti in Excel e servono ad analizzare rapidamente una grande mole di dati. Esse esaminano tali dati e li riepilogano sintetizzandoli, il tutto con pochi clic. Inoltre le pivot sono flessibili poiché è possibile modificarle velocemente, per di più dai loro valori si possono creare grafici pivot filtrabili che, come i grafici normali, si aggiornano al cambiamento dei dati che l’hanno originati. In poche parole mostrano tutta la loro utilità quando è necessario elaborare grandi quantità di informazioni decidendo quali campi (colonne) mettere in relazione al fine di ottenere una funzione di riepilogo quali la somma, il conteggio, la media, il minimo, il massimo, il prodotto, il conta numeri, la deviazione standard, la deviazione standard della popolazione, la varianza, la varianza della popolazione.
Ad esempio in una tabella che mostra vari campi tra i quali Cognome, Fatturato, Prodotto, Zona, Data, si potrebbe decidere di mettere in relazione il Fatturato con il Cognome e la Zona o relazionare qualsiasi altro campo a proprio piacimento per ottenere risultati derivanti da una qualsiasi delle funzioni suddette o inventarsene delle proprie, ad esempio la Provvigione sulle vendite.
Con le pivot si possono inoltre elencare i valori univoci, creare report, filtrare, ordinare, trasporre, ecc., oltre a ciò le righe aggiunte a una tabella vengono incluse in modo automatico nella tabella pivot aggiornando i dati e le nuove colonne vengono incluse nell'Elenco campi tabella pivot.
Inoltre è possibile utilizzare le tabelle pivot insieme all'Analisi di simulazione di Excel, in particolare con Gestione Scenari.
Se ci si vuole esercitare con le nozioni successive è possibile scaricare il seguente database. Ma ora iniziamo con Excel: tabelle pivot e grafici pivot dando uno sguardo anche alle Power Pivot.

Indice Tabelle pivot e Grafici pivot

• Costruire una Tabelle pivot
  • Creare Campi calcolati
  • INFO.DATI.TAB.PIVOT
  • Raggruppare campi in modo temporale
  • Avere un intervallo di origine sempre corretto
  • Estrazione univoci
  • Aggiornare la tabella pivot
  • Creare più tabelle pivot nello stesso foglio
• Grafico pivot
• Tabelle pivot e Power Pivot
• Creare relazioni nelle tabelle pivot
• Limiti Tabelle pivot

Costruire una Tabella pivot

Prima di poter costruire una tabella pivot è necessario organizzare i dati nella tabella sorgente in modo che non ci siano righe (tuple) o colonne (campi) vuote. È anche importante che i campi presentino lo stesso tipo di dati, va evitato ad esempio che si combinino date e testo.

Copiate la seguente tabella (sarebbe però opportuno creare più righe di dati) con i campi Prodotto, Anno, Mese, Fatturato, Venditore, Zona di un’ipotetica società che vende materiale informatico.

Ora per creare la tabella pivot è necessario seguire questi passi:

1. Posizionarsi su una cella all’interno della tabella (o selezionare quelle sulle quali costruire la pivot comprese le intestazioni)
2. Dalla barra multifunzione cliccare la scheda Inserisci e poi l’icona Tabella pivot
3. Nella finestra Crea tabella pivot che si apre specificare i dati da analizzare (normalmente l'intervallo della tabella sorgente, ma è anche possibile prelevare dati presenti in un altro foglio di lavoro o in un'altra cartella di lavoro) e dove collocarli (la scelta migliore è Nuovo foglio di lavoro)
4. Si crea automaticamente un nuovo foglio di lavoro (che si potrebbe nominare TabellaPivot) prima del foglio corrente (in questo esempio chiamato TabellaDati) e a destra e sinistra appaiono dei riquadri. In quello di destra (Elenco campi tabella pivot) bisogna trascinare nelle 4 aree sottostanti (Filtri, Colonne, Righe, Valori, anche se non è necessario riempirle tutte e quattro ed è anche possibile portare più campi nella stessa area) i campi presenti nella parte superiore del riquadro (o cliccare le rispettive caselle di controllo allo scopo di farsi aiutare dall’applicativo stesso). Ad esempio facendo la seguente impostazione (o trascinando direttamente i campi dell’Elenco campi tabella pivot nelle aree sottostanti)
   si ottiene la seguente tabella pivot
   
   

A questo punto con pochissimo sforzo sarà possibile crearne di più complesse trascinando ulteriori campi nelle 4 aree.
In particolare va detto che nell'area:

• Valori, di norma, vanno i campi numerici in modo che la pivot possa fare calcoli attraverso le funzioni di riepilogo disponibili (Somma, Media, Min, Max, Conteggio, Prodotto, Conta.numeri, Deviazioni e Varianze, un po' come le funzioni Excel SUBTOTALE e AGGREGA)
• Filtri va il campo per il quale si desidera vengano create automaticamente tante pivot quanti sono i record contenuti nel campo posizionato nel filtro stesso (ad esempio portando il campo Prodotto nell'area Filtri, attraverso la scheda Analizza tabella Pivot, gruppo Tabella Pivot, freccia dell'icona Opzioni, icona Mostra pagine filtro rapporto..., è possibile creare in automatico 3 pivot in 3 fogli separati che si chiameranno LCD, RAM e CPU, con le relative pivot)

Va precisato che è possibile filtrare i dati anche in altri due modi:

• usando la freccia verso il basso che compare sulle Etichette di riga e sulle Etichette di colonna poste nella tabella pivot
• cliccando nella scheda Analizza tabella Pivot, gruppo Filtra, icona Inserisci filtro dati. Nella finestra che compare basterà spuntare le caselle per le quali si desidera appaia sul foglio un pratico, ed esteticamente accattivante, filtro (leggi i filtri in Excel)

Si sarà notato che ogni volta che si rigenera sullo stesso foglio una nuova tabella pivot, la vista precedente scompare, è dunque consigliabile copiare ed incollare le varie pivot in un altro foglio così da poterle conservare. Esiste un modo semplice per farlo: tasto destro sul nome del foglio da copiare (ovvero la scheda in basso a sinistra della schermata), clic sulla voce Sposta o copia..., spuntare la casella di controllo Crea una copia e pigiare il tasto Ok.

Ecco qualche suggerimento utile a modificare una tabella pivot, si potrà ad esempio:

• applicare qualsiasi formattazione alla tabella pivot attraverso la scheda Progettazione (che insieme ad Opzioni compare quando si clicca dentro la tabella pivot)
• modificare le formule di riepilogo (che di default è Somma) facendo clic destro su una cella della colonna da modificare e scegliendo Riepiloga valori per (Modificando il metodo di calcolo si modifica anche il nome della funzione di riepilogo, ad esempio "Media di Fatturato". È possibile cambiare questo nome dalla scheda Opzioni attraverso la casella di testo Campo attivo, ma è preferibile rinominare i campi a lavoro terminato ad esempio attraverso Trova e sostituisci)
• aggiornare la tabella pivot dalla scheda Opzioni attraverso l’icona Aggiorna se cambiano i dati nella tabella dati (quella per così dire non pivot) o Aggiorna tutto per modificare tutte le tabelle pivot correlate
• creare un grafico pivot cliccando dalla scheda Opzioni l’icona Grafico pivot
• visualizzare i dati in percentuale sul totale complessivo invece di usare un calcolo per riepilogare i dati; per farlo bisogna cliccare dalla scheda Opzioni l’icona Impostazioni campo valore e da Mostra valori come scegliere % del totale complessivo
• conoscere il dettaglio di un valore all’interno della tabella pivot, facendo doppio clic su di esso; Excel creerà un nuovo foglio di lavoro con il dettaglio del dato selezionato

Creare campi calcolati

Le tabelle pivot, come detto ad inizio articolo, permettono di eseguire 11 funzioni di riepilogo, ma se avessi necessità di fare altri calcoli oltre quelli offerti di default? Niente paura! 
È possibile inserire formule personalizzate utilizzando gli operatori matematici come ^, *, /, +, -, ma anche molte formule di Excel con il limite che non è consentito l'uso di formule di ricerca e riferimento, nomi e matrici.
Per farlo occorre andare dal gruppo Calcoli sull’icona Campi, elementi e set, cliccare Campo calcolato… per aprire la finestra Inserisci campi calcolati, dare un nome al calcolo da effettuare e scriverne la formula relativa, infine cliccare prima Aggiungi e poi OK.

Il campo calcolato viene aggiunto alla pivot con la funzione di riepilogo SOMMA (l'unica consentita per i campi calcolati).

Ad esempio, se si hanno i campi Valore e Quantità sarebbe possibile calcolare il prezzo scrivendo con la formula =Valore/Quantità.
In caso si avesse una quantità pari a 0, per prevenire l'errore #DIV/0! si potrebbe usare un classico SE: =SE(Quantità=0;0;Valore/Quantità)

Per non mostrare un campo calcolato bisogna semplicemente togliere il segno di spunta nel riquadro di destra Elenco campi tabella pivot.

Per eliminare definitivamente un campo calcolato è necessario riaprire dal gruppo Calcoli l’icona Campi, elementi e set, cliccare Campo calcolato… per mostrare la finestra Inserisci campi calcolati,  aprire l’elenco a discesa Nome, selezionare il campo calcolato da eliminare e fare clic sul pulsante Elimina e poi su OK.

Nell’esempio seguente viene calcolata una Provvigione del 3% sul Fatturato.

INFO.DATI.TAB.PIVOT

La funzione INFO.DATI.TAB.PIVOT permette di estrarre il valore di un campo di una tabella pivot. È possibile utilizzarla rapidamente digitando = nella cella dove va restituito il valore e selezionare la cella nella tabella pivot con i dati da restituire. Si potrebbe obiettare che si otterrebbe lo stesso risultato utilizzando un semplice riferimento a una cella della tabella pivot, ad esempio =C3, ma INFO.DATI.TAB.PIVOT ha l’enorme vantaggio di riportare il valore corretto anche se la pivot cambia di dimensione.
Per i più curiosi si mostra la sintassi della formula: INFO.DATI.TAB.PIVOT(CampoDati;TabellaPivot;[Campo1;Elemento1;Campo2;Elemento2];...)

Raggruppare campi in modo temporale

Supponiamo di voler raggruppare i dati in Mesi
Dopo aver portato il relativo campo in Etichette di riga o in Etichette di colonna della tabella pivot, fare clic con il  destro del mouse su una qualsiasi data e scegliere Raggruppa...
Nelle caselle A partire da e Fino a immettere ad esempio le informazioni seguenti:

È possibile fare clic su altri periodi di tempo in base ai quali raggruppare. Ad esempio, è possibile raggruppare per Trimestri e Mesi.
Per rimuovere il raggruppamento, fare clic con il destro del mouse su una qualsiasi data raggruppata e scegliere Separa...

Ovviamente è anche possibile effettuare altre tipi di raggruppamenti, ad esempio classi di Stipendi; la procedura è identica.

Avere un intervallo di origine sempre corretto

Nel momento in cui si costruisce una tabella pivot l’origine dati è precisamente determinata (ad esempio A1:M3254), ma se l’utente decide in corso d’opera di introdurre ulteriori righe, deve anche preoccuparsi di correggere il suddetto intervallo per aggiornare la pivot.

Per ovviare al problema è possibile ad esempio usare la funzione =SCARTO(Rif;Righe;Colonne;Altezza;Largh)
In pratica quando si seleziona la tabella da trasformare in pivot, come prima cosa bisogna dichiarare di iniziare da A1 e incorporare ogni cella occupata della colonna A e della riga 1.
La funzione restituisce un intervallo spostato rispetto a una cella (o a un intervallo di celle) di un certo numero di righe e colonne e ha 5 argomenti, di cui i primi 3 obbligatori:

• Rif la cella da dove iniziare (es. $A$1)
• Righe e Colonne da includere; dato che per questo esempio sono inutili, inserire 0
• Altezza e Largh intervallo di celle occupate segnalato da CONTA.VALORI (per l’Altezza si usa CONTA.VALORI($A:$A) e per la larghezza CONTA.VALORI($1:$1))

La funzione risultante è:
=SCARTO($A$1;0;0; CONTA.VALORI($A:$A); CONTA.VALORI($1:$1))
da mettere in Formule/Definisci Nome/Riferito a, dopo aver assegnato un Nome (senza spazi) che sarà segnalato come origine dati della pivot quando all’inizio della sua creazione si andrà in Inserisci/Tabella Pivot

Estrazione univoci

In realtà non occorrerebbe una tabella pivot per estrarre gli univoci di un campo, perché è una caratteristica dei filtri avanzati, in ogni caso volendo procedere attraverso una pivot, basterebbe trascinare il campo per il quale si desidera estrarre gli univoci, sia nel campo Riga che Colonna; in caso si volessero anche contare quante volte compare un determinato record all’interno del campo, bisognerebbe trascinare quest’ultimo anche nel campo Valori, prendendolo da Riga o Colonna

Aggiornare la tabella pivot

Può capitare di dover modificare i dati che originano la tabella pivot o addirittura di aggiungere determinate colonne (campi) che in un primo tempo non si erano previste; in tali casi si deve provvedere ad aggiornare la pivot già creata.
Se si è soltanto modificato qualche record già presente basterà cliccare l'icona Aggiorna (aggiorna le informazioni della cartella di lavoro provenienti da un'origine dati) o Aggiorna tutti (aggiorna contemporaneamente tutte le tabelle pivot nella cartella di lavoro).
Se invece si sono aggiunte una o più colonne sarà necessario cliccare l'icona Cambia origine dati e selezionare il nuovo intervallo.

Creare più tabelle pivot nello stesso foglio

Volendo è possibile creare più tabelle pivot nello stesso foglio. Dopo aver costruito la prima ci si può infatti posizionare nell'origine dati, cliccare Tabella pivot dalla scheda Inserisci, scegliere Foglio di lavoro esistente, nel campo Posizione, cliccare la cella qualche riga al di sotto della prima tabella pivot e dare Ok.

Grafico pivot

I grafici pivot organizzano in maniera grafica i dati delle tabelle pivot mostrando le serie di dati, le categorie e gli assi allo stesso modo dei grafici standard, ma offrendo filtri interattivi direttamente sul grafico, ciò consente di analizzare più velocemente i sottoinsiemi dei dati.

È possibile creare un grafico pivot senza necessariamente creare precedentemente una tabella pivot.
Per formattare la tabella pivot si procede allo stesso modo che nei grafici normali ovvero aprendo le schede in alto a destra della barra multifunzione: Progettazione, Layout, Formato e Analizza; da quest’ultima è anche possibile da Pulsanti campo nascondere i filtri presenti sul campo in caso si voglia stampare il grafico.

Tabelle pivot e Power Pivot

Power Pivot è un componente aggiuntivo utilizzabile dalla versione Microsoft Excel 2010.
Esso amplia le possibilità già offerte dalle tabelle pivot perché queste ultime analizzano una sola tabella per volta la quale non deve perciò essere messa in relazione con altro; al contrario Power Pivot può analizzare più di una tabella per volta, ma per poterlo fare deve mettere in relazione le tabelle. Per saperne di più leggi l'articolo: Excel Power Pivot. Dai uno sguardo anche agli Strumenti di Microsoft Excel per Business Intelligence (Power Query, Power Pivot, Power View, Power Map, Power BI).

Creare ralazioni nelle Tabelle pivot

Prima occorre che siano rispettate le seguenti condizioni:

1. Ogni intervallo va formattato come tabella (scheda Home / Formatta come tabella), magari dando, dalla scheda Progettazione, un nome significativo alle tabelle
2. Almeno in una tabella deve avere un campo (colonna) con dati univoci, cioè senza duplicati, per esempio codice fiscale, id, numero targa, ecc.

Si supponga di avere tre tabelle:

1. Magazzino con anagrafica di Prodotti presenti e giacenza iniziale
2. Carico con le singole entrate dei prodotti in magazzino e relative quantità
3. Scarico con le quantità di prodotti venduti

Nell’anagrafica di Magazzino, il nome del prodotto è univoco mentre in Carico e Scarico può essere duplicato perché il medesimo può essere caricato e scaricato più volte.
I nomi delle tabelle sono: TabellaMagazzino, TabellaCarico, TabellaScarico.
Dalla scheda Dati cliccare l’icona Relazioni; nella finestra che si apre, cliccare Nuova..., impostare come segue e dare Ok:

TabellaMagazzino, è la tabella correlata, cioè quella in cui si trova il campo con il record univoco.
Le relazioni impostate compaiono nella finestra iniziale delle Relazioni.
Il vantaggio della relazione sta nel poter inserire una tabella pivot che peschi dalle 3 tabelle.

Limiti Tabelle pivot

Le Tabelle Pivot rappresentano dunque una funzionalità molto utile di Excel per analizzare velocemente grandi quantità di dati, ma hanno dei limiti. Il componente Power Pivot è un'evoluzione delle tabelle pivot ed è utilizzabile dalla versione Microsoft Excel 2010. Fa parte dei tool power insieme a Power Query, Power Map, Power View e Power BI. Power Pivot consente di realizzare un elementare sistema di business intelligence, grazie alla capacità di importare dati da più fonti, ottimizzarli, creare calcoli più sofisticati con le misure DAX e costruire indicatori KPI, il tutto può poi essere analizzato con le Tabelle Pivot di Excel o con Power BI. Per terminare è anche utile ricordare che le tabelle pivot si possono formattare come tabella.

Mi auguro ti abbia chiarito qualche dubbio leggere Excel: tabelle pivot e grafici pivot e ti sia piciuto il link alle Power Pivot.

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel