Contenuti
Tabelle pivot è uno degli strumenti più potenti di Excel. Consentono di analizzare e riassumere vari riepiloghi di grandi quantità di dati con pochi clic del mouse. In questo articolo conosceremo le tabelle pivot, capiremo cosa sono, impareremo come crearle e personalizzarle.
Questo articolo è stato scritto utilizzando Excel 2010. Il concetto di tabelle pivot non è cambiato molto nel corso degli anni, ma il modo in cui le crei è leggermente diverso in ogni nuova versione di Excel. Se hai una versione di Excel non 2010, preparati che gli screenshot in questo articolo differiranno da quelli che vedi sullo schermo.
Un po 'di storia
Agli albori del software per fogli di calcolo, la palla regola Lotus 1-2-3. Il suo dominio era così completo che gli sforzi di Microsoft per sviluppare il proprio software (Excel) come alternativa a Lotus sembravano una perdita di tempo. Ora avanti veloce al 2010! Excel domina i fogli di calcolo più di quanto il codice Lotus abbia mai fatto nella sua storia e il numero di persone che usano ancora Lotus è vicino allo zero. Come è potuto accadere? Qual è stato il motivo di una svolta così drammatica degli eventi?
Gli analisti identificano due fattori principali:
- In primo luogo, Lotus decise che questa nuova piattaforma GUI chiamata Windows era solo una moda passeggera che non sarebbe durata a lungo. Si rifiutarono di creare una versione Windows di Lotus 1-2-3 (ma solo per pochi anni), prevedendo che la versione DOS del loro software sarebbe stata tutto ciò di cui i consumatori avrebbero mai avuto bisogno. Microsoft naturalmente ha sviluppato Excel appositamente per Windows.
- In secondo luogo, Microsoft ha introdotto uno strumento in Excel chiamato tabelle pivot che non era disponibile in Lotus 1-2-3. Le tabelle pivot, esclusive di Excel, si sono rivelate così estremamente utili che le persone tendevano a rimanere con la nuova suite di software Excel piuttosto che continuare con Lotus 1-2-3, che non le aveva.
Le tabelle pivot, oltre a sottovalutare il successo di Windows in generale, hanno giocato la marcia della morte per Lotus 1-2-3 e hanno inaugurato il successo di Microsoft Excel.
Cosa sono le tabelle pivot?
Quindi, qual è il modo migliore per caratterizzare cosa sono le tabelle pivot?
In parole povere, le tabelle pivot sono riepiloghi di alcuni dati, creati per facilitare l'analisi di questi dati. A differenza dei totali creati manualmente, le tabelle pivot di Excel sono interattive. Una volta creati, puoi modificarli facilmente se non danno l'immagine che speravi. Con solo un paio di clic del mouse, i totali possono essere capovolti in modo che le intestazioni di colonna diventino intestazioni di riga e viceversa. Puoi fare molte cose con le tabelle pivot. Invece di cercare di descrivere a parole tutte le caratteristiche delle tabelle pivot, è più facile dimostrarlo in pratica...
I dati che analizzi con le tabelle pivot non possono essere casuali. Dovrebbero essere dati grezzi grezzi, come un elenco di qualche tipo. Ad esempio, potrebbe essere un elenco delle vendite effettuate dall'azienda negli ultimi sei mesi.
Osserva i dati mostrati nella figura seguente:
Si noti che non si tratta di dati grezzi grezzi, poiché è già stato riepilogato. Nella cella B3 vediamo $ 30000, che è probabilmente il risultato totale che James Cook ha ottenuto a gennaio. Dove sono i dati originali allora? Da dove viene la cifra di $ 30000? Dov'è l'elenco originale delle vendite da cui è stato ricavato questo totale mensile? È chiaro che qualcuno ha fatto un ottimo lavoro nell'organizzare e ordinare tutti i dati di vendita degli ultimi sei mesi e trasformarli nella tabella dei totali che vediamo. Quanto tempo pensi ci sia voluto? Ora? Dieci?
Il fatto è che la tabella sopra non è una tabella pivot. È stato realizzato a mano da dati grezzi archiviati altrove e l'elaborazione ha richiesto almeno un paio d'ore. Solo una tabella di riepilogo di questo tipo può essere creata utilizzando tabelle pivot in pochi secondi. Scopriamo come...
Se torniamo all'elenco di vendita originale, assomiglierebbe a questo:
Potresti essere sorpreso dal fatto che da questo elenco di operazioni con l'aiuto delle tabelle pivot e in pochi secondi, possiamo creare un rapporto mensile sulle vendite in Excel, che abbiamo analizzato sopra. Sì, possiamo fare questo e altro!
Come creare una tabella pivot?
Innanzitutto, assicurati di avere alcuni dati di origine in un foglio Excel. L'elenco delle transazioni finanziarie è il più tipico che si verifica. In effetti, potrebbe essere un elenco di qualsiasi cosa: i dettagli di contatto dei dipendenti, una raccolta di CD o i dati sul consumo di carburante della tua azienda.
Quindi, avviamo Excel ... e carichiamo un elenco del genere ...
Dopo aver aperto questo elenco in Excel, possiamo iniziare a creare una tabella pivot.
Seleziona una cella da questo elenco:
Poi sulla scheda Inserimento (Inserisci) seleziona il comando Tabella pivot (Tabella pivot):
Apparirà una finestra di dialogo Crea tabella pivot (Creazione di una tabella pivot) con due domande per te:
- Quali dati utilizzare per creare una nuova tabella pivot?
- Dove mettere la tabella pivot?
Poiché nel passaggio precedente abbiamo già selezionato una delle celle dell'elenco, l'intero elenco verrà selezionato automaticamente per creare una tabella pivot. Si noti che è possibile selezionare un intervallo diverso, una tabella diversa e persino un'origine dati esterna come una tabella di database di Access o MS-SQL. Inoltre, dobbiamo scegliere dove posizionare la nuova tabella pivot: su un nuovo foglio o su uno di quelli esistenti. In questo esempio, sceglieremo l'opzione – Nuovo foglio di lavoro (a un nuovo foglio):
Excel creerà un nuovo foglio e vi posizionerà una tabella pivot vuota:
Non appena facciamo clic su qualsiasi cella nella tabella pivot, apparirà un'altra finestra di dialogo: Elenco campi tabella pivot (Campi della tabella pivot).
L'elenco dei campi nella parte superiore della finestra di dialogo è un elenco di tutti i titoli dell'elenco originale. Le quattro aree vuote nella parte inferiore dello schermo consentono di indicare alla tabella pivot come si desidera riepilogare i dati. Finché queste aree sono vuote, non c'è nulla nella tabella. Tutto quello che dobbiamo fare è trascinare i titoli dall'area superiore alle aree vuote sotto. Allo stesso tempo, viene generata automaticamente una tabella pivot, secondo le nostre istruzioni. Se commettiamo un errore, possiamo rimuovere le intestazioni dall'area inferiore o trascinarne altre per sostituirle.
Zona Valori (Significato) è probabilmente il più importante dei quattro. Quale intestazione viene inserita in quest'area determina quali dati verranno riepilogati (somma, media, massimo, minimo, ecc.). Si tratta quasi sempre di valori numerici. Un ottimo candidato per un posto in quest'area sono i dati sotto l'intestazione Quantità (Costo) del nostro tavolo originale. Trascina questo titolo nell'area Valori (I valori):
Si prega di notare che il titolo Quantità è ora contrassegnato da un segno di spunta e nell'area Valori (Valori) è apparsa una voce Somma dell'importo (Campo Importo Importo), indicando che la colonna Quantità riassunto.
Se osserviamo la tabella pivot stessa, vedremo la somma di tutti i valori dalla colonna Quantità tavola originale.
Quindi, viene creata la nostra prima tabella pivot! Comodo, ma non particolarmente suggestivo. Probabilmente vorremmo ottenere più informazioni sui nostri dati di quelle che abbiamo attualmente.
Passiamo ai dati originali e proviamo a identificare una o più colonne che possono essere utilizzate per dividere questa somma. Ad esempio, possiamo formare la nostra tabella pivot in modo tale che l'importo totale delle vendite venga calcolato individualmente per ciascun venditore. Quelli. le righe verranno aggiunte alla nostra tabella pivot con il nome di ogni venditore nell'azienda e l'importo totale delle vendite. Per ottenere questo risultato, basta trascinare il titolo venditore (rappresentante di vendita) alla regione Etichette di riga (Stringhe):
Diventa più interessante! La nostra tabella pivot inizia a prendere forma...
Vedi i vantaggi? In un paio di clic abbiamo creato una tabella che avrebbe richiesto molto tempo per essere creata manualmente.
Cos'altro possiamo fare? Bene, in un certo senso, la nostra tabella pivot è pronta. Abbiamo creato un utile riepilogo dei dati originali. Informazioni importanti già ricevute! Nel resto di questo articolo, esamineremo alcuni modi per creare tabelle pivot più complesse e impareremo come personalizzarle.
Configurazione tabella pivot
Innanzitutto, possiamo creare una tabella pivot bidimensionale. Facciamolo usando l'intestazione della colonna Metodo di pagamento (Metodo di pagamento). Basta trascinare il titolo Metodo di pagamento alla zona Etichette di colonna (Colonne):
Otteniamo il risultato:
Sembra molto bello!
Ora creiamo una tabella tridimensionale. Come sarebbe un tavolo del genere? Vediamo…
Trascina intestazione CONFEZIONE (Complesso) alla zona filtri di report (Filtri):
Nota dove si trova...
Questo ci dà l'opportunità di filtrare il rapporto sulla base di "Per quale complesso turistico è stato pagato". Ad esempio, possiamo vedere una suddivisione per venditori e per modalità di pagamento per tutti i complessi, oppure in un paio di clic del mouse, cambiare la visualizzazione della tabella pivot e mostrare la stessa suddivisione solo per chi ha ordinato il complesso Cercatori di sole.
Quindi, se lo capisci correttamente, la nostra tabella pivot può essere chiamata tridimensionale. Continuiamo a configurare...
Se all'improvviso si scopre che solo il pagamento tramite assegno e carta di credito (ovvero il pagamento senza contanti) deve essere visualizzato nella tabella pivot, possiamo disattivare la visualizzazione del titolo Contanti (Contanti). Per questo, accanto a Etichette di colonna fare clic sulla freccia giù e deselezionare la casella nel menu a discesa Contanti:
Vediamo ora come appare la nostra tabella pivot. Come puoi vedere, la colonna Contanti scomparso da lei.
Formattazione di tabelle pivot in Excel
Le tabelle pivot sono ovviamente uno strumento molto potente, ma finora i risultati sembrano un po' semplici e noiosi. Ad esempio, i numeri che sommiamo non sembrano importi in dollari: sono solo numeri. Risolviamo questo problema.
Si è tentati di fare ciò a cui si è abituati in una situazione del genere e selezionare semplicemente l'intera tabella (o l'intero foglio) e utilizzare i pulsanti di formattazione dei numeri standard sulla barra degli strumenti per impostare il formato desiderato. Il problema con questo approccio è che se in futuro si modifica la struttura della tabella pivot (cosa che si verifica con una probabilità del 99%), la formattazione andrà persa. Quello di cui abbiamo bisogno è un modo per renderlo (quasi) permanente.
Per prima cosa, troviamo la voce Somma dell'importo in Valori (Valori) e fare clic su di esso. Nel menu visualizzato, seleziona la voce Impostazioni campo valore (Opzioni campo valore):
Apparirà una finestra di dialogo Impostazioni campo valore (Opzioni campo valore).
Fare clic sul pulsante Formato numero (Formato numero), si aprirà una finestra di dialogo. formato celle (formato cella):
Dalla lista Categoria (Formati numerici) selezionare Contabilità (Finanziario) e impostare il numero di cifre decimali su zero. Ora premi un paio di volte OKper tornare alla nostra tabella pivot.
Come puoi vedere, i numeri sono formattati come importi in dollari.
Mentre ci siamo con la formattazione, impostiamo il formato per l'intera tabella pivot. Ci sono diversi modi per farlo. Usiamo quello più semplice...
Clicca su Strumenti tabella pivot: progettazione (Lavorare con le tabelle pivot: Costruttore):
Successivamente, espandi il menu facendo clic sulla freccia nell'angolo in basso a destra della sezione Stili di tabella pivot (Stili di tabella pivot) per vedere l'ampia raccolta di stili in linea:
Scegli uno stile adatto e guarda il risultato nella tua tabella pivot:
Altre impostazioni della tabella pivot in Excel
A volte è necessario filtrare i dati in base alle date. Ad esempio, nel nostro elenco di operazioni ci sono molte, molte date. Excel fornisce uno strumento per raggruppare i dati per giorno, mese, anno e così via. Vediamo come è fatto.
Per prima cosa rimuovi la voce. Metodo di pagamento dalla regione Etichette di colonna (Colonne). Per fare ciò, trascinalo di nuovo nell'elenco dei titoli e, al suo posto, sposta il titolo Data prenotata (data di prenotazione):
Come puoi vedere, questo ha temporaneamente reso inutilizzabile la nostra tabella pivot. Excel ha creato una colonna separata per ogni data in cui è stato effettuato uno scambio. Di conseguenza, abbiamo ottenuto un tavolo molto ampio!
Per risolvere questo problema, fai clic con il pulsante destro del mouse su una data qualsiasi e seleziona dal menu di scelta rapida GRUPPO (Gruppo):
Verrà visualizzata la finestra di dialogo di raggruppamento. Noi scegliamo Mesi (Mesi) e fare clic OK:
Ecco! Questa tabella è molto più utile:
A proposito, questa tabella è quasi identica a quella mostrata all'inizio dell'articolo, dove i totali delle vendite sono stati compilati manualmente.
C'è un altro punto molto importante che devi sapere! Puoi creare non uno, ma diversi livelli di intestazioni di riga (o colonna):
… e sembrerà così …
Lo stesso può essere fatto con le intestazioni di colonna (o anche con i filtri).
Torniamo alla forma originale della tabella e vediamo come visualizzare le medie invece delle somme.
Per iniziare, fai clic su Somma dell'importo e dal menu che compare selezionare Impostazioni campo valore (Opzioni campo valore):
La lista Riepiloga il campo del valore per (Operazione) nella finestra di dialogo Impostazioni campo valore (Opzioni campo valore) selezionare Media (Media):
Allo stesso tempo, mentre siamo qui, cambiamo Nome personalizzato (Nome personalizzato) con Importo medio (Campo Importo Importo) a qualcosa di più breve. Inserisci in questo campo qualcosa di simile Avg:
Rassegna Stampa OK e guarda cosa succede. Nota che tutti i valori sono cambiati da totali a medie e l'intestazione della tabella (nella cella in alto a sinistra) è cambiata in Avg:
Se lo desideri, puoi ottenere immediatamente l'importo, la media e il numero (vendite) inseriti in una tabella pivot.
Ecco una guida passo passo su come farlo, partendo da una tabella pivot vuota:
- Trascina intestazione venditore (rappresentante di vendita) alla regione Etichette di colonna (Colonne).
- Trascina il titolo tre volte Quantità (Costo) per area Valori (I valori).
- Per il primo campo Quantità cambia il titolo in Totale (Importo) e il formato del numero in questo campo è Contabilità (Finanziario). Il numero di cifre decimali è zero.
- Secondo campo Quantità Nome mediae, impostare l'operazione per esso Media (Media) e anche il formato numerico in questo campo cambiano in Contabilità (Finanziario) con zero cifre decimali.
- Per il terzo campo Quantità impostare un titolo Contare e un'operazione per lui - Contare (Quantità)
- Nel Etichette di colonna (Colonne) creato automaticamente Σ Valori (Σ Valori) – trascinalo nell'area Etichette di riga (Linee)
Ecco cosa finiremo con:
Importo totale, valore medio e numero di vendite: tutto in un'unica tabella pivot!
Conclusione
Le tabelle pivot in Microsoft Excel contengono molte funzionalità e impostazioni. In un articolo così piccolo, non sono nemmeno vicini a coprirli tutti. Ci vorrebbe un piccolo libro o un grande sito web per descrivere completamente tutte le possibilità delle tabelle pivot. I lettori audaci e curiosi possono continuare la loro esplorazione delle tabelle pivot. Per fare ciò, fai semplicemente clic con il pulsante destro del mouse su quasi tutti gli elementi della tabella pivot e guarda quali funzioni e impostazioni si aprono. Sulla barra multifunzione troverai due schede: Strumenti tabella pivot: Opzioni (analisi) e Design (Costruttore). Non aver paura di sbagliare, puoi sempre eliminare la tabella pivot e ricominciare da capo. Hai un'opportunità che gli utenti di lunga data di DOS e Lotus 1-2-3 non hanno mai avuto.