Excel What-If Analysis offre una tabella dati. Questo è un brutto nome. Dovrebbe essere chiamato analisi della sensibilità. È fresco. Leggi qui.
Ricerca obiettivo consente di trovare l'insieme di input che portano a un risultato particolare. A volte, si desidera visualizzare molti risultati diversi da varie combinazioni di input. A condizione che tu abbia solo due celle di input da modificare, la tabella dati fornisce un modo rapido per confrontare le alternative.
Utilizzando l'esempio del pagamento del prestito, supponi di voler calcolare il prezzo per una varietà di saldi principali e per una varietà di termini.
Assicurati che la formula che desideri modellare sia nell'angolo in alto a sinistra di un intervallo. Metti vari valori per una variabile nella colonna di sinistra e vari valori per un'altra variabile nella parte superiore.
Dalla scheda Dati, seleziona Analisi what-if, tabella dati.
Hai valori lungo la riga superiore della tabella di input. Si desidera che Excel inserisca questi valori in una determinata cella di input. Specificare quella cella di input come cella di input riga.
Hai valori lungo la colonna di sinistra. Vuoi quelli collegati a un'altra cella di input. Specificare quella cella come cella di input della colonna.
Quando fai clic su OK, Excel ripeterà la formula nella colonna in alto a sinistra per tutte le combinazioni della riga superiore e della colonna sinistra. Nell'immagine seguente, vedi 60 diversi pagamenti di prestito basati su vari risultati.
Nota che ho formattato i risultati della tabella in modo che non abbiano decimali e ho usato Home, Formattazione condizionale, Scala colori per aggiungere l'ombreggiatura rosso / giallo / verde.
Ecco la parte migliore: questo tavolo è "live". Se modifichi le celle di input lungo la colonna di sinistra o la riga superiore, i valori nella tabella verranno ricalcolati. Di seguito, i valori a sinistra si concentrano sull'intervallo da $ 23.000 a $ 24.000.
Grazie a Owen W. Green per aver suggerito dei tavoli.
Guarda un video
- Tre strumenti what-if in Excel
- Ieri - Goal Seek
- Oggi - una tabella dati
- Ottimo per problemi a due variabili
- Curiosità: la funzione array TABLE non può essere inserita manualmente - non funzionerà
- Usa una scala di colori per colorare le risposte
- E se hai 3 variabili da cambiare? Scenari? No! Copia foglio di lavoro
- Le tabelle sono lente da calcolare: modalità di calcolo per tutte tranne le tabelle
- Grazie a Owen W. Green per aver suggerito questo suggerimento
Trascrizione del video
Impara Excel dal podcast, episodio 2034 - What-Ifs con una tabella dati!
Sto podcasting l'intero libro, fai clic sulla "i" nell'angolo in alto a destra per accedere alla playlist!
Oggi parleremo del secondo strumento sotto What-If Analysis, ieri abbiamo parlato di Goal Seek, oggi parleremo di una Data Table. Quindi abbiamo questo bel modello qui, questo è un piccolo modello, 3 celle di input, una formula. Ma questo modello potrebbe essere costituito da centinaia di celle di input, migliaia di righe, purché si riduca a una risposta finale, e vogliamo modellare questa risposta per diversi valori di 2-3 (?) Celle di input. Ad esempio, forse siamo interessati a guardare auto diverse, quindi dovunque da 20000 in su, quindi inserirò 20 e 21000 lì, afferrerò la maniglia di riempimento e trascineremo, portandola fino a 28000. In alto noi " stai guardando termini diversi, quindi un prestito di 36 mesi, un prestito di 42 mesi, un prestito di 48 mesi, 54, 60, 66 e persino 72.
Va bene ora, questo passaggio successivo è completamente facoltativo, ma mi aiuta davvero a pensarci, cambio sempre i colori dei valori in alto e dei valori a sinistra. E la cosa veramente importante qui è che quella cella d'angolo, quell'importantissima cella d'angolo, deve essere la risposta che stiamo cercando di modellare, va bene. Quindi devi iniziare a selezionare da quella cella d'angolo con la risposta, quindi selezionare tutte le righe e tutte le colonne. Quindi entriamo in dati, analisi what-if e una tabella dati, e qui si richiedono due cose, ed ecco come la pensereste. Dice che c'è un sacco di elementi diversi lungo la riga superiore della tabella, voglio prendere quegli elementi, uno alla volta, e collegarli al modello, dove dovremmo inserire? Quindi questi elementi, questi sono termini, dovrebbero andare nella cella B2. E poi,c'è un sacco di elementi lungo la colonna di sinistra, vogliamo prenderli, uno alla volta, e collegarli a B1, così, va bene e facciamo clic su OK, BAM, esegue questo modello ancora e ancora e ancora .
Ora solo un po 'di pulizia qui, vado sempre a fare Home, e probabilmente 0 decimali, in questo modo. E forse un po 'di formattazione condizionale, scale di colore e andiamo con numeri rossi per numeri grandi e verdi per piccoli, solo per darmi un modo, sai, per tracciarlo visivamente. Ora sembra che se stiamo girando per $ 425, siamo più o meno, sai, in questo posto o in questo posto, o sai, forse qui, ci avvicineremo tutti a $ 425. Quindi posso vedere quali sono le varie probabilità, le nostre varie combinazioni, per portarci a quei valori.
Ora un paio di cose, questa parte qui dentro, è in realtà una grande formula di matrice, quindi = TABLE (B2, B1), l'input di riga e colonna. Questo è curioso, non ti è permesso digitarlo, puoi solo crearlo usando Dati, Analisi what-if, devi usare quella finestra di dialogo. Se provi a digitare quella formula, premi Ctrl + Maiusc + Invio, non funzionerà, giusto? Quindi, è una funzione in Excel, ma se sei abbastanza intelligente da digitarla, peccato, non funzionerà, ma ricalcola costantemente. Quindi, se determiniamo che stiamo guardando solo i termini da 48, e vogliamo guardare in gruppi di 3 o qualcosa del genere, così come cambio questi numeri, tutto sta calcolando. In questo caso, sta facendo solo una formula per ciascuna, ma immagina che se stessimo facendo 100 formule, questo verrà rallentato notevolmente. Quindi fuori qui sotto Formule, là 'In realtà è un'opzione Opzioni di calcolo, Automatica o Manuale, ce n'è una terza che dice "Sì, ricalcola tutto tranne le tabelle dati, non continuare a ricalcolare la tabella dati". Perché questo può essere un enorme ostacolo ai tempi di calcolo.
Va bene ora, le tabelle di dati sono fantastiche quando hai due variabili da modificare, ma abbiamo tre variabili da modificare. E se ci fossero tassi di interesse diversi, consiglio di andare allo Scenario Manager? NO, non consiglio MAI di andare allo Scenario Manager! In questo caso abbiamo 9x7, ovvero 63 diversi scenari che abbiamo calcolato qui, per creare 63 diversi scenari di Scenario Manager richiederebbero 2 ore, è orribile. Non lo copro nel libro "MrExcel XL", perché sono i 40 migliori consigli. Questo è probabilmente nel mio libro "Power Excel" con 567 misteri di Excel risolti, ma sono sicuro di essermi lamentato di quanto sia miserabile da usare, non mi vedrai fare lo Scenario Manager qui. Se davvero dovessimo farlo per diverse velocità, la cosa migliore da fare è semplicemente Ctrl + trascinamento, prendere questo foglio, Ctrl + trascinamento, Ctrl + trascinamento,Trascina tenendo premuto il tasto Ctrl, quindi modifica le tariffe su ogni foglio. Quindi, se potessimo ottenere un 5% o 4,75% o qualcosa del genere e così via, giusto, non esiste un modo semplice per impostarlo per 3 variabili in Scenario Manager. Bene, "40 Greatest Excel Tips of All Time", tutto in questo libro, puoi acquistare il libro, fai clic sulla "i" nell'angolo in alto a destra.
Riepilogo episodio di oggi: ci sono tre strumenti What-If in Excel, ieri abbiamo parlato di Goal Seek, oggi la tabella dei dati. È fantastico per problemi a 2 variabili, domani ne vedrai uno con un problema a 1 variabile. La funzione di matrice della tabella non può essere inserita manualmente, non funzionerà, è necessario utilizzare Dati, analisi what-if, tabella dati. Ho usato una scala di colori, Home, Formattazione condizionale, Scale di colori, per colorare le risposte. Se hai 3 variabili da cambiare, fai scenari? No, basta fare delle copie del foglio di lavoro o delle copie della tabella, sono lente da calcolare, soprattutto con un modello complesso. Esiste una modalità di calcolo automatica per tutti tranne le tabelle e Owen W. Green ha suggerito di includere questa funzione nei libri.
Quindi grazie a lui, e grazie a te per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2034.xlsx
