Tabela e të dhënave në Microsoft Excel

Shpesh, duhet të llogaritni rezultatin 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 veprime, të zgjedhë ata, rezultatet e ndërveprimit të të cilëve e kënaqin atë, dhe, më në fund, të zgjedhë opsionin më optimal. Në Excel, për të kryer këtë detyrë, ekziston një mjet i veçantë - "Tabela e të dhënave" (Tabela e Zëvendësimit). Le të zbulojmë se si ta përdorim atë për të përfunduar skenarët e mësipërm.

Lexoni gjithashtu: Zgjedhja e parametrave në Excel

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

mjet "Tabela e të dhënave" Ka për qëllim të llogarisë rezultatin për ndryshime të ndryshme të një ose dy ndryshoreve të përcaktuara. Pas llogaritjes, të gjitha opsionet e mundshme 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 "Po sikur analiza", e cila vendoset në shiritin në skedë "Data" në bllok "Puna me të dhëna". Para Excel 2007, ky mjet u thirr Tabela e Zëvendësimit, e cila edhe më saktë pasqyronte thelbin e saj sesa emri aktual.

Tabela e kërkimit mund të përdoret në shumë raste. Për shembull, një opsion tipik është kur ju duhet të llogaritni shumën e pagesës mujore të kredisë për ndryshime të ndryshme të periudhës së kreditimit dhe shumës së kredisë, ose periudhës së kreditimit dhe normës së interesit. Gjithashtu, ky mjet mund të përdoret në analizën e modeleve të projekteve të investimeve.

Por gjithashtu duhet të jeni të vetëdijshëm që përdorimi i tepërt i këtij mjeti mund të çojë në frenimin e sistemit, pasi të dhënat rrëfehen vazhdimisht. Prandaj, rekomandohet në grupe të tabelave të vogla për të zgjidhur probleme të ngjashme për të mos përdorur këtë mjet, por për të përdorur kopjimin formula duke përdorur shënuesin e mbushjes.

Aplikim i justifikuar "Tabela e të dhënave" është vetëm në varg tabelash të mëdha, kur kopjimi i formulave mund të marrë shumë kohë, dhe gjatë vetë procedurës probabiliteti për të bërë gabime rritet. Por në këtë rast, rekomandohet të çaktivizoni rillogaritjen automatike të formulave në rangun e tabelës së zëvendësimit, në mënyrë që të shmangeni ngarkesa e tepërt 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ërdorni mjetin me një ndryshore

Menjëherë le të shohim opsionin kur tabela e të dhënave përdoret me një vlerë të ndryshueshme. Merrni shembullin më tipik të huadhënies.

Pra, aktualisht na ofrohen kushtet e mëposhtme të kredisë:

• Afati i huasë - 3 vjet (36 muaj);
• Shuma e kredisë - 900,000 rubla;
• Norma e interesit - 12.5% ​​në vit.

Pagesat ndodhin në fund të periudhës së pagesës (muaji) sipas skemës së pensionit, pra në aksione të barabarta. Në të njëjtën kohë, në fillim të gjithë afatit të kredisë, një pjesë e konsiderueshme e pagesave janë pagesa e interesit, por ndërsa organi zvogëlohet, pagesa e interesit zvogëlohet, dhe rritet shuma e ripagimit të vetë trupit. Pagesa totale, siç u përmend më lart, mbetet e pandryshuar.

Shtë e nevojshme të llogaritet se cila do të jetë shuma e pagesës mujore, përfshirë ripagimin e trupit të kredisë dhe pagesat e interesit. Për këtë, Excel ka një operator PMT.

PMT i përket grupit të funksioneve financiare dhe detyra e tij është të llogarisë pagesën mujore të llojit të pensionit mujor, bazuar në shumën e trupit të kredisë, afatin e huasë dhe normën e interesit. Sintaksa e këtij funksioni është paraqitur si

= PLT (norma; nper; ps; bs; lloji)

"Bet" - një argument që përcakton normën e interesit të pagesave të kredisë. Treguesi është caktuar për periudhën. Periudha jonë e pagimit është e barabartë me një muaj. Prandaj, shkalla vjetore prej 12.5% ​​duhet të ndahet me numrin e muajve në një vit, domethënë 12.

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

"PS" - një argument që përcakton vlerën e tanishme të kredisë, domethënë është madhësia e trupit të kredisë në kohën e lëshimit të saj. 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ë opsional. Nëse e kaloni atë, supozohet se është e barabartë me numrin "0".

"Type" - gjithashtu një argument opsional. Ai njofton se kur do të bëhet saktë 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ë me "0". Por, duke pasur parasysh faktin se ky tregues nuk është i detyrueshëm, dhe si parazgjedhje, nëse nuk përdoret, vlera nënkuptohet të jetë e barabartë "0", atëherë në shembullin e treguar mund të lihet krejt.

1. Pra, ne vazhdojmë me llogaritjen. Zgjidhni një qelizë në fletë ku do të shfaqet vlera e llogaritur. Klikoni në butonin "Vendos funksionin".



2. Fillon lart Wizard Feature. Ne kalojmë në kategori "Financial", zgjidhni emrin nga lista "PMT" dhe klikoni në butonin "OK".



3. Pas kësaj, aktivizohet dritarja e argumenteve të funksionit të mësipërm.

   Vendoseni kursorin në fushë "Bet", pas së cilës klikojmë 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ç kujtojmë, na duhet 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ë afatit të kredisë. Në këtë rast, nuk keni nevojë të ndani asgjë.

   Në fushë "Ps" ju duhet të specifikoni koordinatat e qelizës që përmbajnë vlerën e trupit të kredisë. Ne e bëjmë atë Ne vendosëm gjithashtu një shenjë përpara koordinatave të shfaqura "-". Fakti është se funksioni PMT si parazgjedhje ai jep rezultatin përfundimtar me një shenjë negative, duke marrë parasysh me të drejtë humbjen e pagesës mujore të huasë. Por për qartësinë e zbatimit të tabelës së të dhënave, na duhet që ky numër të jetë pozitiv. Prandaj, ne vendosim një shenjë "Minus" para njërit prej argumenteve të funksionit. Dihet shumzimi "Minus" në "Minus" në fund jep "Plus".

   Në fusha "BS" dhe "Type" të dhënat nuk janë futur fare. Klikoni në butonin "OK".



4. Pas kësaj, operatori llogarit dhe tregon rezultatin e pagesës totale mujore në një qelizë të paracaktuar - 30108,26 rubla. Por problemi është se huamarrësi është në gjendje të paguajë një maksimum 29,000 rubla në muaj, domethënë, ai ose duhet të gjejë një bankë që ofron kushte me një normë më të ulët interesi, ose të zvogëlojë trupin e kredisë, ose të rrisë afatin e kredisë. Tabela e kërkimit do të na ndihmojë të kuptojmë opsionet e ndryshme.



5. Së pari, përdorni tabelën e kërkimit me një ndryshore. Le të shohim se si shuma e pagesës mujore të detyrueshme do të ndryshojë me ndryshime të ndryshme të normës vjetore, duke filluar nga 9,5% në vit dhe mbarim 12,5% në vit në rritje 0,5%. Të gjitha kushtet e tjera kanë mbetur të pandryshuara. Ne tërheqim një gamë tabelash, emrat e kolonave të së cilës do të korrespondojnë me ndryshime të ndryshme të normës së interesit. Me këtë linjë "Pagesat Mujore" largohu ashtu siç është. Qeliza e parë e saj duhet të përmbajë formulën që kemi llogaritur më herët. Për më shumë informacion, mund të shtoni linja "Shuma totale e kredisë" dhe "Totali i interesit". Kolona në të cilën është vendosur llogaritja bëhet pa një kokë.



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



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



8. Tani është koha për të aplikuar mjetin "Tabela e të dhënave". Ne zgjedhim të gjithë grupin e tabelës, përveç emrave të rreshtave. Pas kësaj, shkoni te skedari "Data". Klikoni butonin në shirit "Po sikur analiza"i cili ndodhet në grupin e mjeteve "Puna me të dhëna" (në Excel 2016, një grup mjetesh "Parashikimi"). Pastaj hapet një menu e vogël. Në të zgjedhim një pozicion "Tabela e të dhënave ...".



9. Hapet një dritare e vogël, e cila quhet "Tabela e të dhënave". Siç mund ta shihni, ajo ka dy fusha. Meqenëse ne punojmë me një ndryshore, na duhet vetëm njëra prej tyre. Meqenëse ndryshojmë kolonën e ndryshueshme sipas kolonës, do të përdorim fushën Zëvendësoni vlerat e kolonës në. Vendoseni kursorin atje dhe pastaj klikoni në qelizë në të dhënat origjinale që përmban përqindjen aktuale. Pasi të shfaqen koordinatat e qelizave në fushë, klikoni në butonin "OK".



10. Mjet llogarit dhe mbush të gjithë gamën tabelare me vlera që korrespondojnë me opsione të ndryshme për normën e interesit. Nëse vendosni kursorin në ndonjë element të kësaj zone të tabelës, mund të shihni që shiriti i formulës nuk tregon formulën e zakonshme për llogaritjen e pagesës, por një formulë të veçantë për një grup të pandashëm. Kjo është, tani është e pamundur të ndryshosh vlerat në qelizat individuale. Ju mund të fshini rezultatet e llogaritjes vetëm të gjitha së bashku, dhe jo veçmas.

Për më tepër, mund të shihni që pagesa mujore në 12.5% ​​në vit të marrë si rezultat i aplikimit të tabelës së kërkimit korrespondon me vlerën për të njëjtën sasi interesi që kemi marrë duke aplikuar funksionin PMT. Kjo vërteton edhe një herë korrektësinë e llogaritjes.

Pas analizimit të këtij grupi të tabelës, duhet të thuhet se, siç mund ta shihni, vetëm me një normë prej 9.5% në vit kemi një nivel të pranueshëm pagese mujore (më pak se 29,000 rubla).

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

Metoda 2: përdorni mjetin me dy ndryshore

Sigurisht, për të gjetur në bankat e tanishme që lëshojnë kredi me 9.5% në vit është shumë e vështirë, nëse jo e pamundur. Prandaj, ne do të shohim se cilat mundësi ekzistojnë për të investuar 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 afati i kredisë. Në këtë rast, norma e interesit do të mbetet e pandryshuar (12.5%). Në zgjidhjen e këtij problemi, një mjet do të na ndihmojë. "Tabela e të dhënave" duke përdorur dy ndryshore.

1. Ne tërheqim një grup të ri tabelash. Tani në emrat e kolonave do të tregohet afati i huasë (nga 2 në 6 vjet në muaj në rritje prej një viti), dhe në rreshta - madhësia e trupit të kredisë (nga 850000 në 950000 rubla në shtesa 10000 rubles). Në këtë rast, një parakusht është që qeliza në të cilën ndodhet formula e llogaritjes (në rastin tonë PMT), të vendosura në kufirin e emrave të rreshtave dhe kolonave. Pa këtë gjendje, mjeti nuk do të funksionojë kur përdorni dy ndryshore.



2. Pastaj zgjidhni të gjithë gamën e tabelës që rezulton, duke përfshirë emrat e kolonave, rreshtave dhe një qelizë me formulën PMT. Shko te skeda "Data". Si herën e kaluar, klikoni në butonin "Po sikur analiza", në grupin e mjeteve "Puna me të dhëna". Në listën që hapet, zgjidhni "Tabela e të dhënave ...".



3. Fillon dritarja e mjetit "Tabela e të dhënave". Në këtë rast, ne kemi nevojë për të dy fushat. Në fushë Zëvendësoni vlerat e kolonës në tregoni koordinatat e qelizës që përmbajnë termin e kredisë në të dhënat parësore. Në fushë "Zëvendësoni vlerat rresht pas rreshtit në" tregoni adresën e qelizës së parametrave fillestarë që përmbajnë vlerën e trupit të kredisë. Pasi të jenë futur të gjitha të dhënat. Klikoni në butonin "OK".



4. Programi kryen llogaritjen dhe mbush gamën e tabelës me të dhëna. Në kryqëzimin e rreshtave dhe kolonave tani është e mundur të vëzhgoni se çfarë do të jetë saktësisht pagesa mujore, me shumën përkatëse të interesit vjetor dhe afatin e treguar të kredisë.



5. Siç mund ta shihni, ka shumë vlera. Për të zgjidhur probleme të tjera, mund të ketë edhe më shumë. Prandaj, për ta bërë rezultatin e rezultateve më vizuale dhe menjëherë të përcaktoni se cilat vlera nuk plotësojnë gjendjen e dhënë, mund të përdorni mjete vizualizimi. Në rastin tonë, ky do të jetë formatim i kushtëzuar. Ne zgjedhim të gjitha vlerat e diapazonit të tabelës, duke përjashtuar titujt e rreshtit dhe kolonës.



6. Kaloni në skedë "Home" dhe klikoni në ikonën Formatimi i kushtëzuar. Ndodhet në bllokun e mjeteve. "Styles" në shirit. Në menunë që hapet, zgjidhni Rregullat e zgjedhjes së qelizave. Në listën shtesë, klikoni në pozicionin "Më pak ...".



7. Pas kësaj, hapet dritarja e cilësimeve të formatimit të kushtëzuar. Në fushën e majtë tregoni vlerën më pak se sa do të zgjidhen qelizat. Siç kujtojmë, ne jemi të kënaqur me kushtin që pagesa mujore e huasë do të jetë më pak se 29000 rubla. Ne e shkruajmë këtë numër. Në fushën e duhur, ju mund të zgjidhni ngjyrën e theksuar, megjithëse mund ta lini atë si parazgjedhje. Pasi të jenë futur të gjitha cilësimet e kërkuara, klikoni në butonin "OK".



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

Pasi të kemi analizuar grupin e tabelës, mund të nxjerrim disa përfundime. Siç mund ta shihni, me afatin ekzistues të kredisë (36 muaj), për të investuar në shumën e treguar më lart të pagesës mujore, duhet të marrim një kredi që nuk tejkalon 860000.00 rubla, d.m.th., 40,000 më pak se sa ishte planifikuar fillimisht.

Nëse ne ende synojmë të marrim një kredi prej 900,000 rubla, atëherë afati i huasë duhet të jetë 4 vjet (48 muaj). Vetëm në këtë rast, pagesa mujore nuk do të kalojë kufirin e vendosur prej 29,000 rubla.

Kështu, duke përdorur këtë grup tabelash dhe duke analizuar të mirat dhe të këqijat e secilit opsion, huamarrësi mund të marrë një vendim specifik për kushtet e kredisë, duke zgjedhur opsionin më të përshtatshëm nga të gjitha të mundshmet.

Sigurisht, tabela e kërkimit 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: Formatimi i kushtëzuar në Excel

Në përgjithësi, duhet të theksohet se tabela e kërkimit është një mjet shumë i dobishëm dhe relativisht i thjeshtë për përcaktimin e rezultatit për kombinime të ndryshme të variablave. Duke përdorur formatimin e kushteve në të njëjtën kohë, përveç kësaj, ju mund të vizualizoni informacionin e marrë.