Excel бағдарламасында регрессиялық: теңдеу мысалдар. сызықтық регрессия

Регрессиялық талдау - бір немесе бірнеше тәуелсіз айнымалылар параметр тәуелділігін көрсету статистикалық зерттеу әдісі. Алдын-ала компьютерлік дәуірінде, оны пайдалану, ол деректердің үлкен көлемін келді, әсіресе, қиын болды. Бүгін, Excel бағдарламасында регрессиялық салу үйрену, сіз жай ғана бірнеше минут күрделі статистикалық мәселелерді шеше алады. Төменде экономика нақты мысалдары болып табылады.

Регрессия түрлері

Бұл тұжырымдама математика енгізілді Фрэнсис Гальтон арқылы 1886 жылы. Регрессия табылады:

• сызықтық;
• параболалық;
• қуаты;
• экспоненциалды;
• гиперболалық;
• экспоненциалды;
• логарифмдік.

МЫСАЛ 1

6 өнеркәсіптік кәсіпорындарда орташа жалақы құрамы мүшелерінің отставкаға санының тәуелділігін анықтау мәселесін қарастырайық.

Тапсырма. Алты компаниялар орташа айлық жалақы және өз еркімен шығу қызметкерлерінің санын талдау жасадық. кесте түрінде бізде:

	А	B	C
1	X	отставкаға саны	жалақы
2		у	30000 рубль
3	1	60	35000 рубль
4	2	35	40000 рубль
5	3	20	45000 рубль
6	4	20	50,000 рубль
7	5	15	55000 рубль
8	6	15	60000 рубль

соманың тәуелділігін анықтау мәселесіне арналған 6 кәсіпорын регрессиялық модель үшін орташа жалақысынан қызметкерлерін бөлу теңдеу Y нысаны х _{Мен 0} + а х _{1 1} + ... + _К х _K, = болды - әсер айнымалылар, _менің - регрессиялық коэффициенттері, Ақ - факторлардың саны.

бұл қызметкерді, фактордың өрт индикаторы болып табылады - - X. белгіленеді жалақы, берілген тапсырма үшін Y

«Excel» электрондық кестенің күш біріктіруге

Excel бағдарламасында регрессиялық талдау кіріктірілген функциялар қолданыстағы кесте деректеріне өтініш алдында тиіс. Алайда, осы мақсаттар үшін бұл өте пайдалы қондырма «пакеттік талдау» пайдалану жақсы. оны қосу үшін, сіз қажет:

• қойындысы «Файл», «Параметрлер» бөліміне өтіңіз отырып;
• ашылады терезесінде, 'Аддон «таңдаңыз;
• желісі «Менеджмент» төменгі оң жағында орналасқан, «Go» түймешігін басыңыз;
• Келесі «Analysis ToolPak» үшін белгі қойып, «OK» пернесін басу арқылы сіздің әрекетті растау.

дұрыс атқарылған болса, жұмыс парағының «Excel» жоғарыда орналасқан «Деректер» қойындысында, оң жағы қажетті түймені көрсетеді.

Excel сызықтық регрессия

Енді сіз жағынан эконометриялық айырысу үшін барлық қажетті виртуалды құралдар бар екенін, біз мәселені шешу үшін бастау алады. Осыған:

• батырмасы «Деректерді талдау» бойынша басқан;
• Ашық терезеде батырмасын басыңыз «регрессияның» басыңыз;
• құндылықтарды Y (бөліністер қызметкерлердің саны) және X (олардың жалақы) ауқымын енгізу пайда қойындысы;
• «Ok» батырмасын басу арқылы өз іс-әрекеттерін тағы да қуаттаймыз.

Нәтижесінде, бағдарлама автоматты түрде жаңа парақ Кестемен регрессиялық талдау толтырады. Назар аударыңыз! Excel бағдарламасында, сіз осы мақсаттар үшін көреді орын орнату мүмкіндігі бар. Мысалы, ол құндылықтар Y және X, немесе арнайы осындай деректерді сақтау үшін арналған, тіпті жаңа кітап, сол парақ болуы мүмкін.

R-квадрат үшін регрессиялық талдау нәтижелері

саналады мысал деректер алынған Excel деректер түрі бар:

Біріншіден, біз R-квадрат құнына назар аудару керек. Ол анықтау коэффициенті білдіреді. Бұл мысалда, R-квадрат = 0.755 (75,5%), м. Е. моделін есептік параметрлері 75,5% -ға саналады параметрлері арасындағы қарым-қатынасты түсіндіру. анықтау коэффициентінің мәні жоғары, таңдалған модель белгілі бір міндеттерді орындау үшін көп пайдалы болып саналады. Ол дұрыс 0,8 жоғарыда R-квадрат құнмен нақты жағдайды сипаттау үшін деп есептеледі. R-квадрат <0,5 болса, онда Excel бағдарламасында регрессивтік талдау ақылға қонымды деп санауға болмайды.

қатынасы талдау

Саны 64,1428 біздің моделін барлық айнымалылар XI қалпына болады, егер Y құны қандай болады көрсетеді. Басқаша айтқанда, ол талданады параметрдің мәні нақты моделі сипатталған көрсетiлгендерден басқа факторлар әсер деп айтуға болады.

жасуша В18 орналасқан келесі фактор -0,16285, бұл модель шеңберінде қызметкерлердің орташа жалақысы -0.16285 салмағы, т отставкаға саны әсер ететінін білдіреді Y. айнымалы X маңызды әсер көрсетеді. E. барлық оның әсер ету дәрежесі шағын. белгісі «-» коэффициенті теріс екенін көрсетеді. біз барлық кәсіпорында көп жалақы, кем адам жұмыспен қамту шартын бұзуға тілек білдірді немесе жұмыстан босатылған деп білеміз, өйткені анық.

бірнеше регрессия

Бұл термин түрінде бірнеше тәуелсіз айнымалы байланыс теңдеу жатады:

у = F (х ₁ + х ₂ + ... х _м) + у ε, - ... х _м, көркем балл (тәуелді ауыспалы) болып табылады, және х _1, х ₂ - белгілері факторлар (тәуелсіз айнымалылар) болып табылады.

параметр бағалау

көптеген Регрессия (MR) үшін бұл ең кіші квадраттар әдісі (LSM) пайдаланып жүзеге асырылады. түрінде Y = A + B х _{1 1 + ... + B м х м +} сызықтық теңдеулер үшін ε қалыпты теңдеулер жүйесін құру (см. Төменде)

әдістің принципі түсіну үшін, біз екі-фактор істі қарастыру. Содан кейін біз формуламен сипатталған жағдайды бар

Демек, біз алуға:

σ, онда - индексінде көрініс, тиісті мүмкіндіктің дисперсиясы болып табылады.

ҚазМұнайТеңіз standartiziruemom шкала теңдеу MR қолданылады. Бұл жағдайда, біз теңдеуін алуға:

, онда т _у, т _{х 1, ...} T _XM - орташа мәндері 0 болып табылатын айнымалылар standartiziruemye; стандартталған регрессия коэффициенттері және стандартты ауытқу - - _Мен р 1.

_Мен барлық р екенін ескеріңіз бұл жағдайда, сондықтан жарамды болып саналады және қолайлы арасындағы салыстыру, нормаланған және tsentraliziruemye ретінде анықталады. Сонымен қатар, ол βi ең төменгі мәндері бар сол тастар, факторлардың скрининг жүргізу үшін қабылданады.

Сызықтық регрессия теңдеуін пайдалана отырып проблемасы

Сіз соңғы 8 айдағы нақты өнімнің N бағасы динамикасының кестесі бар делік. Ол 1850 рубль бағамен өз партиясының сатып алу туралы шешiм қабылдауға қажет. / Т.

	А	B	C
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 «тәуелді айнымалы (жылдың нақты айында тауарлар бұл жағдайда бағасы) және мәндердің ауқымына енгізу керек» екенін есте сақтау қажет. Біз «Ok» түймесін басу арқылы әрекетті растау. жаңа жұмыс парағына (сондықтан көрсетілген болса), біз Регрессия деректерді алу.

Біз оларға параметрлері А және В коэффициенттерінің айдың және атауы жолдың рет нөмірі және регрессиялық талдау нәтижелерімен парағының «Y-қиылыс» сызығынан коэффициенттер ретінде нысаны у = Ax + B сызықтық теңдеуді, құрудамыз. Осылайша, мәселенің үшін сызықтық регрессия теңдеуі (EQ) 3-ақ жазуға болады:

тауарлардың бағасы N = 11,714 * 1727.54 ай нөмірі +.

немесе алгебралық белгілеу

у = 11.714 х + 1727,54

нәтижелерін талдау

Бірнеше корреляция коэффициенттері (CMC) және анықтау, сондай-ақ тест және балық аулаушылар T-тест арқылы алынған барабар сызықтық регрессия теңдеуінің шешеді. олар тиісінше бірнеше R, R-алаңы, F-Т-статистика және статистика, атаулары бойынша әрекет нәтижелерімен үстел «Excel» Регрессия.

ҚИК R тәуелсіз және тәуелді айнымалылар арасындағы жақындығы ықтималдық қарым-қатынас бағалауға мүмкіндік береді. Оның құны жоғары күшті жеткілікті айнымалы арасындағы байланыс «айдың саны» және көрсетеді «1 тоннасына рубль N Өнім бағасын». Алайда, бұл қарым-қатынас сипаты белгісіз.

анықтау R ² (RI) коэффициентінің квадрат жалпы шашыраудың үлесі сандық сипаттамасы болып табылады және эксперименттік деректер бөлігін, яғни бір ыдыратуды көрсетеді, Сызықтық регрессия теңдеу сәйкес тәуелді айнымалы мәндері. Бұл мәселені, бұл мән 84,8%, МП болып табылады. E. Статистика алынған дәлдік дәрежесі жоғары SD сипатталған.

Сондай-ақ, сызықтық тәуелділік немесе оның бар болуын растайтын жоққа шығаратын гипотезаның маңыздылығын бағалау үшін пайдаланылатын Фишер критерийі ретінде белгілі F-статистика.

T-статистикалық (Студенттік т тест) құны қандай да бір бос белгісіз сызықтық тәуелділігі мүшесі коэффициенті маңызын бағалауға көмектеседі. T-сынақ> T _CR құны _болса, еркін мерзімі сызықтық теңдеудің тым гипотеза қабылданбайды.

құралдар «Excel» арқылы тегін мерзімге осы проблеманы ол T = 169,20903, және р = 2,89E-12, Т екендігі анықталды. E. адал тегін мерзімі маңыздылығының аздығы туралы гипотезаны қабылданбайды деп нөлдік ықтималдығы. Т = 5,79405, және р = 0,001158 белгісіз коэффициентін үшін. Басқаша айтқанда, бұл бас тартқан дұрыс гипотеза белгісіз үшін коэффициентін маңыздылығының аздығы деп ықтималдығы, 0,12% құрайды.

Осылайша, ол тиісті түрде алынған сызықтық регрессия теңдеуі деп айтуға болады.

акцияларды сатып алу орындылығы мәселесі

Бірнеше Регрессия бірдей «Деректерді талдау» құралын пайдаланып Excel жүргізілді. нақты қолдануды қарастырады.

Жол компаниясы «NNN» АҚ «МММ» АҚ акцияларының 20% -ын сатып алу туралы шешім қабылдауға тиіс. Пакеттің бағасы (SP) 70 млн АҚШ долларын құрайды. «NNN» мамандары осыған ұқсас операциялар бойынша деректер жинады. Ол мұндай параметрлер бойынша акциялардың құнын бағалау туралы шешім қабылданды, АҚШ доллары миллиондаған білдірді, мысалы:

• кредиторлық берешек (VK);
• жылдық айналымы көлемі (В.О.);
• дебиторлық берешек (VD);
• негізгі құралдардың (СОФ) мәні.

Сонымен қатар, АҚШ доллары мың (V3 U) кәсіпорындардың жалақы қарыздарын пайдаланыңыз.

шешім үстел процессор Excel құралдары

Біріншіден, сіз кіріс деректердің кестені жасау керек. Ол келесідей болып табылады:

келесі:

• қоңырау жәшіктің «мәліметтерін талдау»;
• Таңдалған «Регрессия» бөлімінде;
• терезе «Кіріс аралығы Y» баған G бастап жүзеге ауқымы тәуелді айнымалы мәндері;
• , Терезе «Input интервал Х» және баған В, С, Д, барлық мәндер парақ ауқымының оқшауланған оң қызыл жебенің белгішесін F. нұқыңыз

Марк нүктесі «Жаңа жұмыс парағы» және «Ok» батырмасын басыңыз.

Осы тапсырманы орындау үшін регрессивтік талдау алыңыз.

зерттеу нәтижелері мен қорытындылары

парақ үстел Excel процессор регрессиялық теңдеу бойынша жоғарыда келтірілген деректерден дөңгелектенеді «жинау»:

SD = 0,103 * СОФ + 0,541 * VO - 0,031 * VK + 0,405 * В.Д. + 0,691 * VZP - 265.844.

көп әдеттегі математикалық түрінде ол ретінде жазуға болады:

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

: «МММ» АҚ төмендегі кестеде көрсетілген арналған деректер

СОФ, АҚШ доллары	VO, АҚШ доллары	VK, АҚШ доллары	В.Д., АҚШ доллары	VZP, АҚШ доллары	БК, АҚШ доллары
102,5	535,5	45,2	41,5	21.55	64,72

Регрессия теңдеуінің оларды алмастыратын, 64,72 млн АҚШ долларын суретті алынған. Бұл олардың құны 70 миллион АҚШ доллары өте бағаны көтеретiн болса, өйткені АҚ «МММ» акциялары, сатып тиіс емес екенін білдіреді.

Сіз, электрондық кестенің пайдалану «Excel» пен орындылық өте нақты мәміле қатысты шешім қабылдауға мүмкіндік берді регрессия теңдеуін Көріп отырғанымыздай.

Енді сіз қандай регрессиялық білеміз. Жоғарыда талқыланды Excel үшін мысалдары, эконометрика практикалық мәселелерді шешуде көмектеседі.