Una tabella dati è un intervallo di celle in cui è possibile modificare i valori in alcune celle e ottenere risposte diverse a un problema. Un buon esempio di tabella dati utilizza la funzione RATA con importi e tassi di interesse diversi per calcolare l'importo accessibile di un mutuo per la casa. La sperimentazione con valori diversi per osservare la variazione corrispondente nei risultati è un'attività comune nell'analisi dei dati.
In Microsoft Excel le tabelle dati fanno parte di una serie di comandi noti come strumenti di analisi What-If. Quando si creano e si analizzano tabelle dati, si esegue 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 valutare i potenziali importi di pagamento mensili.
Tipi di analisi di simulazione
Esistono tre tipi di strumenti di analisi di simulazione in Excel: scenari, tabelle dati e ricerca obiettivo. Gli scenari e le tabelle dati usano set di valori di input per calcolare i possibili risultati. Ricerca obiettivo è distintamente diversa, 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 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 la cella di input della colonna, una tabella dati può includere tutti i valori di variabile desiderati. Uno scenario può avere al massimo 32 valori diversi, ma è possibile creare un numero illimitato di scenari.
Per altre informazioni, vedere Introduzione all'analisi What-If.
Creare tabelle dati a una o due variabili, a seconda del numero di variabili e formule da 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. È ad esempio possibile usare una tabella dati a una variabile per vedere in che modo i diversi tassi di interesse incidono sulla rata mensile del mutuo usando 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 che contiene una tabella dati, è possibile modificare le opzioni di 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 che contiene tabelle dati.
Una tabella dati a una variabile contiene i valori di input in una singola colonna (orientata per colonna) o in una riga (orientata per riga). Qualsiasi formula in una tabella dati a una variabile deve fare riferimento a una sola cella di input.
Effettuare questi passaggi:
-
Digitare l'elenco di valori da sostituire nella cella di input, in basso di una colonna o su una riga. Lasciare alcune righe e colonne vuote su entrambi i lati dei valori.
-
Eseguire una delle operazioni seguenti:
-
Se la tabella dati è orientata a colonne (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 è orientata per colonna e la formula è contenuta nella cella D2.
-
Se la tabella dati è orientata per 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 vari valori su altre formule, immettere le formule aggiuntive nelle celle sotto la prima formula.
-
-
Selezionare l'intervallo di celle contenente le formule e i valori da sostituire. Nella figura precedente, questo intervallo è C2:D5.
-
Nella scheda Dati selezionare Analisi di simulazione > tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016 ).
-
Eseguire una delle operazioni seguenti:
-
Se la tabella dati è orientata per colonna, immettere il riferimento di cella per la cella di input nel campo Cella di input per colonna. Nella figura precedente la cella di input è B3.
-
Se la tabella dati è orientata per riga, immettere il riferimento di cella per la cella di input nel campo Cella di input riga.
Nota: Dopo aver creato la tabella dati, è consigliabile modificare il formato delle celle risultato. Nella figura le celle risultato 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
-
Eseguire una delle operazioni seguenti:
-
Se la tabella dati è orientata a colonne, 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 per riga, immettere la nuova formula in una cella vuota sotto una formula esistente nella prima colonna della tabella dati.
-
-
Selezionare l'intervallo di celle contenente la tabella dati e la nuova formula.
-
Nella scheda Dati selezionare Analisi di simulazione > tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016 ).
-
Eseguire una delle operazioni seguenti:
-
Se la tabella dati è orientata a colonne, immettere il riferimento di cella per la cella di input nella casella Cella di input per colonna.
-
Se la tabella dati è orientata per riga, immettere il riferimento di cella per la cella di input nella casella Cella di input per 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:
-
In una cella del foglio di lavoro immettere la formula che fa riferimento alle due celle di input.
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.
-
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.
-
Immettere il secondo elenco nella stessa riga della formula, a destra.
Digitare i termini del prestito (in mesi) nelle celle D2 ed E2.
-
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.
-
Nel gruppo Strumenti dati o Previsione della scheda DatiExcel 2016 selezionare Analisi di simulazione >tabella dati nel gruppo Strumenti dati o Previsione di Excel 2016.
-
Nel campo Cella di input riga immettere il riferimento alla cella di input per i valori di input nella riga.Digitare la cella B4 nella casella Cella di input riga.
-
Nel campo Cella di input per colonna immettere il riferimento alla cella di input per i valori di input nella colonna.Digitare B3 nella casella Cella di input per colonna .
-
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, quando viene eseguito un ricalcolo sull'intera cartella di lavoro non viene eseguito alcun calcolo della tabella dati. Per ricalcolare manualmente la tabella dati, selezionarne le formule e quindi premere F9.
Seguire questa procedura per migliorare le prestazioni di calcolo:
-
Selezionare Opzioni > file > Formule.
-
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 previsto da una formula, ma non si conosce con precisione il valore di input necessario alla formula per ottenerlo, usare la caratteristica Goal-Seek. Vedere l'articolo Usare 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 funziona con un gruppo di celle, denominate variabili di decisione o semplicemente celle variabili, utilizzate per elaborare le formule nelle celle obiettivo e 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 questo articolo: Definire e risolvere un problema usando il Risolutore.
Collegando numeri diversi a una cella, è possibile trovare rapidamente risposte diverse a un problema. Un ottimo esempio consiste nell'usare la funzione RATA con tassi di interesse e periodi di prestito diversi (in mesi) per determinare la quantità di un prestito che è possibile pagare per una casa o un'auto. I numeri si immettono in un intervallo di celle denominato tabella dati.
La tabella dati è l'intervallo di celle B2:D8. È possibile modificare il valore in B4, l'importo del prestito e i pagamenti mensili nella colonna D vengono aggiornati automaticamente. Usando un tasso di interesse del 3,75%, D2 restituisce una rata mensile di $ 1.042,01 usando questa formula: =RATA(C2/12,$B$3$B$4).
È possibile usare una o due variabili, a seconda del numero di variabili e formule da testare.
Usare un test a una variabile per vedere in che modo valori diversi di una variabile in una formula modificano i risultati. È ad esempio possibile modificare il tasso di interesse per il pagamento mensile di un mutuo usando la funzione RATA. I valori variabili (i tassi di interesse) vengono immessi in una colonna o riga e i risultati vengono visualizzati in una colonna o riga nelle vicinanze.
In questa cartella di lavoro in tempo reale la cella D2 contiene la formula di pagamento =RATA(C2/12$B$3,$B$4). La cella B3 è la cella variabile , in cui è possibile collegare un periodo di validità diverso (numero di periodi di pagamento mensili). Nella cella D2 la funzione RATA inserisce il tasso di interesse 3,75%/12, 360 mesi e un prestito di $ 225.000 e calcola una rata mensile di $ 1.042,01.
Usare un test a due variabili per vedere in che modo valori diversi di due variabili in una formula modificheranno i risultati. È ad esempio possibile verificare diverse combinazioni di tassi di interesse e il numero di periodi di pagamento mensili per calcolare il pagamento di un mutuo.
In questa cartella di lavoro dinamica la cella C3 contiene la formula di pagamento =RATA($B$12/3,$B$2,B4), che usa due celle variabili, B2 e B3. Nella cella C2 la funzione RATA inserisce il tasso di interesse 3,875%/12, 360 mesi e un prestito di $ 225.000 e calcola un pagamento mensile di $ 1.058,03.
Servono altre informazioni?
È sempre possibile rivolgersi a un esperto della Tech Community di Excel o ottenere supporto nelle Community.
