ხშირად, საჭიროა გამოითვალოს საბოლოო შედეგი შეყვანის მონაცემების სხვადასხვა კომბინაციებისთვის. ამდენად, მომხმარებელს შეეძლება შეაფასოს ყველა შესაძლო ვარიანტის მოქმედება, შეარჩიოს ისინი, რომელთა ურთიერთქმედება მას აკმაყოფილებს და საბოლოოდ აირჩიოს ყველაზე ოპტიმალური ვარიანტი. In Excel, არსებობს სპეციალური ინსტრუმენტი ამ ამოცანის - "მონაცემთა მაგიდა" ("საძიებო მაგიდა"). მოდი ვიპოვოთ ის, თუ როგორ გამოვიყენოთ იგი ზემოთ სცენარით.
იხილეთ ასევე: პარამეტრის შერჩევა Excel- ში
მონაცემთა ცხრილის გამოყენება
ინსტრუმენტი "მონაცემთა მაგიდა" მიზნად ისახავს შედეგების გამოთვლას ერთი ან ორი განსაზღვრული ცვლადის განსხვავებული ვარიაციით. გაანგარიშების შემდეგ ყველა შესაძლო ვარიანტი გამოჩნდება ცხრილში, რომელსაც ეწოდება მატრიცის ფაქტორების ანალიზი. "მონაცემთა მაგიდა" ეხება ინსტრუმენტების ჯგუფს "რა, თუ" ანალიზირომელიც მოთავსებულია ლენტით ჩანართში "მონაცემები" ბლოკში "მუშაობა მონაცემებით". Excel 2007-მდე, ეს ინსტრუმენტი ეცვათ სახელით. "საძიებო მაგიდა"რომ კიდევ უფრო ზუსტად აისახა თავისი არსება, ვიდრე ამჟამინდელი სახელი.
საძიებო მაგიდა შეიძლება გამოყენებულ იქნას მრავალ შემთხვევაში. მაგალითად, ტიპიური ვარიანტია, როდესაც თქვენ უნდა გამოვთვალოთ ყოველთვიური სესხის თანხის ოდენობა კრედიტის პერიოდის განსხვავებულ ვარიანტებთან და სესხის თანხის ან კრედიტის პერიოდისა და საპროცენტო განაკვეთის მიხედვით. ეს ინსტრუმენტი შეიძლება გამოყენებულ იქნას საინვესტიციო პროექტის მოდელების ანალიზისას.
მაგრამ თქვენ ასევე უნდა იცოდეს, რომ ამ ინსტრუმენტის გადაჭარბებული გამოყენება შეიძლება გამოიწვიოს სისტემის დამუხრუჭება, რადგან მონაცემები მუდმივად ხელახლა გადაახდევინებს. აქედან გამომდინარე, მიზანშეწონილია არ გამოიყენოს ეს ინსტრუმენტი მცირე tabular arrays ამ პრობლემების მოსაგვარებლად, მაგრამ გამოიყენოს ფორმულების კოპირება შევსების მარკერის გამოყენებით.
დასაბუთებული განაცხადი "მონაცემთა ცხრილები" მხოლოდ დიდი tabular მერყეობს, როდესაც კოპირება ფორმულები შეუძლია დიდი დრო, ხოლო პროცედურის დროს გაზრდის შეცდომების ალბათობა. მაგრამ ამ შემთხვევაშიც კი რეკომენდირებულია ფორმულების ავტომატური შეჯერება გამშვები ცხრილის სპექტრში, რათა თავიდან იქნას აცილებული სისტემის ზედმეტი დატვირთვა.
ძირითადი განსხვავება მონაცემთა ცხრილის სხვადასხვა მიზნებს შორის არის გაანგარიშებაში ჩართული ცვლადების რაოდენობა: ერთი ცვლადი ან ორი.
მეთოდი 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", მაშინ მითითებულ მაგალითში არ შეიძლება გამოყენებული იქნას ყველა.
- ასე რომ, ჩვენ გავაგრძელებთ გაანგარიშებას. შეარჩიეთ უჯრედის ფურცელი, სადაც გამოითვლება გამოითვლება მნიშვნელობა. ღილაკზე დაჭერით "ჩასმა ფუნქცია".
- იწყება ფუნქციის ოსტატი. გადადით კატეგორიაში "ფინანსური", აირჩიეთ სიიდან სახელი "PLT" და დააჭირეთ ღილაკს "OK".
- ამის შემდეგ, არსებობს ფუნქციის არგუმენტების გააქტიურება.
განათავსეთ კურსორი სფეროში "ფსონი"შემდეგ დააჭირეთ საკანში ფურცელზე წლიური საპროცენტო განაკვეთის ღირებულებას. როგორც ხედავთ, მისი კოორდინატები დაუყოვნებლივ გამოჩნდება ამ სფეროში. მაგრამ, როგორც ჩვენ გვახსოვს, ჩვენ გვჭირდება ყოველთვიური კურსი და, შესაბამისად,/12).
სფეროში "კპერ" ანალოგიურად, ჩვენ შევაჯამოთ საკრედიტო საკნების კოორდინატებს. ამ შემთხვევაში არაფერია გაყოფილი.
სფეროში "ფს" თქვენ უნდა მიუთითოთ საკრედიტო ორგანოს ღირებულების შემცველი საკანში კოორდინატები. ჩვენ ამას ვაკეთებთ. ჩვენ ასევე ვიყენებთ ნიშანი კოორდინატების წინ. "-". საქმე იმაშია, რომ ფუნქცია PMT საბოლოო ჯამში, საბოლოო შედეგს აძლევს უარყოფით ნიშანს, ყოველთვიურად სესხის გადახდის დაკარგვის გათვალისწინებით. მაგრამ სიცხადე, ჩვენ გვჭირდება მონაცემები მაგიდასთან დადებითი. აქედან გამომდინარე, ჩვენ დავსვამთ ნიშანს "მინუსი" ერთი ფუნქციის არგუმენტამდე. როგორც ცნობილია, გამრავლება "მინუსი" on "მინუსი" საბოლოოდ იძლევა პლუს.
სფეროში "Bs" და "ტიპი" ჩვენ არ შევა მონაცემები. ღილაკზე დაჭერით "OK".
- ამის შემდეგ, ოპერატორი აანაზღაურებს და აჩვენებს წინასწარ განსაზღვრულ საკანში ჯამური თანხის გადახდის შედეგს - 30108,26 რუბლი. მაგრამ პრობლემა ისაა, რომ მსესხებელს შეუძლია თვეში მაქსიმუმ 29,000 რუბლი გადაუხადოს, ანუ ის უნდა იპოვოთ ბანკის მიერ დაბალი საპროცენტო განაკვეთის პირობები, ან შეამციროს სესხი ან შეღავათიანი ვადა. გამოითვალეთ სხვადასხვა ვარიანტის მოქმედება დაგვეხმარება საძიებო მაგიდაზე.
- დასაწყისისთვის გამოიყენეთ საძიებო მაგიდა ერთი ცვლადით. მოდით დავინახავთ, თუ სავალდებულო ყოველთვიური გადასახდელი ღირებულება იცვლება სხვადასხვა ვარიაციით წლიური განაკვეთის მიხედვით 9,5% წლიური და დამთავრებული 12,5% pa ნაბიჯი 0,5%. ყველა სხვა პირობები უცვლელი დარჩა. დახაზეთ ცხრილის დიაპაზონი, რომლის სვეტების სახელები შეესაბამება საპროცენტო განაკვეთის განსხვავებულ ვარიანტებს. ამ ხაზით "ყოველთვიური გადასახადი" დატოვე, როგორც ეს. მისი პირველი საკანში უნდა შეიცავდეს ფორმულას წინასწარ გაანგარიშებული. დამატებითი ინფორმაციისთვის შეგიძლიათ დაამატოთ ხაზები "სულ სესხის ოდენობა" და "საერთო ინტერესი". სვეტი, რომელშიც გაანგარიშება განლაგებულია კარის გარეშე.
- შემდეგი, ჩვენ გამოვთვალეთ სესხის საერთო ოდენობა არსებული პირობებით. ამისათვის აირჩიეთ რიგის პირველი უჯრედი. "სულ სესხის ოდენობა" და გამრავლების საკანში შინაარსი "ყოველთვიური გადასახადი" და "სესხის ვადა". ამის შემდეგ დააჭირეთ შეიყვანეთ.
- არსებული პირობების მიხედვით დაინტერესებული მთლიანი თანხის გამოთვლა, ანალოგიურად, სესხის მთლიანი თანხისაგან სესხის ღირებულების შემცირება. ეკრანის შედეგის სანახავად დააჭირეთ ღილაკს. შეიყვანეთ. ამდენად, ჩვენ თანხა, რომ ჩვენ overpay როდესაც დაბრუნების სესხი.
- ახლა დროა მიმართოს ინსტრუმენტს. "მონაცემთა მაგიდა". აირჩიეთ მთლიანი ცხრილის მასივი, გარდა ზედიზედ სახელები. ამის შემდეგ გადადით tab "მონაცემები". დაწკაპეთ ღილაკს ლენტი "რა, თუ" ანალიზირომელიც განთავსებულია იარაღის ჯგუფში "მუშაობა მონაცემებით" (Excel 2016, ჯგუფის ინსტრუმენტები "პროგნოზი"). შემდეგ პატარა მენიუ იხსნება. მასში ჩვენ შეარჩიეთ პოზიცია "მონაცემთა მაგიდა ...".
- პატარა ფანჯარა იხსნება, რომელსაც ეწოდება "მონაცემთა მაგიდა". როგორც ხედავთ, მას აქვს ორი სფერო. მას შემდეგ, რაც ჩვენ ვმუშაობთ ერთი ცვლადით, ჩვენ გვჭირდება მხოლოდ ერთი მათგანი. მას შემდეგ, რაც ჩვენი ცვლადი ცვლილებები მოხდება სვეტში, ჩვენ გამოვიყენებთ ველს "შემცვლელი ღირებულებების სვეტების მიერ". ჩვენ კურსორს მივყავართ და შემდეგ დააჭირეთ საკანში საწყისი მონაცემების კომპლექტი, რომელიც შეიცავს მიმდინარე ღირებულების პროცენტი. მას შემდეგ, რაც საკანში კოორდინატები გამოჩნდება, დააჭირეთ ღილაკს "OK".
- ინსტრუმენტი აანგარიშებს და ავსებს მთელ ცხრილს, რომლებიც შეესაბამება სხვადასხვა საპროცენტო განაკვეთის პარამეტრებს. თუ ამ კურსორის ნებისმიერ ელემენტზე კურსორის განთავსება შეგიძლიათ, ხედავთ, რომ ფორმულა ბარი არ წარმოადგენს რეგულარული გადახდის გაანგარიშების ფორმულას, მაგრამ არამომგებიანი მასივის სპეციალური ფორმულა. ანუ, შეუძლებელია ინდივიდუალური საკნების ღირებულებების შეცვლა. გაანგარიშების შედეგების წაშლა შესაძლებელია მხოლოდ ერთად და არა ცალკე.
გარდა ამისა, შეიძლება აღინიშნოს, რომ ყოველთვიური გადასახადის ღირებულება წელიწადში 12.5% -ზე, რომელიც გამოიყურება საძიებო ცხრილის გამოყენებით, შეესაბამება იმავე საპროცენტო განაკვეთის ღირებულებას, რომელსაც ჩვენ მივიღებთ ფუნქციის გამოყენებისას PMT. ეს კიდევ ერთხელ ადასტურებს გაანგარიშების სისწორეს.
ამ ცხრილის ანალიზის შემდეგ უნდა აღინიშნოს, რომ, როგორც ვხედავთ, მხოლოდ წელიწადში 9.5% -ს მივიღებთ, მივიღებთ მისაღები დონის ყოველთვიური გადასახადი (29,000 რუბლი).
გაკვეთილი: გაანგარიშება ანუიტეტი გადახდა Excel- ში
მეთოდი 2: გამოიყენეთ ინსტრუმენტი ორი ცვლადით
რასაკვირველია, ძალიან რთულია, თუ ყველა რეალისტურია, რომ იპოვოთ ბანკები, რომლებიც სესხებს გასცემენ 9.5% წელიწადში. აქედან გამომდინარე, მოდი ვნახოთ, რა ვარიანტები არსებობს სხვადასხვა ცვლადების სხვადასხვა კომბინაციებისთვის ყოველთვიური გადასახადის მისაღებად: სესხის ორგანოს ზომა და სესხის პერიოდი. ამავე დროს, საპროცენტო განაკვეთი უცვლელი რჩება (12.5%). ინსტრუმენტი დაგვეხმარება ამ ამოცანით. "მონაცემთა მაგიდა" ორი ცვლადის გამოყენებით.
- ახალი მაგიდის მასივის დახატვა. ახლა კრედიტების ვადა აღინიშნება სვეტის სახელებში (დან 2 მდე 6 წელიწადში თვეში ერთი წლით) და რიგები - სესხის ზოლის ზომა (დან 850000 მდე 950000 რუბლი in increments 10000 რუბლი). ამ შემთხვევაში აუცილებელია, რომ საკანში, სადაც გაანგარიშების ფორმულა მდებარეობს (ჩვენს შემთხვევაში) PMT), რომელიც მდებარეობს რიგისა და სვეტების საზღვრებში. ამ მდგომარეობის გარეშე, ინსტრუმენტი არ იმუშავებს ორი ცვლადის გამოყენებისას.
- შემდეგ შეარჩიეთ ყველა ცხრილის დიაპაზონი, მათ შორის სვეტების, რიგებისა და ფორმის სახელების სახელები PMT. გადადით tab- ზე "მონაცემები". როგორც წინა პერიოდში, დააჭირეთ ღილაკს. "რა, თუ" ანალიზიინსტრუმენტების ჯგუფში "მუშაობა მონაცემებით". სიაში, რომელიც იხსნება, აირჩიეთ ელემენტი "მონაცემთა მაგიდა ...".
- ინსტრუმენტი ფანჯარა იწყება. "მონაცემთა მაგიდა". ამ შემთხვევაში ჩვენ ორივე სფერო გვჭირდება. სფეროში "შემცვლელი ღირებულებების სვეტების მიერ" ჩვენ განსაზღვრავს საკანში კოორდინატები, რომელიც შეიცავს სესხის ტერმინს პირველადი მონაცემებით. სფეროში "შეცვლის ღირებულებების რიგები" მიუთითეთ სესხის ორგანოს ღირებულების შემცველი პირველადი პარამეტრების საკანში. მას შემდეგ, რაც ყველა მონაცემები შევიდა. ღილაკზე დაჭერით "OK".
- პროგრამა ასრულებს გაანგარიშებას და შეავსებს ცხრილის დიაპაზონს მონაცემებით. რიგები და სვეტების გადაკვეთაზე, შესაძლებელია, დააკვირდეს, რამდენად ზუსტად არის ყოველთვიური გადახდა, შესაბამისი წლიური საპროცენტო განაკვეთი და განსაზღვრული კრედიტის პერიოდი.
- როგორც ხედავთ, საკმაოდ ბევრი ღირებულებები. სხვა პრობლემების გადასაჭრელად შეიძლება უფრო მეტი იყოს. აქედან გამომდინარე, იმისათვის, რომ გამომავალი შედეგები უფრო ვიზუალური და დაუყოვნებლივ განსაზღვროს, თუ რომელი ღირებულებები არ აკმაყოფილებს მოცემულ მდგომარეობას, შეგიძლიათ გამოიყენოთ ვიზუალიზაცია ინსტრუმენტები. ჩვენს შემთხვევაში ეს იქნება პირობითი ფორმატირება. აირჩიეთ ცხრილის დიაპაზონის ყველა მნიშვნელობა, რიგისა და სვეტის სათაურების გარდა.
- გადატანა tab- ზე "მთავარი" და დაწკაპეთ ხატი "პირობითი ფორმატირება". იგი მდებარეობს ყუთისთვის. "სტილები" ფირზე. მენიუში რომ გახსნა, აირჩიეთ ელემენტი "საკანში შერჩევის წესები". დამატებითი სიაში დააჭირეთ პოზიციას "ნაკლები ...".
- ამის შემდეგ, პირობითი ფორმატირების პარამეტრების ფანჯარა იხსნება. მარცხენა ველის ჩვენ განსაზღვრავს ღირებულება, ნაკლებია, ვიდრე უჯრედები შეირჩევა. როგორც გვახსოვს, კმაყოფილი ვართ იმ პირობით, რომლითაც სესხის ყოველთვიური გადასახადი ნაკლები იქნება 29000 რუბლი. შეიყვანეთ ეს ნომერი. მარჯვენა სფეროში შესაძლებელია შეარჩიოთ ფერი შერჩევის ფერი, თუმცა შეგიძლიათ დატოვოთ იგი. მას შემდეგ, რაც ყველა საჭირო პარამეტრი შევიდა, დააჭირეთ ღილაკს. "OK".
- ამის შემდეგ, ყველა უჯრედი, რომლის ღირებულებებიც შეესაბამება ზემოთ მოცემულ მდგომარეობას, ხაზს უსვამს ხაზს.
ცხრილის მასივის გაანალიზების შემდეგ შეგიძლიათ დასკვნის გაკეთება. როგორც მოგეხსენებათ, არსებული სესხის პერიოდის (36 თვე) განმავლობაში, ყოველთვიური გადასახადის ზემოთ მითითებული თანხის ინვესტირების მიზნით, სესხის აღება არ უნდა აღემატებოდეს 8,600,000.00 რუბლს, რაც თავდაპირველად დაგეგმილზე 40,000-ზე ნაკლებია.
თუ ჩვენ კვლავ ვაპირებთ სესხის აღება 900,000 რუბლის ოდენობით, მაშინ სესხის ვადა უნდა იყოს 4 წელი (48 თვე). მხოლოდ ამ შემთხვევაში, ყოველთვიური თანხის ოდენობა არ უნდა აღემატებოდეს 29 000 რუბლს.
ამგვარად, ამ ტაბულური მასივის უპირატესობა და თითოეული ვარიანტის დადებითი და უარყოფითი ანალიზი, მსესხებელს შეუძლია მიიღოს კონკრეტული გადაწყვეტილება დაკრედიტების პირობებზე, აირჩიოს ვარიანტი, რომელიც საუკეთესოდ შეესაბამება მის მოთხოვნებს.
რა თქმა უნდა, საძიებო მაგიდა შეიძლება გამოყენებულ იქნას არა მხოლოდ საკრედიტო პარამეტრების გამოთვლაზე, არამედ სხვა მრავალი პრობლემის მოსაგვარებლად.
გაკვეთილი: პირობითი ფორმატირება in Excel
ზოგადად, უნდა აღინიშნოს, რომ საძიებო მაგიდა არის ძალიან სასარგებლო და შედარებით მარტივი ინსტრუმენტი ცვლადების სხვადასხვა კომბინაციების განსაზღვრისათვის. ამასთანავე პირობითი ფორმატირების გამოყენებით, შეგიძლიათ მიიღოთ ინფორმაცია მიღებული ინფორმაცია.
