Preparazione dei dati di origine per la tabella pivot

Avere i dati nel formato giusto è un passaggio cruciale nella creazione di una tabella pivot robusta e priva di errori. Se non lo fai nel modo giusto, puoi finire per avere molti problemi con la tua tabella pivot.

Qual è una buona progettazione per i dati di origine per la tabella pivot?

Diamo un'occhiata a un esempio di dati di origine validi per una tabella pivot.

Ecco cosa lo rende un buon data design di origine:

• La prima riga contiene intestazioni che descrivono i dati nelle colonne.
• Ogni colonna rappresenta una categoria di dati univoca. Ad esempio, la colonna C contiene solo i dati del prodotto e la colonna D e solo i dati del mese.
• Ogni riga è un record che rappresenterebbe un'istanza della transazione o della vendita.
• Le intestazioni dei dati sono univoche e non vengono ripetute da nessuna parte nel set di dati. Ad esempio, se hai numeri di vendita per quattro trimestri in un anno, NON dovresti nominarli tutti come Vendite. Invece, assegna a queste intestazioni di colonna nomi univoci come Vendite Q1, Vendite Q2 e così via…
  • Se non disponi di titoli univoci, puoi comunque procedere e creare una tabella pivot ed Excel li renderà automaticamente univoci aggiungendo un suffisso (come Vendite, Vendite2, Vendite3). Tuttavia, sarebbe un modo terribile per preparare e utilizzare una tabella pivot.

Insidie ​​comuni da evitare durante la preparazione dei dati di origine

• Non dovrebbero esserci colonne vuote nei dati di origine. Questo è facile da individuare. Se hai una colonna vuota nei dati di origine, non sarai in grado di creare una tabella pivot. Mostrerà un errore come mostrato di seguito.
• Non dovrebbero esserci celle/righe vuote nei dati di origine. Sebbene tu possa creare con successo una tabella pivot nonostante abbia celle o righe vuote, ci sono molti effetti collaterali che possono venire a morderti nel corso della giornata.
  • Ad esempio, supponiamo che tu abbia una cella vuota nella colonna delle vendite. Se crei una tabella pivot utilizzando questi dati e inserisci il campo delle vendite nell'area delle colonne, ti mostrerebbe il COUNT e non la SOMMA. Questo perché Excel interpreta l'intera colonna come contenente dati di testo (solo a causa di una singola cella vuota).
• Applica il formato pertinente alle celle nei dati di origine. Ad esempio, se disponi di date (memorizzate come numeri di serie nel backend in Excel), applica uno dei formati di data accettabili. Ciò ti aiuterebbe a creare la tabella pivot e utilizzare la data come uno dei criteri per riepilogare, raggruppare e ordinare i dati.
  • Se hai un paio di secondi, prova questo. Formatta le date nella tua tabella pivot come numeri, quindi crea una tabella pivot utilizzando questi dati. Ora nella tabella pivot, seleziona il campo della data e guarda cosa succede. Lo inserirà automaticamente nell'area dei valori. Questo perché la tua tabella pivot non sa che queste sono date. Li interpreta come numeri.
• Non includere totali di colonne, totali di righe, medie e così via come parte dei dati di origine. Una volta che hai la tabella pivot, puoi facilmente ottenerli in seguito.
• Crea sempre una tabella Excel e poi usala come origine per una tabella pivot. Questa è più una buona pratica e non una trappola. La tua tabella pivot funzionerebbe perfettamente con dati di origine che non sono anche una tabella Excel. Il vantaggio di Excel Table è che può regolare i dati in espansione. Se aggiungi più righe al set di dati, non è necessario modificare i dati di origine più e più volte. Puoi semplicemente aggiornare la tabella pivot e terrà conto automaticamente delle nuove righe aggiunte ai dati di origine.

Esempi di progetti di dati di origine non validi

Diamo un'occhiata ad alcuni cattivi esempi di progetti di dati di origine.

Progettazione di dati di origine errata - Esempio 1

Questo è un modo comune per conservare i dati poiché è facile da seguire e comprendere. Ci sono due problemi con questa disposizione dei dati:

• Non ottieni il quadro completo. Ad esempio, puoi vedere che le vendite per Mid West nel trimestre 1 sono 2924300. Ma si tratta di una singola vendita o di un numero di vendite. Se ogni record è disponibile in una riga separata, puoi eseguire un'analisi migliore.
• Se vai avanti e crei una tabella pivot utilizzando questo (cosa che puoi), otterrai campi diversi per trimestri diversi. Qualcosa come mostrato di seguito:

Progettazione di dati di origine errata - Esempio 2

Questa rappresentazione dei dati può essere ben accolta dalla direzione e dal pubblico delle presentazioni PowerPoint, ma non è adatta per creare una tabella pivot.

Ancora una volta, questo è il tipo di riepilogo che puoi creare facilmente utilizzando una tabella pivot. Quindi, anche se alla fine desideri un aspetto simile per i tuoi dati, mantieni i dati di origine in un formato pronto per Pivot e crea questa vista utilizzando la tabella pivot.

Progettazione di dati di origine errata - Esempio 3

Anche questo è un output che può essere facilmente ottenuto utilizzando una tabella pivot. Ma non può essere utilizzato per creare una tabella pivot.

Ci sono celle vuote nel set di dati e i trimestri sono distribuiti come intestazioni di colonna.

Inoltre, la regione è specificata in alto, mentre dovrebbe far parte di ogni record.

[CASO DI STUDIO] Conversione di dati formattati in modo errato in dati di origine pronti per la tabella pivot

A volte, potresti ottenere un set di dati che non è adatto per essere utilizzato come dati di origine per la tabella pivot. In tal caso, potresti non avere altra scelta che convertire i dati in un formato dati compatibile con Pivot.

Ecco un esempio di cattiva progettazione dei dati:

Ora puoi utilizzare le funzioni di Excel o la query pivot per convertire questi dati in un formato che può essere utilizzato come dati di origine per la tabella pivot.

Vediamo come funzionano entrambi questi metodi.

Metodo 1: utilizzo delle formule di Excel

Vediamo come utilizzare le funzioni di Excel per convertire questi dati in un formato pronto per la tabella pivot.

• Crea un'intestazione di colonna univoca per tutte le categorie nel set di dati originale. In questo esempio, sarebbe Regione, Trimestre e Vendite.
• Nella cella sotto l'intestazione della regione, usa la seguente formula: =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
  • Trascina la formula verso il basso e ripeterà tutte le regioni.
• Nella cella sotto l'intestazione del trimestre, usa la seguente formula: =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1) ,0))
  • Trascina la formula verso il basso e ripeterà tutti i quarti.
• Nell'intestazione sotto Vendite, usa la seguente formula: =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0 ))
  • Trascinalo verso il basso per ottenere tutti i valori. Questa formula utilizza i dati Regione e Trimestre come valori di ricerca e restituisce il valore delle vendite dal set di dati originale.

Ora puoi utilizzare questi dati risultanti come dati di origine per la tabella pivot.

Fare clic qui per scaricare il file di esempio.

Metodo 2: utilizzo di Power Query

Power Query ha una funzionalità che può convertire facilmente questo tipo di dati in un formato dati pronto per Pivot.

Se stai usando Excel 2016, le funzionalità di Power Query sarebbero disponibili nella scheda Dati nel gruppo Ottieni e trasforma. Se utilizzi Excel 2013 o versioni precedenti, puoi utilizzarlo come componente aggiuntivo.

Ecco un'eccellente guida sull'installazione di Power Query di Jon da Excel Campus.

Ancora una volta, considerando che i dati sono formattati come mostrato di seguito:

Ecco i passaggi per convertire i dati di origine in formato pronto per la tabella pivot:

• Converti i dati in una tabella Excel. Seleziona il set di dati e vai su Inserisci -> Tabelle -> Tabella.
• Nella finestra di dialogo Inserisci tabella, assicurati che sia selezionato l'intervallo corretto e fai clic su OK. Questo convertirà i dati tabulari in una tabella Excel.
• In Excel 2016, vai su Dati -> Ottieni e trasforma -> Da tabella.
  • Se stai usando il componente aggiuntivo Power Query in una versione precedente, vai a Power Query -> Dati esterni -> Da tabella.
• Nell'editor di query selezionare le colonne che si desidera annullare il pivot. In questo caso, questi sono quelli per i quattro trimestri. Per selezionare tutte le colonne, tieni premuto il tasto Maiusc, quindi seleziona la prima colonna e poi l'ultima.
• Nell'editor di query, vai a Trasforma -> Qualsiasi colonna -> Rimuovi colonne. Questo convertirà i dati della colonna in un formato adatto alla tabella pivot.
• Power Query assegna nomi generici alle colonne. Cambia questi nomi con quelli che desideri. In questo caso, modificare Attributo in Trimestre e Valore in Vendite.
• Nell'editor di query, vai a File -> Chiudi e carica. Questo chiuderà la finestra di dialogo Editor di Power Query e creerà un foglio di lavoro separato che conterrà i dati con colonne non pivot.

Ora che sai come preparare i dati di origine per la tabella pivot, sei pronto per Excel nel mondo delle tabelle pivot.

Ecco alcuni altri tutorial sulla tabella pivot che potresti trovare utili:

• Come aggiornare la tabella pivot in Excel
• Utilizzo dei filtri dei dati nella tabella pivot di Excel: una guida per principianti.
• Come raggruppare le date nelle tabelle pivot in Excel
• Come raggruppare i numeri nella tabella pivot in Excel.
• Pivot Cache in Excel: cos'è e come utilizzarla al meglio.
• Come filtrare i dati in una tabella pivot in Excel
• Come aggiungere e utilizzare un campo calcolato in una tabella pivot di Excel
• Come applicare la formattazione condizionale in una tabella pivot in Excel
• Come sostituire le celle vuote con zeri nelle tabelle pivot di Excel