Calcolare i valori in una tabella pivot

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 dell'area dei valori rappresentano un riepilogo dei 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 contenuti in una tabella pivot, i valori contenuti nel grafico pivot rifletteranno 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 visualizza valori basati su altri elementi o altre celle dell'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 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, il rapporto includerà automaticamente la commissione nei subtotali e nei totali complessivi.

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 un'origine dati OLAP    Nelle tabelle pivot create sulla base di cubi OLAP, i valori di riepilogo vengono precalcolati a livello del server OLAP prima di visualizzare i risultati in Excel. 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 un'origine dati non OLAP    Nelle tabelle pivot create sulla base di altri tipi di dati esterni o di dati di fogli di lavoro, Excel usa la funzione di riepilogo Somma per calcolare i campi valore che contengono 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.

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 tabella pivot    Nelle formule create per campi calcolati ed elementi calcolati è possibile usare operatori ed espressioni come nelle altre formule dei fogli 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 tali elementi di un rapporto all'interno delle 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 dei campi calcolati operano sulla somma dei dati sottostanti per ogni campo 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    Quando un nome include più campi, l'ordine dei campi non ha alcuna importanza. 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    Nel riferimento a un elemento è possibile includere il nome del campo. Il nome dell'elemento deve essere racchiuso tra parentesi quadre, ad esempio Area[Nord]. Usare questo formato per evitare l'insorgenza di errori di tipo #NOME? quando due elementi in due diversi campi di uno stesso rapporto hanno lo stesso nome. Ad esempio, se un rapporto contiene un elemento denominato Carne nel campo Tipo e un altro elemento Carne nel campo Categoria, è possibile evitare che si verifichino errori #NOME? facendo riferimento agli elementi nel formato Tipo[Carne] e Categoria[Carne].

• Riferimento a elementi in base alla posizione    È possibile fare riferimento a un elemento in base alla sua posizione nel rapporto secondo l'ordinamento e la visualizzazione corrente. 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! .

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.

Prima di iniziare, decidere se usare un campo calcolato o un elemento calcolato in un campo. Optare per un campo calcolato per usare nella formula i dati di un altro campo. Optare invece per un elemento calcolato per usare nella formula i dati di uno o più elementi specifici contenuti in un campo.

Per gli elementi calcolati è possibile immettere formule diverse cella per cella. Se ad esempio un elemento calcolato denominato RegioneArance ha una formula del tipo =Arance * 0,25 per tutti i mesi, è possibile modificare la formula in =Arance * 0,5 per giugno, luglio e agosto.

Se sono presenti più elementi calcolati o formule, si può definire l'ordine di calcolo.

Aggiungere un campo calcolato

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Campo calcolato.

   

3. Nella casella Nome digitare un nome per il campo.

4. Nella casella Formula immettere la formula per il campo.

   Per usare nella formula i dati di un altro campo, fare clic sul campo nella casella Campi e quindi fare clic su Inserisci campo. Ad esempio, per calcolare a una commissione del 15% su ogni valore del campo Vendite, è possibile immettere = Vendite * 15%.

5. Fare clic su Aggiungi.

Aggiungere un elemento calcolato a un campo

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Se gli elementi inclusi nel campo sono raggruppati, nel gruppo Raggruppa della scheda Analizza fare clic su Separa.

   

3. Fare clic sul campo in cui si vuole aggiungere l'elemento calcolato.

4. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Elemento calcolato

   

5. Nella casella Nome digitare un nome per l'elemento calcolato.

6. Nella casella Formula immettere la formula per l'elemento.

   Per usare nella formula i dati di un elemento, fare clic sull'elemento nell'elenco Elementi e quindi fare clic su Inserisci elemento. L'elemento deve appartenere allo stesso campo dell'elemento calcolato.

7. Fare clic su Aggiungi.

Immettere formule diverse cella per cella per gli elementi calcolati

1. Fare clic sulla cella per cui si vuole modificare la formula.

   Per modificare la formula per più celle, tenere premuto CTRL e fare clic sulle diverse celle.

2. Nella barra della formula digitare le modifiche alla formula.

Definire l'ordine di calcolo per più elementi calcolati o formule

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Ordine di soluzione.

   

3. Selezionare una formula e quindi fare clic su Sposta su o Sposta giù.

4. Proseguire finché le formule non si trovano nell'ordine in cui si vogliono calcolare.

È possibile visualizzare un elenco di tutte le formule usate nella tabella pivot corrente.

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Elenca formule.

   

Prima di modificare una formula, determinare se si trova in un campo calcolato o un elemento calcolato. Se la formula si trova in un elemento calcolato, determinare inoltre se si tratta dell'unica formula associata all'elemento.

Per gli elementi calcolati è possibile modificare singole formule per specifiche celle. Se ad esempio un elemento calcolato denominato CalcArance ha una formula del tipo =Arance * 0,25 per tutti i mesi, si può modificare la formula in =Arance * 0,5 per giugno, luglio e agosto.

Determinare se una formula si trova in un campo calcolato o un elemento calcolato

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Elenca formule.

   

3. Nell'elenco delle formule individuare la formula da modificare riportata in Campo calcolato o Elemento calcolato.
   
   Quando sono disponibili più formule per un elemento calcolato, nella colonna B della formula predefinita immessa quando è stato creato l'elemento sarà visualizzato il nome dell'elemento calcolato. Per le altre formule dell'elemento calcolato, la colonna B conterrà sia il nome dell'elemento calcolato, sia i nomi degli elementi intersecanti.

   Si supponga ad esempio di avere una formula predefinita per un elemento calcolato denominato MioElemento e un'altra formula per questo elemento definita MioElemento Vendite Gennaio. Nella tabella pivot questa formula si troverà nella cella Vendite della riga MioElemento e della colonna Gennaio.

4. Continuare usando uno dei metodi di modifica seguenti.

Modificare una formula di un campo calcolato

1. Fare clic sulla tabella pivot.

   Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Campo calcolato.

   

3. Nella casella Nome selezionare il campo calcolato per cui si vuole modificare la formula.

4. Nella casella Formula modificare la formula.

5. Fare clic Modifica.

Modificare una singola formula per un elemento calcolato

1. Fare clic sul campo contenente l'elemento calcolato.

2. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Elemento calcolato

   

3. Nella casella Nome selezionare l'elemento calcolato.

4. Nella casella Formula modificare la formula.

5. Fare clic Modifica.

Modificare una singola formula per una cella specifica di un elemento calcolato

1. Fare clic sulla cella per cui si vuole modificare la formula.

   Per modificare la formula per più celle, tenere premuto CTRL e fare clic sulle diverse celle.

2. Nella barra della formula digitare le modifiche alla formula.

   Suggerimento: Se sono presenti più elementi calcolati o formule, si può definire l'ordine di calcolo. Per altre informazioni, vedere Definire l'ordine di calcolo per più elementi calcolati o formule.

1. Determinare se la formula si trova in un campo calcolato o un elemento calcolato.

   I campi calcolati sono visualizzati nell'elenco dei campi della tabella pivot, mentre gli elementi calcolati sono visualizzati come elementi all'interno di altri campi.

2. Eseguire una delle operazioni seguenti:

   • Per eliminare un campo calcolato, fare clic in un punto qualsiasi della tabella pivot.

   • Per eliminare un elemento calcolato, nella tabella pivot fare clic sul campo che contiene l'elemento da eliminare.

     Verrà visualizzato il gruppo Strumenti tabella pivot con le schede Analizza e Progettazione.

3. Nel gruppo Calcoli della scheda Analizza fare clic su Campi, elementi e set e quindi su Campo calcolato o Elemento calcolato.

   

4. Nella casella Nome selezionare il campo o l'elemento da eliminare.

5. Fare clic su Elimina.