Tabelo de datumoj en Microsoft Excel

Tre ofte, vi bezonas kalkuli la finan rezulton por diversaj kombinaĵoj de enigaj datumoj. Tiel la uzanto povos taksi ĉiujn eblajn eblojn por agoj, elekti tiujn, kies interagaj rezultoj kontentigas lin kaj fine elekti la plej optimuman eblon. En Excel, por plenumi ĉi tiun taskon, ekzistas speciala ilo - "Datuma tablo" (Anstataŭa Tabelo) Ni eksciu kiel uzi ĝin por kompletigi ĉi-suprajn scenojn.

Legu ankaŭ: Parametra elekto en Excel

Uzante datumtabelon

Instrumento "Datuma tablo" Ĝi celas kalkuli la rezulton por diversaj variaĵoj de unu aŭ du difinitaj variabloj. Post kalkulo, ĉiuj eblaj opcioj aperas en formo de tablo, kiu estas nomata matrico de faktoro-analizo. "Datuma tablo" rilatas al grupo de iloj "Kio se analizo", kiu estas metita sur la rubandon en la langeto "Datumoj" en bloko "Labori kun datumoj". Antaŭ Excel 2007, ĉi tiu ilo estis nomata Anstataŭa Tabelo, kiu eĉ pli precize reflektis ĝian esencon ol la nuna nomo.

La serĉa tablo povas esti uzata en multaj kazoj. Ekzemple, tipa opcio estas kiam vi bezonas kalkuli la sumon de la monata pruntpago por diversaj variaĵoj de la kreditperiodo kaj de la prunto, aŭ de la kreditperiodo kaj intereza indico. Ankaŭ ĉi tiu ilo povas esti uzata en la analizo de modeloj de investaj projektoj.

Sed vi ankaŭ devas konstati, ke troa uzo de ĉi tiu ilo povas konduki al bremsita sistemo, ĉar datumoj estas rakontataj konstante. Tial rekomendas en malgrandaj tabelaj tabeloj solvi similajn problemojn ne uzi ĉi tiun ilon, sed uzi formulon kun kopiado per la pleniga markilo.

Pravigita apliko "Datumaj tabloj" estas nur en grandaj tabelvaroj, kiam kopiado de formuloj povas daŭri multan tempon, kaj dum la proceduro mem la probablo de eraroj pliigas. Sed ĉi-kaze rekomendas malaktivigi la aŭtomatan rekalkuladon de formuloj en la gamo de la anstataŭa tabelo por eviti nenecesan ŝarĝon en la sistemo.

La ĉefa diferenco inter la malsamaj uzoj de la datuma tabelo estas la nombro de variabloj implikitaj en la kalkulo: unu variablo aŭ du.

Metodo 1: uzu la ilon kun unu variablo

Tuj ni rigardu la eblon, kiam la datuma tabelo estas uzata kun unu ŝanĝiĝema valoro. Prenu la plej tipan pruntedonan ekzemplon.

Do, nuntempe ni ofertas la jenajn pruntajn kondiĉojn:

• Pruntedono - 3 jaroj (36 monatoj);
• Prunto kvanto - 900.000 rubloj;
• Intereso-procento - 12,5% jare.

Pagoj okazas ĉe la fino de la pagperiodo (monato) laŭ la jara skemo, tio estas en egalaj akcioj. Samtempe, komence de la tuta prunto-periodo, grava parto de pagoj estas interezpagoj, sed ĉar la korpo malpliiĝas, interezaj pagoj malpliiĝas kaj la repago-kvanto de la korpo mem pliiĝas. La tuta pago, kiel menciite supre, restas senŝanĝa.

Estas necese kalkuli kia estos la kvanto de la monata pago, inkluzive de repago de la prunto-korpo kaj interezpagoj. Por ĉi tio, Excel havas telefoniston PMT.

PMT apartenas al la grupo de financaj funkcioj kaj ĝia tasko estas kalkuli la monatan jarkvantan tipon de prunto surbaze de la kvanto de la prunto-korpo, prunto-limtempo kaj interezokosto. La sintakso de ĉi tiu funkcio estas prezentita kiel

= PLT (indico; nper; ps; bs; tipo)

Oferto - argumento, kiu determinas la interezon de kreditpagoj. La indikilo estas agordita por la periodo. Nia pagenda periodo egalas al monato. Tial la jara indico de 12,5% devas esti dividita laŭ la nombro de monatoj en jaro, tio estas 12.

"Nper" - argumento kiu determinas la nombron de periodoj por la tuta prunto. En nia ekzemplo, la periodo estas unu monato, kaj la prunto-periodo estas 3 jaroj aŭ 36 monatoj. Tiel la nombro de periodoj estos frua 36.

"PS" - argumento, kiu determinas la aktualan valoron de la prunto, tio estas, ĝi estas la grandeco de la prunto-korpo en la momento de sia elsendo. En nia kazo, ĉi tiu cifero estas 900.000 rubloj.

"BS" - argumento indikanta la grandecon de la prunto-korpo en la momento de plena pago. Nature ĉi tiu indikilo egalos al nulo. Ĉi tiu argumento estas laŭvola. Se vi preterlasas ĝin, oni supozas, ke ĝi egalas al la nombro "0".

"Tajpu" - ankaŭ laŭvola argumento. Li anoncas kiam ĝuste la pago estos farita: komence de la periodo (parametro - "1") aŭ fine de la periodo (parametro - "0") Kiel ni memoras, nia pago fariĝas fine de la kalendara monato, tio estas, ke la valoro de ĉi tiu argumento estos egala al "0". Sed, konsiderante ke ĉi tiu indikilo ne estas deviga, kaj implicite, se ne uzata, la valoro implicas esti egala "0", tiam en la indikita ekzemplo ĝi povas esti preterlasita entute.

1. Do ni procedas al la kalkulo. Elektu ĉelon sur la folio, kie aperos la kalkulita valoro. Alklaku la butonon "Enmetu funkcion".



2. Komencas supren Feature Sorĉisto. Ni transiras al la kategorio "Financa", elektu la nomon de la listo "PLT" kaj alklaku la butonon "Bone".



3. Sekve de tio, la argumenta fenestro de ĉi-supra funkcio estas aktivigita.

   Metu la kursoron sur la kampo Oferto, post tio ni alklakas la ĉelon sur la folio kun la valoro de la jara intereza normo. Kiel vi povas vidi, ĝiaj koordinatoj tuj montras sur la kampo. Sed, kiel ni memoras, ni bezonas monatan indicon, kaj tial ni dividas la rezulton je 12 (/12).

   En la kampo "Nper" sammaniere ni enigas la koordinatojn de la ĉeloj de la prunto. En ĉi tiu kazo, vi ne bezonas dividi ion ajn.

   En la kampo Ps vi bezonas precizigi la koordinatojn de la ĉelo enhavanta la valoron de la prunto-korpo. Ni faras ĝin. Ni ankaŭ metis signon antaŭ la montritaj koordinatoj "-". Fakto estas, ke la funkcio PMT defaŭlte ĝi donas la finan rezulton kun negativa signo, prave pripensante la monatan prunton perdo. Sed por klareco de apliko de la datuma tabelo, ni bezonas ke ĉi tiu nombro estu pozitiva. Tial ni metis signon minus antaŭ unu el la funkciaj argumentoj. Multiplikado estas konata minus sur minus al la fino donas plus.

   En la kampoj "Bs" kaj "Tajpu" datumoj tute ne estas enigitaj. Alklaku la butonon "Bone".



4. Post tio, la telefonisto kalkulas kaj montras la rezulton de la tuta monata pago en antaŭdizajnita ĉelo - 30108,26 rubloj. Sed la problemo estas, ke la prunteprenanto kapablas pagi maksimume 29.000 rublojn monate, tio estas, li devas aŭ trovi bankon ofertantan kondiĉojn kun malpli alta interezokosto, aŭ redukti la pruntan korpon, aŭ pliigi la prunton. La serĉa tablo helpos nin konstati la diversajn eblojn.



5. Unue, uzu la serĉan tablon kun unu variablo. Ni vidu, kiel ŝanĝiĝos la kvanto de la deviga monata pago kun diversaj variadoj de la jara imposto, ekde 9,5% jare kaj finiĝas 12,5% jare per pliigoj 0,5%. Ĉiuj aliaj kondiĉoj restas senŝanĝaj. Ni desegnas tabelon, kies nomoj respondos al diversaj variadoj de la intereza normo. Kun ĉi tiu linio "Monataj pagoj" foriru, kiel ĝi estas. Ĝia unua ĉelo devus enhavi la formulon, kiun ni kalkulis antaŭe. Por pliaj informoj, vi povas aldoni liniojn "Totala prunto" kaj "Tuta Intereso". La kolumno en kiu situas la kalkulo estas farita sen kaplinio.



6. Tuj poste, ni kalkulas la tutan pruntan sumon laŭ nunaj kondiĉoj. Por fari tion, elektu la unuan ĉelon de la vico "Totala prunto" kaj multobligu la enhavon de la ĉeloj "Monata pago" kaj "Prunto termino". Post tio, alklaku la butonon Eniru.



7. Por kalkuli la tutan sumon de intereso laŭ nunaj kondiĉoj, ni simile subtrahas la sumon de la prunto-korpo el la tuta prunto. Por montri la rezulton sur la ekrano, alklaku la butonon Eniru. Tiel, ni ricevas la sumon, kiun ni superpagis repagante la prunton.



8. Nun estas tempo apliki la ilon "Datuma tablo". Ni elektas la tutan tabelon. Post tio, iru al la langeto "Datumoj". Alklaku la butonon sur la rubando "Kio se analizo"kiu situas en la ilo-grupo "Labori kun datumoj" (en Excel 2016, grupo de iloj "Antaŭvido") Poste malgranda menuo malfermiĝas. En ĝi ni elektas pozicion "Datuma tabelo ...".



9. Malgranda fenestro malfermiĝas, nomata "Datuma tablo". Kiel vi povas vidi, ĝi havas du kampojn. Ĉar ni laboras kun unu variablo, ni bezonas nur unu el ili. Ĉar ni ŝanĝas la variablan kolumnon per kolumno, ni uzos la kampon Anstataŭa Kolumvaloro en. Ŝanĝu la kursoron tie, kaj tiam alklaku la ĉelon en la originala datumaro, kiu enhavas la nunan procenton. Post kiam la ĉelaj koordinatoj estas montritaj sur la kampo, alklaku la butonon "Bone".



10. La ilo kalkulas kaj plenigas la tutan tabelan gamon per valoroj, kiuj respondas al malsamaj ebloj por la interezokvalo. Se vi metas la kursoron en iun elementon de ĉi tiu tabela areo, vi povas vidi, ke la formula trinkejo ne montras la kutiman formulon por kalkuli la pagon, sed specialan formulon por nekomprenebla tabelo. Tio estas, estas nun neeble ŝanĝi la valorojn en unuopaj ĉeloj. Vi povas forigi kalkulrezultojn nur kune, kaj ne aparte.

Krome vi povas vidi, ke la monata pago je 12,5% jare akirita rezulte de la aplikado de la serĉa tabelo respondas al la valoro por la sama kvanto de intereso, kiun ni ricevis per la apliko de la funkcio PMT. Ĉi tio denove pruvas la ĝustecon de la kalkulo.

Post analizo de ĉi tiu tabela tabelo, oni devas diri, kiel vi povas vidi, nur kun rapideco de 9,5% jare ni akiras akcepteblan monatan pagon-nivelon (malpli ol 29 000 rublojn).

Leciono: Kalkulado de jarajara pago en Excel

Metodo 2: uzu la ilon kun du variabloj

Kompreneble trovi nuntempe bankojn, kiuj donas pruntojn je 9,5% jare, estas tre malfacila, se ne malebla. Tial ni vidos, kiaj ebloj investi en akceptebla nivelo de monata pago por diversaj kombinaĵoj de aliaj variabloj: la grandeco de la prunto-korpo kaj la prunto-limtempo. En ĉi tiu kazo, la interezokvanto restos senŝanĝa (12,5%). Por solvi ĉi tiun problemon, ilo helpos nin. "Datuma tablo" uzante du variablojn.

1. Ni desegnas novan tablon. Nun en la kolumnaj nomoj estos indikita la prunto-termino (de 2 antaŭe 6 jaroj en monatoj en pliigoj de unu jaro), kaj laŭ linioj - la grandeco de la prunto-korpo 850000 antaŭe 950000 rubloj per pliiĝoj 10000 rubloj). En ĉi tiu kazo, antaŭkondiĉo estas ke la ĉelo en kiu troviĝas la kalkula formulo (en nia kazo PMT), situanta ĉe la limo de nomoj de vico kaj kolumno. Sen ĉi tiu kondiĉo, la ilo ne funkcios uzante du variablojn.



2. Tiam elektu la tutan rezultan tabelon, inkluzive de nomoj de kolumnoj, vicoj kaj ĉelo kun la formulo PMT. Iru al la langeto "Datumoj". Kiel la antaŭa tempo, alklaku la butonon "Kio se analizo", en la ilo-grupo "Labori kun datumoj". En la listo, kiu malfermiĝas, elektu "Datuma tabelo ...".



3. La ilo-fenestro komenciĝas "Datuma tablo". Ĉi-kaze ni bezonas ambaŭ kampojn. En la kampo Anstataŭa Kolumvaloro en indiku la koordinatojn de la ĉelo enhavanta la pruntotempon en la primaraj datumoj. En la kampo "Anstataŭigi valorojn vico post vico en" indiku la adreson de la ĉelo de la komencaj parametroj enhavantaj la valoron de la prunto-korpo. Post kiam ĉiuj datumoj estas enigitaj. Alklaku la butonon "Bone".



4. La programo plenumas la kalkulon kaj plenigas datumojn de la tabelo. En la intersekciĝo de vicoj kaj kolumnoj eblas nun observi, kia estos ĝuste la ĉiumonata pago, kun la responda kvanto de jara interezo kaj la indikita prunto.



5. Kiel vi povas vidi, estas multaj 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 ilojn de bildigo. En nia kazo, ĉi tio estos kondiĉa formatado. Ni elektas ĉiujn valorojn de la tabela gamo, ekskludante la vicojn kaj kolumnajn titolojn.



6. Movu al la langeto "Hejmo" kaj alklaku la ikonon Kondiĉa formatado. Ĝi situas en la ilo-bloko. Stiloj sur la bendo. En la menuo, kiu malfermiĝas, elektu Reguloj pri Selektado de Ĉeloj. En la aldona listo alklaku la pozicion "Malpli ...".



7. Sekvante ĉi tion, la fenestro de kondiĉaj formataj agordoj malfermiĝas. En la maldekstra kampo indiku la valoron malpli ol kiuj estos elektitaj la ĉeloj. Kiel ni memoras, ni kontentas pri la kondiĉo, ke la monata prunto pagos malpli ol 29000 rubloj. Ni enmetas ĉi tiun numeron. En la ĝusta kampo, vi povas elekti la plej altan koloron, kvankam vi povas lasi ĝin defaŭlte. Post kiam ĉiuj bezonataj agordoj estas eniritaj, alklaku la butonon "Bone".



8. Post tio, ĉiuj ĉeloj, kies valoroj respondas al la supra kondiĉo, estos emfazitaj.

Analizinte la tabelon, ni povas eltiri iujn konkludojn. Kiel vi povas vidi, kun la ekzistanta prunto-limtempo (36 monatoj), por investi en la indikita sumo de la monata pago, ni bezonas prunton ne pli ol 860000,00 rublojn, tio estas 40.000 malpli ol antaŭe planita.

Se ni ankoraŭ intencas prunti 900.000 rublojn, tiam la prunto devas esti 4 jaroj (48 monatoj). Nur en ĉi tiu kazo, la monata pago ne superos la establitan limon de 29.000 rubloj.

Tiel, uzante ĉi tiun tabelan tabelon kaj analizante la avantaĝojn kaj kontraŭojn de ĉiu opcio, la prunteprenanto povas fari specifan decidon pri la kondiĉoj de la prunto, elektante la plej taŭgan eblon el ĉiuj eblaj.

Kompreneble la serĉa tabelo povas esti uzata ne nur por kalkuli kreditajn eblojn, sed ankaŭ por solvi multajn aliajn problemojn.

Leciono: Kondiĉa formatado en Excel

Ĝenerale oni devas rimarki, ke la serĉa tabelo estas tre utila kaj relative simpla ilo por determini la rezulton por diversaj kombinaĵoj de variabloj. Uzante kondiĉan formatadon samtempe, krome, vi povas videbligi la ricevitajn informojn.