Spesso, una volta creata una tabella pivot, è necessario espandere l'analisi e includere più dati/calcoli come parte di essa.
Se è necessario un nuovo punto dati che può essere ottenuto utilizzando punti dati esistenti nella tabella pivot, non è necessario tornare indietro e aggiungerlo ai dati di origine. Invece, puoi usare a Campo calcolato tabella pivot per fare questo.
Scarica il set di dati e segui.
Che cos'è un campo calcolato di una 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 mostrato di seguito:
La tabella pivot di cui sopra riassume i valori di vendita e profitto per i rivenditori.
Ora, cosa succede se vuoi anche sapere qual è stato il margine di profitto di questi rivenditori (dove il margine di profitto è "Profitto" diviso per "Vendite").
Ci sono un paio di modi per farlo:
- 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. Una volta fatto ciò, è necessario aggiornare i dati di origine della tabella pivot per ottenere questa nuova colonna come parte di essa.
- Sebbene questo metodo sia possibile, dovresti tornare manualmente al set di dati ed eseguire i calcoli. Ad esempio, potrebbe essere necessario aggiungere un'altra colonna per calcolare la vendita media per unità (Vendite/Quantità). Ancora una volta dovrai aggiungere questa colonna ai tuoi dati di origine e quindi aggiornare la tabella pivot.
- Questo metodo gonfia anche la tua tabella pivot mentre aggiungi nuovi dati.
- Aggiungi calcoli al di fuori della tabella pivot. Questa può essere un'opzione se è improbabile che la struttura della tabella pivot cambi. Ma se modifichi la tabella Pivot, il calcolo potrebbe non aggiornarsi di conseguenza e potrebbe darti risultati o errori errati. Come mostrato di seguito, ho calcolato il margine di profitto quando c'erano rivenditori nella riga. Ma quando l'ho cambiato da clienti a regioni, la formula ha dato un errore.
- Utilizzo di un campo calcolato di una tabella pivot. Questo è il modo più efficiente per utilizzare i dati della tabella pivot esistente e calcolare la metrica desiderata. Considera il campo calcolato come una colonna virtuale che hai aggiunto utilizzando le colonne esistenti della tabella pivot. Ci sono molti vantaggi nell'usare un campo calcolato di una tabella pivot (come vedremo tra un minuto):
- Non richiede la gestione di formule o l'aggiornamento dei dati di origine.
- È scalabile in quanto terrà conto automaticamente di tutti i nuovi dati che potresti aggiungere alla tua tabella pivot. Una volta aggiunto un campo Calcola, puoi utilizzarlo come qualsiasi altro campo nella tua tabella pivot.
- È facile da aggiornare e gestire. Ad esempio, se le metriche cambiano o devi modificare il calcolo, puoi farlo facilmente dalla tabella pivot stessa.
Aggiunta di un campo calcolato alla tabella pivot
Vediamo come aggiungere un campo calcolato di una tabella pivot in una tabella pivot esistente.
Supponiamo di avere una tabella pivot come mostrato di seguito e di voler calcolare il margine di profitto per ogni rivenditore:
Ecco i passaggi per aggiungere un campo calcolato tabella pivot:
- Seleziona una cella nella tabella pivot.
- Vai a Strumenti tabella pivot -> Analizza -> Calcoli -> Campi, elementi e set.
- Dal menu a discesa, seleziona Campo calcolato.
- Nella finestra di dialogo Inserisci file calcolato:
- Dagli un nome inserendolo nel campo Nome.
- Nel campo Formula, crea la formula che desideri per il campo calcolato. Tieni presente che puoi scegliere tra i nomi dei campi elencati di seguito. In questo caso, la formula è "= Profitto/Vendite". È possibile immettere manualmente i nomi dei campi o fare doppio clic sul nome del campo elencato nella casella Campi.
- Fare clic su Aggiungi e chiudere la finestra di dialogo.
Non appena aggiungi il campo calcolato, verrà visualizzato come uno dei campi nell'elenco dei campi della tabella pivot.
Ora è possibile utilizzare questo campo calcolato come qualsiasi altro campo della tabella pivot (si noti che non è possibile utilizzare il campo calcolato della tabella pivot come filtro di report o affettatrice).
Come accennato in precedenza, il vantaggio dell'utilizzo di un campo calcolato di una tabella pivot è che è possibile modificare la struttura della tabella pivot e si regolerà automaticamente.
Ad esempio, se trascino la regione nell'area delle righe, otterrai il risultato come mostrato di seguito, dove viene riportato il valore del margine di profitto per i rivenditori e la regione.
Nell'esempio sopra, ho usato una semplice formula (=Profitto/Vendite) per inserire un campo calcolato. Tuttavia, puoi anche utilizzare alcune formule avanzate.
Prima di mostrarti un esempio di utilizzo di una formula avanzata per creare un campo di calcolo tabella pivot, ecco alcune cose che devi sapere:
- NON è possibile utilizzare riferimenti o intervalli denominati durante la creazione di un campo calcolato tabella pivot. Ciò escluderebbe molte formule come VLOOKUP, INDEX, OFFSET e così via. Tuttavia, puoi utilizzare formule che possono funzionare senza riferimenti (come SOMMA, SE, CONTA e così via…).
- Puoi usare una costante nella formula. Ad esempio, se si desidera conoscere le vendite previste in cui si prevede una crescita del 10%, è possibile utilizzare la formula =Vendite*1.1 (dove 1.1 è costante).
- L'ordine di precedenza è seguito nella formula che compone il campo calcolato. Come best practice, usa le parentesi per assicurarti di non dover ricordare l'ordine di precedenza.
Ora, vediamo un esempio di utilizzo di una formula avanzata per creare un campo calcolato.
Supponiamo di avere il set di dati come mostrato di seguito e di dover mostrare il valore delle vendite previste nella tabella pivot.
Per il valore previsto, è necessario utilizzare un aumento delle vendite del 5% per i grandi rivenditori (vendite superiori a 3 milioni) e un aumento delle vendite del 10% per i rivenditori piccoli e medi (vendite inferiori a 3 milioni).
Nota: i numeri di vendita qui sono falsi e sono stati utilizzati per illustrare gli esempi in questo tutorial.
Ecco come farlo:
- Seleziona una cella nella tabella pivot.
- Vai a Strumenti tabella pivot -> Analizza -> Calcoli -> Campi, elementi e set.
- Dal menu a discesa, seleziona Campo calcolato.
- Nella finestra di dialogo Inserisci file calcolato:
- Dagli un nome inserendolo nel campo Nome.
- Nel campo Formula, utilizzare la seguente formula: =IF(Regione =”Sud”,Sales *1.05,Sales *1.1)
- Fare clic su Aggiungi e chiudere la finestra di dialogo.
Questo aggiunge una nuova colonna alla tabella pivot con il valore di previsione delle vendite.
Fare clic qui per scaricare il set di dati.
Un problema con i campi calcolati della tabella pivot
Il campo calcolato è una funzionalità straordinaria che migliora davvero il valore della tua tabella pivot con i calcoli dei campi, pur mantenendo tutto scalabile e gestibile.
Esiste, tuttavia, un problema con i campi calcolati della tabella pivot che è necessario conoscere prima di utilizzarlo.
Supponiamo di avere una tabella pivot come mostrato di seguito in cui ho utilizzato il campo calcolato per ottenere i numeri di vendita previsti.
Notare che il subtotale e il totale generale non sono corretti.
Sebbene questi dovrebbero aggiungere il valore di previsione delle vendite individuali 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, il South Total lo riporta erroneamente come 22.287.000. Ciò accade poiché utilizza la formula 21,225,800 * 1,05 per ottenere il valore.
Sfortunatamente, non c'è modo di correggerlo.
Il modo migliore per gestirlo sarebbe rimuovere i subtotali e i totali complessivi dalla tabella pivot.
Puoi anche seguire alcune soluzioni alternative innovative che Debra ha mostrato per gestire questo problema.
Come modificare o eliminare un campo calcolato della tabella pivot?
Dopo aver creato un campo calcolato della tabella pivot, è possibile modificare la formula o eliminarla utilizzando i passaggi seguenti:
- Seleziona una cella nella tabella pivot.
- Vai a Strumenti tabella pivot -> Analizza -> Calcoli -> Campi, elementi e set.
- Dal menu a discesa selezionare Campo calcolato.
- Nel campo Nome, fare clic sulla freccia a discesa (piccola freccia rivolta verso il basso alla fine del campo).
- Dall'elenco, seleziona il campo calcolato che desideri eliminare o modificare.
- Cambia la formula nel caso in cui desideri modificarla o fai clic su Elimina nel caso in cui desideri eliminarla.
Come ottenere un elenco di tutte le formule dei campi calcolati?
Se crei molti campi Calcolati tabella pivot, non preoccuparti di tenere traccia della formula utilizzata in ognuno di essi.
Excel ti consente di creare rapidamente un elenco di tutte le formule utilizzate nella creazione dei campi calcolati.
Ecco i passaggi per ottenere rapidamente l'elenco delle formule Tutti i campi calcolati:
- Seleziona una cella nella tabella pivot.
- Vai a Strumenti tabella pivot -> Analizza -> Campi, elementi e set -> Elenca formule.
Non appena fai clic su Elenca formule, Excel inserirà automaticamente un nuovo foglio di lavoro che conterrà i dettagli di tutti i campi/elementi calcolati che hai utilizzato nella tabella pivot.
Questo può essere uno strumento davvero utile se devi inviare il tuo lavoro al cliente o condividerlo con il tuo team.
Potresti anche trovare utili i seguenti tutorial sulla tabella pivot:
- Preparazione dei dati di origine per la tabella pivot.
- Utilizzo delle affettatrici 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.
- Come filtrare i dati in una tabella pivot in Excel
- Come sostituire le celle vuote con zeri nelle tabelle pivot di Excel
- Come applicare la formattazione condizionale in una tabella pivot in Excel
- Pivot Cache in Excel: cos'è e come utilizzarla al meglio?
