Možnosti exponenciální regrese v Excelu. Korelační a regresní analýza v Excelu: instrukce pro provedení

Regresní přímka je grafickým odrazem vztahu mezi jevy. V Excelu můžete snadno vytvořit regresní přímku.

K tomu potřebujete:

1. Otevřete program Excel

2. Vytvořte sloupce s daty. V našem příkladu vybudujeme regresní linii neboli vztah mezi agresivitou a pochybnostmi o sobě samém u prvňáčků. Experimentu se zúčastnilo 30 dětí, data jsou uvedena v excelové tabulce:

1 sloupec - číslo předmětu

2 sloupec - agresivita v bodech

3 sloupec - nesmělost v bodech

3. Poté je potřeba vybrat oba sloupce (bez názvu sloupce), stisknout tabulátor vložit , Vybrat směřovat a z navrhovaných rozvržení vyberte úplně první tečka se značkami .

4. Získali jsme tedy blank pro regresní přímku - tzv. bodový diagram. Chcete-li přejít na regresní přímku, musíte kliknout na výsledný obrázek a kliknout na kartu konstruktér, najít na panelu rozložení grafů a vybrat si M A ket9 , říká také f(x)

5. Máme tedy regresní přímku. V grafu je také znázorněna její rovnice a druhá mocnina korelačního koeficientu

6. Zbývá doplnit název grafu, název os. Pokud si to přejete, můžete také odstranit legendu, snížit počet vodorovných čar mřížky (tab rozložení , pak síť ). Hlavní změny a nastavení se provádějí v záložce Rozložení

Regresní přímka je postavena v MS Excel. Nyní jej lze doplnit do textu práce.

Balíček MS Excel vám umožňuje provést většinu práce velmi rychle při konstrukci lineární regresní rovnice. Je důležité pochopit, jak interpretovat výsledky. Chcete-li sestavit regresní model, vyberte Nástroje\Analýza dat\Regrese (v aplikaci Excel 2007 se tento režim nachází v části Data/Analýza dat/Regrese). Poté zkopírujte získané výsledky do bloku pro analýzu.

Regresní analýza je statistická výzkumná metoda, která umožňuje ukázat závislost parametru na jedné nebo více nezávislých proměnných. V předpočítačové době bylo jeho použití poměrně obtížné, zvláště když šlo o velké objemy dat. Dnes, když jste se naučili, jak vytvořit regresi v aplikaci Excel, můžete vyřešit složité statistické problémy během několika minut. Níže uvádíme konkrétní příklady z oblasti ekonomie.

Typy regrese

Samotný koncept byl zaveden do matematiky v roce 1886. Regrese se děje:

• lineární;
• parabolický;
• Napájení;
• exponenciální;
• hyperbolický;
• demonstrativní;
• logaritmický.

Příklad 1

Zvažte problém stanovení závislosti počtu členů týmu v důchodu na průměrné mzdě v 6 průmyslových podnicích.

Úkol. U šesti podniků jsme analyzovali průměrnou měsíční mzdu a počet zaměstnanců, kteří odešli z vlastní vůle. V tabulkové podobě máme:

Pro problém stanovení závislosti počtu odcházejících pracovníků na průměrné mzdě v 6 podnicích má regresní model tvar rovnice Y = a 0 + a 1 x 1 +…+a k x k , kde x i jsou ovlivňující proměnné , a i jsou regresní koeficienty, a k je počet faktorů.

U tohoto úkolu je Y ukazatel zaměstnanců, kteří odešli, a ovlivňujícím faktorem je mzda, kterou označujeme X.

Použití možností tabulky "Excel"

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

• na kartě "Soubor" přejděte do části "Možnosti";
• v okně, které se otevře, vyberte řádek "Doplňky";
• klikněte na tlačítko "Přejít" umístěné dole, napravo od řádku "Správa";
• zaškrtněte políčko vedle názvu „Analytický balíček“ a potvrďte své akce kliknutím na „OK“.

Pokud je vše provedeno správně, zobrazí se požadované tlačítko na pravé straně karty Data, která se nachází nad pracovním listem aplikace Excel.

v Excelu

Nyní, když máme po ruce 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 zobrazené kartě zadejte rozsah hodnot pro Y (počet zaměstnanců, kteří odešli) a pro X (jejich platy);
• Naše akce potvrdíme stisknutím tlačítka "Ok".

Výsledkem je, že program automaticky vyplní nový list tabulky daty regresní analýzy. Poznámka! Excel má možnost ručně nastavit umístění, které pro tento účel preferujete. Může to být například stejný list, kde jsou 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-kvadrát

V Excelu vypadají data získaná při zpracování dat uvažovaného příkladu takto:

Nejprve byste měli věnovat pozornost hodnotě R-čtverce. Je to koeficient determinace. V tomto příkladu R-kvadrát = 0,755 (75,5 %), tj. vypočtené parametry modelu vysvětlují vztah mezi uvažovanými parametry o 75,5 %. Čím vyšší je hodnota koeficientu determinace, tím vhodnější je zvolený model pro konkrétní úlohu. Předpokládá se, že správně popisuje skutečnou situaci s hodnotou R-squared nad 0,8. Pokud R-kvadrát<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Poměrová analýza

Číslo 64,1428 ukazuje, jaká bude hodnota Y, pokud jsou všechny proměnné xi v modelu, o kterém uvažujeme, nastaveny na nulu. Jinými slovy, lze tvrdit, že hodnotu analyzovaného parametru ovlivňují i ​​další faktory, které nejsou v konkrétním modelu popsány.

Další koeficient -0,16285 umístěný v buňce B18 ukazuje váhu vlivu proměnné X na Y. To znamená, že průměrná měsíční mzda zaměstnanců v rámci uvažovaného modelu ovlivňuje počet odcházejících s váhou -0,16285, tzn. míra jeho vlivu vůbec malá. Znaménko "-" znamená, že koeficient má zápornou hodnotu. To je 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 ukončit pracovní poměr.

Vícenásobná regrese

Tento termín se vztahuje na rovnici spojení s několika nezávislými proměnnými ve tvaru:

y \u003d f (x 1 + x 2 + ... x m) + ε, kde y je efektivní znak (závislá proměnná) a x 1, x 2, ... x m jsou faktory faktoru (nezávislé proměnné).

Odhad parametrů

Pro vícenásobnou regresi (MR) se provádí metodou nejmenších čtverců (OLS). Pro lineární rovnice tvaru Y = a + b 1 x 1 +…+b m x m + ε sestrojíme soustavu normálních rovnic (viz níže)

Abyste pochopili princip metody, zvažte dvoufaktorový případ. Pak máme situaci popsanou vzorcem

Odtud dostáváme:

kde σ je rozptyl odpovídajícího znaku vyjádřený v indexu.

LSM je použitelný pro MP rovnici na standardizovaném měřítku. V tomto případě dostaneme rovnici:

kde t y, t x 1, … t xm jsou standardizované proměnné, jejichž střední hodnoty jsou 0; β i jsou standardizované regresní koeficienty a směrodatná odchylka je 1.

Upozorňujeme, že všechna β i jsou v tomto případě nastavena jako normalizovaná a centralizovaná, takže jejich vzájemné srovnání je považováno za správné a přípustné. Kromě toho je obvyklé odfiltrovat faktory a vyřadit ty s nejmenšími hodnotami βi.

Problém s lineární regresní rovnicí

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

Chcete-li tento problém vyřešit v tabulce Excel, musíte použít nástroj Analýza 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 v poli „Interval vstupu Y“ je třeba zadat rozsah hodnot pro závislou proměnnou (v tomto případě cenu produktu v konkrétních měsících roku) a v poli „Vstup interval X" - pro nezávislou proměnnou (číslo měsíce). Potvrďte akci kliknutím na „OK“. Na novém listu (pokud to bylo naznačeno) získáme data pro regresi.

Na jejich základě sestavíme lineární rovnici ve tvaru y=ax+b, kde parametry a a b jsou koeficienty řádku s názvem čísla měsíce a koeficienty a řádek „průsečík Y“ z řádku list s výsledky regresní analýzy. Rovnice lineární regrese (LE) 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ů

Pro rozhodnutí, zda je výsledná lineární regresní rovnice adekvátní, se používají vícenásobné korelační koeficienty (MCC) a determinační koeficienty, dále Fisherův test a Studentův test. V excelové tabulce s výsledky regrese se objevují pod názvy více R, R-kvadrát, F-statistika a t-statistika.

KMC R umožňuje posoudit těsnost pravděpodobnostního vztahu mezi nezávislými a závislými proměnnými. Jeho vysoká hodnota ukazuje na poměrně silný vztah mezi proměnnými „Číslo měsíce“ a „Cena zboží N v rublech za 1 tunu“. Povaha tohoto vztahu však zůstává neznámá.

Druhá mocnina koeficientu determinace R 2 (RI) je číselnou charakteristikou podílu na celkovém rozptylu a ukazuje rozptyl té které části experimentálních dat, tzn. hodnoty závislé proměnné odpovídají lineární regresní rovnici. V uvažovaném problému je tato hodnota rovna 84,8 %, tj. statistická data jsou s vysokou mírou přesnosti popsána získaným SD.

F-statistika, nazývaná také Fisherův test, se používá k posouzení významnosti lineárního vztahu, vyvracejícího nebo potvrzujícího hypotézu o jeho existenci.

(Studentovo kritérium) pomáhá vyhodnotit významnost koeficientu s neznámým nebo volným členem lineárního vztahu. Pokud je hodnota t-kritéria > t cr, pak je hypotéza o nevýznamnosti volného členu lineární rovnice zamítnuta.

V uvažované úloze pro volný člen bylo pomocí nástrojů Excelu získáno, že t = 169,20903 a p = 2,89E-12, tj. máme nulovou pravděpodobnost, že správná hypotéza o nevýznamnosti volného členu bude odmítl. Pro koeficient při neznámé hodnotě t=5,79405 a p=0,001158. Jinými slovy, pravděpodobnost, že bude zamítnuta správná hypotéza o nevýznamnosti koeficientu pro neznámou, je 0,12 %.

Lze tedy tvrdit, že výsledná lineární regresní rovnice je adekvátní.

Problém účelnosti nákupu balíku akcií

Vícenásobná regrese v Excelu se provádí pomocí stejného nástroje pro analýzu dat. Zvažte konkrétní aplikovaný problém.

Vedení NNN musí rozhodnout o vhodnosti nákupu 20% podílu v MMM SA. Cena balíčku (JV) je 70 milionů amerických dolarů. Specialisté NNN shromáždili data o podobných transakcích. Bylo rozhodnuto ohodnotit hodnotu balíku akcií podle takových parametrů, vyjádřených v milionech amerických dolarů, jako:

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

Kromě toho se používá parametr nedoplatky mezd podniku (V3 P) v tisících amerických dolarů.

Řešení pomocí tabulky Excel

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

• vyvolejte okno "Analýza dat";
• vyberte sekci "Regrese";
• do pole "Interval vstupu Y" zadejte rozsah hodnot závislých proměnných ze sloupce G;
• klikněte na ikonu s červenou šipkou vpravo od okna "Interval vstupu X" a vyberte rozsah všech hodnot ze sloupců B, C, D, F na listu.

Vyberte „Nový list“ a klikněte na „OK“.

Získejte regresní analýzu pro daný problém.

Přezkoumání výsledků a závěrů

„Shromažďujeme“ ze zaokrouhlených údajů uvedených výše v tabulkovém procesoru Excel, regresní rovnice:

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

Ve známější matematické formě to lze napsat jako:

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

Údaje pro JSC "MMM" jsou uvedeny v tabulce:

Dosadíme-li je do regresní rovnice, dostanou číslo 64,72 milionů amerických dolarů. To znamená, že akcie JSC MMM by se neměly kupovat, protože jejich hodnota 70 milionů amerických dolarů je poněkud nadhodnocená.

Jak vidíte, použití excelové tabulky a regresní rovnice umožnilo učinit informované rozhodnutí o proveditelnosti velmi specifické transakce.

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

Sestavení lineární regrese, odhad jejích parametrů a jejich významnosti lze mnohem rychleji provést pomocí analytického balíku Excel (Regression). Uvažujme interpretaci získaných výsledků v obecném případě ( k vysvětlující proměnné) podle příkladu 3.6.

Stůl regresní statistiky jsou uvedeny hodnoty:

Násobek R – koeficient vícenásobné korelace ;

R- náměstí– koeficient determinace R 2 ;

Normalizované R - náměstí- upraveno R 2 upraveno pro počet stupňů volnosti;

standardní chyba je standardní chyba regrese S;

Pozorování - počet pozorování n.

Stůl Analýza rozptylu dáno:

1. Sloupec df - počet stupňů volnosti rovný

pro řetězec Regrese df = k;

pro řetězec Zbytekdf = n – k – 1;

pro řetězec Celkovýdf = n– 1.

2. Sloupec SS- součet čtverců odchylek, rovný

pro řetězec Regrese ;

pro řetězec Zbytek ;

pro řetězec Celkový .

3. Sloupec SLEČNA rozptyly určené vzorcem SLEČNA = SS/df:

pro řetězec Regrese– rozptyl faktorů;

pro řetězec Zbytek je zbytkový rozptyl.

4. Sloupec F - vypočtená hodnota F-kritéria vypočtená podle vzorce

F = SLEČNA(regrese)/ SLEČNA(zbytek).

5. Sloupec Význam F je hodnota hladiny významnosti odpovídající vypočtené F-statistika .

Význam F= FRIST( F- statistika, df(regrese), df(zbytek)).

Pokud význam F < стандартного уровня значимости, то R 2 je statisticky významný.

Tato tabulka ukazuje:

1. Kurzy– hodnoty koeficientů A, b.

2. Standardní chyba jsou standardní chyby regresních koeficientů S a, Sb.

3. t- statistika– vypočtené hodnoty t -kritéria vypočítaná podle vzorce:

t-statistika = koeficienty / standardní chyba.

4.R-hodnota (významnost t) je hodnota hladiny významnosti odpovídající vypočtené t- statistika.

R-hodnota= STUDRASP(t-statistika, df(zbytek)).

Li R-význam< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. Spodních 95 % a horních 95 % jsou dolní a horní hranice 95% intervalu spolehlivosti pro koeficienty teoretické lineární regresní rovnice.

Stůl ZBÝVAJÍCÍ ODBĚR uvedeno:

ve sloupci Pozorování– číslo pozorování;

ve sloupci předpověděl y jsou vypočtené hodnoty závislé proměnné;

ve sloupci Zůstává E je rozdíl mezi pozorovanými a vypočtenými hodnotami závislé proměnné.

Příklad 3.6. Dostupné údaje (arb. jednotky) o výdajích na potraviny y a příjem na hlavu X pro devět skupin rodin:

Pomocí výsledků analytického balíčku Excel (Regrese) analyzujeme závislost nákladů na potraviny na hodnotě příjmu na hlavu.

Výsledky regresní analýzy se obvykle zapisují takto:

kde v závorkách jsou standardní chyby regresních koeficientů.

Regresní koeficienty A = 65,92 a b= 0,107. Směr komunikace mezi y A X určuje znaménko regresního koeficientu b= 0,107, tzn. vztah je přímý a pozitivní. Součinitel b= 0,107 ukazuje, že při zvýšení příjmu na hlavu o 1 arb. Jednotky náklady na potraviny se zvyšují o 0,107 konv. Jednotky

Odhadneme význam koeficientů získaného modelu. Význam koeficientů ( a, b) se kontroluje proti t- test:

p-hodnota ( A) = 0,00080 < 0,01 < 0,05

p-hodnota ( b) = 0,00016 < 0,01 < 0,05,

proto ty koeficienty ( a, b) jsou významné na 1% hladině a ještě více na 5% hladině významnosti. Regresní koeficienty jsou tedy významné a model je adekvátní původním datům.

Výsledky regresního odhadu jsou kompatibilní nejen se získanými hodnotami regresních koeficientů, ale také s některými jejich sadami (interval spolehlivosti). S pravděpodobností 95 % jsou intervaly spolehlivosti pro koeficienty (38,16 - 93,68) pro A a (0,0728 - 0,142) pro b.

Kvalita modelu se posuzuje koeficientem determinace R 2 .

Hodnota R 2 = 0,884 znamená, že faktor příjmu na hlavu může vysvětlit 88,4 % variace (rozptyl) ve výdajích na potraviny.

Význam R 2 zkontroloval F- test: významnost F = 0,00016 < 0,01 < 0,05, следовательно, R 2 je významný na 1% hladině a ještě více na 5% hladině významnosti.

V případě párové lineární regrese lze korelační koeficient definovat jako . Získaná hodnota korelačního koeficientu naznačuje, že vztah mezi výdaji na potraviny a příjmem na hlavu je velmi těsný.

Regresní a korelační analýza - statistické metody výzkumu. Toto jsou nejběžnější způsoby, jak ukázat závislost parametru na jedné nebo více nezávislých proměnných.

Níže se na konkrétních praktických příkladech podíváme na tyto dvě mezi ekonomy velmi oblíbené analýzy. Uvedeme také příklad získání výsledků při jejich kombinaci.

Regresní analýza v Excelu

Ukazuje vliv některých hodnot (nezávislých, nezávislých) na závisle proměnnou. Například, jak závisí počet ekonomicky aktivního obyvatelstva na počtu podniků, mzdách a dalších parametrech. Nebo: jak zahraniční investice, ceny energií atd. ovlivňují výši HDP.

Výsledek analýzy umožňuje stanovit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí, činit manažerská rozhodnutí.

Regrese se děje:

• lineární (y = a + bx);
• parabolický (y = a + bx + cx 2);
• exponenciální (y = a * exp(bx));
• mocnina (y = a*x^b);
• hyperbolický (y = b/x + a);
• logaritmické (y = b * ln(x) + a);
• exponenciální (y = a * b^x).

Zvažte příklad vytvoření regresního modelu v Excelu a interpretaci výsledků. Vezměme si lineární typ regrese.

Úkol. U 6 podniků byla analyzována průměrná měsíční mzda a počet zaměstnanců, kteří odešli. Je třeba určit závislost počtu zaměstnanců v důchodu na průměrné mzdě.

Lineární regresní model má následující podobu:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Kde a jsou regresní koeficienty, x jsou ovlivňující proměnné a k je počet faktorů.

V našem příkladu je Y indikátorem odchodu pracovníků. Ovlivňujícím faktorem jsou mzdy (x).

Excel má vestavěné funkce, které lze použít k výpočtu parametrů lineárního regresního modelu. Ale doplněk Analysis ToolPak to udělá rychleji.

Aktivujte si výkonný analytický nástroj:

Po aktivaci bude doplněk dostupný na kartě Data.

Nyní se budeme zabývat přímo regresní analýzou.

V první řadě věnujeme pozornost R-kvadrátu a koeficientům.

R-kvadrát je koeficient determinace. V našem příkladu je to 0,755 nebo 75,5 %. To znamená, že vypočtené parametry modelu vysvětlují vztah mezi studovanými parametry ze 75,5 %. Čím vyšší je koeficient determinace, tím lepší je model. Dobré - nad 0,8. Špatná - méně než 0,5 (takovou analýzu lze stěží považovat za rozumnou). V našem příkladu - "není špatné".

Koeficient 64,1428 ukazuje, jaké bude Y, pokud se všechny proměnné v uvažovaném modelu rovnají 0. To znamená, že hodnotu analyzovaného parametru ovlivňují i ​​další faktory, které nejsou v modelu popsány.

Koeficient -0,16285 ukazuje váhu proměnné X na Y. To znamená, že průměrná měsíční mzda v rámci tohoto modelu ovlivňuje počet odcházejících s váhou -0,16285 (to je malá míra vlivu). Znak „-“ označuje negativní dopad: čím vyšší plat, tím méně odvykání. Což je spravedlivé.

Korelační analýza v Excelu

Korelační analýza pomáhá určit, zda existuje vztah mezi ukazateli v jednom nebo dvou vzorcích. Například mezi dobou provozu stroje a náklady na opravy, cenou zařízení a dobou provozu, výškou a hmotností dětí atd.

Pokud existuje vztah, pak zda zvýšení jednoho parametru vede ke zvýšení (pozitivní korelace) nebo snížení (negativní) druhého. Korelační analýza pomáhá analytikovi určit, zda hodnota jednoho ukazatele může předpovědět možnou hodnotu jiného ukazatele.

Korelační koeficient se značí r. Pohybuje se od +1 do -1. Klasifikace korelací pro různé oblasti se bude lišit. Když je hodnota koeficientu 0, není mezi vzorky žádný lineární vztah.

Zvažte, jak použít Excel k nalezení korelačního koeficientu.

K nalezení párových koeficientů se používá funkce CORREL.

Úkol: Určete, zda existuje vztah mezi provozní dobou soustruhu a náklady na jeho údržbu.

Umístěte kurzor do libovolné buňky a stiskněte tlačítko fx.

1. V kategorii "Statistické" vyberte funkci CORREL.
2. Argument "Pole 1" - první rozsah hodnot - čas stroje: A2: A14.
3. Argument "Pole 2" - druhý rozsah hodnot - náklady na opravy: B2:B14. Klepněte na tlačítko OK.

Chcete-li určit typ připojení, musíte se podívat na absolutní číslo koeficientu (každý obor činnosti má svou vlastní stupnici).

Pro korelační analýzu několika parametrů (více než 2) je výhodnější použít "Data Analysis" (doplněk "Analysis Package"). V seznamu musíte vybrat korelaci a určit pole. Všechno.

Výsledné koeficienty se zobrazí v korelační matici. Jako tento:

Korelační-regresní analýza

V praxi se tyto dvě techniky často používají společně.

Příklad:

Nyní jsou viditelná data regresní analýzy.