Calcolare più risultati usando una tabella dati

In Microsoft Excel le tabelle dati fanno parte di una serie di comandi noti come strumenti di analisi di simulazione. Quando si creano e analizzano le tabelle dati, si sta eseguendo un'analisi di simulazione.

L'analisi di simulazione è il processo di modifica dei valori nelle celle per osservare l'effetto di tali modifiche sul risultato delle formule nel foglio di lavoro. Ad esempio, è possibile usare una tabella dati per variare il tasso di interesse e la durata dei termini di un prestito per determinare i possibili importi delle rate mensili.

Tipi di analisi di simulazione    

In Excel sono disponibili tre tipi di strumenti per l'analisi di simulazione: scenari, tabelle dati e Ricerca obiettivo. Gli scenari e le tabelle dati accettano set di valori di input per calcolare i possibili risultati. Ricerca obiettivo è distintamente diverso, usa un singolo risultato e calcola i valori di input possibili che produrrebbero tale risultato.

Analogamente agli scenari, le tabelle dati consentono di esplorare un set di possibili risultati. A differenza degli scenari, le tabelle dati mostrano tutti i risultati in una tabella in un unico foglio.di lavoro. Le tabelle dati consentono di esaminare facilmente una serie di possibilità in modo immediato. I risultati sono facilmente leggibili in formato tabulare perché si usano solo una o due variabili.

Una tabella dati non può accettare più di due variabili. Se si vogliono analizzare più di due variabili, è possibile usare gli scenari. Anche se è limitata solo a una o due variabili, una per la cella di input della riga e una per quella della colonna, una tabella dati può includere qualsiasi numero di valori variabili. Uno scenario può avere al massimo 32 valori diversi, ma è possibile creare un numero illimitato di scenari.

Per altre informazioni, vedere Introduzione all'analisi di simulazione.

È possibile creare tabelle dati a una o due variabili, a seconda del numero di variabili e formule che si vuole testare.

Tabelle dati a una variabile    

Usare una tabella dati a una variabile per vedere come valori diversi di una stessa variabile in una o più formule incideranno sui risultati di tali formule. È possibile usare una tabella dati di questo tipo, ad esempio, per verificare gli effetti di tassi di interesse diversi sulle rate mensili di un prestito applicando la funzione RATA. I valori variabili vengono inseriti in una colonna o in una riga e i risultati vengono visualizzati in una colonna o riga adiacente.

Nella figura seguente, la cella D2 contiene la formula di pagamento = RATA(B3/12,B4,-B5), che fa riferimento alla cella di input B3.

Tabelle dati a due variabili    

Usare una tabella dati a due variabili per vedere come valori diversi di due variabili in una formula incideranno sui risultati di tale formula. Usare una tabella dati di questo tipo ad esempio per verificare gli effetti di combinazioni diverse di tassi di interesse e termini di restituzione del prestito sulla rata mensile di un mutuo.

Nella figura seguente la cella C2 contiene la formula di pagamento = RATA(B3/12,B4,-B5), che usa due celle di input, B3 e B4.

 

Calcoli delle tabelle dati    

Ogni volta che un foglio di lavoro viene ricalcolato, vengono ricalcolate anche le tabelle dati, anche se non sono state effettuate modifiche ai dati. Per velocizzare il calcolo di un foglio di lavoro contenente una tabella dati, è possibile modificare le opzioni Calcolo in modo da ricalcolare automaticamente il foglio di lavoro ma non le tabelle dati. Per altre informazioni, vedere la sezione Velocizzare il calcolo in un foglio di lavoro contenente le tabelle dati.

Una tabella dati a una variabile contiene i valori di input elencati in una singola colonna (orientati a colonna) o in una riga (orientati a riga). Qualsiasi formula in una tabella dati a una variabile deve fare riferimento a una sola cella di input.

Effettuare questi passaggi:

1. Digitare l'elenco di valori da sostituire in una cella di input, in una colonna o in una riga. Lasciare vuote alcune righe e colonne ai lati dei valori.

2. Eseguire una delle operazioni seguenti:

   • Se la tabella dati è orientata a colonna (i valori variabili si trovano in una colonna), digitare la formula nella cella una riga sopra e una cella a destra della colonna di valori. Questa tabella dati a una variabile mostrata è orientata a colonna e la formula è contenuta nella cella D2.
     
     
     
     Per esaminare gli effetti di valori diversi su altre formule, digitare le formule aggiuntive nelle celle a destra della prima formula.

   • Se la tabella dati è orientata a riga (i valori variabili si trovano in una riga), digitare la formula nella cella una colonna a sinistra del primo valore e una cella sotto la riga di valori.
     
     Per esaminare gli effetti di valori diversi su altre formule, digitare le formule aggiuntive sotto la prima formula.

3. Selezionare l'intervallo di celle contenente le formule e i valori da sostituire. Nella figura precedente, questo intervallo è C2:D5.

4. Nella scheda Dati selezionare Analisi di simulazione > Tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016.

5. Eseguire una delle operazioni seguenti:

   • Se la tabella dati è orientata a colonna, immettere il riferimento di cella per la cella di input nel campo Cella di input a colonna. Nella figura precedente la cella di input è B3.

   • Se la tabella dati è orientata a riga, immettere il riferimento alla cella per la cella di input nel campo Cella di input a riga.

     Nota: Dopo aver creato la tabella dati, è consigliabile modificare il formato delle celle risultato. Nella figura le celle dei risultati sono formattate come valuta.

È necessario che le formule utilizzate in una tabella dati a una variabile facciano riferimento alla stessa cella di input.

Seguire questa procedura

1. Eseguire una delle operazioni seguenti:

   • Se la tabella dati è orientata a colonna, immettere la nuova formula in una cella vuota a destra di una formula esistente nella prima riga della tabella dati.

   • Se la tabella dati è orientata a riga, immettere la nuova formula in una cella vuota sotto una formula esistente nella prima colonna della tabella dati.

2. Selezionare l'intervallo di celle contenente la tabella dati e la nuova formula.

3. Nella scheda Dati selezionare Analisi di simulazione > Tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016.

4. Eseguire una delle operazioni seguenti:

   • Se la tabella dati è orientata a colonna, immettere il riferimento alla cella per la cella di input nella casella Cella di input a colonna.

   • Se la tabella dati è orientata a riga, immettere il riferimento alla cella per la cella di input nella casella Cella di input a riga.

Una tabella dati a due variabili utilizza una formula contenente due elenchi di valori di input. È necessario che la formula faccia riferimento a due celle di input diverse.

Effettuare questi passaggi:

1. Immettere la formula che si riferisce alle due celle di input in una cella del foglio di lavoro.

   Nell'esempio seguente, in cui i valori iniziali della formula vengono immessi nelle celle B3, B4 e B5, digitare la formula =RATA(B3/12,B4,-B5) nella cella C2.

2. Digitare un elenco di valori di input nella stessa colonna, sotto la formula.

   In questo caso, digitare tassi di interesse diversi nelle celle C3, C4 e C5.

3. Immettere il secondo elenco nella stessa riga della formula, a destra di quest'ultima.

   Digitare i termini del prestito (in mesi) nelle celle D2 ed E2.

4. Selezionare l'intervallo di celle contenente la formula (C2), sia la riga che la colonna di valori (C3:C5 e D2:E2) e le celle in cui devono essere visualizzati i valori calcolati (D3:E5).

   In questo caso, selezionare l'intervallo C2:E5.

5. Nella scheda Dati, nel gruppo Strumenti dati o Previsione (in Excel 2016), selezionare Analisi di simulazione > Tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016.

6. Nella casella Cella di input a riga immettere il riferimento alla cella di input per i valori di input della riga.
   Digitare cella B4 nella casella Cella di input a riga.

7. Nella casella Cella di input a colonna immettere il riferimento alla cella di input per i valori di input della colonna.
   Digitare B3 nella casella Cella di input a colonna.

8. Selezionare OK.

Esempio di tabella dati a due variabili

Una tabella dati a due variabili può mostrare in che modo diverse combinazioni di tassi di interesse e termini del prestito avranno effetto su un mutuo mensile. Nella figura seguente la cella C2 contiene la formula di pagamento = RATA(B3/12,B4,-B5), che usa due celle di input, B3 e B4.

Quando si imposta questa opzione di calcolo, non viene eseguito alcun calcolo della tabella dati quando viene eseguito un ricalcolo dell'intera cartella di lavoro. Per ricalcolare manualmente la tabella dati, selezionarne le formule e quindi premere F9.

Seguire questa procedura per migliorare le prestazioni di calcolo:

1. Fare clic su File > Opzioni > Formule.

2. Nella sezione Opzioni di calcolo selezionare Automatico.

   Suggerimento: Facoltativamente, nella scheda Formule selezionare la freccia in Opzioni di calcolo, quindi selezionare Automatico.

È possibile usare alcuni altri strumenti di Excel per eseguire l'analisi di simulazione se si hanno obiettivi specifici o set più grandi di dati variabili.

Ricerca obiettivo

Se si conosce il risultato desiderato di una formula, ma non si sa quale valore di input è necessario immettere nella formula per ottenerlo, usare la funzionalità Ricerca obiettivo. Vedere l'articolo Utilizzare Ricerca obiettivo per trovare il risultato desiderato modificando un valore di input.

Risolutore di Excel

È possibile usare il componente aggiuntivo Risolutore di Excel per trovare il valore ottimale per un set di variabili di input. Il Risolutore usa un gruppo di celle, denominate variabili decisionali o semplicemente celle variabili, che contribuiscono al calcolo delle formule nelle celle obiettivo e nelle celle vincolo. Il Risolutore modifica inoltre i valori nelle celle variabili di decisione in modo da soddisfare i limiti nelle celle vincolo e produrre i risultati desiderati per la cella obiettivo. Per altre informazioni, vedere l'articolo Definire e risolvere un problema usando il Risolutore.