Come creare e utilizzare una tabella dati in Excel

Il tutorial mostra come utilizzare le tabelle di dati per l’analisi What-If in Excel. Scopri come creare una tabella a una variabile e a due variabili per visualizzare gli effetti di uno o due valori di input sulla formula e come impostare una tabella dati per valutare più formule contemporaneamente.

Hai creato una formula complessa dipendente da più variabili e vuoi sapere come cambiare quegli input cambia i risultati., Invece di testare ciascuna variabile individualmente, crea una tabella di dati di analisi What-if e osserva tutti i possibili risultati con una rapida occhiata!

• Tabella dati in Excel
• Crea una tabella dati variabile
• Crea due tabelle dati variabili
• Tabella dati per valutare più formule
• Tabelle dati Excel – 3 cose che dovresti sapere
• Elimina una tabella dati in Excel
• Modifica i risultati della tabella dati
• Ricalcola manualmente la tabella dati

Che cos’è una tabella dati in Excel?,

In Microsoft Excel, una tabella dati è uno degli strumenti di analisi What-If che consente di provare diversi valori di input per le formule e vedere come le modifiche in tali valori influenzano l’output delle formule.

Le tabelle di dati sono particolarmente utili quando una formula dipende da diversi valori e si desidera sperimentare diverse combinazioni di input e confrontare i risultati.

Attualmente esistono una tabella di dati variabili e due tabelle di dati variabili. Sebbene limitata a un massimo di due celle di input diverse, una tabella di dati consente di testare tutti i valori delle variabili che si desidera.,

Come creare una tabella di dati variabili in Excel

Una tabella di dati variabili in Excel consente di testare una serie di valori per una singola cella di input e mostra come tali valori influenzano il risultato di una formula correlata.,

Per aiutarti a capire meglio questa funzione, seguiremo un esempio specifico piuttosto che descrivere passaggi generici.

Supponiamo che state pensando di depositare i vostri risparmi in una banca, che paga 5% di interesse che composti mensile. Per controllare diverse opzioni, hai creato il seguente calcolatore di interessi composti dove:

• B8 contiene la formula FV che calcola il saldo di chiusura.
• B2 è la variabile che si desidera testare (investimento iniziale).,

E ora, facciamo una semplice analisi What-If per vedere quali saranno i tuoi risparmi in 5 anni a seconda dell’importo del tuo investimento iniziale, che va da $1.000 a $6.000.

Ecco i passaggi per creare una tabella di dati a una variabile:

1. Immettere i valori delle variabili in una colonna o su una riga. In questo esempio, creeremo una tabella di dati orientata alle colonne, quindi digitiamo i nostri valori variabili in una colonna (D3:D8) e lasciamo almeno una colonna vuota a destra per i risultati.,
2. Digita la tua formula nella cella una riga sopra e una cella a destra dei valori della variabile (E2 nel nostro caso). Oppure, collega questa cella alla formula nel set di dati originale (se decidi di cambiare la formula in futuro, dovrai aggiornare solo una cella). Scegliamo quest’ultima opzione e inseriamo questa semplice formula in E2: =B8
   
   Tip., Se si desidera esaminare l’impatto dei valori delle variabili su altre formule che si riferiscono alla stessa cella di input, immettere le formule aggiuntive a destra della prima formula, come mostrato in questo esempio.
3. Selezionare l’intervallo della tabella dati, incluse la formula, le celle dei valori variabili e le celle vuote per i risultati (D2:E8).,
4. Vai alla scheda Dati > Dati gruppo Strumenti, fare clic su il pulsante Analisi, e quindi fare clic su Tabella Dati…
   
5. Nella Tabella di Dati finestra di dialogo, fare clic nella casella cella di Input per Colonna (perché i nostri Investimenti valori in una colonna), e selezionare la variabile di cellule a cui fa riferimento la formula. In questo esempio, selezioniamo B3 che contiene il valore di investimento iniziale.
   
6. Fare clic su OK e Excel popolerà immediatamente le celle vuote con risultati corrispondenti al valore della variabile nella stessa riga.,
7. Applica il formato numerico desiderato ai risultati (Valuta nel nostro caso) e sei a posto!

Ora puoi dare una rapida occhiata alla tua tabella di dati a una variabile, esaminare i possibili saldi e scegliere la dimensione ottimale del deposito:

Tabella di dati orientata alle righe

L’esempio sopra mostra come impostare una tabella di dati verticale o orientata alla colonna in Excel., Se preferisci un layout orizzontale, ecco cosa devi fare:

1. Digita i valori delle variabili in una riga, lasciando almeno una colonna vuota a sinistra (per la formula) e una riga vuota sotto (per i risultati). Per questo esempio, inseriamo i valori delle variabili nelle celle F3: J3.
2. Inserisci la formula nella cella che è una colonna a sinistra del tuo primo valore variabile e una cella sotto (E4 nel nostro caso).,
3. Fare una tabella di dati, come descritto sopra, ma, immettere il valore in ingresso (B3) nella Riga casella cella di input:
   
4. fare Clic su OK, e si avrà il seguente risultato:
   

Come fare una due variabili tabella di dati in Excel

dati a due variabili tabella mostra come le varie combinazioni di 2 set di valori di variabili influenzano il risultato della formula. In altre parole, mostra come la modifica di due valori di input della stessa formula cambia l’output.,

I passaggi per creare una tabella di dati a due variabili in Excel sono fondamentalmente gli stessi dell’esempio precedente, tranne per il fatto che si immettono due intervalli di possibili valori di input, uno in una riga e un altro in una colonna.

Per vedere come funziona, usiamo la stessa calcolatrice interesse composto ed esaminare gli effetti della dimensione dell’investimento iniziale e il numero di anni sul saldo. Per farlo, imposta la tua tabella dati in questo modo:

1. Inserisci la tua formula in una cella vuota o collega quella cella alla tua formula originale., Assicurati di avere abbastanza colonne vuote a destra e righe vuote qui sotto per accogliere i valori delle variabili. Come prima, colleghiamo la cella E2 alla formula FV originale che calcola il saldo:=B8
2. Digitare un set di valori di input sotto la formula, nella stessa colonna (valori di investimento in E3:E8).
3. Inserire l’altro insieme di valori variabili a destra della formula, nella stessa riga (numero di anni in F2:H2).,

   A questo punto, la tua tabella di dati a due variabili dovrebbe essere simile a questa:
   

4. Seleziona l’intero intervallo della tabella di dati inclusa la formula, la riga e la colonna dei valori delle variabili e le celle in cui appariranno i valori calcolati. Selezioniamo l’intervallo E2: H8.,
5. Crea una tabella dati nel modo già familiare: Scheda dati> Pulsante Analisi What-If> Tabella dati
6. Nella casella Cella di input riga, inserisci il riferimento alla cella di input per i valori delle variabili nella riga (in questo esempio, è B6 contenente il valore Anni).
7. Nella casella Cella di input colonna, immettere il riferimento alla cella di input per i valori delle variabili nella colonna (B3 contenente il valore di investimento iniziale).
8. Fare clic su OK.,n nostro caso), e analizzare i risultati:

   

Dati di tabella di confronto di risultati multipli

Se si desidera valutare più di una formula, allo stesso tempo, costruire la vostra tabella di dati, come mostrato negli esempi precedenti, e inserire la formula supplementare(s):

• A destra della prima formula in caso di una verticale tabella di dati organizzati in colonne
• Sotto la prima formula in caso di orizzontale tabella di dati organizzati in righe

Per il “multi-formula” tabella dati per funzionare correttamente, tutte le formule devono fare riferimento alla stessa cella di input.,

Ad esempio, aggiungiamo un’altra formula alla nostra tabella di dati a una variabile per calcolare l’interesse e vedere come è influenzato dalla dimensione dell’investimento iniziale. Ecco cosa facciamo:

1. Nella cella B10, calcola l’interesse con questa formula: =B8-B3
2. Disporre i dati di origine della tabella dati come abbiamo fatto in precedenza: valori variabili in D3:D8 ed E2 collegati a B8 (formula di equilibrio).,
3. Aggiungi un’altra colonna all’intervallo della tabella dati (colonna F) e collega F2 a B10 (formula di interesse):
   
4. Seleziona l’intervallo della tabella dati esteso (D2:F8).
5. Aprire la finestra di dialogo Tabella dati facendo clic su Scheda dati> Analisi what-If > Tabella dati Table
6. Nella casella Cella di input colonna, fornire la cella di input (B3) e fare clic su OK.,

Voilà, ora puoi osservare gli effetti dei tuoi valori variabili su entrambe le formule:

Tabella dati in Excel – 3 cose che dovresti sapere

Per utilizzare efficacemente le tabelle di dati in Excel, tieni presente questi 3 semplici fatti:

1. Per creare una tabella di dati con successo, le celle di input stesso foglio della tabella dati.,
2. Microsoft Excel utilizza la funzione TABLE (row_input_cell, colum_input_cell) per calcolare i risultati della tabella di dati:
   • Nella tabella di dati a una variabile, uno degli argomenti viene omesso, a seconda del layout (orientato alla colonna o alla riga). Ad esempio, nella nostra tabella di dati a una variabile orizzontale, la formula è =TABLE(, B3) dove B3 è la cella di input della colonna.
   • Nella tabella di dati a due variabili, entrambi gli argomenti sono a posto. Ad esempio,=TABLE(B6, B3) dove B6 è la cella di input della riga e B3 è la cella di input della colonna.,

   La funzione TABELLA viene immessa come formula di matrice. Per assicurarsi di ciò, selezionare qualsiasi cella con il valore calcolato, guardare la barra della formula e notare le {parentesi graffe} attorno alla formula. Tuttavia, non è una normale formula di array: non è possibile digitarla nella barra della formula né modificarne una esistente. È solo “per spettacolo”.

3. Poiché i risultati della tabella dati sono calcolati con una formula di matrice, le celle risultanti non possono essere modificate singolarmente. È possibile modificare o eliminare solo l’intero array di celle come spiegato di seguito.,

Come eliminare una tabella di dati in Excel

Come accennato in precedenza, Excel non consente l’eliminazione di valori nelle singole celle contenenti i risultati. Ogni volta che si tenta di farlo, verrà visualizzato un messaggio di errore “Impossibile modificare parte di una tabella di dati”.

Tuttavia, è possibile cancellare facilmente l’intera matrice dei valori risultanti. Ecco come:

1. A seconda delle tue esigenze, seleziona tutte le celle della tabella dati o solo le celle con i risultati.
2. Premere il tasto Canc.

Fatto!, 🙂

Come modificare i risultati della tabella dati

Poiché non è possibile modificare parte di un array in Excel, non è possibile modificare singole celle con valori calcolati. È possibile sostituire tutti questi valori solo con uno proprio eseguendo questi passaggi:

1. Selezionare tutte le celle risultanti.
2. Elimina la formula della TABELLA nella barra della formula.
3. Digitare il valore desiderato e premere Ctrl + Invio.,

Questo inserirà lo stesso valore in tutte le celle selezionate:

Una volta che la formula della TABELLA è scomparsa, la precedente tabella di dati diventa un normale intervallo e sei libero di modificare normalmente qualsiasi singola cella.

Come ricalcolare manualmente la tabella dati

Se una tabella di dati di grandi dimensioni con più valori e formule variabili rallenta Excel, è possibile disabilitare i ricalcoli automatici in quella e in tutte le altre tabelle di dati.,

Per questo, vai alla scheda Formule> Gruppo di calcolo, fai clic sul pulsante Opzioni di calcolo, quindi fai clic su Automatico Eccetto tabelle dati.

Questo disattiva i calcoli automatici della tabella dati e velocizza i ricalcoli dell’intera cartella di lavoro.

Per ricalcolare manualmente la tabella dati, selezionare le celle risultanti, ovvero le celle con formule TABLE() e premere F9.

Questo è il modo in cui si crea e si utilizza una tabella di dati in Excel., Per avere uno sguardo più da vicino gli esempi discussi questo questo tutorial, siete invitati a scaricare il nostro esempio Excel Tabelle di dati cartella di lavoro. Vi ringrazio per la lettura e sarei felice di rivedervi la prossima settimana!

• Analisi What-If con Ricerca obiettivo in Excel
• Come utilizzare il risolutore in Excel: passo dopo passo con esempi
• Come raggruppare le righe in Excel