Heel dikwels is dit nodig om die finale uitslag vir verskillende kombinasies van insetdata te bereken. So kan die gebruiker alle moontlike opsies vir aksie evalueer, kies diegene wie se interaksie resultaat hom bevredig, en uiteindelik kies die mees optimale opsie. In Excel is daar 'n spesiale hulpmiddel vir hierdie taak - "Data Table" ("Opsoektafel"). Kom ons uitvind hoe om dit te gebruik om bogenoemde scenario's uit te voer.
Sien ook: Parameter seleksie in Excel
Gebruik data tabel
hulpmiddel "Data Table" Dit is ontwerp om die resultaat te bereken met verskillende variasies van een of twee gedefinieerde veranderlikes. Na berekening sal alle moontlike opsies in die vorm van 'n tabel verskyn, wat die matriks van faktoranalise genoem word. "Data Table" verwys na 'n groep gereedskap Wat-as-analisewat op die lint in die oortjie geplaas word "Data" in blok "Werk met data". Voor Excel 2007 het hierdie instrument 'n naam gedra. "Opsoektafel"wat selfs meer akkuraat sy essensie weerspieël as die huidige naam.
Die soektogtafel kan in baie gevalle gebruik word. Byvoorbeeld, 'n tipiese opsie is wanneer jy die bedrag van 'n maandelikse leningbetaling met verskillende variasies van die kredietydperk en die leningsbedrag, of die kredietydperk en rentekoers moet bereken. Hierdie instrument kan ook gebruik word wanneer beleggingsprojekmodelle ontleed word.
Maar jy moet ook bewus wees daarvan dat oormatige gebruik van hierdie instrument tot stelselremming kan lei, aangesien data voortdurend herbereken word. Daarom word aanbeveel om nie hierdie instrument in klein tabelraamwerke te gebruik om soortgelyke probleme op te los nie, maar om die formatering van die formules toe te pas met die vulpen.
Geregverdigde aansoek "Data Tables" is slegs in groot tabelreekse, wanneer die formules vir die kopiëring baie tyd kan neem, en tydens die prosedure self is die waarskynlikheid van foute verhoog. Maar selfs in hierdie geval word aanbeveel om outomatiese herberekening van formules in die reeks van die opblaftabel uit te skakel, om onnodige las op die stelsel te voorkom.
Die hoofverskil tussen die verskillende gebruike van 'n datatabel is die aantal veranderlikes wat by die berekening betrokke is: een veranderlike of twee.
Metode 1: Gebruik die instrument met een veranderlike
Onmiddellik, laat ons die opsie oorweeg wanneer 'n data tabel met een veranderlike waarde gebruik word. Neem die mees tipiese voorbeeld van uitleen.
So, tans word ons die volgende kredietvoorwaardes aangebied:
- Leningsperiode - 3 jaar (36 maande);
- Leningsbedrag - 900000 roebels;
- Rentekoers - 12,5% per jaar.
Betalings word gemaak aan die einde van die betalingstydperk (maand) met behulp van die annuïteitsskema, dit wil sê in gelyke aandele. Terselfdertyd, aan die begin van die hele leningsperiode, maak rentebetalings 'n beduidende deel van die betalings uit, maar soos die liggaam krimp, word rentebetalings verminder en die bedrag van die terugbetaling van die liggaam self verhoog. Die totale betaling, soos hierbo genoem, bly onveranderd.
Dit is nodig om te bereken wat die bedrag van die maandelikse betaling sal wees, wat die terugbetaling van die leningsliggaam en rentebetalings insluit. Hiervoor het Excel 'n operateur PMT.
PMT Dit behoort aan 'n groep finansiële funksies en sy taak is om die maandelikse leningbetaling van die annuïteitstipe te bereken gebaseer op die bedrag van die leningsliggaam, die leningstermyn en die rentekoers. Die sintaksis vir hierdie funksie is soos volg.
= PMT (koers; nper; ps; bs; tipe)
"Bet" - Die argument wat die rentekoers van kredietbetalings bepaal. Die aanwyser is vir die tydperk gestel. Ons uitbetalingstydperk is een maand. Daarom moet die jaarlikse koers van 12,5% verdeel word in die aantal maande in 'n jaar, dit is 12.
"NPER" - Die argument wat die aantal periodes vir die hele tydperk van die lening bepaal. In ons voorbeeld is die tydperk een maand en die leningstydperk is 3 jaar of 36 maande. Dus sal die aantal tydperke vroeg 36 wees.
"PS" - Die argument wat die huidige waarde van die lening bepaal, dit wil sê, is die grootte van die leningsliggaam ten tye van sy uitreiking. In ons geval is hierdie syfer 900.000 roebels.
"BS" - 'n argument wat die grootte van die leningsliggaam aandui ten tyde van die volle betaling daarvan. Natuurlik sal hierdie aanwyser gelyk wees aan nul. Hierdie argument is opsioneel. As jy dit slaan, word aanvaar dat dit gelyk is aan die getal "0".
"Tipe" - ook opsionele argument. Hy lig oor wanneer die betaling gemaak sal word: aan die begin van die tydperk (parameter - "1") of aan die einde van die tydperk (parameter - "0"). Soos ons onthou, word ons betaling aan die einde van die kalendermaand gemaak, dit wil sê die waarde van hierdie argument sal gelyk wees aan "0". Maar aangesien hierdie aanwyser nie verpligtend is nie, en as dit nie gebruik word nie, word die waarde aanvaar "0", dan kan dit in die gespesifiseerde voorbeeld glad nie gebruik word nie.
- Dus, ons gaan voort met die berekening. Kies die sel op die vel waar die berekende waarde vertoon sal word. Ons kliek op die knoppie "Voeg funksie in".
- begin Funksie Wizard. Maak die oorgang na die kategorie "Finansiële", kies uit die lys die naam "PMT" en klik op die knoppie "OK".
- Hierna is daar 'n aktivering van die argument venster van die bogenoemde funksie.
Plaas die wyser in die veld "Bet"Klik dan op die sel op die blad met die waarde van die jaarlikse rentekoers. Soos u kan sien, word die koördinate onmiddellik in die veld vertoon. Maar, soos ons onthou, benodig ons 'n maandelikse koers, en daarom deel ons die uitslag met 12 (/12).
In die veld "NPER" op dieselfde manier betree ons die koördinate van die kredietterme selle. In hierdie geval moet niks verdeel word nie.
In die veld "Ps" U moet die koördinate van die sel spesifiseer wat die waarde van die kredietkrediet bevat. Ons doen dit. Ons sit ook 'n teken voor die vertoonde koördinate. "-". Die punt is dat die funksie PMT By verstek gee dit die finale uitslag met 'n negatiewe teken, met 'n billike oorweging van die maandelikse lening. Maar vir die duidelikheid het ons die data tabel nodig om positief te wees. Daarom stel ons 'n punt "Min" voor een van die funksie argumente. Soos bekend, vermenigvuldiging "Min" op "Min" gee uiteindelik "Plus".
In die velde "BS" en "Tipe" Ons gee glad nie data op nie. Ons kliek op die knoppie "OK".
- Daarna word die operateur bereken en vertoon in die voorafbepaalde sel die resultaat van die totale maandelikse betaling - 30108,26 roebels. Maar die probleem is dat die lener 'n maksimum van 29 000 roebels per maand kan betaal, dit wil sê, hy moet óf 'n bankaanbod met 'n laer rentekoers vind, óf die lening van die lening verminder of die leningstermyn verleng. Bereken die verskillende opsies vir aksie sal ons help met die opzoekingstabel.
- Om te begin, gebruik die soektogtafel met een veranderlike. Kom ons kyk hoe die waarde van die verpligte maandelikse betaling wissel met verskillende variasies in die jaarkoers, wat wissel van 9,5% jaarliks en eindigend 12,5% pj met stap 0,5%. Alle ander voorwaardes word onveranderd gelaat. Teken 'n tabelreeks, waarvan die name van die kolomme ooreenstem met verskillende variasies van die rentekoers. Met hierdie lyn "Maandelikse betalings" verlaat soos dit is. Sy eerste sel moet die formule wat ons vroeër bereken het, bevat. Vir meer inligting, kan u lyne byvoeg "Totale leningsbedrag" en "Totale rente". Die kolom waarin die berekening geleë is, is sonder 'n koptekst.
- Vervolgens bereken ons die totale bedrag van die lening onder die huidige voorwaardes. Om dit te doen, kies die eerste sel van die ry. "Totale leningsbedrag" en vermenigvuldig die selinhoud "Maandelikse betaling" en "Leningtermyn". Na hierdie kliek op Tik.
- Om die totale bedrag van rente onder die huidige omstandighede te bereken, aftrek ons die waarde van die leningsliggaam met die totale bedrag van die lening. Om die resultaat op die skerm te vertoon, klik op die knoppie. Tik. So kry ons die bedrag wat ons oorbetaal wanneer u die lening terugbesorg.
- Nou is dit tyd om die gereedskap toe te pas. "Data Table". Kies die hele tabel skikking, behalwe vir die ry name. Daarna gaan jy na die oortjie "Data". Klik op die knoppie op die lint Wat-as-analisewat in 'n groep gereedskap geplaas word "Werk met data" (in Excel 2016, 'n groep gereedskap "Voorspelling"). Dan kom 'n klein spyskaart oop. Daarin kies ons die posisie "Data tabel ...".
- 'N Klein venster word oopgemaak, wat genoem word "Data Table". Soos u kan sien, het dit twee velde. Aangesien ons met een veranderlike werk, benodig ons net een van hulle. Aangesien ons veranderlike veranderinge in kolomme voorkom, sal ons die veld gebruik "Vervangingswaardes deur kolomme in". Ons plaas die wyser daar en klik dan op die sel in die aanvanklike datastel, wat die huidige waarde van persent bevat. Nadat die koördinate van die sel in die veld vertoon word, klik op die knoppie "OK".
- Die instrument bereken en vul die hele tabelreeks met waardes wat ooreenstem met verskillende rentekoersopsies. As jy die wyser in enige element van hierdie tabelruimte plaas, kan jy sien dat die formulebalk nie 'n gereelde betaalberekeningformule vertoon nie, maar 'n spesiale formule van 'n nie-brekende skikking. Dit is dus nie meer moontlik om die waardes in individuele selle te verander nie. Verwyder die berekening resultate kan net almal saam wees, en nie afsonderlik nie.
Daarbenewens kan daarop gelet word dat die waarde van die maandelikse betaling teen 12,5% per jaar, verkry deur die opsoektabel te gebruik, ooreenstem met die waarde teen dieselfde rentekoers wat ons ontvang het deur die funksie toe te pas PMT. Dit bewys weereens die korrektheid van die berekening.
Na die ontleding van hierdie tabelraamwerk, moet daar gesê word dat die aanvaarbare maandelikse betaalvlak (minder as 29 000 roebels), soos ons sien, slegs teen 'n koers van 9.5% per jaar behaal.
Les: Berekening van die annuïteitsbetaling in Excel
Metode 2: Gebruik 'n instrument met twee veranderlikes
Natuurlik is dit baie moeilik, indien enigsins realisties, om banke te vind wat lenings op 9,5% per jaar uitreik. Laat ons dus sien watter opsies daar belê moet word in 'n aanvaarbare vlak van maandelikse betaling vir verskeie kombinasies van ander veranderlikes: die grootte van die leningsliggaam en die leningsperiode. Terselfdertyd sal die rentekoers onveranderd bly (12.5%). Die instrument sal ons help met hierdie taak. "Data Table" Gebruik twee veranderlikes.
- Teken 'n nuwe tabel skikking. Nou word die krediettermyn in die kolom name aangedui (van 2 tot en met 6 jaar in maande in stappe van een jaar), en in die rye - die grootte van die leningsliggaam (vanaf 850000 tot en met 950000 roebels in inkremente 10000 roebels). In hierdie geval is dit noodsaaklik dat die sel waarin die berekeningsformule geleë is (in ons geval PMT), geleë op die grens van ry en kolom name. Sonder hierdie toestand sal die instrument nie werk wanneer twee veranderlikes gebruik word nie.
- Kies dan al die tabelreekse wat daaruit voorkom, insluitende die name van die kolomme, rye en die sel met die formule PMT. Gaan na die oortjie "Data". Soos in die vorige tyd, klik op die knoppie. Wat-as-analisein 'n groep gereedskap "Werk met data". Kies die item in die lys wat oopmaak "Data tabel ...".
- Die gereedskap venster begin. "Data Table". In hierdie geval benodig ons albei velde. In die veld "Vervangingswaardes deur kolomme in" Ons spesifiseer die koördinate van die sel wat die leningstermyn bevat in die primêre data. In die veld "Vervangingswaardes deur rye in" spesifiseer die adres van die sel van die aanvanklike parameters wat die waarde van die liggaam van die lening bevat. Na al die data is ingevoer. Ons kliek op die knoppie "OK".
- Die program voer die berekening uit en vul die tabelreeks met data. By die kruising van rye en kolomme is dit nou moontlik om te sien hoe presies die maandelikse betaling sal wees, met 'n ooreenstemmende bedrag van jaarlikse rente en 'n bepaalde kredietperiode.
- Soos u kan sien, nogal baie waardes. Om ander probleme op te los, kan daar selfs meer wees. Om sodoende die uitset van die resultate meer visueel te maak en om dadelik vas te stel watter waardes nie aan die gegewe toestand voldoen nie, kan u visualiseringsinstrumente gebruik. In ons geval sal dit voorwaardelike formatering wees. Kies al die waardes van die tabelreeks, met uitsondering van ry- en kolomopskrifte.
- Skuif na oortjie "Home" en klik op die ikoon "Voorwaardelike formatering". Dit is in die gereedskapkas geleë. "Styles" op die band. Kies die item in die kieslys wat oopmaak "Reëls vir sel seleksie". Klik in die bykomende lys op die posisie "Minder ...".
- Hierna word die venster voorwaardelike formatering oopgemaak. In die linker veld spesifiseer ons die waarde, minder as wat die selle gekies sal word. Soos ons onthou, is ons tevrede met die toestand waaronder die maandelikse betaling op die lening minder sal wees 29000 roebels. Tik hierdie nommer in. In die regte veld is dit moontlik om die kleur van die seleksie te kies, alhoewel jy dit as standaard kan verlaat. Nadat al die vereiste instellings ingevoer is, klik op die knoppie. "OK".
- Daarna sal alle selle wie se waardes ooreenstem met die bogenoemde kondisie in kleur uitgelig word.
Nadat u die tabelreeks ontleed het, kan u 'n paar gevolgtrekkings maak. Soos u kan sien, moet u met die huidige leningstydperk (36 maande) belê in die bogenoemde aangeduide bedrag van 'n maandelikse betaling. U moet 'n lening van hoogstens 8,600,000.00 roebels neem, dit is 40.000 minder as wat oorspronklik beplan is.
As ons steeds van plan is om 'n lening van 900.000 roebels te neem, dan moet die leningstermyn 4 jaar wees (48 maande). Slegs in hierdie geval sal die bedrag van die maandelikse betaling nie die gevestigde limiet van 29 000 roebels oorskry nie.
Dus, om voordeel te trek uit hierdie tabelversameling en om die voor- en nadele van elke opsie te ontleed, kan die lener 'n spesifieke besluit neem oor die terme van die uitleen en die opsie kies wat die beste pas by sy behoeftes.
Uiteraard kan die opsoektabel nie net gebruik word om kredietopsies te bereken nie, maar ook om baie ander probleme op te los.
Les: Voorwaardelike formatering in Excel
In die algemeen moet daarop gelet word dat die opsoek tabel 'n baie nuttige en relatief eenvoudige hulpmiddel is om die uitslag van verskillende kombinasies van veranderlikes te bepaal. Deur voorwaardelike formatering toe te pas, kan u ook die inligting wat u ontvang, visualiseer.
