Regresia v Excel: príkladoch rovnice. lineárna regresia

Regresná analýza - štatistickou metódou štúdia, ktorá ukazuje závislosť parametra jedného alebo viacerých nezávislých premenných. V pre-počítačovej éry, jeho použitie bolo pomerne ťažké, najmä keď išlo o veľkých objemov dát. V súčasnej dobe učí, ako zostaviť regresii v programe Excel, môžete riešiť zložité štatistické problémy počas niekoľkých minút. Nižšie sú uvedené konkrétne príklady ekonomiky.

typy regresie

Tento koncept bol predstavený do matematiky Francis Galton v roku 1886. Regresie je:

• lineárne;
• parabolický;
• energie;
• exponenciálny;
• hyperbolické;
• exponenciálny;
• logaritmické.

Príklady uskutočnenia vynálezu Príklad 1

Zvažovať problém stanovenia závislosť počtu odstúpenie zamestnancov priemernej mzdy v 6 priemyselných podnikov.

Úloha. Šesť firiem analyzovali priemerný mesačný plat a počet zamestnancov, ktorí prestali dobrovoľne. Vo forme tabuľky máme:

		B	C
1	X	počet rezignácia	plat
2		y	30000 rubľov
3	1	60	35000 rubľov
4	2	35	40000 rubľov
5	3	20	45000 rubľov
6	4	20	50.000 rubľov
7	5	15	55000 rubľov
8	6	15	60000 rubľov

K problému stanovenie závislosť pracovníkov množstvo separačných od priemerného platu 6 podniky regresie modelu má tvar rovnica Y = ₀ + ₁ x ₁ + ... + a _k x _k, kde x _i - ovplyvňujúce veličiny, a _i - regresné koeficienty, AK - rad faktorov.

Y pre danú úlohu - to je indikátorom prepustiť zamestnanca, jedným z faktorov - plat, ktorý je označovaný X.

Využitie silu "Excel" tabuľky

Regresná analýza v Exceli je potrebné predchádzať používaním existujúcich dát v tabuľke vstavaných funkcií. Avšak, pre tieto účely je lepšie použiť veľmi užitočnú "analýzu paketov" Add-in. Ak ju chcete povoliť, je potrebné:

• s záložke "Súbor" ísť na "Nastavenia";
• V okne, ktoré sa otvorí, vyberte, Add-ons ';
• Kliknutím na tlačidlo "Go", ktorá sa nachádza v pravom dolnom rohu linky "riadenia";
• dal zaškrtnutie "analytické" a potvrdiť tlačidlom "OK".

Ak sa to urobí správne, na pravej strane v záložke "Data", ktorá sa nachádza nad pracovný list "Excel", zobrazí požadované tlačidlo.

Lineárna regresia v Exceli

Teraz, keď máte po ruke všetky potrebné nástroje pre virtuálne ekonometrických výpočtov, môžeme začať riešiť náš problém. Postupujte nasledovne:

• kliknutie na tlačidlo na "analýzy dát";
• Kliknutím na tlačidlo "regresia" v otvorenom okne;
• karta, ktorá sa objaví zaviesť rad hodnôt Y (počet separačných pracovníkov) a X (ich plat);
• potvrdzujú ich akcie stlačením «OK» tlačidlo.

V dôsledku toho bude program automaticky vyplní nový hárok tabuľky dát regresnej analýzy. Dávajte pozor! V programe Excel, je tu možnosť nastaviť miesto, ktoré dávate prednosť pre tento účel. Napríklad môže byť rovnaký list, kde sú hodnoty X a Y, alebo dokonca nová kniha, špeciálne pre ukladanie týchto dát.

Výsledky regresnej analýzy pre R-námestie

Údaje získané v Excel považované napríklad dáta majú tvar:

Po prvé, mali by sme venovať pozornosť na hodnotu R-kvadrát. To predstavuje koeficient stanovenia. V tomto príklade je R-kvadrát = 0,755 (75,5%), m. E. Vypočítané parametre modelu vysvetliť vzťah medzi parametrami zvažovaných 75,5%. Čím vyššia je hodnota koeficientu stanovenie, vybraný model je považovaný za užitočnejšie pre konkrétne úlohy. Predpokladá sa, že správne popísať skutočnú situáciu na hodnotu R-square nad 0,8. V prípade, že R-square <0,5, potom regresná analýza v Exceli nemožno považovať za primerané.

analýza pomeru

Počet 64,1428 ukazuje, čo bude hodnotu Y, ak sú všetky premenné xi v našom modeli sa vynuluje. Inými slovami, je možné tvrdiť, že hodnota analyzovaného parametra je ovplyvnená ďalšími faktormi, ako sú opísané v konkrétny model.

Ďalším faktorom -0,16285 nachádza v bunke B18, ukazuje významný vplyv premenné X Y. To znamená, že priemerný plat zamestnancov v rámci modelu ovplyvňuje počet odstúpenie od hmotnosti -0.16285, t. E. Stupeň jeho dopadu vôbec malé. Znak "-" znamená, že koeficient je záporný. Je zrejmé, pretože všetci vieme, že čím viac plat v podniku, tým menej ľudí vyjadrili želanie ukončiť pracovnú zmluvu alebo odmietnuť.

viacnásobná regresná

Pod týmto pojmom sa rozumie komunikačné rovnice s niekoľkých nezávislých premenných tvare:

y = f (x ₁ + x ₂ + ... x _m) + ε, kde y - je funkcia skóre (závislá premenná), a X _1, X _2, ... x _m - sú značky faktory (nezávislé premenné).

parameter Odhad

Pre viacnásobné regresie (MR) sa vykonáva s použitím metódy najmenších štvorcov (LSM). U lineárnych rovníc tvaru Y = a + b ₁ x _{1 + ... + b m x m +} ε budovaní systému normálnych rovníc (cm. Nižšie)

Aby sme pochopili princíp tejto metódy považujeme prípad dvojfaktorové. Potom sme sa situácia popísaná vzorcom

Z tohto dôvodu, dostaneme:

kde σ - je rozptyl príslušnej funkcie, ktorá sa prejavuje v indexe.

MNC sa vzťahuje na rovnice MR k standartiziruemom meradlo. V tomto prípade sa dostaneme rovnicu:

kde t _y, t _{x 1, ...} t _xm - standartiziruemye premenné, za ktorý priemerné hodnoty sú 0; p _i - štandardizované regresné koeficienty a štandardná odchýlka - 1.

Upozorňujeme, že všetky p _i v tomto prípade definovaný ako normalizované a tsentraliziruemye, teda porovnanie medzi považovaný za platný a prijateľné. Okrem toho, že je účelné vykonávať skríning faktorov, odhadzovať tie, ktoré majú najnižšie hodnoty βi.

Problém s použitím lineárnej regresnej rovnici

Predpokladajme, že máte tabuľku dynamiky cenu určitého produktu N za posledných 8 mesiacov. Je potrebné rozhodnúť, či nadobudnutie jeho strany za cenu 1850 rubľov. / T.

		B	C
1	mesiac	názov mesiaca	cena N
2	1	január	1750 rubľov za tonu
3	2	február	1755 rubľov za tonu
4	3	marec	1767 rubľov za tonu
5	4	apríl	1760 rubľov za tonu
6	5	máj	1770 rubľov za tonu
7	6	jún	1790 rubľov za tonu
8	7	júl	1810 rubľov za tonu
9	8	august	1840 rubľov za tonu

Pre vyriešenie tohto problému sa v tabuľkovom procesore "Excel" potrebné na použitie už známe napríklad nástroj "Analýza dát" opísané vyššie. Ďalej zvoľte oddiel "regresné" a nastaviť parametre. Musíme si uvedomiť, že v "Rozsah zadaný Y» by mala byť zavedená na rad hodnôt závislých premenných (v tomto prípade cenu tovaru v určitých mesiacov v roku) a na "Input interval X» - pre nezávislý (mesiac). Máme potvrdenie akcie kliknutím na «OK». V novom liste (ak je to uvedené), získame dáta pre regresie.

Sme v nadväznosti na ne lineárnej rovnice tvaru y = ax + b, kde ako parametre a, b sú koeficienty z číslo riadka v mesiaci a názov koeficientov a «Y-priesečník" rad listu s výsledkami regresnej analýzy. To znamená, že lineárnej regresnej rovnice (EQ) 3 za problém môže byť zapísaný ako:

Cena tovaru N = 11714 * 1727,54měsíc číslo +.

alebo v notáciu

y = 11714 x + 1727,54

analýza výsledkov

Pri rozhodovaní, či prijaté dostatočne lineárnou regresiou s použitím viac korelačné koeficienty (CMC) a určenie, ako aj test a Fisherovho t-testu. V tabuľke "uzavretý" regresie s výsledkami, ktoré pôsobia pod názvami násobok R, R-kvadrát, F-t-štatistiky a štatistiky, v uvedenom poradí.

KMC R umožňuje odhadnúť podobnosti pravdepodobnostné vzťahu medzi nezávislých a závislých premenných. Jeho vysoká hodnota indikuje dostatočne silné spojenie medzi premennej "Počet mesiaca" a "N cene výrobku v rubľov za 1 tonu." Avšak povaha tohto vzťahu nie je známy.

Štvorec koeficientu stanovenie R ² (RI) je číselná charakteristika podielu celkového rozptylu a vykazuje rozptyl experimentálne časti dát, to znamená, hodnoty závislej premennej zodpovedajúce lineárne regresiou. V riešení tohto problému, táto hodnota je 84,8%, teplota topenia. E. Štatistika s vysokým stupňom presnosti zozbieraných sú popísané SD.

F-štatistiky, tiež známe ako Fisher kritérium použiť na posúdenie význam lineárnej závislosti či vyvrátiť hypotézu potvrdzuje jeho existenciu.

Hodnota t-štatistiky (Student t test) umožňuje zhodnotiť význam koeficientu v každom voľnom neznámej priamočiary člen závislosť. Ak je hodnota t-testu> t _cr, hypotéza lineárny rovnica bezvýznamnosti voľný termín je odmietnutý.

Na tento problém pre voľný termín z nástrojov "Excel" sa zistilo, že t = 169,20903 a p = 2,89-12, t. E. mať nulovú pravdepodobnosť, že verný bude odmietnutá hypotézu bezvýznamnosti voľné obdobie. Z neznámych koeficient v čase t = 5,79405, a p = 0,001158. Inými slovami, pravdepodobnosť, že zamietnutá správna hypotéza bezvýznamnosti koeficientu pre neznámeho, je 0,12%.

Tak, možno tvrdiť, že získané lineárnej regresnej rovnice zodpovedajúcim spôsobom.

Problém vhodnosti nákupu akcií

Mnohonásobná regresia bola vykonaná v Exceli s použitím rovnakého "analýza dát" nástroj. Zoberme si konkrétnu aplikáciu.

Guide spoločnosť «NNN» musí rozhodnúť, či ku kúpe 20% akcií spoločnosti JSC «MMM». Cena balíčka (SP) je 70 miliónov amerických dolárov. Špecialisti «NNN» zhromažďované údaje o podobných transakcií. Bolo rozhodnuté, aby posúdil hodnotu akcií na takých parametroch, vyjadrený v miliónoch amerických dolárov, ako sú:

• záväzky (VK);
• Objem ročný obrat (VO);
• pohľadávky (VD);
• Hodnota majetku (SOF).

Okrem toho iba mzdové pohľadávky podnikov (V3 U) tisíc amerických dolárov.

K rozhodnutiu stolný procesor Excel prostriedky

Najprv je potrebné vytvoriť tabuľku vstupných dát. Je to takto:

Ďalšie:

• telefónne búdky "analýza dát";
• Zvolený úsek "regresia";
• okno "Vstupné interval Y» podávané rozsah hodnôt závislých premenných zo stĺpca G;
• na ikonu s červenou šípkou na pravej strane okna "Vstupné interval X» a izolované na rade plechu všetky hodnoty stĺpca B, C, D, F.

Označte bod "Nový list" a kliknite na "OK".

Získajte regresnej analýzy pre túto úlohu.

Výsledky štúdie a závery

"Collect" zaoblené z vyššie uvedených údajov na list tabuľky Excel procesor regresnej rovnice:

SD = 0,103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265.844.

V ďalšie obvyklé matematickej forme, môže byť zapísaný ako:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * X5 - 265.844

Dáta pre «MMM» JSC uvedené v nasledujúcej tabuľke:

SOF, USD	VO, USD	VK, USD	VD, USD	VšZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

Dosadením ich do regresnej rovnice, získal postavu 64,72 miliónov amerických dolárov. To znamená, že akcie JSC «MMM» nemali kupovať, pretože ich cena je docela predražené na 70 miliónov amerických dolárov.

Ako môžete vidieť, použitie tabuľky "Excel" a regresnej rovnice sa nechá urobiť kvalifikované rozhodnutie týkajúce sa vhodnosti celkom konkrétnu transakciu.

Teraz už viete, čo je to regresia. Príklady do Excelu, diskutované vyššie, vám pomôže pri riešení praktických problémov ekonometrie.