Paní excelová regrese. Sestavení vícenásobné regresní rovnice v Excelu. Využití možností tabulkového procesoru Excel

Balíček MS Excel umožňuje sestavit rovnici lineární regrese většina odvést práci velmi rychle. Je důležité pochopit, jak interpretovat získané 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 analytický balíček, musíte kliknout na tlačítko Přejít na blokování Možnosti aplikace Excel stisknutím tlačítka vlevo horní roh a poté na tlačítko Možnosti aplikace Excel"ve spodní části okna:

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

1) Interval vstupu Y¾ obsahuje odkaz na buňky, které obsahují hodnoty výsledné charakteristiky y. Hodnoty musí být uspořádány do sloupce;

2) Interval vstupu X¾ obsahuje odkaz na buňky, které obsahují hodnoty faktorů. Hodnoty musí být uspořádány do sloupců;

3) Podepsat Tagy je nastaveno, pokud první buňky obsahují vysvětlující text(podpisy dat);

4) Úroveň spolehlivosti¾ je úroveň spolehlivosti, která je standardně považována za 95 %. Pokud nejste spokojeni s touto hodnotou, musíte povolit tento příznak a zadat požadovanou hodnotu;

5) Podepsat Konstanta-nula 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. Ve výchozím režimu sestavení Nový pracovní list;

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

V důsledku toho informace obsahující vše nezbytné informace a seskupeny do tří bloků: Regresní statistika, Analýza rozptylu, Stažení rovnováhy. Pojďme se na ně podívat blíže.

1. Regresní statistika:

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

R (koeficient determinace);

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

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řekročí , pak je hypotéza přijata (neexistuje 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á ;

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 koeficient, směrodatnou chybu a t-statistika.

P-hodnota ¾ je hodnota hladin významnosti odpovídající vypočtené t- statistici. Určeno funkcí STUDIST( 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 hodnota pravděpodobnosti spolehlivosti ve vstupním bloku dat ponechána na výchozí hodnotě, pak poslední dva sloupce budou duplikovat ty předchozí. Pokud uživatel zadal hodnotu spolehlivosti, poslední dva sloupce obsahují hodnoty dolní a horní meze pro zadanou úroveň spolehlivosti.

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

7. Blokovat Stažení rovnováhy obsahuje předpokládané hodnoty y(v našem zápisu je to ) a zbytky .

Regresní a korelační analýza jsou statistické výzkumné metody. 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 o konkrétních praktické příklady Podívejme se na tyto dvě mezi ekonomy velmi oblíbené analýzy. Uvedeme také příklad získání výsledků při jejich kombinování.

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 vám umožní zvýraznit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí a č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).

Podívejme se na příklad sestavení regresního modelu v Excelu a interpretaci výsledků. Pojďme vzít lineární typ regrese.

Úkol. U 6 podniků průměr měsíčně mzda a počet zaměstnanců, kteří odešli. Je třeba určit závislost počtu odcházejících zaměstnanců na průměrné mzdě.

Lineární regresní model vypadá takto:

Y = a 0 + a 1 x 1 +…+ak x k.

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

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

Excel má vestavěné funkce, které vám pomohou vypočítat parametry lineárního regresního modelu. Ale doplněk „Analysis Package“ to udělá rychleji.

Aktivujeme výkonný analytický nástroj:

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

Nyní udělejme samotnou regresní analýzu.

Nejprve věnujeme pozornost R-squared a koeficientům.

R-squared je koeficient determinace. V našem příkladu – 0,755 nebo 75,5 %. To znamená, že vypočtené parametry modelu vysvětlují 75,5 % vztahu mezi studovanými parametry. Čím vyšší je koeficient determinace, tím kvalitnější 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 hodnota analyzovaného parametru je ovlivněna i dalšími 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 špatný vliv: čím vyšší plat, tím méně lidí skončí. 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 souvislost, pak 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 lze hodnotu jednoho ukazatele použít k predikci možný význam další.

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

Podívejme se, jak používat Excel nástroje najít korelační koeficient.

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

Cíl: Zjistit, 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 – provozní doba stroje: A2:A14.
3. Argument „Pole 2“ – druhý rozsah hodnot – náklady na opravu: 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ýza několik parametrů (více než 2), je výhodnější použít „Data Analysis“ (doplněk „Analysis Package“). Musíte vybrat korelaci ze seznamu a označit pole. Všechno.

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

Korelační a regresní analýza

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

Příklad:

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

V předchozích příspěvcích se analýza často zaměřovala na jedinou číselnou proměnnou, jako jsou výnosy podílových fondů, doba načítání webových stránek nebo spotřeba nealkoholických nápojů. V této a následujících poznámkách se podíváme na 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 průřezový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 se společnost chystá otevřít nový obchod, je stanovena na základě subjektivních úvah. Kritéria výběru jsou ziskové podmínky nájem nebo představu 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í. Byl jste pověřen vývojem strategický plán otevírání nových prodejen. Tento plán by měl obsahovat předpověď ročních tržeb pro nově otevřené prodejny. Domníváte se, že maloobchodní prostory přímo souvisí s příjmy, a chcete to zahrnout do svého rozhodovacího procesu. Jak se rozvíjet statistický model který předpovídá roční tržby na základě velikosti nového obchodu?

Regresní analýza se obvykle používá k predikci hodnot proměnné. Jeho cílem je vyvinout statistický model, který dokáže předpovídat hodnoty závislé proměnné nebo odezvy z hodnot alespoň jedné nezávislé nebo vysvětlující proměnné. V tomto příspěvku se podíváme na jednoduchou lineární regresi - statistická metoda, což umožňuje předpovídat hodnoty závislé proměnné Y nezávisle proměnnými hodnotami X. Následující poznámky budou popisovat vícenásobný regresní model určený k predikci hodnot nezávislé proměnné Y na základě hodnot několika závislých proměnných ( X 1, X 2, …, X k).

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

Typy regresních modelů

Kde ρ 1 – 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 porovnávání hodnoty D s kritickými teoretickými hodnotami d L A d U pro daný počet pozorování n, počet nezávislých modelové proměnné k(pro jednoduchou lineární regresi k= 1) a hladina významnosti α. Li D< d L , hypotéza o 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 d L< D < d U , neexistují dostatečné důvody pro rozhodnutí. Když vypočtená hodnota D přesahuje 2, pak s d L A d U Neporovnává se samotný koeficient D a výraz (4 – D).

Pro výpočet Durbin-Watsonovy statistiky v Excelu se podívejme na spodní tabulku na Obr. 14 Stažení rovnováhy. Čitatel ve výrazu (10) se vypočítá pomocí funkce =SUMMAR(pole1;pole2) a jmenovatel =SUMMAR(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í: jaká hodnota Durbin-Watsonovy statistiky by měla být považována za dostatečně malou, abychom dospěli k závěru, že existuje pozitivní autokorelace? Je nutné korelovat hodnotu D s kritickými hodnotami ( d L 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ží domů tedy existuje jedna nezávislá proměnná ( k= 1), 15 pozorování ( n= 15) a hladina významnosti α = 0,05. Proto, d L= 1,08 a dU= 1,36. Protože D = 0,883 < d L= 1,08, mezi rezidui je pozitivní autokorelace, metoda nejmenší čtverce nelze použít.

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

Výše byla regrese použita pouze pro prognózování. Stanovit regresní koeficienty a predikovat hodnotu proměnné Y pro danou hodnotu proměnné X Byla použita metoda nejmenších čtverců. Kromě toho jsme zkoumali střední kvadratickou chybu odhadu a smíšený korelační koeficient. Pokud analýza reziduí potvrdí, že podmínky použitelnosti metody nejmenších čtverců nejsou porušeny a jednoduchý lineární regresní model je na základě výběrových dat adekvátní, lze tvrdit, že existuje rozdíl mezi proměnnými v populaci lineární závislost.

aplikacet -kritéria pro sklon. Testováním, zda je sklon populace β 1 roven nule, můžete 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 (neexistuje lineární závislost), H1: β 1 ≠ 0 (existuje lineární závislost). A-převorství t-statistika se rovná rozdílu mezi sklonem vzorku a hypotetickou hodnotou sklonu populace, děleno střední kvadraturní chybou odhadu sklonu:

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

Kde b 1 – sklon přímé regrese na vzorových datech, β1 – hypotetický sklon přímé populace, a statistiky testů t Má to t- distribuce s n – 2 stupně svobody.

Ověřte si, zda existuje statisticky významný vztah mezi velikostí prodejny a ročním obratem při α = 0,05. t-při použití se kritérium zobrazí spolu s dalšími parametry Balíček analýzy(volba Regrese). Kompletní 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

Vzhledem k počtu prodejen n= 14 (viz obr. 3), kritická hodnota t-statistiku na hladině významnosti α = 0,05 lze zjistit pomocí vzorce: t L=STUDENT.ARV(0,025,12) = –2,1788, kde 0,025 je polovina hladiny významnosti a 12 = n – 2; tU=STUDENT.OBR(0,975;12) = +2,1788.

Protože t-statistika = 10,64 > tU= 2,1788 (obr. 19), nulová hypotéza H 0 odmítl. Na druhé straně, R- hodnota pro X= 10,6411, vypočteno podle vzorce =1-STUDENT.DIST(D3,12,TRUE), je přibližně rovno nule, takže hypotéza H 0 opět zamítnuta. Skutečnost, že R-hodnota téměř nula znamená, že pokud by neexistoval skutečný lineární vztah mezi velikostí prodejen a ročními tržbami, bylo by prakticky nemožné ji detekovat 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 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. Připomeňme vám to F-test slouží k testování vztahu mezi dvěma rozptyly (podrobněji viz). Při testování hypotézy sklonu mírou náhodné chyby je rozptyl chyby (součet čtverečních chyb dělený počtem stupňů volnosti), tak F-kritérium 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 = S YX 2 ).

A-převorství F-statistika se rovná střední čtverci regrese (MSR) dělené odchylkou chyby (MSE): F = MSR/ MSE, Kde MSR=SSR / k, MSE =SSE/(n– k – 1), k– 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.

Na danou úroveň význam α je rozhodující 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 rozptylové analýzy, jsou uvedeny na Obr. 20.

Rýže. 20. Analýza rozptylové tabulky pro testování hypotézy o statistické významnosti regresního koeficientu

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

Rýže. 21. Výsledky aplikace F-kritéria získaná pomocí Balíčku Excel analýza

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-rozdělení s jedním a 12 stupni volnosti lze získat pomocí vzorce F U=F.OBR(1-0,05;1;12) = 4,7472 (obr. 22). Protože F = 113,23 > F U= 4,7472 a R- hodnota blízká 0< 0,05, нулевая гипотеза H 0 je odmítnut, tzn. Velikost prodejny úzce souvisí s jejími ročními tržbami.

Rýže. 22. Testování hypotézy sklonu 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, že mezi proměnnými existuje lineární vztah, můžete sestrojit interval spolehlivosti obsahující sklon β 1 a ověřit, ž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 ±tn –2 Sb 1

Jak je znázorněno na Obr. 18, b 1 = +1,670, n = 14, Sb 1 = 0,157. t 12 =STUDENT.ARV(0,975;12) = 2,1788. Proto, b 1 ±tn –2 Sb 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 nebo + 1,328 ≤ p1 ≤ +2,012. Existuje tedy pravděpodobnost 0,95, že sklon populace leží mezi +1,328 a +2,012 (tj. 1 328 000 až 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. Interval spolehlivosti navíc znamená, že každé zvýšení plochy prodejny o 1 000 m2. ft. má za následek zvýšení průměrného objemu prodeje mezi 1 328 000 USD a 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. Může být použit k určení, zda existuje statistický rozdíl mezi dvěma proměnnými. smysluplné spojení. 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 o síti prodejen Slunečnice r 2= 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 týkajících se sklonu populace se intervaly spolehlivosti a testy hypotéz používají zaměnitelně. Výpočet intervalu spolehlivosti obsahujícího korelační koeficient se však ukazuje jako obtížnější, protože typ 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 matematického očekávání odezvy Y a predikce jednotlivých hodnot Y pro dané hodnoty proměnné X.

Konstrukce intervalu spolehlivosti. V příkladu 2 (viz část výše Metoda nejmenších čtverců) regresní rovnice nám umožnil předpovědět hodnotu proměnné Y X. V problému výběru místa pro místě prodeje průměrný roční objem prodeje v prodejně o rozloze 4000 m2. stop se rovnal 7,644 milionům dolarů, nicméně tento odhad matematického očekávání obecné populace je bodový. Pro odhad matematického očekávání populace byl navržen koncept intervalu spolehlivosti. Podobně můžeme představit koncept interval spolehlivosti pro matematické očekávání odpovědi na nastavená hodnota variabilní X:

Kde , = b 0 + b 1 X i– predikovaná hodnota je proměnná Y na X = X i, S YX– střední kvadratická chyba, n- velikost vzorku, Xi- za daná hodnota variabilní X, µ Y|X = Xi– matematické očekávání proměnné Y na X = 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, jak by se dalo očekávat, nárůst velikosti vzorku je 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 umístění prodejny chceme zkonstruovat 95% interval spolehlivosti pro průměrné roční tržby všech prodejen, jejichž plocha je 4000 metrů čtverečních. chodidla:

Proto průměrný roční objem prodeje ve všech prodejnách o rozloze 4 000 m2. 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 spíše předpokládanou hodnotu než odhad parametru. Interval pro předpokládanou odpověď YX = Xi pro konkrétní proměnnou hodnotu Xi určeno vzorcem:

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

Proto předpokládaný roční objem prodeje pro prodejnu o rozloze 4000 m2. stop, s 95% pravděpodobností leží v rozmezí od 5,433 do 9,854 milionů dolarů, jak vidíme, 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 matematického očekávání.

Ú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é posouzení podmínek použitelnosti metody nejmenších čtverců.
• Nesprávný výběr alternativních metod při porušení podmínek použitelnosti metody nejmenších čtverců.
• Aplikace regresní analýzy bez hluboké znalosti předmětu zkoumání.
• Extrapolace regrese za rozsah vysvětlující proměnné.
• Záměna statistických a kauzálních vztahů.

Široké použití tabulky A software pro statistické výpočty eliminovaly výpočetní problémy, které bránily použití regresní analýzy. To však vedlo k tomu, že regresní analýzu využívali uživatelé, kteří neměli dostatečnou kvalifikaci a znalosti. Jak mohou uživatelé vědět 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 křupavými čísly – počítáním 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é soubory 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é. Kdyby tam ten rozbor skončil, přišli bychom o hodně užitečné informace. To dokazují bodové grafy (obrázek 25) a zbytkové grafy (obrázek 26) vytvořené pro tyto soubory dat.

Rýže. 25. Bodové grafy pro čtyři soubory dat

Bodové grafy a reziduální grafy naznačují, že se tyto údaje 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. To 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 zbytkový graf, který má parabolický tvar. Bodový graf a graf zbytků ukazují, že soubor dat B obsahuje odlehlou hodnotu. V této situaci je nutné vyloučit ze souboru dat odlehlou hodnotu a analýzu zopakovat. Metoda pro detekci a eliminaci odlehlých hodnot v pozorováních se nazývá analýza vlivu. Po odstranění odlehlé hodnoty může být výsledek přehodnocení modelu zcela odlišný. Bodový graf vynesený z dat ze sady G ilustruje neobvyklou situaci, ve které je empirický model významně závislý na individuální odpovědi ( X 8 = 19, Y 8 = 12,5). Takové regresní modely musí být vypočítány obzvláště pečlivě. Takže rozptylové a zbytkové grafy jsou extrémně potřebný nástroj regresní analýzy a měla 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 úskalí v regresní analýze:

• Analýza možných vztahů mezi proměnnými X A Y vždy začněte kreslením bodového grafu.
• Před interpretací výsledků regresní analýzy si ověřte podmínky její použitelnosti.
• Vyneste rezidua versus nezávislá proměnná. To umožní určit, jak dobře empirický model odpovídá výsledkům pozorování, a odhalit porušení konstanty rozptylu.
• K otestování předpokladu normálního rozdělení chyb použijte histogramy, grafy stonků a listů, krabicové grafy a grafy normálního rozdělení.
• Pokud nejsou splněny podmínky pro použitelnost metody nejmenších čtverců, použijte alternativní metody(například kvadratické nebo vícenásobné regresní modely).
• Pokud jsou splněny podmínky pro použitelnost 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é vztahy nejsou vždy příčinou a následkem. Pamatujte, že korelace mezi proměnnými neznamená, že mezi nimi existuje vztah příčina-následek.

Souhrn. Jak ukazuje blokové schéma (obrázek 27), poznámka popisuje jednoduchý lineární regresní model, podmínky jeho použitelnosti a způsob testování těchto podmínek. Považováno t-kritérium pro testování statistické významnosti regresní směrnice. K predikci hodnot závislé proměnné byl použit regresní model. Jako příklad je uvažován výběr místa pro maloobchod, ve kterém je zkoumána závislost ročního objemu prodeje na ploše prodejny. Získané informace vám umožní přesněji vybrat místo pro prodejnu a předpovědět její roční objem prodeje. Následující poznámky budou pokračovat v diskusi o regresní analýze a také se zaměří na vícenásobné regresní modely.

Rýže. 27. Strukturální schéma poznámky

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

Pokud je závislá proměnná kategorická, je třeba použít logistickou regresi.

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

Ve stole regresní statistiky jsou uvedeny následující hodnoty:

Násobek R – vícenásobný korelační koeficient;

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

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

Standardní chyba– standardní chyba regrese S;

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

Ve stole Analýza rozptylu jsou uvedeny:

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– zbytkový rozptyl.

4. Sloupec F – vypočítaná hodnota F-kritérium vypočtené pomocí vzorce

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

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

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

Pokud význam F < standardní úroveň význam tedy R 2 je statisticky významný.

	Kurzy	Standardní chyba	t-statistiky	P-hodnota	Spodních 95 %	Nejlepších 95 %
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

Tato tabulka ukazuje:

1. Kurzy– hodnoty koeficientů A, b.

2. Standardní chyba–standardní chyby regresní koeficienty S a, Sb.

3. t- statistika – vypočítané 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 = STUDIDIST(t-statistika, df(zbytek)).

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

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

ODBĚR ZBYTKU
Pozorování	Předpokládaný r	Zbytky e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

Ve stole ODBĚR ZBYTKU uvedeno:

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

ve sloupci Předpovězeno y – vypočtené hodnoty závislé proměnné;

ve sloupci Zbytky E – rozdíl mezi pozorovanými a vypočtenými hodnotami závislé proměnné.

Příklad 3.6. Existují údaje (konvenční jednotky) o nákladech na potraviny y a příjem na hlavu X pro devět skupin rodin:

X
y

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

Výsledky regresní analýzy se obvykle zapisují ve tvaru:

kde jsou v závorkách uvedeny směrodatné 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. spojení je přímé a pozitivní. Součinitel b= 0,107 ukazuje, že při zvýšení příjmu na hlavu o 1 konvenční. Jednotky náklady na potraviny se zvyšují o 0,107 konvenční jednotky. Jednotky

Zhodnoťme význam koeficientů výsledného modelu. Význam koeficientů ( a, b) kontroluje t-test:

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

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

proto 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 jejich určitou sadou (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 .

Velikost 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 kontroluje 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 náklady na potraviny a příjmem na hlavu je velmi těsný.

Regresní analýza je jednou z nejpopulárnějších metod statistického výzkumu. Lze jej použít ke stanovení míry vlivu nezávislých proměnných na závisle proměnnou. Ve funkčnosti Microsoft Excel Existují nástroje určené k provádění tohoto typu analýzy. Podívejme se, jaké to jsou a jak je používat.

Ale abyste mohli používat funkci, která vám umožňuje provádět regresní analýzu, musíte nejprve aktivovat balíček analýzy. 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ý;
• usedlý;
• logaritmický;
• exponenciální;
• demonstrativní;
• hyperbolický;
• lineární regrese.

O provádění 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 znázorňující průměrnou denní venkovní teplotu vzduchu 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 maloobchodu.

Obecná rovnice lineární regrese je následující: Y = a0 + a1x1 +…+ akhk. V tomto vzorci Y znamená proměnnou, vliv faktorů, které 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 oni jsou těmi, kdo určují význam konkrétního faktoru. Index k označuje celkový počet právě těchto 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. Závislost menší než 0,5 je špatná.

Další důležitý ukazatel umístěný v buňce na průsečíku čáry "Křižovatka Y" a sloupec "kurzy". To udává, 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 "kurzy" 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íme, pomocí programy společnosti Microsoft Excel je poměrně snadné vytvořit tabulku regresní analýzy. Pracovat s výstupními daty a pochopit jejich podstatu ale umí jen proškolený člověk.