Utilizzo delle tabelle pivot in Microsoft Excel

Le tabelle pivot sono una delle funzionalità più potenti di Microsoft Excel. Permettono di analizzare e riassumere grandi quantità di dati in pochi clic del mouse. In questo articolo esploriamo le tabelle pivot, comprendiamo cosa sono e impariamo come crearle e personalizzarle.

Nota: questo articolo è stato scritto utilizzando Excel 2010 (Beta). Il concetto di una tabella pivot è cambiato poco nel corso degli anni, ma il metodo per crearne uno è cambiato in quasi ogni iterazione di Excel. Se stai utilizzando una versione di Excel che non è il 2010, aspettati schermi diversi da quelli che vedi in questo articolo.

Un po 'di storia

Nei primi giorni dei programmi per fogli di calcolo, Lotus 1-2-3 ha stabilito il ruolo. Il suo dominio era così completo che la gente pensava che fosse una perdita di tempo per Microsoft preoccuparsi di sviluppare il proprio software per fogli elettronici (Excel) per competere con Lotus. Flash-forward al 2010, e il dominio di Excel sul mercato dei fogli elettronici è maggiore di quello di Lotus, mentre il numero di utenti che eseguono Lotus 1-2-3 si avvicina a zero. Come è successo? Che cosa ha causato una svolta così drammatica delle fortune?

Gli analisti del settore hanno messo in discussione due fattori: in primo luogo, la Lotus ha deciso che questa nuova e fantastica piattaforma GUI chiamata "Windows" era una moda passeggera che non sarebbe mai decollata. Hanno rifiutato di creare una versione Windows di Lotus 1-2-3 (per alcuni anni, comunque), prevedendo che la loro versione DOS del software fosse tutto ciò di cui qualcuno avrebbe mai avuto bisogno. Microsoft, naturalmente, ha sviluppato Excel in esclusiva per Windows. In secondo luogo, Microsoft ha sviluppato una funzionalità per Excel che Lotus non ha fornito in 1-2-3, vale a dire Tabelle pivot. La funzione PivotTables, esclusiva di Excel, era considerata così incredibilmente utile che le persone erano disposte a imparare un intero nuovo pacchetto software (Excel) piuttosto che seguire un programma (1-2-3) che non ce l'aveva. Questa caratteristica, insieme al giudizio erroneo sul successo di Windows, è stata la morte per Lotus 1-2-3 e l'inizio del successo di Microsoft Excel.

Informazioni sulle tabelle pivot

Quindi cos'è una tabella pivot, esattamente?

In parole povere, una tabella pivot è un riepilogo di alcuni dati, creati per consentire una facile analisi di tali dati. Ma a differenza di un riepilogo creato manualmente, le tabelle pivot di Excel sono interattive. Dopo averne creato uno, puoi facilmente cambiarlo se non offre le informazioni esatte sui tuoi dati che speravi. In un paio di clic il riepilogo può essere "ruotato" - ruotato in modo tale che le intestazioni delle colonne diventino intestazioni di riga e viceversa. C'è molto di più che può essere fatto anche. Piuttosto che cercare di descrivere tutte le funzionalità di PivotTables, li dimostreremo semplicemente ...

I dati che si analizzano utilizzando una tabella pivot non possono essere solo qualunque dati - deve essere crudo dati, precedentemente non elaborati (non riassunti) - in genere un elenco di qualche tipo. Un esempio di ciò potrebbe essere l'elenco delle transazioni di vendita in un'azienda negli ultimi sei mesi.

Esaminare i dati mostrati di seguito:

Si noti che questo è non dati grezzi. In realtà, è già un riassunto di qualche tipo. Nella cella B3 possiamo vedere $ 30.000, che apparentemente è il totale delle vendite di James Cook per il mese di gennaio. Quindi dove sono i dati grezzi? Come siamo arrivati alla cifra di $ 30.000? Dov'è l'elenco originale delle transazioni di vendita da cui è stata generata questa figura? È chiaro che da qualche parte, qualcuno deve aver preso la briga di raccogliere tutte le transazioni di vendita degli ultimi sei mesi nel riepilogo che vediamo sopra. Quanto pensi che sia durato? Un'ora? Dieci?

Molto probabilmente, sì. Vedete, il foglio di calcolo sopra è in realtà non una tabella pivot. È stato creato manualmente da dati grezzi memorizzati altrove, e ci sono voluti un paio d'ore per la compilazione. Tuttavia, è esattamente il tipo di riassunto poteva essere creato utilizzando le tabelle pivot, nel qual caso ci sarebbero voluti solo pochi secondi. Scopriamo come ...

Se dovessimo rintracciare l'elenco originale delle transazioni di vendita, potrebbe essere qualcosa del genere:

Potresti essere sorpreso di apprendere che, utilizzando la funzionalità di tabella pivot di Excel, possiamo creare un riepilogo mensile delle vendite simile a quello sopra in pochi secondi, con pochi clic del mouse. Possiamo farlo - e anche molto di più!

Come creare una tabella pivot

Innanzitutto, assicurati di disporre di alcuni dati grezzi in un foglio di lavoro in Excel. Un elenco di transazioni finanziarie è tipico, ma può essere una lista di qualsiasi cosa: dati di contatto dei dipendenti, la tua collezione di CD o dati sul consumo di carburante per la flotta di auto della tua azienda.

Quindi iniziamo Excel ... e carichiamo una lista simile ...

Una volta che abbiamo aperto la lista in Excel, siamo pronti per iniziare a creare la tabella pivot.

Fai clic su una singola cella nell'elenco:

Quindi, dal Inserire scheda, fare clic su Tabella pivot icona:

Il Crea tabella pivot viene visualizzata una finestra, che ti pone due domande: su quali dati deve essere basata la nuova tabella pivot e dove dovrebbe essere creata? Poiché abbiamo già fatto clic su una cella all'interno dell'elenco (nel passaggio precedente), l'intera lista che circonda quella cella è già selezionata per noi ($ A $ 1: $ G $ 88 sul pagamenti foglio, in questo esempio). Si noti che è possibile selezionare un elenco in qualsiasi altra area di qualsiasi altro foglio di lavoro o anche qualche origine dati esterna, ad esempio una tabella di database di Access o anche una tabella di database MS-SQL Server. Dobbiamo anche selezionare se vogliamo che la nostra nuova tabella pivot venga creata su a nuovo foglio di lavoro o su un esistente uno. In questo esempio selezioneremo a nuovo uno:

Il nuovo foglio di lavoro viene creato per noi e viene creata una tabella pivot vuota su quel foglio di lavoro:

Appare anche un'altra finestra: The Elenco campi tabella pivot. Questo elenco di campi verrà mostrato ogni volta che clicchiamo su qualsiasi cella all'interno della tabella pivot (sopra):

L'elenco di campi nella parte superiore della casella è in realtà la raccolta di intestazioni di colonna dal foglio di lavoro originale di dati non elaborati. Le quattro caselle vuote nella parte inferiore dello schermo ci consentono di scegliere il modo in cui vorremmo che la nostra tabella pivot riepilogasse i dati grezzi. Finora, non c'è nulla in quelle caselle, quindi la tabella pivot è vuota. Tutto quello che dobbiamo fare è trascinare i campi dall'elenco in alto e rilasciarli nelle caselle inferiori. Una tabella pivot viene quindi creata automaticamente per corrispondere alle nostre istruzioni. Se sbagliamo, dobbiamo solo trascinare i campi indietro da dove sono venuti e / o trascinati nuovo i campi in basso per sostituirli.

Il Valori la scatola è probabilmente la più importante delle quattro. Il campo che viene trascinato in questa casella rappresenta i dati che devono essere riassunti in qualche modo (sommando, facendo la media, trovando il massimo, il minimo, ecc.). È quasi sempre numerico dati. Un candidato perfetto per questa scatola nei nostri dati di esempio è il campo / colonna "Quantità". Trasciniamo quel campo nel Valori scatola:

Si noti che (a) il campo "Quantità" nell'elenco dei campi è ora spuntato e "Somma dell'importo" è stata aggiunta al Valori casella, indicando che la colonna delle quantità è stata sommata.

Se esaminiamo la tabella pivot stessa, troviamo effettivamente la somma di tutti i valori di "importo" dal foglio di lavoro dei dati non elaborati:

Abbiamo creato la nostra prima tabella pivot! Pratico, ma non particolarmente impressionante. È probabile che abbiamo bisogno di un po 'più di conoscenza dei nostri dati.

Facendo riferimento ai nostri dati di esempio, dobbiamo identificare una o più intestazioni di colonna che potremmo utilizzare per suddividere questo totale. Ad esempio, potremmo decidere che vorremmo vedere un riepilogo dei nostri dati in cui abbiamo a intestazione di riga per ciascuno dei diversi venditori nella nostra azienda e un totale per ciascuno. Per ottenere ciò, tutto ciò che dobbiamo fare è trascinare il campo "Venditore" nel Etichette di fila scatola:

Adesso, finalmente, le cose cominciano a diventare interessanti! La nostra tabella pivot inizia a prendere forma ... .

Con un paio di clic abbiamo creato una tabella che avrebbe richiesto molto tempo per essere eseguita manualmente.

Quindi che altro possiamo fare? Bene, in un certo senso la nostra tabella pivot è completa. Abbiamo creato un utile riepilogo dei nostri dati di origine. Le cose importanti sono già apprese! Per il resto dell'articolo, esamineremo alcuni modi in cui è possibile creare tabelle pivot più complesse e i modi in cui tali tabelle pivot possono essere personalizzate.

In primo luogo, possiamo creare un Due-tavolo dimensionale. Facciamo ciò usando "Metodo di pagamento" come intestazione di colonna. Trascinare semplicemente l'intestazione "Metodo di pagamento" su Colonne Etichette scatola:

Che assomiglia a questo:

Iniziare a ottenere molto freddo!

Facciamolo a tre-tavolo dimensionale. Come potrebbe essere un tavolo simile? Bene vediamo…

Trascina la colonna "Pacchetto" / titolo su Segnala filtro scatola:

Notate dove finisce ... .

Questo ci consente di filtrare il nostro rapporto in base al quale è stato acquistato il "pacchetto vacanze". Ad esempio, possiamo vedere la ripartizione del venditore rispetto al metodo di pagamento tutti pacchetti, o, con un paio di clic, cambiarlo per mostrare la stessa suddivisione per il pacchetto "Sunseekers":

Quindi, se ci pensi nel modo giusto, la nostra tabella pivot ora è tridimensionale. Continuiamo a personalizzare ...

Se si scopre, per esempio, vogliamo solo vedere assegno e carta di credito transazioni (cioè nessuna transazione in contanti), quindi possiamo deselezionare la voce "Contanti" dalle intestazioni di colonna. Fai clic sulla freccia a discesa accanto a Colonne Etichette, e deselezionare "Contanti":

Vediamo cosa sembra ... Come puoi vedere, "Cash" non c'è più.

formattazione

Questo è ovviamente un sistema molto potente, ma finora i risultati sono molto semplici e noiosi. Per cominciare, i numeri che stiamo sommando non assomigliano a importi in dollari - solo numeri vecchi e semplici. Risolviamolo.

Una tentazione potrebbe essere quella di fare ciò che siamo abituati a fare in tali circostanze e semplicemente selezionare l'intera tabella (o l'intero foglio di lavoro) e utilizzare i pulsanti di formattazione numerici standard sulla barra degli strumenti per completare la formattazione. Il problema con questo approccio è che se si modifica la struttura della tabella pivot in futuro (che è probabile al 99%), allora quei formati numerici andranno persi. Abbiamo bisogno di un modo che li renderà (semi) permanenti.

Per prima cosa, localizziamo la voce "Sum of Amount" nel file Valori casella e fare clic su di esso. Appare un menu. Selezioniamo Impostazioni campo valore ... dal menu:

Il Impostazioni del campo valore appare la scatola.

Clicca il Formato numero pulsante e lo standard Casella Formato celle appare:

Dal Categoria lista, selezionare (dire) Contabilità, e rilascia il numero di posizioni decimali a 0. Fare clic su ok un paio di volte per tornare alla tabella pivot ...

Come puoi vedere, i numeri sono stati formattati correttamente come importi in dollari.

Mentre siamo in tema di formattazione, formattare l'intera tabella pivot. Ci sono alcuni modi per farlo. Usiamo un semplice ...

Clicca il Strumenti / design di tabella pivot tab:

Quindi rilasciare la freccia in basso a destra del Stili di tabella pivot elenca per vedere una vasta collezione di stili incorporati:

Scegli uno che fa appello e guarda il risultato nella tabella pivot:

Altre opzioni

Possiamo lavorare anche con le date. Ora di solito, ci sono molte, molte date in una lista di transazioni come quella con cui abbiamo iniziato. Ma Excel offre l'opzione di raggruppare gli elementi di dati insieme per giorno, settimana, mese, anno, ecc. Vediamo come è fatto.

Innanzitutto, rimuoviamo la colonna "Metodo di pagamento" dal Colonne Etichette box (basta trascinarlo di nuovo nella lista dei campi) e sostituirlo con la colonna "Date Booked":

Come potete vedere, ciò rende istantaneamente inutilizzabile la nostra tabella pivot, dandoci una colonna per ogni data in cui si è verificata una transazione: una tabella molto ampia!

Per risolvere il problema, fai clic con il tasto destro su qualsiasi data e seleziona Gruppo… dal menu di scelta rapida:

Viene visualizzata la casella di raggruppamento. Selezioniamo mesi e fare clic su OK:

Ecco! UN tanto tabella più utile:

(Per inciso, questa tabella è praticamente identica a quella mostrata all'inizio di questo articolo - il riepilogo vendite originale che è stato creato manualmente.)

Un'altra cosa interessante da sapere è che puoi avere più di un set di intestazioni di riga (o intestazioni di colonna):

... che assomiglia a questo ... .

Puoi fare una cosa simile con le intestazioni di colonna (o anche i filtri di report).

Mantenere le cose semplici di nuovo, vediamo come tracciare media valori, piuttosto che valori sommati.

Innanzitutto, fai clic su "Sum of Amount" e seleziona Impostazioni campo valore ... dal menu di scelta rapida visualizzato:

Nel Riepiloga il campo del valore di lista in Impostazioni del campo valore casella, selezionare Media:

Mentre siamo qui, cambiamo il Nome personalizzato, da "Average of Amount" a qualcosa di un po 'più conciso. Digita qualcosa come "Avg":

Clic ok, e guarda come appare. Si noti che tutti i valori cambiano da totali sommati a medie e il titolo della tabella (cella in alto a sinistra) è cambiato in "Avg":

Se ci piace, possiamo anche avere somme, medie e conteggi (conteggi = quante vendite ci sono state) tutti sulla stessa Tabella pivot!

Ecco i passaggi per ottenere qualcosa del genere (a partire da una tabella pivot vuota):

Trascina "Venditore" nel Colonne Etichette
Trascina il campo "Quantità" in Valori casella tre volte
Per il primo campo "Quantità", cambia il suo nome personalizzato in "Totale" e il suo formato numerico in Contabilità (0 posizioni decimali)
Per il secondo campo "Quantità", cambia il suo nome personalizzato in "Media", la sua funzione in Media ed è il formato numerico in Contabilità (0 posizioni decimali)
Per il terzo campo "Quantità", cambia il suo nome in "Conteggio" e la sua funzione in Contare
Trascina il automaticamente creato campo da Colonne Etichette a Etichette di fila

Ecco cosa finiamo con:

Totale, media e conteggio sulla stessa tabella pivot!

Conclusione

Esistono molte, molte più funzionalità e opzioni per le tabelle pivot create da Microsoft Excel: troppe per elencarle in un articolo come questo. Per coprire completamente il potenziale di PivotTables, sarebbe necessario un piccolo libro (o un sito Web di grandi dimensioni). I lettori coraggiosi e / o geek possono esplorare le tabelle pivot in modo abbastanza semplice: basta fare clic con il pulsante destro del mouse su quasi tutto e vedere quali opzioni diventano disponibili per te. Ci sono anche le due schede della barra multifunzione: Strumenti / Opzioni tabella pivot e Design. Non importa se commetti un errore - è facile eliminare la tabella pivot e ricominciare - una possibilità che i vecchi utenti DOS di Lotus 1-2-3 non hanno mai avuto.

Se stai lavorando in Office 2007, ti consigliamo di consultare il nostro articolo su come creare una tabella pivot in Excel 2007.

Abbiamo incluso una cartella di lavoro di Excel che puoi scaricare per esercitarti sulle tue abilità di tabella pivot. Dovrebbe funzionare con tutte le versioni di Excel dalla 97 in poi.

Scarica la nostra cartella di lavoro di Excel