რეგრესიის ანალიზი ხაზოვანი დამოკიდებულება ექსელში. არაწრფივი რეგრესია Excel-ში

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

რეგრესიის ძირითადი ამოცანები და ტიპები

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

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

რეგრესიის მოდელის აგების ძირითადი ამოცანები შემდეგია:

1. მნიშვნელოვანი დამოუკიდებელი ცვლადების შერჩევა (X1, X2, ..., Xk).
2. ფუნქციის ტიპის შერჩევა.
3. კოეფიციენტებისთვის შეფასებების აგება.
4. ნდობის ინტერვალებისა და რეგრესიის ფუნქციების აგება.
5. გამოთვლილი შეფასებებისა და აგებული რეგრესიის განტოლების მნიშვნელოვნების შემოწმება.

არსებობს რეგრესიის ანალიზის რამდენიმე ტიპი:

• დაწყვილებული (1 დამოკიდებული და 1 დამოუკიდებელი ცვლადი);
• მრავალჯერადი (რამდენიმე დამოუკიდებელი ცვლადი).

არსებობს რეგრესიის განტოლების ორი ტიპი:

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

ინსტრუქციები მოდელის შესაქმნელად

Excel-ში მოცემული კონსტრუქციის შესასრულებლად, თქვენ უნდა მიჰყვეთ ინსტრუქციას:

შემდგომი გაანგარიშებისთვის გამოიყენეთ "Linear()" ფუნქცია, რომელშიც მითითებულია Y მნიშვნელობები, X მნიშვნელობები, Const და სტატისტიკა. ამის შემდეგ განსაზღვრეთ რეგრესიის ხაზზე პუნქტების სიმრავლე „Trend“ ფუნქციის გამოყენებით - Y Values, X Values, New Values, Const. მოცემული პარამეტრების გამოყენებით გამოთვალეთ კოეფიციენტების უცნობი მნიშვნელობა, ამოცანის მოცემული პირობებიდან გამომდინარე.

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

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

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

რეგრესიული ანალიზი 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-ზე მეტი) კორელაციური ანალიზისთვის უფრო მოსახერხებელია „მონაცემთა ანალიზის“ გამოყენება (დამატება „ანალიზის პაკეტი“). თქვენ უნდა აირჩიოთ კორელაცია სიიდან და მიუთითოთ მასივი. ყველა.

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

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

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

მაგალითი:

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

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

ამისათვის საჭიროა:

1.გახსენით Excel

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

1 სვეტი - თემის ნომერი

2 სვეტი - აგრესიულობაქულებში

3 სვეტი - საკუთარ თავში ეჭვიქულებში

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

4. ასე რომ, ჩვენ გვაქვს რეგრესიის ხაზის შაბლონი - ე.წ. გაფანტული ნაკვეთი. რეგრესიის ხაზზე გადასასვლელად დააწკაპუნეთ მიღებულ ფიგურაზე და დააჭირეთ ჩანართს კონსტრუქტორი, იპოვნეთ პანელზე გრაფიკის განლაგება და აირჩიე მ ა ket9 , ასევე ნათქვამია f(x)

5. ასე რომ, ჩვენ გვაქვს რეგრესიის ხაზი. გრაფიკზე ასევე ნაჩვენებია მისი განტოლება და კორელაციის კოეფიციენტის კვადრატი

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

რეგრესიის ხაზი აშენდა MS 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-ს აქვს ჩაშენებული ფუნქციები, რომლებიც დაგეხმარებათ გამოთვალოთ ხაზოვანი რეგრესიის მოდელის პარამეტრები. მაგრამ „ანალიზის პაკეტის“ დანამატი ამას უფრო სწრაფად გააკეთებს.

მოდით გავააქტიუროთ ძლიერი ანალიტიკური ინსტრუმენტი:

1. დააჭირეთ ღილაკს "ოფისი" და გადადით "Excel Options" ჩანართზე. "დამატებები".

2. ბოლოში, ჩამოსაშლელი სიის ქვეშ, "მართვა" ველში იქნება წარწერა "Excel Add-ins" (თუ ის იქ არ არის, დააწკაპუნეთ ჩამრთველზე მარჯვნივ და აირჩიეთ). და ღილაკი "წადი". დააწკაპუნეთ.

3. იხსნება ხელმისაწვდომი დანამატების სია. აირჩიეთ "ანალიზის პაკეტი" და დააჭირეთ OK.

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

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

1. გახსენით "მონაცემთა ანალიზის" ხელსაწყოს მენიუ. აირჩიეთ "რეგრესია".

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

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

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

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

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

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