Donnéeën Tabelle a Microsoft Excel

Zimmlech oft musst Dir d'Finale Resultat fir verschidde Kombinatioune vun Inputdaten berechnen. Sou kann de Benotzer all méiglech Optiounen fir Aktiounen bewäerten, wielt déi deenen hir Interaktiounsresultater him zefridden hunn, a schlussendlech déi optimal Optioun ze wielen. An Excel, fir dës Aufgab ze maachen, gëtt et e speziellen Tool - "Datetafel" (Substitution Table) Loosst eis erausfannen wéi et benotzt gëtt fir déi uewe genannte Szenarie fäerdeg ze maachen.

Liest och: Parameterauswiel an Excel

Mat Hëllef vun Daten Tabelle

Instrument "Datetafel" Et soll d'Resultat fir verschidde Variatiounen vun engem oder zwee definéierte Variabelen berechnen. No der Berechnung erschéngen all méiglech Optiounen a Form vun engem Dësch, wat d'Matrix vu Faktoranalyse genannt gëtt. "Datetafel" bezitt sech op eng Grupp vun Tools "Wat wann Analyse", déi op der Band am Tab geluecht ass "Donnéeën" a Spär "Aarbecht mat Daten"An. Virun Excel 2007 gouf dëst Tool genannt Substitution Table, wat seng Essenz nach méi genau reflektéiert wéi den aktuellen Numm.

De Lookup Tabelle kann a ville Fäll benotzt ginn. Zum Beispill, eng typesch Optioun ass wann Dir de Betrag vum monatleche Prêtbezuelung fir verschidde Variatioune vun der Kredittperiod an dem Prêtbetrag, oder der Kredittperiod an dem Zënssaz muss berechnen. Och dëst Tool kann an der Analyse vu Modeller vun Investitiounsprojeten benotzt ginn.

Awer Dir sollt och bewosst sinn datt exzessiv Benotzung vun dësem Tool kann zu Systembremsen féieren, well Daten kontinuéierlech zréckgezunn ginn. Dofir ass et an klengen Tabellraisungen ze recommandéieren ähnlech Probleemer ze léisen fir dëst Tool net ze benotzen, awer fir Formelkopie mat der Füllmarkéierung ze benotzen.

Gerechtfäerdegt Applikatioun "Daten Dëscher" ass nëmmen a groussen Dëschberäicher, wann d'Kopieformele vill Zäit daueren kënnen, a wärend der Prozedur selwer ass d'Wahrscheinlechkeet fir Feeler ze maachen. Awer an dësem Fall ass et recommandéiert déi automatesch Berechnung vun de Formelen an der Gamme vun der Auswiesselungstabel auszeschalten fir onnéideg Belaaschtung op de System ze vermeiden.

Den Haaptunterschied tëscht de verschiddene Gebrauch vun der Datatafel ass d'Zuel vu Variabelen déi an der Berechnung involvéiert sinn: eng Variabel oder zwee.

Method 1: benotzt d'Instrument mat enger Variabel

Loosst eis direkt d'Optioun kucken wann d'Datatabelle mat engem variabelen Wäert benotzt gëtt. Huelt dat typeschst Prêt Beispill.

Sou, am Moment gi mir déi folgend Prêtbedingunge ugebueden:

• Prêt Dauer - 3 Joer (36 Méint);
• Prêt Betrag - 900.000 Rubelen;
• Zënssaz - 12,5% pro Betrag.

Bezuelungen geschéien um Enn vun der Bezuelungsperiod (Mount) no dem Annuity Schema, also a gläiche Aktien. Zur selwechter Zäit, am Ufank vum ganzen Prêtperiod, ass e wichtegen Deel vun de Bezuelungen Zënssätz, awer wéi de Kierper schrumpelt, falen d'Interesszuelungen erof, an de Remboursementzomm vum Kierper selwer erhéicht. D'Gesamtbezuelung, wéi uewen erwähnt, bleift onverännert.

Et ass noutwendeg ze berechnen wat de Montant vun der monatlecher Bezuelung wäert sinn, och de Remboursement vum Prêtkierper an Zënssätz. Fir dëst huet Excel e Bedreiwer PMT.

PMT gehéiert zu der Grupp vu finanziellen Funktiounen an hir Aufgab ass de monatlechen Annuity-Typ Prêtzuel ze berechnen op Basis vum Betrag vum Prêtkierper, Prêtbezeechnung an Zënssaz. D'Syntax vun dëser Funktioun gëtt presentéiert

= PLT (Taux; nper; ps; bs; Typ)

Offer - en Argument dat den Zënssaz vu Kreditbezuelungen bestëmmt. Den Indikator ass fir d'Period agestallt. Eis Bezuelperiod ass gläich wéi e Mount. Dofir soll de Joresraten vun 12,5% duerch d'Zuel vun de Méint an engem Joer gedeelt ginn, also 12.

"Nper" - en Argument dat d'Zuel vun de Perioden fir de ganze Prêt Begrëff bestëmmt. An eisem Beispill ass d'Period ee Mount, an de Prêt Dauer ass 3 Joer oder 36 Méint. Sou ass d'Zuel vun de Perioden fréi 36.

"PS" - en Argument dat den aktuelle Wäert vum Prêt bestëmmt, dat ass, et ass d'Gréisst vum Prêtkierper bei der Zäit vu senger Ausgab. An eisem Fall ass dës Figur 900.000 Rubel.

"BS" - en Argument wat d'Gréisst vum Prêtkierper ugeet an der Zäit vu vollem Bezuelen. Natierlech wäert dësen Indikator gläich Null sinn. Dëst Argument ass fakultativ. Wann Dir en iwwerspréngt, gëtt ugeholl datt se d'selwecht wéi d'Zuel "0" ass.

"Aart" - och eng fakultativ Argument. Hien annoncéiert wéini genau d'Bezuelung gemaach gëtt: am Ufank vun der Period (Parameter - "1") oder um Enn vun der Period (Parameter & ndash; "0") Wéi mir eis erënneren, gëtt eis Bezuelung um Enn vum Kalenner Mount gemaach, also de Wäert vun dësem Argument wäert gleichgestallt sinn "0"An. Awer, wéinst der Tatsaach, datt dësen Indikator net obligatoresch ass, a mam Standard, wann net benotzt, gëtt de Wäert implizéiert als gläich "0"an, dann am ugewisenen Beispill kann et ganz ewechgelooss ginn.

1. Also, mir ginn weider an d'Berechnung. Wielt eng Zell op der Plack wou de berechte Wäert ugewise gëtt. Klickt op de Knäppchen "Füügt Funktioun".



2. Fänkt un Feature WizardAn. Mir plënneren an d'Kategorie "Finanziell"wielt den Numm vun der Lëscht "PLT" a klickt op de Knäppchen "OK".



3. Duerno gëtt d'Argumenterfenster vun der uewe genannter Funktioun aktivéiert.

   Stellt de Cursor am Feld Offer, no deem mir op d'Zelle klickt op de Blat mam Wäert vum jäerlechen Zënssaz. Wéi Dir kënnt gesinn, ginn hir Koordinaten direkt am Feld ugewisen. Awer, wéi mir eis drun erënneren, brauche mir e monatlechen Taux, an dofir deele mir d'Resultat mat 12 (/12).

   Am Feld "Nper" déiselwecht Manéier gi mir d'Koordinaten vun den Zellen vum Prêt Term. An dësem Fall braucht Dir näischt ze deelen.

   Am Feld Ps Dir musst d'Koordinaten vun der Zell spezifizéieren, déi de Wäert vum Prêtkierper enthält. Mir maachen et. Mir setzen och e Schëld virun de gewise Koordinaten "-"An. De Fakt ass datt d'Funktioun PMT Par défaut gëtt et d'Schlussresultat mat engem negativen Zeechen, richteg berécksiichtegt de monatlecht Bezuelverloscht. Awer fir Kloerheet vun der Uwendung vun der Datetafel, brauche mir dës Nummer positiv. Dofir setzen mir en Zeechen Minus virun engem vun der Funktiounsargument. Multiplikatioun ass bekannt Minus un Minus am Endeffekt gëtt plus.

   An d'Felder "Bs" an "Aart" Daten sinn guer net aginn. Klickt op de Knäppchen "OK".



4. Duerno rechent de Bedreiwer d'Resultat vum Gesamtbetrag an enger pre-designéierter Zell eraus a weist & ndash; 30108,26 rubelen. Awer de Problem ass datt de Prêt e Maximum vun 29.000 Rubelen pro Mount kann bezuelen, dat heescht, hie sollt entweder eng Bank fannen, déi Konditioune ubidden mat engem nidderegen Zënssaz, oder de Prêtkierper reduzéieren, oder de Prêtperiod erhéijen. De Lookup Tabelle hëlleft eis déi verschidden Optiounen erauszefannen.



5. Als éischt benotzt de Lookup-Dësch mat enger Variabel. Kucke mer wéi d'Quantitéit vum obligatoresche monatleche Bezuelen mat verschiddene Variatiounen vum Joresraten geännert 9,5% pro Joer an op en Enn 12,5% pro Joer am Steigerung 0,5%An. All aner Konditioune sinn onverännert. Mir molen eng Tabellerei, déi d'Nimm vun de Sailen vun deenen entspriechend Variatiounen vum Zënssaz entspriechen. Mat dëser Linn "Mount Bezuelungen" verlooss wéi et ass. Seng éischt Zell soll d'Formel enthalen déi mir virdru berechent hunn. Fir méi Informatioun kënnt Dir Linnen addéieren "Gesamtbetrag Prêt" an "Gesamtinteress"An. D'Kolonn an där d'Berechnung ass gëtt ouni Header gemaach.



6. Als nächst berechent mir de Gesamtprêtzuelen ënnert aktuellen Bedingungen. Fir dëst ze maachen, wielt déi éischt Zell vun der Zeil "Gesamtbetrag Prêt" an multiplizéieren d'Inhalter vun den Zellen "Monatlech Bezuelung" an "Prêt Begrëff"An. Duerno klickt op de Knäppchen Gitt an.



7. Fir de Gesamtbetrag vun Interesse ënner aktuellen Bedéngungen ze berechnen, subtrahéiere mir de Betrag vum Prêtkierper ähnlech vum Gesamtprêt. Fir d'Resultat um Bildschierm ze weisen, klickt op de Knäppchen Gitt anAn. Doduerch kréie mer de Betrag dee mir iwwerbezuelen beim Prêt zréckbezuelen.



8. Elo ass et Zäit de Tool ëmzesetzen "Datetafel"An. Mir wielt déi ganz Tabellarray, ausser Roudennamen. Duerno gitt op d'Tab "Donnéeën"An. Klickt op de Knäppchen um Band "Wat wann Analyse"déi an der Toolgruppe steet "Aarbecht mat Daten" (an Excel 2016, eng Grupp vun Tools "Prognosen") Da geet e klenge Menu op. An deem wiele mer eng Positioun "Datentafel ...".



9. Eng kleng Fënster geet op, wat nennt een "Datetafel"An. Wéi Dir kënnt gesinn, huet et zwee Felder. Well mir mat enger Variabel schaffen, brauche mir nëmmen eng vun hinnen. Well mir d'Variabel Kolonn no Kolonn änneren, benotze mir d'Feld Ersatz Kolonn Wäerter anAn. Setzt de Cursor do, a klickt duerno op d'Zell am ursprénglechen Datesaz, deen den aktuellen Prozentsaz enthält. Nodeems d'Zellkoordinaten am Feld ugewise ginn, klickt op de Knäppchen "OK".



10. De Tool rechent a fëllt d'ganz Tabular Range mat Wäerter déi entspriechend Optioune fir den Zënssaz entspriechen. Wann Dir de Cursor an all Element vun dësem Tabelleberäich placéiert, kënnt Dir gesinn datt d'Formelbar net déi üblech Formel fir d'Bezuelung ze berechnen, awer eng speziell Formel fir eng inextricéierbar Array. Dat ass, et ass elo onméiglech d'Wäerter an eenzel Zellen z'änneren. Dir kënnt Berechnungsresultater nëmmen all zesummen läschen, an net getrennt.

Zousätzlech kënnt Dir gesinn datt de monatleche Bezuelung bei 12,5% pro Joer kritt als Resultat vun der Uwendung vun der Lookup Tabelle entsprécht dem Wäert fir de selwechte Montant vun Interessi dee mir kritt hunn andeems Dir d'Funktioun uwendegt PMTAn. Dëst beweist nach eng Kéier d'Richtegkeet vun der Berechnung.

Nodeems mir dës Tabelle-Array analyséiert hunn, sollt et gesot ginn, wéi Dir gesitt, nëmmen mat engem Taux vun 9,5% pro Joer kréien mir en akzeptablen monatleche Bezuelungsniveau (manner wéi 29.000 Rubelen).

Lektioun: Berechnung annuity Bezuelung an Excel

Method 2: benotzt den Tool mat zwou Variabelen

Natierlech, fir de Moment Banken ze fannen déi Prêten op 9,5% pro Joer ausginn, ass ganz schwéier, wann net onméiglech. Dofir wäerte mir gesinn wéi eng Optiounen et existéieren fir en akzeptablen Niveau vun der monatlecher Bezuelung fir verschidde Kombinatioune vun anere Variabelen ze investéieren: d'Gréisst vum Prêtkierper an de Prêtbegrëff. An dësem Fall bleift den Zënssaz onverännert (12,5%). Fir dëse Problem ze léisen hëlleft e Tool. "Datetafel" zwou Variabelen ze benotzen.

1. Mir molen en neit Tabellesystem. Elo an der Kolonn Nimm ginn de Prêt Begrëff ugewisen (vun 2 virdrun 6 Joer a Méint an Inkrementer vun engem Joer), an an de Linnen - d'Gréisst vum Prêtkierper (vun 850000 virdrun 950000 Rubelen an Steigerungen 10000 rubelen). An dësem Fall ass eng Viraussetzung datt d'Zelle an där d'Berechnungsformel läit (an eisem Fall PMT), op der Grenz vun Zeilen- a Kolonnennamen. Ouni dës Bedingung funktionnéiert de Tool net wann Dir zwou Variabelen benotzt.



2. Wielt dann d'ganz resultéierend Tabellebereich, ënner anerem d'Nimm vun de Sailen, Reihen an eng Zell mat der Formel PMTAn. Gitt op den Tab "Donnéeën"An. Wéi déi viregt Kéier, klickt op de Knäppchen "Wat wann Analyse", an der Toolsengrupp "Aarbecht mat Daten"An. An der Lëscht déi opmaacht, wielt "Datentafel ...".



3. Den Toolfenster fänkt un "Datetafel"An. An dësem Fall brauche mir béid Felder. Am Feld Ersatz Kolonn Wäerter an uginn d'Koordinaten vun der Zell déi de Prêtbegrëff an de primäre Daten enthält. Am Feld "Ersatz Wäerter Zeile vun Zeil an" gitt d'Adress vun der Zell un déi initial Parameteren mat dem Wäert vum Prêtkierper. No all d'Daten ass aginn. Klickt op de Knäppchen "OK".



4. De Programm fiert d'Berechnung aus a fëllt den Tabellebereich mat Daten. Op der Kräizung vu Reihen a Kolonnen ass et elo méiglech ze beobachten wat genau de monatleche Bezuelen wäert sinn, mat dem entspriechende Betrag vum jäerlechen Zënssaz an dem gezeechente Prêtperiod.



5. Wéi Dir kënnt gesinn, ginn et vill Wäerter. Fir aner Probleemer ze léisen, kann et nach méi sinn. Dofir, fir d'Ausgab vun de Resultater méi visuell ze maachen an direkt ze bestëmmen déi Wäerter net déi uginn Konditioun erfëllen, kënnt Dir Visualiséierungsinstrumenter benotzen. An eisem Fall ass dëst geplangten Formatéierung. Mir wielt all d'Wäerter vun der Tabellepalette, ausgeschloss der Zeil- a Kolonn Rubriken.



6. Plënnert op den Tab "Heem" a klickt op d'Ikon Conditioune FormatéierungAn. Et steet am Toolblock. Stiler op der Band. Am Menü deen opmécht, wielt Zell Auswiel RegelenAn. An der zousätzlech Lëscht, klickt op d'Positioun "Manner ...".



7. Duerno fänkt d'konditiounsformatéiert Astellungsfenster op. Am lénksen Feld gitt de Wäert manner wéi deen d'Zellen ausgewielt ginn. Wéi mir eis drun erënneren, si mir zefridden mat der Konditioun datt de monatleche Prêtebezuelen manner wäert sinn wéi 29000 rubelen. Mir ginn dës Zuel an. Am richtege Feld kënnt Dir d'Highlight Faarf auswielen, awer Dir kënnt se als Standard verloossen. Nodeems all déi erfuerderlech Astellunge aginn sinn, klickt op de Knäppchen "OK".



8. Duerno ginn all Zellen deenen hir Wäerter un déi uewe genannte Conditioun entspriechen beliicht.

Nodeems mir den Tabellenraum analyséiert hunn, kënne mir e puer Konklusioune treffen. Wéi Dir kënnt gesinn, mam existente Prêtbegrëff (36 Méint), fir an den ugewisenen Betrag vun der monatlecher Bezuelung ze investéieren, musse mir e Prêt huelen net méi wéi 860000,00 Rubel, dat heescht 40.000 manner wéi ursprénglech geplangt.

Wa mir ëmmer nach e Prêt vun 900.000 Rubel huelen, da soll de Prêt Dauer 4 Joer (48 Méint) sinn. Nëmmen an dësem Fall wäert de monatleche Bezuelen net den etabléierte Limit vun 29.000 Rubelen iwwerschreiden.

Also, andeems Dir dës Tabellearray benotzt an d'Pros a Cons vun all Optioun analyséiert, kann de Prêt eng spezifesch Entscheedung iwwer d'Konditioune vum Prêt huelen, déi passend Optioun aus allen Méiglechkeeten ze wielen.

Natierlech kann de Lookup Tabelle benotzt ginn net nëmme fir Kredittoptiounen ze berechnen, awer och fir vill aner Probleemer ze léisen.

Lektioun: Konditiounsformatéierung an Excel

Allgemeng sollt et bemierkt datt de Lookup-Dësch e ganz nëtzlecht a relativ einfacht Tool ass fir d'Resultat fir verschidde Kombinatioune vu Variabelen ze bestëmmen. Mat Hëllef vu bedéngungsmässegen Formatéiere gläichzäiteg kënnt Dir d'Informatioune kréien visualiséiert.