Korelační regresní analýza v příkladu řešení v Excelu. Konrad Carlberg. Regresní analýza v aplikaci Microsoft Excel

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řipraven;

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).

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.

Pomocí doplňku lze také provádět statistické zpracování dat BALÍČEK ANALÝZY(obr. 62).

Z navrhovaných položek vyberte položku " REGRESE“ a klikněte na něj levým tlačítkem myši. Dále klikněte na OK.

Okno zobrazené na Obr. 63.

Analytický nástroj « REGRESE» se používá k přizpůsobení grafu množině pozorování pomocí metody nejmenších čtverců. Regrese se používá k analýze vlivu hodnot jedné nebo více nezávislých proměnných na jednu závislou proměnnou. Například sportovní výkon sportovce je ovlivněn několika faktory, včetně věku, výšky a hmotnosti. Je možné vypočítat míru vlivu každého z těchto tří faktorů na výkon sportovce, a následně získaná data použít k predikci výkonu jiného sportovce.

Tuto funkci používá nástroj Regrese LINEST.

Dialogové okno REGRESS

Štítky Zaškrtněte toto políčko, pokud první řádek nebo první sloupec vstupního rozsahu obsahuje názvy. Pokud zde nejsou žádná záhlaví, zrušte zaškrtnutí tohoto políčka. V tomto případě se automaticky vygenerují vhodné hlavičky pro data výstupní tabulky.

Úroveň spolehlivosti Zaškrtnutím tohoto políčka zahrnete do tabulky součtů výstupů další úroveň. Do příslušného pole zadejte úroveň spolehlivosti, kterou chcete použít, kromě výchozí úrovně spolehlivosti 95 %.

Konstanta - nula Zaškrtněte políčko, aby regresní přímka procházela počátkem.

Výstupní rozsah Zadejte odkaz na levou horní buňku výstupního rozsahu. Pro výstupní tabulku výsledků přidělte alespoň sedm sloupců, které budou obsahovat: výsledky analýzy rozptylu, koeficienty, směrodatnou chybu výpočtu Y, směrodatné odchylky, počet pozorování, směrodatné chyby koeficientů.

Nový list Zaškrtnutím tohoto políčka otevřete nový list v sešitu a vložíte výsledky analýzy počínaje buňkou A1. V případě potřeby zadejte název nového listu do pole naproti příslušné pozici přepínače.

Nový sešit Zaškrtnutím tohoto políčka vytvoříte nový sešit, ve kterém budou výsledky přidány do nového listu.

Zbytky Zaškrtnutím tohoto políčka zahrnete zbytky do výstupní tabulky.

Standardizované rezidua Zaškrtnutím tohoto políčka zahrnete standardizovaná rezidua do výstupní tabulky.

Graf reziduí Zaškrtnutím tohoto políčka vykreslíte rezidua pro každou nezávislou proměnnou.

Přizpůsobit graf Zaškrtnutím tohoto políčka zobrazíte předpokládané hodnoty oproti pozorovaným hodnotám.

Normální pravděpodobnostní graf Zaškrtnutím políčka zobrazíte normální pravděpodobnost.

Funkce LINEST

Pro provedení výpočtů vybereme kurzorem buňku, ve které chceme zobrazit průměrnou hodnotu, a stiskneme klávesu = na klávesnici. Dále v poli Název zadejte například požadovanou funkci PRŮMĚRNÝ(obr. 22).

Funkce LINEST vypočítá statistiku pro řadu pomocí metody nejmenších čtverců k výpočtu přímky, která nejlépe aproximuje dostupná data, a poté vrátí pole, které popisuje výslednou přímku. Funkci můžete také kombinovat LINEST s dalšími funkcemi pro výpočet jiných druhů modelů, které jsou lineární v neznámých parametrech (jejichž neznámé parametry jsou lineární), včetně polynomiálních, logaritmických, exponenciálních a mocninných řad. Protože je vráceno pole hodnot, musí být funkce zadána jako maticový vzorec.

Rovnice pro přímku je:

y=m 1 x 1 +m 2 x 2 +…+b (v případě několika rozsahů hodnot x),

kde závislá hodnota y je funkcí nezávislé hodnoty x, hodnoty m jsou koeficienty odpovídající každé nezávisle proměnné x a b je konstanta. Všimněte si, že y, x a m mohou být vektory. Funkce LINEST vrátí pole (mn;mn-1;…;m 1 ;b). LINEST může také vrátit další regresní statistiky.

LINEST(známé_y-hodnoty; známé_x-hodnoty; konst; statistiky)

Známé_y hodnoty - sada hodnot y, které jsou již známé pro vztah y=mx+b.

Pokud má pole známé_y jeden sloupec, pak je každý sloupec pole pole známé_x interpretován jako samostatná proměnná.

Pokud má pole známé_y jeden řádek, pak je každý řádek pole pole známé_x interpretován jako samostatná proměnná.

Známé_x hodnoty - volitelná sada hodnot x, které jsou již známé pro vztah y=mx+b.

Pole známe_x může obsahovat jednu nebo více sad proměnných. Pokud je použita pouze jedna proměnná, pak pole_známé_y_hodnoty a známé_x_hodnoty mohou mít jakýkoli tvar – pokud mají stejný rozměr. Je-li použito více než jedna proměnná, pak musí být známé_y vektor (tj. jeden řádek vysoký nebo jeden sloupec široký).

Pokud je pole_známé_x vynecháno, předpokládá se, že toto pole (1;2;3;...) má stejnou velikost jako pole_známé_y.

Const je booleovská hodnota, která určuje, zda má být konstanta b 0.

Pokud je argument "const" PRAVDA nebo je vynechán, pak se konstanta b vyhodnotí normálně.

Pokud je argument "const" NEPRAVDA, pak se předpokládá, že hodnota b je 0 a hodnoty m jsou vybrány tak, aby byl splněn vztah y=mx.

Statistika je logická hodnota, která označuje, zda mají být vráceny další regresní statistiky.

Pokud je statistika PRAVDA, funkce LINREGRESE vrátí další regresní statistiku. Vrácené pole bude vypadat takto: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Pokud je statistika NEPRAVDA nebo je vynechána, funkce LINREGRESE vrátí pouze koeficienty ma konstantu b.

Další regresní statistiky. (Tabulka 17)

Obrázek níže ukazuje pořadí, ve kterém jsou vráceny další regresní statistiky (Obrázek 64).

Poznámky:

Jakákoli přímka může být popsána jejím sklonem a průsečíkem s osou y:

Sklon (m): pro určení sklonu přímky, obvykle označované m, je třeba vzít dva body na přímce (x 1 ,y 1) a (x 2 ,y 2); sklon bude roven (y 2 -y 1) / (x 2 -x 1).

Průsečík Y (b): Průsečík čáry y, obvykle označovaný b, je hodnota y pro bod, kde přímka protíná osu y.

Rovnice přímky má tvar y=mx+b. Pokud jsou známy hodnoty m a b, lze jakýkoli bod na přímce vypočítat dosazením hodnot y nebo x do rovnice. Můžete také použít funkci TREND.

Pokud existuje pouze jedna nezávislá proměnná x, můžete získat sklon a průsečík y přímo pomocí následujících vzorců:

Sklon: INDEX(LINEST(známé_y, známé_x); 1)

Intercept Y: INDEX(LINEST(známé_y, známé_x); 2)

Přesnost aproximace pomocí přímky vypočítané funkcí LINREGRESE závisí na míře rozptylu dat. Čím blíže jsou data k přímce, tím přesnější je model použitý funkcí LINREGRESE. Funkce LINREGRESE používá k určení nejlepšího přizpůsobení datům metodu nejmenších čtverců. Pokud existuje pouze jedna nezávislá proměnná x, m a b se vypočítají pomocí následujících vzorců:

kde x a y jsou průměry vzorku, například x = AVERAGE(známé_x) a y = AVERAGE(známé_y).

Funkce LINREGRESE a LGRFPRIBL mohou vypočítat přímou nebo exponenciální křivku, která nejlépe odpovídá datům. Neodpovídají však na otázku, který z obou výsledků je pro řešení problému vhodnější. Můžete také vypočítat funkci TREND(známé_y-hodnoty; známé_x-hodnoty) pro přímku nebo funkci RŮST (známé_y-hodnoty; známé_x-hodnoty) pro exponenciální křivku. Tyto funkce, pokud jsou vynechány z argumentu new_x_values ​​​​, vrátí pole vypočtených hodnot y pro skutečné hodnoty x podle přímky nebo křivky. Poté můžete porovnat vypočítané hodnoty se skutečnými hodnotami. Můžete také vytvářet grafy pro vizuální srovnání.

Při provádění regresní analýzy Microsoft Excel vypočítá pro každý bod druhou mocninu rozdílu mezi předpokládanou hodnotou y a skutečnou hodnotou y. Součet těchto čtverců rozdílů se nazývá zbytkový součet čtverců (ssresid). Microsoft Excel pak vypočítá celkový součet čtverců (sstotal). Pokud const = TRUE nebo pokud tento argument není zadán, celkový součet čtverců se bude rovnat součtu čtverců rozdílů skutečných hodnot y a středních hodnot y. Pokud const = FALSE, bude součet čtverců roven součtu druhých mocnin skutečných hodnot y (bez odečtení střední hodnoty y od podílu y). Poté lze regresní součet čtverců vypočítat následovně: ssreg = sstotal - ssresid. Čím menší je zbytkový součet čtverců, tím větší je hodnota koeficientu determinismu r2, což ukazuje, jak dobře rovnice získaná pomocí regresní analýzy vysvětluje vztahy mezi proměnnými. Koeficient r2 se rovná ssreg/sstotal.

V některých případech jeden nebo více sloupců X (za předpokladu, že hodnoty Y a X jsou ve sloupcích) nemá další prediktivní hodnotu v jiných sloupcích X. Jinými slovy, odstranění jednoho nebo více sloupců X může vést k hodnotám Y vypočítané se stejnou přesností. V tomto případě budou z regresního modelu vyloučeny nadbytečné sloupce X. Tento jev se nazývá "kolinearita", protože redundantní sloupce X mohou být reprezentovány jako součet několika neredundantních sloupců. Funkce LINREGRESE zkontroluje kolinearitu a pokud nějaké najde, odstraní z regresního modelu všechny nadbytečné sloupce X. Odebrané sloupce X lze ve výstupu funkce LINREGRESE identifikovat faktorem 0 a hodnotou se 0. Odstranění jednoho nebo více sloupců jako nadbytečných změní hodnotu df, protože závisí na počtu X sloupců skutečně použitých pro prediktivní účely. Další podrobnosti o výpočtu df viz příklad 4 níže. Když se df změní kvůli odstranění nadbytečných sloupců, změní se také hodnoty sey a F. Často se nedoporučuje používat kolinearitu. Mělo by se však použít, pokud některé sloupce X obsahují 0 nebo 1 jako indikátor, který označuje, zda je předmět experimentu v samostatné skupině. Pokud const = TRUE nebo pokud tento argument není zadán, funkce LINREGRESE vloží další sloupec X pro simulaci průsečíku. Pokud existuje sloupec s hodnotami 1 pro muže a 0 pro ženy a existuje sloupec s hodnotami 1 pro ženy a 0 pro muže, pak je poslední sloupec odstraněn, protože jeho hodnoty lze získat z sloupec "mužský ukazatel".

Výpočet df pro případy, kdy X sloupců není z modelu odstraněno kvůli kolinearitě, je následující: pokud existuje k známých_x sloupců a const = TRUE nebo není zadáno, pak df = n - k - 1. Pokud const = FALSE, pak df = n -k. V obou případech odstranění X sloupců kvůli kolinearitě zvýší hodnotu df o 1.

Vzorce, které vracejí pole, musí být zadány jako maticové vzorce.

Při zadávání pole konstant jako argumentu známé_x_hodnoty použijte například středník k oddělení hodnot na stejném řádku a dvojtečku k oddělení řádků. Oddělovací znaky se mohou lišit v závislosti na nastavení v okně "Jazyk a standardy" na ovládacím panelu.

Všimněte si, že hodnoty y předpovězené regresní rovnicí nemusí být správné, pokud jsou mimo rozsah hodnot y, které byly použity k definování rovnice.

Hlavní algoritmus použitý ve funkci LINEST, se liší od hlavního algoritmu funkcí NAKÝNIT A ÚSEČKA. Rozdíly mezi algoritmy mohou vést k různým výsledkům pro nejistá a kolineární data. Pokud jsou například datové body argumentu známé_y 0 a datové body argumentu známé_x jsou 1, pak:

Funkce LINEST vrátí hodnotu rovnou 0. Algoritmus funkce LINEST se používá k vrácení vhodných hodnot pro kolineární data, v takovém případě lze nalézt alespoň jednu odpověď.

Funkce SLOPE a INTERCEPT vrátí chybu #DIV/0!. Algoritmus funkcí SLOPE a INTERCEPT se používá k nalezení pouze jedné odpovědi, v tomto případě jich může být několik.

Kromě výpočtu statistik pro jiné typy regrese lze funkci LINREGRESE použít k výpočtu rozsahů pro jiné typy regrese zadáním funkcí proměnných x a y jako řady proměnných x a y pro funkci LINREGRESE. Například následující vzorec:

LINEST(y-hodnoty, x-hodnoty^COLUMN($A:$C))

pracuje s jedním sloupcem hodnot Y a jedním sloupcem hodnot X pro výpočet aproximace krychle (polynom 3. stupně) následujícího tvaru:

y=m1x+m2x2+m3x3+b

Vzorec lze upravit pro výpočty jiných typů regrese, ale v některých případech jsou nutné úpravy výstupních hodnot a další statistiky.

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 > d U, hypotéza není zamítnuta (tj. neexistuje autokorelace); Li d L< 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ěleno 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 obchodů 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.INR (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=S YX 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.

KORELAČNÍ-REGRESNÍ ANALÝZA VSLEČNA VYNIKAT

1. Vytvořte zdrojový datový soubor v MS Excel (například tabulka 2)

2. Konstrukce korelačního pole

Chcete-li vytvořit korelační pole v příkazovém řádku, vyberte nabídku Vložit / Diagram. V zobrazeném dialogovém okně vyberte typ grafu: tečkovaný; Pohled: bodový diagram, což vám umožní porovnávat dvojice hodnot (obr. 22).

Obrázek 22 - Výběr typu grafu

2. V místní nabídce vyberte příkaz Přidejte trendovou linii.

3. V zobrazeném dialogovém okně vyberte typ grafu (v našem příkladu lineární) a parametry rovnice, jak je znázorněno na obrázku 26.

Obrázek 27 - Korelační pole závislosti produktivity práce na poměru kapitálu a práce

Podobně konstruujeme korelační pole pro závislost produktivity práce na směnném poměru zařízení. (Obrázek 28).

z faktoru posunu zařízení

3. Konstrukce korelační matice.

Chcete-li vytvořit korelační matici v nabídce Servis Vybrat Analýza dat.

Použití nástroje pro analýzu dat Regrese, kromě výsledků regresní statistiky, analýzy rozptylu a intervalů spolehlivosti můžete získat rezidua a prokládací grafy regresní přímky, rezidua a normální pravděpodobnost. Chcete-li to provést, musíte zkontrolovat přístup k analytickému balíčku. Z hlavní nabídky vyberte Služba / Doplňky. Zaškrtávací políčko Balíček analýzy(Obrázek 29)

Po kliknutí na OK v zobrazeném dialogovém okně zadejte vstupní interval (v našem příkladu A2: D26), seskupení (v našem případě podle sloupců) a výstupní parametry, jak je znázorněno na obrázku 31.

Výsledek výpočtu je uveden v tabulce 4.

Tabulka 4 - Korelační matice

JEDNOVARIANTNÍ REGRESNÍ ANALÝZA

POUŽITÍ NÁSTROJE REGRESE

V nabídce provést regresní analýzu závislosti produktivity práce na poměru kapitálu a práce Servis Vybrat Analýza dat a určit nástroj pro analýzu Regrese(Obrázek 32).