Nelle tabelle pivot è possibile usare le funzioni di riepilogo nei campi valore per combinare i valori dei dati di origine sottostanti. Se le funzioni di riepilogo e i calcoli personalizzati non forniscono i risultati desiderati, è possibile creare formule personalizzate in campi calcolati ed elementi calcolati. Ad esempio, si può aggiungere un elemento calcolato con la formula per la commissione di vendita, variabile a seconda dell'area geografica. In tal caso, la tabella pivot includerà automaticamente la commissione nei subtotali e nei totali complessivi.
Un altro modo per calcolare consiste nell'usare le misure in Power Pivot, che vengono create usando una formula DAX (Data Analysis Expressions). Per altre informazioni, vedere Creare una misura in Power Pivot.
Le tabelle pivot offrono varie modalità di calcolo dei dati. Questo articolo illustra i metodi di calcolo disponibili, l'influenza del tipo di dati di origine sui calcoli e l'uso delle formule nelle tabelle pivot e nei grafici pivot.
Metodi di calcolo disponibili
Per calcolare i valori in una tabella pivot è possibile usare uno o tutti i metodi di calcolo seguenti:
Funzioni di riepilogo nei campi valore I dati nell'area dei valori riepilogano i dati di origine sottostanti nella tabella pivot. Ad esempio, i dati di origine seguenti:
Generano le tabelle pivot e i grafici pivot seguenti. Se si crea un grafico pivot dai dati di una tabella pivot, i valori in tale grafico pivot riflettono i calcoli nel rapporto di tabella pivot associato.
Nella tabella pivot il campo colonna Mese contiene gli elementi Marzo e Aprile. Il campo riga Area contiene gli elementi Nord, Sud, Est e Ovest. Il valore all'intersezione della colonna Aprile con la riga Nord rappresenta i ricavi totali delle vendite ricavate dai record dei dati di origine il cui valore per Mese è Aprile e il cui valore per Area è Nord.
In un grafico pivot il campo Area potrebbe essere un campo categoria che contiene le categorie Nord, Sud, Est e Ovest. Il campo Mese potrebbe essere un campo serie che contiene gli elementi Marzo, Aprile e Maggio come serie rappresentata nella legenda. Un campo Valori denominato Somma vendite potrebbe contenere indicatori di dati che rappresentano il totale dei ricavi in ogni area per ciascun mese. Ad esempio, un indicatore di dati potrebbe rappresentare, in base alla sua posizione sull'asse verticale (dei valori), il totale delle vendite di Aprile nell'area Nord.
Le funzioni di riepilogo elencate di seguito consentono di calcolare i campi valore per tutti i tipi di origine dati ad eccezione dei dati di origine OLAP (Online Analytical Processing).
Funzione Riepilogo Somma La somma dei valori. Questa è la funzione predefinita per i dati di tipo numerico. Conteggio Il numero di valori dei dati. La funzione di riepilogo Conteggio è equivalente alla funzione CONTA.VALORI. Conteggio è la funzione predefinita per i dati diversi dai numeri. Media La media dei valori. Max Il valore più grande. Min Il valore più piccolo. Prodotto Il prodotto dei valori. Conta num. Il numero di valori dei dati corrispondenti a numeri. La funzione di riepilogo Conta num. è equivalente alla funzione CONTA.VALORI. DevSt La stima della deviazione standard di una popolazione, dove il campione è un sottoinsieme dell'intera popolazione. DevStPop La deviazione standard di una popolazione, dove la popolazione è costituita da tutti i dati che si desidera riepilogare. Varianza La stima della varianza di una popolazione, dove il campione è un sottoinsieme dell'intera popolazione. Varianza pop. La varianza di una popolazione, dove la popolazione è costituita da tutti i dati che si desidera riepilogare.
Calcoli personalizzati Un calcolo personalizzato mostra i valori basati su altri elementi o celle nell'area dati. Ad esempio, è possibile visualizzare i valori del campo dati Somma vendite come percentuale delle vendite di Marzo o come totale parziale degli elementi del campo Mese.
Per i calcoli personalizzati nei campi valore sono disponibili le funzioni seguenti.Funzione Risultato Nessun calcolo Visualizza il valore immesso nel campo. % del totale complessivo Visualizza i valori come percentuale del totale complessivo di tutti i valori o punti dati del rapporto. % di totale colonna Visualizza tutti i valori di ogni colonna o serie come percentuale del totale della colonna o della serie. % del totale riga Visualizza il valore di ciascuna riga o categoria come percentuale del totale della riga o della categoria. % di Visualizza i dati come percentuale del valore dell'Elemento base contenuto nel Campo base. % del totale riga padre Calcola i valori come segue:
(valore dell'elemento) / (valore dell'elemento padre nelle righe)% del totale colonna padre Calcola i valori come segue:
(valore dell'elemento) / (valore dell'elemento padre nelle colonne)% del totale padre Calcola i valori come segue:
(valore dell'elemento) / (valore dell'elemento padre del Campo base selezionato)Discordanza da Visualizza i dati come discordanza dal valore dell'Elemento base contenuto nel Campo base. % discordanza da Visualizza i dati come discordanza in percentuale dal valore dell'Elemento base contenuto nel Campo base. Totale parziale in Visualizza il valore degli elementi successivi nel Campo base come totale parziale. % del totale parziale in Calcola il valore percentuale degli elementi successivi nel Campo base visualizzati come totale parziale. Rango dal più piccolo al più grande Visualizza il rango dei valori selezionati in un campo specifico. L'elemento più piccolo nel campo viene elencato con il rango 1 e ogni valore maggiore con un valore di rango superiore. Rango dal più grande al più piccolo Visualizza il rango dei valori selezionati in un campo specifico. L'elemento più grande nel campo viene elencato con il rango 1 e ogni valore inferiore con un valore di rango superiore. Indice Calcola i valori come segue:
((valore nella cella) x (Totale complessivo dei totali complessivi)) / ((Totale complessivo di riga) x (Totale complessivo di colonna))
- Formule Se le funzioni di riepilogo e i calcoli personalizzati non forniscono i risultati desiderati, è possibile creare formule personalizzate nei campi calcolati e negli elementi calcolati. Ad esempio, si può aggiungere un elemento calcolato con la formula per la commissione di vendita, variabile a seconda dell'area geografica. In tal caso, il rapporto includerà automaticamente la commissione nei subtotali e nei totali complessivi.
Influenza del tipo di dati di origine sui calcoli
I calcoli e le opzioni disponibili in un rapporto dipendono dalla provenienza dei dati di origine, ovvero da un database OLAP o da un'origine dati non OLAP.
-
Calcoli basati su dati di origine OLAP Per le tabelle pivot create da cubi OLAP, i valori riepilogati vengono precalcolati nel server OLAP prima che Excel visualizzi i risultati. Non è possibile modificare la modalità di calcolo dei valori precalcolati nella tabella pivot. Non si può ad esempio modificare la funzione di riepilogo usata per calcolare i campi dati o i subtotali o aggiungere campi calcolati o elementi calcolati.
Inoltre, se il server OLAP fornisce campi calcolati, noti come membri calcolati, questi campi saranno visualizzati nell'elenco dei campi della tabella pivot. Sarà anche possibile visualizzare, ma non modificare, eventuali campi ed elementi calcolati creati da macro scritte in Visual Basic, Applications Edition (VBA) e archiviati nella cartella di lavoro. Per altri tipi di calcoli, contattare l'amministratore del database OLAP.
Per i dati di origine OLAP, è possibile includere o escludere i valori degli elementi nascosti quando si calcolano i subtotali e i totali complessivi. - Calcoli basati su dati di origine non OLAP Nelle tabelle pivot basate su altri tipi di dati esterni o su dati di fogli di lavoro Excel usa la funzione di riepilogo Somma per calcolare i campi valore contenenti dati numerici e la funzione di riepilogo Conteggio per calcolare i campi dati che contengono testo. È possibile scegliere un'altra funzione di riepilogo, ad esempio Media, Max o Min, per analizzare e personalizzare ulteriormente i dati. Creando un campo calcolato o un elemento calcolato in un campo, è anche possibile creare formule personalizzate che usano gli elementi del rapporto o dati di altri fogli di calcolo.
Uso delle formule nelle tabelle pivot
Le formule possono essere create solo nei rapporti basati su dati di origine non OLAP. Non è possibile usare formule nei rapporti basati su database OLAP. Di seguito è riportato un elenco di regole di sintassi e comportamenti relativi alle formule che è necessario conoscere quando si usano formule nelle tabelle pivot.
Elementi delle formule di una tabella pivot Nelle formule create per i campi calcolati e gli elementi calcolati è possibile usare operatori ed espressioni come nelle altre formule del foglio di lavoro. Si possono usare costanti e si può fare riferimento a dati del rapporto, ma non usare riferimenti di cella o nomi definiti. Non è possibile usare funzioni del foglio di lavoro che richiedono riferimenti di cella o nomi definiti come argomenti, né funzioni di matrice.
Nomi di campi ed elementi Excel usa i nomi di campi ed elementi per identificare gli elementi di un report nelle formule. Nell'esempio seguente, i dati dell'intervallo C3:C9 usano il nome campo Latticini. Un elemento calcolato nel campo Tipo che fornisce la stima delle vendite di un nuovo prodotto in base alle vendite di Latticini potrebbe usare una formula come =Latticini * 115%.
Nota
In un grafico pivot i nomi dei campi sono visualizzati nell'elenco dei campi della tabella pivot e i nomi degli elementi nell'elenco di riepilogo a discesa di ogni campo. Non confondere questi nomi con quelli mostrati nei suggerimenti sui grafici, che invece riflettono i nomi delle serie e delle coordinate.
Le formule operano sui totali delle somme, non sui singoli record Le formule per i campi calcolati operano sulla somma dei dati sottostanti per tutti i campi della formula. Ad esempio, la formula del campo calcolato =Vendite * 1,2 moltiplica per 1,2 volte la somma delle vendite di ogni tipo e area. Non moltiplica per 1,2 ciascuna vendita per sommare poi i valori ottenuti dalla moltiplicazione.
Le formule degli elementi calcolati operano sui singoli record. Ad esempio, la formula dell'elemento calcolato =Latticini *115% moltiplica ogni singola vendita di Latticini per 115%, quindi i valori ottenuti dalla moltiplicazione vengono sommati nell'area dei valori.Spazi, numeri e simboli nei nomi In un nome che include più campi, i campi possono essere in qualsiasi ordine. Nell'esempio precedente, le celle C6:D6 possono essere 'Aprile Nord' o 'Nord Aprile'. Usare le virgolette singole per i racchiudere nomi composti da più parole o che includono numeri o simboli.
Totali Le formule non possono fare riferimento a totali, come Totale marzo, Totale aprile e Totale complessivo nell'esempio.
Nomi di campo nei riferimenti agli elementi È possibile includere il nome del campo in un riferimento a un elemento. Il nome dell'elemento deve essere racchiuso tra parentesi quadre, ad esempio Area[Nord]. Usare questo formato per evitare #NAME? quando due elementi in due campi diversi in un report hanno lo stesso nome. Ad esempio, se un report contiene un elemento denominato Carne nel campo Tipo e un altro elemento denominato Carne nel campo Categoria, è possibile impedire #NAME? facendo riferimento agli elementi come Tipo[Carne] e Categoria[Carne].
Riferimento a elementi in base alla posizione È possibile fare riferimento a un elemento in base alla sua posizione nel report come attualmente ordinato e visualizzato. Tipo[1] è Latticini e Tipo[2] è Pesce. L'elemento a cui si fa riferimento in questo modo può cambiare ogni volta che cambiano le posizioni degli elementi o quando vengono visualizzati o nascosti altri elementi. In questo indice non sono considerati gli elementi nascosti.
Per fare riferimento agli elementi è possibile usare le posizioni relative. Le posizioni vengono determinate in relazione all'elemento calcolato contenente la formula. Se l'area corrente è Sud, Area[-1] è Nord e se l'area corrente è Nord, Area[+1] è Sud. Un elemento calcolato potrebbe ad esempio usare la formula =Area[-1] * 3%. Se la posizione immessa è precedente al primo elemento o successiva all'ultimo elemento del campo, la formula restituisce un errore #RIF! .
Uso delle formule nei grafici pivot
Per usare le formule in un grafico pivot, è necessario creare le formule nella tabella pivot associata, in cui è possibile vedere i singoli valori che costituiscono i dati, quindi sarà possibile visualizzare i risultati graficamente nel grafico pivot.
Nel grafico pivot seguente, ad esempio, sono illustrate le vendite di ogni agente di vendita suddivise per area:
Per conoscere il risultato delle vendite a fronte di un incremento del 10%, si può creare un campo calcolato nella tabella pivot associata con la formula =Vendite * 110%.
Il risultato compare immediatamente nel grafico pivot, come illustrato di seguito:
Per visualizzare un indicatore di dati distinto per le vendite nell'area Nord detratte del costo di trasporto, pari all'8%, si può creare un elemento calcolato nel campo Area con una formula come =Nord – (Nord * 8%).
Il grafico risultante avrà l'aspetto seguente:
Un elemento calcolato creato nel campo Agenti di vendita verrebbe invece visualizzato come punto dati in ciascuna categoria nel grafico e rappresentato come serie nella legenda.
Servono altre informazioni?
È sempre possibile rivolgersi a un esperto della Tech Community di Excel o ottenere supporto nelle community.