Spessu, si hè necessariu per calculà u risultatu finale per vari cumminazioni di dati di input. Cusì l'utente puderà valutà tutte le opzioni possibili per l'azione, selezziunate quelli chì u risultatu di interazzioni li rispondenu, è, infine, scegliete l'opzione più ottimale. In Excel, ci hè un strumentu particulare per sta missione - "Tabulu di dati" ("Tavola di ricerca"). Scopemu cumu si usa per fà i scenarii sopra.
Vede ancu: Selezzione di parametri in Excel
Uta tavula di dati
Strumentu "Tabulu di dati" hè disignatu per calculà u risultatu cù diverse variazioni di una o duie variabili definite. Dopu u calculu, tutte e opzioni possibili appariranu in forma di una tavula, chjamata a matrice di l'analisi fattore. "Tabulu di dati" si riferisce à un gruppu di strumenti "Cosa-si" analisichì hè messu nantu à u nastru in a tab "Dati" in blocu "U travagliu cù i dati". Prima di Excel 2007, questu arnesi hà purtatu un nome. "Tavola di ricerca"chì ancu più accuratamente rifletteva a so essenza di u nome attuale.
A tabella di ricerca pò esse aduprata in parechji casi. Per esempiu, una opzione tipica hè quandu avete bisognu di calculà a quantità di un prestitu mensile cun differente variazione di u periodu di creditu è l'importo di u prestitu, o di u periodu di creditu è di tassi d'interessu. Questu strumentu pò esse utilizatu ancu in analisi di mudelli di prughjetti d'investimentu.
Ma anu ancu cunnosce chì l'usu eccessivu di questu strumentu pò cunduce à a frenata di u sistema, cum'è dati sò sempre ricalculati. Dunque, si propone di ùn aduprà micca questu strumentu in piccole matrici tabulari per risolve problemi simuli, ma per applicà a copia di formule aduprendu u marker di riempimentu.
Applicazione giustificata "Tabelle di dati" hè solu in larghe gamme tabulari, quandu a copia di formule pò piglià una grande quantità di tempu, è durante a procedura stessa, a probabilità di errori aumenta. Ma ancu in stu casu, si consiglia di disattivà a ricalcazione automatica di formule in u intervallu di a tavula di ricerca, per evità a carica inutile nantu à u sistema.
A principale differenza trà i vari usi di una tavola di dati hè u numaru di variabili implicatu in u calculu: una variabile o dui.
Metudu 1: aduprà u strumentu cù una variabile
Immediatamente consideremu l'opzione quandu un tabulariu di dati hè adupratu cù un valore di variabile. Pigliate l'esempiu più tipicu di pruntamentu.
Dunque, oghje avemu pruposti i seguenti condizioni di creditu:
- Periodu di prestitu - 3 anni (36 mesi);
- Ammucu di u prestitu - 900000 rubli;
- U tasso di interesse - 12,5% annu.
I pagamenti sò fatti à a fine di u periodu di pagamentu (mese) aduprendu u schema di renda, chì hè, in parti uguali. In listessu tempu, à l'iniziu di tuttu u periodu di prestitu, i pagamenti di interessi assimilani una parte significativa di i pagamenti, ma mentre u corpu si micca, i pagamenti di interessi diminuiscenu, è a quantità di rimborsu di u corpu aumenta. U versu cumunale, cum'è di sopra dettu, ùn hè micca cambiatu.
Hè necessariu calcolà ciò chì a quantità di u pagamento mensile sarà, chì include u rimborsu di u corpu di prestitu è pagamenti di interessi. Per ciò, Excel hà un operatore PMT.
PMT Appartene à un gruppu di funzioni finanziarie è a so missione hè di calcà u prestitu mensile di u tipu di annuità basatu nantu à a quantità di u corpu di prestitu, u terminu di u prestitu è u tandu. A sintassi per questa funzione hè cum'è seguitu.
= PMT (tasso; nper; ps; bs; tippu)
"Scommessa" - L'argumentu chì determina u tipu d'interessu di i pagamenti di creditu. L'indicatore hè stabilitu per u periodu. U nostru periodu di pagamentu hè di un mese. Dunque, u mumentu annu di u 12,5% deve esse spartitu in u numaru di mesi in un annu, cioè 12.
"Kper" - L'argumentu chì determina u numaru di periodi per l'interu periodu di u prestitu. In u nostru esempiu, u periodu hè un mese, è u periodu di u prestitu hè 3 anni o 36 mesi. Dunque, u numaru di periodi sarà pricessu 36.
"PS" - L'argumentu chì determina u valore attuale di u prestitu, chì hè a dimensione di u corpu di mutu à u mumentu di a so emissione. In u nostru casu, sta figura hè 900.000 rubli.
"BS" - Un argumentu chì indica a dimensione di u corpu di mutu à u mumentu di u so pagamentu cumpletu. Naturalmente, questu indicatore hè uguale à zero. Questu argumentu hè opzionale. Se site u saltu, si suppone chì hè uguale à u numeru "0".
"Tipu" - ancu argumentu opzionale. Informeghja quandu u paghju sarà fattu: à l'iniziu di u periodu (parametru - "1") o à a fine di u periodu (parametru - "0"). Cum'è noi si ricorda, u nostru pagamento hè fattu à a fine di u mese calendario, chì hè, u valore di stu argumentu hè uguale a "0". Ma, datu chì questu indicatore ùn hè micca obbligatorio, è per difettu, s'ellu ùn hè micca usatu, u valore hè supposu esse "0", allora in l'esempiu specificatu ùn pò esse adupratu à tutti.
- Cusì, procedemu à u calculu. Selezzi a cella nantu à u fogliu in cui u valore calculatu sarà visualizatu. Facemu clic nantu à u pulsante "Inserisci funzione".
- Iniziò Wizard di Funzione. Fate a transizione à a categoria "Finanziaria", sceglite da a lista u nome "PLT" è cliccate nantu à u pulsante "OK".
- Dopu, ci hè una attivazione di a finestra d'argomenti di a funzione di sopra.
Mette u cursore in u campu "Scommessa"Fate un clic nantu à a cella nantu à u fogliu cù u valore di u interessu annuale. Comu pudete vede, i so coordinate sò immediatamente visualizzati in u campu. Ma cum'è si ricorda, avemu bisognu di una tassa mensile, è cusì spartemu u risultatu per 12 (/12).
In u campu "Kper" cù u listessu modu, entremu e coordinate di e cellule di u terminu di creditu. In stu casu, ùn ci vole nunda da dividi.
In u campu "Ps" bisogna specificà e coordinate di a cella chì cuntene u valore di u corpu di creditu. U fattu. Pigliemu ancu un segnu di fronte à e coordinate visualizate. "-". U punto hè chì a funzione PMT per difettu, dà u risultatu finale cun un segnu negativu, basta cunsiderendu u pagamentu menstru di u prestitu cum'è una perdita. Ma per a chiarezza, avemu bisognu di u tavulu di dati à esse positivi. Allora avemu messu una marca "minus" nanzu à unu di l'argumenti di a funzione. Comu si sapi, multiplicazione "minus" on "minus" tardi dà plus.
In i campi "Bs" è "Tipu" Ùn intremu micca i dati. Facemu clic nantu à u pulsante "OK".
- Dopu, l'operatore calcula è mostra in a cella prefissata u risultatu di u pagamentu mensile totale - 30108,26 rubles. Ma u prublema hè chì u mutuatariu sia capace di pagà un massimu di 29.000 rubli à u mese, cioè, si duva a truvà una banca chì offre condizioni cù un tasso di interesse inferiore, o riduce u corpu di prestitu, o allargà u mumentu di u prestitu. Calculà e varie opzioni per l'azione ci aiuterà a tavula di ricerca.
- Per principiu, aduprà a tavula di ricerca cun una variabile. Videmu cumu u valore di u pagamentu mensile obligatu varieghja cù e variazioni di u tassi annuale, da a 9,5% annuale è finisce 12,5% pa cù passu 0,5%. Tutte e altre condizioni sò lasciate cambiate. Tratta un intervallu tavulu, chì i nomi di e colonne di u quali risponde à differenti variazioni di u tassi d'interessu. Cù sta linea "Pagamenti mensili" lasciate cum'è. A prima cellula deve cuntene a formula chì avemu calcolatu nanzu. Per più infurmazione, pudete aghjunghje righe "Totu quantità di prestitu" è "Totu di l'Interessu". A colonna in cui si trova u calculu hè fatta senza un intestu.
- Dopu, noi calculu l'ammunà tutale di u prestitu in e situazione attuale. Per fà quessa, selezziunate a prima cella di a riga. "Totu quantità di prestitu" e multiplica u cuntenutu di a cella "Pagamentu mensile" è "Terminu di prestitu". Dopu cliccate nant'à Enter.
- Per calculà a quantità totale di dumande in i cundizioni attuali, in un modu simu rimprunemu u valore di u corpu di prestitu da l'importo tuttu di u prestitu. Per fà vede u risultu nantu à u schermu cliccate nantu à u bottinu. Enter. Cusì, avemu a quantità chì avemu troppali cumu restituisce u prestitu.
- Oghji hè u tempu di applicà u strumentu. "Tabulu di dati". Selezziunate a matrice tutta di a tavula, fora di i nomi di righe. Dopu à ciò andate in tab "Dati". Cliccate nantu à u bottinu nantu à u nastro "Cosa-si" analisichì hè messu in un gruppu di strumentu "U travagliu cù i dati" (In Excel 2016, un gruppu di strumenti "Predictia"). Poi aprite un menu menu In questu si seleziona a posizione "Tabulu di dati ...".
- Una piccola finestra si apre, chì si chjama "Tabulu di dati". Comu pudete vede, hà duie campi. Siccome avemu travaglià cù una variabile, avemu bisognu di unu solu. Chì i nostri cambiamenti variabili si facenu in cuttuni, aduprà u campu "Sostitu i valori per e colonne in". Piazzamu u cursore quì, è cliccemu nantu à a cella in u set di dati iniziale, chì cuntene u valore attuale di percentuale. Dopu chì e coordinate di a cella venenu affissu in u campu, cliccate nantu à u bottinu "OK".
- L'utunimu calcula è riempe tutta a gamma di tavuli cù valori chì risponde à differenti opzioni di tassi di interesse. Se poni u cursore in qualunque elementu di stu spaziu di tavulu, pudete vede chì a barra di formula ùn mostra micca una formula di calculu di pagamenti regolari, ma una formula speciale di una matrice senza interruzzione. Hè cusì, ùn hè più pussibule di cambià i valori in cella individuale. Cancellà i risultati di calculu pò esse solu tutti insieme, è micca separatamente.
Inoltre, si pò rilevà chì u valore di u pagamento mensile à 12,5% annu, ottenutu applicendu a tavula di ricerca, currisponde à u valore à u listessu tassu di interessu ch'è noi hà ricevutu applicendu a funzione PMT. Questu una volta di più prova a giustizia di u calculu.
Dopu avè analizzatu stu array di tabulazioni, ci vole à dì chì, cum'è si vede, solu à un ritruu di 9,5% annu, u livulu accettatu di pagamenti mensili (menu di 29.000 rubli) hè ottenutu.
Lezione: Calculu di u pagamentu di renda in Excel
Metudu 2: aduprà un strumentu cù dui variabili
Benintesa, hè assai difficile, se hè realistu, truvà banche chì emettenu prestiti à 9,5% annu. Allora, videmu chì opzioni ci sò per investe in un livello accettabile di pagamento mensile per vari cumbinazioni di altre variabili: a dimensione di u corpu di prestitu è u periodu di prestitu. In listessu tempu, u tassessu di l'interessu resterà in cambellu (12,5%). L'utunile ci aiuta in stu scopu. "Tabulu di dati" cù duie variabili.
- Draw a new array array. Ora u terminu di crediti sarà indicatu in i nomi di colonne (da 2 finu à 6 anni in mesi in passi di un annu), è in le righe - a dimensione di u corpu di prestitu (da u 850000 finu à 950000 rubli in incrementi 10000 rubli). In stu casu, hè imperativu chì a cella in cui hè situata a formula di calculu (in u nostru casu PMT), situatu à u cunfernu di i nomi di righe è di colonne. Senza questa situazione, u strumentu ùn funziona micca cù duie variabili.
- Allora selezziunate tutta a gamma di tavule risultante, includendu i nomi di e colonne, righe è a cella cù a formula PMT. Vai à la tabulazione "Dati". Com'è u tempu precedente, cliccate nantu à u bottinu. "Cosa-si" analisiin un gruppu di strumentu "U travagliu cù i dati". In a lista chì si apre, selezziunate l'elemento "Tabulu di dati ...".
- A finestra d'utile cumincia. "Tabulu di dati". In stu casu, avemu bisognu di dui campi. In u campu "Sostitu i valori per e colonne in" specificamu e coordinate di a cella chì contenenu u termine di u prestitu in i dati primarii. In u campu "Sostituisce i valori da righe in" specificate l'indirizzu di a cella di i parametri iniziali chì cuntenenu u valore di u corpu di u prestitu Quandu tutti i dati sò entrati. Facemu clic nantu à u pulsante "OK".
- U prugramma esegue u calculu è riempe a gamma di tavule cù i dati. À a intersezione di righe è colonne, hè ora possibile osservà cume si verrà esattamente u pagamentu mensile, cù una quantità corrispondente di interessi annuale è un periodu di creditu specificatu.
- Comu pudete vede, abbastanza valori. Per risolve altri prublemi, ci pò esse ancu di più. Dunque, per rendà più prestuvule l'uscita di i risultati è determinà immediatamente quali valori ùn soddisfanu micca a condizione data, pudete aduprà strumenti di visualizazione. In u nostru casu sarà formattazione condizionata. Selezziunate tutti i valori di a gamma di tabculi, esclusu i testate di righe è colonne.
- Move to tab "Casa" è cliccate nantu à l'icona "Formattazione condizionata". Si trova in a casella di attrezzi. "Stili" nant'à u tape. In u menu chì si apre, selezziunate l'elemento "Regole per a selezzione di cella". In a lista supplementare cliccate nantu à a posizione "Menu ...".
- Dopu, apre a finestra di impostazione di furmazione cuniziale. In u campu di sinistra, specificu u valore, menu di chì le cellule saranu selezionate. Cum'è si ricorda, simu soddisfatti di a situazione in u quali u pagamentu mensile di u prestitu ùn hè micca 29000 rubles. Inserite stu numeru. In u campu di diritta hè pussibule di sceglie u culore di a selezzione, ancu se pudete lasciate per difettu. Dopu chì tutte e impostazioni richieste sò state inserite, cliccate nantu à u bottinu. "OK".
- Dopu à quessa, tutte e cellule chì ante rispessu i valori à a situazione sopra riportata saranu evidenziate in culore.
Dopu avè analizzatu l'array di tavulu, pudete tracciate qualchì conclusione. Cumu si pudete vede, cù u periodu di prestitu attuale (36 mesi), per investì in stima di quì sopra un soldu mensile, avemu bisognu di piglià un prestitu chì ùn supera micca 8,600,000.00 rubli, cioè 40.000 menu di quellu originariu previsto.
Sì avemu l'intenzione di piglià un prestitu per quantità di 900.000 rubli, allora u termine di u prestitu deve esse 4 anni (48 mesi). Solu in stu casu, a quantità di u pagamento mensile ùn supera à u limite stabilitu di 29.000 rubli.
Così, avvalendu di questu array tabulatu è analizzendu i pro e l'ordine di ogni opzione, u mutuatariu pò fà una decisione specifica nantu à i termini di prestiti, sceglie l'opzione chì più si adatta à i so bisogni.
Benintesa, a tavula di ricerca pò esse aduprata micca solu per calculà e opzioni di creditu, ma dinò per risolve parechji prublemi.
Lezione: formattazione condizionata in Excel
In generali, si deve notà chì a tavula di ricerca hè un strumentu assai utile è relativamente simplici per determinà u risultatu di vari combinazioni di variabili. Aduprendu una furmazione cumpressa nta è, in più, pudete visualizà l'infurmazioni ricevute.
