Il foglio elettronico

Excel 2013 – 3° PARTE

Versione 15/10/2013

Le tabelle PIVOT

Tabelle PIVOT

Le tabelle Pivot si costruiscono partendo da un elenco di dati (eventualmente importati da una fonte esterna come ad esempio un database).

Le tabelle pivot sono tabelle interattive che consentono all'utente di raggruppare e riassumere grandi quantità di dati in un formato più conciso.

La tabella pivot riepiloga i dati utilizzando formule di calcolo come Somma, Media, Minimo, Massimo e Conteggio.

Creazione di una tabella Pivot

� 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)

Vediamo ora come procedere:

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.

Creazione di una tabella PivotLa 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 PivotPer 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 PivotQuando 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).

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.

Creazione di una tabella Pivot

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»

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

Creazione di una tabella Pivot

A

B

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

Creazione di una tabella Pivot

AB1

B2

E’ possibile cambiare l’ordine delle voci sulla riga/colonna utilizzando il tasto destro del mouse sulla riga/colonna da riposizionare

Creazione di una tabella Pivot

In caso di aggiunta di nuovi dati è possibile modificare l’area delle celle contenente i dati da analizzare.

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:

Creazione di una tabella Pivot

=INDICE($K$2:$K$7;C11)

=RICERCA.VERT(C2;$J$2:$K$7;2;FALSO)

oppure:

Creazione di una tabella PivotE’ 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 ProdottiVendite

Relazione sul campo prodottoRelazione sul campo provincia

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”:

� 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)

� Andiamo nel ribbon «Inserisci» e clicchiamo sul bottone «Tabella» (A).

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

� Clicchiamo su una delle celle della tabella (A)

� Andiamo nel ribbon «Progettazione» (B).

� Clicchiamo su «Converti in intervallo» (C)

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:

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 Venditee 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: