Regrese v Excel: příkladech rovnice. lineární regrese

Regresní analýza - statistickou metodou studie, která ukazuje závislost parametru jednoho nebo více nezávislých proměnných. V pre-počítačové éry, jeho použití bylo poměrně obtížné, zvláště když šlo o velkých objemů dat. V současné době učí, jak sestavit regresi v aplikaci Excel, můžete řešit složité statistické problémy během několika minut. Níže jsou uvedeny konkrétní příklady ekonomiky.

typy regrese

Tento koncept byl představen do matematiky Francis Galton v roce 1886. Regrese je:

• lineární;
• parabolický;
• energie;
• exponenciální;
• hyperbolické;
• exponenciální;
• logaritmická.

Příklady provedení vynálezu Příklad 1

Zvažovat problém stanovení závislost počtu odstoupení zaměstnanců průměrné mzdy v 6 průmyslových podniků.

Úkol. Šest firem analyzovali průměrný měsíční plat a počet zaměstnanců, kteří přestali dobrovolně. Ve formě tabulky máme:

		B	C
1	X	Počet rezignace	plat
2		y	30000 rublů
3	1	60	35000 rublů
4	2	35	40000 rublů
5	3	20	45000 rublů
6	4	20	50.000 rublů
7	5	15	55000 rublů
8	6	15	60000 rublů

K problému stanovení závislost pracovníků množství separačních od průměrného platu 6 podniky regresním modelu má tvar rovnice Y = ₀ + ₁ x ₁ + ... + a _k x _k, kde x _i - ovlivňující veličiny, a _i - regresní koeficienty, AK - řada faktorů.

Y pro daný úkol - to je indikátorem propustit zaměstnance, jedním z faktorů - plat, který je označován X.

Využití sílu „Excel“ tabulky

Regresní analýza v Excelu je třeba předcházet používání stávajících dat v tabulce vestavěných funkcí. Nicméně, pro tyto účely je lepší použít velmi užitečnou „analýzu paketů“ Add-in. Chcete-li ji povolit, je třeba:

• s záložce „Soubor“ jít na „Nastavení“;
• V okně, které se otevře, zvolte ‚Add-ons‘;
• Kliknutím na tlačítko „Go“, která se nachází v pravém dolním rohu linky „řízení“;
• dal zaškrtnutí „analytické“ a potvrďte akci stisknutím tlačítka „OK“.

Pokud se to udělá správně, na pravé straně v záložce „Data“, která se nachází nad pracovní list „Excel“, zobrazí požadované tlačítko.

Lineární regrese v Excelu

Nyní, když máte po ruce všechny potřebné nástroje pro virtuální ekonometrických výpočtů, můžeme začít řešit náš problém. Postupujte takto:

• kliknutí na tlačítko na „analýzy dat“;
• Kliknutím na tlačítko „regrese“ v otevřeném okně;
• karta, která se objeví zavést řadu hodnot Y (počet separačních pracovníků) a X (jejich plat);
• potvrzují jejich akce stiskem «OK» tlačítko.

V důsledku toho bude program automaticky vyplní nový list tabulky dat regresní analýzy. Dávejte pozor! V aplikaci Excel, je zde možnost nastavit místo, které dáváte přednost pro tento účel. Například může být stejný list, kde jsou hodnoty X a Y, nebo dokonce nová kniha, speciálně pro ukládání těchto dat.

Výsledky regresní analýzy pro R-náměstí

Údaje získané v Excel považovány například data mají tvar:

Za prvé, měli bychom věnovat pozornost na hodnotu R-kvadrát. To představuje koeficient stanovení. V tomto příkladu je R-kvadrát = 0,755 (75,5%), m. E. Vypočtené parametry modelu vysvětlit vztah mezi parametry zvažovaných 75,5%. Čím vyšší je hodnota koeficientu stanovení, vybraný model je považován za užitečnější pro konkrétní úkoly. Předpokládá se, že správně popsat skutečnou situaci na hodnotu R-square nad 0,8. V případě, že R-square <0,5, pak regresní analýza v Excelu nelze považovat za přiměřené.

analýza poměru

Počet 64,1428 ukazuje, co bude hodnotu Y, pokud jsou všechny proměnné xi v našem modelu se vynuluje. Jinými slovy, je možné tvrdit, že hodnota analyzovaného parametru je ovlivněna dalšími faktory, než jsou popsány v konkrétní model.

Dalším faktorem -0,16285 nachází v buňce B18, ukazuje významný vliv proměnné X Y. To znamená, že průměrný plat zaměstnanců v rámci modelu ovlivňuje počet odstoupení od hmotnosti -0.16285, t. E. Stupeň jeho dopadu vůbec malé. Znak „-“ znamená, že koeficient je záporný. Je zřejmé, protože všichni víme, že čím více plat v podniku, tím méně lidí vyjádřili přání ukončit pracovní smlouvu nebo odmítnout.

vícenásobná regresní

Pod tímto pojmem se rozumí komunikační rovnice s několika nezávislých proměnných tvaru:

y = f (x ₁ + x ₂ + ... x _m) + ε, kde y - je funkce skóre (závislá proměnná), a X _1, X _2, ... x _m - jsou značky faktory (nezávislé proměnné).

parametr Odhad

Pro vícenásobné regrese (MR) se provádí s použitím metody nejmenších čtverců (LSM). U lineárních rovnic tvaru Y = a + b ₁ x _{1 + ... + b m x m +} ε budování systému normálních rovnic (cm. Níže)

Abychom pochopili princip této metody považujeme případ dvoufaktorové. Pak jsme se situace popsaná vzorcem

Z tohoto důvodu, dostaneme:

kde σ - je rozptyl příslušné funkce, která se projevuje v indexu.

MNC se vztahuje na rovnice MR k standartiziruemom měřítko. V tomto případě se dostaneme rovnici:

kde t _y, t _{x 1, ...} t _xm - standartiziruemye proměnné, v němž průměrné hodnoty jsou 0; p _i - standardizované regresní koeficienty a standardní odchylka - 1.

Upozorňujeme, že všechny p _i v tomto případě definován jako normalizované a tsentraliziruemye, tedy srovnání mezi považován za platný a přijatelné. Kromě toho, že je účelné provádět screening faktorů, odhazovat ty, které mají nejnižší hodnoty βi.

Problém s použitím lineární regresní rovnici

Předpokládejme, že máte tabulku dynamiky cenu určitého produktu N za posledních 8 měsíců. Je třeba rozhodnout, zda nabytí jeho strany za cenu 1850 rublů. / T.

		B	C
1	měsíc	název měsíce	cena N
2	1	leden	1750 rublů za tunu
3	2	únor	1755 rublů za tunu
4	3	březen	1767 rublů za tunu
5	4	duben	1760 rublů za tunu
6	5	květen	1770 rublů za tunu
7	6	červen	1790 rublů za tunu
8	7	červenec	1810 rublů za tunu
9	8	srpen	1840 rublů za tunu

Pro vyřešení tohoto problému se v tabulkovém procesoru „Excel“ potřebné k použití již známé například nástroj „Analýza dat“ popsaná výše. Dále zvolte oddíl „regresní“ a nastavit parametry. Musíme si uvědomit, že v „Rozsah zadán Y» by měla být zavedena na řadu hodnot závislých proměnných (v tomto případě cenu zboží v určitých měsíců v roce) a na „Input interval X» - pro nezávislý (měsíc). Máme potvrzení akce kliknutím na «OK». V novém listu (je-li to uvedeno), získáme data pro regrese.

Jsme v návaznosti na ně lineární rovnice tvaru y = ax + b, kde jako parametry a, b jsou koeficienty z číslo řádku v měsíci a název koeficientů a «Y-průsečík“ řada listu s výsledky regresní analýzy. To znamená, že lineární regresní rovnice (EQ) 3 za problém může být zapsán jako:

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

nebo v algebraické notaci

y = 11714 x + 1727,54

analýza výsledků

Při rozhodování, zda přijatá dostatečně lineární regresí s použitím více korelační koeficienty (CMC) a určení, jakož i test a Fisherova t-testu. V tabulce „uzavřený“ regrese s výsledky, které působí pod názvy násobek R, R-kvadrát, F-t-statistiky a statistiky, v uvedeném pořadí.

KMC R umožňuje odhadnout podobnosti pravděpodobnostní vztahu mezi nezávislých a závislých proměnných. Jeho vysoká hodnota indikuje dostatečně silné spojení mezi proměnné „Počet měsíce“ a „N ceně výrobku v rublů za 1 tunu.“ Avšak povaha tohoto vztahu není znám.

Čtverec koeficientu stanovení R ² (RI) je číselná charakteristika podílu celkového rozptylu a vykazuje rozptyl experimentální části dat, to znamená, hodnoty závislé proměnné odpovídající lineární regresí. V řešení tohoto problému, tato hodnota je 84,8%, teplota tání. E. Statistika s vysokým stupněm přesnosti získaných jsou popsány SD.

F-statistiky, také známé jako Fisher kritérium použít k posouzení význam lineární závislosti či vyvrátit hypotézu potvrzuje jeho existenci.

Hodnota t-statistiky (Studentův t test) umožňuje zhodnotit význam koeficientu v každém volném neznámé přímočarý člen závislost. Je-li hodnota t-testu> t _cr, hypotéza lineární rovnice bezvýznamnosti volný termín je odmítnut.

Na tento problém pro volný termín z nástrojů „Excel“ bylo zjištěno, že t = 169,20903 a p = 2,89E-12, t. E. mít nulovou pravděpodobnost, že věrný bude odmítnuta hypotézu bezvýznamnost volné období. Z neznámých koeficient v čase t = 5,79405, a p = 0,001158. Jinými slovy, pravděpodobnost, že zamítnuta správná hypotéza bezvýznamnosti koeficientu pro neznámého, je 0,12%.

Tak, lze tvrdit, že získané lineární regresní rovnice odpovídajícím způsobem.

Problém vhodnosti nákupu akcií

Mnohonásobná regrese byla provedena v Excelu s použitím stejného „analýza dat“ nástroj. Vezměme si konkrétní aplikaci.

Guide společnost «NNN» musí rozhodnout, zda ke koupi 20% akcií společnosti JSC «MMM». Cena balíčku (SP) je 70 milionů amerických dolarů. Specialisté «NNN» shromažďovány údaje o podobných transakcí. Bylo rozhodnuto, aby posoudil hodnotu akcií na takových parametrech, vyjádřený v milionech amerických dolarů, jako jsou:

• závazky (VK);
• Objem roční obrat (VO);
• pohledávky (VD);
• Hodnota majetku (SOF).

Kromě toho pouze mzdové pohledávky podniků (V3 U) tisíc amerických dolarů.

K rozhodnutí stolní procesor Excel prostředky

Nejprve je třeba vytvořit tabulku vstupních dat. Je to takto:

Další:

• telefonní budky „analýza dat“;
• Zvolený úsek „regrese“;
• okno „Vstupní interval Y» podávány rozsah hodnot závislých proměnných ze sloupce G;
• na ikonu s červenou šipkou na pravé straně okna „Vstupní interval X» a izolovány na řadě plechu všechny hodnoty sloupce B, C, D, F.

Označte bod „Nový list“ a klikněte na „OK“.

Získejte regresní analýzy pro tento úkol.

Výsledky studie a závěry

„Collect“ zaoblené z výše uvedených údajů na list tabulky Excel procesor regresní rovnice:

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

V další obvyklé matematické formě, může být zapsán jako:

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

Data pro «MMM» JSC uvedené v následující tabulce:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

Dosazením je do regresní rovnice, získal postavu 64,72 milionů amerických dolarů. To znamená, že akcie JSC «MMM» neměli kupovat, protože jejich cena je docela předražené na 70 milionů amerických dolarů.

Jak můžete vidět, použití tabulky „Excel“ a regresní rovnice se nechá učinit informované rozhodnutí týkající se vhodnosti zcela konkrétní transakci.

Teď už víte, co je to regrese. Příklady do Excelu, diskutované výše, vám pomůže při řešení praktických problémů ekonometrie.