Регресия в 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 = ₀ + ₁ х ₁ + ... + _К х _К, където х _и - влияещи променливи, а _I - регресионни коефициенти, ак - редица фактори.

Y за дадена задача - това е индикатор за пожар служител, допринасящ фактор - заплатата, която е обозначена с X.

Възползвайки се от мощта на електронна таблица "Excel"

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

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

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

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

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

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

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

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

Данните за Excel, получени в разгледаните примерни данни имат следния вид:

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

анализ съотношение

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

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

множествена регресия

Под този термин се отнася до комуникационната уравнение с няколко независими променливи от вида:

Y = е (х ₁ + х ₂ + ... х _т) + ε, където Y - е знак за ефективно (зависима променлива), и X _1, X _2, ... х _m - това са признаци на фактори (независими променливи).

оценка на параметър

За множествена регресия (MR) се извършва с помощта на метод на най-малките квадрати (LSM). За линейни уравнения на форма Y = A + B ₁ х _{1 + ... + б m х m +} ε изграждане на система на нормални уравнения (см. По-долу)

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

Следователно, ние се получи:

където σ - е дисперсията на съответната функция, отразено в индекса.

МНК е приложимо към уравнението MR да standartiziruemom мащаб. В този случай, ние получаваме уравнението:

където т _у, т _{х 1, ...} т _х - standartiziruemye променливи, за които средните стойности са 0; р _и - стандартизирани регресионни коефициенти и стандартно отклонение - 1.

Моля, имайте предвид, че всички р _аз в този случай определя като нормализирани и tsentraliziruemye следователно сравнение между счита за валидно и приемливо. В допълнение, той е приет за извършване на скрининг на фактори, като се отстраняват тези, които имат най-ниските стойности на βi.

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

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

	А	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» - за независима (месец). Ние потвърдите действието, като кликнете върху «ОК». В нов работен лист (ако е посочено), ние получаваме данните за регресия.

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

Цената на стоки N = 11714 * 1727.54месеца номер +.

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

у = 11 714 х + 1727,54

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

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

KMC R дава възможност да се оцени близостта вероятностни връзката между независими и зависими променливи. Високата му стойност показва достатъчно силна връзка между променливата "Брой на месеца" и "N Каталог на цената в рубли за един т." Въпреки това, естеството на тези отношения е неизвестен.

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

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

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

В този проблем за свободен Терминът чрез инструменти "Excel" е установено, че т = 169,20903, и р = 2,89E-12, т. Е. имат нулева вероятност верните ще бъде отхвърлена хипотезата за незначителност на свободната план. За неизвестен коефициент при Т = 5,79405 и р = 0,001158. С други думи, вероятността отхвърли правилната хипотеза ще незначимост на коефициента за неизвестното, е 0,12%.

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

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

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

Ръководство компания «NNN» трябва да реши дали да закупи 20% от акциите на ЗАО «MMM». Пакетна цена (SP) е 70 милиона щатски долара. Специалисти на "NNN» събрани данни за подобни сделки. Решено бе да се направи оценка на стойността на акциите на тези параметри, изразени в милиони щатски долари, като например:

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

В допълнение, използвайте дълговете на заплатите на предприятия (V3 U) в хиляди щатски долара.

решение маса процесорни Excel средство

Първо трябва да се създаде таблица за въвеждане на данни. Това е, както следва:

Напред:

• повикване кутия "анализ на данни";
• избран раздел "регресия";
• прозореца "Input интервал Y» прилага диапазон зависими променливи стойности от колона G;
• кликнете върху иконата с червена стрелка отдясно на прозореца "Input интервал X» и изолирани върху лист гама от всички стойности на колона B, C, D, Е.

Марк точката "New лист" и кликнете "OK".

Вземете регресионен анализ за тази задача.

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

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

SD = 0.103 * СОФ + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265,844.

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

у = 0103 * x1 + 0541 * х2 - 0031 * x3 + 0405 * x4 + 0691 * Х5 - 265844

Данните за «MMM» АД, представени в таблицата по-долу:

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