Vytvoření tabulky párového regresního modelu ke stažení excelové tabulky. Konrad Carlberg. Regresní analýza v aplikaci Microsoft Excel

Regresní analýza je jednou z nejpopulárnějších metod statistického výzkumu. Lze jím určit míru vlivu nezávislých proměnných na závisle proměnnou. Funkce Microsoft Excel má nástroje navržené k provádění tohoto typu analýzy. Pojďme se podívat, jaké to jsou a jak je používat.

Abyste však mohli používat funkci, která vám umožňuje provádět regresní analýzu, musíte nejprve aktivovat analytický balíček. Teprve poté se na pásu karet Excelu objeví nástroje potřebné pro tento postup.

Nyní, když přejdeme na kartu "Data", na pásu karet v panelu nástrojů "Analýza" uvidíme nové tlačítko - "Analýza dat".

Typy regresní analýzy

Existuje několik typů regresí:

parabolický;
Napájení;
logaritmický;
exponenciální;
demonstrace;
hyperbolický;
lineární regrese.

O implementaci posledního typu regresní analýzy v Excelu si povíme podrobněji později.

Lineární regrese v Excelu

Níže je jako příklad uvedena tabulka, která ukazuje průměrnou denní teplotu vzduchu na ulici a počet zákazníků prodejny pro odpovídající pracovní den. Pojďme pomocí regresní analýzy zjistit, jak přesně mohou povětrnostní podmínky v podobě teploty vzduchu ovlivnit návštěvnost maloobchodní provozovny.

Obecná rovnice lineární regrese vypadá takto: Y = a0 + a1x1 + ... + axk. V tomto vzorci Y znamená proměnnou, jejíž vliv se snažíme studovat. V našem případě se jedná o počet kupujících. Význam X jsou různé faktory, které ovlivňují proměnnou. Možnosti A jsou regresní koeficienty. To znamená, že určují význam konkrétního faktoru. Index k označuje celkový počet těchto stejných faktorů.

Analýza výsledků analýzy

Výsledky regresní analýzy se zobrazí ve formě tabulky na místě určeném v nastavení.

Jedním z hlavních ukazatelů je R-čtverec. Označuje kvalitu modelu. V našem případě je tento koeficient 0,705 nebo asi 70,5 %. To je přijatelná úroveň kvality. Vztah menší než 0,5 je špatný.

Další důležitý ukazatel se nachází v buňce na průsečíku čáry "Křižovatka Y" a sloupec "koeficienty". Zde je uvedeno, jakou hodnotu bude mít Y, a v našem případě je to počet kupujících, přičemž všechny ostatní faktory jsou rovné nule. V této tabulce je tato hodnota 58,04.

Hodnota v průsečíku grafu "Proměnná X1" A "koeficienty" ukazuje úroveň závislosti Y na X. V našem případě se jedná o úroveň závislosti počtu zákazníků prodejny na teplotě. Koeficient 1,31 je považován za poměrně vysoký ukazatel vlivu.

Jak vidíte, je poměrně snadné vytvořit tabulku regresní analýzy pomocí aplikace Microsoft Excel. S daty získanými na výstupu však může pracovat pouze vyškolený člověk a pochopit jejich podstatu.

V předchozích poznámkách se pozornost často soustředila na jedinou číselnou proměnnou, jako jsou výnosy podílových fondů, doba načítání webové stránky nebo spotřeba nealkoholických nápojů. V této a následujících poznámkách zvážíme metody předpovídání hodnot číselné proměnné v závislosti na hodnotách jedné nebo více dalších číselných proměnných.

Materiál bude ilustrován přesným příkladem. Prognóza objemu prodeje v obchodě s oblečením. Síť diskontních obchodů s oblečením Sunflowers se již 25 let neustále rozšiřuje. Společnost však v současné době nemá systematický přístup k výběru nových provozoven. Místo, kde společnost hodlá otevřít novou prodejnu, se určuje na základě subjektivních úvah. Kritériem výběru jsou výhodné podmínky pronájmu nebo představa manažera o ideálním umístění prodejny. Představte si, že jste vedoucím oddělení speciálních projektů a plánování. Dostali jste za úkol vypracovat strategický plán pro otevírání nových prodejen. Tento plán by měl obsahovat předpověď ročních tržeb v nově otevřených prodejnách. Domníváte se, že prodejní prostor přímo souvisí s příjmy a chcete tuto skutečnost zohlednit ve svém rozhodovacím procesu. Jak vytvoříte statistický model, který předpovídá roční tržby na základě velikosti nové prodejny?

Regresní analýza se obvykle používá k predikci hodnot proměnné. Jeho cílem je vyvinout statistický model, který předpovídá hodnoty závislé proměnné nebo odpovědi z hodnot alespoň jedné nezávislé nebo vysvětlující proměnné. V této poznámce budeme zvažovat jednoduchou lineární regresi - statistickou metodu, která umožňuje předpovídat hodnoty závislé proměnné Y hodnotami nezávisle proměnné X. Následující poznámky budou popisovat vícenásobný regresní model určený k predikci hodnot nezávislé proměnné Y hodnotami několika závislých proměnných ( Xi, X2, …, Xk).

Stáhněte si poznámku ve formátu nebo formátu, příklady ve formátu

Typy regresních modelů

Kde ρ 1 je autokorelační koeficient; Li ρ 1 = 0 (žádná autokorelace), D≈ 2; Li ρ 1 ≈ 1 (pozitivní autokorelace), D= 0; Li ρ 1 = -1 (negativní autokorelace), D ≈ 4.

V praxi je použití Durbin-Watsonova kritéria založeno na srovnání hodnoty D s kritickými teoretickými hodnotami dl A d U pro daný počet pozorování n, počet nezávislých proměnných modelu k(pro jednoduchou lineární regresi k= 1) a hladina významnosti α. Li D< d L , hypotéza nezávislosti náhodných odchylek je zamítnuta (proto existuje pozitivní autokorelace); Li D > dU, hypotéza není zamítnuta (tj. neexistuje autokorelace); Li dl< D < d U není dost důvodů k rozhodnutí. Když vypočtená hodnota D překročí 2, tedy dl A d U není to samotný koeficient, který se porovnává D a výraz (4 – D).

Pro výpočet Durbin-Watsonovy statistiky v Excelu přejdeme na spodní tabulku na Obr. 14 Výběr zůstatku. Čitatel ve výrazu (10) se vypočítá pomocí funkce = SUMMQDIFF(pole1, pole2) a jmenovatel = SUMMQ(pole) (obr. 16).

Rýže. 16. Vzorce pro výpočet Durbin-Watsonovy statistiky

V našem příkladu D= 0,883. Hlavní otázka zní: jakou hodnotu Durbin-Watsonovy statistiky bychom měli považovat za dostatečně malou na to, abychom dospěli k závěru, že existuje pozitivní autokorelace? Je nutné korelovat hodnotu D s kritickými hodnotami ( dl A d U) v závislosti na počtu pozorování n a hladina významnosti α (obr. 17).

Rýže. 17. Kritické hodnoty statistiky Durbin-Watson (fragment tabulky)

V problému objemu prodeje v obchodě dodávajícím zboží až domů tedy existuje jedna nezávislá proměnná ( k= 1), 15 pozorování ( n= 15) a hladina významnosti α = 0,05. Proto, dl= 1,08 a dU= 1,36. Protože D = 0,883 < dl= 1,08, mezi rezidui je pozitivní autokorelace, nelze použít metodu nejmenších čtverců.

Testování hypotéz o sklonu a korelačním koeficientu

Výše uvedená regrese byla použita pouze pro prognózování. Stanovit regresní koeficienty a predikovat hodnotu proměnné Y pro danou proměnnou hodnotu X byla použita metoda nejmenších čtverců. Kromě toho jsme vzali v úvahu směrodatnou chybu odhadu a koeficient smíšené korelace. Pokud reziduální analýza potvrdí, že podmínky použitelnosti metody nejmenších čtverců nejsou porušeny a jednoduchý lineární regresní model je na základě dat vzorku adekvátní, lze tvrdit, že mezi proměnnými v populaci existuje lineární vztah.

aplikacet -kritéria pro sklon. Kontrolou, zda je sklon populace β 1 roven nule, lze určit, zda existuje statisticky významný vztah mezi proměnnými X A Y. Pokud je tato hypotéza zamítnuta, lze tvrdit, že mezi proměnnými X A Y existuje lineární vztah. Nulová a alternativní hypotéza jsou formulovány následovně: H 0: β 1 = 0 (žádný lineární vztah), H1: β 1 ≠ 0 (existuje lineární vztah). A-převorství t-statistika se rovná rozdílu mezi sklonem vzorku a hypotetickým sklonem populace, děleným standardní chybou odhadu sklonu:

(11) t = (b 1 – β 1 ) / Sb 1

Kde b 1 je sklon přímé regrese na základě údajů ze vzorku, β1 je hypotetický sklon přímé obecné populace, a statistiky testů t Má to t- distribuce s n-2 stupně svobody.

Zkontrolujme, zda existuje statisticky významný vztah mezi velikostí prodejny a ročním obratem při α = 0,05. t-kritéria se při použití zobrazí spolu s dalšími parametry Balíček analýzy(volba Regrese). Úplné výsledky analytického balíčku jsou uvedeny na Obr. 4, fragment související s t-statistikou - na obr. 18.

Rýže. 18. Výsledky aplikace t

Protože počet prodejen n= 14 (viz obr. 3), kritická hodnota t-statistiku na hladině významnosti α = 0,05 lze zjistit vzorcem: t L=STUDENT.INV(0,025;12) = -2,1788, kde 0,025 je polovina hladiny významnosti a 12 = n – 2; t U\u003d STUDENT.INV (0,975; 12) \u003d +2,1788.

Protože t-statistika = 10,64 > t U= 2,1788 (obr. 19), nulová hypotéza H 0 je odmítnut. Na druhé straně, R-hodnota pro X\u003d 10,6411, vypočtená podle vzorce \u003d 1-STUDENT.DIST (D3, 12, TRUE), je přibližně rovna nule, takže hypotéza H 0 je znovu odmítnut. Skutečnost, že R-hodnota je téměř nulová, což znamená, že pokud by neexistoval skutečný lineární vztah mezi velikostí prodejny a ročními tržbami, bylo by téměř nemožné ji najít pomocí lineární regrese. Proto existuje statisticky významný lineární vztah mezi průměrným ročním prodejem prodejny a velikostí prodejny.

Rýže. 19. Testování hypotézy o sklonu obecné populace na hladině významnosti 0,05 a 12 stupňů volnosti

aplikaceF -kritéria pro sklon. Alternativním přístupem k testování hypotéz o sklonu jednoduché lineární regrese je použití F-kritéria. Odvolej to F-kritérium se používá k testování vztahu mezi dvěma rozptyly (viz podrobnosti). Při testování hypotézy sklonu je mírou náhodných chyb rozptyl chyb (součet čtverečních chyb dělený počtem stupňů volnosti), takže F-test používá poměr rozptylu vysvětleného regresí (tj SSR děleno počtem nezávislých proměnných k), na odchylku chyby ( MSE=SYX 2 ).

A-převorství F-statistika se rovná středním čtvercovým odchylkám v důsledku regrese (MSR) děleným rozptylem chyby (MSE): F = MSR/ MSE, Kde MSR=SSR / k, MSE =SSE/(n– k – 1), k je počet nezávislých proměnných v regresním modelu. Testovací statistiky F Má to F- distribuce s k A n– k – 1 stupně svobody.

Pro danou hladinu významnosti α je rozhodovací pravidlo formulováno takto: jestliže F > FU, nulová hypotéza je zamítnuta; jinak se neodmítá. Výsledky, prezentované ve formě souhrnné tabulky analýzy rozptylu, jsou uvedeny na Obr. 20.

Rýže. 20. Tabulka analýzy rozptylu pro testování hypotézy statistické významnosti regresního koeficientu

Podobně t-kritérium F-kritéria se při použití zobrazí v tabulce Balíček analýzy(volba Regrese). Kompletní výsledky práce Balíček analýzy znázorněno na Obr. 4, fragment související s F-statistika - na obr. 21.

Rýže. 21. Výsledky aplikace F- Kritéria získaná pomocí Excel Analysis ToolPack

F-statistika je 113,23 a R-hodnota blízká nule (buňka VýznamF). Je-li hladina významnosti α 0,05, určete kritickou hodnotu F-ze vzorce lze získat rozdělení s jedním a 12 stupni volnosti F U\u003d F. OBR (1-0,05; 1; 12) \u003d 4,7472 (obr. 22). Protože F = 113,23 > F U= 4,7472 a R-hodnota blízká 0< 0,05, нулевая гипотеза H 0 odchyluje, tzn. Velikost obchodu úzce souvisí s jeho ročním objemem prodeje.

Rýže. 22. Testování hypotézy o sklonu obecné populace na hladině významnosti 0,05, s jedním a 12 stupni volnosti

Interval spolehlivosti obsahující sklon β 1 . Chcete-li otestovat hypotézu existence lineárního vztahu mezi proměnnými, můžete sestavit interval spolehlivosti obsahující sklon β 1 a ujistit se, že hypotetická hodnota β 1 = 0 patří do tohoto intervalu. Střed intervalu spolehlivosti obsahující sklon β 1 je sklon vzorku b 1 a jeho hranicemi jsou množství b 1 ±t n –2 Sb 1

Jak je znázorněno na Obr. 18, b 1 = +1,670, n = 14, Sb 1 = 0,157. t 12 \u003d STUDENT.OBR (0,975; 12) \u003d 2,1788. Proto, b 1 ±t n –2 Sb 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 nebo + 1,328 ≤ p1 ≤ +2,012. Sklon populace tedy s pravděpodobností 0,95 leží v rozmezí od +1,328 do +2,012 (tj. od 1 328 000 USD do 2 012 000 USD). Protože tyto hodnoty jsou větší než nula, existuje statisticky významný lineární vztah mezi ročním prodejem a plochou prodejny. Pokud by interval spolehlivosti obsahoval nulu, nebyl by mezi proměnnými žádný vztah. Kromě toho interval spolehlivosti znamená, že každých 1 000 m2. stop má za následek zvýšení průměrného prodeje o 1 328 000 USD na 2 012 000 USD.

Používánít -kritéria pro korelační koeficient. byl zaveden korelační koeficient r, což je míra vztahu mezi dvěma číselnými proměnnými. Lze jej použít ke zjištění, zda existuje statisticky významný vztah mezi dvěma proměnnými. Označme korelační koeficient mezi populacemi obou proměnných symbolem ρ. Nulová a alternativní hypotéza jsou formulovány takto: H 0: ρ = 0 (žádná korelace), H 1: ρ ≠ 0 (existuje korelace). Kontrola existence korelace:

Kde r = + , Pokud b 1 > 0, r = – , Pokud b 1 < 0. Тестовая статистика t Má to t- distribuce s n-2 stupně svobody.

V problému obchodního řetězce Slunečnice r2= 0,904 a b 1- +1,670 (viz obr. 4). Protože b 1> 0, korelační koeficient mezi ročním obratem a velikostí prodejny je r= +√0,904 = +0,951. Otestujme pomocí nulové hypotézy, že mezi těmito proměnnými neexistuje žádná korelace t- statistika:

Na hladině významnosti α = 0,05 by měla být nulová hypotéza zamítnuta, protože t= 10,64 > 2,1788. Lze tedy tvrdit, že mezi ročními tržbami a velikostí prodejny existuje statisticky významný vztah.

Při diskuzi o závěrech o populačních sklonech jsou intervaly spolehlivosti a kritéria pro testování hypotéz vzájemně zaměnitelné nástroje. Výpočet intervalu spolehlivosti obsahující korelační koeficient se však ukazuje jako obtížnější, protože forma výběrového rozdělení statistiky r závisí na skutečném korelačním koeficientu.

Odhad matematického očekávání a predikce jednotlivých hodnot

Tato část pojednává o metodách odhadu očekávané odezvy Y a predikce jednotlivých hodnot Y pro dané hodnoty proměnné X.

Konstrukce intervalu spolehlivosti. V příkladu 2 (viz výše část Metoda nejmenších čtverců) regresní rovnice umožnila předpovědět hodnotu proměnné Y X. Při problému výběru místa pro maloobchodní prodejnu je průměrný roční prodej v obchodě o rozloze 4000 m2. stop se rovnal 7,644 milionům dolarů. Tento odhad matematického očekávání obecné populace je však bodem. pro odhad matematického očekávání obecné populace byl navržen koncept intervalu spolehlivosti. Podobně lze představit koncept interval spolehlivosti pro matematické očekávání odpovědi pro danou hodnotu proměnné X:

Kde , = b 0 + b 1 X i– proměnná predikované hodnoty Y na X = X i, S YX je střední kvadratická chyba, n je velikost vzorku, Xi- daná hodnota proměnné X, µ Y|X = Xi– matematické očekávání proměnné Y na X = Х i,SSX=

Analýza vzorce (13) ukazuje, že šířka intervalu spolehlivosti závisí na několika faktorech. Na dané hladině významnosti vede zvýšení amplitudy fluktuací kolem regresní přímky, měřené pomocí střední kvadratické chyby, ke zvětšení šířky intervalu. Na druhou stranu je podle očekávání nárůst velikosti vzorku doprovázen zúžením intervalu. Šířka intervalu se navíc mění v závislosti na hodnotách Xi. Pokud je hodnota proměnné Y předpovězené pro množství X, blízko průměrné hodnoty , interval spolehlivosti se ukazuje být užší než při předpovídání odezvy pro hodnoty daleko od průměru.

Řekněme, že při výběru místa pro prodejnu chceme vybudovat 95% interval spolehlivosti pro průměrné roční tržby ve všech prodejnách o rozloze 4000 metrů čtverečních. chodidla:

Proto je průměrný roční objem prodeje ve všech prodejnách o rozloze 4000 metrů čtverečních. stop, s 95% pravděpodobností leží v rozmezí od 6,971 do 8,317 milionů dolarů.

Vypočítejte interval spolehlivosti pro předpokládanou hodnotu. Kromě intervalu spolehlivosti pro matematické očekávání odezvy pro danou hodnotu proměnné X, je často nutné znát interval spolehlivosti pro predikovanou hodnotu. Přestože vzorec pro výpočet takového intervalu spolehlivosti je velmi podobný vzorci (13), tento interval obsahuje predikovanou hodnotu a nikoli odhad parametru. Interval pro předpokládanou odpověď YX = Xi pro konkrétní hodnotu proměnné Xi se určuje podle vzorce:

Předpokládejme, že při výběru místa pro maloobchodní prodejnu chceme vybudovat 95% interval spolehlivosti pro předpokládaný roční objem prodeje v prodejně o rozloze 4000 metrů čtverečních. chodidla:

Proto předpokládaný roční objem prodeje na 4 000 m2. stop, s 95% pravděpodobností leží v rozmezí od 5,433 do 9,854 milionů dolarů Jak můžete vidět, interval spolehlivosti pro předpokládanou hodnotu odezvy je mnohem širší než interval spolehlivosti pro její matematické očekávání. Je to proto, že variabilita v predikci jednotlivých hodnot je mnohem větší než v odhadu očekávané hodnoty.

Úskalí a etické problémy spojené s používáním regrese

Potíže spojené s regresní analýzou:

Ignorování podmínek použitelnosti metody nejmenších čtverců.
Chybný odhad podmínek použitelnosti metody nejmenších čtverců.
Špatný výběr alternativních metod v rozporu s podmínkami použitelnosti metody nejmenších čtverců.
Aplikace regresní analýzy bez hlubší znalosti předmětu studia.
Extrapolace regrese za rozsah vysvětlující proměnné.
Záměna statistických a kauzálních vztahů.

Rozšířené používání tabulek a statistického softwaru odstranilo výpočetní problémy, které bránily použití regresní analýzy. To však vedlo k tomu, že regresní analýzu začali využívat uživatelé, kteří nemají dostatečnou kvalifikaci a znalosti. Jak se uživatelé dozvědí o alternativních metodách, když řada z nich nemá vůbec ponětí o podmínkách použitelnosti metody nejmenších čtverců a neví, jak zkontrolovat jejich implementaci?

Výzkumník by se neměl nechat unést broušením čísel – výpočtem posunu, sklonu a smíšeného korelačního koeficientu. Potřebuje hlubší znalosti. Ukažme si to na klasickém příkladu převzatém z učebnic. Anscombe ukázal, že všechny čtyři datové sady zobrazené na Obr. 23 mají stejné regresní parametry (obr. 24).

Rýže. 23. Čtyři umělé datové soubory

Rýže. 24. Regresní analýza čtyř umělých datových souborů; hotovo Balíček analýzy(kliknutím na obrázek se obrázek zvětší)

Z pohledu regresní analýzy jsou tedy všechny tyto datové soubory zcela totožné. Pokud by tam analýza skončila, přišli bychom o spoustu užitečných informací. To dokazují bodové grafy (obr. 25) a reziduální grafy (obr. 26) vytvořené pro tyto soubory dat.

Rýže. 25. Bodové grafy pro čtyři datové sady

Bodové grafy a grafy reziduí ukazují, že tato data se od sebe liší. Jediná množina rozložená podél přímky je množina A. Graf reziduí vypočítaný ze množiny A nemá žádný vzor. Totéž nelze říci o množinách B, C a D. Bodový graf vynesený pro množinu B ukazuje výrazný kvadratický vzor. Tento závěr potvrzuje graf reziduí, který má parabolický tvar. Bodový graf a graf zbytků ukazují, že datová sada B obsahuje odlehlou hodnotu. V této situaci je nutné vyloučit ze souboru dat odlehlou hodnotu a analýzu zopakovat. Technika zjišťování a odstraňování odlehlých hodnot z pozorování se nazývá analýza vlivu. Po vyloučení odlehlé hodnoty může být výsledek přehodnocení modelu zcela odlišný. Bodový graf vynesený ze souboru dat D ilustruje neobvyklou situaci, ve které je empirický model vysoce závislý na jediné odpovědi ( X 8 = 19, Y 8 = 12,5). Takové regresní modely je třeba počítat obzvláště pečlivě. Bodové a reziduální grafy jsou tedy základním nástrojem regresní analýzy a měly by být její nedílnou součástí. Bez nich není regresní analýza důvěryhodná.

Rýže. 26. Grafy reziduí pro čtyři soubory dat

Jak se vyhnout nástrahám regresní analýzy:

Analýza možného vztahu mezi proměnnými X A Y vždy začněte bodovým grafem.
Před interpretací výsledků regresní analýzy zkontrolujte podmínky její použitelnosti.
Vyneste rezidua versus nezávislá proměnná. To umožní určit, jak empirický model odpovídá výsledkům pozorování, a odhalit porušení stálosti rozptylu.
K otestování předpokladu normální distribuce chyb použijte histogramy, grafy stonků a listů, krabicové grafy a grafy normálního rozdělení.
Pokud nejsou splněny podmínky použitelnosti metody nejmenších čtverců, použijte alternativní metody (například kvadratické nebo vícenásobné regresní modely).
Pokud jsou splněny podmínky použitelnosti metody nejmenších čtverců, je nutné otestovat hypotézu o statistické významnosti regresních koeficientů a sestrojit intervaly spolehlivosti obsahující matematické očekávání a predikovanou hodnotu odezvy.
Vyhněte se predikci hodnot závislé proměnné mimo rozsah nezávislé proměnné.
Mějte na paměti, že statistické závislosti nejsou vždy kauzální. Pamatujte, že korelace mezi proměnnými neznamená, že mezi nimi existuje kauzální vztah.

Souhrn. Jak ukazuje blokové schéma (obr. 27), poznámka popisuje jednoduchý lineární regresní model, podmínky jeho použitelnosti a způsoby testování těchto podmínek. Považováno t-kritérium pro testování statistické významnosti sklonu regrese. K predikci hodnot závislé proměnné byl použit regresní model. Příklad je považován za související s výběrem místa pro maloobchod, ve kterém je studována závislost ročního objemu prodeje na ploše prodejny. Získané informace umožňují přesněji vybrat místo pro prodejnu a předvídat její roční tržby. V následujících poznámkách bude diskuse o regresní analýze pokračovat, stejně jako o vícenásobných regresních modelech.

Rýže. 27. Blokové schéma noty

Jsou použity materiály z knihy Levin et al Statistika pro manažery. - M.: Williams, 2004. - str. 792–872

Pokud je závislá proměnná kategorická, měla by se použít logistická regrese.

V vynikat existuje ještě rychlejší a pohodlnější způsob, jak vykreslit lineární regresi (a dokonce i hlavní typy nelineárních regresí, viz níže). To lze provést takto:

1) vyberte sloupce s daty X A Y(musí být v tomto pořadí!);

2) zavolat Průvodce grafem a vyberte ve skupině Typ – tečkovaný a ihned stiskněte Připraveno;

3) bez zrušení výběru diagramu vyberte položku hlavní nabídky, která se zobrazí Diagram, ve kterém byste měli vybrat položku Přidat trendovou linii;

4) v dialogovém okně, které se objeví trendová linie tab Typ Vybrat Lineární;

5) tab Možnosti spínač lze aktivovat Zobrazit rovnici na grafu, což vám umožní vidět rovnici lineární regrese (4.4), ve které se budou počítat koeficienty (4.5).

6) Na stejné záložce můžete aktivovat přepínač Vložte do diagramu hodnotu spolehlivosti aproximace (R^2). Tato hodnota je druhou mocninou korelačního koeficientu (4.3) a ukazuje, jak dobře vypočítaná rovnice popisuje experimentální závislost. Li R 2 se blíží jednotě, pak teoretická regresní rovnice dobře popisuje experimentální závislost (teorie dobře souhlasí s experimentem), a pokud R 2 se blíží nule, pak tato rovnice není vhodná pro popis experimentální závislosti (teorie nesouhlasí s experimentem).

V důsledku provedení popsaných akcí získáte diagram s regresním grafem a jeho rovnicí.

§4.3. Hlavní typy nelineární regrese

Parabolická a polynomiální regrese.

Parabolický závislost hodnoty Y z hodnoty X závislost vyjádřená kvadratickou funkcí (parabola 2. řádu) se nazývá:

Tato rovnice se nazývá parabolická regrese Y na X. Možnosti A, b, S volal parabolické regresní koeficienty. Výpočet parabolických regresních koeficientů je vždy těžkopádný, proto se doporučuje pro výpočty použít počítač.

Rovnice (4.8) parabolické regrese je speciálním případem obecnější regrese zvané polynom. polynom závislost hodnoty Y z hodnoty X se nazývá závislost vyjádřená polynomem n- pořadí:

kde jsou čísla a i (i=0,1,…, n) jsou nazývány polynomiální regresní koeficienty.

Regrese moci.

Napájení závislost hodnoty Y z hodnoty X se nazývá závislost tvaru:

Tato rovnice se nazývá mocninná regresní rovnice Y na X. Možnosti A A b volal mocninné regresní koeficienty.

ln=ln A+b ln X. (4.11)

Tato rovnice popisuje přímku v rovině s logaritmickými souřadnicovými osami ln X a ln. Proto je kritériem použitelnosti mocninné regrese požadavek, aby body logaritmů empirických dat ln x i a ln i byly nejblíže přímce (4.11).

exponenciální regrese.

příkladný(nebo exponenciální) závislost množství Y z hodnoty X se nazývá závislost tvaru:

(nebo ). (4.12)

Tato rovnice se nazývá exponenciální rovnice(nebo exponenciální) regrese Y na X. Možnosti A(nebo k) A b volal exponenciální(nebo exponenciální) regrese.

Pokud vezmeme logaritmus obou stran mocninné regresní rovnice, dostaneme rovnici

ln = X ln A+ln b(nebo ln = k x+ln b). (4.13)

Tato rovnice popisuje lineární závislost logaritmu jedné veličiny ln na jiné veličině X. Proto je kritériem použitelnosti mocninné regrese požadavek, aby empirické datové body stejné velikosti x i a logaritmy jiné hodnoty ln i byly nejblíže přímce (4.13).

logaritmická regrese.

Logaritmické závislost hodnoty Y z hodnoty X se nazývá závislost tvaru:

=A+b ln X. (4.14)

Tato rovnice se nazývá logaritmická regrese Y na X. Možnosti A A b volal logaritmické regresní koeficienty.

hyperbolická regrese.

Hyperbolický závislost hodnoty Y z hodnoty X se nazývá závislost tvaru:

Tato rovnice se nazývá hyperbolická regresní rovnice Y na X. Možnosti A A b volal hyperbolické regresní koeficienty a jsou určeny metodou nejmenších čtverců. Použití této metody vede ke vzorcům:

Ve vzorcích (4.16-4.17) se sčítání provádí přes index i od jednoho po počet pozorování n.

Bohužel v vynikat neexistuje žádná funkce, která by vypočítávala koeficienty hyperbolické regrese. V případech, kdy není s jistotou známo, že naměřené hodnoty souvisí inverzní úměrností, se doporučuje hledat rovnici mocninné regrese namísto rovnice hyperbolické regrese, takže v vynikat existuje postup, jak to najít. Pokud se předpokládá hyperbolická závislost mezi naměřenými hodnotami, pak bude nutné její regresní koeficienty vypočítat pomocí pomocných výpočtových tabulek a součtových operací pomocí vzorců (4.16-4.17).

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.

K práci vyžaduje doplněk Balíček analýzy, který musí být povolen v položce nabídky Služba\Doplňky

Chcete-li v aplikaci Excel 2007 povolit Analysis Pack, klikněte na Přejít na blok Možnosti aplikace Excel kliknutím na tlačítko v levém horním rohu a poté na „ Možnosti aplikace Excel» ve spodní části okna:

Chcete-li sestavit regresní model, vyberte položku Služba\Analýza dat\Regrese. (V Excelu 2007 je tento režim v Data/analýza dat/regrese). Zobrazí se dialogové okno, které je třeba vyplnit:

1) Interval vstupu Y¾ obsahuje odkaz na buňky, které obsahují hodnoty výsledného atributu y. Hodnoty musí být ve sloupci;

2) Interval vstupu X¾ obsahuje odkaz na buňky, které obsahují hodnoty faktorů. Hodnoty musí být ve sloupcích;

3) Podepsat Tagy nastavit, pokud první buňky obsahují vysvětlující text (označení dat);

4) Úroveň spolehlivosti¾ je úroveň spolehlivosti, která je standardně považována za 95 %. Pokud vám tato hodnota nevyhovuje, musíte tuto funkci povolit a zadat požadovanou hodnotu;

5) Podepsat Nulová konstanta je zahrnut, pokud je nutné sestavit rovnici, ve které je volná proměnná ;

6) Možnosti výstupu určit, kam mají být výsledky umístěny. Výchozí režim sestavení Nový pracovní list;

7) Blokovat Zůstává umožňuje zahrnout výstup reziduí a konstrukci jejich grafů.

V důsledku toho se zobrazí informace, které obsahují všechny potřebné informace a jsou seskupeny do tří bloků: Regresní statistika, Analýza rozptylu, Výběr zůstatku. Zvažme je podrobněji.

1. Regresní statistika:

násobek R je definován vzorcem ( Pearsonův korelační koeficient);

R (koeficient determinace);

Normalizované R-čtverec se vypočítá podle vzorce (používá se pro vícenásobnou regresi);

standardní chyba S vypočítané podle vzorce ;

Pozorování ¾ je množství dat n.

2. Analýza rozptylu, řádek Regrese:

Parametr df rovná se m(počet sad faktorů X);

Parametr SS je určeno vzorcem ;

Parametr SLEČNA je určeno vzorcem ;

Statistika F je určeno vzorcem ;

Význam F. Pokud výsledné číslo přesáhne , pak je hypotéza přijata (není lineární vztah), v opačném případě je hypotéza přijata (existuje lineární vztah).

3. Analýza rozptylu, řádek Zbytek:

Parametr df rovná se ;

Parametr SS je určeno vzorcem ;

Parametr SLEČNA je určeno vzorcem .

4. Analýza rozptylu, řádek Celkový obsahuje součet prvních dvou sloupců.

5. Analýza rozptylu, řádek Y-křižovatka obsahuje hodnotu koeficientu , směrodatnou chybu a t-statistika.

P-hodnota ¾ je hodnota hladin významnosti odpovídající vypočtené t- statistici. Určeno STUDISTEM ( t-statistika; ). Li P-hodnota překročí , pak je odpovídající proměnná statisticky nevýznamná a lze ji z modelu vyloučit.

spodních 95 % A Nejlepších 95 %¾ jsou dolní a horní hranice 95procentního intervalu spolehlivosti pro koeficienty teoretické lineární regresní rovnice. Pokud byla v bloku zadávání dat standardně ponechána hodnota pravděpodobnosti spolehlivosti, pak poslední dva sloupce budou duplikovat předchozí. Pokud uživatel zadal vlastní hodnotu spolehlivosti, pak poslední dva sloupce obsahují hodnoty dolní a horní hranice pro zadanou úroveň spolehlivosti.

6. Analýza rozptylu, řádky obsahují hodnoty koeficientů, směrodatné chyby, t-statistik, P-hodnoty a intervaly spolehlivosti pro odpovídající .

7. Blokovat Výběr zůstatku obsahuje předpokládané hodnoty y(v našem zápisu je to ) a zbytky .

Připojení analytického balíčku

Přejděte na kartu "Soubor".
Přejděte do sekce "Nastavení".
Otevře se okno Možnosti aplikace Excel. Přejděte do podsekce "Doplňky".
V úplně spodní části okna, které se otevře, přeuspořádáme přepínač v bloku "Správa" do polohy "Doplňky Excelu", pokud je v jiné poloze. Klikněte na tlačítko "Go".
Otevře se okno doplňků aplikace Excel. Zaškrtněte políčko vedle položky „Analytický balíček“. Klikněte na tlačítko "OK".

Nyní, když přejdeme na kartu "Data", na pásu karet v bloku nástrojů "Analýza" uvidíme nové tlačítko - "Analýza dat".

Typy regresní analýzy

Existuje několik typů regresí:

parabolický;
Napájení;
logaritmický;
exponenciální;
demonstrace;
hyperbolický;
lineární regrese.

O implementaci posledního typu regresní analýzy v Excelu si povíme podrobněji později.

Lineární regrese v Excelu

Obecná rovnice lineární regrese vypadá takto: Y = a0 + a1x1 + ... + axk. V tomto vzorci znamená Y proměnnou, na kterou se snažíme zkoumat vliv faktorů. V našem případě se jedná o počet kupujících. Hodnota x jsou různé faktory, které ovlivňují proměnnou. Parametry a jsou regresní koeficienty. To znamená, že určují význam konkrétního faktoru. Index k označuje celkový počet těchto stejných faktorů.

Analýza výsledků analýzy

Výsledky regresní analýzy se zobrazí ve formě tabulky na místě určeném v nastavení.

Jedním z hlavních ukazatelů je R-kvadrát. Označuje kvalitu modelu. V našem případě je tento koeficient 0,705 nebo asi 70,5 %. To je přijatelná úroveň kvality. Vztah menší než 0,5 je špatný.

Další důležitý ukazatel se nachází v buňce na průsečíku čáry "Y-průsečík" a sloupce "Koeficienty". Zde je uvedeno, jakou hodnotu bude mít Y, a v našem případě je to počet kupujících, přičemž všechny ostatní faktory jsou rovné nule. V této tabulce je tato hodnota 58,04.

Hodnota na průsečíku sloupce „Proměnná X1“ a „Koeficienty“ ukazuje míru závislosti Y na X. V našem případě se jedná o úroveň závislosti počtu zákazníků prodejny na teplotě. Koeficient 1,31 je považován za poměrně vysoký ukazatel vlivu.

Jsme rádi, že jsme vám mohli pomoci problém vyřešit.

Zeptejte se v komentářích a podrobně popište podstatu problému. Naši odborníci se pokusí odpovědět co nejrychleji.

Pomohl vám tento článek?

Metoda lineární regrese nám umožňuje popsat přímku, která nejlépe odpovídá řadě uspořádaných dvojic (x, y). Rovnice pro přímku, známá jako lineární rovnice, je uvedena níže:

ŷ je očekávaná hodnota y pro danou hodnotu x,

x - nezávislá proměnná,

a - segment na ose y pro přímku,

b je sklon přímky.

Na obrázku níže je tento koncept znázorněn graficky:

Obrázek výše ukazuje přímku popsanou rovnicí ŷ =2+0,5x. Úsek na ose y je průsečík přímky s osou y; v našem případě a = 2. Sklon úsečky b, poměr stoupání úsečky k délce úsečky, má hodnotu 0,5. Kladný sklon znamená, že čára stoupá zleva doprava. Pokud b = 0, čára je vodorovná, což znamená, že mezi závislými a nezávislými proměnnými není žádný vztah. Jinými slovy, změna hodnoty x neovlivní hodnotu y.

ŷ a y se často zaměňují. V grafu je zobrazeno 6 uspořádaných dvojic bodů a přímka podle dané rovnice

Tento obrázek ukazuje bod odpovídající uspořádané dvojici x = 2 a y = 4. Všimněte si, že očekávaná hodnota y podle přímky u X= 2 je ŷ. Můžeme to potvrdit následující rovnicí:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Hodnota y je skutečný bod a hodnota ŷ je očekávaná hodnota y pomocí lineární rovnice pro danou hodnotu x.

Dalším krokem je určení lineární rovnice, která nejlépe odpovídá množině uspořádaných dvojic, o tom jsme hovořili v předchozím článku, kde jsme určovali tvar rovnice metodou nejmenších čtverců.

Použití Excelu k definici lineární regrese

Chcete-li použít nástroj regresní analýzy zabudovaný do Excelu, musíte aktivovat doplněk Balíček analýzy. Najdete ji kliknutím na záložku Soubor –> Možnosti(2007+), v dialogovém okně, které se objeví Možnostivynikat přejděte na kartu Doplňky. V terénu Řízení Vybrat doplňkyvynikat a klikněte Jít. V okně, které se zobrazí, zaškrtněte políčko vedle analytický balíček, klikněte OK.

V záložce Data ve skupině Analýza objeví se nové tlačítko Analýza dat.

Abychom demonstrovali, jak doplněk funguje, použijme data z předchozího článku, kde chlap a dívka sdílejí stůl v koupelně. Zadejte údaje pro náš příklad koupelny do sloupců A a B prázdného listu.

Přejděte na kartu Data, ve skupině Analýza klikněte Analýza dat. V okně, které se objeví Analýza dat vybrat Regrese jak je znázorněno na obrázku a klikněte na OK.

V okně nastavte požadované parametry regrese Regrese, jak je vidět na obrázku:

Klikněte OK. Níže uvedený obrázek ukazuje získané výsledky:

Tyto výsledky jsou v souladu s těmi, které jsme získali nezávislými výpočty v předchozím článku.

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 zavedl do matematiky Francis Galton 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 propuštěných pracovníků na průměrné mzdě v 6 podnicích má regresní model tvar rovnice Y = a0 + a1 × 1 + ... + akxk, kde хi jsou ovlivňující proměnné, ai 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.

Lineární regrese 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 je R-kvadrát tcr, 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.