Регресија во Excel: равенка примери. линеарна регресија

Регресивна анализа - Метод на статистичка студија за да се покаже на зависноста на параметарот на еден или повеќе независни променливи. Во ерата пред компјутер, неговата употреба е прилично тешко, особено кога станува збор за големи количини на податоци. Денес, учење како да се изгради регресија во Excel, може да се реши сложени статистички проблеми во само неколку минути. Подолу се дадени конкретни примери на економијата.

видови на регресија

Овој концепт беше воведен во математиката од страна на Френсис Галтон во 1886 година. Регресија е:

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

ПРИМЕР 1

Да се разгледа проблемот на утврдување на зависноста на бројот на оставки на членовите на персоналот на просечната плата во 6 индустриски претпријатија.

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

	А	B	C
1	X	Бројот на оставки	плата
2		y	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 = a ₀ + ₁ x ₁ + ... + _к x _k, каде x _i - влијаат променливи, а _јас - регресија коефициенти, AK - број на фактори.

Y за дадена задача - тоа е показател на оган на работникот, што придонесува фактор - плата, што се означува со Х

Искористување на моќта на табела "Excel"

Регресивна анализа во Excel треба да биде проследен и со примена на постоечките податоци маса вградена во функциите. Сепак, за овие цели, тоа е подобро да се користи многу корисен додаток во "пакет анализа". За да се овозможи тоа, треба да:

• со "File" јазичето за да одат во "Settings";
• во прозорецот што ќе се отвори, изберете "Add-ons";
• кликнете на копчето "Врати се", кој се наоѓа во долниот десен агол на линијата "управување";
• стави знак за штиклирање веднаш до "Анализа ToolPak" и потврдете ја вашата акција со притискање на копчето "OK".

Ако се направи правилно, на десната страна од "податоци" табот, кој се наоѓа над работата лист "Excel", покажува саканото копче.

Линеарна регресија во Excel

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

• копче е кликнато на "Анализа на податоци";
• кликнете на копчето "регресија" во отворениот прозорец;
• јазичето, која се појавува да воведе низа на вредности Y (бројот на работници разделби) и Х (плата);
• реафирмира своите постапки со притискање на копчето "Во ред" копче.

Како резултат на тоа, програмата автоматски ќе го пополни нов лист табела регресивна анализа на податоци. Обрни внимание! Во 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 наоѓа во ќелија Б18, покажува големо влијание на променлива X да Y. Ова значи дека просечната плата на вработените во рамките на моделот, влијае на бројот на оставки од тежината на -,16285, т. Е. Степенот на влијанието на сите мал. Знакот "-" покажува дека коефициентот е негативен. Тоа е очигледно, бидејќи сите знаеме дека повеќе плата во претпријатието, толку помалку луѓе изразија желба да го раскине договорот за вработување или отфрли.

повеќе регресија

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

y = f (x ₁ + x ₂ + ... x _m) + ε, каде што y - е карактеристика резултат (зависна променлива), и x _1, x _2, ... x _m - се знаци фактори (независни променливи).

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

За повеќе регресија (МЖ) се врши со помош на методот на најмали квадрати (LSM). За линеарни равенки на форма Y = a + b ₁ x _{1 + ... + b m x m +} ε градење на систем на нормални равенки (cm. Подолу)

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

Според тоа, ние се добие:

каде σ - е варијансата на соодветната функција, што се гледа во индексот.

МНК се применува на равенката МР да standartiziruemom скала. Во овој случај, се добива равенката:

каде што t _y, t _{x 1, ...} t _XM - standartiziruemye променливите за кои просечните вредности се 0; бета _i - стандардизиран регресија коефициенти и стандардната девијација - 1.

Ве молиме имајте во предвид дека сите бета _i во овој случај се дефинира како нормализирани и tsentraliziruemye, според споредбата помеѓу сметаат за валидни и прифатливи. Покрај тоа, таа е прифатена да се изврши проверка на фактори, отфрлајќи оние кои имаат најниски вредности на β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" потребно да се користи веќе познати на пример алатката "Анализа на податоци" презентирани погоре. Следно, изберете делот "регресија" и ја постави параметри. Ние мора да се запамети дека во "Влез опсег Y» треба да се воведе на опсег на вредности на зависната променлива (во овој случај цената на стоката во одредени месеци од годината) и во "Input интервал X» - за независно (на месец). Ние потврди акција со кликнување «Добро». Во нов работен лист (ако тоа е наведено), можеме да ги добие податоците за регресија.

Градиме за нив линеарна равенка од облик y = ax + b, каде како параметри А и Б се на коефициентите од бројот на линијата на месецот и името на коефициенти и "Y-пресек" линија на листот со резултатите од анализата на регресија. Така, на линеарна регресија равенката (EQ) 3 за проблемот може да се запише како:

На цените на стоките N = 11.714 1.727,54 * месец бројот +.

или во алгебарска нотација

y = 11.714 x + 1727,54

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

Да одлучи дали добил соодветно линеарна регресија равенка со користење на повеќе коефициенти на корелација (ЦУК) и одлучност како и тест и т-тест Фишер. Во табелата "Ексел" регресија со резултатите што тие дејствуваат под имињата на повеќе R, R-плоштад, односно F-т-статистика и статистика,.

KMC R овозможува да се процени на блискост веројатна односот меѓу независни и зависни променливи. Нејзината висока вредност покажува доволно силна врска помеѓу променливата "Број на месецот" и "N Цена на производот во рубли по 1 тон". Сепак, природата на овој однос е непознат.

На плоштадот на коефициентот на детерминација R ² (RI) е нумерички карактеристика на дел од вкупната растера и покажува растера на експериментални дел со податоци, на пример, вредности на зависна варијабла што одговара на линеарна регресија равенката. Во овој проблем, оваа вредност е 84,8%, пратеник. Е. статистика со висок степен на точност добиени се опишани SD.

F-статистика, исто така познат како критериум Фишер се користи за да се оцени значајноста на линеарна зависност или негира претпоставка потврдува своето постоење.

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

Во овој проблем за слободен термин преку инструментите на "Ексел", беше откриено дека t = 169,20903, и p = 2,89E-12, т. Е. Дали нулта веројатност дека верниците ќе бидат одбиени хипотезата на незначителноста на слободен термин. Од непознати коефициент на t = 5,79405, и p = 0,001158. Со други зборови, веројатноста дека одбиениот точна хипотезата ќе безначајност на коефициентот за непознатото, е 0,12%.

Така, може да се тврди дека добиените линеарна регресија равенка соодветно.

Проблемот на целисходноста од купување акции

Повеќе регресија е направена во Excel со користење на истите алатката "Анализа на податоци". Размислете за специфична апликација.

Водич компанијата «NNN» мора да одлучи дали да се купи 20% од акциите на АД "MMM». Пакет цена (СП) е 70 милиони американски долари. Специјалисти на «NNN» собираат податоци за слични трансакции. Беше одлучено да се процени вредноста на акциите на овие параметри, изразени во милиони американски долари, како што се:

• обврски (VK);
• волумен годишен промет (VO);
• побарувања (VD);
• вредност на основни средства (СОФ).

Покрај тоа, користете долгови на платите на претпријатија (V3 U) во илјади САД долари.

Одлуката маса процесор Excel средства на

Прво што треба да се создаде маса на влезни податоци. Тоа е како што следува:

Следно:

• повик кутија "анализа на податоци";
• избраниот дел "регресија";
• на прозорецот "Влез интервал Y» администрира опсег зависна променлива вредности од колона G;
• кликнете на иконата со црвена стрелка на десната страна на прозорецот "Влез интервал X" и изолирани на лист спектар на сите вредности од колона Б, Ц, Д, Ѓ

Означи точката "Нови лист" и кликнете на "OK".

Земи регресивна анализа за оваа задача.

Резултатите од истражувањето и заклучоци

"Собери" заоблени од горенаведените податоци на лист Excel табела процесор регресија равенка:

SD = 0,103 * SOF + 0541 * VO - 0031 * В.К. + 0405 + 0691 * В.Д. * VZP - 265.844.

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

y = 0103 * 0541 * X1 + X2 - 0031 * X3 + 0405 * X4 + 0691 * X5 - 265.844

Податоци за "МММ" АД прикажани во табелата подолу:

SOF, долари	VO, долари	В.К., долари	В.Д., долари	VZP, долари	JV, долари
102.5	535,5	45,2	41,5	21,55	64,72

ги замена во равенката на регресија, добива бројка од 64,72 милиони американски долари. Ова значи дека на акции на АД "MMM" не треба да се купи, бидејќи нивната цена е доста прескапата на 70 милиони американски долари.

Како што можете да видите, со користење на табела "Ексел" и регресивната равенка е дозволено да се донесе информирана одлука во однос на препорачливоста доста специфичен трансакција.

Сега знаете што регресија. Примери во Excel, беше дискутирано погоре, ќе ви помогнат во решавање на практични проблеми на економетријата.