Რეგრესიის Excel: განტოლება მაგალითები. წრფივი რეგრესია

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

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

1886 წელს ფრენსის გოლტონის მიერ მათემატიკის სპეციალობით . რეგრესია ხდება:

• ხაზოვანი;
• პარაბოლური;
• ძალაუფლება;
• ექსპონენციალური;
• ჰიპერბოლური;
• ინდიკატური;
• ლოგარითმული.

მაგალითი 1

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

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

	ა	ბ	გ
1	X	გადადებული ხმების რაოდენობა	ხელფასი
2		ი	30000 რუბლი
3	1	60	35,000 რუბლი
4	2	35	40000 მანეთი
5	3	20	45,000 რუბლი
მე -6	4	20	50,000 რუბლი
მე -7	5	მე -15	55,000 რუბლი
მე -8	მე -6	მე -15	60000 მანეთი

6 საწარმოში საშუალო ხელფასის დატოვების მიზნით დასაქმებულთა რაოდენობაზე დამოკიდებულების დასადგენად, რეგრესიულ მოდელს აქვს Y = a + ₀ + ₁ x ₁ + ... + _k x _კ , სადაც x _{მე ვარ} ზეგავლენის ცვლადები, _{მე ვარ} რეგრესიული კოეფიციენტები, ფაქტორების რაოდენობა.

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

Excel ცხრილის პროცესორი შესაძლებლობების გამოყენება

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

• "ფაილი" ჩანართიდან წასვლა "პარამეტრები" სექციაში;
• გახსნილი ფანჯარაში შეარჩიეთ ხაზი "Add-ins";
• დააჭირეთ "Go" ღილაკს, რომელიც მდებარეობს ქვემოთ, "მართვის" ხაზის მარჯვნივ;
• ჩაწერეთ სახელწოდება "ანალიზის პაკეტი" და დაადასტურეთ თქვენი ქმედებები "OK" - ზე დაჭერით.

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

ხაზოვანი რეგრესია Excel- ში

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

• დააჭირეთ ღილაკს "მონაცემთა ანალიზი";
• გახსნილი ფანჯრის ღილაკზე დააჭირეთ ღილაკს "რეგრესია";
• აღმოჩენილი ჩანართში შეიტანეთ Y- ის ღირებულებები (გათავისუფლებული მუშათა რაოდენობა) და X (მათი ხელფასი);
• ჩვენ ვადასტურებთ ჩვენს ქმედებებს "Ok" ღილაკზე დაჭერით.

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

რეგრესიის შედეგების ანალიზი R- კვადრატისთვის

Excel- ში, მონაცემების დამუშავებისას მიღებული მონაცემებია:

უპირველესად, ყურადღება მიაქციეთ R- კვადრატის ღირებულებას. ეს არის განსაზღვრის კოეფიციენტი. ამ მაგალითში, R- კვადრატი = 0.755 (75.5%), ანუ, გამოთვლითი პარამეტრების მოდელი განმარტავს ურთიერთობას პარამეტრებს შორის გათვალისწინებული 75.5%. განსაზღვრულობის კოეფიციენტის უფრო მაღალი ღირებულება, შერჩეული მოდელი უფრო კონკრეტული ამოცანისთვის მიჩნეულია. ითვლება, რომ სწორად აღწერს ფაქტობრივ სიტუაციას R- კვადრატულ ღირებულებას 0.8-ზე ზემოთ. თუ R- კვადრატი არის <0.5, მაშინ Excel- ის რეგრესიის ასეთი ანალიზი არ შეიძლება ჩაითვალოს გონივრულად.

კოეფიციენტების ანალიზი

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

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

მრავალჯერადი რეგრესია

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

Y = f (x ₁ + x ₂ + ... x _m ) + ε, სადაც y არის შედეგიანი ცვლადი (დამოკიდებული ცვლადი) და x ₁ , x ₂ , ... x მმ ატრიბუტი ფაქტორები (დამოუკიდებელი ცვლადები).

პარამეტრების შეფასება

მრავალჯერადი რეგრესიისთვის (MR), ხორციელდება მინიმუმ მოედნების (OLS) მეთოდის გამოყენებით. ფორმის ხაზოვანი განტოლებისთვის Y = a + b ₁ x ₁ + ... + b _m x _m + ε ჩვენ ნორმალური განტოლების სისტემას ვაშენებთ (იხ. ქვემოთ)

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

აქედან გამომდინარე:

სადაც σ არის ინდექსში ასახული შესაბამისი მახასიათებლის განსხვავება.

OLS გამოიყენება დეპუტატის განტოლებისთვის სტანდარტიზებული მასშტაბის შესახებ. ამ შემთხვევაში ჩვენ განვადება:

რომელშიც t _y , t _{x 1, ...} t _xm - სტანდარტიზირებული ცვლადები, რომელთა საშუალო მაჩვენებელია 0;  _მე სტანდარტიზებული რეგრესიული კოეფიციენტები და სტანდარტული გადახრა არის 1.

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

პრობლემა წრფივი რეგრესიის განტოლების გამოყენებით

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

	ა	ბ	გ
1	თვის ნომერი	თვის დასახელება	სასაქონლო ფასის N
2	1	იანვარი	1750 რუბლი ტონაზე
3	2	თებერვალი	1755 რუბლი ტონაზე
4	3	მარტი	1767 რუბლი თითო ტონაზე
5	4	აპრილი	1760 რუბლი თითო ტონაზე
მე -6	5	მაისი	1770 რუბლი თითო ტონაზე
მე -7	მე -6	ივნისი	1790 რუბლი თითო ტონაზე
მე -8	მე -7	ივლისი	1810 რუბლი თითო ტონაზე
მე -9	მე -8	აგვისტო	1840 რუბლი თითო ტონაზე

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

ჩვენ ვაშენებთ მათ y = ax + b- ის ხაზოვანი განტოლება, სადაც a და b პარამეტრების კოეფიციენტები არიან ნომრის ნომრისა და კოეფიციენტების სახელით და კოეფიციენტები და ხაზები "Y- კვეთა" ფურცელიდან რეგრესიული ანალიზის შედეგებით. ამდენად, პრობლემის 3-ის ხაზოვანი რეგრესიის განტოლება (VR) იწერება:

სასაქონლო ფასის N = 11,714 * თვის რიცხვი + 1727,54 თვე.

ან ალგებრული ნოტაციაში

Y = 11.714 x + 1727.54

შედეგების ანალიზი

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

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

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

F-statistic, ასევე მოუწოდა ფიშერის კრიტერიუმი, გამოიყენება შეფასების ხაზგასმა დამოკიდებულების მნიშვნელობის შესაფასებლად, მისი არსებობის ჰიპოთეზის უარყოფა ან დამადასტურებელი.

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

პრობლემის გათვალისწინებით, Excel- ის ინსტრუმენტების გამოყენებით თავისუფალი ტერმინი, აღმოჩნდა, რომ t = 169.20903 და p = 2.89E-12, ანუ ჩვენ გვაქვს ნულოვანი ალბათობა, რომ თავისუფალი ტერმინის უმნიშვნელო ჰიპოთეზა უარყოფილი იქნება. T = 5.79405, და p = 0.001158- ის კოეფიციენტი. სხვა სიტყვებით რომ ვთქვათ, ალბათ, რომ სწორი ჰიპოთეზა არ არის კოეფიციენტის უმნიშვნელოდ უცნობი, უარყოფითად არის 0.12%.

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

აქციების ბლოკის ყიდვის მიზანშეწონილობის პრობლემა

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

მმართველმა კომპანიამ "NNN" უნდა მიიღოს გადაწყვეტილება სააქციო საზოგადოება "MMM" - ს 20% წილის შეძენის მიზანშეწონილობის შესახებ. პაკეტის ღირებულება (JV) შეადგენს 70 მილიონ აშშ დოლარს. "NNN" სპეციალისტების მიერ შეგროვებული მონაცემები მსგავსი გარიგებების შესახებ. გადაწყდა, რომ ამგვარი ღირებულების ღირებულების შეფასება შეაფასოს მილიონობით აშშ დოლარში, როგორც:

• გადასახდელი (VK);
• წლიური ბრუნვის მოცულობა (VO);
• დებიტორული დავალიანება (VD);
• ფიქსირებული აქტივების ღირებულება (SOF).

გარდა ამისა, საწარმოს სახელფასო დავალიანების პარამეტრი (V3 P) გამოიყენება ათასობით აშშ დოლარში.

Solution Excel- ის გამოყენებით

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

შემდეგი:

• "მონაცემთა ანალიზის" ფანჯარა;
• აირჩიეთ "რეგრესიული" სექცია;
• "შეყვანის ინტერვალი Y" ყუთში შეიტანეთ სვეტის G- დან დამოკიდებული ცვლადების დიაპაზონი.
• დაწკაპეთ ხატი წითელი arrow "შეყვანის ინტერვალი X" ფანჯრის მარჯვნივ და შეარჩიეთ ყველა ღირებულების დიაპაზონი B- ს, C, D, F- ს ფურცელზე.

მიუთითეთ პუნქტი "New worksheet" და დააჭირეთ "Ok".

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

სწავლის შედეგები და დასკვნები

Excel- ის ცხრილის პროცესის ფურცელზე ზემოთ წარმოდგენილი მრგვალებული მონაცემების "შეგროვება", რეგრესიული განტოლება:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

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

y = 0.103 * x1 + 0,541 * x2 - 0.031 * x3 + 0,405 * x4 + 0,691 * x5 - 265.844

მონაცემები «MMM» სს წარმოდგენილი ცხრილი:

SOF, USD	VO, USD	VK, USD	VD, USD	Vzp, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

შემცვლელი მათ რეგრესიის განტოლება, მიღებული ფიგურა 64,72 მილიონი აშშ დოლარი. ეს ნიშნავს, რომ სს «MMM» არ უნდა იყიდოს, რადგან მათი ღირებულება საკმაოდ ფული 70 მილიონი აშშ დოლარი.

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

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