ხშირად, თქვენ უნდა გამოთვალოთ საბოლოო შედეგი შეყვანის მონაცემების სხვადასხვა კომბინაციებისთვის. ამრიგად, მომხმარებელს შეეძლება შეაფასოს მოქმედებების ყველა შესაძლო ვარიანტი, შეარჩიოს ის, ვისი ურთიერთქმედების შედეგიც მას აკმაყოფილებს და, საბოლოოდ, აირჩიოს ყველაზე ოპტიმალური ვარიანტი. Excel- ში, ამ ამოცანის შესასრულებლად, არსებობს სპეციალური ინსტრუმენტი - "მონაცემთა ცხრილი" (შემცვლელი ცხრილი) მოდით გავეცნოთ როგორ გამოვიყენოთ იგი ზემოთ მოცემული სცენარების შესასრულებლად.
წაიკითხეთ აგრეთვე: პარამეტრების შერჩევა Excel- ში
მონაცემთა ცხრილის გამოყენება
ინსტრუმენტი "მონაცემთა ცხრილი" იგი მიზნად ისახავს შედეგის გამოთვლას ერთი ან ორი განსაზღვრული ცვლადის სხვადასხვა ვარიაციით. გაანგარიშების შემდეგ, ყველა შესაძლო ვარიანტი ცხრილის სახით ჩნდება, რომელსაც ფაქტორების ანალიზის მატრიქს უწოდებენ. "მონაცემთა ცხრილი" ეხება ინსტრუმენტების ჯგუფს "რა მოხდება, თუ ანალიზი", რომელიც მოთავსებულია ლენტით ჩანართში "მონაცემები" ბლოკში "მონაცემებთან მუშაობა". Excel 2007 წლამდე ამ ინსტრუმენტს ეძახდნენ შემცვლელი ცხრილი, რაც კიდევ უფრო ზუსტად ასახავდა მის არსს ვიდრე არსებულ სახელს.
საძიებო ცხრილის გამოყენება ბევრ შემთხვევაში შეიძლება. მაგალითად, ტიპიური ვარიანტია, როდესაც თქვენ უნდა გამოანგარიშოთ ყოველთვიური სესხის დაფარვის თანხა საკრედიტო პერიოდის სხვადასხვა ვარიაციისა და სესხის თანხის, ან საკრედიტო პერიოდის და პროცენტის პროცენტისთვის. ასევე, ამ ინსტრუმენტის გამოყენება შესაძლებელია საინვესტიციო პროექტების მოდელების ანალიზში.
თქვენ ასევე უნდა გაითვალისწინოთ, რომ ამ ხელსაწყოს გადაჭარბებულმა გამოყენებამ შეიძლება გამოიწვიოს სისტემის დამუხრუჭება, რადგან მონაცემები მუდმივად იბრუნებს. აქედან გამომდინარე, რეკომენდებულია მცირე მაგიდის მასივებში მსგავსი პრობლემების გადასაჭრელად, რომ არ გამოიყენოთ ეს ინსტრუმენტი, არამედ გამოიყენოთ ფორმულის კოპირება შევსების მარკერის გამოყენებით.
გამართლებული განაცხადი "მონაცემთა ცხრილი" მხოლოდ დიდი ცხრილების დიაპაზონშია, როდესაც ფორმულების კოპირებას ბევრი დრო სჭირდება, ხოლო თავად პროცედურის დროს შეცდომების დაშვების ალბათობა იზრდება. მაგრამ ამ შემთხვევაში, რეკომენდებულია ფორმულის ავტომატური გადაანგარიშება ჩანაცვლების ცხრილის დიაპაზონში, რათა თავიდან იქნას აცილებული სისტემაში ზედმეტი დატვირთვა.
მონაცემთა ცხრილის სხვადასხვა გამოყენებებს შორის მთავარი განსხვავებაა გაანგარიშებაში ჩართული ცვლადის რაოდენობა: ერთი ცვლადი ან ორი.
მეთოდი 1: გამოიყენეთ ინსტრუმენტი ერთი ცვლადი
დაუყოვნებლივ გადავხედოთ ვარიანტს, როდესაც მონაცემთა ცხრილი გამოიყენება ერთი ცვლადი მნიშვნელობით. მიიღეთ ყველაზე ტიპიური დაკრედიტების მაგალითი.
ამრიგად, ჩვენ გთავაზობენ შემდეგი სესხის პირობებს:
- სესხის ვადა - 3 წელი (36 თვე);
- სესხის თანხა - 900,000 რუბლი;
- საპროცენტო განაკვეთი - 12,5% წელიწადში.
გადახდები ხდება გადახდის პერიოდის ბოლოს (თვეში) ანუიტეტის სქემის მიხედვით, ანუ თანაბარ აქციებში. ამავე დროს, მთელი სესხის ვადის დასაწყისში, გადასახდელების მნიშვნელოვანი ნაწილია პროცენტის გადახდა, მაგრამ როგორც სხეული მცირდება, პროცენტული გადასახადები კლებულობს და თვით სხეულზე დაფარვის თანხა იზრდება. მთლიანი ანაზღაურება, როგორც ზემოთ აღინიშნა, უცვლელი რჩება.
აუცილებელია გამოვთვალოთ, თუ რა ოდენობა იქნება ყოველთვიური გადასახადი, მათ შორის სესხის ორგანოს დაფარვისა და პროცენტის გადახდების ჩათვლით. ამისათვის Excel– ს აქვს ოპერატორი PMT.
PMT მიეკუთვნება ფინანსური ფუნქციების ჯგუფს და მისი ამოცანაა ყოველთვიური გადასახადის ტიპის სესხის გადაანგარიშება სესხის ორგანოს ოდენობის, სესხის ვადის და პროცენტის საფუძველზე. ამ ფუნქციის სინტაქსი წარმოდგენილია როგორც
= PLT (კურსი; nper; ps; bs; ტიპი)
წინადადება - არგუმენტი, რომელიც განსაზღვრავს საკრედიტო გადახდების საპროცენტო განაკვეთს. ინდიკატორი მითითებულია პერიოდისთვის. ჩვენი ანაზღაურებადი პერიოდის ტოლია ერთი თვის განმავლობაში. ამრიგად, წლიური განაკვეთი 12.5% უნდა გაიყოს წელიწადში თვეების მიხედვით, ანუ 12-ზე.
"ნპერი" - არგუმენტი, რომელიც განსაზღვრავს მთელი სესხის ვადის პერიოდების რაოდენობას. ჩვენს მაგალითში, პერიოდი ერთი თვეა, ხოლო სესხის ვადაა 3 წელი ან 36 თვე. ამრიგად, პერიოდების რაოდენობა იქნება ადრეული 36.
"პს" - არგუმენტი, რომელიც განსაზღვრავს სესხის ამჟამინდელ ღირებულებას, ანუ ეს არის სესხის ორგანოს ზომა მისი გაცემის დროს. ჩვენს შემთხვევაში, ეს ციფრი 900,000 რუბლს შეადგენს.
"BS" - არგუმენტი, რომელიც მიუთითებს სესხის ორგანოს ზომაზე სრული გადახდის დროს. ბუნებრივია, ეს მაჩვენებელი ნულის ტოლი იქნება. ეს არგუმენტი არასავალდებულოა. თუ გამოტოვებთ მას, ითვლება, რომ იგი უდრის რიცხვს "0".
"ტიპი" - ასევე არჩევითი არგუმენტი. იგი აცხადებს, როდის მოხდება გადახდა: პერიოდის დასაწყისში (პარამეტრი - "1") ან პერიოდის ბოლოს (პარამეტრი - "0") როგორც მახსოვს, ჩვენი ანაზღაურება ხდება კალენდარული თვის ბოლოს, ანუ ამ არგუმენტის ღირებულება ტოლი იქნება "0". მაგრამ იმის გათვალისწინებით, რომ ეს მაჩვენებელი არ არის სავალდებულო და, სტანდარტულად, თუ არ გამოიყენება, მნიშვნელობა გულისხმობს, რომ თანაბარი იყოს "0"შემდეგ მითითებულ მაგალითში იგი შეიძლება საერთოდ გამოტოვდეს.
- ამრიგად, ჩვენ ვიანგარიშებით. შეარჩიეთ უჯრედი ფურცელზე, სადაც გამოითვლება გამოთვლილი მნიშვნელობა. დააჭირეთ ღილაკს "ფუნქციის ჩასმა".
- იწყებს მხატვრული ოსტატი. კატეგორიაში გადავდივართ "ფინანსური"აირჩიეთ სახელი სიიდან "PLT" და დააჭირეთ ღილაკს "კარგი".
- ამის შემდეგ გააქტიურებულია ზემოთ ჩამოთვლილი ფუნქციის არგუმენტების ფანჯარა.
კურსორი განათავსეთ მინდორში წინადადებაამის შემდეგ, ჩვენ ფურცელზე უჯრედს ვაკლებთ წლიური საპროცენტო განაკვეთის მნიშვნელობით. როგორც ხედავთ, მისი კოორდინატები მაშინვე ნაჩვენებია ველში. მაგრამ, როგორც მახსოვს, ყოველთვიური მაჩვენებელი გვჭირდება და, შესაბამისად, შედეგს ვყოფთ 12-ით (/12).
მინდორში "ნპერი" ანალოგიურად, ჩვენ ვიღებთ სესხის ვადის უჯრედების კოორდინატებს. ამ შემთხვევაში, არაფრის გაზიარება არ გჭირდებათ.
მინდორში ფსალმ თქვენ უნდა მიუთითოთ უჯრედის კოორდინატები, რომლებიც შეიცავს სასესხო ორგანოს ღირებულებას. ჩვენ ამას ვაკეთებთ. ჩვენ ასევე დავაყენეთ ნიშანი ნაჩვენები კოორდინატების წინ "-". ფაქტია, რომ ფუნქცია PMT ნაგულისხმევი, ეს საბოლოო შედეგს უარყოფითი ნიშნით იძლევა, სამართლიანად თუ გავითვალისწინებთ ყოველთვიურად სესხის გადახდის ზარალს. მაგრამ მონაცემთა ცხრილის გამოყენების სიცხადისთვის, ჩვენ გვჭირდება ეს რიცხვი იყოს პოზიტიური. ამიტომ, ჩვენ ნიშანს ვაყენებთ მინუს ერთი ფუნქციის არგუმენტამდე. ცნობილია გამრავლება მინუს on მინუს ბოლოს იძლევა პლიუსი.
მინდვრებში "Bs" და "ტიპი" მონაცემები საერთოდ არ შედის. დააჭირეთ ღილაკს "კარგი".
- ამის შემდეგ, ოპერატორი ითვლის და აჩვენებს მთლიანი ყოველთვიური გადასახადის შედეგს წინასწარ განსაზღვრულ საკანში - 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. ჩანართზე გადასვლა "მონაცემები". როგორც წინა ჯერზე, დააჭირეთ ღილაკს "რა მოხდება, თუ ანალიზი", ინსტრუმენტთა ჯგუფში "მონაცემებთან მუშაობა". სიაში, რომელიც იხსნება, აირჩიეთ "მონაცემთა ცხრილი ...".
- ხელსაწყოს ფანჯარა იწყება "მონაცემთა ცხრილი". ამ შემთხვევაში, ორივე სფერო გვჭირდება. მინდორში ჩაანაცვლეთ სვეტის მნიშვნელობები პირველ მონაცემებში მიეთითეთ საკნის კოორდინატები, რომლებიც შეიცავს სესხის ვადას. მინდორში "შეცვალეთ მნიშვნელობები მწკრივში" მიუთითეთ საწყისი პარამეტრების უჯრედის მისამართი, რომელიც შეიცავს სასესხო ორგანოს ღირებულებას. ყველა მონაცემის შეტანის შემდეგ. დააჭირეთ ღილაკს "კარგი".
- პროგრამა ასრულებს გაანგარიშებას და ავსებს ცხრილის დიაპაზონს მონაცემებით. სტრიქონების და სვეტების კვეთაზე ახლა უკვე დააკვირდით, რა იქნება თვიური გადასახადი, წლიური პროცენტის შესაბამისი თანხით და მითითებული სესხის ვადა.
- როგორც ხედავთ, ბევრი მნიშვნელობა აქვს. სხვა პრობლემების გადასაჭრელად შეიძლება კიდევ უფრო მეტი იყოს. აქედან გამომდინარე, შედეგების გამომუშავება უფრო ვიზუალური გახადეთ და დაუყოვნებლივ განსაზღვროთ რომელი ფასეულობები არ აკმაყოფილებს მოცემულ მდგომარეობას, შეგიძლიათ გამოიყენოთ ვიზუალიზაციის ხელსაწყოები. ჩვენს შემთხვევაში, ეს იქნება პირობითი ფორმატირება. ჩვენ ვირჩევთ ცხრილის დიაპაზონის ყველა მნიშვნელობას, რიგის და სვეტის სათაურების გამოკლებით.
- ჩანართზე გადასვლა "მთავარი" და დააჭირეთ ხატულას პირობითი ფორმატირება. იგი მდებარეობს ხელსაწყოს ბლოკში. სტილები ფირზე. მენიუში, რომელიც იხსნება, აირჩიეთ უჯრედის შერჩევის წესები. დამატებითი სიაში დააჭირეთ პოზიციას "ნაკლები ...".
- ამის შემდეგ იხსნება პირობითი ფორმატის პარამეტრების ფანჯარა. მარცხენა ველში მიუთითეთ მნიშვნელობა ნაკლები, ვიდრე შეირჩევა უჯრედები. როგორც მახსოვს, ჩვენ კმაყოფილნი ვართ იმ პირობით, რომ სესხის ყოველთვიური გადასახადი ნაკლები იქნება 29000 რუბლი. ჩვენ ამ ნომერზე ვდივართ. მარჯვენა ველში შეგიძლიათ მონიშნოთ მონიშნული ფერი, თუმცა შეგიძლიათ დატოვოთ ის ნაგულისხმევი. ყველა საჭირო პარამეტრის შეყვანის შემდეგ დააჭირეთ ღილაკს "კარგი".
- ამის შემდეგ, ყველა უჯრედი, რომლის მნიშვნელობაც შეესაბამება ზემოთ მოცემულ მდგომარეობას, ხაზგასმული იქნება.
ცხრილის მასივის გაანალიზების შემდეგ, შეგვიძლია გამოვიტანოთ რამდენიმე დასკვნა. როგორც ხედავთ, არსებული სესხის პირობით (36 თვე), იმისათვის რომ ინვესტიცია ჩადეთ ზემოთ მითითებულ ყოველთვიურ თანხაში, უნდა ავიღოთ სესხი, რომელიც არ აღემატება 860000.00 რუბლს, ანუ 40,000-ით ნაკლებია, ვიდრე თავდაპირველად იყო დაგეგმილი.
თუ ჩვენ მაინც განზრახული გვაქვს 900000 რუბლის სესხის აღება, მაშინ სესხის ვადა უნდა იყოს 4 წელი (48 თვე). მხოლოდ ამ შემთხვევაში, ყოველთვიური გადასახადი არ აღემატება დადგენილ ზღვარს 29,000 რუბლს.
ამრიგად, ამ ცხრილის მასივის გამოყენებით და თითოეული ვარიანტის დადებითი და უარყოფითი მხარეების ანალიზით, მსესხებელს შეუძლია მიიღოს გადაწყვეტილება კონკრეტული სესხის პირობებზე, ყველა შესაძლოდან ირჩევს ყველაზე შესაფერისი ვარიანტს.
რა თქმა უნდა, საძიებო ცხრილი შეგიძლიათ გამოიყენოთ არა მხოლოდ საკრედიტო ვარიანტების გამოსათვლელად, არამედ მრავალი სხვა პრობლემის გადასაჭრელად.
გაკვეთილი: პირობითი ფორმატირება Excel- ში
ზოგადად, უნდა აღინიშნოს, რომ საძიებო ცხრილი არის ძალზე სასარგებლო და შედარებით მარტივი საშუალება ცვლადის სხვადასხვა კომბინაციებისთვის შედეგის დასადგენად. პირობითი ფორმატის ერთდროულად გამოყენებით, გარდა ამისა, შეგიძლიათ მიიღოთ მიღებული ინფორმაციის ვიზუალიზაცია.
