Analisi what-if con tabelle dati

Con una tabella dati in Excel, puoi facilmente variare uno o due input ed eseguire analisi what-if. Una tabella dati è un intervallo di celle in cui è possibile modificare i valori in alcune celle e trovare risposte diverse a un problema.

Esistono due tipi di tabelle dati:

  • Tabelle dati a una variabile
  • Tabelle dati a due variabili

Se hai più di due variabili nel tuo problema di analisi, devi utilizzare lo strumento Gestione scenari di Excel. Per i dettagli, fare riferimento al capitolo - Analisi what-if con Scenario Manager in questo tutorial.

Tabelle dati a una variabile

È possibile utilizzare una tabella dati a una variabile se si desidera vedere come i diversi valori di una variabile in una o più formule cambieranno i risultati di tali formule. In altre parole, con una tabella dati a una variabile, è possibile determinare in che modo la modifica di un ingresso cambia un numero qualsiasi di uscite. Lo capirai con l'aiuto di un esempio.

Example

C'è un prestito di 5.000.000 per un mandato di 30 anni. Vuoi conoscere i pagamenti mensili (EMI) per vari tassi di interesse. Potresti anche essere interessato a conoscere l'importo degli interessi e del capitale che viene pagato nel secondo anno.

Analisi con tabella dati a una variabile

L'analisi con una tabella dati a una variabile deve essere eseguita in tre passaggi:

Step 1 - Imposta lo sfondo richiesto.

Step 2 - Crea la tabella dati.

Step 3 - Eseguire l'analisi.

Cerchiamo di capire questi passaggi in dettaglio:

Passaggio 1: imposta lo sfondo richiesto

  • Supponiamo che il tasso di interesse sia del 12%.

  • Elenca tutti i valori richiesti.

  • Assegna un nome alle celle che contengono i valori, in modo che le formule abbiano nomi anziché riferimenti di cella.

  • Impostare i calcoli per EMI, Interesse cumulativo e Principal cumulativo con le funzioni di Excel - rispettivamente PMT, CUMIPMT e CUMPRINC.

Il tuo foglio di lavoro dovrebbe apparire come segue:

Puoi vedere che le celle nella colonna C sono denominate come indicato nelle celle corrispondenti nella colonna D.

Passaggio 2: creare la tabella dati

  • Digita l'elenco dei valori, ad esempio i tassi di interesse che desideri sostituire nella cella di input in basso nella colonna E, come segue:

    Come osservi, c'è una riga vuota sopra i valori del tasso di interesse. Questa riga è per le formule che desideri utilizzare.

  • Digita la prima funzione (PMT) nella cella una riga sopra e una cella a destra della colonna dei valori. Digita le altre funzioni (CUMIPMT and CUMPRINC) nelle celle a destra della prima funzione.

    Ora, le due righe sopra i valori del tasso di interesse hanno il seguente aspetto:

    La tabella dati appare come di seguito:

Passaggio 3: eseguire l'analisi con lo strumento Tabella dati di analisi what-if

  • Seleziona l'intervallo di celle che contiene le formule ei valori che desideri sostituire, ovvero seleziona l'intervallo - E2: H13.

  • Fare clic sulla scheda DATI sulla barra multifunzione.

  • Fare clic su Analisi what-if nel gruppo Strumenti dati.

  • Seleziona Tabella dati nell'elenco a discesa.

Data Table appare la finestra di dialogo.

  • Fare clic sull'icona nella casella della cella di input della colonna.
  • Fare clic sulla cella Interest_Rate, che è C2.

Puoi vedere che la cella di input della colonna viene presa come $ C $ 2. Fare clic su OK.

La tabella dati è riempita con i risultati calcolati per ciascuno dei valori di input come mostrato di seguito -

Se puoi pagare un EMI di 54.000, puoi osservare che il tasso di interesse del 12,6% è adatto a te.

Tabelle dati a due variabili

È possibile utilizzare una tabella dati a due variabili se si desidera vedere come i diversi valori di due variabili in una formula cambieranno i risultati di quella formula. In altre parole, con una tabella dati a due variabili, è possibile determinare in che modo la modifica di due input cambia un singolo output. Lo capirai con l'aiuto di un esempio.

Example

C'è un prestito di 50.000.000. Vuoi sapere in che modo diverse combinazioni di tassi di interesse e durata del prestito influenzeranno il pagamento mensile (EMI).

Analisi con tabella dati a due variabili

L'analisi con la tabella dati a due variabili deve essere eseguita in tre passaggi:

Step 1 - Imposta lo sfondo richiesto.

Step 2 - Crea la tabella dati.

Step 3 - Eseguire l'analisi.

Passaggio 1: imposta lo sfondo richiesto

  • Supponiamo che il tasso di interesse sia del 12%.

  • Elenca tutti i valori richiesti.

  • Assegna un nome alle celle contenenti i valori, in modo che la formula abbia nomi invece di riferimenti di cella.

  • Imposta il calcolo per EMI con la funzione Excel - PMT.

Il tuo foglio di lavoro dovrebbe apparire come segue:

Puoi vedere che le celle nella colonna C sono denominate come indicato nelle celle corrispondenti nella colonna D.

Passaggio 2: creare la tabella dati

  • genere =EMI nella cella F2.

  • Digitare il primo elenco di valori di input, ovvero i tassi di interesse nella colonna F, iniziando con la cella sotto la formula, ovvero F3.

  • Digita il secondo elenco di valori di input, ovvero il numero di pagamenti nella riga 2, iniziando dalla cella a destra della formula, ovvero G2.

    La tabella dei dati ha il seguente aspetto:

Eseguire l'analisi con la tabella dei dati dello strumento di analisi What-If

  • Seleziona l'intervallo di celle che contiene la formula e le due serie di valori che desideri sostituire, ovvero seleziona l'intervallo - F2: L13.

  • Fare clic sulla scheda DATI sulla barra multifunzione.

  • Fare clic su Analisi what-if nel gruppo Strumenti dati.

  • Seleziona Tabella dati dall'elenco a discesa.

Viene visualizzata la finestra di dialogo Tabella dati.

  • Fare clic sull'icona nella casella della cella di input della riga.
  • Fare clic sulla cella NPER, che è C3.
  • Di nuovo, fai clic sull'icona nella casella della cella di input della riga.
  • Quindi, fare clic sull'icona nella casella della cella di input della colonna.
  • Fare clic sulla cella Tasso_interesse, che è C2.
  • Di nuovo, fai clic sull'icona nella casella della cella di input della colonna.

Vedrai che la cella di input della riga viene presa come $ C $ 3 e la cella di input della colonna viene presa come $ C $ 2. Fare clic su OK.

La tabella dati viene riempita con i risultati calcolati per ciascuna combinazione dei due valori di input -

Se puoi pagare un EMI di 54.000, il tasso di interesse del 12,2% e 288 EMI sono adatti a te. Ciò significa che la durata del prestito sarebbe di 24 anni.

Calcoli della tabella dati

Le tabelle dati vengono ricalcolate ogni volta che il foglio di lavoro che le contiene viene ricalcolato, anche se non sono state modificate. Per velocizzare i calcoli in un foglio di lavoro che contiene una tabella dati, è necessario modificare le opzioni di calcolo inAutomatically Recalculate il foglio di lavoro ma non le tabelle dati, come illustrato nella sezione successiva.

Accelerare i calcoli in un foglio di lavoro

Puoi velocizzare i calcoli in un foglio di lavoro contenente tabelle dati in due modi:

  • Dalle opzioni di Excel.
  • Dal nastro.

Dalle opzioni di Excel

  • Fare clic sulla scheda FILE sulla barra multifunzione.
  • Seleziona Opzioni dall'elenco nel riquadro di sinistra.

Viene visualizzata la finestra di dialogo Opzioni di Excel.

  • Dal riquadro di sinistra, seleziona Formulas.

  • Seleziona l'opzione Automatic except for data tables sotto Workbook Calculationnella sezione Opzioni di calcolo. Fare clic su OK.

Dal nastro

  • Fare clic sulla scheda FORMULE sulla barra multifunzione.

  • Clicca il Calculation Options nel gruppo Calcoli.

  • Selezionare Automatic Except for Data Tables nell'elenco a discesa.