Le tabelle pivot sono una delle funzionalità più potenti di Excel. Anche se sei un principiante, puoi trasformare grandi quantità di dati in informazioni utili. La tabella pivot può aiutarti a creare rapporti in pochi minuti. Analizza facilmente i tuoi dati puliti e se i dati non sono puliti, può aiutarti a pulire i tuoi dati. Non voglio annoiarti, quindi saltiamoci dentro ed esploriamo.
Come creare una tabella pivot
È semplice. Basta selezionare i tuoi dati. Vai a Inserisci. Fare clic sulla tabella pivot e il gioco è fatto.
Ma aspetta. Prima di creare una tabella pivot, assicurati che tutte le colonne abbiano un'intestazione.
Se un'intestazione di colonna viene lasciata vuota, la tabella pivot non verrà creata e visualizzerà un messaggio di errore.
Requisito 1: tutte le colonne devono avere un'intestazione per iniziare con le tabelle pivot in Excel
Dovresti avere i tuoi dati organizzati con un'intestazione corretta. Una volta che lo hai, puoi inserire la tabella pivot.
Inserisci la tabella pivot dalla barra multifunzione
Per inserire una tabella pivot dal menu, segui questi passaggi:
1. Seleziona il tuo intervallo di dati
2. Vai alla scheda Inserisci
3. Fare clic sull'icona Tabella pivot.
4. Apparirà la casella di opzione Crea una tabella pivot.
5. Qui puoi vedere l'intervallo di dati che hai selezionato. Se ritieni che questo non sia l'intervallo che desideri selezionare, ridimensiona direttamente l'intervallo da qui, invece di tornare indietro e selezionare nuovamente i dati.
6. Successivamente, puoi selezionare dove vuoi la tua tabella pivot. Consiglio di utilizzare il nuovo foglio di lavoro, ma puoi anche utilizzare il foglio di lavoro corrente. Basta definire la posizione nella casella Posizione.
7. Ora, se hai finito con le impostazioni, premi il pulsante OK. Avrai la tua tabella pivot in un nuovo foglio. Basta selezionare i campi per i riepiloghi. Vedremo come creiamo un riepilogo dei dati utilizzando la tabella pivot, ma prima chiariamo le basi. In questo tutorial sulla tabella pivot di Excel, imparerai più di quanto ti aspetti.
Inserisci collegamento alla tabella pivot (Alt > N > V)
Questa è una scorciatoia da tastiera sequenziale per aprire il Crea Tabella pivot casella delle opzioni.
Premi il pulsante Alt e rilascialo. Colpire n e rilasciarlo. Colpire V e rilasciarlo. Si aprirà la casella di opzione Crea una tabella pivot.
Ora segui la procedura sopra per creare una tabella pivot in Excel.
Inserisci collegamento alla tabella pivot utilizzando il vecchio collegamento Excel (Alt > D > P)
Una cosa che mi piace di più di Microsoft Excel è che in ogni nuova versione di Excel introducono nuove funzionalità ma… non scartare le vecchie funzionalità (come ha fatto MS con win 8. È stato patetico). Questo consente ai vecchi utenti di lavorare normalmente su nuove versioni come prima lavoravano su versioni precedenti.
Se si preme in sequenza ALT, D e P sulla tastiera, Excel si aprirà per creare una procedura guidata per la tabella pivot.
Seleziona l'opzione appropriata. L'opzione selezionata nello screenshot sopra ci porterà a creare una tabella pivot come abbiamo creato prima.
Premi Invio o fai clic su Avanti se desideri controllare l'intervallo selezionato.
Premi di nuovo Invio.
Seleziona un nuovo foglio di lavoro o dove vuoi che la tua tabella pivot premi Invio. Ed è fatto.
Creare report utilizzando tabelle pivot
Ora sai come inserire una tabella pivot. Prepariamoci a creare report utilizzando le tabelle pivot in pochi minuti.
Abbiamo i dati per l'ordine stazionario.
I campi della colonna sono:
Data dell'ordine: Data dell'ordine (ovviamente)
Regione: La regione dell'ordine nel paese
Cliente: Nome del cliente (cos'altro può essere)
Articolo: Articolo ordinato
Unità: Numero di unità di un articolo ordinato
Costo unitario: Costo unitario
Totale: Costo totale dell'ordine (unità*costo unitario).
Per creare report utilizzando tabelle pivot useremo
Campi tabella pivot: Contiene l'elenco dei nomi delle colonne nei dati.
Aree pivot: Queste 4 aree vengono utilizzate per mostrare i tuoi dati in modo ordinato.
FILTRI: Inserisci qui i campi da cui desideri applicare i filtri nel tuo rapporto.
COLONNE: Inserisci qui i campi che vuoi nelle colonne del rapporto: (È meglio mostrare che spiegare)
RIGHE: Trascina i campi che vuoi mostrare come nell'immagine sopra, ho mostrato Region in ROWS.
VALORI: Seleziona un campo per ottenere Conteggio, Somma, Media, Percentuale (e molti altri) ecc. che vorrai vedere.
Ora, utilizzando le informazioni di cui sopra, abbiamo preparato questo resoconto pivot rapido che mostra da quale regione quanti ordini vengono effettuati per ciascun articolo.
Ora che hai compreso i tuoi dati e i campi Pivot (dopotutto sei intelligente), rispondiamo rapidamente ad alcune domande relative a questi dati utilizzando la tabella pivot.
Q1. Quanti ordini ci sono?
La tabella pivot è inizialmente vuota, come mostrato nell'immagine sottostante.
È necessario selezionare i campi (nomi delle colonne) nelle aree appropriate per visualizzare il riepilogo o i dettagli di quel campo.
Ora, per rispondere alla domanda precedente, selezionerò Item (seleziona una colonna qualsiasi, assicurati solo che non abbia una cella vuota in mezzo) nei campi dei valori.
Seleziona Elemento dall'elenco dei campi e trascinalo nel campo Valore.
Abbiamo la nostra risposta. Le tabelle pivot mi dicono che ci sono un totale di 43 ordini. Questo è corretto.
Ora che ho selezionato Data dell'ordine nell'area Valori, mostra 42. Questo si intende Data dell'ordine ha una cella vuota da sappiamo che il numero totale di ordini è 43.
Identifica i dati irregolari utilizzando le tabelle pivot e puliscili.
La tabella pivot può aiutarti a trovare informazioni errate nei dati.
La maggior parte delle volte i nostri dati vengono preparati da operatori di data entry o da utenti che di solito sono irregolari e necessitano di una pulizia per preparare report e analisi accurati.
Dovresti sempre pulire e preparare i tuoi dati in modo ordinato, prima di preparare qualsiasi report. Ma a volte solo dopo aver preparato il rapporto veniamo a sapere che i nostri dati hanno qualche irregolarità. Vieni, ti faccio vedere come…
Q2: Indica il numero di ordini da ciascuna regione
Ora per rispondere a questa domanda:
Selezionare Regione e trascinalo su righe Area e Articolo a Valori La zona.
Otterremo un dato diviso per regione. Possiamo rispondere da quale regione quanti ordini sono arrivati.
Ci sono un totale di 4 regioni nei nostri dati secondo la tabella pivot. Ma aspetta, nota che Centrale e Centro regione. Sappiamo che Centrle dovrebbe essere Centrale. C'è un'irregolarità. Dobbiamo andare ai nostri dati e fare la pulizia dei dati.
Per pulire i dati nel campo regione, filtriamo il nome della regione errato (Centrale) e lo correggiamo (Centrale).
Ora torna ai tuoi dati pivot.
Fare clic con il tasto destro in qualsiasi punto della tabella pivot e fare clic su Aggiorna.
Il tuo rapporto è stato aggiornato.
Ora puoi vedere che ci sono solo 3 regioni in realtà.
Formattazione del rapporto pivot con righe categorizzate.
A volte avrai bisogno di rapporti come l'immagine sopra. Ciò semplifica la visualizzazione dei dati in modo strutturato. Puoi facilmente dire da quale regione quanti articoli sono stati ordinati. Vediamo come puoi farlo.
Spostare Regione e Articolo a RIGHE la zona. Assicurati che la regione sia in alto e gli elementi in basso come mostrato nell'immagine.
Lagna Articolo per Valore La zona.
Di conseguenza otterrai questo rapporto.
Lo farà. Ma a volte il tuo capo vuole segnalare in forma tabellare senza subtotali. Per fare ciò, dobbiamo formattare la nostra tabella pivot.
Rimuovi i subtotali dalla tabella pivot
Segui questi passi:
1. Fare clic in un punto qualsiasi della tabella pivot.
2. Vai a Design tab.
3. Fare clic su i subtotali menù.
4. Fare clic su Non mostrare i subtotali.
Puoi vedere che non ci sono subtotali ora.
Bene. Ma non è ancora in forma tabellare. Le regioni e gli articoli sono mostrati in un'unica colonna. Mostrali separatamente.
Crea una tabella pivot tabulare
Ora per mostrare le regioni e gli articoli in colonne diverse, segui questi passaggi:
1. Fare clic in un punto qualsiasi della tabella pivot
2. Vai alla scheda Progettazione
3. Fare clic su Layout del rapporto.
4. Fare clic su Mostra per l'opzione Modulo tabulare. Finalmente avrai questa visione sofisticata del tuo rapporto.
Ora conosciamo il numero totale di ordini effettuati per ogni articolo da ciascuna regione. È mostrato nel Conte delle colonne Articoli.
Si prega di modificare il nome della colonna in Ordini.
Sembra meglio ora.
Q3: Quante unità di ogni articolo sono ordinate?
Per rispondere a questa domanda abbiamo bisogno di una somma di Unità. Per farlo, sposta il campo Unità su Valori. Somma automaticamente il numero di unità per ogni articolo. Se una colonna nella tabella pivot contiene solo valori, la tabella pivot per impostazione predefinita mostra la somma di tali valori. Ma può essere modificato da un'impostazione del campo del valore. Come? Ti mostrerò quest'ultimo.
Impostazioni campo valore tabella pivot
Q4: Il prezzo medio di ogni articolo?
Nei nostri dati di esempio il prezzo di un articolo è diverso per ordini diversi. Ad esempio, vedere Raccoglitori.
Voglio conoscere il costo medio di ogni articolo nella tabella pivot. Per scoprirlo, trascina Costo unitario su Campo valore. Mostrerà la somma del costo unitario.
Non vogliamo Somma del costo unitario, vogliamo Media del costo unitario. Fare così…
1. Fare clic con il tasto destro in qualsiasi punto della somma della colonna Costo unitario nella tabella pivot
2. Fare clic su Impostazioni campo valore
3. In base alle Opzioni disponibili, Selezionare Media e premi OK.
Infine, avrai questo rapporto pivot:
Campi calcolati tabella pivot
Una delle funzioni più utili della tabella pivot sono i suoi campi calcolati. I campi calcolati sono campi ottenuti da alcune operazioni sulle colonne disponibili.
Capiamo come inserire i campi calcolati nella tabella pivot con un esempio:
Sulla base dei nostri dati, abbiamo preparato questo rapporto.
Ecco, abbiamo Somma delle unità e Costo totale. Ho appena spostato la colonna totale nel campo Valori e poi l'ho rinominata in Costo totale. Ora voglio conoscere il prezzo medio di un'unità di ogni articolo per ogni regione. E sarebbe:
Prezzo medio = costo totale / unità totali
Inseriamo un campo nella tabella pivot che mostra il prezzo medio di ciascun articolo per regione:
Segui questi passaggi per inserire un campo calcolato nella tabella pivot
1. Fai clic in un punto qualsiasi della tabella pivot e vai alla scheda Analizza
2. Fare clic su Campi, elementi e set nel gruppo di calcolo.
3. Fare clic su Campi calcolati.
Vedrai questa casella di input per il tuo campo di calcolo:
4. Nella casella di immissione del nome scrivi il costo medio o qualsiasi cosa ti piaccia, a excel non dispiacerà. Nella casella di immissione della formula, scrivi e premi OK.
=Totale / Unità
Puoi scriverlo manualmente dalla tastiera oppure puoi fare doppio clic sui nomi dei campi elencati nell'area Campi per eseguire le operazioni.
5. Hai il tuo campo calcolato aggiunto ora alla tua tabella pivot. È chiamato come la somma del costo medio ma non è una somma. Excel esegue solo la funzione predefinita per denominare la colonna (come un rituale). Rinominare questa colonna e limitare le cifre decimali mostrate.
E lì hai un campo calcolato. Puoi renderlo complesso quanto vuoi. Per fare un esempio ho preso questa semplice operazione di media.
Raggruppamento nella tabella pivot
Hai preparato questo rapporto pivot.
Ora voglio che questo rapporto venga diviso annualmente. Guarda l'istantanea qui sotto.
Abbiamo una colonna sulla data dell'ordine. Sposta il campo OrderDate su Righe in alto.
Non assomiglia per niente al rapporto richiesto. Abbiamo bisogno di appuntamenti di gruppo per anno.
Ora per raggruppare un campo nella tabella pivot di Excel, segui questi passaggi:
1. Fare clic con il tasto destro sul campo che si desidera raggruppare.
2. Fare clic su Gruppo. Avrai questa casella di opzione per la personalizzazione. Poiché si tratta di una colonna di dati, Excel ci mostra il raggruppamento di conseguenza. Puoi scegliere la data di inizio e di fine.
3. Scegli per anni e premi OK. Hai eseguito il raggruppamento annuale nella tabella pivot di Excel.
Affettatrici di Tavola Pivot
I filtri dei dati sono stati introdotti in Excel 2010 come componente aggiuntivo. In Excel 2013 e 2016 è disponibile per impostazione predefinita proprio come i filtri.
Le affettatrici non sono altro che filtri. A differenza di Filtri, Slicers mostra tutte le opzioni disponibili proprio di fronte a te. Rende la tua dashboard più interattiva.
Come aggiungere affettatrici nella tabella pivot, Excel 2016 e 2013
Pivot Table Slicers è facile da aggiungere. Segui questi passi:
1. Fare clic in un punto qualsiasi della tabella pivot e andare alla scheda Analizza.
2. Fare clic su Inserisci affettatrice. Avrai un elenco di campi per i tuoi dati. Seleziona quanti ne vuoi.
3. Per questo esempio, seleziona Regione e premi OK.
Hai aggiunto Slicer al tuo rapporto. Ora applica il filtro con un solo clic.
Inserisci sequenza temporale in Excel 2016 e 2013
Questa è una delle mie funzionalità preferite Tabelle pivot di Excel. Questa nuova funzionalità funziona solo con le date. Usando questo, puoi selezionare visivamente un intervallo di tempo per filtrare i tuoi dati.
Per inserire un cursore Timeline, segui questi passaggi:
Come aggiungere una sequenza temporale nella tabella pivot, Excel 2016 e 2013
1. Fare clic in un punto qualsiasi della tabella pivot e andare alla scheda Analizza.
2. Fare clic su Inserisci sequenza temporale dal gruppo di filtri. Tutte le colonne contenenti valori temporali nei dati di origine verranno elencate in una casella di opzioni tra cui scegliere. Qui ne abbiamo solo uno. Quindi sì…
2. Scegli le tue opzioni e premi Invio o fai clic su OK. È fatto e hai la timeline della tabella pivot proprio di fronte a te.
Puoi scegliere di mostrarlo giornalmente, mensilmente, trimestralmente o annualmente. Ho scelto Mensile qui.
In questo articolo ho trattato le funzionalità più importanti e utili della tabella pivot. Abbiamo esplorato le nuove funzionalità della tabella pivot in Excel 2016 e 2013. Abbiamo appreso l'uso classico di una tabella pivot eseguita in Excel 2007, 2010 e versioni precedenti. Sono ancora utili. Se non hai trovato la tua risposta relativa alla tua tabella pivot qui, chiedi nella sezione commenti.
Ci sono molte altre funzioni che devono ancora essere spiegate. Impariamo la funzione avanzata della tabella pivot nel prossimo articolo. Fino ad allora Excel in tutto.