Come aggiungere e utilizzare i campi calcolati della tabella pivot di Excel

In genere, una volta creata una tabella pivot, è necessario espandere l'analisi e includervi più dati/calcoli.

Se è necessario un nuovo punto dati che può essere ottenuto utilizzando un punto dati esistente nella tabella pivot, non è necessario tornare indietro e aggiungerlo ai dati di origine.Invece, puoi usareCampi calcolati tabella pivotper farlo.

Che cos'è un campo calcolato di tabella pivot?

Iniziamo con un esempio di base di una tabella pivot.

Supponiamo di avere un set di dati di rivenditori e di creare una tabella pivot come questa:

La tabella pivot sopra riassume i valori di vendita e profitto del rivenditore.

E se volessi anche sapere quali erano i margini di profitto di quei rivenditori (il margine di profitto è "profitto" diviso per "vendite")?

Ci sono diversi modi per farlo:

1. Torna al set di dati originale e aggiungi questo nuovo punto dati.Quindi puoi inserire una nuova colonna nei dati di origine e calcolare il margine di profitto in essa.Al termine, è necessario aggiornare i dati di origine della tabella pivot per ottenere questa nuova colonna come parte di essa.
   • Sebbene questo approccio funzioni, è necessario tornare manualmente al set di dati ed eseguire i calcoli.Ad esempio, potresti voler aggiungere un'altra colonna per calcolare le vendite medie per unità (vendite/quantità).Ancora una volta, devi aggiungere questa colonna ai dati di origine e quindi aggiornare la tabella pivot.
   • Questo metodo gonfia anche la tua tabella pivot quando aggiungi nuovi dati ad essa.
2. Aggiungi calcoli al di fuori delle tabelle pivot.Scegli questa opzione se è improbabile che la struttura della tabella pivot cambi.Tuttavia, se modifichi la tabella pivot, i calcoli potrebbero non aggiornarsi di conseguenza e potrebbero darti risultati errati o errori.Come mostrato di seguito, ho calcolato il margine di profitto quando ci sono rivenditori nella riga.Ma quando lo cambio da cliente a zona, la formula va storta.
3. Calcola i campi utilizzando le tabelle pivot.questo èUtilizzare i dati di tabella pivot esistenti e calcolare ilmodo più efficiente.Pensa ai campi calcolati come a colonne virtuali che aggiungi utilizzando le colonne esistenti nella tabella pivot.L'uso dei campi calcolati dalla tabella pivot ha molti vantaggi (come vedremo più avanti):
   • Non richiede l'utilizzo di formule o l'aggiornamento dei dati di origine.
   • È estensibile perché tiene automaticamente conto di tutti i nuovi dati che potresti aggiungere alla tabella pivot.Dopo aver aggiunto un campo calcolato, puoi usarlo come qualsiasi altro campo in una tabella pivot.
   • È facile da aggiornare e gestire.Ad esempio, se una metrica cambia o devi modificare un calcolo, puoi farlo facilmente dalla tabella pivot stessa.

Aggiungi campi calcolati alle tabelle pivot

Vediamo come aggiungere un campo calcolato di una tabella pivot a una tabella pivot esistente.

Supponiamo di avere una tabella pivot simile a questa e di voler calcolare il margine di profitto per ogni rivenditore:

Di seguito sono riportati i passaggi per aggiungere un campo calcolato di tabella pivot:

Dopo aver aggiunto il campo calcolato, apparirà come uno dei campi nell'elenco Campi tabella pivot.

È ora possibile utilizzare questo campo calcolato come qualsiasi altro campo della tabella pivot (si noti che non è possibile utilizzare un campo calcolato della tabella pivot come filtro di report o filtro dei dati).

Come accennato in precedenza, il vantaggio dell'utilizzo di un campo calcolato della tabella pivot è che puoi modificare la struttura della tabella pivot e si regolerà automaticamente.

Ad esempio, se trascino una regione nell'area della riga, otterrai risultati come i seguenti, in cui vengono riportati i valori del margine di profitto per il rivenditore e la regione.

 

Nell'esempio sopra, ho usato una semplice formula (=Profitto/Vendite) per inserire il campo calcolato.Tuttavia, puoi anche utilizzare alcune formule avanzate.

Prima di mostrarti un esempio di utilizzo di una formula avanzata per creare un campo calcolato di tabella pivot, ecco alcune cose che devi sapere:

• Non è possibile utilizzare riferimenti o intervalli denominati durante la creazione di campi calcolati di tabella pivot.Ciò escluderà molte formule come VLOOKUP, INDEX, OFFSET, ecc.Tuttavia, puoi utilizzare formule che funzionano senza riferimenti (es. SUM, IF, COUNT, ecc...).
• Puoi usare le costanti nelle formule.Ad esempio, se si desidera conoscere le vendite previste che dovrebbero crescere del 10%, utilizzare la formula =Vendite*1.1 (dove 1.1 è una costante).
• Segui l'ordine di precedenza nelle formule che generano campi calcolati.Come best practice, usa le parentesi per assicurarti di non dover ricordare la precedenza.

Ora diamo un'occhiata a un esempio di creazione di un campo calcolato utilizzando una formula avanzata.

Supponiamo di avere il set di dati mostrato di seguito e di dover visualizzare le vendite previste in una tabella pivot.

Per i valori di previsione, utilizzeresti un aumento delle vendite del 300% per la grande distribuzione (oltre 5 milioni di vendite) e un aumento delle vendite del 300% per la piccola e media distribuzione (meno di 10 milioni di vendite).

Nota: i dati sulle vendite qui sono falsi e sono stati utilizzati per illustrare gli esempi in questo tutorial.

Ecco come farlo:

Ciò aggiungerà una nuova colonna alla tabella pivot con i valori delle previsioni di vendita.

Problema del campo calcolato nella tabella pivot

I campi calcolati sono una caratteristica straordinaria che aumenta davvero il valore delle tabelle pivot con i calcoli dei campi, pur mantenendo tutto scalabile e gestibile.

Tuttavia, esiste un problema con i campi calcolati della tabella pivot che è necessario comprendere prima di utilizzarli.

Supponiamo di avere una tabella pivot come quella di seguito e di utilizzare un campo calcolato per ottenere i numeri di vendita previsti.

Si noti che i totali parziali e i totali non sono corretti.

Sebbene questi dovrebbero aggiungere valori di previsione di vendita separati per ciascun rivenditore, in realtà segue la stessa formula del campo calcolato che abbiamo creato.

Quindi per South Total, mentre il valore dovrebbe essere 22,824,000, South Total lo riporta erroneamente come 22,287,000.Ciò accade perché utilizza la formula 21,225,800*1.05 per ottenere il valore.

Sfortunatamente, non è possibile correggere questo problema.

Il modo migliore per gestirlo è rimuovere i totali parziali e complessivi dalla tabella pivot.

Puoi anche passare Presentato da DeboraAlcune soluzioni innovative per affrontare questo problema.

Come modificare o eliminare un campo calcolato da una tabella pivot?

Dopo aver creato un campo calcolato di tabella pivot, è possibile modificare la formula o eliminarla attenendosi alla seguente procedura:

Come ottenere un elenco di tutte le formule dei campi calcolati?

Se crei molti campi calcolati di tabella pivot, non devi preoccuparti di tenere traccia delle formule utilizzate in ogni campo.

Excel consente di creare rapidamente un elenco di tutte le formule utilizzate per creare i campi calcolati.

Di seguito sono riportati i passaggi per ottenere rapidamente un elenco di tutte le formule dei campi calcolati:

• Seleziona una cella qualsiasi nella tabella pivot.
• Vai a Strumenti tabella pivot -> Analizza -> Campi, elementi e insiemi -> Formule elenco.

Dopo aver fatto clic sulla formula dell'elenco, Excel inserirà automaticamente un nuovo foglio di lavoro con i dettagli di tutti i campi/elementi calcolati che hai utilizzato nella tabella pivot.

Questo può essere uno strumento molto utile se devi inviare un lavoro a un cliente o condividerlo con il tuo team.