Регресия в Excel: уравнение, примери. Линейна регресия

Регресионният анализ е статистически методПроучване, което показва зависимостта на даден параметър от една или повече независими променливи. В епохата на предкомпютъра, използването му беше доста трудно, особено когато става въпрос за големи количества данни. Днес, след като научите как да изградите регресия в Excel, можете да разрешите сложни статистически проблеми само за няколко минути. По-долу са дадени конкретни примери от областта на икономиката.

Видове регресия

Самата концепция е въведена в математиката от Франсис Галтън през 1886 г. Регресията се случва:

линеен;
параболична;
закон за властта;
експоненциално;
хиперболичен;
експоненциално;
логаритмична.

Пример 1

Нека разгледаме проблема с определянето на зависимостта на броя на пенсионираните членове на колектива от средната работна заплата в 6 промишлени предприятия.

Задачата. Шест предприятия анализираха средната месечна заплата и броя на служителите, които напуснаха собствената си воля. В табличен вид имаме:

	А	B	C
1	X	Брой подадени оставки	заплата
2	X	ш	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₀ + а₁х₁ + ... + a_кх_к, където x_аз - повлияване на променливите, a_аз - коефициенти на регресия и k - брой фактори.

За тази задача Y е индикаторът на служителите, които са напуснали, а факторът, който влияе върху тях, е заплатата, която е означена с X.

Използване на процесор за таблици в Excel

Анализът на регресията в Excel трябва да бъде предхожданприлагане на вградените функции към наличните данни. За тези цели обаче е по-добре да използвате много полезната добавка "Пакет за анализ". За да го активирате, трябва:

От раздела "Файл" отидете в секцията "Опции";
в отворения прозорец изберете реда "Добавки";
Кликнете върху бутона "Отиди", разположен долу, вдясно от линията "Управление";
сложите отметка до "допълнителният модул" и потвърдите действието си, като натиснете "ОК".

Ако е направено правилно, правилният бутон се появява в дясната част на раздела Данни, разположен над работния лист на Excel.

Линейна регресия в Excel

Сега, когато разполагаме с всички необходими виртуални инструменти, за да приложим иконометрични изчисления, можем да започнем да решаваме нашия проблем. За да направите това:

кликнете върху бутона "Анализ на данните";
в отворения прозорец кликнете върху бутона "Регресия";
в съответния раздел вписваме диапазона от стойности за Y (броя на останалите служители) и за X (заплатата им);
потвърждаваме действията си, като кликнете върху бутона "ОК".

В резултат на това програмата автоматично ще се попълнинов регресионен анализ на данните от процесора на табл. таблица. Обърнете внимание! В Excel има възможност самостоятелно да определите мястото, което предпочитате за тази цел. Например, това може да бъде един и същ лист като стойностите Y и X или дори нова книга, специално предназначена за съхранение на такива данни.

Анализ на резултатите от регресията за R-квадрата

В Excel данните, получени по време на обработката на данните от въпросния пример, имат формата:

На първо място, е необходимо да се обърне вниманиестойността на R-квадрата. Това е коефициентът на определяне. В този пример R-квадрата = 0.755 (75.5%), т.е. изчислените параметри на модела обясняват зависимостта между разглежданите параметри с 75.5%. Колкото по-висока е стойността на коефициента на определяне, избраният модел се счита за по-приложим за определена задача. Смята се, че правилно описва реалната ситуация с R-квадратна стойност над 0.8. Ако R-квадрата е <0.5, тогава такъв анализ на регресията в Excel не може да се счита за разумен.

Анализ на коефициентите

Номерът 64.1428 показва каква ще бъде стойността на Y,ако всички променливи xi в разглеждания модел се нулират. С други думи, може да се твърди, че стойността на анализирания параметър е повлияна от други фактори, които не са описани в конкретен модел.

Следният коефициент е -0.166285, разположен вБ18 клетка показва ефекта от теглото на променливата X на Y. Това означава, че средното възнаграждение на служители в модел отразява броя на оставка от теглото на -0.16285, т. Е. степента на неговото въздействие е доста малък. Знакът "-" показва, че коефициентът има отрицателна стойност. Това е очевидно, тъй като всеки знае, че колкото по-висока е заплатата в предприятието, толкова по-малко хора изразяват желание да прекратят трудовия договор или да напуснат.

Многократна регресия

С това понятие имаме предвид уравнението на връзката с няколко независими променливи на формата:

y = f (х₁+ х₂+ ... x_m) + ε, където y е резултантният атрибут (зависима променлива) и x₁, х₂, ... x_m- това са признаци-фактори (независими променливи).

Оценка на параметрите

За множествена регресия (MP), тя се изпълнява с метода на най-малките квадрати (OLS). За линейни уравнения с форма Y = a + b₁х₁ + ... + b_mх_m+ ε изграждаме система от нормални уравнения (виж по-долу)

За да разберете принципа на метода, разгледайте случая с два фактора. След това имаме ситуация, описана от формулата

Оттук получаваме:

където σ е отклонението на съответната характеристика, отразена в индекса.

OLS е приложима към уравнението на MP в стандартизиран мащаб. В този случай получаваме уравнението:

в която t_ш, t_х_{1, ...}т_XM-стандартизирани променливи, за които средните стойности са 0; β_аз - стандартизирани регресионни коефициенти, а стандартното отклонение е 1.

Обърнете внимание, че всички β_азВ този случай те се дават като нормализирани ицентрализирано, така че сравнението между тях се счита за правилно и допустимо. Освен това е обичайно да се изясняват факторите, като се изхвърлят тези, които имат най-ниските стойности на βi.

Проблемът с използването на уравнението за линейна регресия

Да предположим, че има таблица на ценовата динамика на дадена стока N през последните 8 месеца. Необходимо е да се вземе решение за целесъобразността от закупуването на партидата на цена от 1850 рубли на тон.

	А	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" се изисква да използва вече известния инструмент "Анализ на данните". След това изберете секцията "Регресия" и задайте параметрите. Ние трябва да помним, че в "Input обхват Y» трябва да се въведе в диапазон от стойности на зависимата променлива (в този случай цената на стоките в определени месеци от годината) и в "Input интервал X» - за независима (месец). Потвърдете действието, като кликнете върху "Ok". На новия лист (ако е посочено) получават данните за регресията.

На тях изграждаме линейно уравнение с формата y = ax + b, къдетопараметрите на a и b са коефициентите на реда с името на числото на месеца и коефициентите и линиите "Y-пресичане" от листа с резултатите от регресионния анализ. По този начин линейната регресионна уравнение (VR) за проблем 3 е написана като:

Цената на стоката N = 11,714 * е числото на месеца + 1727,54.

или в алгебрична нотация

у = 11.714 х + 1727.54

Анализ на резултатите

За да решите дали резултантното уравнениелинейна регресия, коефициентите на множествена корелация (KMC) и определяне, както и критерият Fisher и теста на Student. В таблицата в Excel с регресионни резултати те се показват под имената на множество R, R-квадрат, F-статистика и t-статистика, съответно.

KMK R дава възможност за оценка на плътносттаВероятностна връзка между независими и зависими променливи. Неговата висока стойност показва относително силна връзка между променливите "Номер на месеца" и "Цената на стоките N в рубли на 1 тон". Въпреки това, естеството на тези отношения остава неизвестно.

Квадратът на коефициента на определяне R²(RI) е цифрова характеристикапроцентът на общия разсейване и показва разсейване на експерименталната част данни, т.е. Стойностите на зависимата променлива съответстват на уравнението на линейната регресия. В този проблем, тази стойност е 84.8%, т.т.. Е. статистика с висока степен на точност, получен са описани SD.

Ф-статистиката, наричана още критерий на Фишър, се използва, за да се оцени значението на линейната зависимост, опровергавайки или потвърждавайки хипотезата за нейното съществуване.

Стойността на t-статистиката (Студентски тест) помага да се оцени значението на коефициента за неизвестен или свободен срок на линейната зависимост. Ако стойността на t-теста> t_кр, тогава хипотезата за незначимостта на свободния термин на линейното уравнение се отхвърля.

В разглеждания проблем за свободния срокизползвайки инструментите на Excel, беше установено, че t = 169.20903 и p = 2.89E-12, т.е. имаме нулевата вероятност правилната хипотеза за незначителността на свободния срок да бъде отхвърлена. За коефициента с неизвестен t = 5.79405 и p = 0.001158. С други думи, вероятността правилната хипотеза за незначителността на коефициента за неизвестното да бъде отхвърлена е 0,12%.

По този начин може да се твърди, че полученото линейно регресионно уравнение е адекватно.

Проблемът за целесъобразността на купуването на блок акции

Многократната регресия в Excel се изпълнява, като се използва същият инструмент "Анализ на данните". Нека разгледаме конкретно приложен проблем.

Управляващото дружество "NNN" трябва да вземе решениеза целесъобразността от закупуването на 20% от MMM. Цената на пакета (SP) е 70 милиона щатски долара. Специалисти от "NNN" събират данни за подобни сделки. Беше решено да се оцени стойността на участието в такива параметри, изразени в милиони щатски долари, като:

дължими сметки (VK);
обем на годишния оборот (VO);
вземания (VD);
стойност на дълготрайните активи (SOF).

Освен това, параметърът за просрочия на заплатите в предприятието (V3 P) се използва в хиляди долари.

Решение, използващо електронна таблица в Excel

На първо място, трябва да създадете таблица с входни данни. Тя има следната форма:

Напред:

Обадете се в прозореца "Данни за анализ";
изберете секцията "Регресия";
В полето "Въведете интервал Y" въведете диапазона от стойности на зависимите променливи от графа G;
кликнете върху иконата с червената стрелка отдясно на прозореца "Интервал на въвеждане X" и изберете диапазона от всички стойности от колони B, C, D, F на листа.

Маркирайте елемента "Нов работен лист" и кликнете върху "Ok".

За тази задача се получава регресионен анализ.

Резултати от проучването и заключения

"Събиране" от закръглените данни, представени по-горе в работния лист на Excel, уравнението на регресията:

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

В по-позната математическа форма тя може да бъде записана като:

у = 0.103 * х1 + 0.541 * х2 - 0.031 * х3 + 0.405 * х4 + 0.691 * х5 - 265.844

Данните за МММ са, както следва:

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, обсъдени по-горе, ще ви помогнат при решаването на практически проблеми от областта на иконометрията.