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

თემა: კორელაცია და რეგრესიული ანალიზი შიEXCEL

ლაბორატორიული სამუშაო No1

1. დაწყვილებული კორელაციის კოეფიციენტის განსაზღვრა პროგრამაშიEXCEL

კორელაცია- ეს არის არასრული, ალბათური ურთიერთობა ინდიკატორებს შორის, რომელიც ვლინდება მხოლოდ დაკვირვების მასაში.

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

მრავალჯერადი კორელაციაწარმოიქმნება რამდენიმე ფაქტორის ეფექტურ ინდიკატორთან ურთიერთქმედებიდან.

კორელაციური ანალიზის გამოყენების აუცილებელი პირობები:

1. საკმარისად დიდი რაოდენობის დაკვირვების არსებობა შესწავლილი ფაქტორისა და შესრულების მაჩვენებლების მნიშვნელობის შესახებ.

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

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

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

2. დაადგინეთ შესრულების ინდიკატორის დამოკიდებულების ფარდობითი ხარისხი თითოეულ ფაქტორზე.

დავალება 1.

მონაცემები ხელმისაწვდომია 20 სასოფლო-სამეურნეო მეურნეობის შესახებ. იპოვე კორელაციის კოეფიციენტიმარცვლეული კულტურების მოსავლიანობასა და მიწის ხარისხს შორის და შეაფასოს მისი მნიშვნელობა. მონაცემები ნაჩვენებია ცხრილში.

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

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

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

განსახილველი მაგალითისთვის r=0.99, n=18.

სტუდენტური განაწილების კვანტილის საპოვნელად გამოიყენეთ STUDISCOVER ფუნქცია შემდეგი არგუმენტებით: ალბათობა –0,05, ხარისხები თავისუფლება –18.

t-სტატისტიკის მნიშვნელობის შედარებით Student განაწილების რაოდენობასთან, გამოიტანეთ დასკვნები წყვილის კორელაციის კოეფიციენტის მნიშვნელოვნების შესახებ. თუ t- სტატისტიკის გამოთვლილი მნიშვნელობა მეტია Student განაწილების კვანტილს, მაშინ კორელაციის კოეფიციენტის მნიშვნელობა მნიშვნელოვანია.

ორ რაოდენობას შორის ურთიერთობის რეგრესიული მოდელის აგება

დავალება 2.

დავალების მიხედვით 1:

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

2). შეამოწმეთ მიღებული მოდელის ადეკვატურობა.

1 - გზა.

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

2. ფუნქციის გამოძახება LINEST.

3. მიუთითეთ შემდეგი არგუმენტები ფუნქციისთვის: Izv_value_წ პროდუქტიულობა, ც/ჰა;Izv_value_x- ინდიკატორის მნიშვნელობების სვეტი მიწის ხარისხი, ქულა; მუდმივი -1, სტატისტიკა - 1(მოდელის ადეკვატურობის შესამოწმებლად გამოყენებული ინდიკატორების გამოთვლის საშუალებას გაძლევთ. თუ სტატისტიკა - 0,მაშინ ასეთი მაჩვენებლები არ გამოითვლება.

4. დააჭირეთ კლავიშთა კომბინაციას Ctrl- ცვლა- შედი.

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

ა 1 , ა 0 – მოდელის კოეფიციენტები;

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

რ 2 – განსაზღვრის კოეფიციენტი. რაც უფრო დიდია ის, მით უფრო ზუსტია მოდელი.

ფ– სტატისტიკა მოდელის მნიშვნელობის შესამოწმებლად.

ნ- კ-1 – თავისუფლების გრადუსების რაოდენობა (n-ნიმუშის ზომა, k-შეყვანის ცვლადების რაოდენობა; ამ მაგალითში n=20, k=1)

ქ რ– კვადრატების ჯამი რეგრესიის გამო;

ქ ე- კვადრატული შეცდომების ჯამი.

5. მოდელის ადეკვატურობის შესამოწმებლად იპოვეთ ფიშერის განაწილების კვანტილი ფ ვ . ფუნქციის გამოყენებით ფაღმოაჩინეთ. ამისათვის შეიყვანეთ ფუნქცია ნებისმიერ თავისუფალ უჯრედში ფაღმოაჩინეთშემდეგი არგუმენტებით: ალბათობა – 0,05, თავისუფლების_ხარისხები _1–1, თავისუფლების_ხარისხები _2–18. თუ F> F f, მაშინ მოდელი ადეკვატურია თავდაპირველი მონაცემებისთვის

6. შეამოწმეთ აგებული მოდელის ადეკვატურობა გამოთვლილი მნიშვნელოვნების დონის (P) გამოყენებით. შეიყვანეთ ფუნქცია ფDISTშემდეგი არგუმენტებით: X- სტატისტიკური ღირებულება ფ, თავისუფლების_ხარისხები_1–1, თავისუფლების_ხარისხები_2– 18. თუ გამოთვლილი მნიშვნელოვნების დონე P<α =0,05, то модель адекватна исходным данным.

მე-2 მეთოდი.

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

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

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

გამომავალი ინტერვალი

და მიუთითეთ უჯრედი, საიდანაც შედეგები დაიწყება. შედეგების მისაღებად დააჭირეთ OK. შედეგების ინტერპრეტაცია.:

საჭირო მოდელის კოეფიციენტები მოცემულია სვეტში

შანსები

ამ მაგალითისთვის, მოდელის განტოლება არის:

Y=2.53+0.5Xშესრულებულია სვეტში მითითებულ P მნიშვნელოვნების გამოთვლილ დონეზე მნიშვნელობაფ. თუ გამოთვლილი მნიშვნელოვნების დონე ნაკლებია მითითებულ მნიშვნელოვნების დონეზე α = 0.05, მაშინ მოდელი ადეკვატურია.

ტესტირება სტატისტიკური მნიშვნელოვნებისთვისმოდელის კოეფიციენტები შესრულებულია სვეტში მითითებული P მნიშვნელოვნების დონის გამოყენებით პ- მნიშვნელობა. თუ გამოთვლილი მნიშვნელოვნების დონე ნაკლებია მითითებულ მნიშვნელოვნების დონეზე α = 0.05, მაშინ შესაბამისი მოდელის კოეფიციენტი სტატისტიკურად მნიშვნელოვანია.

მრავალჯერადირ – კორელაციის კოეფიციენტი. რაც უფრო ახლოს არის მისი მნიშვნელობა 1-თან, მით უფრო მჭიდროა კავშირი შესწავლილ მაჩვენებლებს შორის. ამ მაგალითისთვის, R= 0.99. ეს საშუალებას გვაძლევს დავასკვნათ, რომ მიწის ხარისხი არის ერთ-ერთი მთავარი ფაქტორი, რომელზეც დამოკიდებულია მარცვლეული კულტურების მოსავლიანობა.

რ- კვადრატი – განსაზღვრის კოეფიციენტი. იგი მიიღება კორელაციის კოეფიციენტის კვადრატში - R 2 =0.98. ის აჩვენებს, რომ მარცვლეულის მოსავლიანობის 98% დამოკიდებულია ნიადაგის ხარისხზე, სხვა ფაქტორები კი 0,02%-ს შეადგენს.

მე-3 მეთოდი. მოდელის აგების გრაფიკული მეთოდი.

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

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

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

1) აირჩიეთ სვეტები მონაცემებით Xდა შეყვანის ინტერვალი(ისინი ამ თანმიმდევრობით უნდა იყვნენ!);

2) დარეკეთ გრაფიკის ოსტატიდა აირჩიეთ ჯგუფში ტიპი – ადგილზედა დაუყოვნებლივ დააჭირეთ მზადაა;

3) დიაგრამის არჩევის გაუქმების გარეშე აირჩიეთ მენიუს მთავარი ელემენტი, რომელიც გამოჩნდება დიაგრამა, რომელშიც უნდა აირჩიოთ ელემენტი დაამატეთ ტრენდის ხაზი;

4) დიალოგურ ფანჯარაში, რომელიც გამოჩნდება ტრენდული ხაზიჩანართში ტიპიაირჩიე ხაზოვანი;

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

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

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

§4.3. არაწრფივი რეგრესიის ძირითადი ტიპები

პარაბოლური და პოლინომიური რეგრესია.

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

ეს განტოლება ე.წ პარაბოლური რეგრესიის განტოლება Y on X. ოფციები ა, ბ, თანეძახიან პარაბოლური რეგრესიის კოეფიციენტები. პარაბოლური რეგრესიის კოეფიციენტების გამოთვლა ყოველთვის რთულია, ამიტომ გამოთვლებისთვის რეკომენდებულია კომპიუტერის გამოყენება.

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

სად არის ნომრები და მე (მე=0,1,…, ნ) ეძახიან პოლინომიური რეგრესიის კოეფიციენტები.

ძალაუფლების რეგრესია.

ძალაუფლებაღირებულების დამოკიდებულება შეყვანის ინტერვალიზომიდან Xფორმის დამოკიდებულება ეწოდება:

ეს განტოლება ე.წ სიმძლავრის რეგრესიის განტოლება Y on X. ოფციები ადა ბეძახიან სიმძლავრის რეგრესიის კოეფიციენტები.

ln = ln ა+ბლნ x. (4.11)

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

ექსპონენციალური რეგრესია.

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

(ან). (4.12)

ეს განტოლება ე.წ ექსპონენციალური განტოლება(ან ექსპონენციალური) რეგრესია Y on X. ოფციები ა(ან კ) და ბეძახიან ექსპონენციალური კოეფიციენტები(ან ექსპონენციალური) რეგრესია.

თუ ავიღებთ სიმძლავრის რეგრესიის განტოლების ორივე მხარის ლოგარითმს, მივიღებთ განტოლებას

ln = xლნ ა+ln ბ(ან ln = k x+ln ბ). (4.13)

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

ლოგარითმული რეგრესია.

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

=ა+ბლნ x. (4.14)

ეს განტოლება ე.წ ლოგარითმული რეგრესიის განტოლება Y on X. ოფციები ადა ბეძახიან ლოგარითმული რეგრესიის კოეფიციენტები.

ჰიპერბოლური რეგრესია.

ჰიპერბოლურიღირებულების დამოკიდებულება შეყვანის ინტერვალიზომიდან Xფორმის დამოკიდებულება ეწოდება:

ეს განტოლება ე.წ ჰიპერბოლური რეგრესიის განტოლება Y on X. ოფციები ადა ბეძახიან ჰიპერბოლური რეგრესიის კოეფიციენტებიდა განისაზღვრება უმცირესი კვადრატების მეთოდით. ამ მეთოდის გამოყენება იწვევს ფორმულებს:

ფორმულებში (4.16-4.17) შეჯამება ხდება ინდექსზე მეერთიდან დაკვირვების რაოდენობამდე ნ.

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

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

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

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

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

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

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

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

მაგალითი:

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