Рэгрэсія ў Excel: раўнанне, прыклады. Лінейная рэгрэсія

Рэгрэсійная аналіз - гэта статыстычны метад даследавання, які дазваляе паказаць залежнасць таго ці іншага параметру ад адной альбо некалькіх незалежных зменных. У докомпьютерную эру яго прымяненне было дастаткова цяжка, асабліва калі гаворка ішла аб вялікіх аб'ёмах дадзеных. Сёння, даведаўшыся як пабудаваць рэгрэсію ў Excel, можна вырашаць складаныя статыстычныя задачы літаральна за пару хвілін. Ніжэй прадстаўлены канкрэтныя прыклады з галіне эканомікі.

віды рэгрэсіі

Само гэта паняцце было ўведзена ў матэматыку Фрэнсісам Гальтон ў 1886 годзе. Рэгрэсія бывае:

• лінейнай;
• парабалічнай;
• спаважнаю;
• экспаненцыяльнай;
• гіпербалічнай;
• паказальнай;
• лагарыфмічнай.

прыклад 1

Разгледзім задачу вызначэння залежнасці колькасці звольніліся членаў калектыву ад сярэдняй зарплаты на 6 прамысловых прадпрыемствах.

Задача. На шасці прадпрыемствах прааналізавалі сярэднямесячную заработную плату і колькасць супрацоўнікаў, якія звольніліся па ўласным жаданні. У таблічнай форме маем:

	A	B	C
1	Х	колькасць якія звольніліся	зарплата
2		y	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 = а ₀ + а ₁ x ₁ + ... + а _k x _k, дзе х _i - якія ўплываюць зменныя, a _i - каэфіцыенты рэгрэсіі, ak - лік фактараў.

Для гэтых мэтаў Y - гэта паказчык, якія звольніліся, а які ўплывае фактар - зарплата, якую пазначаем X.

Выкарыстанне магчымасцяў таблічнага працэсара «Эксэля»

Аналізу рэгрэсіі ў 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 ў разгляданай намі мадэлі абнуляцца. Іншымі словамі можна сцвярджаць, што на значэнне аналізаванай параметру ўплываюць і іншыя фактары, якія не апісаныя ў канкрэтнай мадэлі.

Наступны каэфіцыент -,16285, размешчаны ў вочку B18, паказвае важкасць ўплыву зменнай Х на Y. Гэта значыць, што сярэднямесячная зарплата супрацоўнікаў у межах разгляданай мадэлі ўплывае на колькасць якія звольніліся з вагой -,16285, т. Е. Ступень яе ўплыву зусім невялікая. Знак «-» паказвае на тое, што каэфіцыент мае адмоўнае значэнне. Гэта відавочна, так як усім вядома, што чым больш зарплата на прадпрыемстве, тым менш людзей выказваюць жаданне скасаваць працоўны дагавор або звальняецца.

Множны рэгрэсія

Пад такім тэрмінам разумеецца раўнанне сувязі з некалькімі незалежнымі зменнымі выгляду:

y = f (x ₁ + x ₂ + ... _x m) + ε, дзе y - гэта выніковы прыкмета (залежная зменная), а x _1, x _2, ... _x m - гэта прыкметы-фактары (незалежныя зменныя).

ацэнка параметраў

Для множнай рэгрэсіі (МР) яе ажыццяўляюць, выкарыстоўваючы метад найменшых квадратаў (МНК). Для лінейных раўнанняў выгляду Y = a + b ₁ x ₁ + ... + _b m x m + ε будуем сістэму нармальных раўнанняў (гл. Ніжэй)

Каб зразумець прынцып метаду, разгледзім двухфакторную выпадак. Тады маем сітуацыю, апісваецца формулай

Адсюль атрымліваем:

дзе σ - гэта дысперсія адпаведнага прыкметы, адлюстраванага ў індэксе.

МНК выкарыстоўваецца і ў дачыненні да раўнанні МР ў стандартизируемом маштабе. У такім выпадку атрымліваем раўнанне:

у якім _t y, _t x _{1, ... t} xm - стандартизируемые зменныя, для якіх сярэднія значэння роўныя 0; _β i - стандартызаваныя каэфіцыенты рэгрэсіі, а сярэднеквадратычнае адхіленне - 1.

Звярніце ўвагу, што ўсе _β i у дадзеным выпадку зададзены, як нарміруемыя і централизируемые, таму іх параўнанне паміж сабой лічыцца карэктным і дапушчальным. Акрамя таго, прынята ажыццяўляць адсеў фактараў, адкідаючы тыя з іх, у якіх найменшыя значэнні βi.

Задача з выкарыстаннем ўраўненні лінейнай рэгрэсіі

Выкажам здагадку, маецца табліца дынамікі цэны канкрэтнага тавару N на працягу апошніх 8 месяцаў. Неабходна прыняць рашэнне аб мэтазгоднасці набыцця яго партыі па цане 1850 руб. / Т.

	A	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 рублёў за тону

Для вырашэння гэтай задачы ў таблічным працэсары «Эксэля» патрабуецца задзейнічаць ужо вядомы па прадстаўленым вышэй прыкладу інструмент «Аналіз дадзеных». Далей выбіраюць раздзел «Рэгрэсія» і задаюць параметры. Трэба памятаць, што ў полі «Уваходны інтэрвал Y» павінен уводзіцца дыяпазон значэнняў для залежнай зменнай (у дадзеным выпадку цэны на тавар у канкрэтныя месяцы года), а ў «Уваходны інтэрвал X» - для незалежнай (нумар месяца). Пацвярджаем дзеянні націскам «Ok». На новым аркушы (калі так было пазначана) атрымліваем дадзеныя для рэгрэсіі.

Будуем па іх лінейнае раўнанне выгляду y = ax + b, дзе ў якасці параметраў a і b выступаюць каэфіцыенты радкі з найменнем нумары месяца і каэфіцыенты і запісы з "Y-скрыжаванне» з ліста з вынікамі Рэгрэсійная аналізу. Такім чынам, лінейнае раўнанне рэгрэсіі (УР) для задачы 3 запісваецца ў выглядзе:

Цана на тавар N = 11,714 * нумар месяца + 1727,54.

або ў алгебраічных пазначэннях

y = 11,714 x + 1727,54

аналіз вынікаў

Каб вырашыць, адэкватна Ці атрыманае ўраўненні лінейнай рэгрэсіі, выкарыстоўваюцца каэфіцыенты множнай карэляцыі (КМК) і дэтэрмінацыі, а таксама крытэр Фішэра і крытэрый Ст'юдэнту. У табліцы «Эксэля» з вынікамі рэгрэсіі яны выступаюць пад назвамі множны R, R-квадрат, F-статыстыка і t-статыстыка адпаведна.

КМК R дае магчымасць ацаніць цеснату імавернаснай сувязі паміж незалежнай і залежнай зменнымі. Яе высокае значэнне сведчыць аб дастаткова моцнай сувязі паміж зменнымі «Нумар месяца» і «Кошт тавару N ў рублях за 1 тону». Аднак, характар гэтай сувязі застаецца невядомым.

Квадрат каэфіцыента дэтэрмінацыі R ² (RI) уяўляе сабой лікавую характарыстыку долі агульнай роскіду і паказвае, роскід якой частцы Эксперыментальная інфармацыя, г.зн. значэнняў залежнай пераменнай адпавядае раўнанні лінейнай рэгрэсіі. У разгляданай задачы гэтая велічыня роўная 84,8%, т. Е. Статыстычныя дадзеныя з высокай ступенню дакладнасці апісваюцца атрыманых УР.

F-статыстыка, якую называюць таксама крытэрыем Фішэра, выкарыстоўваецца для ацэнкі значнасці лінейнай залежнасці, абвяргаючы або пацвярджаючы гіпотэзу пра яе існаванне.

Значэнне t-статыстыкі (крытэрый Ст'юдэнту) дапамагае ацэньваць значнасць каэфіцыента пры невядомай альбо вольнага члена лінейнай залежнасці. Калі значэнне t-крытэру> t _кр, то гіпотэза аб нязначным вольнага члена лінейнага ўраўненні адпрэчваецца.

У разгляданай задачы для вольнага члена з дапамогай інструментаў «Эксэля» было атрымана, што t = 169,20903, а p = 2,89Е-12, т. Е. Маем нулявую верагоднасць таго, што будзе адвергнутая верная гіпотэза аб нязначным вольнага члена. Для каэфіцыента пры невядомай t = 5,79405, а p = 0,001158. Іншымі словамі верагоднасць таго, што будзе адвергнутая верная гіпотэза аб нязначным каэфіцыента пры невядомай, роўная 0,12%.

Такім чынам, можна сцвярджаць, што атрыманае раўнанне лінейнай рэгрэсіі адэкватна.

Задача аб мэтазгоднасці набыцця пакета акцый

Множны рэгрэсія ў Excel выконваецца з выкарыстаннем ўсё таго ж інструмента «Аналіз дадзеных». Разгледзім канкрэтную прыкладную задачу.

Кіраўніцтва кампанія «NNN» павінна прыняць рашэнне аб мэтазгоднасці набыцця 20% пакета акцый АТ «MMM». Кошт пакета (СП) складае 70 млн амерыканскіх долараў. Спецыялістамі «NNN» сабраныя дадзеныя аб аналагічных здзелках. Было прынята рашэнне ацэньваць кошт пакета акцый па такіх параметрах, які выражаны ў мільёнах амерыканскіх долараў, як:

• крэдыторская запазычанасць (VK);
• аб'ём гадавога абароту (VO);
• дэбіторская запазычанасць (VD);
• кошт асноўных фондаў (соф).

Акрамя таго, выкарыстоўваецца параметр запазычанасць прадпрыемства па зарплаце (V3 П) у тысячах амерыканскіх долараў.

Рашэнне сродкамі таблічнага працэсара Excel

Перш за ўсё, неабходна скласці табліцу зыходных дадзеных. Яна мае наступны выгляд:

далей:

• выклікаюць акно «Аналіз дадзеных»;
• выбіраюць раздзел «Рэгрэсія»;
• у акенца «Уваходны інтэрвал Y» ўводзяць дыяпазон значэнняў залежных зменных з слупка G;
• пстрыкаюць па абразку з чырвонай стрэлкай справа ад акна «Уваходны інтэрвал X» і вылучаюць на лісце дыяпазон ўсіх значэнняў з слупкоў B, C, D, F.

Адзначаюць пункт «Новы працоўны ліст» і націскаюць "Ok».

Атрымліваюць аналіз рэгрэсіі для дадзенай задачы.

Вывучэнне вынікаў і высновы

«Збіраем» з акругленых дадзеных, прадстаўленых вышэй на лісце таблічнага працэсара Excel, раўнанне рэгрэсіі:

СП = 0,103 * соф + 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» прадстаўлены ў табліцы:

Соф, USD	VO, USD	VK, USD	VD, USD	VZP, USD	СП, USD
102,5	535,5	45,2	41,5	21,55	64,72

Падставіўшы іх у раўнанне рэгрэсіі, атрымліваюць лічбу ў 64,72 млн амерыканскіх долараў. Гэта значыць, што акцыі АТ «MMM» не варта набываць, так як іх кошт у 70 млн амерыканскіх долараў досыць завышаная.

Як бачым, выкарыстанне таблічнага працэсара «Эксэля» і раўнання рэгрэсіі дазволіла прыняць абгрунтаванае рашэнне адносна мэтазгоднасці цалкам канкрэтнай здзелкі.

Цяпер вы ведаеце, што такое рэгрэсія. Прыклады ў Excel, разгледжаныя вышэй, дапамогуць вам у вырашэнне практычных задач з вобласці эканаметрыка.