Շատ հաճախ, դուք պետք է հաշվարկեք վերջնական արդյունքը մուտքային տվյալների տարբեր համակցությունների համար: Այսպիսով, օգտագործողը կկարողանա գնահատել գործողությունների բոլոր հնարավոր տարբերակները, ընտրել նրանց, ում փոխգործակցության արդյունքները բավարարում են նրան և, վերջապես, ընտրելու առավել օպտիմալ տարբերակ: Excel- ում այս առաջադրանքը կատարելու համար կա հատուկ գործիք ` «Տվյալների աղյուսակ» (Փոխարինման աղյուսակ) Եկեք պարզենք, թե ինչպես օգտագործել այն վերը նշված սցենարները լրացնելու համար:
Կարդացեք նաև ՝ պարամետրերի ընտրությունը Excel- ում
Օգտագործելով տվյալների աղյուսակը
Գործիք «Տվյալների աղյուսակ» Նախատեսված է հաշվարկել արդյունքը մեկ կամ երկու սահմանված փոփոխականների տարբեր տատանումների համար: Հաշվարկից հետո բոլոր հնարավոր տարբերակները հայտնվում են աղյուսակի տեսքով, որը կոչվում է գործոնների վերլուծության մատրից: «Տվյալների աղյուսակ» վերաբերում է գործիքների խմբին «Ի՞նչ անել, եթե վերլուծություն», որը տեղադրված է ժապավենի վրա ներդիրում «Տվյալներ» բլոկում «Աշխատել տվյալների հետ». Excel 2007-ից առաջ այս գործիքը կոչվեց Փոխարինման աղյուսակ, որը նույնիսկ ավելի ճշգրիտ արտացոլեց դրա էությունը, քան ներկայիս անվանումը:
Որոնման աղյուսակը կարող է օգտագործվել շատ դեպքերում: Օրինակ, բնորոշ տարբերակն այն է, երբ դուք պետք է հաշվարկեք վարկի ամսական վճարման գումարը վարկավորման ժամանակաշրջանի և վարկի գումարի տարբեր տատանումների, կամ վարկավորման ժամանակահատվածի և տոկոսադրույքի համար: Բացի այդ, այս գործիքը կարող է օգտագործվել ներդրումային նախագծերի մոդելների վերլուծության մեջ:
Բայց պետք է նաև տեղյակ լինել, որ այս գործիքի չափազանց մեծ օգտագործումը կարող է հանգեցնել համակարգի արգելակման, քանի որ տվյալները անընդհատ վերահաշվարկվում են: Հետևաբար, խորհուրդ է տրվում փոքր սեղանի զանգվածներում լուծել նմանատիպ խնդիրներ `այս գործիքը չօգտագործելու համար, այլ օգտագործելու բանաձևի պատճենումը` լրացնելով նշիչով:
Հիմնավորված դիմում «Տվյալների աղյուսակներ» միայն մեծ սեղանների միջակայքում է, երբ բանաձևերը պատճենելը կարող է շատ ժամանակ պահանջել, և ընթացակարգի ընթացքում ինքնին սխալներ թույլ տալու հավանականությունը մեծանում է: Բայց այս դեպքում խորհուրդ է տրվում անջատել բանաձևերի ավտոմատ վերահաշվարկը փոխարինման աղյուսակի սահմաններում ՝ համակարգում ավելորդ բեռից խուսափելու համար:
Տվյալների աղյուսակի տարբեր օգտագործման հիմնական տարբերությունը հաշվարկում ընդգրկված փոփոխականների քանակն է `մեկ փոփոխական կամ երկուս:
Մեթոդ 1. Գործիքը օգտագործել մեկ փոփոխականով
Անմիջապես եկեք նայենք այն տարբերակին, երբ տվյալների աղյուսակը օգտագործվում է մեկ փոփոխական արժեքով: Վերցրեք վարկավորման առավել բնորոշ օրինակ:
Այսպիսով, ներկայումս մեզ առաջարկվում են վարկի հետևյալ պայմանները.
- Վարկի ժամկետը `3 տարի (36 ամիս)
- Վարկի գումարը `900,000 ռուբլի;
- Տոկոսադրույքը `տարեկան 12,5%:
Վճարումները տեղի են ունենում վճարման ժամանակաշրջանի ավարտին (ամիսը) `ըստ անուիտետի սխեմայի, այսինքն` հավասար բաժնետոմսերի: Միևնույն ժամանակ, վարկի ամբողջ ժամկետի սկզբում վճարումների զգալի մասը կազմում են տոկոսագումարները, բայց քանի որ մարմինը նեղանում է, տոկոսադրույքները նվազում են, իսկ մարմնի մարման գումարը ինքնին ավելանում է: Ընդհանուր վճարը, ինչպես նշվեց վերևում, մնում է անփոփոխ:
Անհրաժեշտ է հաշվարկել, թե ինչ կլինի ամսական վճարման գումարը, ներառյալ վարկային մարմնի մարման և տոկոսների վճարումները: Դրա համար Excel- ն ունի օպերատոր PMT.
PMT պատկանում է ֆինանսական գործառույթների խմբին, և նրա խնդիրն է հաշվարկել ամսեկան անուիտետային վարկի վճարումը `ելնելով վարկի մարմնի գումարի, վարկի ժամկետի և տոկոսադրույքի հիման վրա: Այս գործառույթի շարահյուսությունը ներկայացված է որպես
= PLT (տոկոսադրույքը; nper; ps; bs; տեսակը)
Առաջարկ - փաստարկ, որը որոշում է վարկային վճարների տոկոսադրույքը: Theուցանիշը սահմանված է ժամանակահատվածի համար: Մեր վճարման ժամկետը հավասար է մեկ ամսվա: Հետևաբար, տարեկան 12,5% տոկոսադրույքը պետք է բաժանել մեկ տարվա ամսվա թվին, այսինքն `12-ին:
«Նպեր» - փաստարկ, որը որոշում է վարկի ամբողջ ժամկետի համար ժամկետների քանակը: Մեր օրինակում ՝ ժամկետը մեկ ամիս է, իսկ վարկի ժամկետը ՝ 3 տարի կամ 36 ամիս: Այսպիսով, ժամանակաշրջանների թիվը կլինի վաղ 36:
«PS» - փաստարկ, որը որոշում է վարկի ներկա արժեքը, այսինքն `դա վարկի մարմնի չափն է դրա թողարկման պահին: Մեր դեպքում այս ցուցանիշը 900 000 ռուբլի է:
«ԲՍ» - փաստարկ `նշելով վարկի մարմնի չափը լրիվ վճարման պահին: Բնականաբար, այս ցուցանիշը հավասար կլինի զրոյի: Այս փաստարկը կամայական է: Եթե այն բաց եք թողնում, ենթադրվում է, որ այն հավասար է «0» թվին:
«Տիպ» - նաև կամընտիր փաստարկ: Նա հայտարարում է, թե երբ է կատարվելու վճարումը. Ժամանակաշրջանի սկզբում (պարամետր - "1") կամ ժամանակաշրջանի վերջում (պարամետր - "0") Ինչպես հիշում ենք, մեր վճարումը կատարվում է օրացուցային ամսվա վերջին, այսինքն ՝ այս փաստարկի արժեքը հավասար կլինի "0". Բայց հաշվի առնելով, որ այս ցուցանիշը պարտադիր չէ, և ըստ լռելյայն, եթե չի օգտագործվում, արժեքը ենթադրում է հավասար "0", ապա նշված օրինակում այն կարելի է ընդհանրապես բաց թողնել:
- Այսպիսով, մենք անցնում ենք հաշվարկին: Ընտրեք բջիջ այն թերթիկի վրա, որտեղ կցուցադրվի հաշվարկված արժեքը: Կտտացրեք կոճակը «Տեղադրեք գործառույթը».
- Սկսվում է Խաղարկային հրաշագործ. Մենք տեղափոխվում ենք կատեգորիա «Ֆինանսական»ընտրեք անունը ցուցակից «PLT» և կտտացրեք կոճակը «Լավ».
- Դրանից հետո ակտիվացվում է վերը նշված գործառույթի փաստարկների պատուհանը:
Կուրսորը դրեք դաշտում Առաջարկ, որից հետո մենք կտտացնում ենք թերթի բջիջը տարեկան տոկոսադրույքի արժեքով: Ինչպես տեսնում եք, դրա կոորդինատներն անմիջապես ցուցադրվում են դաշտում: Բայց, ինչպես հիշում ենք, մեզ պետք է ամսական դրույքաչափ, ուստի արդյունքը բաժանում ենք 12-ով (/12).
Դաշտում «Նպեր» նույն կերպ մենք մտնում ենք վարկի ժամկետի բջիջների կոորդինատները: Այս դեպքում ձեզ հարկավոր չէ որևէ բան կիսել:
Դաշտում Ս հարկավոր է նշել վարկի մարմնի արժեքը պարունակող բջջի կոորդինատները: Մենք դա անում ենք: Մենք նաև ցուցանակ ենք դնում ցուցադրված կոորդինատների դիմաց "-". Փաստն այն է, որ գործառույթը PMT լռելյայն այն տալիս է վերջնական արդյունքը բացասական նշանով ՝ իրավամբ հաշվի առնելով վարկի մարման ամսական կորուստը: Բայց տվյալների աղյուսակի կիրառման հստակության համար մեզ անհրաժեշտ է, որ այս թիվը դրական լինի: Հետևաբար, մենք նշան ենք դնում մինուս գործառույթի փաստարկներից մեկից առաջ: Հայտնի է բազմապատկումը մինուս վրա մինուս վերջում տալիս է գումարած.
Դաշտերը «ԲՍ» և «Տիպ» տվյալներն ընդհանրապես մուտքագրված չեն: Կտտացրեք կոճակը «Լավ».
- Դրանից հետո, օպերատորը հաշվարկում և ցուցադրում է նախապես նշանակված խցում ընդհանուր ամսական վճարման արդյունքը. 30108,26 ռուբլի: Բայց խնդիրն այն է, որ վարկառուն ի վիճակի է ամսեկան վճարել առավելագույնը 29,000 ռուբլի, այսինքն ՝ նա կամ պետք է գտնի այնպիսի բանկ, որն առաջարկում է ավելի ցածր տոկոսադրույքով պայմաններ, կամ կրճատել վարկի մարմինը, կամ ավելացնել վարկի ժամկետը: Որոնման աղյուսակը կօգնի մեզ պարզել տարբեր ընտրանքներ:
- Նախ օգտագործեք որոնման աղյուսակը մեկ փոփոխականով: Տեսնենք, թե ինչպես կփոխվի պարտադիր ամսական վճարման գումարը տարեկան փոխարժեքի տարբեր տատանումների հետ `սկսած 9,5% տարեկան և վերջ 12,5% տարեկան կտրվածքով հավելավճարներով 0,5%. Մնացած բոլոր պայմանները մնում են անփոփոխ: Մենք գծագրում ենք սեղանի միջակայքը, որի սյունակների անունները կհամապատասխանեն տոկոսադրույքի տարբեր տատանումներին: Այս տողով «Ամսական վճարումներ» հեռացեք ինչպես կա: Դրա առաջին բջիջը պետք է պարունակի այն բանաձևը, որը մենք հաշվարկել էինք ավելի վաղ: Լրացուցիչ տեղեկությունների համար կարող եք տողեր ավելացնել «Ընդհանուր վարկի գումար» և «Ընդհանուր տոկոս». Սյունակը, որում գտնվում է հաշվարկը, կատարվում է առանց վերնագրի:
- Հաջորդը, մենք հաշվարկում ենք վարկի ընդհանուր գումարը ներկայիս պայմաններում: Դա անելու համար ընտրեք շարքի առաջին բջիջը «Ընդհանուր վարկի գումար» և բազմապատկել բջիջների պարունակությունը «Ամսական վճարում» և «Վարկի ժամկետ». Դրանից հետո կտտացրեք կոճակը Մտնեք.
- Ընթացիկ պայմաններում տոկոսների ընդհանուր գումարը հաշվարկելու համար մենք նմանապես հանում ենք վարկի մարմնի գումարը վարկի ընդհանուր գումարից: Արդյունքը էկրանին ցուցադրելու համար կտտացրեք կոճակը Մտնեք. Այսպիսով, մենք ստանում ենք այն գումարը, որը մենք վճարել ենք վարկը մարելիս:
- Այժմ ժամանակն է կիրառել գործիքը «Տվյալների աղյուսակ». Մենք ընտրում ենք սեղանի ամբողջ զանգվածը, բացառությամբ տողի անունների: Դրանից հետո անցեք ներդիրին «Տվյալներ». Կտտացրեք ժապավենի վրա գտնվող կոճակը «Ի՞նչ անել, եթե վերլուծություն»որը գտնվում է գործիքների խմբում «Աշխատել տվյալների հետ» (Excel 2016-ում ՝ գործիքների խումբ «Կանխատեսում») Այնուհետև բացվում է փոքր մենյու: Դրա մեջ մենք ընտրում ենք դիրք "Տվյալների աղյուսակ ...".
- Մի փոքր պատուհան է բացվում, որը կոչվում է «Տվյալների աղյուսակ». Ինչպես տեսնում եք, այն ունի երկու դաշտ: Քանի որ մենք աշխատում ենք մեկ փոփոխականի հետ, մեզ պետք է միայն դրանցից մեկը: Քանի որ փոփոխական սյունը մենք փոխում ենք սյունակով, մենք կօգտագործենք դաշտը Փոխարինեք սյունակի արժեքները. Սահմանեք կուրսորը այնտեղ, ապա կտտացրեք բջջայինը սկզբնական տվյալների բազայում, որը պարունակում է ընթացիկ տոկոս: Բջջային կոորդինատները դաշտում ցուցադրվելուց հետո կտտացրեք կոճակը «Լավ».
- Գործիքը հաշվարկում և լրացնում է ամբողջ աղյուսակային տիրույթը արժեքներով, որոնք համապատասխանում են տոկոսադրույքի տարբեր տարբերակների: Եթե կուրսորը տեղադրեք այս սեղանի տարածքի որևէ տարրում, ապա կարող եք տեսնել, որ բանաձևի սանդղակը չի ցուցադրում վճարման հաշվարկման սովորական բանաձևը, այլ անբաժանելի զանգվածի հատուկ բանաձև: Այսինքն, այժմ անհնար է արժեքները փոփոխել առանձին բջիջներում: Դուք կարող եք ջնջել հաշվարկման արդյունքները միայն բոլորը միասին, և ոչ թե առանձին:
Բացի այդ, դուք կարող եք տեսնել, որ որոնման աղյուսակը կիրառելու արդյունքում ստացված տարեկան 12,5% ամսական վճարը համապատասխանում է այն նույն տոկոսի համար ստացված արժեքին, որը մենք ստացել ենք գործառույթը կիրառելով PMT. Սա ևս մեկ անգամ փաստում է հաշվարկի ճիշտությունը:
Այս սեղանի զանգվածը վերլուծելուց հետո պետք է ասել, որ, ինչպես տեսնում եք, միայն տարեկան 9,5% տոկոսադրույքով մենք ստանում ենք ընդունելի ամսական վճարման մակարդակ (29,000 ռուբլիից պակաս):
Դաս. Annuity վճարման հաշվարկ Excel- ում
Մեթոդ 2. Գործիքը օգտագործել երկու փոփոխականով
Իհարկե, ներկայումս գտնելը, որ բանկերը, ովքեր տարեկան 9,5% տոկոսադրույքով վարկ են տալիս, շատ դժվար է, եթե ոչ անհնար: Հետևաբար, մենք կտեսնենք, թե ինչ տարբերակներ կան ՝ ներդրումներ կատարելու ամսական վճարման ընդունելի մակարդակի վրա ՝ այլ փոփոխականների տարբեր համակցությունների համար ՝ վարկի մարմնի չափը և վարկի ժամկետը: Այս դեպքում տոկոսադրույքը կմնա անփոփոխ (12,5%): Այս խնդիրը լուծելու հարցում մեզ կօգնի մի գործիք: «Տվյալների աղյուսակ» օգտագործելով երկու փոփոխական:
- Մենք գծագրում ենք սեղանի նոր զանգված: Այժմ սյունակում նշված կլինեն վարկի ժամկետը (սկսած 2 առաջ 6 տարիներ ամիսների ընթացքում `մեկ տարվա աճով), իսկ տողերով` վարկի մարմնի չափը (սկսած 850000 առաջ 950000 ռուբլով հավելավճարներով 10000 ռուբլի): Այս դեպքում նախադրյալն այն բջիջն է, որում գտնվում է հաշվարկման բանաձևը (մեր դեպքում) PMT), որը գտնվում է տողի և սյունակի անվանումների սահմանին: Առանց այս պայմանի, գործիքը չի աշխատի երկու փոփոխական օգտագործելիս:
- Դրանից հետո ընտրեք ամբողջ արդյունքում ստացված սեղանի միջակայքը, ներառյալ սյունակների, տողերի և բջիջների անունները բանաձևով PMT. Գնացեք ներդիրին «Տվյալներ». Ինչպես նախորդ անգամ, կտտացրեք կոճակը «Ի՞նչ անել, եթե վերլուծություն»՝ գործիքների խմբում «Աշխատել տվյալների հետ». Opensանկում, որը բացվում է, ընտրեք "Տվյալների աղյուսակ ...".
- Գործիքի պատուհանը սկսվում է «Տվյալների աղյուսակ». Այս պարագայում մեզ անհրաժեշտ են երկու դաշտեր: Դաշտում Փոխարինեք սյունակի արժեքները առաջնային տվյալների մեջ նշեք վարկի ժամկետը պարունակող բջջի կոորդինատները: Դաշտում "Փոխարինեք արժեքները անընդմեջ անընդմեջ" նշեք վարկային մարմնի արժեքը պարունակող նախնական պարամետրերի բջիջի հասցեն: Բոլոր տվյալները մուտքագրվելուց հետո: Կտտացրեք կոճակը «Լավ».
- Ծրագիրը կատարում է հաշվարկը և սեղանի միջակայքը լրացնում է տվյալներով: Տողերի և սյուների խաչմերուկում այժմ հնարավոր է դիտարկել, թե կոնկրետ ինչ է լինելու ամսական վճարը ՝ տարեկան տոկոսների համապատասխան չափով և նշված վարկի ժամկետով:
- Ինչպես տեսնում եք, արժեքները շատ են: Այլ խնդիրներ լուծելու համար կարող են լինել նույնիսկ ավելին: Հետևաբար, արդյունքների արդյունքը ավելի տեսողական դարձնելու և անմիջապես որոշելու, թե որ արժեքները չեն բավարարում տվյալ պայմանը, կարող եք օգտագործել վիզուալացման գործիքներ: Մեր դեպքում սա կլինի պայմանական ձևաչափում: Մենք ընտրում ենք սեղանի տիրույթի բոլոր արժեքները ՝ բացառությամբ տողի և սյունակի վերնագրերի:
- Տեղափոխեք դեպի ներդիր «Տուն» և կտտացրեք պատկերակին Պայմանական ձևաչափում. Այն գտնվում է գործիքի բլոկում: Ոճերը ժապավենի վրա: Ընտրացանկում ընտրեք Բջջային ընտրության կանոններ. Լրացուցիչ ցուցակում կտտացրեք դիրքը «Քիչ ...»:.
- Դրանից հետո բացվում է պայմանական ձևաչափման պարամետրերի պատուհանը: Ձախ դաշտում նշեք այն արժեքը, որը պակաս է, քան որ կընտրվեն բջիջները: Ինչպես հիշում ենք, մենք գոհ ենք այն պայմանից, որ վարկի ամսական վճարումը կլինի ավելի ցածր, քան 29000 ռուբլի: Մենք մուտքագրում ենք այս համարը: Fieldիշտ դաշտում կարող եք ընտրել ընդգծված գույնը, չնայած կարող եք լռելյայն թողնել այն: Բոլոր անհրաժեշտ պարամետրերը մուտքագրելուց հետո կտտացրեք կոճակը «Լավ».
- Դրանից հետո կցուցադրվեն բոլոր բջիջները, որոնց արժեքները համապատասխանում են վերը նշված պայմանին:
Վերլուծելով սեղանի զանգվածը ՝ մենք կարող ենք եզրակացություններ անել: Ինչպես տեսնում եք, գոյություն ունեցող վարկի ժամկետով (36 ամիս), վերը նշված ամսական վճարման գումարի ներդրման համար մենք պետք է վերցնենք վարկ, որը չի գերազանցում 860000.00 ռուբլի, այսինքն ՝ 40,000-ով պակաս, քան նախատեսված էր նախապես:
Եթե մենք դեռ մտադիր ենք վարկ վերցնել 900,000 ռուբլի, ապա վարկի ժամկետը պետք է լինի 4 տարի (48 ամիս): Միայն այս դեպքում ամսական վճարումը չի գերազանցի սահմանված սահմանը 29,000 ռուբլի:
Այսպիսով, օգտագործելով այս աղյուսակի զանգվածը և վերլուծելով յուրաքանչյուր տարբերակի կողմ և դեմ եղած կողմերը, վարկառուն կարող է որոշակի որոշում կայացնել վարկի պայմանների վերաբերյալ ՝ հնարավորից ընտրելով առավել հարմար տարբերակ:
Իհարկե, որոնման աղյուսակը կարող է օգտագործվել ոչ միայն վարկային ընտրանքները հաշվարկելու, այլև բազմաթիվ այլ խնդիրներ լուծելու համար:
Դաս. Պայմանական ձևաչափում Excel- ում
Ընդհանուր առմամբ, հարկ է նշել, որ որոնման աղյուսակը փոփոխականների տարբեր համակցությունների համար արդյունքը որոշելու համար շատ օգտակար և համեմատաբար պարզ գործիք է: Միաժամանակ օգտագործելով պայմանական ձևաչափումը, բացի այդ, կարող եք պատկերացնել ստացված տեղեկատվությունը:
