Come creare tabelle pivot in Excel

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 della 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 della 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à e ma non scartano le vecchie funzionalità (come ha fatto MS con win 8. È stato patetico). Questo consente all'utente più vecchio di lavorare normalmente su nuove versioni come prima lavorava 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.

Crea report utilizzando le 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).

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: Metti 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 di cui sopra, selezionerò Articolo (seleziona una colonna qualsiasi, assicurati solo che non abbia una cella vuota in mezzo) nei campi dei valori.

Selezionare Articolo dall'elenco dei campi e trascinarlo nel campo Valore.

Abbiamo la nostra risposta. Le tabelle pivot mi dicono che ci sono un totale di 43 ordini. Questo è corretto.
Suggerimento professionale: Dovresti controllare i tuoi dati se sono corretti o meno. Se questo numero non corrisponde ai dati, significa che hai selezionato un intervallo errato o che il campo contiene celle vuote.
Informazioni: Il campo valore per impostazione predefinita conta il numero di voci in una colonna se contiene testo e somma se il campo contiene solo valori. Puoi modificarlo nelle impostazioni del campo del valore. Come? Ci vediamo più avanti in questo tutorial sulla tabella pivot.
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.
INFORMAZIONI: Indipendentemente dalle modifiche apportate ai dati di origine, la tabella pivot continuerà a funzionare sui vecchi dati finché non lo aggiorni. Excel crea una cache pivot e una tabella pivot viene eseguita su tale cache. Quando viene aggiornata, la vecchia cache viene modificata con dati nuovi.

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 sul menu Subtotali.

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 Regioni e 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 la forma tabulare opzione. 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 di articoli colonne.
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ì…

Fare clic con il tasto destro in qualsiasi punto della somma della colonna Costo unitario nella tabella pivot
Clic Impostazioni campo valore
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. Fare clic in un punto qualsiasi della tabella pivot e andare su 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 inserimento del nome scrivi la Media Costo o qualsiasi cosa ti piaccia, Excel non ti dispiacerà. Nel formula casella di input, scrivere e premere ok.