Tabelle pivot di Excel

Gli utenti utilizzano raramente le tabelle pivot di Excel a causa della complessità apparente. Tuttavia, lo sforzo e il tempo che saranno spesi per impararli ripagherà enormemente dall’utilizzo di questo strumento. Le tabelle pivot sono spesso indispensabili quando si analizzano i dati di tabelle molto grandi in vari aspetti. Quindi, le tabelle pivot dovrebbero essere utilizzate se esiste un registro (riepilogo, dichiarazione, ecc.) Con centinaia di righe e devi analizzare i dati con la loro selezione e raggruppamento e presentare questi dati in tutti i tipi di sezioni per creare rapporti.
Preparazione della tabella di origine
Crea tabelle pivot in Excel
Come aggiungere campi a una tabella pivot
Ordinamento della tabella pivot
Filtra nella tabella pivot
Formule nelle tabelle pivot
Campi calcolati nelle tabelle pivot
Oggetti calcolati nelle tabelle pivot
Nascondi i subtotali e i totali complessivi della tabella pivot

Preparazione della tabella di origine

Prima di iniziare a creare una tabella pivot, devi lavorare sulla tabella originale. Il fatto è che non tutte le strutture dati sono adatte per creare una tabella pivot basata su di esse.

  1. La tabella di origine non deve contenere colonne vuote vuote. È impossibile generare una tabella pivot corretta se almeno una colonna nell’origine non è intitolata.
  2. La tabella originale non deve contenere celle vuote nella riga del valore, altrimenti la tabella pivot conterà il numero, non la somma dei valori delle celle.
  3. La tabella di origine non deve contenere righe o colonne vuote. Altrimenti, quando si crea una tabella pivot, verranno presi in considerazione solo i dati prima della prima colonna vuota o della prima riga vuota. Se vuoi che la colonna appaia vuota, lascia visibile l’intestazione della colonna e rendi il testo nelle celle dello stesso colore dello sfondo delle celle.
  4. Ogni colonna deve contenere dati dello stesso formato. Ad esempio, una colonna con dati in formato testo non deve contenere dati di tipo DATE.
  5. Crea una colonna separata per i dati dello stesso tipo che verranno inseriti nella tabella pivot. Ad esempio, puoi impostare le colonne “Mese” e “Anno”. Ciò ti consentirà di visualizzare i dati complessivi nella tabella pivot per mese o per anno.
  6. Si consiglia di posizionare la tabella con i dati di origine su un foglio della cartella di lavoro di Excel e la tabella pivot su un altro. In ogni caso, la tabella originale deve essere separata dal resto dei dati, ad esempio altre tabelle, da almeno una colonna e almeno una riga.
  7. Se la tabella di origine contiene colonne nascoste, assicurati che tutte abbiano intestazioni.
  8. È necessario rimuovere i totali dalla tabella di origine. Altrimenti, Excel prenderà il risultato finale come il valore della cella della colonna specificata e i dati nella tabella pivot verranno raddoppiati.

Dopo che l’origine dati è stata rivista e regolata secondo necessità, puoi iniziare a creare la tabella pivot.

Crea tabelle pivot in Excel

La tabella pivot è uno strumento per una facile analisi dei dati. Il processo di creazione è più semplice da spiegare con un esempio. Quindi, c’è una tabella delle vendite di merci che indica il nome del prodotto, la data di vendita e l’importo per il quale questo o quel prodotto è stato venduto.  Crea tavolo pivot

  1. Seleziona l’intera tabella, una singola cella o un intervallo di celle, a seconda delle informazioni che devi analizzare.
  2. Vai alla scheda “Insert” e fai clic sul pulsante “PivotTable”. Inserisci tabella pivot
  3. Nella finestra “Create PivotTable” visualizzata, specifica l’origine dati. Se la tabella o le celle sono già state selezionate, non è necessario specificare nulla. Nella stessa finestra, è necessario specificare dove verrà visualizzata la tabella pivot, sullo stesso foglio o su un nuovo foglio. Aggiungi tabella pivot
  4. Dopo aver fatto clic su “OK”, verrà creato un layout di tabella pivot vuoto. Tabella pivot di Excel
  5. Sul lato destro del foglio c’è un blocco con un elenco di campi. In questo blocco, seleziona i campi che dovrebbero essere inclusi nel rapporto. I campi possono anche essere trascinati con il mouse nella parte desiderata della tabella pivot.  Utilizzo della tabella pivot
  6. Due nuove schede appariranno nel menu in alto: “Options” e “Design”. Opzioni Tabella pivot

Dopo aver creato il layout iniziale della tabella pivot, è necessario iniziare a personalizzarlo per ottenere il report del tipo desiderato.

Come aggiungere campi a una tabella pivot

I campi nella tabella pivot vengono aggiunti nel blocco “PivotTable Field List”. Elenco campi tabella pivot
Se i campi in questo blocco sono semplicemente controllati, Excel li inserirà nella tabella secondo il suo algoritmo di presentazione. I campi non numerici vengono aggiunti all’area Righe, i campi numerici all’area Valori e la data e l’ora vengono aggiunte all’area Colonne.  Aggiungi campi
Per posizionare un campo nell’area desiderata della tabella, fare clic con il pulsante destro del mouse sul suo nome e selezionare una delle sezioni: “Add to Report Filter”, “Add to Row Labels”, “Add to Column Labels” o “Add to Values”.

I campi selezionati possono essere scambiati trascinandoli con il mouse nella sezione aree.  Aggiungi a filtro report
La sezione dell’area è composta da 4 blocchi.

  1. Report Filter. Questo blocco specifica i filtri necessari per i valori della tabella pivot.
  2. Column Labels. Questa sezione inserisce i valori specificati del campo selezionato in colonne. Di regola, si tratta di valori qualitativi piuttosto che quantitativi.
  3. Line names. Questi sono i nomi delle righe dalla colonna più a sinistra. È possibile creare una tabella pivot multilivello inserendo più campi in quest’area. I valori qualitativi vengono solitamente inseriti anche qui, ad esempio anno o mese, nome del prodotto e così via.
  4. Values. Questa è la parte principale della tabella pivot. Ecco i valori che saranno ottenuti con il metodo selezionato. Molto spesso, i risultati della somma vengono inseriti qui.

Ordinamento della tabella pivot

Come con qualsiasi tabella di Excel, è possibile ordinare una tabella pivot. Questa selezione può essere eseguita per riga o per colonna. È possibile distribuire i dati in ordine alfabetico o in ordine crescente e decrescente.

Per ordinare i dati, è necessario selezionare il loro intervallo, quindi andare alla scheda “Data” del menu principale.  Ordinamento della tabella pivot
Per distribuire i dati nella direzione desiderata, premere le frecce su o giù nel blocco “Sort & Filter”. Si consiglia inoltre di utilizzare il pulsante “Sort”. Dopo aver fatto clic su di esso, verrà visualizzata una finestra di contesto con i parametri di ordinamento dei dati. C’è un pulsante simile nella scheda “Design” del blocco “PivotTable Tools”.

C’è un altro modo per ordinare i dati in una tabella pivot. Se è presente un pulsante triangolo nelle celle “Row Labels” o “Column Labels”, è possibile fare clic su di esso e visualizzare il menu contestuale. In esso, selezionare il parametro di ordinamento desiderato e fare clic su “OK”. Ordina i dati in una tabella pivot
Se sono presenti celle con spazi iniziali nell’intervallo ordinato, ciò potrebbe influire sui risultati dell’ordinamento. Tali spazi dovrebbero essere rimossi in anticipo.

Non è possibile ordinare i dati in base al caso della lingua impostata, ordinare in base al colore di sfondo delle celle, al carattere selezionato e ad altre opzioni di formattazione.

Le tabelle pivot hanno la capacità di ordinare le celle manualmente. Per fare ciò, fare clic su una cella della colonna da ordinare. Quindi fare clic sul triangolo nel campo “Row Labels” e selezionare “More Sort Options”. Altre opzioni di ordinamento
Apparirà una finestra con una scelta di diversi parametri.  Parametri diversi
Nella stessa finestra, seleziona il parametro “Manual” e ordina le celle trascinandole nella posizione desiderata. Le eccezioni sono i campi dell’intervallo. Non puoi trascinarli.

Nella stessa finestra di dialogo è possibile fare clic sul pulsante “More Options” e impostare ulteriori opzioni di ordinamento. Ordinamento manuale
Qui puoi abilitare o disabilitare l’ordinamento automatico dei dati dopo ogni aggiornamento della tabella, nonché impostare altri parametri di ordinamento.

Filtra nella tabella pivot

Una tabella pivot può avere un numero enorme di colonne e righe. A volte queste informazioni sono ridondanti ed è necessario estrarre solo una parte di questo array di dati senza modificare il contenuto della tabella pivot. I filtri offrono questa opportunità. Le informazioni visualizzate utilizzando i filtri possono essere modificate in qualsiasi momento e per diversi utenti. In questo caso, la tabella pivot rimane invariata, cambia solo la visibilità dei dati in essa contenuti.

Per inserire un filtro in una tabella pivot, segui questi passaggi.

  1. Apri la tabella pivot e individua il blocco “Report Filter” nell’angolo inferiore destro. Filtro report
  2. Dall’elenco dei campi della tabella pivot, trascinare l’intestazione del campo richiesto nel blocco del filtro. Blocco filtro
  3. Sopra la tabella pivot verrà visualizzato un elenco di tutti i campi su cui è impostato il filtro. Fare clic sul pulsante triangolo accanto al nome del campo e selezionare l’opzione di filtro desiderata.  Opzioni di filtro
  4. Fare clic su “OK”.

È possibile impostare un filtro su più elementi della tabella. Ad esempio, filtra il numero di vendite per le date specificate.

Se fai clic sul triangolo accanto al nome del campo nella tabella pivot, viene visualizzato un menu contestuale, che contiene le voci “Label Filters”, “Value Filters”, “Date Filters”. Filtri etichetta
Organizzano gruppi di record che corrispondono a determinati criteri. Utilizzando i filtri per firma, è possibile filtrare i dati in base a criteri specificati del valore di testo. Ad esempio, nell’elenco degli articoli di cancelleria, seleziona i prodotti che contengono la parola “matita” nel loro nome. Qui puoi anche specificare caratteri jolly nei parametri del filtro.

I filtri di valore ordinano i dati numerici. Ad esempio, utilizzandoli, puoi visualizzare lo stesso tipo di prodotti con le vendite più elevate.

I filtri data possono essere selezionati solo per i campi Formato data. Questa sezione presenta un’ampia selezione di filtri di dati basati su una data specifica. Ad esempio, “Next Month”, “Last Quarter”, “This Week”. Puoi filtrare i dati in un intervallo di date.

È possibile impostare più filtri per lo stesso campo di tabella pivot.

Formule nelle tabelle pivot

Le tabelle pivot offrono l’opportunità non solo di visualizzare i dati in diverse sezioni. È inoltre possibile eseguire una serie di calcoli su queste tabelle utilizzando le formule di Excel. Ad esempio, utilizzando tali calcoli, è possibile calcolare il reddito medio giornaliero di un’azienda o calcolare la percentuale di vendite di prodotti per diverse regioni. Le tabelle pivot utilizzano i concetti “Calculated Field” e “Calculated Item” durante l’esecuzione di tali calcoli.

Campi calcolati nelle tabelle pivot

Viene creato un campo calcolato in base ai calcoli eseguiti sui campi esistenti della tabella pivot. La colonna con i campi calcolati non è inclusa nei dati di origine e contiene i risultati dei calcoli utilizzando le formule di Excel. La colonna contenente i dati recuperati dal campo calcolato diventa parte della tabella pivot e i dati possono interagire con i dati della tabella pivot.

L’inserimento di campi calcolati in una tabella pivot anziché in un’origine dati è spesso la soluzione migliore. Con questo metodo di calcolo, non è necessario modificare le formule quando si modifica l’origine dati, poiché la tabella pivot viene regolata automaticamente. Inoltre, in questo caso, è possibile modificare la struttura della tabella originale o modificare i campi dati utilizzati nei calcoli e allo stesso tempo assicurarsi che non vi siano errori nelle formule.

I calcoli nei campi calcolati vengono sempre eseguiti sui totali, non sui singoli elementi di dati.

Come si crea un campo calcolato?

  1. Fare clic sulla tabella pivot per attivare le schede “PivotTable Tools”.
  2. Nella scheda “Options” fare clic sul pulsante “Formulas”. Formule nelle tabelle pivot
  3. Apri la sezione “Calculated Field”.
  4. Nella finestra “Insert Calculated Field” che appare, specifica il nome del campo calcolato e scrivi la formula richiesta per questo campo. Inserisci campo calcolato
  5. Fare clic su “OK”.

Per impostazione predefinita, il campo “Formula” contiene l’espressione “= 0”. Prima di scrivere la formula, è necessario rimuovere lo zero. Quando si crea una formula, i suoi elementi vengono selezionati dal blocco “Fields”. Quando crei un campo calcolato, la colonna aggiuntiva non viene visualizzata nell’origine dati. Tali campi imitano semplicemente i campi specificati esplicitamente dalla tabella originale.

I parametri del campo calcolato che crei possono essere modificati allo stesso modo degli altri elementi della tabella pivot. Puoi modificare il nome del campo, il colore, il carattere e altro ancora. Sebbene la formula utilizzata per creare un campo calcolato sia simile a una formula Excel standard, esiste una differenza fondamentale tra le due. Una formula standard di Excel utilizza forti riferimenti di cella o valori numerici, mentre le formule nelle tabelle pivot utilizzano riferimenti ai campi di dati della tabella pivot per il calcolo.

Oggetti calcolati nelle tabelle pivot

Un oggetto calcolato è essenzialmente una riga virtuale che viene aggiunta a una tabella eseguendo calcoli su altre righe nello stesso campo. Ad esempio, potrebbe essere la media delle vendite calcolate per un determinato periodo. Spesso puoi ottenere lo stesso risultato raggruppando i dati.

Come si crea un oggetto calcolato?

  1. Fare clic su una cella qualsiasi del campo selezionato e accedere alla scheda “Options” nel blocco “PivotTable Tools”.
  2. Fare clic sul pulsante “Formulas” e selezionare la sezione “Calculated Item”. Articolo calcolato
  3. Nella finestra che compare, scrivi il nome dell’oggetto calcolato, seleziona i campi e gli elementi richiesti, redige una formula di calcolo. Formula di calcolo
  4. Dopo aver fatto clic su “OK”, le righe con l’elemento calcolato vengono aggiunte alla tabella pivot.

Si noti che gli oggetti calcolati possono influire sui totali parziali e sui totali complessivi nella tabella pivot. Nascondi questi elementi di dati per ottenere i totali corretti. Inoltre, quando si calcolano i campi e gli oggetti calcolati, non è possibile utilizzare riferimenti di cella o intervalli denominati. Di conseguenza, non è possibile utilizzare funzioni che accettano riferimenti di cella o oggetti con nome come argomenti. Allo stesso tempo, le funzioni COUNT, AVERAGE, IF, AND, NO, OR possono essere utilizzate liberamente. Qualsiasi costante può essere utilizzata nei calcoli.

Le formule delle tabelle pivot dovrebbero tenere conto dell’ordine dei calcoli e della priorità di alcune operazioni rispetto ad altre.

È possibile modificare o persino eliminare le formule create nei campi o negli oggetti calcolati. Per fare ciò, fai clic su uno dei campi della tabella pivot, fai clic sul pulsante “Formulas” e vai alla sezione “Calculated field”. Fare clic sulla freccia accanto al nome del campo e visualizzare un elenco di tutti i campi calcolati. Campo calcolato
Seleziona il campo di cui desideri modificare la formula e correggi o elimina la formula. Negli oggetti calcolati, le formule vengono modificate ed eliminate allo stesso modo.

Nascondi i subtotali e i totali complessivi della tabella pivot

Se la tabella pivot contiene somme di valori, verranno automaticamente visualizzati i totali parziali e i totali complessivi. È possibile nascondere questi totali e mostrarli di nuovo in un secondo momento, se necessario. Per mostrare o nascondere entrambi i tipi di totali, fare clic in un punto qualsiasi della tabella pivot e nel blocco “PivotTable Tools” aprire la scheda “Design”. Quindi fare clic sul pulsante “Subtotals”. Nel menu che si apre, puoi scegliere se mostrare o meno i subtotali. Subtotali
Accanto al pulsante “Subtotals” si trova il pulsante “Grand Totals”. Totali complessivi
Utilizzando il menu contestuale di questo pulsante, è possibile abilitare o disabilitare la visualizzazione dei totali complessivi.

Quindi, sebbene le tabelle pivot siano uno degli strumenti più potenti per analizzare le informazioni dai fogli di calcolo Excel, sono anche uno degli strumenti più sottovalutati in questa applicazione. Le tabelle pivot non solo consentono di riepilogare rapidamente, ma forniscono anche la possibilità di cambiare istantaneamente il modo in cui analizzi trascinando e rilasciando i campi da un’area all’altra. Per padroneggiare questo strumento, è sufficiente dedicare solo un po ‘di tempo alla teoria e alla pratica.

Le tabelle pivot mostrano l’analisi dei dati grezzi a colpo d’occhio. Spesso è indesiderabile che le persone non autorizzate vedano queste informazioni, pertanto si consiglia di proteggere tali documenti. Questa importante regola non deve essere trascurata. In questo caso, programmi speciali ti aiuteranno a recuperare password Excel dimenticata.