ეს არის ყველაზე გავრცელებული გზა ზოგიერთი ცვლადის სხვაზე დამოკიდებულების საჩვენებლად, მაგალითად, როგორ ხდება მშპ დონეზომიდან უცხოური ინვესტიციაან დან ეროვნული ბანკის დაკრედიტების განაკვეთიან დან ძირითადი ენერგორესურსების ფასები.

მოდელირება საშუალებას გაძლევთ აჩვენოთ ამ დამოკიდებულების სიდიდე (კოეფიციენტები), რომლის წყალობითაც შეგიძლიათ პირდაპირ გააკეთოთ პროგნოზი და განახორციელოთ რაიმე სახის დაგეგმვა ამ პროგნოზებზე დაყრდნობით. ასევე, რეგრესიული ანალიზის საფუძველზე შესაძლებელია მენეჯმენტის გადაწყვეტილებების მიღება, რომლებიც მიზნად ისახავს პრიორიტეტული მიზეზების სტიმულირებას, რომლებიც გავლენას ახდენენ საბოლოო შედეგზე.

ხაზოვანი რეგრესიის მოდელის ზოგადი ხედი:

Y=a 0 +a 1 x 1 +...+a k x k

სად ა - რეგრესიის პარამეტრები (კოეფიციენტები), x - გავლენის ფაქტორები, კ - მოდელის ფაქტორების რაოდენობა.

საწყისი მონაცემები

საწყის მონაცემებს შორის, ჩვენ გვჭირდება მონაცემთა გარკვეული ნაკრები, რომელიც წარმოადგენს Y საბოლოო პარამეტრის რამდენიმე თანმიმდევრულ ან ურთიერთდაკავშირებულ მნიშვნელობას (მაგალითად, მშპ) და იმ ინდიკატორების მნიშვნელობების იგივე რაოდენობას, რომელთა გავლენას ჩვენ ვსწავლობთ ( მაგალითად, უცხოური ინვესტიცია).

ზემოთ მოყვანილი ფიგურა გვიჩვენებს ცხრილს იგივე საწყისი მონაცემებით, Y არის ეკონომიკურად აქტიური მოსახლეობის მაჩვენებელი, ხოლო საწარმოების რაოდენობა, ინვესტიციების მოცულობა და შინამეურნეობის შემოსავალი არის გავლენის ფაქტორები, ანუ X-ები.

ფიგურიდან გამომდინარე, ასევე შეიძლება გაკეთდეს მცდარი დასკვნა, რომ მოდელირება შეიძლება იყოს მხოლოდ დროის სერიებზე, ანუ დროში თანმიმდევრულად ჩაწერილი მომენტების სერიებზე, მაგრამ ეს ასე არ არის იგივე წარმატებით, შეიძლება მოდელირება ა სტრუქტურა, მაგალითად, ცხრილში მითითებული მნიშვნელობები შეიძლება დაიშალოს არა წლის, არამედ რეგიონის მიხედვით.

ადეკვატური ხაზოვანი მოდელების ასაგებად, სასურველია, რომ წყაროს მონაცემებს არ ჰქონდეს ძლიერი ვარდნა ან ჩამონგრევა ასეთ შემთხვევებში, მიზანშეწონილია განახორციელოს დაგლუვება, მაგრამ შემდეგ ჯერზე ვისაუბრებთ.

ანალიზის პაკეტი

ხაზოვანი რეგრესიის მოდელის პარამეტრები ასევე შეიძლება გამოითვალოს ხელით ჩვეულებრივი უმცირესი კვადრატების მეთოდის (OLS) გამოყენებით, მაგრამ ეს საკმაოდ შრომატევადია. ეს შეიძლება გამოითვალოს ცოტა უფრო სწრაფად იმავე მეთოდის გამოყენებით Excel-ში ფორმულების გამოყენებით, სადაც თავად პროგრამა გააკეთებს გამოთვლებს, მაგრამ თქვენ მაინც მოგიწევთ ფორმულების ხელით შეყვანა.

Excel-ს აქვს დანამატი ანალიზის პაკეტი, რომელიც საკმაოდ ძლიერი ინსტრუმენტია ანალიტიკოსის დასახმარებლად. ამ ხელსაწყოთა კომპლექტს, სხვა საკითხებთან ერთად, შეუძლია გამოთვალოს რეგრესიის პარამეტრები იგივე უმცირესი კვადრატების მეთოდით, მხოლოდ რამდენიმე დაწკაპუნებით, ფაქტობრივად, როგორ გამოვიყენოთ ეს ინსტრუმენტი შემდგომში იქნება განხილული.

გააქტიურეთ ანალიზის პაკეტი

ნაგულისხმევად, ეს დანამატი გამორთულია და თქვენ ვერ იპოვით მას ჩანართის მენიუში, ამიტომ ჩვენ ეტაპობრივად განვიხილავთ როგორ გავააქტიუროთ იგი.

Excel-ში, მარცხენა ზედა ნაწილში, გააქტიურეთ ჩანართი ფაილი, მენიუში, რომელიც იხსნება, მოძებნეთ ელემენტი ოფციებიდა დააწკაპუნეთ მასზე.

ფანჯარაში, რომელიც იხსნება, მარცხნივ, მოძებნეთ ელემენტი დანამატებიდა გაააქტიურეთ, ამ ჩანართში ბოლოში იქნება ჩამოსაშლელი საკონტროლო სია, სადაც ნაგულისხმევად დაიწერება Excel დანამატებიჩამოსაშლელი სიის მარჯვნივ იქნება ღილაკი წადი, თქვენ უნდა დააჭიროთ მასზე.

ამომხტარი ფანჯარა მოგთხოვთ აირჩიოთ ხელმისაწვდომი დანამატები, თქვენ უნდა მონიშნოთ ყუთი ანალიზის პაკეტიდა ამავე დროს, ყოველი შემთხვევისთვის, გამოსავლის პოვნა(ასევე სასარგებლო რამ) და შემდეგ დაადასტურეთ თქვენი არჩევანი ღილაკზე დაჭერით OK.

ანალიზის პაკეტის გამოყენებით წრფივი რეგრესიის პარამეტრების მოძებნის ინსტრუქციები

Analysis Pack დანამატის გააქტიურების შემდეგ ის ყოველთვის ხელმისაწვდომი იქნება მთავარი მენიუს ჩანართში მონაცემებიბმულის ქვეშ მონაცემთა ანალიზი

აქტიური ხელსაწყოს ფანჯარაში მონაცემთა ანალიზიჩვენ მიერ მოძიებული და ვირჩევთ შესაძლებლობების სიიდან რეგრესია

შემდეგი, გაიხსნება ფანჯარა რეგრესიის მოდელის პარამეტრების გამოსათვლელად წყაროს მონაცემების დაყენებისა და არჩევისთვის. აქ თქვენ უნდა მიუთითოთ საწყისი მონაცემების ინტერვალები, კერძოდ, აღწერილი პარამეტრი (Y) და მასზე მოქმედი ფაქტორები (X), როგორც ეს ნაჩვენებია ქვემოთ მოცემულ ფიგურაში, პრინციპში, კონფიგურაცია არჩევითია.

მას შემდეგ რაც აირჩიეთ წყაროს მონაცემები და დააწკაპუნეთ OK ღილაკზე, Excel აწარმოებს გამოთვლებს აქტიური სამუშაო წიგნის ახალ ფურცელზე (თუ სხვაგვარად არ არის მითითებული პარამეტრებში), ეს გამოთვლები ასე გამოიყურება:

საკვანძო უჯრები ივსება ყვითლად, პირველ რიგში, მნიშვნელოვანია სხვა მნიშვნელობის პარამეტრებიც, მაგრამ მათი დეტალური ანალიზი, ალბათ, ცალკე პოსტს მოითხოვს.

ასე რომ, 0,865 - ეს R 2- განსაზღვრის კოეფიციენტი, რომელიც აჩვენებს, რომ მოდელის გამოთვლილი პარამეტრების 86,5%, ანუ თავად მოდელი, ხსნის შესწავლილ პარამეტრში დამოკიდებულებას და ცვლილებებს - იშესწავლილი ფაქტორებიდან - X-ები. თუ გადაჭარბებულია, მაშინ ეს არის მოდელის ხარისხის მაჩვენებელიდა რაც უფრო მაღალია, მით უკეთესი. ნათელია, რომ ის არ შეიძლება იყოს 1-ზე მეტი და ითვლება კარგად, როდესაც R 2 0.8-ზე მეტია, ხოლო თუ ის 0.5-ზე ნაკლებია, მაშინ ასეთი მოდელის გონივრულობა შეიძლება უსაფრთხოდ დადგეს ეჭვქვეშ.

ახლა გადავიდეთ მოდელის კოეფიციენტები:
2079,85 - ეს a 0- კოეფიციენტი, რომელიც აჩვენებს, რა იქნება Y, თუ მოდელში გამოყენებული ყველა ფაქტორი ტოლია 0-ის, გასაგებია, რომ ეს არის დამოკიდებულება სხვა ფაქტორებზე, რომლებიც არ არის აღწერილი მოდელში;
-0,0056 - a 1- კოეფიციენტი, რომელიც აჩვენებს x 1 ფაქტორის გავლენის წონას Y-ზე, ანუ მოცემულ მოდელში საწარმოების რაოდენობა გავლენას ახდენს ეკონომიკურად აქტიური მოსახლეობის ინდიკატორზე მხოლოდ -0.0056 წონით (გავლენის საკმაოდ მცირე ხარისხი ). მინუს ნიშანი აჩვენებს, რომ ეს გავლენა უარყოფითია, ანუ რაც მეტია საწარმო, მით ნაკლებია ეკონომიკურად აქტიური მოსახლეობა, რაც არ უნდა პარადოქსული იყოს ეს მნიშვნელობით;
-0,0026 - a 2- ეკონომიკურად აქტიური მოსახლეობის რაოდენობაზე კაპიტალის მოცულობის გავლენის კოეფიციენტი, ეს გავლენაც უარყოფითია;
0,0028 - a 3- მოსახლეობის შემოსავლის გავლენის კოეფიციენტი ეკონომიკურად აქტიური მოსახლეობის ზომაზე, აქ გავლენა დადებითია, ანუ მოდელის მიხედვით, შემოსავლის ზრდა ხელს შეუწყობს ეკონომიკურად აქტიური მოსახლეობის რაოდენობის ზრდას.

მოდით შევკრიბოთ გამოთვლილი კოეფიციენტები მოდელში:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

სინამდვილეში, ეს არის ხაზოვანი რეგრესიის მოდელი, რომელიც მაგალითში გამოყენებული საწყისი მონაცემებისთვის ზუსტად ასე გამოიყურება.

მოდელის შეფასებები და პროგნოზი

როგორც ზემოთ უკვე განვიხილეთ, მოდელი აგებულია არა მხოლოდ იმისთვის, რომ აჩვენოს შესწავლილი პარამეტრის დამოკიდებულების სიდიდე გავლენის ფაქტორებზე, არამედ ისე, რომ ამ გავლენის ფაქტორების ცოდნით, შესაძლებელი იყოს პროგნოზის გაკეთება. ამ პროგნოზის გაკეთება საკმაოდ მარტივია, თქვენ უბრალოდ უნდა შეცვალოთ გავლენის ფაქტორების მნიშვნელობები მიღებული მოდელის განტოლებაში შესაბამისი X-ების ნაცვლად. ქვემოთ მოცემულ ფიგურაში, ეს გამოთვლები შესრულებულია Excel-ში ცალკე სვეტში.

ფაქტობრივი მნიშვნელობები (ის, რაც მოხდა სინამდვილეში) და გამოთვლილი მნიშვნელობები მოდელის მიხედვით იმავე ფიგურაში ნაჩვენებია გრაფიკების სახით, რათა აჩვენოს განსხვავება და, შესაბამისად, მოდელის შეცდომა.

კიდევ ერთხელ ვიმეორებ, მოდელის გამოყენებით პროგნოზის გასაკეთებლად აუცილებელია, რომ იყოს ცნობილი გავლენის ფაქტორები და თუ ვსაუბრობთ დროის სერიაზე და, შესაბამისად, მომავლის პროგნოზზე, მაგალითად, შემდეგზე. წელი ან თვე, მაშინ ყოველთვის არ არის შესაძლებელი იმის გარკვევა, თუ რა გავლენას მოახდენს ამ მომავალში. ასეთ შემთხვევებში აუცილებელია გავლენის ფაქტორების პროგნოზის გაკეთება ყველაზე ხშირად ეს ხდება ავტორეგრესიული მოდელის გამოყენებით - მოდელი, რომელშიც გავლენის ფაქტორები არის შესასწავლი ობიექტი და დრო, ანუ ინდიკატორის დამოკიდებულება; მოდელირებულია იმაზე, თუ რა იყო წარსულში.

ჩვენ განვიხილავთ, თუ როგორ უნდა ავაშენოთ ავტორეგრესიული მოდელი შემდეგ სტატიაში, მაგრამ ახლა დავუშვათ, რომ ჩვენ ვიცით, როგორი იქნება გავლენის ფაქტორების მნიშვნელობები მომავალ პერიოდში (მაგალითად, 2008) და ამ მნიშვნელობების ჩანაცვლებით. გამოთვლებში მივიღებთ ჩვენს პროგნოზს 2008 წლისთვის.

MS Excel პაკეტი საშუალებას გაძლევთ შეასრულოთ სამუშაოს უმეტესი ნაწილი ძალიან სწრაფად ხაზოვანი რეგრესიის განტოლების აგებისას. მნიშვნელოვანია გვესმოდეს, თუ როგორ უნდა მოხდეს მიღებული შედეგების ინტერპრეტაცია. რეგრესიის მოდელის ასაგებად, თქვენ უნდა აირჩიოთ Tools\Data Analysis\Regression (Excel 2007-ში ეს რეჟიმი არის მონაცემთა/მონაცემთა ანალიზი/რეგრესიის ბლოკში). შემდეგ დააკოპირეთ შედეგები ბლოკში ანალიზისთვის.

28 ოქტ

შუადღე მშვიდობისა, ძვირფასო ბლოგის მკითხველებო! დღეს ჩვენ ვისაუბრებთ არაწრფივი რეგრესიებზე. ხაზოვანი რეგრესიის ამოხსნა შეგიძლიათ იხილოთ LINK-ზე.

ეს მეთოდი ძირითადად გამოიყენება ეკონომიკურ მოდელირებასა და პროგნოზირებაში. მისი მიზანია დააკვირდეს და გამოავლინოს დამოკიდებულებები ორ ინდიკატორს შორის.

არაწრფივი რეგრესიების ძირითადი ტიპებია:

• მრავალწევრი (კვადრატული, კუბური);
• ჰიპერბოლური;
• დამამშვიდებელი;
• დემონსტრაციული;
• ლოგარითმული

ასევე შეიძლება გამოყენებულ იქნას სხვადასხვა კომბინაციები. მაგალითად, საბანკო, სადაზღვევო და დემოგრაფიულ კვლევებში დროის სერიების ანალიტიკისთვის გამოიყენება გომფცერის მრუდი, რომელიც წარმოადგენს ლოგარითმული რეგრესიის ტიპს.

არაწრფივი რეგრესიების გამოყენებით პროგნოზირებისას მთავარია გავარკვიოთ კორელაციის კოეფიციენტი, რომელიც გვაჩვენებს არის თუ არა მჭიდრო კავშირი ორ პარამეტრს შორის. როგორც წესი, თუ კორელაციის კოეფიციენტი ახლოს არის 1-თან, მაშინ არის კავშირი და პროგნოზი საკმაოდ ზუსტი იქნება. არაწრფივი რეგრესიების კიდევ ერთი მნიშვნელოვანი ელემენტია საშუალო ფარდობითი შეცდომა ( ა ), თუ ინტერვალშია<8…10%, значит модель достаточно точна.

სწორედ აქ დავასრულებთ ალბათ თეორიულ ბლოკს და გადავალთ პრაქტიკულ გამოთვლებზე.

გვაქვს მანქანების გაყიდვების ცხრილი 15 წლის განმავლობაში (მოდით ავღნიშნოთ X), საზომი ნაბიჯების რაოდენობა იქნება არგუმენტი n, გვაქვს შემოსავალიც ამ პერიოდებისთვის (მოდით ავღნიშნოთ Y), უნდა ვიწინასწარმეტყველოთ რა შემოსავალი იქნება მომავალში. ავაშენოთ შემდეგი ცხრილი:

კვლევისთვის დაგვჭირდება განტოლების ამოხსნა (Y-ის დამოკიდებულება X-ზე): y=ax 2 +bx+c+e. ეს არის წყვილი კვადრატული რეგრესია. ამ შემთხვევაში უცნობი არგუმენტების გასარკვევად ვიყენებთ უმცირეს კვადრატების მეთოდს - a, b, c. ეს გამოიწვევს ფორმის ალგებრული განტოლებების სისტემას:

ამ სისტემის გადასაჭრელად, ჩვენ გამოვიყენებთ, მაგალითად, კრამერის მეთოდს. ჩვენ ვხედავთ, რომ სისტემაში შემავალი ჯამები არის კოეფიციენტები უცნობისთვის. მათ გამოსათვლელად ცხრილს დავამატებთ რამდენიმე სვეტს (D,E,F,G,H) და ვაწერთ ხელს გამოთვლების მნიშვნელობის მიხედვით - D სვეტში გავა კვადრატში x, E-ში კუბურები, F-ში. ჩვენ გავამრავლებთ x და y მაჩვენებლებს, H-ში ვა კვადრატში x და გავამრავლებთ y-ზე.

თქვენ მიიღებთ ფორმის ცხრილს, რომელიც შევსებულია განტოლების ამოსახსნელად საჭირო ნივთებით.

შევქმნათ მატრიცა ა სისტემა, რომელიც შედგება განტოლებების მარცხენა მხარეს უცნობი უცნობის კოეფიციენტებისგან. მოვათავსოთ ის A22 უჯრედში და ვუწოდოთ " A=". ჩვენ მივყვებით განტოლებათა სისტემას, რომელიც ავირჩიეთ რეგრესიის ამოსახსნელად.

ანუ B21 უჯრედში უნდა მოვათავსოთ იმ სვეტის ჯამი, სადაც X ინდიკატორი ავწიეთ მეოთხე ხარისხზე - F17. მოდით მივმართოთ უჯრედს - "=F17". შემდეგი, ჩვენ გვჭირდება იმ სვეტის ჯამი, სადაც X იყო კუბურები - E17, შემდეგ მკაცრად მივდივართ სისტემის მიხედვით. ამრიგად, ჩვენ დაგვჭირდება მთელი მატრიცის შევსება.

კრამერის ალგორითმის მიხედვით აკრიფებთ A-ს მსგავს A1 მატრიცას, რომელშიც პირველი სვეტის ელემენტების ნაცვლად უნდა განთავსდეს სისტემის განტოლებების მარჯვენა მხარის ელემენტები. ანუ X სვეტის ჯამი კვადრატში გამრავლებული Y-ზე, XY სვეტის ჯამი და Y სვეტის ჯამი.

ასევე დაგვჭირდება კიდევ ორი ​​მატრიცა - დავარქვათ მათ A2 და A3, რომლებშიც მეორე და მესამე სვეტები შედგება განტოლებების მარჯვენა მხარის კოეფიციენტებისგან. სურათი ასეთი იქნება.

არჩეული ალგორითმის შემდეგ, ჩვენ უნდა გამოვთვალოთ მიღებული მატრიცების დეტერმინანტების (განმსაზღვრელი, D) მნიშვნელობები. მოდით გამოვიყენოთ MOPRED ფორმულა. შედეგებს განვათავსებთ უჯრედებში J21:K24.

ჩვენ გამოვთვლით განტოლების კოეფიციენტებს კრამერის მიხედვით შესაბამისი დეტერმინანტების მოპირდაპირე უჯრედებში ფორმულის გამოყენებით: ა(უჯრედში M22) - “=K22/K21”; ბ(უჯრედში M23) - “=K23/K21”; თან(უჯრედში M24) - "=K24/K21".

ჩვენ ვიღებთ დაწყვილებული კვადრატული რეგრესიის სასურველ განტოლებას:

y=-0,074x 2 +2,151x+6,523

მოდით შევაფასოთ წრფივი ურთიერთობის სიახლოვე კორელაციის ინდექსის გამოყენებით.

გამოსათვლელად დაამატეთ ცხრილს დამატებითი სვეტი J (მოდით დავარქვათ y*). გაანგარიშება იქნება შემდეგი (ჩვენს მიერ მიღებული რეგრესიის განტოლების მიხედვით) - „=$m$22*B2*B2+$M$23*B2+$M$24“.მოვათავსოთ ის J2 უჯრედში. რჩება მხოლოდ ავტომატური შევსების მარკერის გადატანა J16 უჯრედში.

ჯამების გამოსათვლელად (Y-Y საშუალო) 2, დაამატეთ K და L სვეტები ცხრილში შესაბამისი ფორმულებით. ჩვენ ვიანგარიშებთ საშუალოს Y სვეტისთვის AVERAGE ფუნქციის გამოყენებით.

K25 უჯრედში განვათავსებთ კორელაციის ინდექსის გამოთვლის ფორმულას - “=ROOT(1-(K17/L17))”.

ჩვენ ვხედავთ, რომ 0.959-ის მნიშვნელობა ძალიან ახლოს არის 1-თან, რაც ნიშნავს, რომ არსებობს მჭიდრო არაწრფივი კავშირი გაყიდვებსა და წლებს შორის.

რჩება მიღებული კვადრატული რეგრესიის განტოლების (დეტერმინაციის ინდექსი) მორგების ხარისხის შეფასება. იგი გამოითვლება კვადრატული კორელაციის ინდექსის ფორმულის გამოყენებით. ანუ, ფორმულა K26 უჯრედში იქნება ძალიან მარტივი - "=K25*K25".

კოეფიციენტი 0,920 უახლოვდება 1-ს, რაც მიუთითებს მორგების მაღალ ხარისხზე.

ბოლო ნაბიჯი არის ფარდობითი შეცდომის გამოთვლა. დავამატოთ სვეტი და შევიყვანოთ ფორმულა იქ: “=ABS((C2-J2)/C2), ABS - მოდული, აბსოლუტური მნიშვნელობა. დახატეთ მარკერი ქვემოთ და M18 უჯრედში აჩვენეთ საშუალო მნიშვნელობა (AVERAGE), მიანიშნეთ უჯრედებს პროცენტული ფორმატი. მიღებული შედეგი - 7,79% არის მისაღები ცდომილების მნიშვნელობების ფარგლებში<8…10%. Значит вычисления достаточно точны.

საჭიროების შემთხვევაში, შეგვიძლია ავაშენოთ გრაფიკი მიღებული მნიშვნელობების გამოყენებით.

მიმაგრებულია ფაილის მაგალითი - ბმული!

რეგრესია და კორელაციური ანალიზი სტატისტიკური კვლევის მეთოდებია. ეს არის პარამეტრის დამოკიდებულების ჩვენების ყველაზე გავრცელებული გზები ერთ ან რამდენიმე დამოუკიდებელ ცვლადზე.

ქვემოთ, კონკრეტული პრაქტიკული მაგალითების გამოყენებით, განვიხილავთ ამ ორ ძალიან პოპულარულ ანალიზს ეკონომისტებს შორის. ჩვენ ასევე მივცემთ შედეგების მიღების მაგალითს მათი გაერთიანებისას.

რეგრესიული ანალიზი Excel-ში

აჩვენებს ზოგიერთი მნიშვნელობის (დამოუკიდებელი, დამოუკიდებელი) გავლენას დამოკიდებულ ცვლადზე. მაგალითად, რამდენად არის დამოკიდებული ეკონომიკურად აქტიური მოსახლეობის რაოდენობა საწარმოების რაოდენობაზე, ხელფასებზე და სხვა პარამეტრებზე. ან: როგორ მოქმედებს მშპ-ის დონეზე უცხოური ინვესტიციები, ენერგიის ფასები და ა.შ.

ანალიზის შედეგი საშუალებას გაძლევთ გამოყოთ პრიორიტეტები. და ძირითადი ფაქტორებიდან გამომდინარე, იწინასწარმეტყველეთ, დაგეგმეთ პრიორიტეტული სფეროების განვითარება და მიიღეთ მენეჯმენტის გადაწყვეტილებები.

რეგრესია ხდება:

• წრფივი (y = a + bx);
• პარაბოლური (y ​​= a + bx + cx 2);
• ექსპონენციალური (y ​​= a * exp(bx));
• სიმძლავრე (y = a*x^b);
• ჰიპერბოლური (y ​​= b/x + a);
• ლოგარითმული (y = b * 1n(x) + a);
• ექსპონენციალური (y ​​= a * b^x).

მოდით შევხედოთ Excel-ში რეგრესიის მოდელის აგების მაგალითს და შედეგების ინტერპრეტაციას. ავიღოთ რეგრესიის ხაზოვანი ტიპი.

დავალება. 6 საწარმოში გაანალიზდა საშუალო თვიური ხელფასი და თანამშრომელთა თანამდებობიდან გათავისუფლების რაოდენობა. აუცილებელია განისაზღვროს თანამშრომელთა თანამშრომელთა რაოდენობის დამოკიდებულება საშუალო ხელფასზე.

ხაზოვანი რეგრესიის მოდელი ასე გამოიყურება:

Y = a 0 + a 1 x 1 +…+a k x k.

სადაც a არის რეგრესიის კოეფიციენტები, x არის გავლენიანი ცვლადები, k არის ფაქტორების რაოდენობა.

ჩვენს მაგალითში, Y არის თანამშრომლების დატოვების მაჩვენებელი. გავლენის ფაქტორი არის ხელფასი (x).

Excel-ს აქვს ჩაშენებული ფუნქციები, რომლებიც დაგეხმარებათ გამოთვალოთ ხაზოვანი რეგრესიის მოდელის პარამეტრები. მაგრამ „ანალიზის პაკეტის“ დანამატი ამას უფრო სწრაფად გააკეთებს.

ჩვენ ვააქტიურებთ ძლიერ ანალიტიკურ ინსტრუმენტს:

გააქტიურების შემდეგ, დანამატი ხელმისაწვდომი იქნება მონაცემთა ჩანართში.

ახლა მოდით გავაკეთოთ თავად რეგრესიული ანალიზი.

უპირველეს ყოვლისა, ყურადღებას ვაქცევთ R-კვადრატსა და კოეფიციენტებს.

R-კვადრატი არის განსაზღვრის კოეფიციენტი. ჩვენს მაგალითში – 0,755, ანუ 75,5%. ეს ნიშნავს, რომ მოდელის გამოთვლილი პარამეტრები ხსნის შესწავლილ პარამეტრებს შორის კავშირის 75.5%-ს. რაც უფრო მაღალია განსაზღვრის კოეფიციენტი, მით უკეთესია მოდელი. კარგი - 0.8-ზე მეტი. ცუდი - 0,5-ზე ნაკლები (ასეთი ანალიზი ძნელად შეიძლება ჩაითვალოს გონივრულად). ჩვენს მაგალითში - "ცუდი არ არის".

კოეფიციენტი 64.1428 გვიჩვენებს, თუ რა იქნება Y, თუ განსახილველ მოდელში ყველა ცვლადი 0-ის ტოლია. ანუ, გაანალიზებული პარამეტრის მნიშვნელობაზე ასევე გავლენას ახდენს სხვა ფაქტორები, რომლებიც არ არის აღწერილი მოდელში.

კოეფიციენტი -0,16285 გვიჩვენებს X ცვლადის წონას Y-ზე. ანუ, საშუალო თვიური ხელფასი ამ მოდელის ფარგლებში გავლენას ახდენს თანამდებობიდან გათავისუფლების მაცხოვრებლების რაოდენობაზე წონით -0,16285 (ეს გავლენის მცირე ხარისხია). ნიშანი "-" მიუთითებს უარყოფით ზემოქმედებაზე: რაც უფრო მაღალია ხელფასი, მით უფრო ნაკლები ადამიანი ტოვებს თავს. რაც სამართლიანია.

კორელაციური ანალიზი Excel-ში

კორელაციური ანალიზი გვეხმარება იმის დადგენაში, არის თუ არა კავშირი ინდიკატორებს შორის ერთ ან ორ ნიმუშში. მაგალითად, მანქანის მუშაობის დროსა და შეკეთების ღირებულებას შორის, აღჭურვილობის ფასსა და მუშაობის ხანგრძლივობას, ბავშვების სიმაღლესა და წონას და ა.შ.

თუ კავშირი არსებობს, მაშინ ერთი პარამეტრის ზრდა იწვევს მეორის ზრდას (დადებითი კორელაცია) ან შემცირებას (უარყოფითს). კორელაციური ანალიზი ანალიტიკოსს ეხმარება განსაზღვროს, შეიძლება თუ არა ერთი ინდიკატორის მნიშვნელობის გამოყენება მეორის შესაძლო მნიშვნელობის პროგნოზირებისთვის.

კორელაციის კოეფიციენტი აღინიშნება r-ით. მერყეობს +1-დან -1-მდე. სხვადასხვა სფეროსთვის კორელაციების კლასიფიკაცია განსხვავებული იქნება. როდესაც კოეფიციენტი არის 0, ნიმუშებს შორის წრფივი კავშირი არ არსებობს.

მოდით შევხედოთ როგორ მოვძებნოთ კორელაციის კოეფიციენტი Excel-ის გამოყენებით.

დაწყვილებული კოეფიციენტების საპოვნელად გამოიყენება CORREL ფუნქცია.

მიზანი: დაადგინეთ არის თუ არა კავშირი ქარხნის მუშაობის დროსა და მისი შენარჩუნების ღირებულებას შორის.

მოათავსეთ კურსორი ნებისმიერ უჯრედში და დააჭირეთ fx ღილაკს.

1. "სტატისტიკური" კატეგორიაში აირჩიეთ CORREL ფუნქცია.
2. არგუმენტი "მასივი 1" - მნიშვნელობების პირველი დიაპაზონი - მანქანის მუშაობის დრო: A2:A14.
3. არგუმენტი "მასივი 2" - მნიშვნელობების მეორე დიაპაზონი - შეკეთების ღირებულება: B2:B14. დააწკაპუნეთ OK.

კავშირის ტიპის დასადგენად, თქვენ უნდა დაათვალიეროთ კოეფიციენტის აბსოლუტური რაოდენობა (საქმიანობის თითოეულ სფეროს აქვს თავისი მასშტაბი).

რამდენიმე პარამეტრის (2-ზე მეტი) კორელაციური ანალიზისთვის უფრო მოსახერხებელია „მონაცემთა ანალიზის“ გამოყენება (დამატება „ანალიზის პაკეტი“). თქვენ უნდა აირჩიოთ კორელაცია სიიდან და მიუთითოთ მასივი. ყველა.

შედეგად მიღებული კოეფიციენტები ნაჩვენები იქნება კორელაციის მატრიცაში. მოსწონს ეს:

კორელაციური და რეგრესიული ანალიზი

პრაქტიკაში, ეს ორი ტექნიკა ხშირად გამოიყენება ერთად.

მაგალითი:

ახლა რეგრესიის ანალიზის მონაცემები ხილული გახდა.

ერთ-ერთი ინდიკატორი, რომელიც აღწერს აგებული მოდელის ხარისხს სტატისტიკაში, არის განსაზღვრის კოეფიციენტი (R^2), რომელსაც ასევე უწოდებენ მიახლოების სანდოობის მნიშვნელობას. მისი გამოყენება შესაძლებელია პროგნოზის სიზუსტის დონის დასადგენად. მოდით გავარკვიოთ, როგორ გამოვთვალოთ ეს მაჩვენებელი Excel-ის სხვადასხვა ხელსაწყოების გამოყენებით.

განსაზღვრის კოეფიციენტის დონიდან გამომდინარე, ჩვეულებრივია მოდელების დაყოფა სამ ჯგუფად:

• 0.8 – 1 – კარგი ხარისხის მოდელი;
• 0.5 – 0.8 – მისაღები ხარისხის მოდელი;
• 0 – 0.5 – უხარისხო მოდელი.

ამ უკანასკნელ შემთხვევაში მოდელის ხარისხი მიუთითებს მისი პროგნოზირებისთვის გამოყენების შეუძლებლობაზე.

არჩევანი, თუ როგორ გამოთვლის Excel მითითებულ მნიშვნელობას, დამოკიდებულია იმაზე, არის თუ არა რეგრესია წრფივი. პირველ შემთხვევაში, შეგიძლიათ გამოიყენოთ ფუნქცია KVPIERSON, ხოლო მეორეში მოგიწევთ სპეციალური ინსტრუმენტის გამოყენება ანალიზის პაკეტიდან.

მეთოდი 1: წრფივი ფუნქციის განსაზღვრის კოეფიციენტის გამოთვლა

უპირველეს ყოვლისა, მოდით გავარკვიოთ, როგორ ვიპოვოთ წრფივი ფუნქციის განსაზღვრის კოეფიციენტი. ამ შემთხვევაში ეს მაჩვენებელი კორელაციის კოეფიციენტის კვადრატის ტოლი იქნება. მოდით გამოვთვალოთ ის ჩაშენებული Excel ფუნქციის გამოყენებით კონკრეტული ცხრილის მაგალითის გამოყენებით, რომელიც მოცემულია ქვემოთ.

მეთოდი 2: განსაზღვრის კოეფიციენტის გამოთვლა არაწრფივი ფუნქციებში

მაგრამ ზემოთ მოყვანილი ვარიანტი სასურველი მნიშვნელობის გამოსათვლელად შეიძლება გამოყენებულ იქნას მხოლოდ წრფივ ფუნქციებზე. რა უნდა გააკეთოთ მის გამოსათვლელად არაწრფივი ფუნქციაში? Excel-საც აქვს ეს ვარიანტი. ეს შეიძლება გაკეთდეს ხელსაწყოს გამოყენებით "რეგრესია", რომელიც პაკეტის ნაწილია "მონაცემთა ანალიზი".

1. მაგრამ სანამ ამ ხელსაწყოს გამოიყენებთ, თქვენ თვითონ უნდა გაააქტიუროთ იგი "ანალიზის პაკეტი", რომელიც ნაგულისხმევად გამორთულია Excel-ში. ჩანართზე გადასვლა "ფაილი", და შემდეგ გადადით პუნქტზე "ოფციები".



2. ფანჯარაში, რომელიც იხსნება, გადადით განყოფილებაში "დამატებები"მარცხენა ვერტიკალური მენიუს ნავიგაციით. ფანჯრის მარჯვენა უბნის ბოლოში არის ველი "კონტროლი". იქ არსებული ქვესექციების სიიდან აირჩიეთ სახელი "Excel-ის დანამატები...", და შემდეგ დააჭირეთ ღილაკს "წადი...", მდებარეობს ველის მარჯვნივ.



3. იხსნება დანამატების ფანჯარა. მის ცენტრალურ ნაწილში არის ხელმისაწვდომი დანამატების სია. შეამოწმეთ ყუთი პოზიციის გვერდით "ანალიზის პაკეტი". ამის შემდეგ თქვენ უნდა დააჭიროთ ღილაკს "კარგი"ფანჯრის ინტერფეისის მარჯვენა მხარეს.



4. ხელსაწყოების პაკეტი "მონაცემთა ანალიზი" Excel-ის მიმდინარე ინსტანციაში გააქტიურდება. მასზე წვდომა განლაგებულია ჩანართში არსებულ ლენტაზე "მონაცემები". გადადით მითითებულ ჩანართზე და დააჭირეთ ღილაკს "მონაცემთა ანალიზი"პარამეტრების ჯგუფში "ანალიზი".



5. ფანჯარა გააქტიურებულია "მონაცემთა ანალიზი"სპეციალიზებული ინფორმაციის დამუშავების ხელსაწყოების ჩამონათვალით. ჩვენ ამ სიიდან ვირჩევთ ნივთს "რეგრესია"და დააჭირეთ ღილაკს "კარგი".



6. შემდეგ იხსნება ინსტრუმენტის ფანჯარა "რეგრესია". პარამეტრების პირველი ბლოკი - "შეყვანილი მონაცემები". აქ ორ ველში თქვენ უნდა მიუთითოთ დიაპაზონის მისამართები, სადაც არგუმენტი და ფუნქციის მნიშვნელობებია განთავსებული. მოათავსეთ კურსორი ველში "შეყვანის ინტერვალი Y"და ფურცელზე მონიშნეთ სვეტის შინაარსი "Y". მას შემდეგ, რაც მასივის მისამართი გამოჩნდება ფანჯარაში "რეგრესია", მოათავსეთ კურსორი ველში "შეყვანის ინტერვალი Y"და აირჩიეთ სვეტის უჯრედები ზუსტად იმავე გზით "X".

   პარამეტრების შესახებ "მარკი"და "მუდმივი ნული"ჩვენ არ ვამოწმებთ ყუთებს. ჩამრთველი შეიძლება დაყენდეს პარამეტრის გვერდით "სანდოობის დონე"ხოლო მოპირდაპირე ველში მიუთითეთ შესაბამისი ინდიკატორის სასურველი მნიშვნელობა (ნაგულისხმევად 95%).

   ჯგუფში "გამომავალი პარამეტრები"თქვენ უნდა მიუთითოთ, რომელ ზონაში იქნება ნაჩვენები გაანგარიშების შედეგი. არსებობს სამი ვარიანტი:

   • ფართობი მიმდინარე ფურცელზე;
   • კიდევ ერთი ფურცელი;
   • კიდევ ერთი წიგნი (ახალი ფაილი).

   ავირჩიოთ პირველი ვარიანტი ისე, რომ წყაროს მონაცემები და შედეგი მოთავსდეს ერთ სამუშაო ფურცელზე. მოათავსეთ გადამრთველი პარამეტრის გვერდით "გამომავალი ინტერვალი". მოათავსეთ კურსორი ამ ელემენტის მოპირდაპირე ველში. ჩვენ დავაწკაპუნეთ მაუსის მარცხენა ღილაკით ფურცელზე არსებულ ცარიელ ელემენტზე, რომელიც გამიზნულია გახდეს ცხრილის ზედა მარცხენა უჯრედი გაანგარიშების შედეგების საჩვენებლად. ამ ელემენტის მისამართი უნდა იყოს ნაჩვენები ფანჯრის ველში "რეგრესია".

   პარამეტრების ჯგუფები "რჩება"და "ნორმალური ალბათობა"ჩვენ უგულებელყოფთ მათ, რადგან ისინი არ არიან მნიშვნელოვანი ამოცანის გადასაჭრელად. ამის შემდეგ დააჭირეთ ღილაკს "კარგი", რომელიც მდებარეობს ფანჯრის ზედა მარჯვენა კუთხეში "რეგრესია".



7. პროგრამა ახორციელებს გამოთვლებს ადრე შეყვანილი მონაცემების საფუძველზე და აჩვენებს შედეგს მითითებულ დიაპაზონში. როგორც ხედავთ, ეს ინსტრუმენტი აჩვენებს შედეგების საკმაოდ დიდ რაოდენობას ფურცელზე არსებული სხვადასხვა პარამეტრებისთვის. მაგრამ მიმდინარე გაკვეთილის კონტექსტში ჩვენ გვაინტერესებს ინდიკატორი "R-კვადრატი". ამ შემთხვევაში ის უდრის 0,947664-ს, რაც ახასიათებს შერჩეულ მოდელს, როგორც კარგი ხარისხის მოდელს.

მეთოდი 3: ტენდენციის ხაზის განსაზღვრის კოეფიციენტი

ზემოაღნიშნული ვარიანტების გარდა, განსაზღვრის კოეფიციენტი შეიძლება პირდაპირ გამოჩნდეს ტრენდის ხაზისთვის Excel-ის ფურცელზე აგებულ გრაფიკში. მოდით გავარკვიოთ, თუ როგორ შეიძლება ამის გაკეთება კონკრეტული მაგალითის გამოყენებით.

1. ჩვენ გვაქვს გრაფიკი, რომელიც ეფუძნება არგუმენტების ცხრილს და ფუნქციის მნიშვნელობებს, რომელიც გამოყენებული იყო წინა მაგალითისთვის. მოდით ავაშენოთ მისთვის ტრენდის ხაზი. მაუსის მარცხენა ღილაკით დააწკაპუნეთ სადმე შეთქმულების ზონაში, რომელზეც დიაგრამა მდებარეობს. ამავდროულად, ლენტაზე ჩნდება ჩანართების დამატებითი ნაკრები - "დიაგრამებთან მუშაობა". გადადით ჩანართზე "განლაგება". დააჭირეთ ღილაკს "ტენდენციის ხაზი", რომელიც მდებარეობს ხელსაწყოების ბლოკში "ანალიზი". ჩნდება მენიუ ტენდენციის ხაზის ტიპის არჩევით. ჩვენ ვირჩევთ ტიპს, რომელიც შეესაბამება კონკრეტულ ამოცანას. მოდით ავირჩიოთ ვარიანტი ჩვენი მაგალითისთვის "ექსპონენციალური მიახლოება".



2. Excel ხაზავს ტენდენციის ხაზს დამატებითი შავი მრუდის სახით პირდაპირ შედგენის სიბრტყეზე.



3. ახლა ჩვენი ამოცანაა თავად ვაჩვენოთ განსაზღვრის კოეფიციენტი. მარჯვენა ღილაკით დააწკაპუნეთ ტრენდის ხაზზე. კონტექსტური მენიუ გააქტიურებულია. ჩვენ ვაჩერებთ მასში არჩევანს წერტილში "ტენდენციის ხაზის ფორმატი...".

   

   არსებობს ალტერნატიული ქმედება, რომლითაც შეგიძლიათ გადახვიდეთ Trendline Format ფანჯარაში. აირჩიეთ ტრენდის ხაზი მასზე მაუსის მარცხენა ღილაკით დაწკაპუნებით. ჩანართზე გადასვლა "განლაგება". დააჭირეთ ღილაკს "ტენდენციის ხაზი"ბლოკში "ანალიზი". სიაში, რომელიც იხსნება, დააწკაპუნეთ ბოლო პუნქტზე მოქმედებების სიაში - "ტენდენციის ხაზის დამატებითი ვარიანტები...".



4. ზემოაღნიშნული ორი მოქმედებიდან რომელიმეს შემდეგ იხსნება ფორმატის ფანჯარა, რომელშიც შესაძლებელია დამატებითი პარამეტრების გაკეთება. კერძოდ, ჩვენი დავალების შესასრულებლად, თქვენ უნდა მონიშნოთ ველი ნივთის გვერდით "დაახლოებით სანდოობის მნიშვნელობა (R^2) მოათავსეთ დიაგრამაზე". იგი მდებარეობს ფანჯრის ბოლოში. ანუ ამ გზით ვაძლევთ კონსტრუქციის ფართობზე განსაზღვრის კოეფიციენტის ჩვენებას. შემდეგ არ დაგავიწყდეთ ღილაკზე დაჭერა "დახურვა"მიმდინარე ფანჯრის ბოლოში.



5. მიახლოებითი სანდოობის მნიშვნელობა, ანუ განსაზღვრის კოეფიციენტის მნიშვნელობა, ნაჩვენები იქნება ფურცელზე სამშენებლო ზონაში. ამ შემთხვევაში, ეს მნიშვნელობა, როგორც ვხედავთ, უდრის 0,9242-ს, რაც ახასიათებს მიახლოებას, როგორც კარგი ხარისხის მოდელს.



6. აბსოლუტურად ამ გზით თქვენ შეგიძლიათ დააყენოთ განსაზღვრის კოეფიციენტის ჩვენება ნებისმიერი სხვა ტიპის ტრენდის ხაზისთვის. თქვენ შეგიძლიათ შეცვალოთ ტენდენციის ხაზის ტიპი ლენტის ღილაკზე ან კონტექსტური მენიუში მისი პარამეტრების ფანჯარაში გადასვლით, როგორც ეს ნაჩვენებია ზემოთ. შემდეგ თავად ფანჯარაში ჯგუფში "ტენდენციის ხაზის აშენება"შეგიძლიათ გადახვიდეთ სხვა ტიპზე. ამავდროულად, არ დაგავიწყდეთ შეამოწმოთ ეს წერტილის მახლობლად "დაახლოებით სანდოობის მნიშვნელობა დიაგრამაზე"ჩამრთველი იყო მონიშნული. ზემოაღნიშნული ნაბიჯების დასრულების შემდეგ დააჭირეთ ღილაკს "დახურვა"ფანჯრის ქვედა მარჯვენა კუთხეში.



7. ხაზოვანი ტიპით, ტრენდის ხაზს უკვე აქვს მიახლოებითი სანდოობის მნიშვნელობა 0.9477, რაც ახასიათებს ამ მოდელს, როგორც კიდევ უფრო საიმედოს, ვიდრე ექსპონენციალური ტიპის ტრენდის ხაზი, რომელიც ადრე განვიხილეთ.



8. ამრიგად, სხვადასხვა ტიპის ტრენდულ ხაზებს შორის გადართვით და მათი მიახლოებითი სანდოობის მნიშვნელობების (განსაზღვრების კოეფიციენტის) შედარებით, შეგიძლიათ იპოვოთ ვარიანტი, რომლის მოდელიც ყველაზე ზუსტად აღწერს წარმოდგენილ გრაფიკს. ყველაზე საიმედო ვარიანტი იქნება განსაზღვრის ყველაზე მაღალი კოეფიციენტი. მასზე დაყრდნობით შეგიძლიათ შექმნათ ყველაზე ზუსტი პროგნოზი.

   მაგალითად, ჩვენს შემთხვევაში, ექსპერიმენტულად შესაძლებელი გახდა იმის დადგენა, რომ საიმედოობის უმაღლეს დონეს აქვს მეორე ხარისხის ტენდენციის ხაზის პოლინომიური ტიპი. განსაზღვრის კოეფიციენტი ამ შემთხვევაში უდრის 1-ს. ეს მიუთითებს იმაზე, რომ მითითებული მოდელი არის აბსოლუტურად სანდო, რაც ნიშნავს, რომ შეცდომები მთლიანად აღმოიფხვრება.

   

   მაგრამ, ამავე დროს, ეს საერთოდ არ ნიშნავს იმას, რომ სხვა სქემისთვის ამ ტიპის ტრენდული ხაზი ასევე ყველაზე საიმედო იქნება. ტრენდის ხაზის ტიპის ოპტიმალური არჩევანი დამოკიდებულია ფუნქციის ტიპზე, რომელზეც შეიქმნა გრაფიკი. თუ მომხმარებელს არ აქვს საკმარისი ცოდნა საუკეთესო ვარიანტის „თვალებით“ შესაფასებლად, მაშინ საუკეთესო პროგნოზის დასადგენად ერთადერთი გზაა განსაზღვრის კოეფიციენტების შედარება, როგორც ეს ნაჩვენებია ზემოთ მოცემულ მაგალითში.