Regrese v aplikaci Excel: rovnice, příklady. Lineární regrese

Regresní analýza je statistická metoda vyšetřování, která umožňuje ukázat závislost parametru na jedné nebo několika nezávislých proměnných. V předkompilované éře bylo jeho použití poměrně obtížné, zvláště když se jednalo o velké množství dat. Dnes, když se učíte stavět regresi v aplikaci Excel, můžete vyřešit složité statistické problémy během několika minut. Níže jsou uvedeny konkrétní příklady z oblasti ekonomie.

Typy regrese

Tento koncept byl zaveden do matematiky Francis Galton v roce 1886. Regrese se děje:

• lineární;
• parabolický;
• mocenské právo;
• exponenciální;
• hyperbolická;
• orientační;
• logaritmický.

Příklad 1

Podívejme se na problém určení závislosti počtu členů na kolektivu v důchodu od průměrné mzdy v 6 průmyslových podnicích.

Úkol. Šest podniků analyzovalo průměrný měsíční plat a počet zaměstnanců, kteří opustili svou vlastní vůli. V tabulkové podobě máme:

Pro problém určení závislosti počtu zaměstnanců, kteří odešli na průměrnou mzdu u 6 podniků, má regresní model podobu rovnice Y = a₀ + a₁x₁ +hellip- + a_kx_k, kde x_i - ovlivňování proměnných, a_i - koeficienty regrese a k - počet faktorů.

Pro tento úkol je Y ukazatelem zaměstnanců, kteří odešli, a faktorem, který ovlivňuje, je plat, kterou označuje X.

Použití procesoru tabulky aplikace Excel

Analýzu regrese v aplikaci Excel by mělo předcházet aplikace vestavěných funkcí na dostupné tabulkové údaje. Pro tyto účely je však lepší použít velmi užitečný doplněk "Analysis package". Chcete-li ji aktivovat, potřebujete:

• Na kartě "Soubor" přejděte do sekce "Možnosti".
• v otevřeném okně vyberte řádek "Doplňky";
• Klikněte na tlačítko "Přejít", které je umístěno vpravo od řádku "Řízení".
• Zaškrtněte políčko vedle názvu "Balíček analýzy" a potvrďte své akce klepnutím na tlačítko "Ok".

Pokud je provedena správně, pravé tlačítko se zobrazí na pravé straně karty Údaje umístěné nad tabulkou aplikace Excel.

Lineární regrese v aplikaci Excel

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

• klikněte na tlačítko "Analýza dat";
• v otevřeném okně klikněte na tlačítko "Regrese";
• na kartě, která se objeví, zadáme rozsah hodnot pro Y (počet zaměstnanců, kteří odešli) a X (jejich plat);
• naše akce potvrdíme klepnutím na tlačítko "Ok".

V důsledku toho program automaticky doplní nový list stolního procesoru s daty regresní analýzy. Dávejte pozor! V aplikaci Excel máte možnost nezávisle nastavit místo, které preferujete pro tento účel. Mohlo by to být například stejný list jako hodnoty Y a X nebo dokonce i nová kniha speciálně navržená pro ukládání takových dat.

Analýza výsledků regrese pro čtvereček R

V aplikaci Excel jsou data získaná během zpracování dat z příkladu v podobě:

Především byste měli věnovat pozornost hodnotě čtverce R. Je to koeficient určení. V tomto příkladu R-čtverec = 0.755 (75.5%), tj. Vypočítané parametry modelu vysvětlují závislost mezi hodnotami považovanými za 75.5%. Čím vyšší je hodnota koeficientu určení, zvolený model je považován za použitelnější pro konkrétní úkol. Předpokládá se, že správně popisuje skutečnou situaci s hodnotou R-čtverce vyšší než 0,8. Pokud je R-čtverec<0,5, taková analýza regrese v aplikaci Excel nemůže být považována za rozumnou.

Analýza koeficientů

Číslo 64.1428 ukazuje, jaká bude hodnota Y, pokud budou všechny proměnné xi v uvažovaném modelu vynulovány. Jinými slovy, lze argumentovat, že hodnota analyzovaného parametru je ovlivněna jinými faktory, které nejsou popsány v konkrétním modelu.

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 ";" označuje, že koeficient má zápornou hodnotu. 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íce regrese

Tímto termínem rozumíme rovnici spojení s několika nezávislými proměnnými tvaru:

y = f (x₁+x₂+hellip-x_m) + epsilon-, kde y je výsledný atribut (závislá proměnná) a x₁, x₂, hellip-x_m - to jsou znaky-faktory (nezávislé proměnné).

Vyhodnocení parametrů

Pro vícenásobnou regresi (MP) se provádí metodou nejmenších čtverců (OLS). Pro lineární rovnice tvaru Y = a + b₁x₁ +hellip- + b_mx_m+ epsilon - budeme vytvářet systém normálních rovnic (viz níže)

Chcete-li porozumět principu metody, zvažte dvoufaktorový případ. Pak máme situaci popsanou vzorcem

Proto získáme:

kde sigma- je rozptyl odpovídající charakteristiky odrážející se v indexu.

OLS je použitelný pro rovnici MP v standardní měřítku. V tomto případě získáme rovnici:

ve kterém t_y, t_{x1, hellip-}t_xm - standardizované proměnné, pro které jsou střední hodnoty 0- beta-_i - standardizované regresní koeficienty a směrodatná odchylka je 1.

Vezměte prosím na vědomí vše beta-_i v tomto případě jsou uvedeny jako normalizované a centralizované, takže jejich vzájemné porovnání je považováno za správné a přípustné. Kromě toho je obvyklé vyčíslovat faktory a vyřadit ty, které mají nejnižší hodnoty beta-i.

Problém pomocí rovnice lineární regrese

Předpokládejme, že v posledních 8 měsících je tabulka cenové dynamiky konkrétní komodity N. Je třeba rozhodnout o účelnosti nákupu své šarže za cenu 1850 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. Poté vyberte sekci Regrese a zadejte 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). Potvrďte akci kliknutím na tlačítko "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 komodity N = 11,714 * číslo měsíce + 1727,54.

nebo v algebraické notaci

y = 11,714 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 těsnost pravděpodobnostního vztahu mezi nezávislými a závislými proměnnými. Jeho vysoká hodnota naznačuje relativně silný vztah mezi proměnnými "Číslo měsíce" a "Cena zboží N v rublech na 1 tunu". Nicméně povaha tohoto vztahu zůstává neznáma.

Čtverec koeficientu určení R²(RI) je číselná charakteristika zlomku celkového rozložení a ukazuje rozptýlení, z čehož část experimentálních dat, tj. Hodnoty závislé proměnné odpovídají lineární regresní rovnici. V daném problému je tato hodnota 84,8%, tj. Statistické údaje jsou popsány s vysokou mírou přesnosti přijatým SD.

F-statistická, také nazývaná Fisherova kritéria, se používá k posouzení významu lineární závislosti, vyvrácení nebo potvrzení hypotézy její existence.

Hodnota t-statistiky (Studentův test) pomáhá vyhodnotit význam koeficientu pro neznámé nebo volné termíny lineární závislosti. Pokud hodnota t-test> t_cr, pak je odmítnuta hypotéza nevýznamnosti volného termínu lineární rovnice.

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 argumentovat, že výsledná lineární regresní rovnice je adekvátní.

Problém účelnosti nákupu bloku akcií

Více regrese v aplikaci Excel se provádí pomocí stejného nástroje "Analýza dat". Uvažujme o konkrétním aplikovaném problému.

Správní společnost "NNN" by měla rozhodnout o vhodnosti nákupu 20% podílu v JSC "MMM". Náklady na balíček (SP) činí 70 milionů amerických dolarů. Specialisté z "NNN" shromáždili údaje o podobných transakcích. Bylo rozhodnuto vyhodnotit hodnotu podílu v těchto parametrech, vyjádřených v milionech dolarů, jako:

• splatné účty (VK);
• objem ročního obratu (VO);
• pohledávky (VD);
• hodnoty dlouhodobých aktiv (SOF).

Kromě toho je v tisících amerických dolarů používán parametr nedoplatků na mzdy podniku (V3 P).

Řešení pomocí tabulky aplikace Excel

Nejprve je třeba vytvořit tabulku vstupních dat. Má následující formu:

Další:

• Zavolejte okno "Analýza dat";
• vyberte sekci "Regrese";
• V poli "Vstupní interval Y" zadejte rozsah hodnot závislých proměnných ze sloupce G;
• klikněte na ikonu s červenou šipkou napravo od okna "Vstupní interval X" a vyberte rozsah všech hodnot ze sloupců B, C, D, F na listu.

Označte položku "Nový pracovní list" a klikněte na tlačítko "Ok".

Pro tento úkol je získána regresní analýza.

Výsledky studie a závěry

"Sběr" ze zaokrouhlených dat uvedených výše v tabulce tabulky Excel, regresní rovnice:

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

V známější matematické formě může být zapsána jako:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Údaje pro MMM jsou následující:

Výměnou je do rovnice regrese získáte číslo 64,72 milionů amerických dolarů. To znamená, že akcie MMM by neměly být zakoupeny, protože jejich hodnota 70 milionů amerických dolarů je poněkud nadhodnocená.

Jak vidíte, použití tabulkového procesoru Excel a regresní rovnice umožnily informované rozhodnutí o proveditelnosti velmi specifické transakce.

Nyní víte, jaká je regrese. Příklady v programu Excel, které jsou popsány výše, vám pomohou při řešení praktických problémů z oblasti ekonometrie.