Microsoft Excel- ում հավասարման համակարգի լուծում

Շատ հաճախ, անհրաժեշտ է հաշվարկել վերջնական արդյունքը մուտքագրման տվյալների տարբեր զուգորդումների համար: Այսպիսով, օգտվողը կկարողանա գնահատել գործողությունների բոլոր հնարավոր տարբերակները, ընտրեք նրանց, որոնց փոխազդեցության արդյունքը բավարարում է նրան եւ, վերջապես, ընտրեք առավել օպտիմալ տարբերակ: Excel- ում այս գործիքի համար կա հատուկ գործիք ` "Տվյալների աղյուսակ" («Փնտրող սեղան»): Եկեք պարզենք, թե ինչպես օգտագործել այն վերը նշված սցենարները կատարելու համար:

Տես նաեւ. Excel- ում պարամետր ընտրություն

Օգտագործելով տվյալների աղյուսակը

Գործիք "Տվյալների աղյուսակ" այն նախատեսված է հաշվարկի արդյունքը որոշակի փոփոխականների տարբեր տատանումներով: Հաշվարկից հետո բոլոր հնարավոր տարբերակները կհայտնվեն սեղանի ձեւով, որը կոչվում է գործոնների վերլուծության մատրից: "Տվյալների աղյուսակ" վերաբերում է մի շարք գործիքների «Ինչ-որ» վերլուծությունորը տեղադրված է ժապավենի վրա ներդիրում «Տվյալների» բլոկում "Աշխատանքային տվյալների հետ". Նախքան Excel 2007, այս գործիքը կրում է անուն: «Փնտրող սեղան»որ ավելի ճշգրիտ կերպով արտացոլեց իր էությունը, քան ներկայիս անունը:

Փնտրման սեղանը կարող է օգտագործվել շատ դեպքերում: Օրինակ, սովորական տարբերակն այն է, երբ դուք պետք է հաշվարկեք ամսական վարկի գումարի չափը վարկավորման ժամանակահատվածի տարբեր տատանումներով եւ վարկի գումարի կամ վարկավորման ժամկետն ու տոկոսադրույքը: Այս գործիքը կարող է օգտագործվել նաեւ ներդրումային նախագծային մոդելների վերլուծության ժամանակ:

Բայց պետք է նաեւ տեղյակ լինեք, որ այս գործիքի չափից ավելի օգտագործումը կարող է հանգեցնել համակարգի արգելակման, քանի որ տվյալները մշտապես վերահաշվարկվում են: Հետեւաբար, խորհուրդ է տրվում չօգտագործել այս գործիքը փոքր աղյուսակային տողերում, նման խնդիրների լուծման համար, այլ կիրառել ձեւաթղթերի պատճենումը, օգտագործելով լրացնել նշիչը:

Հիմնավորված դիմում "Տվյալների սեղաններ" ընդամենը մեծ աղյուսակային տատանվում է, երբ բանաձեւերի պատճենումը կարող է մեծ քանակությամբ ժամանակ անցկացնել, եւ ընթացակարգի ընթացքում ավելանում է սխալների հավանականությունը: Սակայն նույնիսկ այս դեպքում խորհուրդ է տրվում անջատել համակարգերի վրա ավելորդ բեռից խուսափելու համար որոնողական աղյուսակի շրջանում բանաձեւերի ավտոմատ վերահաշվարկը:

Տվյալների աղյուսակի տարբեր օգտագործման հիմնական տարբերությունը հաշվարկման մեջ ներգրավված փոփոխականների քանակը. Մեկ փոփոխական կամ երկու:

Մեթոդ 1 `գործիքը մեկ փոփոխականով օգտագործեք

Անմիջապես դիտարկենք տարբերակը, երբ տվյալների աղյուսակը օգտագործվում է մեկ փոփոխական արժեքով: Վերցրեք վարկավորման առավել բնորոշ օրինակ:

Այսպիսով, ներկայումս մեզ առաջարկվում են հետեւյալ վարկային պայմանները.

• Վարկի ժամկետ `3 տարի (36 ամիս);
• Վարկի գումարը `900000 ռուբլի;
• Տոկոսադրույքը `տարեկան 12.5%:

Վճարումները կատարվում են վճարման ժամկետի (ամսվա) ավարտին `օգտագործելով անուիտետի սխեման, այսինքն` հավասարաչափ: Միեւնույն ժամանակ, ամբողջ վարկի շրջանի սկզբում տոկոսային վճարումները կազմում են վճարումների զգալի մասը, բայց քանի որ մարմինը նահանջում է, տոկոսագումարների կրճատում է, եւ աճում է մարմնի մարման չափը: Ընդհանուր վճարը, ինչպես նշված է վերեւում, մնում է անփոփոխ:

Անհրաժեշտ է հաշվարկել, թե ինչ կլինի ամսական վճարման գումարը, որը ներառում է վարկի մարման եւ տոկոսների վճարումները: Դրա համար Excel- ն ունի օպերատոր PMT- ը.

PMT- ը Այն վերաբերում է ֆինանսական գործառույթների մի խումբին եւ դրա խնդիրն է հաշվարկել վարկի մարմնի գումարի, վարկի ժամկետի եւ տոկոսադրույքի հիման վրա հաշվարկելու անուիտետի ամսական վարկի վճարումը: Այս գործառույթի սինթետիկան հետեւյալն է.

= PMT (փոխարժեք; nper; ps; bs; տիպ)

«Խաղադրույք» - Վարկային վճարումների տոկոսադրույքները որոշող փաստարկ: Ցուցանիշը սահմանվում է ժամանակահատվածի համար: Մեր վճարման ժամկետը մեկ ամիս է: Հետեւաբար, 12.5% ​​տարեկան տոկոսադրույքը պետք է բաժանվի տարվա մեջ, այսինքն, 12:

"Կպեր" - Վարկի ողջ ժամանակահատվածի համար սահմանվող փաստարկը: Մեր օրինակում, ժամկետը մեկ ամիս է, իսկ վարկի ժամկետը `3 տարի կամ 36 ամիս: Այսպիսով, ժամկետների թիվը 36 տարեկան է:

"PS" - վարկի ներկա արժեքը որոշող փաստարկը, այսինքն `վարկի մարմնի չափը դրա թողարկման պահին: Մեր դեպքում այս թիվը 900,000 ռուբլի է:

"BS" - Վարկային մարմնի չափը լրիվ վճարման պահին նշելով փաստարկ: Բնականաբար, այս ցուցանիշը հավասար կլինի զրոյի: Այս փաստարկը պարտադիր է: Եթե ​​դուք անցնեք այն, ապա ենթադրվում է, որ այն հավասար է «0»:

"Տեսակ" - նաեւ լրացուցիչ փաստարկ: Նա տեղեկացնում է այն մասին, թե երբ է վճարումը կատարվելու: ժամանակի սկզբում (պարամետր - "1") կամ ժամկետի վերջում (պարամետր - "0"): Ինչպես հիշում ենք, մեր վճարումը կատարվում է օրացուցային ամսվա վերջում, այսինքն, այս փաստարկի արժեքը հավասար կլինի "0". Սակայն, հաշվի առնելով, որ այդ ցուցանիշը պարտադիր չէ, եւ, որպես լռելյայն, եթե այն չի օգտագործվում, արժեքը ենթադրվում է "0", ապա նշված օրինակով այն չի կարող օգտագործվել ընդհանրապես:

1. Այսպիսով, մենք հաշվարկում ենք: Ընտրեք թերթիկի բջիջը, որտեղ կհայտնվի հաշվարկված արժեքը: Մենք սեղմեք կոճակը "Տեղադրեք գործառույթը".



2. Սկսվում է Function Wizard. Կատարեք անցում դեպի կատեգորիա «Ֆինանսական», ցանկից ընտրեք անունը "PLT" եւ սեղմեք կոճակը "OK".



3. Դրանից հետո գոյություն ունի վերը նշված գործառույթի փաստարկների պատուհանների ակտիվացում:

   Կպցնել կուրսորը դաշտում «Խաղադրույք»ապա սեղմեք թերթի բջիջը, տարեկան տոկոսադրույքի արժեքով: Ինչպես տեսնում եք, նրա կոորդինատները անմիջապես ցուցադրվում են դաշտում: Սակայն, ինչպես հիշում ենք, մենք պետք է ամսական դրույքաչափ, ուստի արդյունքը բաժանենք 12-ով (/12).

   Դաշտում "Կպեր" նույն կերպ, մենք մտնում ենք վարկային տերմինների բջիջների կոորդինատները: Այս դեպքում ոչինչ չի կարելի բաժանել:

   Դաշտում «Սեբ» դուք պետք է նշեք վարկի մարմնի արժեքը պարունակող բջիջի կոորդինատները: Մենք դա անում ենք: Մենք նաեւ ցուցադրեցինք ցուցադրված կոորդինատների առջեւ: "-". Բանն այն է, որ գործառույթը PMT- ը լռելյայնորեն, վերջնական արդյունքը տալիս է բացասական նշան, արդարացիորեն հաշվի առնելով ամսական վարկի վճարումը կորուստ: Սակայն հստակության համար անհրաժեշտ է տվյալների աղյուսակը դրական լինել: Հետեւաբար, մենք նշում ենք "մինուս" նախքան ֆունկցիայի փաստարկներից մեկը: Ինչպես հայտնի է, բազմապատկումը "մինուս" ին "մինուս" վերջապես տալիս է գումարած.

   Դաշտերում «Բեսթս» եւ "Տեսակ" Մենք ընդհանրապես տվյալներ չենք մտնում: Մենք սեղմեք կոճակը "OK".



4. Դրանից հետո օպերատորը հաշվարկում եւ ցուցադրում է նախնական նշանակված բջիջում ընդհանուր ամսական վճարի արդյունքը, 30108,26 ռուբլի: Սակայն խնդիրն այն է, որ վարկառուն կարող է վճարել ամսական առավելագույնը 29,000 ռուբլի, այսինքն, պետք է գտնի բանկի առաջարկի պայմանները ավելի ցածր տոկոսադրույքով, կամ նվազեցնի վարկային մարմնի կամ վարկի ժամկետը երկարաձգելու համար: Հաշվարկել գործողությունների տարբեր տարբերակներ, որոնք կօգնեն մեզ փնտրել աղյուսակը:



5. Սկսելու համար օգտագործեք lookup աղյուսակը մեկ փոփոխականով: Եկեք տեսնենք, թե ինչպես է պարտադիր ամսական վճարման արժեքը տարբեր տատանումներով տարբեր տատանումներով, տարեկան կտրվածքով 9,5% տարեկան եւ ավարտին 12,5% քայլով 0,5%. Բոլոր մյուս պայմանները մնացել են անփոփոխ: Սեղանի աղյուսակը նկարեք, որի սյունակների անունները կհամապատասխանեն տոկոսադրույքի տարբեր տատանումներին: Այս գծով "Ամսական վճարումներ" հեռանալ այնպես, ինչպես կա: Նրա առաջին բջիջը պետք է պարունակի նախկինում հաշվարկված բանաձեւը: Լրացուցիչ տեղեկությունների համար կարող եք ավելացնել գծեր "Ընդհանուր վարկի գումարը" եւ «Ընդհանուր հետաքրքրություն». Սյունակը, որի հաշվարկը գտնվում է, կատարվում է առանց վերնագրի:



6. Դրանից հետո մենք հաշվարկում ենք վարկի ընդհանուր գումարը `ներկայիս պայմաններում: Դա անելու համար ընտրեք տողի առաջին բջիջը: "Ընդհանուր վարկի գումարը" եւ բազմապատկել բջիջների բովանդակությունը "Ամսական վճարում" եւ "Վարկի ժամկետը". Այնուհետեւ կտտացրեք Մուտքագրեք.



7. Ընթացիկ պայմաններում տոկոսների ընդհանուր գումարը հաշվարկելու համար, նույն ձեւով, մենք վարկի ընդհանուր գումարից դուրս ենք բերում վարկի մարմնի արժեքը: Էկրանի վրա արդյունքը ցուցադրելու համար սեղմեք կոճակը: Մուտքագրեք. Այսպիսով, մենք ստանում ենք այն գումարը, որ մենք գերազանցում ենք վարկը վերադարձնելու ժամանակ:



8. Հիմա ժամանակն է կիրառել գործիքը: "Տվյալների աղյուսակ". Ընտրեք ամբողջ սեղանի պարունակությունը, բացառությամբ տողի անունների: Դրանից հետո անցեք էջանշանը «Տվյալների». Սեղմիր ժապավենի վրա կոճակը «Ինչ-որ» վերլուծությունորը տեղադրված է մի շարք գործիքների մեջ "Աշխատանքային տվյալների հետ" (Excel 2016- ում, գործիքների մի խումբ "Կանխատեսում"): Այնուհետեւ բացվում է փոքր մենյու: Այնտեղ ընտրում ենք դիրքորոշումը "Տվյալների աղյուսակ ...".



9. Մի փոքրիկ պատուհան է բացվում, որը կոչվում է "Տվյալների աղյուսակ". Ինչպես տեսնում եք, այն ունի երկու դաշտ: Քանի որ մենք աշխատում ենք մեկ փոփոխականի հետ, մեզանից միայն մեկին պետք է: Քանի որ փոփոխական փոփոխությունները տեղի են ունենում սյունակներում, մենք կօգտագործենք դաշտը "Փոխարինեք արժեքները սյունակներով". Այնտեղ տեղադրեք կուրսորը, այնուհետեւ սեղմեք նախնական տվյալների հավաքածուի մեջ, որը պարունակում է տոկոսների ներկա արժեքը: Բջջի կոորդինատները ցուցադրվում են դաշտում, սեղմեք կոճակը "OK".



10. Գործիքը հաշվարկում եւ լրացնում է ամբողջ սեղանի տիրույթը արժեքների հետ, որոնք համապատասխանում են տարբեր տոկոսադրույքի տարբերակներին: Եթե ​​դուք այս սանդղակի ցանկացած տարրում տեղադրեք կուրսորը, ապա կարող եք տեսնել, որ բանաձեւի բարը ոչ թե հերթական վճարման հաշվարկի բանաձեւն է, այլ ոչ զանգվածային զանգվածի հատուկ բանաձեւ: Այսինքն, այլեւս հնարավոր չէ փոխել արժեքները անհատական ​​բջիջներում: Ջնջել հաշվարկի արդյունքները միայն կարող են լինել բոլորը, եւ ոչ թե առանձին:

Բացի այդ, կարելի է նշել, որ ամսական վճարը տարեկան 12.5% ​​-ով, ստացված աղյուսակի կիրառմամբ, համապատասխանում է նույն տոկոսադրույքի արժեքին, որը ստացել ենք ֆունկցիան կիրառելով PMT- ը. Սա եւս մեկ անգամ ապացուցում է հաշվարկի ճշգրտությունը:

Այս աղյուսակային զանգվածը վերլուծելուց հետո պետք է ասել, որ, ինչպես տեսնում ենք, միայն տարեկան 9.5% տոկոսադրույքով ընդունվում է ամսական վճարման ընդունելի ամսական մակարդակը (29,000 ռուբլից պակաս):

Դաս: Excel- ում անուիտետի վճարման հաշվարկ

Մեթոդ 2 `երկու փոփոխականով գործիք օգտագործեք

Իհարկե, շատ դժվար է, եթե ամեն ինչ իրատեսական լինի, գտնելու բանկեր, որոնք վարկեր են տրամադրում տարեկան 9.5% -ով: Հետեւաբար, եկեք տեսնենք, թե ինչ տարբերակներ կան ներդրումներ կատարելու ամսական վճարման ընդունելի մակարդակում այլ փոփոխականների տարբեր կոմբինացիաների համար `վարկային մարմնի չափը եւ վարկի ժամկետը: Միեւնույն ժամանակ, տոկոսադրույքը կմնա անփոփոխ (12.5%): Գործիքը կօգնի մեզ այս հարցում: "Տվյալների աղյուսակ" օգտագործելով երկու փոփոխական:

1. Նվիրեք նոր աղյուսակի զանգված: Այժմ վարկերի տերմինը կցուցադրվի սյունակի անուններով (սկսած 2 մինչեւ 6 տարի տարիների ընթացքում, մեկ տարվա ընթացքում), իսկ շարքերում, վարկային մարմնի չափը (սկսած 850000 մինչեւ 950000 ռուբլին ավելացումներով 10000 ռուբլի): Այս դեպքում հրամայական է, որ բջիջը, որի հաշվարկման բանաձեւը գտնվում է (մեր դեպքում PMT- ը), որը գտնվում է գծի եւ սյունակների անունների սահմանում: Առանց այս պայմանի, գործիքը չի գործի, երբ օգտագործվում է երկու փոփոխական:



2. Այնուհետեւ ընտրեք բոլոր արդյունքների աղյուսակը, ներառյալ սյունակների անունները, տողերը եւ բջիջը բանաձեւով PMT- ը. Գնալ դեպի ներդիր «Տվյալների». Ինչպես նախորդ անգամ, սեղմեք կոճակը: «Ինչ-որ» վերլուծությունմի գործիքների խմբում "Աշխատանքային տվյալների հետ". Ցանկում, որը բացվում է, ընտրեք նյութը "Տվյալների աղյուսակ ...".



3. Գործիքային պատուհանը սկսվում է: "Տվյալների աղյուսակ". Այս դեպքում մեզ հարկավոր է երկու ոլորտներ: Դաշտում "Փոխարինեք արժեքները սյունակներով" մենք նշում ենք առաջնային տվյալների հիման վրա վարկի ժամկետը պարունակող բջիջի կոորդինատները: Դաշտում "Փոխարինեք արժեքների շարքերով" նշեք վարկի մարմնի արժեքը պարունակող սկզբնական պարամետրերի բջիջի հասցեն: Հետո բոլոր տվյալները մուտքագրվում են: Մենք սեղմեք կոճակը "OK".



4. Ծրագիրը կատարում է հաշվարկը եւ լրացնում է աղյուսակի տիրույթը տվյալների հետ: Տողերի եւ սյունակների խաչմերուկում այժմ հնարավոր է հետեւել, թե որքանով է ամսական վճարը լինելու, համապատասխան տոկոսի տարեկան տոկոսադրույքով եւ որոշակի վարկավորման ժամանակահատվածում:



5. Ինչպես տեսնում եք, շատ արժեքներ: Այլ խնդիրների լուծման համար կարող է լինել ավելին: Հետեւաբար արդյունքների արդյունքներն ավելի տեսողական դարձնելու եւ անմիջապես որոշելու համար, թե որ արժեքները չեն բավարարում տվյալ վիճակը, դուք կարող եք օգտագործել արտացոլման գործիքները: Մեր դեպքում դա պայմանական ձեւաչափ է: Ընտրեք աղյուսակի տիրույթի բոլոր արժեքները, բացառությամբ տողի եւ սյունակների վերնագրերի:



6. Տեղափոխեք էջանիշ «Տուն» եւ սեղմեք պատկերակը "Պայմանական ֆորմատավորում". Այն գտնվում է գործիքակազմում: «Սթայլ» վրա ժապավենը: Բացվում է ընտրացանկում ընտրեք նյութը «Բջջային ընտրության կանոններ». Լրացուցիչ ցանկում կտտացրեք դիրքորոշումը «Ավելի քիչ ...».



7. Դրանից հետո բացվում է պայմանական ձեւավորման պատուհանի պատուհանը: Ձախ դաշտում մենք նշում ենք այն արժեքը, որը պակաս է, որը կընտրվի բջիջները: Ինչպես հիշում ենք, մենք գոհ ենք այն պայմաններից, որոնցով վարկի ամսական վճարումը քիչ կլինի 29000 ռուբլի: Մուտքագրեք այս համարը: Ճիշտ դաշտում հնարավոր է ընտրել ընտրության գույնը, չնայած դուք կարող եք թողնել այն լռելյայն: Բոլոր պահանջվող պարամետրերից հետո մուտքագրեք կոճակը: "OK".



8. Դրանից հետո, բոլոր բջիջները, որոնց արժեքները համապատասխանում են վերը նշված պայմանին, կնշվի գունավոր:

Աղյուսակի զանգվածը վերլուծելուց հետո կարող եք որոշ եզրակացություններ անել: Ինչպես տեսնում եք, ներկայումս վարկի ժամանակահատվածը (36 ամիս), ամսական վճարումների վերը թվարկված գումար ներդնելու համար մենք պետք է վարկ վերցնենք 8,600,000.00 ռուբլի չգերազանցող գումարի չափով, այսինքն `40.000 պակաս, քան նախապես պլանավորված:

Եթե ​​մենք դեռ մտադիր ենք 900 հազար ռուբլի վարկ ստանալ, ապա վարկի տեւողությունը պետք է լինի 4 տարի (48 ամիս): Միայն այս դեպքում ամսական վճարման գումարը չի գերազանցի 29,000 ռուբլի սահմանաչափը:

Այսպիսով, օգտվելով այս աղյուսակային զանգվածից եւ վերլուծել յուրաքանչյուր տարբերակի առավելություններն ու թերությունները, վարկառուն կարող է որոշակի որոշում կայացնել վարկավորման պայմանների վերաբերյալ `ընտրելով այնպիսի տարբերակ, որը լավագույնս համապատասխանում է իր պահանջներին:

Անշուշտ, lookup սեղանը կարող է օգտագործվել ոչ միայն հաշվարկելու վարկային տարբերակները, այլեւ լուծել բազմաթիվ այլ խնդիրներ:

Դաս. Պայմանական ֆորմատավորում Excel- ում

Ընդհանուր առմամբ, պետք է նշել, որ lookup աղյուսակը շատ օգտակար եւ համեմատաբար պարզ գործիք է փոփոխականների տարբեր համակցությունների արդյունքների որոշման համար: Դրա հետ մեկտեղ պայմանական ձեւաչափը կիրառելով, կարող եք պատկերացնել ստացված տեղեկությունները: