Datatabel in Microsoft Excel

Dikwels moet u die finale resultaat vir verskillende kombinasies van invoerdata bereken. Die gebruiker sal dus in staat wees om alle moontlike opsies vir aksies te evalueer, diegene te kies wie die interaksie-resultate vir hom bevredig, en uiteindelik die beste opsie te kies. In Excel is daar 'n spesiale instrument om hierdie taak uit te voer - "Datatabel" (Vervangingstabel). Kom ons kyk hoe u dit kan gebruik om bogenoemde scenario's te voltooi.

Lees ook: Parameterseleksie in Excel

Met behulp van datatabel

hulpmiddel "Datatabel" Dit is bedoel om die resultaat vir verskillende variasies van een of twee gedefinieerde veranderlikes te bereken. Na berekening verskyn alle moontlike opsies in die vorm van 'n tabel, wat die matriks van faktoranalise genoem word. "Datatabel" verwys na 'n groep gereedskap “Wat as analise”, wat op die lint in die oortjie geplaas is "Data" in blok "Werk met data". Voor Excel 2007 is hierdie instrument genoem Vervangingstabel, wat die wese selfs meer akkuraat weerspieël het as die huidige naam.

Die opsommingstabel kan in baie gevalle gebruik word. 'N Tipiese opsie is byvoorbeeld wanneer u die bedrag van die maandelikse leningbetaling moet bereken vir verskillende variasies van die kredietperiode en die leningsbedrag, of die kredietperiode en rentekoers. Hierdie instrument kan ook gebruik word in die ontleding van modelle van beleggingsprojekte.

U moet egter ook bewus wees daarvan dat oormatige gebruik van hierdie werktuig tot stelselrem kan lei, aangesien data voortdurend vertel word. Daarom word dit aanbeveel in klein tabelreeks om soortgelyke probleme op te los om nie hierdie instrument te gebruik nie, maar om formule-kopiëring te gebruik met behulp van die vulmerker.

Geregverdigde aansoek "Datatabelle" is slegs in groot tabelreekse wanneer die formule van kopiëring baie tyd kan neem, en tydens die prosedure self neem die waarskynlikheid van foute toe. Maar in hierdie geval word aanbeveel dat die outomatiese herberekening van formules in die reeks van die vervangingstabel gedeaktiveer word om onnodige belasting op die stelsel te voorkom.

Die belangrikste verskil tussen die verskillende gebruike van die datatabel is die aantal veranderlikes wat by die berekening betrokke is: een of twee veranderlikes.

Metode 1: gebruik die instrument met een veranderlike

Laat ons dadelik kyk na die opsie wanneer die datatabel met een veranderlike waarde gebruik word. Neem die mees tipiese uitleenvoorbeeld.

Dus word ons tans die volgende leningsvoorwaardes aangebied:

• Leningstermyn - 3 jaar (36 maande);
• Leningsbedrag - 900.000 roebels;
• Rentekoers - 12,5% per jaar.

Betalings geskied aan die einde van die betalingstydperk (maand) volgens die annuïteitsskema, dit wil sê in gelyke aandele. Terselfdertyd, aan die begin van die hele leningstermyn, is 'n beduidende deel van die betalings rentebetalings, maar namate die liggaam inkrimp, daal rentebetalings en neem die terugbetalingsbedrag van die liggaam self toe. Die totale uitbetaling, soos hierbo genoem, bly onveranderd.

Dit is nodig om te bereken wat die bedrag van die maandelikse betaling sal wees, insluitend die terugbetaling van die leningsliggaam en rentebetalings. Hiervoor het Excel 'n operateur PMT.

PMT behoort tot die groep finansiële funksies en die taak daarvan is om die maandelikse annuïteitstipe-uitbetaling te bereken op grond van die bedrag van die leningsliggaam, leningsperiode en rentekoers. Die sintaksis van hierdie funksie word aangebied as

= PLT (koers; nper; ps; bs; tipe)

"Bet" - 'n argument wat die rentekoers van kredietbetalings bepaal. Die aanwyser is ingestel vir die periode. Ons uitbetalingstydperk is gelyk aan 'n maand. Daarom moet die jaarlikse koers van 12,5% gedeel word deur die aantal maande in 'n jaar, dit wil sê 12.

"NPER" - 'n argument wat die aantal periodes vir die hele leningsterm bepaal. In ons voorbeeld is die periode een maand en die leningstermyn is drie jaar of 36 maande. Dus is die aantal periodes vroeg 36.

"PS" - 'n argument wat die huidige waarde van die lening bepaal, dit wil sê die grootte van die leningsliggaam ten tyde van die uitreiking daarvan. In ons geval is hierdie syfer 900.000 roebels.

"BS" - 'n argument wat die grootte van die leningsliggaam ten tyde van die volle betaling aandui. Uiteraard sal hierdie aanwyser gelyk wees aan nul. Hierdie argument is opsioneel. As u dit oorslaan, word aanvaar dat dit gelyk is aan die getal "0".

"Tik" - ook 'n opsionele argument. Hy kondig aan wanneer die betaling presies gedoen word: aan die begin van die periode (parameter - "1") of aan die einde van die periode (parameter - "0"). Soos ons onthou, word ons betaling aan die einde van die kalendermaand gedoen, dit wil sê, die waarde van hierdie argument sal gelyk wees aan "0". Maar gegewe die feit dat hierdie aanwyser nie verpligtend is nie, en as dit nie gebruik word nie, word die waarde impliseer dat dit gelyk is "0", dan kan dit in die aangeduide voorbeeld heeltemal weggelaat word.

1. Dus gaan ons voort met die berekening. Kies 'n sel op die bladsy waarop die berekende waarde vertoon sal word. Klik op die knoppie "Voeg funksie in".



2. Begin Funksie-towenaar. Ons beweeg na die kategorie "Finansiële", kies die naam uit die lys "PMT" en klik op die knoppie "OK".



3. Hierna word die argiefvenster van bogenoemde funksie geaktiveer.

   Sit die wyser in die veld "Bet", waarna ons op die blad klik met die waarde van die jaarlikse rentekoers. Soos u kan sien, word die koördinate onmiddellik in die veld vertoon. Maar soos ons onthou, het ons 'n maandelikse tarief nodig, en daarom deel ons die resultaat met 12 (/12).

   In die veld "NPER" op dieselfde manier voer ons die koördinate van die selle van die leningstermyn in. In hierdie geval hoef u niks te deel nie.

   In die veld "Ps" u moet die koördinate van die sel wat die waarde van die leningsliggaam bevat, spesifiseer. Ons doen dit. Ons plaas ook 'n bord voor die vertoonde koördinate "-". Die feit is dat die funksie PMT standaard gee dit die finale resultaat met 'n negatiewe teken, met inagneming van die maandelikse verlies aan lenings. Maar vir die duidelikheid van die toepassing van die datatabel, moet ons hierdie nommer positief hê. Daarom plaas ons 'n teken "Min" voor een van die funksie-argumente. Vermenigvuldiging is bekend "Min" op "Min" op die ou end gee "Plus".

   In die veld "BS" en "Tik" data word glad nie ingevoer nie. Klik op die knoppie "OK".



4. Daarna bereken en wys die operateur die resultaat van die totale maandelikse betaling in 'n vooraf-aangeduide sel - 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 bank vind met voorwaardes met 'n laer rentekoers, óf die leningsliggaam verlaag, óf die leningstermyn verhoog. Die soek-tabel help ons om die verskillende opsies uit te vind.



5. Gebruik eers die soek-tabel met een veranderlike. Kom ons kyk hoe die bedrag van die verpligte maandelikse betaling met verskillende variasies van die jaarkoers sal verander, vanaf 9,5% per jaar en eindig 12,5% per jaar in inkremente 0,5%. Alle ander voorwaardes word onveranderd gelaat. Ons teken 'n tabelreeks waarvan die name van die kolomme ooreenstem met verskillende variasies van die rentekoers. Met hierdie lyn "Maandelikse betalings" los soos dit is. Die eerste sel moet die formule bevat wat ons vroeër bereken het. Vir meer inligting, kan u reëls byvoeg "Totale leningsbedrag" en 'Totale belangstelling'. Die kolom waarin die berekening geleë is, word sonder 'n opskrif gedoen.



6. Vervolgens bereken ons die totale leningsbedrag onder huidige omstandighede. Om dit te doen, kies die eerste sel van die ry "Totale leningsbedrag" en die inhoud van die selle te vermenigvuldig "Maandelikse betaling" en "Leningstermyn". Klik daarna op die knoppie Tik.



7. Om die totale rente onder huidige omstandighede te bereken, trek ons ​​die bedrag van die leningsliggaam op dieselfde manier af van die totale leningsbedrag. Klik op die knoppie om die resultaat op die skerm te vertoon Tik. Dus kry ons die bedrag wat ons te veel betaal wanneer ons die lening terugbetaal.



8. Nou is dit tyd om die instrument aan te wend "Datatabel". Ons kies die volledige tabelreeks, behalwe vir ryname. Gaan daarna na die oortjie "Data". Klik op die knoppie op die lint “Wat as analise”wat in die gereedskapgroep geleë is "Werk met data" (in Excel 2016, 'n groep gereedskap "Voorspelling"). Dan word 'n klein menu oopgemaak. Daarin kies ons 'n posisie "Datatabel ...".



9. Daar word 'n klein venster oopgemaak wat genoem word "Datatabel". Soos u kan sien, het dit twee velde. Aangesien ons met een veranderlike werk, benodig ons net een daarvan. Aangesien ons die veranderlike kolom vir kolom verander, sal ons die veld gebruik Plaas kolomwaardes in. Stel die muis daar en klik dan op die sel in die oorspronklike datastel wat die huidige persentasie bevat. Nadat die selkoördinate in die veld vertoon is, klik op die knoppie "OK".



10. Die instrument bereken en vul die volledige tabelreeks met waardes wat ooreenstem met verskillende opsies vir die rentekoers. As u die wyser in enige element van hierdie tabelarea plaas, kan u sien dat die formulebalk nie die gewone formule vir die berekening van die betaling vertoon nie, maar 'n spesiale formule vir 'n onlosmaaklike skikking. Dit wil sê, dit is nou onmoontlik om die waardes in individuele selle te verander. U kan slegs die berekeningsresultate gesamentlik en nie afsonderlik verwyder nie.

Daarbenewens kan u sien dat die maandelikse betaling van 12,5% per jaar wat verkry is as gevolg van die toepassing van die soek-tabel, ooreenstem met die waarde vir dieselfde rente wat ons ontvang het deur die funksie toe te pas. PMT. Dit bewys weereens die korrektheid van die berekening.

Nadat u hierdie tabelreeks geanaliseer het, moet daar gesê word dat ons, soos u sien, slegs teen 'n koers van 9,5% per jaar 'n aanvaarbare maandelikse betalingsvlak kry (minder as 29 000 roebels).

Les: Berekening van annuïteitsbetaling in Excel

Metode 2: gebruik die instrument met twee veranderlikes

Natuurlik is dit baie moeilik, indien nie onmoontlik nie, om banke te vind wat lenings teen 9,5% per jaar uitreik. Daarom sal ons kyk watter opsies bestaan ​​om te belê in 'n aanvaarbare maandelikse bedrag vir verskillende kombinasies van ander veranderlikes: die grootte van die leningsliggaam en die leningstermyn. In hierdie geval sal die rentekoers onveranderd bly (12,5%). As u hierdie probleem oplos, sal 'n hulpmiddel ons help. "Datatabel" gebruik twee veranderlikes.

1. Ons teken 'n nuwe tabelreeks. In die kolom sal die leningsperiode aangedui word (vanaf 2 om 6 jare in maande in stappe van een jaar), en in reëls - die grootte van die leningsliggaam (vanaf 850000 om 950000 roebels in inkremente 10000 roebels). In hierdie geval is 'n voorvereiste dat die sel waarin die berekeningsformule geleë is (in ons geval) PMT), geleë op die rand van die ry- en kolomname. Sonder hierdie voorwaarde sal die instrument nie werk as twee veranderlikes gebruik word nie.



2. Kies dan die volledige resulterende tabelreeks, insluitend die name van kolomme, rye en 'n sel met die formule PMT. Gaan na die oortjie "Data". Klik die vorige keer op die knoppie “Wat as analise”, in die gereedskapgroep "Werk met data". Kies in die lys wat oopmaak "Datatabel ...".



3. Die gereedskapvenster begin "Datatabel". In hierdie geval het ons albei velde nodig. In die veld Plaas kolomwaardes in dui die koördinate van die sel wat die leningstermyn bevat in die primêre data aan. In die veld "Vervang waardes ry vir ry in" dui die adres aan van die sel van die aanvanklike parameters wat die waarde van die leningsliggaam bevat. Nadat al die data ingevoer is. Klik op die knoppie "OK".



4. 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 wat presies die maandelikse betaling sal wees, met die ooreenstemmende jaarlikse rente en die aangeduide leningstermyn.



5. Soos u kan sien, is daar baie waardes. Om ander probleme op te los, kan daar selfs meer wees. Om die uitset van die resultate meer visueel te maak en onmiddellik te bepaal watter waardes nie aan die gegewe voorwaarde voldoen nie, kan u visualiseringshulpmiddels gebruik. In ons geval is dit voorwaardelike formatering. Ons kies al die waardes van die tabelreeks, met uitsondering van die ry- en kolomopskrifte.



6. Gaan na die oortjie "Home" en klik op die ikoon Voorwaardelike formatering. Dit is in die gereedskapblok geleë. "Styles" op die band. Kies in die menu wat oopmaak Seleksie-reëls. Klik in die addisionele lys op die posisie "Minder ...".



7. Hierna word die voorwaardelike formateringsinstellingsvenster oopgemaak. Dui in die linkerveld die waarde aan waaronder die selle gekies sal word. Soos ons onthou, is ons tevrede met die voorwaarde dat die maandelikse leningbetaling minder is as 29000 roebels. Ons voer hierdie nommer in. In die regte veld kan u die kleur van die hoogtepunt kies, alhoewel u dit standaard kan verlaat. Nadat al die vereiste instellings ingevoer is, klik op die knoppie "OK".



8. Daarna sal alle selle waarvan die waardes ooreenstem met bogenoemde toestand, uitgelig word.

Nadat ons die tabelreeks geanaliseer het, kan ons enkele gevolgtrekkings maak. Soos u kan sien, moet ons met die bestaande leningstermyn (36 maande), om te belê in die aangeduide bedrag van die maandelikse betaling, 'n lening van hoogstens 860000.00 roebels neem, dit wil sê 40.000 minder as wat oorspronklik beplan is.

As ons nog steeds van plan is om 'n lening van 900,000 roebels te neem, moet die leningsperiode 4 jaar (48 maande) wees. Slegs in hierdie geval sal die maandelikse betaling nie die vasgestelde limiet van 29 000 roebels oorskry nie.

Deur die tabel-skikking te gebruik en die voor- en nadele van elke opsie te ontleed, kan die lener 'n spesifieke besluit neem oor die voorwaardes van die lening deur die beste keuse uit alle moontlike opsies te kies.

Uiteraard kan die op soekstabel 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 op soekstabel 'n baie nuttige en relatief eenvoudige instrument is om die resultaat vir verskillende kombinasies van veranderlikes te bepaal. As u terselfdertyd voorwaardelike formatering gebruik, kan u die inligting wat u ontvang, visualiseer.