Párová lineárna regresia v príkladoch programu Excel. Rýchla lineárna regresia v Exceli: Trendová čiara

Balík MS Excel vám umožňuje vykonať väčšinu práce veľmi rýchlo pri zostavovaní lineárnej regresnej rovnice. Je dôležité pochopiť, ako interpretovať získané výsledky.

Na fungovanie vyžaduje doplnok Analytický balík, ktorý musí byť povolený v položke menu Služba\Doplnky

V Exceli 2007, ak chcete povoliť analytický balík, musíte kliknúť na tlačidlo Prejsť na blokovanie Možnosti programu Excel kliknutím na tlačidlo v ľavom hornom rohu a potom na „ Možnosti programu Excel"v spodnej časti okna:

Ak chcete vytvoriť regresný model, musíte vybrať položku Služba\Analýza údajov\Regresia. (V Exceli 2007 je tento režim v bloku Údaje/analýza údajov/regresia). Zobrazí sa dialógové okno, ktoré musíte vyplniť:

1) Interval vstupu Y¾ obsahuje odkaz na bunky, ktoré obsahujú hodnoty výslednej charakteristiky r. Hodnoty musia byť usporiadané v stĺpci;

2) Interval vstupu X¾ obsahuje odkaz na bunky, ktoré obsahujú hodnoty faktorov. Hodnoty musia byť usporiadané v stĺpcoch;

3) Podpíšte sa Tagy nastaviť, ak prvé bunky obsahujú vysvetľujúci text (označenie údajov);

4) Úroveň spoľahlivosti¾ je úroveň spoľahlivosti, ktorá sa štandardne považuje za 95 %. Ak nie ste spokojní s touto hodnotou, musíte povoliť tento príznak a zadať požadovanú hodnotu;

5) Podpíšte sa Konštantná nula je zahrnuté, ak je potrebné zostrojiť rovnicu, v ktorej je voľná premenná ;

6) Možnosti výstupu určiť, kde majú byť výsledky umiestnené. V predvolenom režime zostavovania Nový pracovný list;

7) Blokovať Zvyšky umožňuje zahrnúť výstup rezíduí a konštrukciu ich grafov.

V dôsledku toho sa zobrazia informácie obsahujúce všetky potrebné informácie a zoskupené do troch blokov: Regresná štatistika, Analýza rozptylu, Odňatie rovnováhy. Poďme sa na ne pozrieť bližšie.

1. Regresná štatistika:

viacnásobný R je určená vzorcom ( Pearsonov korelačný koeficient);

R (koeficient determinácie);

Normalizované R-štvorec sa vypočíta podľa vzorca (používa sa na viacnásobnú regresiu);

Štandardná chyba S vypočítané podľa vzorca ;

Pozorovania ¾ je množstvo údajov n.

2. Analýza rozptylu, riadok Regresia:

Parameter df rovná sa m(počet súprav faktorov X);

Parameter SS je určený vzorcom ;

Parameter PANI je určený vzorcom ;

Štatistiky F je určený vzorcom ;

Význam F. Ak výsledné číslo presiahne , potom je hypotéza prijatá (neexistuje lineárny vzťah), v opačnom prípade je hypotéza prijatá (existuje lineárny vzťah).

3. Analýza rozptylu, riadok Zvyšok:

Parameter df rovná ;

Parameter SS sa určuje podľa vzorca ;

Parameter PANI sa určuje podľa vzorca.

4. Analýza rozptylu, riadok Celkom obsahuje súčet prvých dvoch stĺpcov.

5. Analýza rozptylu, riadok Priesečník Y obsahuje koeficient, smerodajnú chybu a t-štatistika.

P-hodnota ¾ je hodnota hladín významnosti zodpovedajúcich vypočítaným t-štatistikov. Určené funkciou STUDIST( t-štatistika; ). Ak P-hodnota presahuje , potom je príslušná premenná štatisticky nevýznamná a môže byť z modelu vylúčená.

Spodných 95 % A 95 % najlepších¾ sú dolné a horné hranice 95-percentného intervalu spoľahlivosti pre koeficienty teoretickej lineárnej regresnej rovnice. Ak bola hodnota pravdepodobnosti spoľahlivosti v bloku na zadávanie údajov ponechaná na predvolenej hodnote, posledné dva stĺpce budú duplikovať predchádzajúce. Ak používateľ zadal svoju vlastnú hodnotu spoľahlivosti, posledné dva stĺpce obsahujú hodnoty dolnej a hornej hranice pre zadanú úroveň spoľahlivosti.

6. Analýza rozptylu, riadky obsahujú hodnoty koeficientov, štandardné chyby, t- štatistik, P-hodnoty a intervaly spoľahlivosti pre zodpovedajúce .

7. Blokovať Odňatie rovnováhy obsahuje predpokladané hodnoty r(v našom zápise je to ) a zvyšky .

IN Excel Existuje ešte rýchlejší a pohodlnejší spôsob vykreslenia lineárnej regresie (a dokonca aj hlavných typov nelineárnych regresií, ako je uvedené nižšie). Dá sa to urobiť nasledovne:

1) vyberte stĺpce s údajmi X A Y(mali by byť v tomto poradí!);

2) zavolajte Sprievodca grafom a vyberte v skupine Typ – Spot a ihneď stlačte Pripravený;

3) bez zrušenia výberu diagramu vyberte položku hlavnej ponuky, ktorá sa zobrazí Diagram, v ktorom by ste mali vybrať položku Pridajte trendovú čiaru;

4) v zobrazenom dialógovom okne Trendová línia v záložke Typ vybrať Lineárne;

5) v záložke možnosti môžete aktivovať spínač Ukáž rovnicu v diagrame, čo vám umožní vidieť rovnicu lineárnej regresie (4.4), v ktorej budú vypočítané koeficienty (4.5).

6) Na tej istej karte môžete aktivovať prepínač Umiestnite hodnotu aproximačnej spoľahlivosti (R^2) do diagramu. Táto hodnota je druhou mocninou korelačného koeficientu (4.3) a ukazuje, ako dobre vypočítaná rovnica popisuje experimentálnu závislosť. Ak R 2 sa blíži k jednote, potom teoretická regresná rovnica dobre popisuje experimentálnu závislosť (teória dobre súhlasí s experimentom), a ak R 2 je blízka nule, potom táto rovnica nie je vhodná na popis experimentálnej závislosti (teória nesúhlasí s experimentom).

V dôsledku vykonania opísaných akcií získate diagram s regresným grafom a jeho rovnicou.

§ 4.3. Hlavné typy nelineárnej regresie

Parabolická a polynomická regresia.

Parabolický závislosť hodnoty Y od veľkosti X sa nazýva závislosť vyjadrená kvadratickou funkciou (parabola 2. rádu):

Táto rovnica sa nazýva parabolická regresná rovnica Y na X. možnosti A, b, s sa volajú parabolické regresné koeficienty. Výpočet parabolických regresných koeficientov je vždy ťažkopádny, preto sa odporúča použiť na výpočty počítač.

Rovnica (4.8) parabolickej regresie je špeciálnym prípadom všeobecnejšej regresie nazývanej polynóm. Polynóm závislosť hodnoty Y od veľkosti X sa nazýva závislosť vyjadrená polynómom n- poradie:

kde sú čísla a ja (i=0,1,…, n) sa volajú polynomiálne regresné koeficienty.

Regresia moci.

Moc závislosť hodnoty Y od veľkosti X sa nazýva závislosť tvaru:

Táto rovnica sa nazýva mocenská regresná rovnica Y na X. možnosti A A b sa volajú mocenské regresné koeficienty.

ln = ln a+b· ln X. (4.11)

Táto rovnica opisuje priamku v rovine s logaritmickými súradnicovými osami ln X a ln. Preto je kritériom použiteľnosti mocninovej regresie požiadavka, aby body logaritmov empirických údajov ln x i a ln y i boli najbližšie k priamke (4.11).

Exponenciálna regresia.

Orientačné(alebo exponenciálny) závislosť hodnoty Y od veľkosti X sa nazýva závislosť tvaru:

(alebo ). (4.12)

Táto rovnica sa nazýva exponenciálna rovnica(alebo exponenciálny) regresia Y na X. možnosti A(alebo k) A b sa volajú exponenciálne koeficienty(alebo exponenciálny) regresia.

Ak vezmeme logaritmus oboch strán mocninovej regresnej rovnice, dostaneme rovnicu

ln = X ln a+ln b(alebo ln = k x+ln b). (4.13)

Táto rovnica popisuje lineárnu závislosť logaritmu jednej veličiny ln na inej veličine X. Preto je kritériom použiteľnosti mocninovej regresie požiadavka, aby empirické údaje mali rovnakú hodnotu x i a logaritmy inej veličiny ln y i boli najbližšie k priamke (4.13).

Logaritmická regresia.

Logaritmický závislosť hodnoty Y od veľkosti X sa nazýva závislosť tvaru:

=a+b· ln X. (4.14)

Táto rovnica sa nazýva logaritmická regresná rovnica Y na X. možnosti A A b sa volajú logaritmické regresné koeficienty.

Hyperbolická regresia.

Hyperbolický závislosť hodnoty Y od veľkosti X sa nazýva závislosť tvaru:

Táto rovnica sa nazýva hyperbolická regresná rovnica Y na X. možnosti A A b sa volajú hyperbolické regresné koeficienty a sú určené metódou najmenších štvorcov. Aplikácia tejto metódy vedie k vzorcom:

Vo vzorcoch (4.16-4.17) sa sčítanie vykonáva cez index i od jedného po počet pozorovaní n.

Bohužiaľ, v Excel neexistujú žiadne funkcie na výpočet hyperbolických regresných koeficientov. V prípadoch, keď nie je známe, že namerané veličiny súvisia inverznou úmernosťou, odporúča sa namiesto hyperbolickej regresnej rovnice hľadať mocninovú regresnú rovnicu, takže v r. Excel existuje postup, ako to nájsť. Ak sa predpokladá hyperbolická závislosť medzi meranými veličinami, potom bude potrebné vypočítať jej regresné koeficienty pomocou pomocných výpočtových tabuliek a súčtových operácií pomocou vzorcov (4.16-4.17).

Regresná analýza v programe Microsoft Excel - najkomplexnejší sprievodca používaním MS Excel na riešenie problémov regresnej analýzy v oblasti obchodnej analýzy. Konrad Carlberg zrozumiteľne vysvetľuje teoretické problémy, ktorých znalosť vám pomôže vyhnúť sa mnohým chybám pri vlastnom vykonávaní regresnej analýzy, ako aj pri vyhodnocovaní výsledkov analýzy vykonanej inými ľuďmi. Všetok materiál, od jednoduchých korelácií a t-testov až po viacnásobnú analýzu kovariancie, je založený na príkladoch z reálneho sveta a je sprevádzaný podrobnými postupmi krok za krokom.

Kniha pojednáva o zvláštnostiach a kontroverziách regresných funkcií Excelu, skúma dôsledky každej možnosti a argumentu a vysvetľuje, ako spoľahlivo aplikovať regresné metódy v oblastiach od lekárskeho výskumu po finančnú analýzu.

Konrad Carlberg. Regresná analýza v programe Microsoft Excel. – M.: Dialektika, 2017. – 400 s.

Stiahnite si poznámku vo formáte alebo formáte, príklady vo formáte

Kapitola 1: Hodnotenie variability údajov

Štatistici majú k dispozícii mnoho variačných meraní. Jedným z nich je súčet štvorcových odchýlok jednotlivých hodnôt od priemeru. V Exceli na to slúži funkcia SQUARE(). Častejšie sa však používa rozptyl. Rozptyl je priemer štvorcových odchýlok. Odchýlka nie je citlivá na počet hodnôt v skúmanom súbore údajov (zatiaľ čo súčet štvorcových odchýlok sa zvyšuje s počtom meraní).

Excel ponúka dve funkcie, ktoré vrátia odchýlku: DISP.G() a DISP.V():

• Ak hodnoty, ktoré sa majú spracovať, tvoria populáciu, použite funkciu DISP.G(). To znamená, že hodnoty obsiahnuté v rozsahu sú jediné hodnoty, ktoré vás zaujímajú.
• Ak hodnoty, ktoré sa majú spracovať, tvoria vzorku z väčšej populácie, použite funkciu DISP.B(). Predpokladá sa, že existujú ďalšie hodnoty, ktorých rozptyl môžete tiež odhadnúť.

Ak sa veličina, ako je priemer alebo korelačný koeficient, vypočítava z populácie, nazýva sa to parameter. Podobná veličina vypočítaná na základe vzorky sa nazýva štatistika. Počítanie odchýlok z priemeru v danej množine dostanete súčet druhých mocnín odchýlok menšej veľkosti, ako keby ste ich počítali od akejkoľvek inej hodnoty. Podobné tvrdenie platí pre rozptyl.

Čím väčšia je veľkosť vzorky, tým presnejšia je vypočítaná štatistická hodnota. Neexistuje však žiadna veľkosť vzorky menšia ako veľkosť populácie, pri ktorej si môžete byť istí, že štatistická hodnota sa zhoduje s hodnotou parametra.

Povedzme, že máte súbor 100 výšok, ktorých priemer sa líši od priemeru populácie, bez ohľadu na to, aký malý je rozdiel. Výpočtom rozptylu pre vzorku získate hodnotu, povedzme 4. Táto hodnota je menšia ako akákoľvek iná hodnota, ktorú možno získať výpočtom odchýlky každej zo 100 hodnôt výšky vo vzťahu k akejkoľvek hodnote inej ako je priemer vzorky , vrátane pomeru k skutočnému priemeru bežnej populácie. Vypočítaný rozptyl sa preto bude líšiť a bude menší od rozptylu, ktorý by ste získali, keby ste nejakým spôsobom zistili a použili parameter populácie a nie výberový priemer.

Priemerná suma štvorcov určená pre vzorku poskytuje nižší odhad rozptylu populácie. Takto vypočítaný rozptyl sa nazýva tzv vysídlený hodnotenie. Ukazuje sa, že na odstránenie skreslenia a získanie nezaujatého odhadu stačí vydeliť súčet štvorcových odchýlok nie n, Kde n- veľkosť vzorky a n – 1.

Rozsah n – 1 sa nazýva počet (počet) stupňov voľnosti. Existujú rôzne spôsoby výpočtu tohto množstva, hoci všetky zahŕňajú buď odpočítanie určitého čísla od veľkosti vzorky, alebo sčítanie počtu kategórií, do ktorých pozorovania spadajú.

Podstata rozdielu medzi funkciami DISP.G() a DISP.V() je nasledovná:

• Vo funkcii VAR.G() sa súčet štvorcov delí počtom pozorovaní, a preto predstavuje skreslený odhad rozptylu, skutočný priemer.
• Vo funkcii DISP.B() sa súčet štvorcov delí počtom pozorovaní mínus 1, t.j. počtom stupňov voľnosti, čo poskytuje presnejší, nezaujatý odhad rozptylu populácie, z ktorej bola vzorka odobratá.

Smerodajná odchýlka smerodajná odchýlka, SD) – je druhá odmocnina rozptylu:

Umocnenie odchýlok transformuje meraciu stupnicu na inú metriku, ktorá je druhou mocninou pôvodnej metriky: metre - na metre štvorcové, doláre - na doláre štvorcové atď. Smerodajná odchýlka je druhá odmocnina rozptylu, a preto nás vracia späť k pôvodným jednotkám merania. Čo je pohodlnejšie.

Často je potrebné vypočítať štandardnú odchýlku potom, čo boli údaje podrobené určitej manipulácii. A hoci v týchto prípadoch sú výsledky nepochybne štandardné odchýlky, zvyčajne sa nazývajú štandardné chyby. Existuje niekoľko typov štandardných chýb, vrátane štandardnej chyby merania, štandardnej chyby proporcie a štandardnej chyby priemeru.

Povedzme, že ste zhromaždili údaje o výške pre 25 náhodne vybraných dospelých mužov v každom z 50 štátov. Ďalej vypočítate priemernú výšku dospelých mužov v každom štáte. Výsledných 50 priemerných hodnôt zasa možno považovať za pozorovania. Z toho by ste mohli vypočítať ich štandardnú odchýlku, ktorá je štandardná chyba priemeru. Ryža. 1. porovnáva rozdelenie 1 250 hrubých individuálnych hodnôt (údaje o výške pre 25 mužov v každom z 50 štátov) s rozdelením priemerov 50 štátov. Vzorec na odhad štandardnej chyby priemeru (t. j. štandardnej odchýlky priemeru, nie jednotlivých pozorovaní):

kde je štandardná chyba priemeru; s– štandardná odchýlka pôvodných pozorovaní; n– počet pozorovaní vo vzorke.

Ryža. 1. Rozdiely v priemeroch medzi jednotlivými štátmi sú výrazne menšie ako odchýlky v jednotlivých pozorovaniach.

V štatistike existuje konvencia týkajúca sa používania gréckych a latinských písmen na reprezentáciu štatistických veličín. Je zvykom označovať parametre všeobecnej populácie gréckymi písmenami a vzorové štatistiky latinskými písmenami. Preto, keď hovoríme o štandardnej odchýlke populácie, píšeme ju ako σ; ak sa berie do úvahy smerodajná odchýlka vzorky, potom použijeme označenie s. Čo sa týka symbolov na označovanie priemerov, tie sa navzájom až tak nezhodujú. Populačný priemer sa označuje gréckym písmenom μ. Symbol X sa však tradične používa na vyjadrenie priemernej hodnoty vzorky.

z-skóre vyjadruje pozíciu pozorovania v rozdelení v jednotkách štandardnej odchýlky. Napríklad z = 1,5 znamená, že pozorovanie je vzdialené 1,5 štandardnej odchýlky od priemeru. Termín z-skóre slúžia na individuálne hodnotenia, t.j. pre rozmery priradené jednotlivým vzorovým prvkom. Termín používaný na označenie takýchto štatistík (ako je štátny priemer) z-skóre:

kde X je priemer vzorky, μ je priemer populácie, je štandardná chyba priemeru súboru vzoriek:

kde σ je štandardná chyba súboru (individuálne merania), n- veľkosť vzorky.

Povedzme, že pracujete ako inštruktor v golfovom klube. Dokázali ste merať vzdialenosť svojich striel počas dlhého časového obdobia a viete, že priemer je 205 yardov a štandardná odchýlka je 36 yardov. Je vám ponúknutá nová palica s tvrdením, že zvýši vašu zásahovú vzdialenosť o 10 yardov. Požiadate každého z ďalších 81 patrónov klubu, aby urobil skúšobný výstrel s novou palicou a zaznamenal svoju vzdialenosť švihu. Ukázalo sa, že priemerná vzdialenosť s novým klubom bola 215 yardov. Aká je pravdepodobnosť, že rozdiel 10 yardov (215 – 205) je spôsobený výlučne výberovou chybou? Alebo povedané inak: Aká je pravdepodobnosť, že pri rozsiahlejšom testovaní nový klub nepreukáže nárast úderovej vzdialenosti nad existujúci dlhodobý priemer 205 yardov?

Môžeme to skontrolovať vygenerovaním z-skóre. Štandardná chyba priemeru:

Potom z-skóre:

Musíme nájsť pravdepodobnosť, že priemer vzorky bude vzdialený 2,5σ od priemeru populácie. Ak je pravdepodobnosť malá, tak rozdiely nie sú spôsobené náhodou, ale kvalitou nového klubu. Excel nemá pripravenú funkciu na určenie pravdepodobnosti z-skóre. Môžete však použiť vzorec =1-NORM.ST.DIST(z-skóre,TRUE), kde funkcia NORM.ST.DIST() vráti oblasť pod normálnou krivkou naľavo od z-skóre (obrázok 2).

Ryža. 2. Funkcia NORM.ST.DIST() vráti oblasť pod krivkou naľavo od hodnoty z; Ak chcete obrázok zväčšiť, kliknite naň pravým tlačidlom myši a vyberte Otvoriť obrázok na novej karte

Druhý argument funkcie NORM.ST.DIST() môže nadobudnúť dve hodnoty: TRUE – funkcia vráti plochu oblasti pod krivkou naľavo od bodu určeného prvým argumentom; FALSE – funkcia vráti výšku krivky v bode určenom prvým argumentom.

Ak stredná hodnota populácie (μ) a štandardná odchýlka (σ) nie sú známe, použije sa t-hodnota (pozri podrobnosti). Štruktúry z-skóre a t-skóre sa líšia v tom, že na nájdenie t-skóre sa používa skôr štandardná odchýlka s získaná z výsledkov vzorky než známa hodnota parametra populácie σ. Normálna krivka má jeden tvar a tvar distribúcie t-hodnoty sa mení v závislosti od počtu stupňov voľnosti df. stupne slobody) vzorky, ktorú predstavuje. Počet stupňov voľnosti vzorky je rovný n – 1, Kde n- veľkosť vzorky (obr. 3).

Ryža. 3. Tvar t-rozdelení, ktoré vznikajú v prípadoch, keď parameter σ nie je známy, sa líši od tvaru normálneho rozdelenia.

Excel má dve funkcie pre t-rozdelenie, ktoré sa tiež nazýva študentské rozdelenie: STUDENT.DIST() vráti oblasť pod krivkou naľavo od danej t-hodnoty a STUDENT.DIST.PH() vráti oblasť do správny.

Kapitola 2. Korelácia

Korelácia je miera závislosti medzi prvkami množiny usporiadaných párov. Charakterizuje sa korelácia Pearsonove korelačné koeficienty–r. Koeficient môže nadobúdať hodnoty v rozsahu od –1,0 do +1,0.

Kde Sx A S y– štandardné odchýlky premenných X A Y, S xy- kovariancia:

V tomto vzorci sa kovariancia delí štandardnými odchýlkami premenných X A Y, čím sa z kovariancie odstránia efekty škálovania súvisiace s jednotkou. Excel používa funkciu CORREL(). Názov tejto funkcie neobsahuje kvalifikačné prvky Г a В, ktoré sa používajú v názvoch funkcií ako STANDARDEV(), VARIANCE() alebo COVARIANCE(). Hoci výberový korelačný koeficient poskytuje skreslený odhad, dôvod skreslenia je iný ako v prípade rozptylu alebo štandardnej odchýlky.

V závislosti od veľkosti všeobecného korelačného koeficientu (často označovaného gréckym písm ρ ), korelačný koeficient r vytvára skreslený odhad, pričom účinok skreslenia sa zvyšuje so znižovaním veľkosti vzorky. Toto skreslenie sa však nesnažíme korigovať tak, ako sme to robili napríklad pri výpočte smerodajnej odchýlky, keď sme do zodpovedajúceho vzorca dosadili nie počet pozorovaní, ale počet stupňov voľnosti. V skutočnosti počet pozorovaní použitých na výpočet kovariancie nemá žiadny vplyv na veľkosť.

Štandardný korelačný koeficient je určený na použitie s premennými, ktoré sú navzájom spojené lineárnym vzťahom. Prítomnosť nelinearity a/alebo chýb v údajoch (odľahlé hodnoty) vedie k nesprávnemu výpočtu korelačného koeficientu. Na diagnostiku problémov s údajmi sa odporúča vytvoriť bodové grafy. Toto je jediný typ grafu v Exceli, ktorý považuje horizontálnu aj vertikálnu os za os hodnôt. Čiarový graf definuje jeden zo stĺpcov ako os kategórie, čo skresľuje obraz údajov (obr. 4).

Ryža. 4. Regresné priamky sa zdajú byť rovnaké, ale porovnajte ich rovnice medzi sebou

Pozorovania použité na zostavenie čiarového grafu sú usporiadané v rovnakej vzdialenosti pozdĺž horizontálnej osi. Označenia delenia pozdĺž tejto osi sú len označenia, nie číselné hodnoty.

Hoci korelácia často znamená, že existuje vzťah príčina-následok, nemožno ju použiť na dôkaz, že je to tak. Štatistika sa nepoužíva na preukázanie toho, či je teória pravdivá alebo nepravdivá. Ak chcete vylúčiť konkurenčné vysvetlenia výsledkov pozorovania, dajte plánované experimenty. Štatistiky sa používajú na zhrnutie informácií zozbieraných počas takýchto experimentov a na kvantifikáciu pravdepodobnosti, že prijaté rozhodnutie môže byť nesprávne vzhľadom na dostupnú základňu dôkazov.

Kapitola 3: Jednoduchá regresia

Ak dve premenné spolu súvisia, takže hodnota korelačného koeficientu presahuje povedzme 0,5, potom je v tomto prípade možné predpovedať (s určitou presnosťou) neznámu hodnotu jednej premennej zo známej hodnoty druhej. . Na získanie hodnôt prognózy cien na základe údajov zobrazených na obr. 5, môžete použiť ktorýkoľvek z niekoľkých možných spôsobov, ale takmer určite nepoužijete ten, ktorý je znázornený na obr. 5. Napriek tomu by ste sa s ňou mali zoznámiť, pretože žiadna iná metóda vám neumožňuje preukázať spojenie medzi koreláciou a predikciou tak jasne ako táto. Na obr. 5 v rozsahu B2:C12 zobrazuje náhodnú vzorku desiatich domov a poskytuje údaje o ploche každého domu (v štvorcových stopách) a jeho predajnej cene.

Ryža. 5. Hodnoty prognózovaných predajných cien tvoria priamku

Nájdite priemer, štandardné odchýlky a korelačný koeficient (rozsah A14:C18). Vypočítajte plošné z-skóre (E2:E12). Napríklad bunka E3 obsahuje vzorec: =(B3-$B$14)/$B$15. Vypočítajte z-skóre prognózovanej ceny (F2:F12). Napríklad bunka F3 obsahuje vzorec: =ЕЗ*$В$18. Preveďte z-skóre na dolárové ceny (H2:H12). V bunke NZ je vzorec: =F3*$C$15+$C$14.

Všimnite si, že predpovedaná hodnota má vždy tendenciu posúvať sa smerom k priemeru 0. Čím bližšie je korelačný koeficient k nule, tým bližšie k nule je predpovedané z-skóre. V našom príklade je korelačný koeficient medzi plochou a predajnou cenou 0,67 a prognózovaná cena je 1,0 * 0,67, t.j. 0,67. To zodpovedá prekročeniu hodnoty nad priemer rovnajúcej sa dvom tretinám štandardnej odchýlky. Ak by sa korelačný koeficient rovnal 0,5, potom by prognózovaná cena bola 1,0 * 0,5, t.j. 0,5. To zodpovedá prekročeniu hodnoty nad priemer, ktorá sa rovná iba polovici štandardnej odchýlky. Vždy, keď sa hodnota korelačného koeficientu líši od ideálnej hodnoty, t.j. väčšie ako -1,0 a menšie ako 1,0, skóre predpovedanej premennej by malo byť bližšie k jej priemeru ako skóre prediktorovej (nezávislej) premennej k jej vlastnej. Tento jav sa nazýva regresia k priemeru alebo jednoducho regresia.

Excel má niekoľko funkcií na určenie koeficientov rovnice regresnej čiary (v Exceli nazývanej trendová čiara) y =kx + b. Na určenie k slúži funkciu

=SLOPE(známe_y_hodnoty, známe_x_hodnoty)

Tu pri je predpokladaná premenná a X- nezávislá premenná. Toto poradie premenných musíte prísne dodržiavať. Sklon regresnej priamky, korelačný koeficient, štandardné odchýlky premenných a kovariancia spolu úzko súvisia (obrázok 6). Funkcia INTERMEPT() vráti hodnotu zachytenú regresnou čiarou na zvislej osi:

=LIMIT(známe_hodnoty_y, známe_x_hodnoty)

Ryža. 6. Vzťah medzi štandardnými odchýlkami prevádza kovarianciu na korelačný koeficient a sklon regresnej priamky

Všimnite si, že počet hodnôt x a y poskytnutých ako argumenty pre funkcie SLOPE() a INTERCEPT() musí byť rovnaký.

Pri regresnej analýze sa používa ďalší dôležitý ukazovateľ - R 2 (R-štvorec), čiže koeficient determinácie. Určuje, aký príspevok k celkovej variabilite údajov má vzťah medzi X A pri. V Exceli na to existuje funkcia s názvom CVPIERSON(), ktorá berie presne tie isté argumenty ako funkcia CORREL().

Dve premenné s nenulovým korelačným koeficientom medzi nimi vysvetľujú rozptyl alebo vysvetľujú rozptyl. Typicky vysvetlený rozptyl je vyjadrený v percentách. Takže R 2 = 0,81 znamená, že je vysvetlených 81 % rozptylu (rozptyl) dvoch premenných. Zvyšných 19 % je spôsobených náhodnými výkyvmi.

Excel má funkciu TREND, ktorá uľahčuje výpočty. Funkcia TREND():

• akceptuje známe hodnoty, ktoré poskytnete X a známe hodnoty pri;
• vypočíta sklon regresnej priamky a konštanty (priesečník);
• vráti predpovedané hodnoty pri, určené použitím regresnej rovnice na známe hodnoty X(obr. 7).

Funkcia TREND() je funkcia poľa (ak ste sa s takýmito funkciami ešte nestretli, odporúčam).

Ryža. 7. Použitie funkcie TREND() umožňuje zrýchliť a zjednodušiť výpočty v porovnaní s použitím dvojice funkcií SLOPE() a INTERCEPT()

Ak chcete zadať funkciu TREND() ako vzorec poľa v bunkách G3:G12, vyberte rozsah G3:G12, zadajte vzorec TREND (NW:S12;V3:B12), stlačte a podržte klávesy a až potom stlačte kláves . Všimnite si, že vzorec je uzavretý v zložených zátvorkách: ( a ). Takto vám Excel povie, že tento vzorec je vnímaný ako vzorec poľa. Nezadávajte zátvorky sami: Ak sa ich pokúsite zadať sami ako súčasť vzorca, Excel bude s vaším vstupom zaobchádzať ako s bežným textovým reťazcom.

Funkcia TREND() má ďalšie dva argumenty: new_values_x A konšt. Prvý vám umožňuje vytvoriť predpoveď do budúcnosti a druhý môže prinútiť regresnú čiaru prejsť cez počiatok (hodnota TRUE hovorí Excelu, aby použil vypočítanú konštantu, hodnota FALSE hovorí Excelu, aby použil konštantu = 0 ). Excel vám umožňuje nakresliť regresnú čiaru na grafe tak, aby prechádzala počiatkom. Začnite nakreslením bodového grafu a potom kliknite pravým tlačidlom myši na jednu zo značiek série údajov. Vyberte položku v kontextovej ponuke, ktorá sa otvorí Pridajte trendovú čiaru; vyberte možnosť Lineárne; ak je to potrebné, prejdite nadol po paneli a začiarknite políčko Nastaviť križovatku; Uistite sa, že jeho priradené textové pole je nastavené na 0,0.

Ak máte tri premenné a chcete určiť koreláciu medzi dvoma z nich a zároveň eliminovať vplyv tretej, môžete použiť čiastočná korelácia. Predpokladajme, že vás zaujíma vzťah medzi percentom obyvateľov mesta, ktorí ukončili vysokú školu, a počtom kníh v mestských knižniciach. Zozbierali ste údaje pre 50 miest, ale... Problém je, že oba tieto parametre môžu závisieť od blahobytu obyvateľov konkrétneho mesta. Samozrejme, je veľmi ťažké nájsť ďalších 50 miest, ktoré sa vyznačujú presne rovnakou úrovňou blahobytu obyvateľov.

Použitím štatistických metód na kontrolu vplyvu bohatstva na finančnú podporu knižnice a cenovú dostupnosť vysokej školy by ste mohli získať presnejšiu kvantifikáciu sily vzťahu medzi premennými, ktoré vás zaujímajú, konkrétne počtom kníh a počtom absolventov. Takáto podmienená korelácia medzi dvoma premennými, keď sú hodnoty iných premenných pevné, sa nazýva čiastočná korelácia. Jedným zo spôsobov, ako to vypočítať, je použiť rovnicu:

Kde rC.B. . W- korelačný koeficient medzi premennými College a Books bez vplyvu (pevná hodnota) premennej Bohatstva; rC.B.- korelačný koeficient medzi premennými College a Books; rCW- korelačný koeficient medzi premennými College a Welfare; rB.W.- korelačný koeficient medzi premennými Books a Welfare.

Na druhej strane čiastočnú koreláciu možno vypočítať na základe analýzy rezíduí, t.j. rozdiely medzi predpovedanými hodnotami a súvisiacimi výsledkami skutočných pozorovaní (obe metódy sú uvedené na obr. 8).

Ryža. 8. Čiastočná korelácia ako korelácia rezíduí

Na zjednodušenie výpočtu matice korelačných koeficientov (B16:E19) použite analytický balík Excel (menu Údaje –> Analýza –> Analýza dát). V predvolenom nastavení tento balík nie je aktívny v Exceli. Ak ho chcete nainštalovať, prejdite cez ponuku Súbor –> možnosti –> Doplnky. V spodnej časti otvoreného okna možnostiExcel nájsť pole Kontrola, vyberte DoplnkyExcel, kliknite Choď. Začiarknite políčko vedľa doplnku Analytický balík. Kliknite na A analýza dát, vyberte možnosť Korelácia. Zadajte $B$2:$D$13 ako vstupný interval, začiarknite políčko Štítky v prvom riadku, zadajte $B$16:$E$19 ako výstupný interval.

Ďalšou možnosťou je určiť semi-čiastočnú koreláciu. Napríklad skúmate vplyv výšky a veku na hmotnosť. Máte teda dve prediktorové premenné – výšku a vek a jednu predikčnú premennú – hmotnosť. Chcete vylúčiť vplyv jednej prediktorovej premennej na inú, ale nie na prediktorovú premennú:

kde H – výška, W – hmotnosť, A – vek; Index semi-parciálneho korelačného koeficientu používa zátvorky na označenie, ktorá premenná sa odstraňuje az ktorej premennej. V tomto prípade zápis W(H.A) označuje, že vplyv premennej Vek je odstránený z premennej Výška, ale nie z premennej Hmotnosť.

Mohlo by sa zdať, že diskutovaná téma nie je príliš dôležitá. Koniec koncov, najviac záleží na tom, ako presne funguje celková regresná rovnica, zatiaľ čo problém relatívnych príspevkov jednotlivých premenných k celkovému vysvetlenému rozptylu sa zdá byť druhoradý. Nie je to však tak. Akonáhle sa začnete pýtať, či sa premenná vôbec oplatí použiť vo viacnásobnej regresnej rovnici, otázka sa stáva dôležitou. Môže ovplyvniť posúdenie správnosti výberu modelu na analýzu.

Kapitola 4. Funkcia LINREGRESE().

Funkcia LINREGRESE() vráti 10 regresných štatistík. Funkcia LINREGRESE() je funkcia poľa. Ak ho chcete zadať, vyberte rozsah obsahujúci päť riadkov a dva stĺpce, zadajte vzorec a kliknite (Obr. 9):

LINEST(B2:B21;A2:A21;PRAVDA;PRAVDA)

Ryža. 9. Funkcia LINREGRESE(): a) vyberte rozsah D2:E6, b) zadajte vzorec, ako je znázornené na riadku vzorcov, c) kliknite

Funkcia LINREGRESE() vráti:

• regresný koeficient (alebo sklon, bunka D2);
• segment (alebo konštanta, bunka E3);
• štandardné chyby regresného koeficientu a konštanty (rozsah D3:E3);
• koeficient determinácie R 2 pre regresiu (bunka D4);
• štandardná chyba odhadu (bunka E4);
• F-test pre úplnú regresiu (bunka D5);
• počet stupňov voľnosti pre zvyškový súčet štvorcov (bunka E5);
• regresný súčet štvorcov (bunka D6);
• zvyškový súčet štvorcov (bunka E6).

Pozrime sa na každú z týchto štatistík a na ich interakciu.

Štandardná chyba v našom prípade je to smerodajná odchýlka vypočítaná pre výberové chyby. To znamená, že ide o situáciu, keď bežná populácia má jednu štatistiku a vzorka má inú. Vydelením regresného koeficientu štandardnou chybou získate hodnotu 2,092/0,818 = 2,559. Inými slovami, regresný koeficient 2,092 je dva a pol štandardnej chyby od nuly.

Ak je regresný koeficient nula, potom najlepším odhadom predpovedanej premennej je jej priemer. Dva a pol štandardnej chyby je pomerne veľká a môžete bezpečne predpokladať, že regresný koeficient pre populáciu je nenulový.

Pravdepodobnosť získania vzorového regresného koeficientu 2,092 môžete určiť, ak je jeho skutočná hodnota v populácii 0,0 pomocou funkcie

STUDENT.DIST.PH (t-kritérium = 2,559; počet stupňov voľnosti = 18)

Vo všeobecnosti počet stupňov voľnosti = n – k – 1, kde n je počet pozorovaní a k je počet prediktorových premenných.

Tento vzorec vráti 0,00987 alebo zaokrúhlené na 1 %. Hovorí nám, že ak je regresný koeficient pre populáciu 0 %, potom pravdepodobnosť získania vzorky 20 ľudí, pre ktorú je odhadovaný regresný koeficient 2,092, je skromné ​​1 %.

F-test (bunka D5 na obr. 9) vykonáva vo vzťahu k plnej regresii rovnaké funkcie ako t-test vo vzťahu ku koeficientu jednoduchej párovej regresie. F test sa používa na testovanie, či je koeficient determinácie R 2 pre regresiu dostatočne veľký na zamietnutie hypotézy, že v populácii má hodnotu 0,0, čo naznačuje, že neexistuje rozptyl vysvetlený prediktorom a predpovedanou premennou. Ak existuje iba jedna prediktorová premenná, F-test sa presne rovná druhej mocnine t-testu.

Doteraz sme sa zaoberali intervalovými premennými. Ak máte premenné, ktoré môžu nadobudnúť viacero hodnôt a predstavujú jednoduché mená, napríklad Muž a žena alebo Plaz, Obojživelník a Ryba, uveďte ich ako číselný kód. Takéto premenné sa nazývajú nominálne.

Štatistika R2 kvantifikuje podiel vysvetleného rozptylu.

Štandardná chyba odhadu. Na obr. Obrázok 4.9 predstavuje predpokladané hodnoty premennej Hmotnosť, získané na základe jej vzťahu s premennou Výška. Rozsah E2:E21 obsahuje zostatkové hodnoty pre premennú Hmotnosť. Presnejšie, tieto rezíduá sa nazývajú chyby – odtiaľ pochádza termín štandardná chyba odhadu.

Ryža. 10. R 2 aj štandardná chyba odhadu vyjadrujú presnosť prognóz získaných pomocou regresie

Čím menšia je štandardná chyba odhadu, tým presnejšia je regresná rovnica a tým bližšie očakávate, že akákoľvek predpoveď vytvorená rovnicou bude zodpovedať skutočnému pozorovaniu. Štandardná chyba odhadu poskytuje spôsob, ako tieto očakávania kvantifikovať. Hmotnosť 95% ľudí s určitou výškou bude v rozmedzí:

(výška * 2,092 – 3,591) ± 2,092 * 21,118

F-štatistika je pomer rozptylu medzi skupinami k rozptylu v rámci skupiny. Tento názov zaviedol štatistik George Snedecor na počesť Sira, ktorý na začiatku 20. storočia vyvinul analýzu rozptylu (ANOVA, Analysis of Variance).

Koeficient determinácie R 2 vyjadruje podiel celkového súčtu štvorcov spojených s regresiou. Hodnota (1 – R 2) vyjadruje podiel celkového súčtu druhých mocnín spojených s rezíduami – chybami prognózy. F-test je možné získať pomocou funkcie LINEST (bunka F5 na obr. 11), pomocou súčtu druhých mocnín (rozsah G10:J11), pomocou proporcií rozptylu (rozsah G14:J15). Vzorce je možné študovať v priloženom súbore Excel.

Ryža. 11. Výpočet F-kritéria

Pri použití nominálnych premenných sa používa fiktívne kódovanie (obrázok 12). Na zakódovanie hodnôt je vhodné použiť hodnoty 0 a 1. Pravdepodobnosť F sa vypočíta pomocou funkcie:

F.DIST.PH(K2;I2;I3)

Tu funkcia F.DIST.PH() vracia pravdepodobnosť získania F-kritéria, ktoré sa riadi centrálnym F-rozdelením (obr. 13) pre dve sady údajov s počtom stupňov voľnosti daným v bunkách I2 a I3, ktorého hodnota sa zhoduje s hodnotou uvedenou v bunke K2.

Ryža. 12. Regresná analýza s použitím fiktívnych premenných

Ryža. 13. Centrálna F-distribúcia pri λ = 0

Kapitola 5. Viacnásobná regresia

Keď prejdete z jednoduchej párovej regresie s jednou prediktorovou premennou k viacnásobnej regresii, pridáte jednu alebo viac prediktorových premenných. Uložte hodnoty premenných prediktora do susedných stĺpcov, ako sú stĺpce A a B v prípade dvoch prediktorov alebo A, B a C v prípade troch prediktorov. Pred zadaním vzorca, ktorý obsahuje funkciu LINREGRESE(), vyberte päť riadkov a toľko stĺpcov, koľko je premenných prediktora, plus jeden pre konštantu. V prípade regresie s dvoma prediktorovými premennými možno použiť nasledujúcu štruktúru:

LINEST(A2: A41; B2: C41;;PRAVDA)

Podobne v prípade troch premenných:

LINEST(A2:A61;B2:D61;;PRAVDA)

Povedzme, že chcete študovať možné účinky veku a stravy na hladiny LDL – lipoproteínov s nízkou hustotou, o ktorých sa predpokladá, že sú zodpovedné za tvorbu aterosklerotických plátov, ktoré spôsobujú aterotrombózu (obr. 14).

Ryža. 14. Viacnásobná regresia

R2 viacnásobnej regresie (zobrazené v bunke F13) je väčšie ako R2 akejkoľvek jednoduchej regresie (E4, H4). Viacnásobná regresia využíva viacero prediktorových premenných súčasne. V tomto prípade sa R2 takmer vždy zvyšuje.

Pre akúkoľvek jednoduchú lineárnu regresnú rovnicu s jednou prediktorovou premennou bude vždy existovať dokonalá korelácia medzi predpovedanými hodnotami a hodnotami prediktorovej premennej, pretože rovnica násobí hodnoty prediktora jednou konštantou a pridáva ďalšiu konštantu. každý výrobok. Tento efekt nepretrváva pri viacnásobnej regresii.

Zobrazenie výsledkov vrátených funkciou LINREGRESE() pre viacnásobnú regresiu (obrázok 15). Regresné koeficienty sa vydávajú ako súčasť výsledkov vrátených funkciou LINREGRESE(). v opačnom poradí premenných(G–H–I zodpovedá C–B–A).

Ryža. 15. Koeficienty a ich štandardné chyby sú na pracovnom liste zobrazené v opačnom poradí.

Princípy a postupy používané v regresnej analýze premenných s jedným prediktorom sa dajú ľahko prispôsobiť tak, aby zohľadňovali viacero premenných prediktorov. Ukazuje sa, že veľká časť tejto adaptácie závisí od eliminácie vzájomného vplyvu prediktorových premenných. Ten je spojený s parciálnymi a semi-parciálnymi koreláciami (obr. 16).

Ryža. 16. Viacnásobnú regresiu možno vyjadriť pomocou párovej regresie rezíduí (vzorce nájdete v súbore Excel)

V Exceli sú funkcie, ktoré poskytujú informácie o t- a F-rozdeleniach. Funkcie, ktorých názvy zahŕňajú časť DIST, ako napríklad STUDENT.DIST() a F.DIST(), berú t-test alebo F-test ako argument a vracajú pravdepodobnosť pozorovania zadanej hodnoty. Funkcie, ktorých názvy zahŕňajú časť OBR, ako napríklad STUDENT.INV() a F.INR(), berú hodnotu pravdepodobnosti ako argument a vrátia hodnotu kritéria zodpovedajúcu zadanej pravdepodobnosti.

Keďže hľadáme kritické hodnoty t-distribúcie, ktoré odrežú okraje jeho koncových oblastí, odovzdáme 5 % ako argument jednej z funkcií STUDENT.INV(), ktorá vráti hodnotu zodpovedajúcu tejto pravdepodobnosti. (obr. 17, 18).

Ryža. 17. Dvojstranný t-test

Ryža. 18. Jednostranný t-test

Zavedením rozhodovacieho pravidla pre jednostrannú oblasť alfa zvýšite štatistickú silu testu. Ak ste si pri začatí experimentu istí, že máte všetky dôvody očakávať pozitívny (alebo negatívny) regresný koeficient, mali by ste vykonať jednostranný test. V tomto prípade bude vyššia pravdepodobnosť, že sa pri odmietnutí hypotézy nulového regresného koeficientu v populácii rozhodnete správne.

Štatistici radšej používajú tento termín riadený test namiesto termínu test s jedným chvostom a termín neriadený test namiesto termínu obojstranný test. Výrazy riadený a neorientovaný sú preferované, pretože zdôrazňujú skôr typ hypotézy než povahu koncových častí distribúcie.

Prístup k hodnoteniu vplyvu prediktorov na základe porovnávania modelov. Na obr. Obrázok 19 predstavuje výsledky regresnej analýzy, ktorá testuje príspevok premennej stravy k regresnej rovnici.

Ryža. 19. Porovnanie dvoch modelov testovaním rozdielov v ich výsledkoch

Výsledky funkcie LINEST() (rozsah H2:K6) súvisia s tým, čo nazývam úplný model, ktorý regresuje premennú LDL na premenné Strava, Vek a HDL. Rozsah H9:J13 predstavuje výpočty bez zohľadnenia prediktorovej premennej Strava. Tomu hovorím limitovaný model. V úplnom modeli bolo 49,2 % rozptylu v závislej premennej LDL vysvetlených prediktorovými premennými. V obmedzenom modeli je len 30,8 % LDL vysvetlených premennými Age a HDL. Strata v R 2 v dôsledku vylúčenia premennej stravy z modelu je 0,183. V rozsahu G15:L17 sa robia výpočty, ktoré ukazujú, že existuje len pravdepodobnosť 0,0288, že vplyv premennej Strava je náhodný. Vo zvyšných 97,1 % má strava vplyv na LDL.

Kapitola 6: Predpoklady a upozornenia pre regresnú analýzu

Pojem „predpoklad“ nie je dostatočne striktne definovaný a spôsob jeho použitia naznačuje, že ak predpoklad nie je splnený, výsledky celej analýzy sú prinajmenšom sporné alebo možno neplatné. V skutočnosti to tak nie je, aj keď určite existujú prípady, keď porušenie predpokladu zásadne zmení obraz. Základné predpoklady: a) rezíduá premennej Y sú normálne rozdelené v ktoromkoľvek bode X pozdĺž regresnej priamky; b) hodnoty Y sú lineárne závislé od hodnôt X; c) disperzia zvyškov je približne rovnaká v každom bode X; d) medzi zvyškami nie je žiadna závislosť.

Ak predpoklady nehrajú významnú úlohu, štatistici tvrdia, že analýza je odolná voči porušeniu predpokladu. Najmä, keď použijete regresiu na testovanie rozdielov medzi priemermi skupín, predpoklad, že hodnoty Y – a teda aj rezíduá – sú normálne rozdelené, nehrá významnú úlohu: testy sú odolné voči porušeniu predpokladu normality. Je dôležité analyzovať údaje pomocou grafov. Napríklad zahrnuté v doplnku Analýza dát nástroj Regresia.

Ak údaje nespĺňajú predpoklady lineárnej regresie, máte k dispozícii iné prístupy ako lineárna regresia. Jednou z nich je logistická regresia (obr. 20). V blízkosti hornej a dolnej hranice prediktorovej premennej vytvára lineárna regresia nerealistické predpovede.

Ryža. 20. Logistická regresia

Na obr. Obrázok 6.8 zobrazuje výsledky dvoch metód analýzy údajov zameraných na skúmanie vzťahu medzi ročným príjmom a pravdepodobnosťou kúpy domu. Je zrejmé, že pravdepodobnosť nákupu sa bude zvyšovať s rastúcim príjmom. Grafy uľahčujú zistenie rozdielov medzi výsledkami, ktoré lineárna regresia predpovedá pravdepodobnosť kúpy domu, a výsledkami, ktoré môžete získať iným prístupom.

V jazyku štatistika sa odmietnutie nulovej hypotézy, aj keď je v skutočnosti pravdivá, nazýva chyba I. typu.

V doplnku Analýza dát ponúka pohodlný nástroj na generovanie náhodných čísel, ktorý umožňuje používateľovi špecifikovať požadovaný tvar rozdelenia (napríklad Normálne, Binomické alebo Poissonovo), ako aj strednú hodnotu a smerodajnú odchýlku.

Rozdiely medzi funkciami rodiny STUDENT.DIST(). Počnúc Excelom 2010 sú k dispozícii tri rôzne formy funkcie, ktoré vracajú podiel rozdelenia vľavo a/alebo vpravo od danej hodnoty t-testu. Funkcia STUDENT.DIST() vráti časť plochy pod distribučnou krivkou naľavo od zadanej hodnoty t-testu. Povedzme, že máte 36 pozorovaní, takže počet stupňov voľnosti pre analýzu je 34 a hodnota t-testu = 1,69. V tomto prípade vzorec

STUDENT.DIST(+1,69;34;TRUE)

vráti hodnotu 0,05 alebo 5 % (obrázok 21). Tretí argument funkcie STUDENT.DIST() môže byť TRUE alebo FALSE. Ak je nastavené na hodnotu TRUE, funkcia vráti kumulatívnu plochu pod krivkou naľavo od špecifikovaného t-testu, vyjadrenú ako podiel. Ak je FALSE, funkcia vráti relatívnu výšku krivky v bode zodpovedajúcom t-testu. Ostatné verzie funkcie STUDENT.DIST() - STUDENT.DIST.PH() a STUDENT.DIST.2X() - berú ako argumenty iba hodnotu t-testu a počet stupňov voľnosti a nevyžadujú zadanie tretieho argument.

Ryža. 21. Tmavšie tieňovaná oblasť na ľavom konci distribúcie zodpovedá podielu oblasti pod krivkou naľavo od veľkej pozitívnej hodnoty t-testu

Na určenie oblasti napravo od t-testu použite jeden zo vzorcov:

1 — STIODENT.DIST (1, 69; 34; TRUE)

STUDENT.DIST.PH(1,69;34)

Celá plocha pod krivkou musí byť 100 %, takže odpočítaním od 1 zlomku plochy naľavo od hodnoty t-testu, ktorú funkcia vráti, dostane zlomok plochy napravo od hodnoty t-testu. Možno zistíte, že je vhodnejšie priamo získať plošný zlomok, o ktorý máte záujem, pomocou funkcie STUDENT.DIST.PH(), kde PH znamená pravý koniec rozdelenia (obr. 22).

Ryža. 22. 5% alfa oblasť pre smerový test

Použitie funkcií STUDENT.DIST() alebo STUDENT.DIST.PH() znamená, že ste si vybrali smerovú pracovnú hypotézu. Smerová pracovná hypotéza v kombinácii s nastavením hodnoty alfa na 5 % znamená, že všetkých 5 % umiestnite na pravý koniec rozdelenia. Nulovú hypotézu budete musieť zamietnuť iba vtedy, ak pravdepodobnosť získanej hodnoty t-testu je 5 % alebo menej. Smerové hypotézy vo všeobecnosti vedú k citlivejším štatistickým testom (táto väčšia citlivosť sa nazýva aj väčšia štatistická sila).

V neriadenom teste zostáva hodnota alfa na rovnakej úrovni 5 %, ale rozdelenie bude iné. Pretože musíte počítať s dvoma výsledkami, pravdepodobnosť falošnej pozitivity musí byť rozdelená medzi dva konce distribúcie. Je všeobecne akceptované rozdeliť túto pravdepodobnosť rovnomerne (obr. 23).

Pomocou rovnakej získanej hodnoty t-testu a rovnakého počtu stupňov voľnosti ako v predchádzajúcom príklade použite vzorec

STUDENT.DIST.2Х(1,69;34)

Ak má funkcia STUDENT.DIST.2X() ako prvý argument zápornú hodnotu t-testu, bez akéhokoľvek konkrétneho dôvodu vráti kód chyby #NUM!.

Ak vzorky obsahujú rôzne množstvá údajov, použite dvojvzorkový t-test s rôznymi odchýlkami, ktorý je súčasťou balenia Analýza dát.

Kapitola 7: Použitie regresie na testovanie rozdielov medzi skupinovými priemermi

Premenné, ktoré sa predtým vyskytovali pod názvom prediktorové premenné, budeme v tejto kapitole nazývať výsledné premenné a namiesto pojmu prediktorové premenné budeme používať termín faktorové premenné.

Najjednoduchší prístup ku kódovaniu nominálnej premennej je fiktívne kódovanie(obr. 24).

Ryža. 24. Regresná analýza založená na fiktívnom kódovaní

Pri použití fiktívneho kódovania akéhokoľvek druhu by sa mali dodržiavať nasledujúce pravidlá:

• Počet stĺpcov vyhradených pre nové údaje sa musí rovnať počtu úrovní faktorov mínus
• Každý vektor predstavuje jednu úroveň faktora.
• Subjekty v jednej z úrovní, ktorá je často kontrolnou skupinou, sú vo všetkých vektoroch kódované 0.

Vzorec v bunkách F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) vráti štatistiku regresie. Pre porovnanie na obr. Obrázok 24 zobrazuje výsledky tradičnej analýzy ANOVA vrátenej nástrojom. Jednosmerná ANOVA doplnky Analýza dát.

Kódovanie efektov. V inom type kódovania tzv kódovanie efektov, Priemer každej skupiny sa porovnáva s priemerom priemeru skupiny. Tento aspekt kódovania efektov je spôsobený použitím -1 namiesto 0 ako kódu pre skupinu, ktorá prijíma rovnaký kód vo všetkých kódových vektoroch (obrázok 25).

Ryža. 25. Kódovanie efektov

Keď sa použije fiktívne kódovanie, konštantná hodnota vrátená funkciou LINREGRESE() je priemerom skupiny, ktorej sú priradené nulové kódy vo všetkých vektoroch (zvyčajne referenčná skupina). V prípade kódovania efektov sa konštanta rovná celkovému priemeru (bunka J2).

Všeobecný lineárny model je užitočný spôsob, ako konceptualizovať zložky hodnoty výslednej premennej:

Y ij = μ + α j + ε ij

Použitie gréckych písmen v tomto vzorci namiesto latinských písmen zdôrazňuje skutočnosť, že sa vzťahuje na populáciu, z ktorej sa odoberajú vzorky, ale môže byť prepísané tak, aby označovalo, že sa týka vzoriek odobratých z danej populácie:

Y ij = Y̅ + a j + e ij

Myšlienka je, že každé pozorovanie Y ij možno považovať za súčet nasledujúcich troch zložiek: veľký priemer, μ; účinok liečby j a j; hodnota e ij, ktorá predstavuje odchýlku jednotlivého kvantitatívneho ukazovateľa Y ij od kombinovanej hodnoty všeobecného priemeru a efektu j-tej liečby (obr. 26). Cieľom regresnej rovnice je minimalizovať súčet druhých mocnín rezíduí.

Ryža. 26. Pozorovania rozložené na zložky všeobecného lineárneho modelu

Faktorová analýza. Ak sa študuje vzťah medzi výslednou premennou a dvoma alebo viacerými faktormi súčasne, potom v tomto prípade hovoríme o použití faktorovej analýzy. Pridanie jedného alebo viacerých faktorov k jednosmernej ANOVA môže zvýšiť štatistickú silu. Pri jednosmernej analýze rozptylu je rozptyl vo výslednej premennej, ktorý nemožno pripísať faktoru, zahrnutý do štvorca reziduálneho priemeru. Môže sa však stať, že táto odchýlka súvisí s iným faktorom. Potom môže byť táto odchýlka odstránená zo strednej štvorcovej chyby, ktorej zníženie vedie k zvýšeniu hodnôt F-testu, a teda k zvýšeniu štatistickej sily testu. Nadstavba Analýza dát obsahuje nástroj, ktorý spracováva dva faktory súčasne (obr. 27).

Ryža. 27. Nástroj Obojsmerná analýza rozptylu s opakovaniami analytického balíka

Nástroj ANOVA použitý na tomto obrázku je užitočný, pretože vracia priemer a rozptyl výslednej premennej, ako aj hodnotu počítadla pre každú skupinu zahrnutú do návrhu. V tabulke Analýza rozptylu zobrazuje dva parametre, ktoré sa nenachádzajú vo výstupe jednofaktorovej verzie nástroja ANOVA. Venujte pozornosť zdrojom variácií Ukážka A Stĺpce v riadkoch 27 a 28. Zdroj variácií Stĺpce odkazuje na pohlavie. Zdroj variácií Ukážka označuje akúkoľvek premennú, ktorej hodnoty zaberajú rôzne riadky. Na obr. 27 hodnôt pre skupinu KursLech1 je v riadkoch 2-6, skupina KursLech2 je v riadkoch 7-11 a skupina KursLechZ je v riadkoch 12-16.

Hlavným bodom je, že oba faktory, pohlavie (označenie Stĺpce v bunke E28) a liečba (označenie Vzorka v bunke E27), sú zahrnuté v tabuľke ANOVA ako zdroje variácií. Prostriedky pre mužov sú odlišné od prostriedkov pre ženy, čo vytvára zdroj variácií. Prostriedky pre tieto tri ošetrenia sa tiež líšia, čo poskytuje ďalší zdroj variácií. Existuje aj tretí zdroj, Interakcia, ktorý odkazuje na kombinovaný účinok premenných Pohlavie a Liečba.

Kapitola 8. Analýza kovariancie

Analýza kovariancie alebo ANCOVA (Analýza kovariancie) znižuje skreslenie a zvyšuje štatistickú silu. Dovoľte mi pripomenúť, že jedným zo spôsobov, ako posúdiť spoľahlivosť regresnej rovnice, sú F-testy:

F = MS regresia/MS zvyšok

kde MS (stredný štvorec) je stredný štvorec a regresné a reziduálne indexy označujú regresnú a reziduálnu zložku. MS Reziduum sa vypočíta pomocou vzorca:

MS Residual = SS Residual / df Residual

kde SS (súčet štvorcov) je súčet štvorcov a df je počet stupňov voľnosti. Keď do regresnej rovnice pridáte kovarianciu, určitá časť celkového súčtu štvorcov nie je zahrnutá v SS Residual, ale v SS Regresii. To vedie k zníženiu rezíduí SS l, a tým aj zvyškov MS. Čím menší je MS Residual, tým väčší je F-test a tým je pravdepodobnejšie, že zamietnete nulovú hypotézu o žiadnom rozdiele medzi priemermi. V dôsledku toho prerozdeľujete variabilitu výslednej premennej. V ANOVA, keď sa neberie do úvahy kovariancia, sa variabilita stáva chybou. Ale v ANCOVA je časť variability, ktorá sa predtým pripisovala chybovému termínu, priradená kovariátu a stáva sa súčasťou SS regresie.

Uvažujme o príklade, v ktorom sa rovnaký súbor údajov analyzuje najskôr pomocou ANOVA a potom pomocou ANCOVA (obrázok 28).

Ryža. 28. Analýza ANOVA ukazuje, že výsledky získané z regresnej rovnice sú nespoľahlivé

Štúdia porovnáva relatívne účinky fyzického cvičenia, ktoré rozvíja svalovú silu, a kognitívneho cvičenia (lúštenie krížoviek), ktoré stimuluje mozgovú aktivitu. Subjekty boli náhodne rozdelené do dvoch skupín tak, že obe skupiny boli na začiatku experimentu vystavené rovnakým podmienkam. Po troch mesiacoch sa merala kognitívna výkonnosť subjektov. Výsledky týchto meraní sú uvedené v stĺpci B.

Rozsah A2:C21 obsahuje zdrojové údaje odovzdané funkcii LINREGRESE() na vykonanie analýzy pomocou kódovania efektov. Výsledky funkcie LINREGRESE() sú uvedené v rozsahu E2:F6, kde bunka E2 zobrazuje regresný koeficient spojený s vektorom dopadu. Bunka E8 obsahuje t-test = 0,93 a bunka E9 testuje spoľahlivosť tohto t-testu. Hodnota obsiahnutá v bunke E9 naznačuje, že pravdepodobnosť rozdielu medzi priemermi skupín pozorovanými v tomto experimente je 36 %, ak sú priemery skupiny v populácii rovnaké. Málokto považuje tento výsledok za štatisticky významný.

Na obr. Obrázok 29 ukazuje, čo sa stane, keď do analýzy pridáte kovariát. V tomto prípade som do súboru údajov pridal vek každého subjektu. Koeficient determinácie R2 pre regresnú rovnicu, ktorá používa kovariát, je 0,80 (bunka F4). Hodnota R2 v rozsahu F15:G19, v ktorom som replikoval výsledky ANOVA získané bez kovariátu, je len 0,05 (bunka F17). Preto regresná rovnica, ktorá zahŕňa kovariát, predpovedá hodnoty pre premennú kognitívneho skóre oveľa presnejšie ako použitie samotného vektora dopadu. V prípade ANCOVA je pravdepodobnosť náhodného získania hodnoty F testu zobrazenej v bunke F5 menšia ako 0,01 %.

Ryža. 29. ANCOVA prináša úplne iný obraz

Je známy tým, že je užitočný v rôznych oblastiach činnosti, vrátane takej disciplíny, ako je ekonometria, kde sa tento softvérový nástroj používa v práci. V zásade sa všetky akcie praktických a laboratórnych tried vykonávajú v programe Excel, čo značne uľahčuje prácu tým, že poskytuje podrobné vysvetlenia určitých akcií. Jeden z analytických nástrojov „Regresia“ sa teda používa na výber grafu pre súbor pozorovaní pomocou metódy najmenších štvorcov. Pozrime sa, čo je tento programový nástroj a aké sú jeho výhody pre používateľov. Nižšie nájdete aj stručný, ale jasný návod na zostavenie regresného modelu.

Hlavné úlohy a typy regresie

Regresia predstavuje vzťah medzi danými premennými, čím umožňuje predpovedať budúce správanie týchto premenných. Premenné sú rôzne periodické javy vrátane ľudského správania. Tento typ analýzy Excel sa používa na analýzu vplyvu hodnôt jednej alebo viacerých premenných na konkrétnu závislú premennú. Napríklad predaj v obchode ovplyvňuje viacero faktorov, medzi ktoré patrí sortiment, ceny a umiestnenie predajne. Vďaka regresii v Exceli môžete určiť mieru vplyvu každého z týchto faktorov na základe výsledkov existujúcich predajov a získané údaje potom použiť na prognózu predaja na ďalší mesiac alebo pre iný obchod nachádzajúci sa v blízkosti.

Typicky sa regresia prezentuje ako jednoduchá rovnica, ktorá odhaľuje vzťahy a silu vzťahov medzi dvoma skupinami premenných, kde jedna skupina je závislá alebo endogénna a druhá je nezávislá alebo exogénna. Ak existuje skupina vzájomne súvisiacich ukazovateľov, závislá premenná Y je určená na základe logiky uvažovania a zvyšok pôsobí ako nezávislé X premenné.

Hlavné úlohy vytvárania regresného modelu sú nasledovné:

1. Výber významných nezávislých premenných (X1, X2, ..., Xk).
2. Výber typu funkcie.
3. Konštrukcia odhadov pre koeficienty.
4. Konštrukcia intervalov spoľahlivosti a regresných funkcií.
5. Kontrola významnosti vypočítaných odhadov a zostrojenej regresnej rovnice.

Existuje niekoľko typov regresnej analýzy:

• párové (1 závislá a 1 nezávislá premenná);
• násobok (niekoľko nezávislých premenných).

Existujú dva typy regresných rovníc:

1. Lineárny, ilustrujúci striktný lineárny vzťah medzi premennými.
2. Nelineárne – rovnice, ktoré môžu zahŕňať mocniny, zlomky a goniometrické funkcie.

Návod na zostavenie modelu

Ak chcete vykonať danú konštrukciu v programe Excel, musíte postupovať podľa pokynov:

Na ďalší výpočet použite funkciu „Linear()“, ktorá špecifikuje hodnoty Y, hodnoty X, Const a Statistics. Potom pomocou funkcie "Trend" určte množinu bodov na regresnej priamke - Y Values, X Values, New Values, Const. Pomocou daných parametrov vypočítajte neznámu hodnotu koeficientov na základe daných podmienok úlohy.

KORELAČNÁ A REGRESNÁ ANALÝZA VPANI EXCEL

1. Vytvorte zdrojový dátový súbor v MS Excel (napríklad tabuľka 2)

2. Konštrukcia korelačného poľa

Ak chcete vytvoriť korelačné pole v príkazovom riadku, vyberte ponuku Vložiť/Diagram. V zobrazenom dialógovom okne vyberte typ grafu: Spot; vyhliadka: Bodový diagram, čo vám umožní porovnávať dvojice hodnôt (obr. 22).

Obrázok 22 – Výber typu grafu

Obrázok 23 – Zobrazenie okna pri výbere rozsahu a riadkov
Obrázok 25 – Pohľad z okna, krok 4

2. V kontextovej ponuke vyberte príkaz Pridajte trendovú čiaru.

3. V zobrazenom dialógovom okne vyberte typ grafu (v našom príklade lineárny) a parametre rovnice, ako je znázornené na obrázku 26.

Kliknite na tlačidlo OK. Výsledok je uvedený na obrázku 27.

Obrázok 27 – Korelačné pole závislosti produktivity práce od pomeru kapitálu a práce

Podobne zostrojíme korelačné pole pre závislosť produktivity práce od zmenového pomeru zariadení. (Obrázok 28).

Obrázok 28 – Korelačné pole produktivity práce

o výmennom kurze zariadenia

3. Konštrukcia korelačnej matice.

Na vytvorenie korelačnej matice v ponuke servis vybrať Analýza dát.

Použitie nástroja na analýzu údajov Regresia Okrem výsledkov regresnej štatistiky, analýzy rozptylu a intervalov spoľahlivosti môžete získať rezíduá a grafy prispôsobenia regresnej priamky, rezíduí a normálnej pravdepodobnosti. Ak to chcete urobiť, musíte skontrolovať prístup k analytickému balíku. V hlavnom menu vyberte Služba/Doplnky. Začiarknite políčko Analytický balík(Obrázok 29)

Obrázok 30 – Dialógové okno Analýza dát

Po kliknutí na tlačidlo OK v zobrazenom dialógovom okne zadajte vstupný interval (v našom príklade A2:D26), zoskupenie (v našom prípade podľa stĺpcov) a výstupné parametre, ako je znázornené na obrázku 31.

Obrázok 31 – Dialógové okno Korelácia

Výsledky výpočtu sú uvedené v tabuľke 4.

Tabuľka 4 – Korelačná matica

	Stĺpec 1	2. stĺpec	Stĺpec 3
Stĺpec 1
2. stĺpec
Stĺpec 3

JEDNOFAKTOROVÁ REGRESNÁ ANALÝZA

POUŽÍVANIE NÁSTROJA REGRESIE

Vykonať regresnú analýzu závislosti produktivity práce od pomeru kapitálu a práce v ponuke servis vybrať Analýza dát a špecifikujte analytický nástroj Regresia(Obrázok 32).

Obrázok 33 – Dialógové okno Regresia