SI Të ZGJIDHET NJë SISTEM EKUACIONI Në EXCEL

Shumë shpesh, kërkohet llogaritja e rezultatit përfundimtar për kombinime të ndryshme të të dhënave hyrëse. Kështu, përdoruesi do të jetë në gjendje të vlerësojë të gjitha opsionet e mundshme për veprim, zgjidhni ato të cilëve rezultati i ndërveprimit e kënaq atë dhe, së fundi, zgjedh opsionin më optimal. Në Excel, ekziston një mjet i veçantë për këtë detyrë - "Tabela e të Dhënave" ("Tabela e lookup"). Le të zbulojmë se si ta përdorim atë për të kryer skenarët e mësipërm.

Shih gjithashtu: Përzgjedhja e parametrave në Excel

Përdorimi i tabelës së të dhënave

mjet "Tabela e të Dhënave" ai është projektuar për të llogaritur rezultatin me variacione të ndryshme të një ose dy variablave të përcaktuar. Pas llogaritjes, të gjitha opsionet e mundshme do të shfaqen në formën e një tabele, e cila quhet matrica e analizës së faktorëve. "Tabela e të Dhënave" i referohet një grupi mjetesh Analiza "Çfarë-nëse"i cili vendoset në shiritin në tab "Data" në bllok "Duke punuar me të dhënat". Para Excel 2007, ky mjet mbante një emër. "Tabela e lookup"që edhe më saktë pasqyroi thelbin e saj sesa emri aktual.

Tabela e kërkimeve mund të përdoret në shumë raste. Për shembull, një opsion tipik është kur duhet të llogaritni shumën e pagesës mujore të huasë me variacione të ndryshme të periudhës së kreditimit dhe shumës së kredisë, ose periudhën e kreditimit dhe normën e interesit. Ky mjet mund të përdoret gjithashtu edhe kur analizohen modelet e projekteve të investimit.

Por gjithashtu duhet të jeni të vetëdijshëm se përdorimi i tepruar i këtij mjeti mund të çojë në frenim të sistemit, pasi të dhënat rilloganohen vazhdimisht. Prandaj, rekomandohet që të mos përdoret ky mjet në vargje të vogla tabelare për zgjidhjen e problemeve të ngjashme, por për të aplikuar kopjimin e formulave duke përdorur shënuesin e mbushjes.

Zbatimi i justifikuar "Tabelat e të Dhënave" është vetëm në vargjet e mëdha tabelore, kur formulimet e kopjimit mund të marrin një sasi të madhe kohe dhe gjatë procedurës vetë mundësia e gabimeve është rritur. Por edhe në këtë rast, rekomandohet që të çaktivizohet rillogaritja automatike e formulave në rangun e tabelës së kërkimit, me qëllim që të shmanget ngarkesa e panevojshme në sistem.

Dallimi kryesor midis përdorimeve të ndryshme të tabelës së të dhënave është numri i variablave të përfshirë në llogaritjen: një ndryshore ose dy.

Metoda 1: përdoreni mjetin me një ndryshore

Menjëherë le të shqyrtojmë mundësinë kur një tabelë e të dhënave përdoret me një vlerë të ndryshueshme. Merrni shembullin më tipik të huadhënies.

Pra, aktualisht ne ofrojmë kushtet e mëposhtme të kreditimit:

Periudha e huazimit - 3 vjet (36 muaj);
Shuma e kredisë - 900000 rubla;
Norma e interesit - 12.5% në vit.

Pagesat bëhen në fund të periudhës së pagesës (muaj) duke përdorur skemën e pensioneve, domethënë në pjesë të barabarta. Në të njëjtën kohë, në fillim të gjithë periudhës së huasë, pagesat e interesit përbëjnë një pjesë të konsiderueshme të pagesave, por ndërsa trupi zvogëlohet, ulja e pagesave të interesit dhe shuma e ripagimit të vetë trupit rritet. Pagesa totale, siç u përmend më lart, mbetet e pandryshuar.

Është e nevojshme të llogaritet se çfarë do të jetë shuma e pagesës mujore, e cila përfshin ripagimin e trupit të huasë dhe pagesat e interesit. Për këtë, Excel ka një operator PMT.

PMT Ai i përket një grupi të funksioneve financiare dhe detyra e tij është të llogarisë pagesën mujore të huasë të tipit të anuitetit bazuar në shumën e trupit të huasë, afatin e kredisë dhe normën e interesit. Sintaksa për këtë funksion është si më poshtë.

= PMT (norma; nper; ps; bs; tipi)

"Bet" - Argumenti që përcakton normën e interesit të pagesave të kredive. Treguesi është caktuar për periudhën. Periudha jonë e pagesës është një muaj. Prandaj, norma vjetore prej 12.5% duhet të ndahet në numrin e muajve në një vit, domethënë 12.

"NPER" - Argumenti që përcakton numrin e periudhave për të gjithë periudhën e huasë. Në shembullin tonë, periudha është një muaj dhe periudha e huasë është 3 vjet ose 36 muaj. Kështu, numri i periudhave do të jetë në fillim 36.

"PS" - Argumenti që përcakton vlerën aktuale të huasë, domethënë madhësia e organit të kredisë në kohën e lëshimit të tij. Në rastin tonë, kjo shifër është 900,000 rubla.

"BS" - një argument që tregon madhësinë e trupit të kredisë në kohën e pagesës së plotë. Natyrisht, ky tregues do të jetë i barabartë me zero. Ky argument është fakultativ. Nëse e kaloni atë, supozohet se është e barabartë me numrin "0".

"Type" - gjithashtu argument opsional. Ai informon për kohën kur do të bëhet pagesa: në fillim të periudhës (parametri - "1") ose në fund të periudhës (parametri - "0"). Siç e kujtojmë, pagesa jonë bëhet në fund të muajit kalendarik, domethënë, vlera e këtij argumenti do të jetë e barabartë "0". Por, duke pasur parasysh se ky tregues nuk është i detyrueshëm, dhe në mënyrë të paracaktuar, nëse nuk përdoret, vlera supozohet të jetë "0", atëherë në shembullin e specifikuar nuk mund të përdoret aspak.

Pra, ne vazhdojmë në llogaritjen. Zgjidhni qelizën në fletë ku do të shfaqet vlera e llogaritur. Ne klikoni mbi butonin "Funksioni i futjes".

fillon Funksion magjistar. Bëni kalimin në kategorinë "Financial", zgjidhni nga lista emrin "PMT" dhe klikoni mbi butonin "OK".

Pas kësaj, ekziston një aktivizim i dritares së argumenteve të funksionit të mësipërm.
Vendos kursorin në fushë "Bet"pastaj klikoni në qelizën në fletë me vlerën e normës vjetore të interesit. Siç mund ta shihni, koordinatat e tij shfaqen menjëherë në fushë. Por, siç e kujtojmë, kemi nevojë për një normë mujore dhe për këtë arsye e ndajmë rezultatin me 12 (/12).
Në fushë "NPER" në të njëjtën mënyrë, ne hyjmë në koordinatat e qelizave të kreditimit. Në këtë rast, asgjë nuk duhet të ndahet.
Në fushë "Ps" ju duhet të specifikoni koordinatat e qelizës që përmbajnë vlerën e trupit të kredisë. Ne e bëjmë këtë. Ne gjithashtu vendosëm një shenjë përpara koordinatave të shfaqura. "-". Pika është se funksioni PMT në parim, ai jep rezultatin përfundimtar me një shenjë negative, duke marrë në konsideratë pagesën mujore të huasë një humbje. Por për qartësi, ne duhet që tabela e të dhënave të jetë pozitive. Prandaj, ne kemi vënë një shenjë "Minus" para një argumenti funksioni. Siç dihet, shumëfishimi "Minus" në "Minus" përfundimisht jep "Plus".
Në fusha "BS" dhe "Type" Ne nuk i futim të dhënat fare. Ne klikoni mbi butonin "OK".

Pas kësaj, operatori llogarit dhe shfaq në qelinë e paracaktuar rezultatin e pagesës totale mujore - 30108,26 rubla. Por problemi është se huamarrësi është në gjendje të paguajë një maksimum prej 29,000 rubla në muaj, dmth. Ai duhet të gjejë një bankë që ofron kushte me një normë më të ulët interesi, ose zvogëlon trupin e kredisë ose zgjat afatin e kredisë. Llogaritni opsionet e ndryshme për veprim do të na ndihmoni tabelën e kërkimit.

Për të filluar, përdorni tabelën e kërkimit me një ndryshore. Le të shohim se si vlera e pagesës mujore të detyrueshme do të ndryshojë me variacione të ndryshme në normën vjetore, duke filluar nga 9,5% vjetore dhe përfundimtare 12,5% pa me hap 0,5%. Të gjitha kushtet e tjera mbeten të pandryshuara. Vizatoni një sërë tabelash, emrat e kolonave të të cilave do të korrespondojnë me variacionet e ndryshme të normës së interesit. Me këtë linjë "Pagesat mujore" lëni ashtu siç është. Qeliza e tij e parë duhet të përmbajë formulën e llogaritur më herët. Për më shumë informacion, ju mund të shtoni linja "Shuma totale e huasë" dhe "Interesi total". Kolona në të cilën ndodhet llogaritja bëhet pa një kokë.

Tjetra, ne llogarisim shumën totale të huasë sipas kushteve aktuale. Për ta bërë këtë, zgjidhni qelizën e parë të rreshtit. "Shuma totale e huasë" dhe shumohen përmbajtjet e qelizave "Pagesa mujore" dhe "Termi i huasë". Pas kësaj klikoni hyj.

Për të llogaritur shumën e përgjithshme të interesit sipas kushteve aktuale, në mënyrë të ngjashme marrim vlerën e trupit të huasë nga shuma totale e huasë. Për të shfaqur rezultatin në ekran klikoni mbi butonin. hyj. Kështu, marrim shumën që ne paguajmë gjatë kthimit të kredisë.

Tani është koha për të aplikuar mjetin. "Tabela e të Dhënave". Përzgjidhni të gjithë tabelën e tabelës, përveç emrave të rreshtit. Pas kësaj shkoni në tab "Data". Klikoni mbi butonin e shiritit Analiza "Çfarë-nëse"i cili është vendosur në një grup mjetesh "Duke punuar me të dhënat" (në Excel 2016, një grup mjetesh "Parashikimi"). Pastaj hapet një meny të vogël. Në të zgjedhim pozicionin "Tabela e të dhënave ...".

Hapet një dritare e vogël, e cila quhet "Tabela e të Dhënave". Siç mund ta shihni, ajo ka dy fusha. Meqë ne punojmë me një ndryshore, ne kemi nevojë vetëm për një prej tyre. Që nga ndryshimet tona të ndryshueshme ndodhin në kolona, ne do të përdorim fushën "Vlerat zëvendësuese sipas kolonave në". Ne vendosim kursorin atje, dhe pastaj klikoni në qelizën në grupin fillestar të të dhënave, i cili përmban vlerën aktuale të përqindjes. Pasi koordinatat e qelizës të shfaqen në fushë, klikoni mbi butonin "OK".

Mjet llogarit dhe plotëson të gjithë gamën e tabelave me vlera që korrespondojnë me opsionet e ndryshme të normës së interesit. Nëse vendosni kursorin në çdo element të kësaj tabele, mund të shihni se shiritat e formulës nuk paraqesin një formulë të rregullt të llogaritjes së pagesave, por një formulë të veçantë të një grupi jo të thyer. Kjo është, nuk është më e mundur për të ndryshuar vlerat në qelizat individuale. Fshij rezultatet e llogaritjes mund të jenë të gjitha së bashku, dhe jo veç e veç.

Përveç kësaj, mund të vërehet se vlera e pagesës mujore në 12.5% në vit, e fituar duke aplikuar tabelën e kërkimit, korrespondon me vlerën me të njëjtën normë interesi që kemi marrë duke aplikuar funksionin PMT. Kjo dëshmon edhe një herë korrektësinë e llogaritjes.

Pas analizimit të këtij grupi tabelar, duhet të thuhet se, siç e shohim, vetëm me një normë prej 9.5% në vit, merret niveli i pagesës mujore të pranueshme (më pak se 29.000 rubla).

Mësimi: Llogaritja e pagesës së anuitetit në Excel

Metoda 2: Përdorni një mjet me dy variabla

Natyrisht, është shumë e vështirë, nëse është fare reale, të gjejmë bankat që japin kredi me 9.5% në vit. Prandaj, le të shohim se cilat opsione duhet të investojnë në një nivel të pranueshëm të pagesës mujore për kombinime të ndryshme të variablave të tjerë: madhësia e trupit të kredisë dhe periudha e huasë. Në të njëjtën kohë, norma e interesit do të mbetet e pandryshuar (12.5%). Mjeti do të na ndihmojë me këtë detyrë. "Tabela e të Dhënave" duke përdorur dy variabla.

Vizatoni një tabelë të re tavoline. Tani termi i kredive do të tregohet në emrat e kolonës (nga 2 deri në 6 vite në muaj në hapat e një viti), dhe në rreshtat - madhësia e trupit të kredisë (nga 850000 deri në 950000 rubla në increments 10000 rubles). Në këtë rast, është e domosdoshme që qeliza në të cilën gjendet formula e llogaritjes (në rastin tonë PMT), që ndodhet në kufirin e emrave të rreshtit dhe kolonave. Pa këtë kusht, mjet nuk do të funksionojë kur përdorin dy variabla.

Pastaj zgjidhni të gjithë gamën e tabelës që rezulton, duke përfshirë emrat e kolonave, rreshtave dhe qelizës me formulën PMT. Shko te tab "Data". Si në kohën e mëparshme, klikoni mbi butonin. Analiza "Çfarë-nëse"në një grup mjetesh "Duke punuar me të dhënat". Në listën që hapet, zgjidhni artikullin "Tabela e të dhënave ...".

Dritarja e mjeteve fillon. "Tabela e të Dhënave". Në këtë rast, na nevojiten të dyja fushat. Në fushë "Vlerat zëvendësuese sipas kolonave në" ne specifikojmë koordinatat e qelizës që përmbajnë termin e kredisë në të dhënat primare. Në fushë "Vlerat zëvendësuese nga rreshtat në" specifikoni adresën e qelizës së parametrave fillestarë që përmbajnë vlerën e trupit të kredisë Pas të gjitha të dhënave është futur. Ne klikoni mbi butonin "OK".

Programi kryen llogaritjen dhe mbush gamen e tabelave me të dhëna. Në kryqëzimin e rreshtave dhe kolonave, tani është e mundur të vëzhgohet se sa saktësisht do të jetë pagesa mujore, me një shumë korresponduese të interesit vjetor dhe një periudhe të caktuar kreditimi.

Siç mund ta shihni, mjaft vlerë. Për të zgjidhur probleme të tjera mund të ketë edhe më shumë. Prandaj, në mënyrë që rezultatet më vizuale dhe menjëherë të përcaktojnë cilat vlera nuk i plotësojnë kushtet e dhëna, mund të përdorni vegla vizualizimi. Në rastin tonë do të jetë formësimi i kushtëzuar. Zgjidh të gjitha vlerat e gamës së tabelës, duke përjashtuar headers rresht dhe kolona.

Lëviz në tab "Home" dhe klikoni mbi ikonën "Formatimi i kushtëzuar". Ajo është e vendosur në kutinë e veglave. "Styles" në kasetë. Në menynë që hapet, zgjidhni artikullin "Rregullat për përzgjedhjen e qelizave". Në listën shtesë klikoni në pozicionin "Më pak ...".

Pas kësaj, hapet dritarja e vendosjes së formatimit të kushtëzuar. Në fushën e majtë ne specifikojmë vlerën, më pak se sa do të zgjidhen qelizat. Siç e kujtojmë, ne jemi të kënaqur me gjendjen nën të cilën pagesa mujore në kredi do të jetë më e vogël 29000 rubla. Futni këtë numër. Në fushën e djathtë mund të zgjidhni ngjyrën e përzgjedhjes, edhe pse mund ta lini atë me parazgjedhje. Pas të gjitha cilësimeve të kërkuara janë futur, klikoni mbi butonin. "OK".

Pas kësaj, të gjitha qelizat, vlerat e të cilave korrespondojnë me gjendjen e mësipërme, do të theksohen me ngjyra.

Pas analizimit të tabelës së tabelës, mund të nxirrni disa përfundime. Siç mund ta shihni, me periudhën aktuale të kredisë (36 muaj), për të investuar në shumën e lartpërmendur të një pagese mujore, ne duhet të marrim një hua që nuk kalon 8,600,000.00 rubla, që është 40,000 më pak se planifikuar fillimisht.

Nëse ende kemi ndërmend të marrim një hua në shumën prej 900,000 rubla, atëherë afati i kredisë duhet të jetë 4 vjet (48 muaj). Vetëm në këtë rast, shuma e pagesës mujore nuk do të tejkalojë kufirin e përcaktuar prej 29,000 rubla.

Kështu, duke përfituar nga ky grup tabelar dhe duke analizuar pro dhe kundrat e secilit opsion, huamarrësi mund të marrë një vendim të veçantë mbi kushtet e kreditimit, duke zgjedhur opsionin që i përshtatet më së miri nevojave të tij.

Sigurisht, tabela e lookup mund të përdoret jo vetëm për të llogaritur opsionet e kredisë, por edhe për të zgjidhur shumë probleme të tjera.

Mësimi: Formulimi i kushtëzuar në Excel

Në përgjithësi, duhet të theksohet se tabela e kërkimeve është një mjet shumë i dobishëm dhe relativisht i thjeshtë për përcaktimin e rezultateve të kombinimeve të ndryshme të variablave. Duke aplikuar formatimin e kushtëzuar së bashku me të, përveç kësaj, mund të vizualizoni informacionin e marrë.