Excelda regressiya: tenglama, misollar. Lineer regressiya

Regresyon tahlil qilish, - bir yoki bir necha mustaqil o'zgaruvchilar bir parametr bog'liqligini ko'rsatish uchun bir statistik o'rganish usuli. pre-kompyuter asrida, uning foydalanish katta hajmdagi ma'lumotlar bilan kelgan, ayniqsa, qiyin bo'ldi. Bugungi kunda, Excel tushish qurish uchun qanday o'rganish, siz faqat bir necha daqiqa ichida murakkab statistik muammolarni hal mumkin. Quyida iqtisodiyot o'ziga xos misollar mavjud.

Regresyon turlari

Bu tushuncha matematika joriy etildi Francis Galton tomonidan 1886 yilda. Regresyonu:

• chiziqli;
• parabolik;
• elektr;
• ko'rsatkichli;
• giperbolik;
• ko'rsatkichli;
• logarifmik.

O'RNAK 1

6 sanoat korxonalarida o'rtacha oylik ish haqining xodimlari istifalarına soni bog'liqligini aniqlash muammosini ko'rib chiqaylik.

Vazifa. Olti kompaniyalari o'rtacha oylik ish haqi va ixtiyoriy ravishda voz kechmoq xodimlari sonini tahlil qilgan. jadval shaklida, biz bor:

	A	B	C
1	X	iste'folar soni	maosh
2		y	30000 rubl
3	1	60	35000 rubl
4	2	35	40000 rubl
5	3	20	45000 rubl
6	4	20	50,000 rubl
7	5	15	55000 rubl
8	6	15	60000 rubl

6 korxona, regresyon modeli uchun o'rtacha ish haqi miqdori ajratish ishchilar bog'liqligini aniqlash muammosi uchun y = tenglama shakliga ega ₀ + a ₁ x ₁ + ... + a _k x _k, qaerda x _i - ta'sir o'zgaruvchilar, bir _i - Regress koeffitsientlari, ak - omillar soni.

Y berilgan vazifa uchun - ish haqi, X. bilan belgilanadi - u xodimi olov uchun ko'rsatkich, bir omil hisoblanadi

"Excel" elektron jadvali kuchini

Excel Regresyon tahlil qilish, ichki funktsiyalari mavjud stol ma'lumotlarga ariza oldin bo'lishi kerak. Biroq, ushbu maqsadlar uchun juda foydali plagin-yilda "paketi tahlil" foydalanish ham yaxshi bo'ladi. uni faollashtirish uchun, kerak:

• tab "File" bilan "Settings" ga;
• ochilgan oynada, "qo'shish-smaylik" ni tanlang;
• line "boshqarish" pastki o'ng qismida joylashgan tugmasini "o'tish", tugmasini bosing;
• "Tahlil ToolPak" yonida bir tasdiq belgisi qo'yish va "OK" tugmasini bosib, siz harakatni tasdiqlash.

Agar shunday qilinsa, ish varag'i "Excel" yuqorida joylashgan "Data" ko'rinishida o'ng tomoni, istalgan tugmani ko'rsatadi.

Excel Chiziqli Regresyon

Endi siz tomondan ekonometrik hisoblar uchun zarur bo'lgan barcha virtual vositalari bor, deb, biz muammoni hal qila boshlaydi. Buning uchun:

• tugmasini "Data tahlil" tıkladım etiladi;
• ochiq oynada tugmasini "tushish" tugmasini bosing;
• qadriyatlar bir qator joriy ko'rinadi a tab Y (ajratish ishchilar soni) va X (ularning ish haqi);
• «OK» tugmasini bosib, o'z harakatlarini tasdiqlaydi.

Natijada, dastur avtomatik ravishda yangi varaq jadval ma'lumotlar regresyon tahlil to'ldiradi. e'tibor bering! Excel, siz bu maqsadda afzal bo'lib o'rnatish imkoniyati bor. Misol uchun, bu qiymatlar Y va X, yoki hatto bir yangi kitob, xususan, bunday ma'lumotlarni saqlash uchun mo'ljallangan Shu lavha, bo'lishi mumkin.

R-maydonida uchun regresyon tahlil natijalari

ko'rib misol ma'lumotlar olingan Excel ma'lumotlar formasini bor:

Avvalo, biz R-kvadratik qiymati e'tibor kerak. Bu aniqlash koeffisiyenti ifodalaydi. Bu misolda, R-kvadrat = 0.755 (75,5%), m. E. modeli hisoblangan parametrlar 75,5% bilan ko'rib parametrlari orasidagi munosabatlarni tushuntirish uchun. aniqlash koeffitsiyentini yuqori qiymati, tanlangan model alohida vazifalar uchun ko'proq foydali bo'lishi hisoblanadi. Bu to'g'ri 0,8 yuqorida R-kvadrat qiymati real vaziyatni tasvirlab ishoniladi. R-kvadrat <0,5, so'ngra Excel bir regresyon oqilona ko'rib bo'lmaydi bo'lsa.

nisbati tahlil

Soni 64,1428 bizning modeli barcha o'zgaruvchilar xi tiklanadi bo'lsa, Y qiymatini bo'ladi, nima ko'rsatadi. Boshqa so'zlar bilan aytganda, bu tahlil parametr qiymati aniq modeli tasvirlangan qaraganda boshqa omillar ta'sir ko'rsatadi, deb aytish mumkin.

Uyali aloqa B18 joylashgan Keyingi omil -0,16285, o'zgaruvchan X muhim ta'siri, bu model ichida xodimlarning o'rtacha ish haqi -0.16285 og'irligi, t dan iste'foga sonini ta'sir degan ma'noni anglatadi Y. uchun ko'rsatadi. E., uning ta'siri darajasi barcha kichik. belgisi "-" koeffitsienti salbiy ekanligini ko'rsatadi. Biz barcha korxona ko'proq ish haqi, kam odamlar ishga yoki ishdan shartnomasini bekor qilish istagini bildirdilar, deb bilaman, chunki ochiq-oydin emas.

ko'p regresyon

Bu muddat ostida shakli bir necha mustaqil o'zgaruvchilar bilan aloqa tenglama anglatadi:

y = f (x ₁ + x ₂ + ... x _n) + ε, y - a xususiyati hisob (qaram o'zgaruvchilar), va x _1, x _2, ... x _m - alomatlar omillar (mustaqil o'zgaruvchilar) bo'ladi.

parametr kiritish

bir necha tushish (MR) uchun u eng kichik kvadratchalar usuli (EKK) yordamida amalga oshiriladi. shakl Y = A + B ₁ x _{1 + ... + b m x m +} ning chiziqli tenglamalar uchun ε normal tenglamalar tizimini barpo (sm. Quyida)

usuli tamoyilini tushunish uchun, biz ikki-faktor ishni ko'rib. Keyin, biz vaziyat formula bilan tasvirlangan qilgan

Shuning uchun, biz olish:

σ qaerda - tegishli xususiyati Varyans bo'lgan, indeks aks.

tenglama MR ko'lamini standartiziruemom uchun MNC qo'llaniladi. Bu holda, biz tenglama olish:

bo'lib, t _y, t _{x 1, ...,} T _XM - o'rtacha qiymatlari 0 bo'lgan o'zgaruvchilar standartiziruemye; _i b - standartlashtirilgan regresyon katsayıları va standart og'ish - 1.

_Men b, deb iltimos unutmang bu holatda o'rtasida shuning uchun bir taqqoslash, normalangan va tsentraliziruemye sifatida belgilangan bir amal va maqbul hisoblanadi. Bundan tashqari, u βi eng qadriyatlarga ega bo'lganlar tashlab, omillar skrining amalga oshirish uchun qabul qilinadi.

chiziqli regresyon Tenglama yordamida bilan muammo

Agar o'tgan 8 oy davomida muayyan mahsulot N narxi dinamikasi jadvali, deb yozilgan. Bu 1850 rubl narxda o'z partiyasi sotib qaror qilish kerak. / T.

	A	B	C
1	oy	oyning nom	Narx N
2	1	Yanvar	tonnasiga 1750 rubl
3	2	fevral	tonnasiga 1755 rubl
4	3	qadam tashlamoq	tonnasiga 1767 rubl
5	4	Aprel	tonnasiga 1760 rubl
6	5	May	tonnasiga 1770 rubl
7	6	iyun	tonnasiga 1790 rubl
8	7	iyul	tonnasiga 1810 rubl
9	8	avgust	tonnasiga 1840 rubl

Yuqorida taqdim misol vositasi "Data tahlil" uchun ma'lum foydalanish uchun zarur, jadval protsessori «Excel» bu muammoni hal qilish uchun. Keyingi, "tushish" bo'limiga va o'rnatish parametrlarini tanlang. (Oy) mustaqil uchun - Biz Kirish interval X »« Input qator Y »va (yil muayyan oylarda bu holda tovarlar narxini) qaram o'zgaruvchilar qiymatlari bir qator joriy bo'lishi kerak", deb yodda tutishimiz kerak. Biz «OK» tugmasini bosish orqali harakatni tasdiqlash. (Shuning uchun ko'rsatilgan bo'lsa), yangi ish sahifasida, biz tushish uchun ma'lumotlarni olish.

Biz ularning ustiga parametrlari A va B deb liniyasi oy soni va nomi koeffitsiyentlarini va regresyon tahlil qilish natijalari bilan varaqning «Y-kesishishi" liniyasi katsayılarıdır shakli y = ax + b, hamda chiziqli tenglama barpo etiladi. Shunday qilib, muammo uchun chiziqli regresyon Tenglama (EQ) 3 kabi yozilishi mumkin:

tovarlar narxi N = 11,714 * 1727.54 oy soni +.

yoki arifmetik notasyonu

y = 11.714 x + 1727,54

natijalarini tahlil

bir necha korrelyatsiya koeffitsiyentlarini (CMC) va qat'iyat, shuningdek test va Fisher t-sinov yordamida etarlicha regresyon tenglama qabul qaror uchun. Ular nomlari bir necha R, R-Square, mos ravishda F-t-statistika va hisobot, ostida harakat natijalari bilan stol "Excel" tushish yilda.

KMC R, mustaqil va qaram o'zgaruvchilar orasidagi yaqinlik olasılıklı munosabatni taxmin qilish imkonini beradi. Uning yuqori qiymati o'zgaruvchan "oyning soni," o'rtasidagi kuchli etarli ulanishini bildiradi "1 tonnasiga rubl N Mahsulot narxi". Biroq, bu munosabatlar tabiati noma'lum.

aniqlash R ² koeffitsiyentini kvadrat (RI), masalan, umumiy tarqalgani ulushi soni bir xususiyati va eksperimental ma'lumotlar qismi bir uchirib ko'rsatadi Doğrusal bir tenglama mos qaram o'zgaruvchilar qiymatlari. Bu muammo, bu qiymati aniq olingan yuqori darajasi bilan E. Statistika SD tasvirlanadi MP., 84.8% bo'lgan.

Bundan tashqari, Fisher mezoni sifatida tanilgan F-statistika, chiziqli qaram yoki uning mavjudligini tasdiqlovchi gipotezani rad ahamiyatini baholash uchun ishlatiladi.

T-statistika (Talabalar t test) qiymati har qanday bepul noma'lum chiziqli bog'liqligi a'zosi koeffisiyenti ahamiyatini baholash yordam beradi. T-sinov> T _CR qiymati _bo'lsa, bepul muddati bir chiziqli tenglama arzimas faraz rad etiladi.

vositalar orqali bepul muddatga bu muammo "Excel" Bu t 169,20903 = aniqlandi, va p = 2,89E-12, T. E. sodiq bepul muddati arzimas gipotezani rad qilinadi nol ehtimoli. noma'lum t = 5,79405 da koeffisiyenti, va p = 0,001158 uchun. Boshqa so'zlar bilan aytganda, bir rad to'g'ri taxmin noma'lum uchun koeffisiyenti ahamiyatsizlik qiladi ehtimoli 0,12% bo'ladi.

Shunday qilib, u etarlicha regresyon tenglama olingan, deb aytish mumkin.

aktsiyalarini sotib maqsadga muvofiqligi muammosi

Ko'p regresyon Shu "Data tahlil" vositasi yordamida Excel amalga oshirildi. maxsus ariza ko'rib chiqaylik.

Guide kompaniyasi «NNN» AK «MMM» aktsiyalari 20% sotib olish uchun qaror kerak. To'plam narxi (SP) 70 million dollarni tashkil etadi. «NNN» mutaxassislari shunga o'xshash bitimlar to'g'risidagi ma'lumotlarni to'plagan. Bunday parametrlar aksiyalarni qiymatini baholash uchun qaror qabul qilindi, kabi AQSh millionlab dollar, ifodalangan:

• qarzlar (VK);
• yillik aylanmasi hajmi (VO);
• bo'lur (VD);
• asosiy fondlarni (SOF) qiymati.

Bundan tashqari, AQSh dollari minglab korxonalar ish haqi qarzlarini (V3 U) foydalaning.

qaror stol protsessor Excel vositasi

Birinchi Kirish ma'lumotlar jadvali yaratish kerak. quyidagicha U:

Keyingi:

• qo'ng'iroq qiling, ", ma'lumotlarni tahlil qilish";
• Tanlangan "tushish" bo'limi;
• Oyna ustun G dan "Input interval Y» boshqariladi qator qaram o'zgarmaydigan qiymati;
• oyna "Kirish interval X» o'ng qizil o'q belgisiga bosing va ustun B, C, D, F, barcha qadriyatlar bir varaq qator xavfsiz holatga

Mark nuqta "yangi ish sahifa» va «OK" tugmasini bosing.

Bu vazifa uchun regresyon tahlil qiling.

o'rganish natijalari va xulosalari

choyshab stol Excel protsessor, regresyon tenglama haqida yuqorida keltirilgan ma'lumotlarga dan yumaloq «yig'ib»:

SD = 0,103 * SOF + 0,541 * VO - 0.031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

yana odatdagi matematik shaklda u yozilishi mumkin:

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

quyidagi jadvalda taqdim «MMM» AK uchun o'tilganlik sanasi:

SOF, AQSh dollari	VO, AQSh dollari	VK, AQSh dollari	VD, AQSh dollari	VZP, AQSh dollari	QK, AQSh dollari
102,5	535,5	45,2	41.5	21,55	64,72

Regresyon tenglama ularni o'rnini bosuvchi, 64,72 million AQSh dollariga teng bo'lgan ko'rsatkichni olingan. Bu ularning qiymati juda 70 million AQSh dollari overpriced, chunki OAJ «MMM» aksiyalari sotib kerak emas, degan ma'noni anglatadi.

Ko'rib turganingizdek, elektron foydalanish "Excel" va regresyon Tenglama muvofiqligi juda aniq bitimni haqida xabardor qaror qabul qilish imkonini berdi.

Endi siz nima bilan tushish bilaman. Yuqorida muhokama Excel misollar, ekonometrinin amaliy muammolarni hal sizga yordam beradi.