Тэгшитгэл Жишээ нь: Excel-д регрессийн. шугаман регрессийн

Регрессийн шинжилгээ - нэг буюу хэд хэдэн бие даасан хувьсагч нь параметрийн хамаарлыг харуулах статистик судалгааны арга. өмнөх компьютерийн эрин зуунд түүний хэрэглээ биш, харин хэцүү байна энэ нь өгөгдлийг их хэмжээний ирсэн ялангуяа. Өнөөдөр, Excel-д регрессийг барих хэрхэн сурах, та хэдхэн минутын дотор нарийн статистик асуудлыг шийдэж чадна. Доорх эдийн засгийн тодорхой жишээ юм.

регрессийн төрөл

Энэ үзэл баримтлал нь Математик танилцуулсан юм Фрэнсис Galton хамт 1886 онд. Регрессийн байна:

• шугаман;
• шидэж;
• эрчим хүч,
• экспоненциал;
• гипер;
• экспоненциал;
• логарифмын.

ЖИШЭЭ: 1

6 аж ахуйн нэгж дундаж цалингийн ажилтнуудын гишүүдийн огцорсныг тоо хамаарлыг тогтоох асуудлыг авч үзье.

Даалгавар. Зургаан компани сарын дундаж цалин, сайн дураараа гарах ажилчдын тоог дүн шинжилгээ хийж байна. хүснэгтэн хэлбэрээр бид:

	A	B	C
1	X	огцрох тоо	цалин
2		у	30000 рубль
3	1	60	35000 рубль
4	2	35	40000 рубль
5	3	20	45000 рубль
6	4	20	50000 рубль
7	5	15	55000 рубль
8	6	15	60000 рубль

6 аж ахуйн нэгж регрессийн загварт дундаж цалин хэмжээ салгах ажилчдын хамаарлыг тодорхойлох асуудал нь тэгшитгэлийн Y = хэлбэртэй байна ₀ + ₁ х ₁ + ... + а _к х _к, хаана х _I - нөлөөлж хувьсагч нь _би - регрессийн коэффициент, AK - хүчин зүйлийн тоо.

Y өгөгдсөн үүрэг нь - X-ээр тэмдэглэсэн байна цалин, - энэ нь ажилтан гал үзүүлэлт нь хувь нэмэр оруулах хүчин зүйл юм

"Excel" хүснэгттэй хүчийг уялдуулан

Excel-д Регрессийн шинжилгээ нь цуг бүтээгдсэн функцуудтай байгаа хүснэгт өгөгдөлд өргөдөл өмнө байх ёстой. Гэсэн хэдий ч эдгээр зорилгоор энэ нь маш их ашигтай нэмэлт онд "пакет шинжилгээ" ашиглах нь зүйтэй юм. Үүнийг идэвхжүүлэхийн тулд та хэрэгтэй:

• таб "File" хамт "Тохиргоо" явагтун;
• нээж цонхонд "нэмэгдлүүд 'сонгох;
• шугам "менежмент" -ийн баруун доод буланд байрлах товчлуур "Go" дээр товшино уу;
• "Шинжилгээ ToolPak" дэргэд шалгах тэмдэг тавьж, "OK" дарж нь таны үйлдэл баталж байна.

зөв хийсэн бол, "Мэдээлэл" таб баруун тал, ажлын хуудсан дээр "Excel" Дээрх байрлах хүссэн товчин дээр харуулж байна.

Excel-д шугаман регрессийн

Одоо та эконометриксийн тооцоо хийхэд шаардлагатай бүх виртуал хэрэгсэл гар дээр байгаа гэдгийг бид асуудлыг шийдвэрлэх эхлэх болно. Үүнийг хийхийн тулд:

• товч "мэдээллийн дүн шинжилгээ хийх" дээр дарсан байх;
• нээлттэй цонхны товчлуур "регрессийн" дээр товшино уу;
• утгын төрөл бүрийн танилцуулж гарч ирэх нь таб Y (салгах ажилчдын тоо) болон X (тэдний цалин);
• «За» товчийг дарж өөрийн үйл ажиллагааг дахин нотолж байна.

Үүний үр дүнд програм нь автоматаар шинэ хуудас хүснэгттэй мэдээлэл регрессийн шинжилгээ дүүргэх болно. анхаарлаа хандуулаарай! Excel, та энэ зорилгоор илүүд газар тогтоох боломж байна. Жишээ нь, нэг хуудас, утга Y ба X, эсвэл бүр шинэ ном, ялангуяа ийм мэдээллийг хадгалах зориулалттай байж болох юм.

R-талбайд нь Регрессийн шинжилгээний үр дүн

үзсэн жишээ нь мэдээлэл олж авсан Excel-ийн мэдээлэл хэлбэртэй байна:

Юуны өмнө, бид R-квадрат үнэ цэнийг анхаарах ёстой. Энэ нь тодорхойлох коэффициент юм. Энэ жишээн дээр, R-квадрат = 0.755 (75.5%), м. E. загварын тооцсон үзүүлэлт 75.5% нь үзсэн үзүүлэлтүүдийн хоорондын уялдаа холбоог тайлбарлаж байна. тодорхойлох коэффициент өндөр үнэ цэнэ, сонгосон загвар нь тодорхой ажлуудад илүү ашигтай гэж үзэж байна. Энэ нь зөв 0.8 Дээрх R квадрат үнэ цэнээр бодит байдлыг тайлбарлах гэж үздэг. R-квадрат <0.5, дараа нь Excel-д регрессийн шинжилгээ боломжийн гэж үзэж чадахгүй байгаа бол.

харьцаа дүн шинжилгээ

Дугаар 64,1428, Y үнэ цэнийг ямар байх вэ манай загварт бүх хувьсагчууд Ши дахин эхлүүлэх байх юм бол харуулж байна. Өөрөөр хэлбэл, энэ нь дүн шинжилгээ хийж параметрийн утга нь тодорхой загварт заасан зааснаас бусад хүчин зүйлс нөлөөлж байна гэж үзэж болох юм.

дараагийн хүчин зүйл нь -0,16285 эсийн консерциум Б-18-д байрладаг, Y. хувьсагч X чухал нөлөө Энэхүү загвар нь дотор ажилчдын дундаж цалин -0.16285 жин, т нь огцрох тоог нөлөөлнө гэсэн үг юм байна. E. түүний нөлөөллийн зэрэг бүх бага байна. тэмдэг "-" коэффициент сөрөг болохыг харуулж байна. Бид бүгд аж ахуйн нэгж илүү цалин бага хүмүүс хөдөлмөр эрхлэлт, эсвэл хэрэгсэхгүй болгосон гэрээг цуцлах хүсэлтэй байгаагаа илэрхийлсэн байна гэдгийг мэдэх хойш, тодорхой байна.

олон регрессийн

Энэ хугацаанд дагуу хэлбэрээр хэд хэдэн бие даасан хувьсагч нь харилцаа холбооны тэгшитгэлийн хэлнэ:

Y = F (х ₁ + х ₂ + ... х _м) + ε, у - нэг онцлог шинж нь оноо (хамаарах хувьсагч) юм, х ₁ х _2, ... х _м - шинж тэмдэг хүчин зүйл (бие даасан хувьсагч) юм.

параметр тооцоо

Олон регрессийн (MR) хувьд энэ нь хамгийн бага квадратын арга (LSM) хийж ашиглаж байна. хэлбэр Y = A + B ₁ х _{1 + ... + б м х м +} шугаман тэгшитгэлийн хувьд ε хэвийн тэгшитгэлийн системийг бий болгох (см. доор)

аргын зарчмыг ойлгохын тулд бид хоёр хүчин зүйл нь хэрэг гэж үздэг. Дараа нь бид нөхцөл байдал томъёогоор тодорхойлсон байна

Тиймээс бид авах:

σ хаана - тухайн онцлог хэлбэлзэл юм индекс тусгагдсан.

тэгшитгэл MR хэмжээний standartiziruemom тулд MNC хэрэглэж болно. Энэ тохиолдолд бид тэгшитгэлийг авах:

үүгээр т _у, т _{х 1, ...} т _XM - дундаж утга нь 0 байх хувьсагчуудыг standartiziruemye; _I бета - стандартчилагдсан регрессийн коэффициент, стандарт хазайлтыг - 1.

бүх _I бета болохыг анхаарна уу Хооронд Тиймээс харьцуулалт хэвийн болон tsentraliziruemye энэ тохиолдолд тодорхойлсон хүчин төгөлдөр ба хүлээн зөвшөөрөгдсөн гэж үздэг. Үүнээс гадна, энэ нь βi хамгийн бага утгыг байна гэж тэд учиргүй зүйлсээс зайлсхийж, хүчин зүйлсийн шинжилгээ явуулах хүлээн зөвшөөрч байна.

шугаман регрессийн тэгшитгэлийг ашиглан асуудал

Та сүүлийн 8 сарын турш тухайн бүтээгдэхүүний N үнийн динамик нь ширээ байна гэж бодъё. Энэ нь 1850 рублийн үнээр өөрийн намын худалдан авах эсэхийг шийдэх шаардлагатай байна. / Т.

	A	B	C
1	сарын	Сарын нэр	Үнэ N
2	1	оны нэгдүгээр сарын	нэг тонн 1750 рубль
3	2	хоёрдугаар сар	нэг тонн 1755 рубль
4	3	Гуравдугаар сар	нэг тонн 1767 рубль
5	4	дөрөвдүгээр сар	нэг тонн 1760 рубль
6	5	May	нэг тонн 1770 рубль
7	6	оны зургадугаар сарын	нэг тонн 1790 рубль
8	7	долдугаар сарын	нэг тонн 1810 рубль
9	8	Наймдугаар сар	нэг тонн 1840 рубль

хүснэгтэн процессор "Excel" жишээ хэрэгсэл "Data Analysis" Дээрх танилцуулсан нь аль хэдийн танигдсан ашиглах шаардлагатай энэ асуудлыг шийдэхийн тулд. Дараа нь, "Регрессийн" хэсгийг мөн тогтоосон үзүүлэлтүүдийг сонгох хэрэгтэй. бие даасан (сар) нь - Бид Оролт завсар X »" Оролт хүрээнд Y »хамааралтай хувьсагчийн утгын хүрээнд нь (энэ тохиолдолд барааны үнийг жилд тодорхой саруудад) болон нэвтрүүлсэн байх ёстой" гэдгийг санах хэрэгтэй. Бид «За» дарж арга хэмжээ баталж байна. Шинэ ажлын (болохоор заасан бол) бид регрессийн өгөгдлийг олж авах хэрэгтэй.

Бид хэлбэр нь у = сүх + B, параметр А ба В зэрэг коэффициентын сарын мөрийн тоо, нэр, регрессийн шинжилгээний үр дүнг хуудас «Y-огтлол" шугамаас коэффициент нь шугаман тэгшитгэлийг тэдний дээр барьж байна. Тиймээс асуудлыг шугаман регрессийн тэгшитгэл (Тэгшитгэл) 3 гэж бичиж болно:

барааны үнэ N = 11,714 * 1727.54 сарын дугаар +.

эсвэл алгебрийн бичлэгээр

у = 11.714 х + 1727,54

үр дүнгийн шинжилгээ

Олон корреляцийн коэффициент (CMC) болон тодорхойлох зэрэг сорил болон Fisher-ийн Т-тест ашиглан зохих ёсоор шугаман регрессийн тэгшитгэлийг хүлээн авсан эсэхийг шийдэх хэрэгтэй. Тэд нэр олон R, R-талбай, F-т статистик болон статистик тус тус заасан үйлдэл үр дүнг хүснэгт "Excel" регрессийн байна.

KMC R, бие даасан, хараат хувьсагчийн хоорондын ойр байдал магадлалт хамаарлыг тооцох боломжийг олгодог. Түүний өндөр утга нь хувьсагч "сарын тоо" ба хоорондын хангалттай хүчтэй холболтыг харуулж байна "1 тонн рубль N Бүтээгдэхүүний үнэ." Гэсэн хэдий ч, энэ нь харилцааны мөн чанар тодорхойгүй байна.

тодорхойлох R ² коэффициент квадрат (ДИ), өөрөөр хэлбэл, нийт тарааж эзлэх хувь нь тоон үзүүлэлт бөгөөд туршилтын өгөгдөл хэсэгт нь тарааж байна Шугаман регрессийн тэгшитгэлийн харгалзах хамааралтай хувьсагчийн утга. Энэ асуудлын хувьд энэ утга нь УИХ-ын гишүүн. нарийвчлалтай авсан өндөр зэрэгтэй E. статистик SD тайлбарласан байгаа, 84.8% байна.

F-статистик, мөн Фишер шалгуур гэгддэг шугаман хамаарал, түүний оршин баталж таамаглалыг disproving ач холбогдлыг үнэлж байсан.

т статистик (Оюутны т тест) -ийн утга нь ямар ч үнэ төлбөргүй үл мэдэгдэх шугаман хамаарал гишүүний дэргэд коэффициент ач холбогдлыг үнэлэх тусалдаг. T-туршилтын> т _{БНЧУ-ын} үнэ _бол үнэгүй хугацааны шугаман тэгшитгэл хэчнээн өчүүхэн тухай таамаглал татгалзсан байна.

хэрэгслээр дамжуулан үнэгүй хугацаагаар энэ асуудлыг онд "Excel" Энэ T = гэсэн 169,20903 олдсон бөгөөд P = 2,89E-12, Т. E. итгэлтэй үнэгүй хугацаанд ач холбогдолгүй нь таамаглалыг татгалзах болно гэсэн тэг магадлал байна. T = 5,79405 үед үл мэдэгдэх коэффициент ба х = 0,001158 нь. Өөрөөр хэлбэл, нэг татгалзсан зөв таамаглал нь үл мэдэгдэх нь коэффициент нь ач холбогдолгүй болно магадлал 0.12% байна.

Тиймээс энэ нь хангалттай шугаман регрессийн тэгшитгэлийг олж авсан гэж үзэж болно.

хувьцааг худалдан авах нь advisability асуудал

Олон регрессийн нэг "Data Analysis" хэрэгсэл ашиглан Excel гүйцэтгэсэн байна. Тодорхой програмыг авч үзье.

Гарын авлага компани «NNN» ХК-ийн «MMM» хувьцааны 20% -ийг худалдан авах эсэхийг шийдэх ёстой. Багц үнэ (SP) 70 сая ам доллар байна. «NNN» мэргэжилтнүүд төстэй гүйлгээний тухай мэдээллийг цуглуулсан. гэх мэт АНУ-ын доллар сая дахь Ийм үзүүлэлтийн дээр хувьцааны үнэ цэнийг үнэлэх шийдсэн байсан илэрхийлж:

• өглөг (ВК);
• жилийн эргэлт хэмжээ (VO);
• авлага (VD);
• үндсэн хөрөнгийн (SOF) -ийн утга.

Үүнээс гадна АНУ-ын доллар мянган аж ахуйн нэгж, цалингийн өр (V3 U) ашигладаг.

шийдвэр хүснэгт процессор Excel-ийн арга хэрэгсэл

Эхлээд та оролтын өгөгдлийн хүснэгт үүсгэх хэрэгтэй. дараах байдлаар байна:

Дараа нь:

• дуудлага хайрцаг "мэдээллийн дүн шинжилгээ хийх";
• Сонгогдсон "Регрессийн" хэсэг;
• цонх "Оролт интервал Y» удирдаж хүрээ хамаарах хувьсагч багана G-аас утга;
• цонхны "Оролт интервал X» баруун талд улаан сумаар дүрсэн дээр товшино уу, багана нь В, С, D, Е бүх утга нь хуудас хүрээтэй тусгаарлагдсан

Марк цэг "Шинэ ажлын" болон "OK" дарна уу.

Энэ үүрэг нь регрессийн шинжилгээ авах.

судалгааны үр дүн, дүгнэлт

"Цуглуулах" хуудас хүснэгт Excel-ийн процессор регрессийн тэгшитгэлийн дээрх танилцуулсан мэдээллээс дугуйрсан:

SD = 0.103 * SOF + 0,541 * VO - 0.031 * ВК + 0,405 * VD + 0,691 * ВЗП - 265.844.

илүү ердийн математик хэлбэрээр энэ нь бичиж болно:

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

Доорх хүснэгтэд үзүүлсэн нь «MMM» ХК-ийн Мэдээлэл:

SOF, ам.доллар	VO, ам.доллар	ВК, ам.доллар	VD, ам.доллар	ВЗП, ам.доллар	Хамтарсан компани, ам.доллар
102.5	535.5	45.2	41.5	21,55	64,72

тэднийг регрессийн тэгшитгэл болгон орлох, 64,72 сая ам.долларын зургийг авсан байна. Энэ нь зардал нь маш 70 сая АНУ-ын доллар хэт өндөр байгаа тул ХК-ийн «MMM» хувьцааг худалдаж авах хэрэггүй гэж хэлнэ.

Таны харж байгаагаар, хүснэгттэй ашиглах нь "Excel" болон регрессийн тэгшитгэл advisability бүр тодорхой гүйлгээг талаархи мэдээлэл шийдвэр гаргах эрхтэй.

Одоо та ямар регрессийн мэднэ. Excel тулд жишээ нь, дээр дурдсан, эдийн засгийг практик асуудлыг шийдвэрлэхэд тань туслах болно.