Spiss, għandek bżonn tikkalkula r-riżultat finali għal diversi kombinazzjonijiet ta 'dejta ta' input. B'hekk, l-utent ikun jista 'jevalwa l-għażliet kollha possibbli għal azzjonijiet, jagħżel dawk li r-riżultati ta' interazzjoni tiegħu jissodisfawh, u, fl-aħħar, jagħżel l-iktar għażla ottimali. Fl-Excel, biex twettaq dan ix-xogħol, hemm għodda speċjali - "Tabella tad-dejta" (Tabella ta 'sostituzzjoni) Ejja nsib kif tużaha biex tlesti x-xenarji ta 'hawn fuq.
Aqra wkoll: Għażla ta 'parametri fl-Excel
Uża tabella tad-dejta
Strument "Tabella tad-dejta" Hija maħsuba biex tikkalkula r-riżultat għal varjazzjonijiet varji ta 'waħda jew żewġ varjabbli definiti. Wara l-kalkolu, l-għażliet possibbli kollha jidhru fil-forma ta 'tabella, li tissejjaħ il-matriċi ta' analiżi tal-fattur. "Tabella tad-dejta" tirreferi għal grupp ta 'għodda "X'jiġri jekk analiżi", li jitqiegħed fuq iż-żigarella fit-tab "Dejta" fi blokk "Taħdem bid-dejta". Qabel l-Excel 2007, din l-għodda kienet imsejħa Tabella ta 'sostituzzjoni, li rriflettiet saħansitra iktar preċiż l-essenza tagħha mill-isem attwali.
It-tabella ta ’tiftix tista’ tintuża f’ħafna każijiet. Pereżempju, għażla tipika hija meta jkollok bżonn tikkalkula l-ammont tal-ħlas ta 'self kull xahar għal varjazzjonijiet varji tal-perjodu ta' kreditu u l-ammont tas-self, jew il-perjodu ta 'kreditu u r-rata ta' imgħax. Ukoll, din l-għodda tista 'tintuża fl-analiżi ta' mudelli ta 'proġetti ta' investiment.
Imma inti għandek tkun taf ukoll li l-użu eċċessiv ta ’din l-għodda jista’ jwassal għal ibbrejkjar tas-sistema, billi d-dejta tingħadda kontinwament. Għaldaqstant, huwa rrakkomandat f'arrays żgħar ta 'tabella biex issolvi problemi simili mhux biex tuża din l-għodda, iżda biex tuża l-ikkupjar tal-formula billi tuża l-immarkar tal-mili.
Applikazzjoni ġġustifikata "Tabelli tad-dejta" huwa biss fil-meded kbar tat-tabella, meta l-ikkupjar tal-formuli jista 'jieħu ħafna ħin, u matul il-proċedura nnifisha l-probabbiltà li tagħmel żbalji tiżdied. Iżda f'dan il-każ, huwa rrakkomandat li tiġi ddiżattivata l-kalkolu mill-ġdid awtomatiku tal-formuli fil-medda tat-tabella ta 'sostituzzjoni sabiex tiġi evitata tagħbija bla bżonn fuq is-sistema.
Id-differenza ewlenija bejn l-użi differenti tat-tabella tad-dejta hija n-numru ta 'varjabbli involuti fil-kalkolu: varjabbli waħda jew tnejn.
Metodu 1: uża l-għodda b'varjabbli waħda
Immedjatament ejja nħarsu lejn l-għażla meta t-tabella tad-dejta tintuża b'valur varjabbli wieħed. Ħu l-iktar eżempju tipiku ta ’self.
Allura, bħalissa qed jiġu offruti l-kundizzjonijiet ta 'self li ġejjin:
- Terminu tas-self - 3 snin (36 xahar);
- Ammont tas-self - 900,000 rublu;
- Rata ta 'imgħax - 12.5% fis-sena.
Il-pagamenti jseħħu fi tmiem il-perjodu ta ’pagament (xahar) skont l-iskema ta’ renta, jiġifieri, f’ishma ugwali. Fl-istess ħin, fil-bidu tat-terminu tas-self kollu, parti sinifikanti tal-ħlasijiet huma l-pagamenti tal-imgħax, iżda hekk kif il-korp jonqos, il-pagamenti tal-imgħax jonqsu, u l-ammont tal-ħlas lura tal-korp innifsu jiżdied. Il-ħlas totali, kif imsemmi hawn fuq, jibqa 'l-istess.
Huwa neċessarju li tikkalkula x’inhu l-ammont tal-pagament ta ’kull xahar, inkluż il-ħlas lura tal-korp tas-self u l-ħlasijiet ta’ imgħax. Għal dan, Excel għandu operatur PMT.
PMT tappartjeni għall-grupp ta 'funzjonijiet finanzjarji u l-kompitu tiegħu huwa li jikkalkula l-ħlas ta' self ta 'tip ta' self ta 'kull sena bbażat fuq l-ammont tal-korp tas-self, it-terminu tas-self u r-rata tal-imgħax. Is-sintassi ta 'din il-funzjoni hija ppreżentata bħala
= PLT (rata; nper; ps; bs; tip)
Bid - argument li jiddetermina r-rata tal-imgħax ta ’ħlasijiet ta’ kreditu. L-indikatur huwa ssettjat għall-perjodu. Il-perjodu ta 'ħlas tagħna huwa daqs xahar. Għalhekk, ir-rata annwali ta '12.5% għandha tkun diviża bl-għadd ta' xhur f'sena, jiġifieri, 12.
"Nper" - argument li jiddetermina n-numru ta 'perjodi għat-tul tas-self kollu. Fl-eżempju tagħna, il-perjodu huwa ta 'xahar, u t-terminu tas-self huwa ta' 3 snin jew 36 xahar. Għalhekk, l-għadd ta 'perjodi se jkun kmieni 36.
"PS" - argument li jiddetermina l-valur preżenti tas-self, jiġifieri, huwa d-daqs tal-korp tas-self fil-ħin tal-ħruġ tiegħu. Fil-każ tagħna, din iċ-ċifra hija ta '900,000 rublu.
"BS" - argument li jindika d-daqs tal-korp tas-self waqt il-ħlas sħiħ. Naturalment, dan l-indikatur ikun daqs żero. Dan l-argument huwa fakultattiv. Jekk taqbeż, huwa preżunt li huwa daqs in-numru "0".
"Tip" - wkoll argument fakultattiv. Huwa jħabbar meta se jsir eżattament il-ħlas: fil-bidu tal-perjodu (parametru - "1") jew fi tmiem il-perjodu (parametru - "0") Kif niftakru, il-ħlas tagħna jsir fl-aħħar tax-xahar kalendarju, jiġifieri, il-valur ta 'dan l-argument ikun daqs "0". Iżda, minħabba l-fatt li dan l-indikatur mhuwiex obbligatorju, u b'kontumaċja, jekk ma jintużax, il-valur huwa implikat li hu ugwali "0", allura fl-eżempju indikat jista 'jitħalla barra.
- Allura, aħna nipproċedu għall-kalkolu. Agħżel ċellola fuq l-iskeda fejn se jintwera l-valur ikkalkulat. Ikklikkja fuq il-buttuna "Funzjoni Daħħal".
- Tibda Wizard tal-Karatteristiċi. Aħna ngħaddu għall-kategorija "Finanzjarju", agħżel l-isem mil-lista "PLT" u kklikkja fuq il-buttuna "OK".
- Wara dan, it-tieqa tal-argumenti tal-funzjoni ta 'hawn fuq hija attivata.
Poġġi l-cursor fil-qasam Bid, wara li aħna ikklikkja fuq iċ-ċellula fuq il-folja bil-valur tar-rata ta 'interess annwali. Kif tistgħu taraw, il-koordinati tagħha huma immedjatament murija fil-qasam. Imma, kif niftakru, għandna bżonn rata ta 'kull xahar, u għalhekk naqsmu r-riżultat fi 12 (/12).
Fil-grawnd "Nper" bl-istess mod aħna nidħlu fil-koordinati taċ-ċelloli tat-terminu tas-self. F'dan il-każ, m'għandekx bżonn taqsam xejn.
Fil-grawnd Ps trid tispeċifika l-koordinati taċ-ċellula li fihom il-valur tal-korp tas-self. Aħna nagħmluh. Qiegħed ukoll sinjal quddiem il-koordinati murija "-". Il-fatt hu li l-funzjoni PMT b'mod awtomatiku, jagħti r-riżultat finali b'sinjal negattiv, bir-raġun meta jikkunsidra t-telf ta 'ħlas ta' self ta 'kull xahar. Iżda għaċ-ċarezza tal-applikazzjoni tat-tabella tad-dejta, għandna bżonn li dan in-numru ikun pożittiv. Għalhekk, aħna npoġġu sinjal nieqes qabel wieħed mill-argumenti tal-funzjoni. Multiplikazzjoni hija magħrufa nieqes fuq nieqes fl-aħħar tagħti flimkien ma '.
Ġo l-għelieqi "Bs" u "Tip" id-dejta ma tiddaħħal xejn. Ikklikkja fuq il-buttuna "OK".
- Wara dan, l-operatur jikkalkula u juri r-riżultat tal-pagament totali fix-xahar f'ċellula magħżula minn qabel - 30108,26 rublu. Iżda l-problema hija li min jissellef huwa kapaċi jħallas massimu ta '29,000 rublu fix-xahar, jiġifieri, huwa għandu jsib bank li joffri kundizzjonijiet b'rata ta' imgħax baxxa, jew inaqqas il-korp tas-self, jew iżid it-terminu tas-self. It-tabella ta ’tfittxija tgħinna insemmu d-diversi għażliet.
- L-ewwel, uża l-iskeda ta ’tiftix b’varjabbli waħda. Ejja naraw kif l-ammont tal-pagament obbligatorju fix-xahar se jinbidel b'diversi varjazzjonijiet tar-rata annwali, billi tibda minn 9,5% fis-sena u fit-tmiem 12,5% fis-sena f’żidiet 0,5%. Il-kundizzjonijiet l-oħra kollha qed jibqgħu l-istess. Tfassal firxa ta 'tabella, li l-ismijiet tal-kolonni tagħhom jikkorrispondu għal varjazzjonijiet varji tar-rata tal-imgħax. B'din il-linja "Ħlasijiet ta 'Kull Xahar" ħalli kif inhu. L-ewwel ċellola tagħha għandu jkun fiha l-formula li aħna kkalkulajna qabel. Għal aktar informazzjoni, tista 'żżid linji "Ammont totali tas-self" u "Interess Totali". Il-kolonna li fiha jinsab il-kalkolu ssir mingħajr intestatura.
- Sussegwentement, aħna nikkalkulaw l-ammont totali tas-self taħt kondizzjonijiet kurrenti. Biex tagħmel dan, agħżel l-ewwel ċellula tar-ringiela "Ammont totali tas-self" u immoltiplika l-kontenut taċ-ċelloli "Ħlas kull xahar" u "Terminu tas-self". Wara dan, ikklikkja fuq il-buttuna Daħħal.
- Biex tikkalkula l-ammont totali ta 'interess taħt kondizzjonijiet kurrenti, aħna wkoll naqqas l-ammont tal-korp tas-self mill-ammont totali tas-self. Biex turi r-riżultat fuq l-iskrin, ikklikkja fuq il-buttuna Daħħal. Għalhekk, irridu nġibu l-ammont li nħallsu żejjed meta nħallas lura s-self.
- Issa wasal iż-żmien li tapplika l-għodda "Tabella tad-dejta". Aħna nagħżlu l-firxa sħiħa tat-tabella, ħlief għall-ismijiet tar-ringiela. Wara dan, mur fit-tab "Dejta". Ikklikkja l-buttuna fuq iż-żigarella "X'jiġri jekk analiżi"li tinsab fil-grupp tal-għodda "Taħdem bid-dejta" (fl-Excel 2016, grupp ta 'għodda "Previżjoni") Imbagħad jiftaħ menu żgħir. Fiha aħna nagħżlu pożizzjoni "Tabella tad-dejta ...".
- Tiftaħ tieqa żgħira, li tissejjaħ "Tabella tad-dejta". Kif tistgħu taraw, għandha żewġ oqsma. Peress li naħdmu ma 'varjabbli wieħed, għandna bżonn wieħed minnhom biss. Peress li nibdlu l-varjabbli kolonna b'kolonna, aħna se nużaw il-qasam Valuri tal-Kolonna Sostitut. Issettja l-cursor hemm, u mbagħad ikklikkja fuq iċ-ċellula fid-dataset oriġinali li fiha l-persentaġġ kurrenti. Wara li l-koordinati taċ-ċelloli jiġu murija fil-qasam, ikklikkja fuq il-buttuna "OK".
- L-għodda tikkalkula u timla l-firxa tabulari kollha b'valuri li jikkorrispondu għal għażliet differenti għar-rata tal-imgħax. Jekk tpoġġi l-cursor fi kwalunkwe element ta 'din it-tabella, tista' tara li l-bar tal-formola ma jurix il-formula tas-soltu għall-kalkolu tal-ħlas, iżda formula speċjali għal firxa li ma tistax titkejjel. Jiġifieri, issa huwa impossibbli li jinbidlu l-valuri f'ċelloli individwali. Tista 'tħassar ir-riżultati tal-kalkolu biss kollha flimkien, u mhux separatament.
Barra minn hekk, tista 'tara li l-ħlas ta' kull xahar għal 12.5% fis-sena miksub bħala riżultat tal-applikazzjoni tat-tabella ta 'riċerka jikkorrispondi għall-valur għall-istess ammont ta' interess li rċivejna billi applikajna l-funzjoni PMT. Dan għal darb'oħra jipprova l-korrettezza tal-kalkolu.
Wara l-analiżi ta 'din il-firxa tal-mejda, għandu jingħad li, kif tista' tara, biss b'rata ta '9.5% fis-sena nikbru livell ta' ħlas aċċettabbli fix-xahar (inqas minn 29,000 rublu).
Lezzjoni: Kalkolu tal-ħlas ta ’annwalità fl-Excel
Metodu 2: uża l-għodda b'żewġ varjabbli
M’għandniex xi ngħidu, li ssib fil-banek preżenti li joħorġu self għal 9.5% fis-sena hija diffiċli ħafna, jekk mhux impossibbli. Għalhekk, se naraw liema għażliet jeżistu biex tinvesti f'livell aċċettabbli ta 'ħlas ta' kull xahar għal diversi kombinazzjonijiet ta 'varjabbli oħra: id-daqs tal-korp tas-self u t-terminu tas-self. F'dan il-każ, ir-rata tal-imgħax tibqa 'l-istess (12.5%). Fis-soluzzjoni ta 'din il-problema, għodda tgħinna. "Tabella tad-dejta" billi tuża żewġ varjabbli.
- Nislet tabella ta 'firxa ġdida. Issa fl-ismijiet tal-kolonna se jkun indikat it-terminu tas-self (minn 2 qabel 6 snin fix-xhur b'żidiet ta 'sena), u f'linji - id-daqs tal-korp tas-self (minn 850000 qabel 950000 rubli b'żidiet 10000 rublu). F'dan il-każ, prerekwiżit huwa dak iċ-ċellula li fiha tinsab il-formula ta 'kalkolu (fil-każ tagħna PMT), li jinsabu fuq il-fruntiera tal-ismijiet tar-ringiela u tal-kolonna. Mingħajr din il-kundizzjoni, l-għodda ma taħdimx meta tuża żewġ varjabbli.
- Imbagħad agħżel il-firxa sħiħa ta 'tabella li tirriżulta, inklużi l-ismijiet ta' kolonni, ringieli u ċellola bil-formula PMT. Mur fit-tab "Dejta". Bħal fil-ħin ta 'qabel, ikklikkja fuq il-buttuna "X'jiġri jekk analiżi", fil-grupp tal-għodda "Taħdem bid-dejta". Fil-lista li tiftaħ, agħżel "Tabella tad-dejta ...".
- It-tieqa tal-għodda tibda "Tabella tad-dejta". F'dan il-każ, neħtieġu ż-żewġ oqsma. Fil-grawnd Valuri tal-Kolonna Sostitut indika l-koordinati taċ-ċellula li fiha t-terminu tas-self fid-dejta primarja. Fil-grawnd "Valuri sostituti filliera b'ringiela fi" indika l-indirizz taċ-ċellula tal-parametri inizjali li jkun fih il-valur tal-korp tas-self. Wara d-dejta kollha tiddaħħal. Ikklikkja fuq il-buttuna "OK".
- Il-programm iwettaq il-kalkolu u jimla l-firxa tal-iskeda bid-dejta. Fl-intersezzjoni ta 'ringieli u kolonni issa huwa possibbli li tosserva x'inhu eżattament il-pagament ta' kull xahar, bl-ammont korrispondenti ta 'interess annwali u t-terminu tas-self indikat.
- Kif tistgħu taraw, hemm ħafna valuri. Biex issolvi problemi oħra, jista 'jkun hemm saħansitra aktar. Għalhekk, sabiex tagħmel ir-riżultati tar-riżultati aktar viżwali u tiddetermina immedjatament liema valuri ma jissodisfawx il-kondizzjoni mogħtija, tista 'tuża għodod ta' viżwalizzazzjoni. Fil-każ tagħna, dan se jkun ifformattjar kondizzjonali. Aħna nagħżlu l-valuri kollha tal-firxa tat-tabella, esklużi l-intestaturi tar-ringiela u tal-kolonna.
- Mexxi fit-tab "Id-dar" u kklikkja fuq l-ikona Ifformattjar kondizzjonali. Jinsab fil-blokka tal-għodda. Stili fuq it-tejp. Fil-menu li jiftaħ, agħżel Regoli tal-Għażla taċ-Ċelluli. Fil-lista addizzjonali, ikklikkja fuq il-pożizzjoni "Anqas ...".
- Wara dan, tinfetaħ it-tieqa tas-settings tal-ifformattjar kondizzjonali. Fil-qasam tax-xellug indika l-valur inqas minn liema jiġu magħżula ċ-ċelloli. Kif niftakru, aħna sodisfatti bil-kondizzjoni li l-ħlas ta 'self ta' kull xahar ikun inqas minn 29000 rublu. Niżżel dan in-numru. Fil-qasam it-tajjeb, tista 'tagħżel il-kulur jenfasizza, għalkemm tista' tħalliha b'default. Wara li l-issettjar meħtieġ kollu jiddaħħal, ikklikkja fuq il-buttuna "OK".
- Wara dan, iċ-ċelloli kollha li l-valuri tagħhom jikkorrispondu għall-kundizzjoni t'hawn fuq se jiġu enfasizzati.
Wara li analizzajna l-firxa tal-mejda, nistgħu nislet xi konklużjonijiet. Kif tistgħu taraw, bit-terminu tas-self eżistenti (36 xahar), sabiex tinvesti fl-ammont indikat tal-ħlas ta 'kull xahar, għandna bżonn nieħdu self li ma jaqbiżx 860000.00 rublu, jiġifieri 40,000 inqas minn dak ippjanat oriġinarjament.
Jekk aħna xorta nieħdu ħsieb li nieħdu self ta '900,000 rublu, allura t-terminu tas-self għandu jkun ta' 4 snin (48 xahar). F'dan il-każ biss, il-ħlas ta 'kull xahar ma jaqbiżx il-limitu stabbilit ta' 29,000 rublu.
Għalhekk, billi tuża din il-firxa ta 'tabella u tanalizza l-vantaġġi u l-iżvantaġġi ta' kull għażla, min jissellef jista 'jieħu deċiżjoni speċifika dwar it-termini tas-self, billi jagħżel l-iktar għażla adattata minn kull possibbli.
Dażgur, it-tabella ta ’tiftix tista’ tintuża mhux biss għall-kalkolu ta ’għażliet ta’ kreditu, iżda wkoll biex issolvi ħafna problemi oħra.
Lezzjoni: Ifformattjar kondizzjonali fl-Excel
B'mod ġenerali, għandu jkun innutat li t-tabella ta 'riċerka hija għodda utli ħafna u relattivament sempliċi biex tiddetermina r-riżultat għal diversi kombinazzjonijiet ta' varjabbli. Meta tuża l-ifformattjar kondizzjonali fl-istess ħin, barra minn hekk, tista 'tara l-informazzjoni riċevuta.
