Ofte necesas kalkuli la finan rezulton por diversaj kombinaĵoj de enigaĵoj. Tiel, la uzanto povos taksi ĉiujn eblajn agmanierojn, elekti tiujn, kiuj interagas rezulton kontentigas lin, kaj fine elekti la plej optimuman elekton. En Excel, ekzistas speciala ilo por ĉi tiu tasko - "Datuma Tabelo" ("Serĉa tablo"). Ni trovu kiel uzi ĝin por plenumi la supre scenarojn.
Vidu ankaŭ: Parametrelekto en Excel
Uzante datumtabelon
Ilo "Datuma Tabelo" ĝi estas desegnita por kalkuli la rezulton per diversaj variaĵoj de unu aŭ du difinitaj variabloj. Post kalkulado, ĉiuj eblaj opcioj aperos en la formo de tabelo, kiu nomiĝas la matrico de faktoraj analizoj. "Datuma Tabelo" rilatas al grupo de iloj "Kion-se" analizokiu estas metita sur la rubando en la langeto "Datumoj" en bloko "Laborante kun datumoj". Antaŭ Excel 2007, ĉi tiu ilo portis nomon. "Serĉa tablo"tio eĉ pli precize reflektis ĝian esencon ol la nuna nomo.
La serĉta tablo povas esti uzata en multaj kazoj. Ekzemple, tipa opcio estas kiam vi bezonas kalkuli la kvanton de monata prunto pagante kun diversaj variadoj de la kreditoperiodo kaj la pruntita kvanto, aŭ la kreditoperiodo kaj intereza normo. Ĉi tiu ilo ankaŭ povas esti uzata dum analizo de investaj projektaj modeloj.
Sed vi ankaŭ konsciu, ke troa uzo de ĉi tiu ilo povas kaŭzi bremson de la sistemo, ĉar datumoj konstante recalculas. Sekve, oni rekomendas ne uzi ĉi tiun ilon per malgrandaj tabularaj tabeloj por solvi similajn problemojn, sed apliki la kopion de formuloj per la pleniga markilo.
Justigita apliko "Datumoj-Tabeloj" estas nur en grandaj tabulaj distancoj, kiam kopiado de formuloj povas preni multan tempon, kaj dum la procedo mem, la probablo de eraroj pliigas. Sed eĉ en ĉi tiu kazo, estas rekomendite malebligi aŭtomatan re-kalkuladon de formuloj en la intervalo de la serĉtabelo, por eviti nenecesan ŝarĝon sur la sistemo.
La ĉefa diferenco inter la diversaj uzoj de datuma tabelo estas la nombro de variabloj implikitaj en la kalkulo: unu variablo aŭ du.
Metodo 1: uzu la ilon per unu variablo
Tuj ni konsideru la opcion kiam datuma tabelo estas uzata kun unu variablo-valoro. Prenu la plej tipan ekzemplon de pruntoj.
Do nuntempe oni proponas al ni la sekvajn kreditajn kondiĉojn:
- Periodo de prunto - 3 jaroj (36 monatoj);
- Prunto: 900000 rubloj;
- Intereso - 12,5% jare.
Pagoj estas faritaj ĉe la fino de la pagperiodo (monato) uzante la anuj-skemon, te en egalaj akcioj. Samtempe, je la komenco de la tuta pruntperiodo, pagendaĵoj de intereso signifas parton de la pagoj, sed dum la korpo ŝrumpas, intereso pagoj malpliiĝas, kaj la kvanto de repago de la korpo mem pliigas. La totala pago, kiel menciita supre, restas senŝanĝa.
Estas necese kalkuli, kia estos la kvanto de la monata pago, kiu inkluzivas la repagon de la prunteduktejo kaj interesajn pagojn. Por ĉi tio, Excel havas telefoniston PMT.
PMT I apartenas al grupo de financaj funkcioj kaj ĝia tasko estas kalkuli la pagon de la monata prunto de la tipo de rento surbaze de la kvanto de la pruntedona korpo, la prunto kaj la intereza normo. La sintakso por ĉi tiu funkcio estas jene.
= PMT (kurzo; nper; ps; bs; tipo)
"Veto" - La argumento difinanta la interezokvoton de kreditpagoj. La indikilo estas difinita por la periodo. Nia pago estas unu monato. Sekve, la jara indico de 12,5% devas esti disigita en la nombron da monatoj en jaro, te 12.
"Kper" - La argumento, kiu determinas la nombron de periodoj por la tuta periodo de la prunto. En nia ekzemplo, la periodo estas unu monato, kaj la prunto estas 3 jaroj aŭ 36 monatoj. Tiel, la nombro de periodoj estos frue 36.
"PS" - la argumento, kiu determinas la nunan valoron de la prunto, te la grandeco de la pruntedona korpo dum ĝia elsendo. En nia kazo, ĉi tiu cifero estas 900.000 rubloj.
"BS" - argumento indikanta la grandecon de la pruntedona korpo dum ĝia plena pago. Nature, ĉi tiu indikilo egalos al nulo. Ĉi tiu argumento estas nedeviga. Se vi preterlasas, oni supozas, ke ĝi egalas al la nombro "0".
"Tipo" - ankaŭ nedeviga argumento. Li informas pri kiam la pago estos farita: komence de la periodo (parametro - "1") aŭ fine de la periodo (parametro - "0"). Kiel ni memoras, nia pago fariĝas je la fino de la kalendaro, te la valoro de ĉi tiu argumento egalos al "0". Sed, ĉar ĉi tiu indikilo ne estas deviga, kaj implicite, se ĝi ne estas uzata, oni supozas ke la valoro estas "0", tiam en la specifita ekzemplo ĝi ne povas esti uzita entute.
- Do ni daŭrigas la kalkulon. Elektu la ĉelon en la folio, kie la kalkulita valoro estos montrata. Ni alklakas la butonon "Enmetu funkcion".
- Komenciĝas Funkciaj Sorĉisto. Faru la transiron al la kategorio "Financa", elektu el la listo la nomon "PLT" kaj alklaku la butonon "Bone".
- Post ĉi tio, estas aktivigo de la argumenta fenestro de la supra funkcio.
Metu la kursoron sur la kampon "Veto"tiam alklaku la ĉelon en la folio kun la valoro de la ĉiujara intereza normo. Kiel vi povas vidi, ĝiaj koordinatoj tuj aperas en la kampo. Sed, kiel ni memoras, ni bezonas monatan tarifon, kaj tial ni dividas la rezulton de 12 (/12).
En la kampo "Kper" sammaniere, ni eniras la koordinatojn de la kredito-termino ĉeloj. En ĉi tiu kazo, nenio devas esti dividita.
En la kampo "Ps" Vi devas specifi la koordinatojn de la ĉelo enhavanta la valoron de la kredita korpo. Ni faras ĝin. Ni ankaŭ metas signon antaŭ la montrataj koordinatoj. "-". La punkto estas ke la funkcio PMT implicite, ĝi donas la finan rezulton per negativa signo, konsiderante la monatan pagan pagon kiel perdon. Sed por klareco, ni bezonas ke la datumtabelo estu pozitiva. Tial ni markas "minus" antaŭ unu el la funkciaj argumentoj. Kiel estas sciata, multipliko "minus" plu "minus" poste donas plus.
En la kampoj "B" kaj "Tipo" Ni tute ne enmetas datumojn. Ni alklakas la butonon "Bone".
- Poste, la operatoro kalkulas kaj montras en la antaŭdestinita ĉelo la rezulton de la tuta monata pago - 30108,26 rubloj. Sed la problemo estas, ke la pruntepruntisto povas pagi maksimume 29.000 rublojn monate, te li devas aŭ trovi bankon proponantan kondiĉojn kun pli malalta intereza normo, aŭ malpliigi la prunton de korpo, aŭ plilongigi la prunton. Kalkuli la diversajn opciojn por agado helpos al ni la serĉotablon.
- Komenci, uzu la serĉan tablon kun unu variablo. Ni vidu kiel la valoro de la deviga monata pago varias kun malsamaj variadoj en la jara kurzo, de kiuj iras 9,5% jara kaj finanta 12,5% pa kun paŝo 0,5%. Ĉiuj aliaj kondiĉoj restas neŝanĝitaj. Desegnu tabulan teritorion, kies nomoj de la kolumnoj respondos al malsamaj varioj de la intereza normo. Kun ĉi tiu linio "Monataj pagoj" lasu ĝin kiel ĝi estas. Ia unua ĉelo devas enhavi la formulon, kiun ni kalkulis pli frue. Por pliaj informoj, vi povas aldoni liniojn "Totala prunta sumo" kaj "Totala Intereso". La kolumno, en kiu troviĝas la kalkulo, estas farita sen kaploko.
- Poste ni kalkulas la tutan sumon de la prunto laŭ la nunaj kondiĉoj. Por fari tion, elektu la unuan ĉelon de la vico. "Totala prunta sumo" kaj multipliki la ĉelan enhavon "Monata pago" kaj "Prunto. Post ĉi tio alklaku Eniru.
- Por kalkuli la tutan intereson laŭ la nunaj kondiĉoj, simile ni subtrahas la valoron de la pruntorgano de la tuta sumo de la prunto. Por montri la rezulton sur la ekrano, alklaku la butonon. Eniru. Tiel, ni ricevas la kvanton, kiun ni tro multe pagas al la redono de la prunto.
- Nun estas tempo apliki la ilon. "Datuma Tabelo". Elektu la tutan tabelan tabelon, krom la vico nomoj. Post tio iru al la langeto "Datumoj". Alklaku la butonon sur la rubando "Kion-se" analizokiu estas enmetita en grupon de iloj "Laborante kun datumoj" (En Excel 2016, grupo de iloj "Prognozo"). Tiam malfermiĝas malgranda menuo. En ĝi ni elektas la pozicion "Datuma Tabelo ...".
- Malgranda fenestro malfermiĝas, kiu nomiĝas "Datuma Tabelo". Kiel vi povas vidi, ĝi havas du kampojn. Ĉar ni laboras kun unu variablo, ni bezonas nur unu. Ĉar niaj variabloj okazas en kolumnoj, ni uzos la kampon "Anstataŭigu valorojn per kolumnoj en". Ni metas la kursoron tie, kaj poste alklaku la ĉelon en la komenca datuma aro, kiu enhavas la nunan valoron de procento. Post kiam la koordinatoj de la ĉelo estas montritaj en la kampo, alklaku la butonon "Bone".
- La ilo kalkulas kaj plenigas la tutan tablotukon per valoroj, kiuj korespondas al malsamaj interezaj opcioj. Se vi metas la kursoron en ĉiun elementon de ĉi tiu tabla spaco, vi povas vidi, ke la formala stango montras ne regulan pagan kalkulan formulon, sed specialan formulon de senrompa tabelo. Tio estas, ne plu eblas ŝanĝi la valorojn en individuaj ĉeloj. Forigi la kalkulajn rezultojn povas esti ĉiuj kune, kaj ne aparte.
Krome, oni povas noti, ke la valoro de la monata pago je 12.5% jare, akirita per aplikado de la serĉa tabelo, respondas al la valoro je la sama intereza normo, kiun ni ricevis per apliko de la funkcio. PMT. Ĉi tio denove pruvas la ĝustecon de la kalkulo.
Analizinte ĉi tiun tabulan tabelon, oni devas diri ke, kiel ni vidas, nur je 9,5% jare, oni akiras la akcepteblan monatan pagon (malpli ol 29.000 rubloj).
Leciono: Kalkulo de la rento-pago en Excel
Metodo 2: uzu ilon kun du variabloj
Kompreneble estas tre malfacile trovi bankojn, kiuj liveras pruntojn je 9,5% jare. Sekve, ni vidu, kiuj opcioj investu en akceptebla nivelo de monata pago por diversaj kombinaĵoj de aliaj variabloj: la grandeco de la pruntedona korpo kaj la prunto. Samtempe la interezokvoto restos neŝanĝita (12,5%). La ilo helpos nin pri ĉi tiu tasko. "Datuma Tabelo" uzante du variablojn.
- Desegnu novan tabelan aron. Nun la kredito-termino estos indikita en la kolumnnomoj (de 2 ĝis 6 jarojn en monatoj laŭ paŝoj de unu jaro), kaj en la vicoj - la grandeco de la pruntedona korpo (de.) 850000 ĝis 950000 Rubloj en pliigoj 10000 rubloj). En ĉi tiu kazo, estas nepre ke la ĉelo en kiu la kalkula formulo troviĝas (en nia kazo PMT), situanta sur la limo de nomoj de vicoj kaj kolonoj. Sen ĉi tiu kondiĉo, la ilo ne funkcios uzinte du variablojn.
- Tiam elektu la tutan rezultan tablotakon, inkluzive la nomojn de la kolumnoj, vicoj kaj la ĉelo kun la formulo PMT. Iru al la langeto "Datumoj". Kiel en la antaŭa tempo, alklaku la butonon. "Kion-se" analizoen grupo de iloj "Laborante kun datumoj". En la listo, kiu malfermiĝas, elektu la elementon "Datuma Tabelo ...".
- La ila fenestro komenciĝas. "Datuma Tabelo". En ĉi tiu kazo, ni bezonas ambaŭ kampojn. En la kampo "Anstataŭigu valorojn per kolumnoj en" Ni specifas la koordinatojn de la ĉelo enhavanta la pruntan terminon en la primaraj datumoj. En la kampo "Anstataŭigu valorojn per vicoj" specifi la adreson de la ĉelo de la komencaj parametroj enhavantaj la valoron de la korpo de la prunto. Post kiam ĉiuj datumoj estas enigitaj. Ni alklakas la butonon "Bone".
- La programo plenumas la kalkulon kaj plenigas la tablotukon per datumoj. Ĉe la intersekciĝo de vicoj kaj kolonoj, nun eblas observi kiel ekzakte estos la monata pago, kun responda kvanto de jara intereso kaj specifa kreditada periodo.
- Kiel vi povas vidi, sufiĉe da valoroj. Por solvi aliajn problemojn, eble eĉ pli. Sekve, por fari la rezulton de la rezultoj pli vida kaj tuj determini kiuj valoroj ne kontentigas la donitan kondiĉon, vi povas uzi videblajn ilojn. En nia kazo ĝi estos kondiĉa aspektigo. Elektu ĉiujn valorojn de la tabela intervalo, ekskluzive de vicoj kaj kolumnoj.
- Movu al langeto "Hejmo" kaj alklaku la ikonon "Kondiĉa Formatado". I troviĝas en la ilobreto. "Stiloj" sur la bendo. En la menuo kiu malfermiĝas, elektu la elementon "Reguloj por ĉela selektado". En la aldona listo klaku sur la pozicio "Malpli ...".
- Post ĉi tio, la fenestro pri agorda laŭpaĝa formatado malfermiĝas. En la maldekstra kampo ni specifas la valoron, malpli ol la ĉelojn elektitajn. Kiel ni memoras, ni estas kontentaj pri la kondiĉo, en kiu la monata pago de la prunto estos malpli 29000 rubloj. Enigu ĉi tiun numeron. En la dekstra kampo eblas elekti la koloron de la selektado, kvankam vi povas lasi ĝin defaŭlte. Post kiam ĉiuj bezonataj agordoj estas enigitaj, alklaku la butonon. "Bone".
- Post tio, ĉiuj ĉeloj kies valoroj korespondas al la antaŭa kondiĉo estos kolorigitaj.
Post analizado de la tabelaro, vi povas eltiri iujn konkludojn. Kiel vi povas vidi, kun la aktuala pruntperiodo (36 monatoj), por investi en la antaŭa indikita kvanto de monata pago, ni devas preni prunton ne superante 8,600,000.00 rublojn, te 40,000 malpli ol la origine planita.
Se ni ankoraŭ intencas pruntepreni kvanton de 900.000 rubloj, tiam la prunto estu 4 jaroj (48 monatoj). Nur en ĉi tiu kazo, la sumo de la monata pago ne superos la establitan limon de 29.000 rubloj.
Do, profite de ĉi tiu tabula tabelo kaj analizante la avantaĝojn kaj malavantaĝojn de ĉiu opcio, la prunteprenas povas fari specifan decidon pri la kondiĉoj de pruntedonado, elektante la opcion plej taŭgan por liaj bezonoj.
Kompreneble, la serĉtabelo povas uzi ne nur por kalkuli opciojn pri kredito, sed ankaŭ por solvi multajn aliajn problemojn.
Leciono: Kondiĉa Formatado en Excel
Enerale, oni notu, ke la serĉotablo estas tre utila kaj relative simpla ilo por determini la rezulton de diversaj kombinaĵoj de variabloj. Uzante kondiĉan formatadon kune kun ĝi, vi ankaŭ povas vidi la informojn ricevitajn.
