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

Regresní analýza je statistická technikavýzkum, umožňující ukázat závislost parametru na jedné nebo více nezávislých proměnných. V době před počítačem byla jeho aplikace poměrně obtížná, zejména pokud šlo o velké množství dat. Dnes, když jste se naučili, jak vytvořit regresi v aplikaci Excel, můžete vyřešit složité statistické problémy za pár minut. Níže jsou uvedeny konkrétní příklady z oblasti ekonomiky.

Regresní typy

Samotný koncept zavedl do matematiky Francis Galton v roce 1886. K regresi dochází:

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

Příklad 1

Uvažujme o problému stanovení závislosti počtu zaměstnanců, kteří opustili zaměstnání, na průměrném platu u 6 průmyslových podniků.

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

Pro problém stanovení závislosti počtu propuštěných zaměstnanců na průměrném platu u 6 podniků má regresní model tvar rovnice Y = a₀ + a₁s₁ + ... + a_nas_nakde x_a - ovlivňování proměnných, a_a Jsou regresní koeficienty a k je počet faktorů.

Pro tento úkol je Y indikátor zaměstnanců, kteří odešli, a ovlivňujícím faktorem je plat, který označujeme X.

Využití schopností tabulkového procesoru Excel

Regresní analýze v Excelu musí předcházetPoužití vestavěných funkcí na existující tabulková data. Pro tyto účely je však lepší použít velmi užitečný doplněk „Analysis Package“. K jeho aktivaci potřebujete:

• z karty „Soubor“ přejděte do části „Parametry“;
• v okně, které se otevře, vyberte řádek „Doplňky“;
• klikněte na tlačítko „Go“ umístěné níže vpravo od řádku „Control“;
• zatrhněte vedle názvu „Analytický balíček“ a potvrďte své akce kliknutím na „OK“.

Pokud je vše provedeno správně, požadované tlačítko se objeví na pravé straně karty „Data“ umístěné nad listem „Excel“.

Lineární regrese v Excelu

Nyní, když máme k dispozici všechny potřebné virtuální nástroje pro provádění ekonometrických výpočtů, můžeme začít řešit náš problém. Pro tohle:

• klikněte na tlačítko „Analýza dat“;
• v okně, které se otevře, klikněte na tlačítko „Regrese“;
• na záložce, která se objeví, zadejte rozsah hodnot pro Y (počet zaměstnanců, kteří dali výpověď) a pro X (jejich platy);
• naše akce potvrdíme stisknutím tlačítka „Ok“.

V důsledku toho se program automaticky vyplnínový tabulkový kalkulátor s daty regresní analýzy. Poznámka! Excel má schopnost nezávisle definovat umístění, které pro tento účel upřednostňujete. Může to být například stejný list, který obsahuje hodnoty Y a X, nebo dokonce nový sešit speciálně navržený pro ukládání takových dat.

Analýza výsledků regrese pro R-square

V aplikaci Excel data získaná během zpracování dat uvažovaného příkladu vypadají takto:

V první řadě byste měli věnovat pozornostHodnota R na druhou. Představuje koeficient determinace. V tomto případě R-square = 0,755 (75,5%), to znamená, že vypočítané parametry modelu vysvětlují vztah mezi uvažovanými parametry o 75,5%. Čím vyšší je hodnota koeficientu determinace, tím více je zvolený model považován za vhodnější pro konkrétní úkol. Předpokládá se, že správně popisuje skutečnou situaci, když je hodnota R na druhou vyšší než 0,8. Pokud je R-kvadrát <0,5, pak takovou regresní analýzu v Excelu nelze považovat za rozumnou.

Analýza kurzů

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

Další koeficient je -0,16285, umístěný vbuňka B18, ukazuje význam vlivu proměnné X na Y. To znamená, že průměrný měsíční plat zaměstnanců v rámci uvažovaného modelu ovlivňuje počet těch, kdo přestali, s váhou -0,16285, tj. stupeň jeho vlivu je docela malý. Znak „-“ znamená, že koeficient je záporný. Je to zřejmé, protože každý ví, že čím vyšší je plat v podniku, tím méně lidí vyjadřuje přání ukončit pracovní smlouvu nebo odejít.

Vícenásobná regrese

Tento termín je chápán jako omezující rovnice s několika nezávislými proměnnými tvaru:

y = f (x₁+ x₂+… X_m) + ε, kde y je výsledný znak (závislá proměnná), a x₁, X₂,… X_m - to jsou znaky-faktory (nezávislé proměnné).

Odhad parametrů

U vícenásobné regrese (MR) se provádí pomocí metody nejmenších čtverců (OLS). Pro lineární rovnice tvaru Y = a + b₁s₁ + ... + b_ms_m+ ε sestrojíme soustavu normálních rovnic (viz níže)

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

Odtud získáváme:

kde σ je rozptyl odpovídajícího znaku odrážejícího se v indexu.

OLS se aplikuje na rovnici MR ve standardizovaném měřítku. V tomto případě dostaneme rovnici:

kde t_a, t_s1,…t_xm - standardizované proměnné, pro které je průměr 0; β_a Jsou standardizované regresní koeficienty a standardní odchylka je 1.

Všimněte si, že všechny β_a v tomto případě jsou uvedeny jako normalizované acentralizované, takže jejich srovnání je považováno za správné a platné. Kromě toho je obvyklé filtrovat faktory a vyřadit ty z nich s nejmenšími hodnotami βi.

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

Předpokládejme, že máte tabulku dynamiky cen konkrétního produktu N za posledních 8 měsíců. Je nutné učinit rozhodnutí o vhodnosti nákupu jeho šarže za cenu 1850 rublů / t.

Chcete -li tento problém vyřešit v tabulkovém procesoruExcel potřebuje použít nástroj pro analýzu dat již známý z výše uvedeného příkladu. Dále vyberte sekci "Regrese" a nastavte parametry. Je třeba mít na paměti, že do pole „Vstupní interval Y“ je třeba zadat rozsah hodnot pro závislou proměnnou (v tomto případě ceny za zboží v konkrétních měsících roku) a do pole „Vstup interval X " - pro nezávislou proměnnou (číslo měsíce). Akce potvrdíme kliknutím na „Ok“. Na novém listu (pokud to bylo uvedeno) získáme data pro regresi.

Pomocí nich sestrojíme lineární rovnici tvaru y = ax + b, kdejako parametry a a b jsou koeficienty řádku s názvem čísla měsíce a koeficienty a řádky "Y-průsečík" z listu s výsledky regresní analýzy. Lineární regresní rovnice (RB) pro problém 3 je tedy zapsána jako:

Cena produktu N = 11,714 * číslo měsíce + 1727,54.

nebo v algebraickém zápisu

y = 11,714 x + 1727,54

Analýza výsledků

Rozhodnout, zda je získaná rovnice adekvátnípoužívá se lineární regrese, vícenásobné korelační a determinační koeficienty, dále Fisherův test a Studentův test. V tabulce aplikace Excel s výsledky regrese se jim říká vícenásobné R, R-square, F-statistics a t-statistics.

KMK R umožňuje posoudit těsnostpravděpodobnostní vztah mezi nezávislými a závislými proměnnými. Jeho vysoká hodnota naznačuje poměrně silný vztah mezi proměnnými „Číslo měsíce“ a „Cena produktu N v rublech za 1 tunu“. Povaha tohoto spojení však zůstává neznámá.

Čtvercový koeficient determinace R²(RI) je numerická charakteristikapodíl na celkovém rozptylu a ukazuje rozptyl, která část experimentálních dat, tj. hodnoty závislé proměnné odpovídají lineární regresní rovnici. V uvažovaném problému je tato hodnota 84,8%, tj. Statistické údaje jsou získanou SD popsány s vysokým stupněm přesnosti.

F-statistika, nazývaná také Fisherův test, slouží k posouzení významnosti lineárního vztahu, vyvrácení nebo potvrzení hypotézy jeho existence.

Hodnota t-statistiky (Studentův test) pomáhá posoudit významnost koeficientu s neznámým nebo volným termínem lineárního vztahu. Pokud je hodnota t-testu> t_kr, pak je hypotéza o bezvýznamnosti volného členu lineární rovnice zamítnuta.

V uvažovaném problému pro volný termínpomocí nástrojů Excelu bylo získáno, že t = 169,20903, a p = 2,89E-12, to znamená, že máme nulovou pravděpodobnost, že správná hypotéza o bezvýznamnosti volného výrazu bude zamítnuta. Pro koeficient při neznámém t = 5,79405, a p = 0,001158. Jinými slovy, pravděpodobnost, že bude zamítnuta správná hypotéza o nevýznamnosti koeficientu s neznámou, je 0,12%.

Lze tedy tvrdit, že získaná lineární regresní rovnice je adekvátní.

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

Vícenásobná regrese v aplikaci Excel se provádí pomocí stejného nástroje pro analýzu dat. Zvažme konkrétní aplikovaný úkol.

Vedení společnosti "NNN" musí učinit rozhodnutío proveditelnosti nákupu 20% podílu ve společnosti JSC MMM. Náklady na balíček (JV) jsou 70 milionů USD. Specialisté NNN shromáždili údaje o podobných transakcích. Bylo rozhodnuto vyhodnotit hodnotu bloku akcií podle těchto parametrů, vyjádřených v milionech amerických dolarů, jako:

• závazky (VK);
• objem ročního obratu (VO);
• pohledávky (VD);
• náklady na dlouhodobý majetek (SOF).

Parametrem jsou navíc mzdové nedoplatky podniku (V3 P) v tisících amerických dolarů.

Tabulkové řešení Excel

Nejprve musíte vytvořit tabulku počátečních dat. Vypadá to takto:

Dále:

• vyvolejte okno „Analýza dat“;
• vyberte sekci „Regrese“;
• rozsah hodnot závislých proměnných ze sloupce G se zadává do pole „Vstupní interval Y“;
• klikněte na ikonu s červenou šipkou napravo od okna „Interval zadávání X“ a vyberte na listu rozsah všech hodnot ze sloupců B, C, D, F.

Zaškrtněte položku „Nový pracovní list“ a klikněte na „Ok“.

Získejte regresní analýzu pro daný úkol.

Studium výsledků a závěrů

„Shromáždíme“ regresní rovnici ze zaokrouhlených dat uvedených výše na listu tabulky aplikace Excel:

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

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

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

Data pro JSC „MMM“ jsou uvedena v tabulce:

Jejich dosazením do regresní rovnice dostanemečíslo 64,72 milionu amerických dolarů. To znamená, že akcie JSC „MMM“ by neměly být nakupovány, 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žnilo učinit informované rozhodnutí o vhodnosti velmi konkrétní transakce.

Nyní víte, co je regrese. Výše diskutované příklady v Excelu vám pomohou vyřešit praktické problémy v oblasti ekonometrie.