Tabelle PIVOT

Il foglio elettronico
Excel 2013 – 3° PARTE
Versione 15/10/2013
Le tabelle PIVOT
Tabelle PIVOT
Le tabelle pivot sono tabelle interattive che consentono all'utente di raggruppare
e riassumere grandi quantità di dati in un formato più conciso.
Le tabelle Pivot si costruiscono partendo da un elenco di dati (eventualmente importati da
una fonte esterna come ad esempio un database).
La tabella pivot riepiloga i dati utilizzando
formule di calcolo come Somma, Media,
Minimo, Massimo e Conteggio.
Creazione di una tabella Pivot
Per poter creare la tabella pivot il foglio di
calcolo di partenza deve avere le seguenti
caratteristiche:
• Avere una colonna con valori duplicati,
cioè con dati ripetuti.
• Contenere dei valori numerici da
comparare o sommare. In caso
contrario l'unica statistica possibile è il
conteggio.
Vediamo ora come procedere:
Dopo aver caricato il file da
analizzare occorre selezionare l‘intera
area dati. Successivamente andare
nel ribbon «inserisci» (A)
Richiamare il menu a discesa
«Tabelle» (B).
Cliccheggiare sul bottone “Tabella
pivot». (C)
Creazione di una tabella Pivot
La finestra di dialogo che viene proposta ripropone l’area dati precedentemente
selezionata. E’ possibile, se lo si desidera, modificarla. Per impostazione
predefinita, Excel seleziona tutti i dati presenti sul foglio di lavoro attivo.
Si può fare clic e selezionare con il
mouse una parte specifica del foglio di
calcolo oppure digitare l'intervallo di
celle manualmente.
In questa sezione si specifica dove
posizionare la tabella pivot. Per
impostazione predefinita Excel inserisce
la tabella pivot in un nuovo foglio di
lavoro. E’ possibile posizionare la tabella
pivot sullo stesso foglio dei dati,
specificando la cella di partenza dove si
desidera che i risultati siano inseriti.
Cliccando su OK verrà creato un foglio che conterrà
dei controlli specifici per la gestione della tabella pivot.
Creazione di una tabella Pivot
Per i dati si può utilizzare una
sorgente esterna. In questo caso
occorre spuntare l'opzione «Origine
dati esterna" e cliccare sul bottone
«Seleziona connessione».
Si aggancia la fonte
dati tramite il bottone
«Sfoglia …»
Creazione di una tabella Pivot
Quando si crea una tabella pivot, essenzialmente si dispongono i dati per righe e
per colonne. La composizione della tabella pivot è determinata dai campi che
aggiungo e la loro posizione. Per comporre la tabella pivot occorre trascinare il
campo che ci interessa dalla lista «Campi tabella pivot» (posta a destra) nelle
sezioni: «Campi Riga», «Campi Colonna» o «Campi Valore» della tabella pivot
(poste a sinistra).
Creazione di una tabella Pivot
Immaginiamo di avere un file dati relativo ad un’ipotetica società che ha 2 sedi di
lavoro: Roma e Milano. Nel file sono registrati gli stipendi mensili per ogni
dipendente e relativo titolo di studio.
Supponiamo che si voglia determinare l’ammontare degli stipendi pagati rispetto alla
sede di lavoro e al titolo di studio.
Per ottenere l’informazione
richiesta dobbiamo procedere
in questo modo:
•
•
•
trascino il campo «Sede di lavoro»
nella sezione «Campi colonna»
trascino il campo «Titolo di studio»
nella sezione «Campi riga»
trascino il campo «Stipendio» nella
sezione «Campi valore»
Creazione di una tabella Pivot
La tabella pivot che si ottiene è la seguente:
Creazione di una tabella Pivot
Le tabelle pivot permettono di aggiungere più campi per ciascuna sezione riga o
colonna, consentendo un maggiore controllo sulla visualizzazione dei dati.
Ampliando l’esempio precedente otteniamo:
Creazione di una tabella Pivot
E’ possibile modificare l'ordine in cui vengono visualizzate queste etichette spostando i
campi nel pannello «Campi tabella pivot» (A) oppure utilizzando il tasto destro del
mouse sulle celle di intestazione (B) dove appaiono i nomi dei campi usati come indice
di riga e colonna
B
A
Creazione di una tabella Pivot
Utilizzando il tasto destro nella sezione «campi valore» è
possibile modificare il tipo di statistica prodotta: conteggio, %
sui totali, media, etc.
Creazione di una tabella Pivot
Lo stesso risultato è ottenibile richiamando le finestre di dialogo
associate alla voce di menu «Impostazioni campo valore …»
Creazione di una tabella Pivot
E’ possibile aggiungere dei filtri restringendo l’analisi ad un sottoinsieme di dati
Creazione di una tabella Pivot
In seguito, agendo sui combo (A) associati ai filtri, è possibile indicare i valori (B) da
prendere in considerazione
B2
B1
A
Creazione di una tabella Pivot
E’ possibile cambiare l’ordine delle voci sulla
riga/colonna utilizzando il tasto destro del
mouse sulla riga/colonna da riposizionare
In caso di aggiunta di nuovi
dati è possibile modificare
l’area delle celle contenente i
dati da analizzare.
Creazione di una tabella Pivot
Talvolta i dati che dobbiamo utilizzare per la nostra tabella pivot risultano codificati
mediante degli ID. Per rendere la tabella pivot leggibile è necessario aggiungere
delle colonne di supporto che permettano una maggior leggibilità del dato. A tale
scopo posso utilizzare la funzione:
=RICERCA.VERT(C2;$J$2:$K$7;2;FALSO)
oppure:
=INDICE($K$2:$K$7;C11)
Creazione di una tabella Pivot
E’ possibile creare delle tabelle pivot più velocemente sfruttando il bottone «Tabelle
Pivot Consigliate».
Combinare più tabelle
in un’unica tabella
PIVOT
Combinare più tabelle in
un’unica tabella PIVOT
Una delle nuove funzionalità incluse in Excel 2013 è il modello dei dati (Data
Model). Il “Data Model” è una versione ridotta dell’add-in PowerPivot che è
disponibile in Excel 2010. Il “Data Model” permette di combinare più tabelle in
un’unica tabella Pivot.
Combinare più tabelle in
un’unica tabella PIVOT
Consideriamo i 3 fogli seguenti e i collegamenti tra i campi “prodotto” e “provincia”
Regioni
Relazione sul campo provincia
Vendite
Relazione sul campo prodotto
Prodotti
Combinare più tabelle in
un’unica tabella PIVOT
Vediamo ora come generare una tabella pivot partendo da queste 3 tabelle. La
prima mossa consiste nel trasformare le singole aree dati in “tabelle”.
Iniziamo con il selezionare il primo foglio: “Regioni”:
Andiamo nel ribbon «Inserisci» e
clicchiamo sul bottone «Tabella» (A).
Nella finestra di dialogo successiva
indichiamo se le intestazioni sono
comprese e poi iniziamo a definire l’area
dati (B)
Selezioniamo l’area dati includendo le
intestazioni (C)
Confermiamo con il pulsante «OK». (D)
Combinare più tabelle in
un’unica tabella PIVOT
Ripetiamo la stessa operazione sul secondo foglio: “Prodotti“ …
… e sul terzo: “Vendite“
Combinare più tabelle in
un’unica tabella PIVOT
L’operazione inversa che ripristina la zona dati come fosse una normale
area di celle (e che quindi rimuove la tabella appena definita) è la
seguente:
Clicchiamo su una
delle celle della
tabella (A)
Andiamo nel ribbon
«Progettazione» (B).
Clicchiamo su
«Converti in
intervallo» (C)
Combinare più tabelle in
un’unica tabella PIVOT
Richiamiamo il ribbon “Progettazione” (attivato quando clicco su una qualsiasi
cella dell’area dati) e forniamo un nome appropriato a ciascuna tabella:
Combinare più tabelle in
un’unica tabella PIVOT
Lo step successivo è quello di aggiungere le 3 tabelle, appena definite, al Data
Model. Procediamo in questo modo:
Clicchiamo su una delle celle di una delle 3 tabelle (A)
Andiamo nel ribbon «Progettazione» (B).
Clicchiamo su «Tabella pivot» (C)
Controlliamo che sia selezionata una tabella (1)
Spuntiamo «Aggiungi questi dati al modello di dati»
(D). In questo modo anche le altre tabelle non
selezionate verranno aggiunte al modello dati.
Combinare più tabelle in
un’unica tabella PIVOT
Nel foglio generato, contenente lo schema per
definire la tabella pivot, clicchiamo sull’etichetta
“Tutti” in modo da mostrare le 3 tabelle appena
definite. Successivamente …
… trasciniamo i campi
prescelti nella sezione righe
(A), colonne (B) e valori (C)
Ed iniziamo ad impostare le
relazioni (D)
Combinare più tabelle in
un’unica tabella PIVOT
Per impostare le relazioni tra le tabelle si utilizza il
bottone “Relazioni” nel ribbon “Analizza”
Iniziamo definendo la relazione tra la tabella Vendite
e Prodotti associata al campo prodotto (A) …
… e confermiamo (B)
Combinare più tabelle in
un’unica tabella PIVOT
Continuiamo aggiungendo una nuova relazione (A). Questa finestra è raggiungibile
anche utilizzando il bottone «Gestisci relazioni …» nella finestra «Crea relazione»
visibile nell’immagine in fondo alla seguente slide (B)
Ecco la relazione tra la tabella Vendite e Regioni determinata dal campo Provincia
Combinare più tabelle in
un’unica tabella PIVOT
Terminata la definizione delle relazioni otteniamo la seguente tabella pivot. Agendo
sulla «barra della formula» possiamo modificare le singole etichette.
Cliccando poi sul bottone «Intestazione campi» del ribbon «Analizza» è
possibile occultare i titoli di riga e colonna:
Combinare più tabelle in
un’unica tabella PIVOT
Possiamo invertire l’ordine riga / colonna
ed otteniamo:
Inoltre aggiungendo un ulteriore campo alle righe
(esempio provincia) generiamo la seguente tabella
pivot: