MS Excel vám umožňuje zostaviť lineárnu regresnú rovnicu najviac práca sa musí vykonať veľmi rýchlo. Je dôležité pochopiť, ako interpretovať získané výsledky. Ak chcete zostaviť regresný model, vyberte položku Služba \ Analýza údajov \ Regresia (v Exceli 2007 sa tento režim nachádza v časti Údaje / Analýza údajov / Regresia). Potom skopírujte získané výsledky do bloku na analýzu.

Počiatočné údaje:

Výsledky analýzy

Zahrnúť do správy
Výpočet parametrov regresnej rovnice
Teoretický materiál
Regresná rovnica v štandardnej mierke
Viacnásobný korelačný koeficient (viacnásobný korelačný index)
Parciálne koeficienty pružnosti
Porovnávacie hodnotenie vplyvu analyzovaných faktorov na efektívny ukazovateľ (d - koeficienty samostatného určenia)

Kontrola kvality zostrojenej regresnej rovnice
Význam regresných koeficientov b i (t-štatistika. Studentov test)
Význam rovnice ako celku (F-štatistika. Fisherovo kritérium). Koeficient determinácie
Súkromné ​​F-testy

Úroveň významnosti 0.005 0.01 0.025 0.05 0.1 0.25 0.4

28 okt

Dobré popoludnie, milí čitatelia blogu! Dnes budeme hovoriť o nelineárnych regresiách. Riešenie lineárnych regresií nájdete na ODKAZE.

Táto metóda používa sa najmä v ekonomickom modelovaní a prognózovaní. Jeho účelom je sledovať a identifikovať vzťah medzi týmito dvoma ukazovateľmi.

Hlavné typy nelineárnych regresií sú:

• polynóm (kvadratický, kubický);
• hyperbolický;
• mocenské právo;
• orientačné;
• logaritmický.

Môžu sa použiť aj rôzne kombinácie. Napríklad pre analytikov časových radov v bankovníctve, poisťovníctve a demografických štúdiách sa používa Gompzerova krivka, čo je druh logaritmickej regresie.

Pri prognózovaní pomocou nelineárnych regresií ide predovšetkým o zistenie korelačného koeficientu, ktorý nám ukáže, či medzi dvoma parametrami existuje úzky vzťah alebo nie. Spravidla, ak je korelačný koeficient blízky 1, potom existuje spojenie a predpoveď bude celkom presná. Ďalší dôležitý prvok nelineárna regresia je priemer relatívna chyba (A ), ak je v intervale<8…10%, значит модель достаточно точна.

Týmto možno dokončíme teoretický blok a prejdeme k praktickým výpočtom.

Máme tabuľku predajov áut za interval 15 rokov (označíme X), počet krokov merania bude argument n, za tieto obdobia je aj tržba (označíme Y), treba predpovedať, aké budú výnosy v budúcnosti. Zostavme si nasledujúcu tabuľku:

Pre výskum potrebujeme vyriešiť rovnicu (závislosť Y na X): y = ax 2 + bx + c + e. Toto je párová kvadratická regresia. Aplikujme v tomto prípade metódu najmenších štvorcov, aby sme zistili neznáme argumenty - a, b, c. Povedie to k systému algebraických rovníc v tvare:

Na vyriešenie tohto systému použijeme napríklad Cramerovu metódu. Vidíme, že sumy zahrnuté v systéme sú koeficienty s neznámymi. Pre ich výpočet pridajte do tabuľky niekoľko stĺpcov (D, E, F, G, H) a podpíšte ich podľa významu výpočtov - v stĺpci D odmocníme x, v E v kocke, v F v 4. mocnina, v G vynásobíme exponenty x a y, v H odmocninu x a vynásobíme y.

Dostanete tabuľku formulára vyplnenú potrebnými na riešenie rovnice.

Vytvorme matricu A systém pozostávajúci z koeficientov s neznámymi na ľavej strane rovníc. Umiestnite ho do bunky A22 a nazvite ho „ A =". Postupujeme podľa sústavy rovníc, ktoré sme si zvolili na riešenie regresie.

To znamená, že do bunky B21 musíme umiestniť súčet stĺpca, kde bol exponent X zvýšený na štvrtú mocninu - F17. Pozrime sa len na bunku - "= F17". Ďalej potrebujeme súčet stĺpca, kde bolo X zvýšené na kocku - E17, potom ideme striktne podľa systému. Preto budeme musieť vyplniť celú maticu.

V súlade s Cramerovým algoritmom napíšeme maticu A1, podobnú A, do ktorej by sa namiesto prvkov prvého stĺpca mali umiestniť prvky pravých strán rovníc systému. To znamená, že súčet stĺpca X na druhú krát Y, súčet stĺpca XY a súčet stĺpca Y.

Potrebujeme tiež dve ďalšie matice – nazvime ich A2 a A3, v ktorých druhý a tretí stĺpec budú pozostávať z koeficientov pravých strán rovníc. Obrázok bude takýto.

Podľa zvoleného algoritmu budeme musieť vypočítať hodnoty determinantov (determinanty, D) výsledných matíc. Použime vzorec MOPRED. Výsledky umiestnite do buniek J21: K24.

Výpočet koeficientov rovnice podľa Kramera sa uskutoční v bunkách oproti zodpovedajúcim determinantom podľa vzorca: a(v bunke M22) - "= K22 / K21"; b(v bunke M23) - "= K23 / K21"; s(v bunke M24) - "= K24 / K21".

Získame našu požadovanú párovú kvadratickú regresnú rovnicu:

y = -0,074 x 2 + 2,151 x + 6,523

Odhadnime tesnosť lineárneho spojenia pomocou korelačného indexu.

Ak chcete vypočítať, pridajte do tabuľky extra stĺpec J (nazvime to y *). Výpočet bude nasledovný (podľa nami získanej regresnej rovnice) - "= $ M $ 22 * ​​​​B2 * B2 + $ M $ 23 * B2 + $ M $ 24". Umiestnite ho do bunky J2. Zostáva natiahnuť značku automatického dopĺňania nadol do bunky J16.

Na výpočet súčtu (priemer Y-Y) 2 pridajte stĺpce K a L do tabuľky so zodpovedajúcimi vzorcami. Priemer pre stĺpec Y sa vypočíta pomocou funkcie AVERAGE.

Do bunky K25 umiestnite vzorec na výpočet korelačného indexu - "= ROOT (1- (K17 / L17)".

Vidíme, že hodnota 0,959 je veľmi blízka 1, čo znamená, že medzi predajom a rokmi existuje úzky nelineárny vzťah.

Zostáva zhodnotiť kvalitu prispôsobenia získanej kvadratickej regresnej rovnice (index determinácie). Vypočíta sa pomocou vzorca druhej mocniny indexu korelácie. To znamená, že vzorec v bunke K26 bude veľmi jednoduchý - "= K25 * K25".

Faktor 0,920 je blízko 1, čo naznačuje vysokú kvalitu prispôsobenia.

Posledným krokom je výpočet relatívnej chyby. Pridajte stĺpec a pridajte tam vzorec: „= ABS ((C2-J2) / C2), ABS - modul, absolútna hodnota. Potiahnite značku nadol a v bunke M18 zobrazte priemernú hodnotu (AVERAGE), priraďte bunkám percentuálny formát. Získaný výsledok - 7,79 % je v rámci prípustných hodnôt chyby<8…10%. Значит вычисления достаточно точны.

V prípade potreby môžeme na základe získaných hodnôt zostaviť graf.

Príklad súboru je priložený - LINK!

Kategórie:/ / zo dňa 28.10.2017

Regresná analýza v programe Microsoft Excel - najkomplexnejšie návody na používanie programu MS Excel na riešenie problémov regresná analýza v oblasti business intelligence. Konrad Karlberg vysvetľuje teoretickú problematiku prístupným spôsobom, ktorého znalosť vám pomôže vyhnúť sa mnohým chybám pri vykonávaní regresnej analýzy na vlastnú päsť, ako aj pri hodnotení 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 rozoberá špecifiká a kontroverzie týkajúce sa 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 rôznych oblastiach, od lekárskeho výskumu až po finančnú analýzu.

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

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

Kapitola 1. Odhad variability údajov

Štatistici majú k dispozícii mnohé ukazovatele variácie (variability). Jedným z nich je súčet druhých mocnín odchýlok jednotlivých hodnôt od priemeru. Excel na to používa funkciu SQUARE (). Ale rozptyl sa používa častejšie. Rozptyl je stredná druhá mocnina 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 dimenzií).

Excel poskytuje dve funkcie, ktoré vrátia odchýlku: VAR.G () a VAR.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 VAR.In (). Predpokladá sa, že existujú dodatočné hodnoty, ktorých rozptyl môžete tiež odhadnúť.

Ak sa veličina, ako je priemer alebo korelačný koeficient, vypočítava na základe populácie, potom sa nazýva parameter. Podobná hodnota vypočítaná na základe vzorky sa nazýva štatistika. Počítanie odchýlok od priemeru v tomto súbore dostanete súčet druhých mocnín odchýlok menšej veľkosti, ako keby ste ich počítali z 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 ani jedna vzorka s veľkosťou menšou ako veľkosť bežnej populácie, v porovnaní s ktorou by ste si mohli byť istí, že hodnota štatistiky sa zhoduje s hodnotou parametra.

Povedzme, že máte súbor 100 hodnôt rastu, ktorých priemer sa líši od priemeru populácie, akokoľvek malý môže byť rozdiel. Výpočtom rozptylu pre vzorku získate určitú hodnotu, povedzme 4. Táto hodnota je menšia ako ktorákoľvek iná, ktorú možno získať výpočtom odchýlky každej zo 100 hodnôt rastu vo vzťahu k akejkoľvek hodnote inej ako je vzorka priemer, vrátane a relatívne k skutočnému priemeru všeobecnej populácie. Preto sa vypočítaný rozptyl bude v menšej miere líšiť od rozptylu, ktorý by ste dostali, keby ste nejakým spôsobom poznali a nepoužili výberový priemer, ale parameter všeobecnej populácie.

Priemerný súčet štvorcov vzorky dáva nižší odhad rozptylu populácie. Takto vypočítaný rozptyl je tzv premiestnený hodnotenie. Ukazuje sa, že na odstránenie skreslenia a získanie nezaujatého odhadu stačí vydeliť súčet druhých mocnín odchýlok nie n, kde n je veľkosť vzorky a ďalej n - 1.

Veľkosť n - 1 nazývaný 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ď odčítanie určitého čísla od veľkosti vzorky, alebo sčítanie počtu kategórií, do ktorých pozorovania patria.

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

• Vo funkcii VARP.G () je súčet štvorcov delený počtom pozorovaní, a preto predstavuje skreslený odhad rozptylu, skutočný priemer.
• Vo funkcii VAR.Vo () 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 extrahovaná.

Smerodajná odchýlka (rus. smerodajná odchýlka, SD) je druhá odmocnina rozptylu:

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

Často je potrebné vypočítať štandardnú odchýlku po určitej manipulácii s údajmi. 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, štandardnej chyby priemeru.

Predpokladajme, že zbierate údaje o výške od 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. Získaných 50 priemerných hodnôt 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 1250 základných individuálnych hodnôt (údaje o výške 25 mužov v každom z 50 štátov) s rozdelením priemerných hodnôt 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 základných pozorovaní; n- počet pozorovaní vo vzorke.

Ryža. 1. Rozdiely v priemerných hodnotách medzi jednotlivými stavmi sú výrazne menšie ako rozdiely v jednotlivých pozorovaniach

V štatistike existuje dohoda o používaní gréckych a latinských písmen na označenie štatistických veličín. Je zvykom označovať parametre všeobecnej populácie gréckymi písmenami a vzorové štatistiky latinkou. Preto, keď hovoríme o smerodajnej odchýlke všeobecnej 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 nezhodujú. Priemer populácie sa označuje gréckym písmenom μ. Symbol X̅ sa však tradične používa na vyjadrenie priemeru vzorky.

z-skóre vyjadruje polohu pozorovania v rozdelení v jednotkách smerodajnej odchýlky. Napríklad z = 1,5 znamená, že pozorovanie je 1,5 štandardnej odchýlky od priemeru v smere k vyšším hodnotám. Termín z-skóre slúžia na individuálne hodnotenia, t.j. pre rozmery priradené jednotlivým položkám vo vzorke. Pre takýchto štatistikov (napríklad štátny priemer) použite termín z-hodnota:

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

kde σ je štandardná chyba všeobecnej populácie (individuálne merania), n Je veľkosť vzorky.

Povedzme, že ste inštruktorom v golfovom klube. Dlho ste mali možnosť merať dosah a viete, že priemer je 205 yardov a štandardná odchýlka je 36 yardov. Dostali ste ponuku nového klubu s tvrdením, že zvýši váš dosah o 10 yardov. Požiadate každého z ďalších 81 návštevníkov klubu, aby vyskúšali skúšobný úder s novou palicou a zaznamenali svoj rozsah. Ukázalo sa, že priemerný dosah nového klubu bol 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 testovaní vo väčšom meradle nový klub neukáže zvýšenie dosahu úderov nad existujúci dlhodobý priemer 205 yardov?

Môžeme to otestovať vygenerovaním hodnoty z. Štandardná chyba stred:

Potom je hodnota z:

Musíme nájsť pravdepodobnosť, že priemer vzorky bude 2,5σ od priemeru populácie. Ak je pravdepodobnosť malá, tak rozdiely nie sú spôsobené náhodnosťou, 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 (hodnota z; TRUE), kde NORM.ST.DIST () vráti oblasť pod normálnou krivkou naľavo od hodnoty z (obrázok 2) .

Ryža. 2. Funkcia NORM.S.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 oblasť 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 nie je známy priemer (μ) a štandardná odchýlka (σ) populácie, použije sa t-hodnota (pozri podrobnosti). Štruktúry hodnôt z a t sa líšia v tom, že na nájdenie hodnoty t sa používa štandardná odchýlka s získaná z výsledkov vzorky, a nie známa hodnota parametra populácie σ. Normálna krivka má jeden tvar a tvar rozloženia t-hodnôt sa mení v závislosti od počtu stupňov voľnosti df (z angl. stupne slobody) vzorky, ktorú predstavuje. Počet stupňov voľnosti vzorky je n - 1, kde n- veľkosť vzorky (obr. 3).

Ryža. 3. Tvar t-rozdelení vznikajúcich 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 Študentovo rozdelenie t: STUDENT.DIST () vráti oblasť pod krivkou naľavo od danej t-hodnoty a STUDENT.DIST.PX () vráti oblasť pod krivka doprava.

Kapitola 2. Korelácia

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

kde S x a S y- smerodajné odchýlky premenných NS a Y, S xy- kovariancia:

V tomto vzorci sa kovariancia delí štandardnými odchýlkami premenných NS a Yčím sa odstránia z kovariancie 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 STDEV (), DISP () alebo COVARIATION (). Hoci korelačný koeficient pre vzorku je skreslený odhad, dôvod skreslenia je iný ako v prípade rozptylu alebo štandardnej odchýlky.

V závislosti od hodnoty všeobecného korelačného koeficientu (často označovaného gréckym písm ρ ), korelačný koeficient r poskytuje skreslený odhad, pričom účinok skreslenia sa zvyšuje s klesajúcou veľkosťou 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 lineárne súvisiacimi premennými. Nelinearita a/alebo chyby v údajoch (odľahlé hodnoty) vedú k nesprávnemu výpočtu korelačného koeficientu. Na diagnostiku problémov s údajmi sa odporúča použiť bodové grafy. Je to jediný typ grafu v Exceli, ktorý považuje horizontálnu aj vertikálnu os za os hodnôt. Čiarový graf na druhej strane definuje jeden zo stĺpcov ako os kategórií, čo skresľuje obraz údajov (obr. 4).

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

Pozorovania použité na vykreslenie čiarového grafu sú rovnako vzdialené pozdĺž horizontálnej osi. Značky pozdĺž tejto osi sú len štítky, nie číselné hodnoty.

Hoci korelácia často znamená príčinnú súvislosť, nemožno ju použiť ako dôkaz, že je. Š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, vložte plánované experimenty... Štatistiky sa používajú na zhrnutie informácií získaných v priebehu 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 sú dve premenné vo vzájomnom vzťahu, 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 predpokladaných hodnôt 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. Mali by ste to však poznať, pretože žiadna iná metóda nedokáže tak jasne preukázať vzťah medzi koreláciou a predikciou ako táto. Na obr. 5, v rozsahu B2: C12, je prezentovaná náhodná vzorka desiatich domov a uvedená podlahová plocha (v štvorcových stopách) každého domu a jeho predajná cena.

Ryža. 5. Predpokladané hodnoty 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 EZ obsahuje vzorec: = (B3- $ B $ 14) / $ B $ 15. Vypočítajte z-skóre prognózovanej ceny (F2: F12). Napríklad bunka F3 obsahuje vzorec: = EZ * $ B $ 18. Preveďte z-skóre na dolárové ceny (H2: H12). V bunke НЗ je vzorec: = F3 * $ C $ 15 + $ C $ 14.

Upozorňujeme, že predpokladaná hodnota má vždy tendenciu pohybovať sa smerom k priemeru rovnému 0. Čím bližšie k nule je korelačný koeficient, tým bližšie k nule je predpokladané 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á prebytku hodnoty nad priemerom, ktorý sa rovná dvom tretinám štandardnej odchýlky. Ak by sa korelačný koeficient rovnal 0,5, potom by predikovaná cena bola 1,0 * 0,5, t.j. 0,5. To zodpovedá prebytku hodnoty nad priemerom, ktorý sa rovná iba polovici štandardnej odchýlky. Vždy, keď sa hodnota korelačného koeficientu líši od ideálu, t.j. väčší ako -1,0 a menší ako 1,0, odhad predpovedanej premennej by mal byť bližšie k jej priemeru ako odhad predikčnej (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 sa nazýva trendová čiara) y =kx + b... Na určenie k slúži funkciu

= SLOPE (známe_y, známe_x)

Tu pri Je predpovedaná premenná a NS Je nezávislá premenná. Toto poradie premenných musíte prísne dodržiavať. Sklon regresie, korelačný koeficient, štandardné odchýlky premenných a kovariancia spolu úzko súvisia (obrázok 6). INTERCEPT () vráti hodnotu orezanú regresnou čiarou na zvislej osi:

= INTERCEPT (známe_y, známe_x)

Ryža. 6. Pomer 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 funkciám SLOPE () a INTERCEPT () ako argumentov musí byť rovnaký.

Regresná analýza používa ešte jednu dôležitým ukazovateľom- R 2 (R-štvorec), alebo koeficient determinácie. To určuje, ako sa vzťah medzi NS a pri... Excel má na to funkciu KVPIRSON (), ktorá berie presne tie isté argumenty ako funkcia CORREL ().

Dve premenné s nenulovým korelačným koeficientom medzi nimi vysvetľujú rozptyl alebo majú vysvetlený rozptyl. Vysvetlený rozptyl je zvyčajne vyjadrený v percentách. Takže R 2 = 0,81 znamená, že 81 % rozptylu (spreadu) dvoch premenných je vysvetlený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 ():

• preberá známe hodnoty, ktoré poskytujete NS a známe hodnoty pri;
• vypočíta sklon regresnej priamky a konštanty (segmentu);
• vráti predpovedané hodnoty pri určené použitím regresnej rovnice na známe hodnoty NS(obr. 7).

Funkcia TREND () je funkcia poľa (ak ste sa s takými 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í TILT () a INTERCEPT ()

Ak chcete zadať funkciu TREND () ako vzorec poľa v bunkách G3: G12, vyberte rozsah G3: G12, zadajte vzorec TREND (SZ: C12; OZ: 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ás Excel informuje, že daný vzorec sa interpretuje ako vzorec poľa. Nezadávajte samotné zátvorky: ak sa ich pokúsite zadať sami ako súčasť vzorca, Excel bude váš vstup interpretovať ako bežný textový reťazec.

Funkcia TREND () má ďalšie dva argumenty: new_x's a konšt... Prvý z nich vám umožňuje urobiť predpoveď do budúcnosti, zatiaľ čo druhý môže vynútiť regresnú čiaru cez počiatok (TRUE hovorí Excelu, aby použil vypočítanú konštantu, FALSE hovorí konštante = 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. V otvorenom kontextovom menu vyberte položku Pridať trendovú čiaru; vyberte možnosť Lineárne; v prípade potreby prejdite nadol po paneli a začiarknite políčko Konfigurovať križovatku; skontrolujte, či je priradené textové pole nastavené na 0,0.

Ak máte tri premenné a chcete určiť koreláciu medzi dvoma z nich, s vylúčením vplyvu tretej, môžete použiť čiastočná korelácia... Predpokladajme, že vás zaujíma vzťah medzi percentom obyvateľov mesta, ktorí vyštudovali 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. Je samozrejme veľmi ťažké nájsť ďalších 50 miest s presne rovnakou úrovňou blahobytu.

Použitím štatistických techník na elimináciu vplyvu blahobytu na finančnú podporu knižníc a dostupnosť vysokoškolského vzdelávania možno budete môcť presnejšie kvantifikovať vzťah medzi premennými, ktoré vás zaujímajú, konkrétne počtom kníh a počtom kníh. absolventov. Táto podmienená korelácia medzi dvoma premennými, keď sú hodnoty ostatný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 rCB . W- koeficient korelácie medzi premennými College a Books s vylúčeným vplyvom (pevnou hodnotou) premennej Bohatstvo; rCB- koeficient korelácie medzi premennými College a Books; rCW- koeficient korelácie medzi premennými College a Welfare; rBw je korelačný koeficient medzi premennými Books a Welfare.

Na druhej strane parciálnu koreláciu možno vypočítať na základe analýzy rezíduí, t.j. rozdiely medzi predpovedanými hodnotami a súvisiacimi skutočnými pozorovaniami (obe metódy sú znázornené 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). Štandardne 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 otváracieho okna možnostiExcel nájsť pole Kontrola, vyberte DoplnkyExcel, kliknite Ísť do... 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ý rozsah, začiarknite políčko Štítky v prvom riadku, zadajte $ B $ 16: $ E $ 19 ako výstupný interval.

Ďalšou možnosťou je definovať polosúkromnú koreláciu. Skúmate napríklad vplyv výšky a veku na hmotnosť. Takže máte dve prediktorové premenné, výšku a vek, a jednu predikčnú premennú, hmotnosť. Chcete eliminovať vplyv jednej prediktorovej premennej na inú, ale nie na predpovedanú premennú:

kde H je výška, W je hmotnosť, A je vek; v indexe semičiastočného korelačného koeficientu, okrúhle zátvorky, pomocou ktorej je naznačené, vplyv ktorej premennej sa eliminuje az ktorej konkrétnej premennej. V v tomto prípade zápis W (NA) znamená, že vplyv veku je odstránený z výšky, ale nie z hmotnosti.

Niekto by mohol nadobudnúť dojem, že diskutovaná téma nie je podstatná. Najdôležitejšie je predsa to, ako presne funguje všeobecná regresná rovnica, pričom 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 začnete uvažovať, či vôbec použiť premennú vo svojej viacnásobnej regresnej rovnici, problém sa stáva dôležitým. Môže ovplyvniť posúdenie správnosti výberu modelu na analýzu.

Kapitola 4. Funkcia LINREGRESE ().

LINEST () vráti 10 regresných štatistík. LINEST () je funkcia poľa. Ak ho chcete zadať, vyberte rozsah obsahujúci päť riadkov a dva stĺpce, zadajte vzorec a stlačte (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) stlačte

LINEST () vráti:

• regresný koeficient (alebo sklon, bunka D2);
• segment (alebo konštanta, bunka E3);
• štandardné chyby regresného koeficientu a konštánt (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 dostanete 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šia známka predpovedaná premenná je jej priemer. Dve a pol štandardnej chyby je pomerne veľké číslo 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.RF (t-test = 2,559; stupne 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 1 % zaokrúhlené. Hovorí nám to toto: ak je regresný koeficient pre populáciu 0 %, potom pravdepodobnosť získania vzorky 20 ľudí, pre ktorú je vypočítaná hodnota regresného koeficientu 2,092, je skromné ​​1 %.

F-test (bunka D5 na obrázku 9) vykonáva rovnakú funkciu vzhľadom na úplnú regresiu ako t-test vzhľadom na koeficient 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 vo všeobecnej populácii má hodnotu 0,0, čo naznačuje absenciu rozptylu vysvetleného prediktorom a predpovedanou premennou. . Ak existuje len 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úť niekoľko hodnôt, ktoré sú jednoduché mená vo forme ich zastupujú napríklad Muž a žena alebo Plaz, Obojživelník a Ryba číselný kód... Takéto premenné sa nazývajú nominálne.

Štatistika R 2 kvantifikuje podiel vysvetleného rozptylu.

Štandardná chyba odhadu. Na obr. 4.9 ukazuje 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 povedané, tieto rezíduá sa nazývajú chyby - odtiaľ sa nazýva štandardná chyba odhadu.

Ryža. 10. R 2 aj štandardná chyba odhadu vyjadrujú presnosť predpovedí získaných regresiou

Čím menšia je štandardná chyba odhadu, tým presnejšia je regresná rovnica a tým presnejšie bude akákoľvek predpoveď urobená rovnicou zodpovedať skutočnému pozorovaniu, ktoré očakávate. Š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 medziskupinového rozptylu k vnútroskupinovému rozptylu. Tento názov vymyslel štatistik George Snedecor na počesť Sira, ktorý na začiatku 20. storočia vyvinul analýzu rozptylu (ANOVA).

Koeficient determinácie R 2 vyjadruje podiel celkového súčtu štvorcov spojených s regresiou. Hodnota (1 - R 2) vyjadruje zlomok celkového súčtu štvorcov spojených s rezíduami - chybami predikcie. F-skóre možno získať pomocou funkcie LINEST (bunka F5 na obrázku 11), pomocou súčtu štvorcov (rozsah G10: J11), pomocou zlomkov rozptylu (rozsah G14: J15). Vzorce je možné preskúmať 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 dummy kódovanie (obr. 12). Pre kódovanie hodnôt je vhodné použiť hodnoty 0 a 1. Pravdepodobnosť F sa vypočíta pomocou funkcie:

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

Funkcia F.DIST.RT () tu vracia pravdepodobnosť získania kritéria F podľa centrálneho rozdelenia F (obrázok 13) pre dva súbory údajov so stupňami voľnosti uvedenými v bunkách I2 a I3, ktorých hodnota je rovnaká ako hodnota uvedená v bunke K2.

Ryža. 12. Regresná analýza s použitím falošných premenných

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

Kapitola 5. Viacnásobná regresia

Keď prejdete od 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 prediktorov do susedných stĺpcov, napríklad stĺpce A a B pre dva prediktory alebo A, B a C pre tri prediktory. 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)

Predpokladajme, že chcete študovať možné účinky veku a stravy na LDL, lipoproteín s nízkou hustotou, o ktorom sa predpokladá, že je zodpovedný za aterosklerotický plak, ktorý spôsobuje aterotrombózu (obrázok 14).

Ryža. štrnásť. 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árna rovnica Regresie s jednou premennou prediktora medzi predpovedanými hodnotami a hodnotami premennej prediktora budú mať vždy dokonalú koreláciu, pretože v takejto rovnici sa hodnoty prediktora vynásobia jednou konštantou a ku každému produktu sa pridá ďalšia konštanta. Tento efekt nepretrváva pri viacnásobnej regresii.

Zobrazuje výsledky vrátené funkciou LINREGRESE () pre viacnásobnú regresiu (obrázok 15). Regresné koeficienty sa zobrazujú ako súčasť výsledkov vrátených funkciou LINEST () 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 s jednou prediktorovou premennou sa dajú ľahko prispôsobiť tak, aby zohľadňovali viaceré prediktorové premenné. Ukazuje sa, že veľká časť tejto adaptácie závisí od eliminácie vplyvu prediktorových premenných na seba navzájom. 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 pozri v súbore Excel)

V Exceli sú funkcie, ktoré poskytujú informácie o t- a F-rozdeleniach. Funkcie, ktorých názvy obsahujú časť DIST, ako napríklad STUDENT.DIST () a F.DIST (), berú t- alebo F-test ako argument a vracajú pravdepodobnosť zobrazenia zadanej hodnoty. Funkcie, ktorých názvy obsahujú časť OBR, ako napríklad STUDENT.INV () a F.OBR (), 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 chvostových oblastí, odovzdáme 5 % ako argument jednej z funkcií STUDENT.OBR (), ktorá vráti hodnotu zodpovedajúcu tomuto pravdepodobnosť (obr. 17, 18).

Ryža. 17. Dvojstranný t-test

Ryža. 18. Jednostranný t-test

Zavedením rozhodovacieho pravidla v prípade jednostrannej alfa oblasti zvýšite štatistickú silu testu. Ak ste si pri začatí experimentovania istí, že máte všetky dôvody očakávať pozitívny (alebo negatívny) regresný koeficient, potom by ste mali vykonať jednostranný test. V tomto prípade pravdepodobnosť, ktorú beriete správne riešenie, odmietajúc hypotézu nulového regresného koeficientu v bežnej populácii, bude vyššia.

Štatistici radšej používajú tento termín smerový test namiesto termínu jednostranný test a termín neriadený test namiesto termínu obojstranný test... Pojmy smerový a nesmerový sú preferované, pretože zdôrazňujú skôr typ hypotézy než povahu distribučných chvostov.

Modelový prístup k hodnoteniu vplyvu prediktorov. Na obr. 19 ukazuje výsledky regresnej analýzy, ktorá testuje príspevok premennej stravy k regresnej rovnici.

Ryža. 19. Porovnanie dvoch modelov kontrolou rozdielov v ich výsledkoch

Výsledky LINEST () (rozsah H2: K6) sú relevantné pre to, čo volám úplný model, ktorý regresuje LDL pre diétu, vek a HDL. V rozsahu H9: J13 sú výpočty prezentované bez zohľadnenia prediktorovej premennej Strava. Tomu hovorím limitovaný model. V úplnom modeli je 49,2 % rozptylu závislej premennej LDL vysvetlených prediktorovými premennými. V obmedzenom modeli je len 30,8 % LDL vysvetlených premennými Vek a HDL. Strata 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 len s pravdepodobnosťou 0,0288 je vplyv premennej Strava náhodný. Vo zvyšných 97,1 % má diéta vplyv na LDL.

Kapitola 6. Predpoklady a upozornenia regresnej analýzy

Pojem „predpoklad“ nie je 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ď, samozrejme, existujú prípady, keď porušenie predpokladu radikálne 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) rozptyl 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 hovoria o robustnosti analýzy vo vzťahu k 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é, nie je podstatný: testy sú odolné voči porušeniu predpokladu normality. Ako už bolo povedané, je dôležité analyzovať údaje pomocou grafov. Napríklad zahrnuté v doplnku Analýza dát nástroj Regresia.

Ak vaše údaje nezodpovedajú predpokladom lineárnej regresie, máte iné prístupy ako lineárna regresia. Jednou z nich je logistická regresia (obrázok 20). V blízkosti horných a dolných limitných hodnôt prediktorovej premennej vedie lineárna regresia k nerealistickým predpovediam.

Ryža. 20. Logistická regresia

Na obr. Obrázok 6.8 ukazuje 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ť so zvyšujúcim sa príjmom. Grafy uľahčujú rozpoznanie rozdielu medzi výsledkami, ktoré predpovedajú pravdepodobnosť kúpy domu pomocou lineárnej regresie, a výsledkami, ktoré môžete získať iným prístupom.

V jazyku štatistiky sa vyradenie nulovej hypotézy, keď je skutočne pravdivá, nazýva chyba I. typu.

V nadstavbe Analýza dát ponúkol šikovný nástroj generovať náhodné čísla, ktorá umožňuje používateľovi špecifikovať požadovaný tvar rozdelenia (napríklad Normálny, Binomický alebo Poissonov), ako aj priemer a štandardnú odchýlku.

Rozdiely medzi funkciami rodiny STUDENT.DIST (). Počnúc Excel verzie 2010 tri dostupné rôzne tvary funkcia, ktorá vracia 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. Predpokladajme, že máte 36 prípadov, takže počet stupňov voľnosti na analýzu je 34 a t-test je 1,69. V tomto prípade vzorec

VZDIALENOSŤ ŠTUDENTOV (+1,69; 34; TRUE)

vráti 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 zlomok. Ak je FALSE, funkcia vráti relatívnu výšku krivky v bode, ktorý spĺňa t-test. Iné verzie funkcie STUDENT.DIST () - STUDENT.DIST.PX () a STUDENT.DIST.2X () - berú ako argumenty iba hodnotu t-kritéria a počet stupňov voľnosti a nevyžadujú zadávanie tretieho argument.

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

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

1 - SHOODENT DIST (1, 69; 34; TRUE)

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

Celá plocha pod krivkou musí byť 100 %, takže odpočítaním 1 plochy naľavo od t-testu, ktorú funkcia vráti, dostaneme plochu napravo od t-testu. Možno nájdete viac preferovaná možnosť priame získanie plošného zlomku, o ktorý máte záujem, pomocou funkcie STUDENT.DIST.PX (), kde PX znamená pravý koniec rozdelenia (obr. 22).

Ryža. 22,5% alfa plocha pre smerové testovanie

Použitie funkcií STUDENT.DIST () alebo STUDENT.DIST.PX () predpokladá, že ste si vybrali riadenú pracovnú hypotézu. Smerová pracovná hypotéza v kombinácii s nastavením alfa 5 % znamená, že všetkých 5 % umiestnite na pravý koniec rozdelenia. Budete musieť odmietnuť nulová hypotéza iba ak je pravdepodobnosť vašej hodnoty t-testu 5 % alebo menej. Smerové hypotézy zvyčajne 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 rozloženie bude iné. Keďže musíte počítať s dvomi 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 rovnakého získaného t-testu a rovnakého počtu stupňov voľnosti ako v predchádzajúcom príklade použite vzorec

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

Bez konkrétneho dôvodu STUDENT.DIST.2X () vráti kód chyby #NUM!, ak dostane ako prvý argument negatívny t-test.

Ak vzorky obsahujú iné čísloúdajov, použite dvojvzorkový t-test s rôznymi rozptylmi, 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 označovali ako predpovedané premenné, sa v tejto kapitole budú označovať ako výsledné premenné a namiesto prediktorových premenných sa použije 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 faktorovú úroveň.
• Subjekty jednej úrovne, ktoré sú často kontrolnou skupinou, dostanú kód 0 vo všetkých vektoroch.

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

Kódovanie efektov. V inom type kódovania tzv efekty kódovania, 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. Efekty kódovania

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 kontrolná skupina). V prípade efektov kódovania sa konštanta rovná celkovému priemeru (bunka J2).

generál lineárny model - užitočným spôsobom konceptualizácia zložiek hodnoty výslednej premennej:

Y ij = μ + α j + ε ij

Použitie v tomto vzorci grécke písmená namiesto latinčiny zdôrazňuje skutočnosť, že sa vzťahuje na populáciu, z ktorej sú vzorky čerpané, ale môže byť prepísané do formy, ktorá naznačuje, že sa týka vzoriek extrahovaných z publikovanej všeobecnej 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: celkový priemer, μ; efekt spracovania j a j; hodnotu e ij, ktorá predstavuje odchýlku jednotlivého kvantitatívneho ukazovateľa Y ij od kombinovanej hodnoty celkového priemeru a účinok j-tého spracovanie (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 súčasne skúma vzťah medzi efektívnou premennou a dvoma alebo viacerými faktormi, potom v tomto prípade hovoríme o použití faktorovej analýzy. Pridanie jedného alebo viacerých faktorov do jednorozmernej ANOVA môže zvýšiť štatistickú silu. V univariantnej ANOVA je variácia vo výslednej premennej, ktorú nemožno pripísať faktoru, zahrnutá do štvorca reziduálneho priemeru. Ale môže sa stať, že táto variácia je spojená s iným faktorom. Potom môže byť táto odchýlka odstránená z priemernej štvorcovej chyby, ktorej zníženie vedie k zvýšeniu hodnôt F-kritéria, a tým k zvýšeniu štatistickej sily testu. Nadstavba Analýza dát obsahuje nástroj, ktorý zabezpečuje spracovanie dvoch faktorov 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ý v tom, že vracia priemer a rozptyl výslednej premennej, ako aj hodnotu počítadla pre každú skupinu zahrnutú v pláne. V tabulke ANOVA sú zobrazené dva parametre, ktoré nie sú prítomné vo výstupe jednosmerného nástroja ANOVA. Venujte pozornosť zdrojom variácií Ukážka a Stĺpce na riadkoch 27 a 28. Zdroj variácií Stĺpce odkazuje na pohlavie. Zdroj variácií Ukážka sa vzťahuje na akúkoľvek premennú, ktorej hodnoty sú rôzne línie... Na obr. 27 hodnôt pre skupinu KursLech1 je v riadkoch 2-6, pre skupinu KursLech2 - v riadkoch 7-11 a pre skupinu KursLechZ - v riadkoch 12-16.

Kľúčovým bodom je, že pohlavie (popis Stĺpce v bunke E28) aj liečba (popis Vzorka v bunke E27) sú zahrnuté v tabuľke ANOVA ako zdroje variácií. Priemery pre mužov sa líšia od priemerov pre ženy, čo vytvára zdroj variácií. Prostriedky pre tieto tri liečby sa tiež líšia - tu je ďalší zdroj variácií. Existuje aj tretí zdroj, Interakcia, ktorý sa týka kombinovaného účinku premenných Pohlavie a Liečba.

Kapitola 8. Analýza kovariancie

Analýza kovariancie (ANCOVA) znižuje skreslenie a zvyšuje štatistickú silu. Dovoľte mi pripomenúť, že jeden zo spôsobov, ako posúdiť spoľahlivosť regresná rovnica sú F testy:

F = MS regresia/MS rezíduum

kde MS (stredná štvorec) je stredná štvorcová hodnota a regresné a reziduálne indexy označujú regresiu a zvyškové zložky resp. Výpočet MS rezidua sa vykonáva podľa vzorca:

MS Residual = SS Residual / df Residual

kde SS (Sum of Squares) je súčet druhých mocnín 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 nižšie je MS Residual, tým vyššie je F-skóre a tým je pravdepodobnejšie, že zamietnete nulovú hypotézu, že medzi priemermi nie sú žiadne rozdiely. V dôsledku toho prerozdeľujete volatilitu výslednej premennej. V ANOVA, keď sa kovariancia neberie do úvahy, volatilita sa stáva chybou. Ale v ANCOVA je časť variability, ktorá sa predtým pripisovala chybe, 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. ANOVA znamená, že výsledky regresnej rovnice sú nespoľahlivé

Štúdia porovnávala relatívne účinky cvičenia, ktoré buduje svalovú silu, a kognitívneho cvičenia (lúštenie krížoviek), ktoré stimuluje mozgovú aktivitu. Predmety boli náhodne rozdelené do dvoch skupín, takže na začiatku experimentu boli obe skupiny v rovnakých podmienkach. Po troch mesiacoch sa merali kognitívne charakteristiky subjektov. Výsledky týchto meraní sú uvedené v stĺpci B.

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

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

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

Regresná analýza je jednou z najžiadanejších metód štatistického výskumu. Môže sa použiť na stanovenie miery vplyvu nezávislých premenných na závislú premennú. Vo funkcionalite Microsoft Excel sú nástroje určené pre tento typ analýzy. Poďme sa pozrieť na to, čo sú a ako ich používať.

Pripojenie analytického balíka

Aby ste však mohli použiť funkciu, ktorá vám umožňuje vykonávať regresnú analýzu, musíte najskôr aktivovať analytický balík. Až potom sa na páse s nástrojmi Excelu objavia nástroje potrebné na tento postup.

1. Prejdite na kartu "Súbor".
2. Prejdite do sekcie "Parametre".
3. Otvorí sa okno Možnosti programu Excel. Prejdite do podsekcie „Doplnky“.
4. Úplne dole v okne, ktoré sa otvorí, posuňte prepínač v bloku „Ovládanie“ do polohy „ Excel doplnky„Ak je v inej polohe. Kliknite na tlačidlo „Prejsť“.
5. Otvorí sa okno dostupných doplnkov programu Excel. Zaškrtneme položku "Analytický balíček". Kliknite na tlačidlo "OK".

Teraz, keď prejdeme na kartu "Údaje", na páse s nástrojmi v paneli nástrojov "Analýza" uvidíme nové tlačidlo - "Analýza údajov".

Typy regresnej analýzy

Existuje niekoľko typov regresií:

• parabolický;
• mocenské právo;
• logaritmický;
• exponenciálny;
• orientačné;
• hyperbolický;
• lineárna regresia.

Povieme si podrobnejšie o výkone posledného typu regresnej analýzy v Exceli.

Lineárna regresia v Exceli

Nižšie je ako príklad uvedená tabuľka, ktorá ukazuje priemernú dennú teplotu vzduchu vonku a počet kupujúcich v obchodoch pre príslušný pracovný deň. Poďme zistiť pomocou regresnej analýzy presne ako počasie v podobe teploty vzduchu môže ovplyvniť návštevnosť živnostenskej prevádzky.

Všeobecná lineárna regresná rovnica vyzerá takto nasledujúcim spôsobom: Y = a0 + a1x1 + ... + akhk. V tomto vzorci Y znamená premennú, vplyv faktorov, ktoré sa snažíme študovať. V našom prípade ide o počet kupujúcich. X-hodnota sú rôzne faktory, ktoré ovplyvňujú premennú. Parametre a sú regresné koeficienty. To znamená, že sú to oni, ktorí určujú význam tohto alebo toho faktora. Index k označuje celková suma práve tieto faktory.

Analýza výsledkov analýzy

Výsledky regresnej analýzy sa zobrazia vo forme tabuľky na mieste určenom v nastaveniach.

Jedným z hlavných ukazovateľov je R-štvorec. Označuje kvalitu modelu. V našom prípade daný koeficient rovná 0,705 alebo približne 70,5 %. Toto je prijateľná úroveň kvality. Závislosť menšia ako 0,5 je zlá.

Ďalší dôležitý ukazovateľ sa nachádza v bunke na priesečníku čiary "Y-prienik" a stĺpca "Koeficienty". Označuje, akú hodnotu bude mať Y, a v našom prípade je to počet kupujúcich, pričom všetky ostatné faktory sú rovné nule. V tejto tabuľke daná hodnota rovná sa 58,04.

Hodnota na priesečníku stĺpcov „Premenná X1“ a „Koeficienty“ zobrazuje úroveň závislosti Y na X. V našom prípade je to úroveň závislosti počtu zákazníkov predajne od teploty. Pomer 1,31 sa považuje za pomerne vysoký ukazovateľ vplyvu.

Ako vidíte, pomocou programy spoločnosti Microsoft Excel je pomerne jednoduchý na vytvorenie tabuľky regresnej analýzy. S dátami získanými na výstupe však môže pracovať a pochopiť ich podstatu len vyškolený človek.

Sme radi, že sme vám mohli pomôcť vyriešiť problém.

Opýtajte sa v komentároch svoju otázku a podrobne opíšte podstatu problému. Naši odborníci sa pokúsia odpovedať čo najrýchlejšie.

Pomohol vám tento článok?

Lineárna regresia nám umožňuje opísať priamku, ktorá najlepšie zodpovedá sérii usporiadaných párov (x, y). Rovnica pre priamku, známa ako lineárna rovnica, je uvedená nižšie:

ŷ je očakávaná hodnota y pre danú hodnotu x,

x je nezávislá premenná,

a - segment na osi y pre priamku,

b - sklon priamky.

Na nasledujúcom obrázku je tento koncept graficky znázornený:

Na obrázku vyššie je čiara opísaná rovnicou ŷ = 2 + 0,5x. Úsečka na osi y je priesečníkom úsečky s osou y; v našom prípade a = 2. Sklon úsečky, b, pomer stúpania úsečky k dĺžke úsečky, má hodnotu 0,5. Pozitívny sklon znamená, že čiara stúpa zľava doprava. Ak b = 0, čiara je vodorovná, čo znamená, že medzi závislými a nezávislými premennými neexistuje žiadny vzťah. Inými slovami, zmena hodnoty x neovplyvní hodnotu y.

Ŷ a y sú často zamieňané. Graf zobrazuje 6 usporiadaných dvojíc bodov a priamku podľa tejto rovnice

Tento obrázok znázorňuje bod zodpovedajúci usporiadanej dvojici x = 2 a y = 4. Všimnite si, že očakávaná hodnota y podľa čiary na NS= 2 je ŷ. Môžeme to potvrdiť nasledujúcou rovnicou:

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

Hodnota y je skutočný bod a hodnota je očakávaná hodnota y pomocou lineárnej rovnice pre danú hodnotu x.

Ďalším krokom je určenie lineárnej rovnice, ktorá sa najviac zhoduje s množinou usporiadaných dvojíc, o tom sme hovorili v predchádzajúcom článku, kde sme určovali tvar rovnice metódou najmenších štvorcov.

Použitie Excelu na definovanie lineárnej regresie

Ak chcete použiť nástroj regresnej analýzy zabudovaný do Excelu, musíte aktivovať doplnok Analytický balík... Nájdete ho kliknutím na záložku Súbor -> Možnosti(2007+), v zobrazenom dialógovom okne možnostiExcel prejdite na kartu Doplnky. V teréne Kontrola vybrať si DoplnkyExcel a kliknite Choď. V zobrazenom okne začiarknite políčko oproti analytický balík, stlačíme OK

V záložke Údaje v skupine Analýza objaví sa nové tlačidlo Analýza dát.

Na ukážku, ako doplnok funguje, použijeme údaje z predchádzajúceho článku, kde sa chlap a dievča delia o stôl v kúpeľni. Zadajte údaje pre náš príklad vane do stĺpcov A a B prázdnej tabuľky.

Prejdite na kartu údaje, v skupine Analýza kliknite Analýza dát. V okne, ktoré sa zobrazí Analýza dát vybrať si Regresia ako je znázornené a kliknite na tlačidlo OK.

V okne nastavte požadované parametre regresie Regresia, ako je znázornené na obrázku:

Kliknite OK Na nasledujúcom obrázku sú uvedené získané výsledky:

Tieto výsledky sú v súlade s tými, ktoré sme získali samopočítaním v predchádzajúcom článku.

Regresná analýza je štatistická metóda výskum, ktorý umožňuje ukázať závislosť parametra od jednej alebo viacerých nezávislých premenných. V predpočítačovej ére bola jeho aplikácia pomerne náročná, najmä ak išlo o veľké množstvo dát. Dnes, keď ste sa naučili, ako vytvoriť regresiu v programe Excel, môžete riešiť komplexné riešenia štatistické úlohy už za pár minút. Nižšie sú uvedené konkrétne príklady z oblasti ekonomiky.

Regresné typy

Samotný koncept zaviedol do matematiky Francis Galton v roku 1886. Regresia sa deje:

• lineárny;
• parabolický;
• mocenské právo;
• exponenciálny;
• hyperbolický;
• orientačné;
• logaritmický.

Príklad 1

Zamyslime sa nad problémom určenia závislosti počtu zamestnancov, ktorí ukončili prácu, od priemernej mzdy v 6 priemyselných podnikoch.

Úloha. Šesť podnikov analyzovalo priemernú mesačnú mzdu a počet zamestnancov, ktorí odišli na vlastnú päsť... V tabuľková forma máme:

Pre problém určenia závislosti počtu odchádzajúcich zamestnancov od priemernej mzdy v 6 podnikoch má regresný model tvar rovnice Y = a0 + a1 × 1 +… + akxk, kde xi sú ovplyvňujúce premenné, ai sú regresné koeficienty a k je počet faktorov.

Pre túto úlohu je Y ukazovateľom zamestnancov, ktorí odídu, a ovplyvňujúcim faktorom je mzda, ktorú označujeme X.

Využitie možností tabuľkového procesora Excel

Regresnej analýze v Exceli musí predchádzať aplikácia vstavaných funkcií na existujúce tabuľkové údaje. Na tieto účely je však lepšie použiť veľmi užitočný doplnok „Analytický balík“. Na jeho aktiváciu potrebujete:

• na karte "Súbor" prejdite do časti "Parametre";
• v okne, ktoré sa otvorí, vyberte riadok "Doplnky";
• kliknite na tlačidlo "Prejsť" umiestnené nižšie, napravo od riadku "Ovládanie";
• zaškrtnite názov "Analýza balík" a potvrďte svoje akcie kliknutím na "OK".

Ak je všetko vykonané správne, požadované tlačidlo sa zobrazí na pravej strane karty "Údaje", ktorá sa nachádza nad pracovným hárkom "Excel".

Lineárna regresia v Exceli

Teraz, keď máme po ruke všetky potrebné virtuálne nástroje na vykonávanie ekonometrických výpočtov, môžeme začať riešiť náš problém. Pre to:

• kliknite na tlačidlo "Analýza údajov";
• v okne, ktoré sa otvorí, kliknite na tlačidlo "Regresia";
• na zobrazenej karte zadajte rozsah hodnôt pre Y (počet zamestnancov, ktorí skončili prácu) a pre X (ich platy);
• potvrdíme naše akcie stlačením tlačidla "Ok".

V dôsledku toho sa program automaticky vyplní nový list stolový procesorúdaje z regresnej analýzy. Poznámka! Excel má schopnosť nezávisle definovať umiestnenie, ktoré na tento účel uprednostňujete. Môže to byť napríklad rovnaký hárok ako hodnoty Y a X alebo dokonca Nová knihašpeciálne navrhnuté na ukladanie takýchto údajov.

Analýza výsledkov regresie pre R-štvorec

V Excel dáta získané v priebehu spracovania údajov z posudzovaného príkladu majú tvar:

V prvom rade by ste si mali dať pozor na hodnotu R-štvorca. Predstavuje koeficient determinácie. V tento príklad R-štvorec = 0,755 (75,5 %), t. j. vypočítané parametre modelu vysvetľujú vzťah medzi uvažovanými parametrami na 75,5 %. Čím vyššia je hodnota koeficientu determinácie, tým viac sa zvolený model považuje za použiteľný konkrétnu úlohu... Predpokladá sa, že správne popisuje skutočnú situáciu, keď je hodnota R-squared nad 0,8. Ak je R-kvadrát tcr, potom sa hypotéza o nevýznamnosti voľného člena lineárnej rovnice zamieta.

V uvažovanej úlohe pre voľný termín pomocou nástrojov Excel sa zistilo, že t = 169,20903 a p = 2,89E-12, to znamená, že máme nulovú pravdepodobnosť, že správna hypotéza o nevýznamnosti voľného termínu bude odmietol. Pre koeficient pri neznámej hodnote t = 5,79405 a p = 0,001158. Inými slovami, pravdepodobnosť, že bude zamietnutá správna hypotéza o nevýznamnosti koeficientu s neznámou, je 0,12 %.

Dá sa teda tvrdiť, že výsledná lineárna regresná rovnica je adekvátna.

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

Viacnásobná regresia v Exceli sa vykonáva pomocou rovnakého nástroja na analýzu údajov. Uvažujme o konkrétnom aplikovanom probléme.

Manažment spoločnosti "NNN" musí rozhodnúť o vhodnosti kúpy 20% podielu v JSC "MMM". Náklady na balík (JV) sú 70 miliónov USD. Špecialisti NNN zhromaždili údaje o podobných transakciách. Bolo rozhodnuté ohodnotiť hodnotu balíka akcií takými parametrami, vyjadrenými v miliónoch amerických dolárov, ako sú:

• splatné účty (VK);
• objem ročného obratu (VO);
• pohľadávky (VD);
• obstarávacia cena fixných aktív (SOF).

Okrem toho je parametrom nedoplatok miezd podniku (V3 P) v tisícoch amerických dolárov.

Tabuľkové riešenie Excel

Najprv musíte vytvoriť tabuľku počiatočných údajov. Vyzerá to takto:

• zavolajte okno "Analýza údajov";
• vyberte sekciu "Regresia";
• rozsah hodnôt závislých premenných zo stĺpca G sa zadáva do poľa „Interval vstupu Y“;
• kliknite na ikonu s červenou šípkou napravo od okna „Interval vstupu X“ a vyberte na hárku rozsah všetkých hodnôt od stĺpce B, C, D, F.

Začiarknite položku "Nový pracovný hárok" a kliknite na tlačidlo "OK".

Získajte regresnú analýzu pre danú úlohu.

Štúdium výsledkov a záverov

„Zhromažďujeme“ zo zaokrúhlených údajov uvedených vyššie na hárku tabuľky Excel procesor, regresná rovnica:

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

V známejšej matematickej forme to možno napísať ako:

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

Údaje pre JSC "MMM" sú uvedené v tabuľke:

Po ich dosadení do regresnej rovnice je to 64,72 milióna amerických dolárov. To znamená, že akcie JSC "MMM" by sa nemali kupovať, pretože ich hodnota 70 miliónov amerických dolárov je dosť nadhodnotená.

Ako vidíte, použitie tabuľkového procesora Excel a regresnej rovnice umožnilo urobiť informované rozhodnutie o vhodnosti veľmi špecifickej transakcie.

Teraz viete, čo je regresia. Vyššie uvedené príklady v Exceli vám pomôžu vyriešiť problém praktické úlohy z oblasti ekonometrie.

Regresia v program Excel

Štatistické spracovanie údajov je možné vykonávať aj pomocou doplnku Analytický balík v položke menu „Služba“. V Exceli 2003, ak otvoríte SERVIS, kartu nenájdeme ANALÝZA DÁT, potom kliknutím ľavého tlačidla myši otvorte kartu NADSTAVBY a opačný bod BALÍK ANALÝZY kliknutím ľavého tlačidla myši zaškrtnite (obr. 17).

Ryža. 17. Okno NADSTAVBY

Potom v menu SERVIS zobrazí sa karta ANALÝZA DÁT.

V Exceli 2007 na inštaláciu BALÍK ANALÝZY musíte stlačiť tlačidlo OFFICE vľavo horný roh list (obr.18a). Ďalej kliknite na tlačidlo EXCEL PARAMETRE... V okne, ktoré sa zobrazí EXCEL PARAMETRE kliknite ľavým tlačidlom myši na položku NADSTAVBY a na pravej strane rozbaľovacieho zoznamu vyberte položku BALÍK ANALÝZY.Ďalej kliknite na OK.

Možnosti programu Excel Tlačidlo Office

Ryža. 18. Inštalácia BALÍK ANALÝZY v Exceli 2007

Ak chcete nainštalovať analytický balík, kliknite na tlačidlo GO, nachádza v spodnej časti otvoreného okna. Okno znázornené na obr. 12. Začiarknite opačne BALÍK ANALÝZY. V záložke ÚDAJE objaví sa tlačidlo ANALÝZA DÁT(obr. 19).

Z navrhnutých položiek si vyberie položku " REGRESIA„A kliknite naň ľavým tlačidlom myši. Potom kliknite na tlačidlo OK.

Okno znázornené na obr. 21

analytický nástroj " REGRESIA»Používa sa na zostavenie grafu pre súbor pozorovaní pomocou metódy najmenších štvorcov. Regresia sa používa na analýzu dopadu na jednotlivca závislú premenné hodnoty jedna alebo viac nezávislých premenných. Napríklad športový výkon športovca ovplyvňuje niekoľko faktorov vrátane veku, výšky a hmotnosti. Môžete vypočítať vplyv každého z týchto troch faktorov na výkon športovca a potom tieto údaje použiť na predpovedanie výkonu iného športovca.

Funkciu používa nástroj Regresia LINEST.

Dialógové okno REGRESIA

Štítky Začiarknite políčko, ak ide o prvý riadok alebo prvý stĺpec vstupný rozsah obsahuje hlavičky. Zrušte začiarknutie tohto políčka, ak neexistujú žiadne tituly. V tomto prípade sa príslušné hlavičky pre údaje výstupnej tabuľky vygenerujú automaticky.

Úroveň spoľahlivosti Označte začiarkavacie políčko, ak chcete do tabuľky súčtov výstupov zahrnúť ďalšiu úroveň. Do príslušného poľa zadajte úroveň spoľahlivosti, ktorú chcete použiť, okrem predvolenej úrovne 95 %.

Konštantná - nula Označte začiarkavacie políčko, aby regresná čiara prechádzala počiatkom.

Výstupný rozstup Zadajte odkaz vľavo horná bunka výstupný rozsah. Prideľte aspoň sedem stĺpcov pre výstupnú tabuľku súčtov, ktorá bude obsahovať: výsledky ANOVA, koeficienty, štandardnú chybu výpočtu Y, štandardné odchýlky, počet pozorovaní, štandardné chyby pre koeficienty.

Nový pracovný hárok Vyberte tento prepínač, ak chcete otvoriť nový pracovný hárok v zošite a vložiť výsledky analýzy počnúc bunkou A1. V prípade potreby zadajte názov nového hárku do poľa oproti zodpovedajúcej polohe prepínača.

Nový pracovný zošit Kliknutím na prepínač do tejto polohy vytvoríte nový zošit, v ktorom sa výsledky pridajú do nového hárka.

Zvyšky Začiarknutím tohto políčka zahrniete zvyšky do výstupnej tabuľky.

Štandardizované rezíduá Začiarknutím tohto políčka zahrniete štandardizované rezíduá do výstupnej tabuľky.

Vykresliť rezíduá Začiarknutím tohto políčka vykreslíte rezíduá pre každú nezávislú premennú.

Fitting plot Začiarknutím tohto políčka zobrazíte graf predpokladaných hodnôt oproti pozorovaným hodnotám.

Graf normálnej pravdepodobnosti Začiarknutím políčka vykreslíte graf normálnej pravdepodobnosti.

Funkcia LINEST

Ak chcete vykonať výpočty, vyberte kurzorom bunku, v ktorej chceme zobraziť priemernú hodnotu a stlačte kláves = na klávesnici. Ďalej v poli Názov uveďte napríklad požadovanú funkciu PRIEMERNÝ(obr. 22).

Ryža. 22 Hľadanie funkcií v Exceli 2003

Ak v teréne NÁZOV názov funkcie sa nezobrazí, potom kliknite ľavým tlačidlom myši na trojuholník vedľa poľa, po ktorom sa zobrazí okno so zoznamom funkcií. Ak táto funkcia nie je v zozname, kliknite ľavým tlačidlom myši na položku zoznamu ĎALŠIE FUNKCIE, zobrazí sa dialógové okno MAJSTER FUNKCIÍ, pri ktorej sa používa vertikálne rolovanie vyberte požadovanú funkciu, vyberte ju kurzorom a kliknite na OK(obr. 23).

Ryža. 23. Sprievodca funkciou

Pre vyhľadanie funkcie v Exceli 2007 je možné v menu otvoriť ľubovoľnú záložku, následne pri výpočtoch vybrať bunku, v ktorej chceme zobraziť priemernú hodnotu a stlačiť kláves = na klávesnici. Ďalej v poli Názov zadajte funkciu PRIEMERNÝ... Okno na výpočet funkcie je podobné tomu, ktoré je zobrazené v Exceli 2003.

Môžete tiež vybrať kartu Vzorce a kliknúť ľavým tlačidlom myši na tlačidlo v ponuke " FUNKCIA VLOŽIŤ“(Obr. 24), objaví sa okno MAJSTER FUNKCIÍ, ktorého typ je podobný Excelu 2003. Aj v menu si môžete hneď vybrať kategóriu funkcií (posledné použité, finančné, logické, textové, dátumové a časové, matematické, iné funkcie), v ktorých budeme hľadať tzv. požadovanú funkciu.

Ďalšie funkcie Referencie a polia Matematické

Ryža. 24 Výber funkcie v Exceli 2007

Funkcia LINEST vypočíta štatistiku pre sériu pomocou metódy najmenších štvorcov, aby vypočítala priamku, ktorá najlepšia cesta aproximuje dostupné údaje a potom vráti pole, ktoré popisuje výsledný riadok. Funkciu môžete aj kombinovať LINEST s inými funkciami na výpočet iných druhov modelov, ktoré sú lineárne v neznámych parametroch (ktorých neznáme parametre sú lineárne), vrátane polynomických, logaritmických, exponenciálnych a mocninových radov. Keďže sa vracia pole hodnôt, funkcia musí byť špecifikovaná ako vzorec poľa.

Rovnica pre priamku je nasledovná:

(v prípade viacerých rozsahov hodnôt x),

kde závislá hodnota y je funkcia nezávislý význam x, hodnoty m sú koeficienty zodpovedajúce každej nezávislej premennej x a b je konštanta. Všimnite si, že y, x a m môžu byť vektory. Funkcia LINEST vráti pole . LINEST môže vrátiť aj dodatočnú regresná štatistika.

LINEST(známe_y; známe_x; konšt.; štatistiky)

Známe_y sú množina hodnôt y, ktoré sú už pre vzťah známe.

Ak má pole známe_y jeden stĺpec, potom sa každý stĺpec v poli známe_x interpretuje ako samostatná premenná.

Ak má parameter známe_y jeden riadok, potom sa každý riadok v poli známe_x interpretuje ako samostatná premenná.

Známe_x sú voliteľná množina hodnôt x, ktoré sú už pre vzťah známe.

Známe_x môžu obsahovať jednu alebo viac sád premenných. Ak sa použije iba jedna premenná, potom známe_y a známe_x môžu mať akýkoľvek tvar, pokiaľ majú rovnaký rozmer. Ak sa použije viac ako jedna premenná, parameter známe_y musí byť vektor (t. j. jeden riadok vysoký alebo jeden stĺpec široký).

Ak pole pole_ známe_x vynecháte, potom sa predpokladá, že toto pole (1; 2; 3; ...) má rovnakú veľkosť ako pole pole_ známe_y.

Const je boolovská hodnota, ktorá udáva, či sa vyžaduje, aby konštanta b bola 0.

Ak je const PRAVDA alebo sa vynechá, konštanta b sa vyhodnotí obvyklým spôsobom.

Ak je argument "const" NEPRAVDA, potom sa hodnota b nastaví na 0 a hodnoty m sa vyberú tak, aby vzťah platil.

Štatistika je logická hodnota, ktorá označuje, či chcete vrátiť ďalšie štatistiky pre regresiu.

Ak má štatistika hodnotu TRUE, funkcia LINEST vráti ďalšie regresné štatistiky. Vrátené pole bude vyzerať takto: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Ak je štatistika FALSE alebo je vynechaná, funkcia LINEST vráti iba koeficienty ma konštantu b.

Ďalšie regresné štatistiky.

Veľkosť Popis se1, se2, ..., sen Hodnoty štandardnej chyby pre koeficienty m1, m2, ..., mn. seb Hodnota štandardnej chyby pre konštantu b (seb = # N / A, ak je const FALSE). r2 Koeficient determinizmu. Skutočné hodnoty y sa porovnávajú s hodnotami získanými z rovnice priamky; na základe výsledkov porovnania sa vypočíta koeficient determinizmu normalizovaný od 0 do 1. Ak sa rovná 1, potom existuje úplná korelácia s modelom, to znamená, že neexistuje rozdiel medzi skutočným a odhadovaným hodnoty y. V opačnom prípade, ak je koeficient determinizmu 0, nemá zmysel používať regresnú rovnicu na predpovedanie hodnôt y. Obdržať Ďalšie informácie Ako vypočítať r2, pozri Poznámky na konci tejto časti. sey Štandardná chyba pre odhad y. F F-štatistika alebo F-pozorovaná hodnota. F štatistika sa používa na určenie, či je pozorovaný vzťah medzi závislými a nezávislými premennými náhodný. df Stupne slobody. Stupne voľnosti sú užitočné pri hľadaní F-kritické hodnoty v štatistickej tabuľke. Ak chcete určiť úroveň spoľahlivosti modelu, porovnajte hodnoty v tabuľke s F-štatistikou vrátenou LINREGRESOM. Ďalšie informácie o výpočte df nájdete v poznámkach na konci tejto časti. Príklad 4 nižšie ukazuje použitie hodnôt F a df. ssreg Regresný súčet štvorcov. ssresid Zvyškový súčet štvorcov. Ďalšie informácie o výpočte hodnôt ssreg a ssresid nájdete v časti Poznámky na konci tejto časti.

Obrázok nižšie ukazuje poradie, v ktorom sa vrátia dodatočné regresné štatistiky.

Poznámky:

Akákoľvek priamka môže byť opísaná jej sklonom a priesečníkom s osou y:

Sklon (m): Na určenie sklonu priamky, zvyčajne označovanej m, musíte zobrať dva body priamky a; sklon bude .

Priesečník Y (b): Priesečník y úsečky, zvyčajne označovaný písmenom b, je hodnota y bodu, v ktorom priamka pretína os y.

Rovnica s priamkou má tvar. Ak poznáte hodnoty m a b, môžete vypočítať ľubovoľný bod na priamke nahradením hodnôt y alebo x v rovnici. Využiť môžete aj funkciu TREND.

Ak existuje iba jedna nezávislá premenná x, môžete získať sklon a priesečník y priamo pomocou nasledujúcich vzorcov:

Sklon: INDEX (LINEST (známe_y; známe_x); 1)

Priesečník Y: INDEX (LINEST (známe_y; známe_x); 2)

Presnosť aproximácie čiary LINEST závisí od stupňa rozptylu v údajoch. Čím bližšie sú údaje k priamke, tým presnejší je model LINEST. Funkcia LINEST používa metódu najmenších štvorcov na určenie najvhodnejšieho prispôsobenia údajom. Ak existuje iba jedna nezávislá premenná x, m a b sa vypočítajú pomocou nasledujúcich vzorcov:

kde x a y sú vzorové priemery, napríklad x = AVERAGE (známe_x) a y = AVERAGE (známe_y).

Funkcie LINREGRESE a LGRFPRIBL dokážu vypočítať priamu alebo exponenciálnu krivku, ktorá najlepšie popisuje údaje. Neodpovedajú však na otázku, ktorý z dvoch výsledkov je vhodnejší na riešenie danej úlohy. Môžete tiež vypočítať TREND (známe_y; známe_x) pre priamku alebo GROWTH (známe_y; známe_x) pre exponenciálnu krivku. Ak tieto funkcie nezadáte new_x_values, vrátia pole vypočítaných hodnôt y pre skutočné hodnoty x pozdĺž priamky alebo krivky. Vypočítané hodnoty potom možno porovnať so skutočnými hodnotami. Môžete tiež zostaviť grafy na vizuálne porovnanie.

Pomocou regresnej analýzy Microsoft Excel vypočíta pre každý bod druhú mocninu rozdielu medzi predpokladanou hodnotou y a skutočnou hodnotou y. Súčet týchto štvorcových rozdielov sa nazýva zvyškový súčet druhých mocnín (ssresid). Microsoft Excel potom vypočíta celkový súčet štvorcov (sstotal). Ak const = TRUE alebo nie je zadaná žiadna hodnota pre tento argument, celková sumaštvorce sa budú rovnať súčtu druhých mocnín rozdielov medzi skutočnými hodnotami y a strednými hodnotami y. Keď const = FALSE, celkový súčet druhých mocnín sa bude rovnať súčtu druhých mocnín skutočných hodnôt y (bez odčítania strednej hodnoty y od hodnoty kvocientu y). Regresný súčet štvorcov sa potom môže vypočítať takto: ssreg = sstotal - ssresid. Čím menší je zvyškový súčet štvorcov, tým väčšia je hodnota koeficientu determinizmu r2, ktorý ukazuje, ako dobre rovnica získaná pomocou regresnej analýzy vysvetľuje vzťah medzi premennými. Koeficient r2 je ssreg / sstotal.

V niektorých prípadoch jeden alebo viac stĺpcov X (nech sú hodnoty Y a X v stĺpcoch) nemajú žiadnu ďalšiu predikatívnu hodnotu v ostatných stĺpcoch X. Inými slovami, odstránenie jedného alebo viacerých stĺpcov X môže viesť k hodnotám Y ​vypočítané s rovnakou presnosťou. V tomto prípade budú z regresného modelu vylúčené nadbytočné X stĺpce. Tento jav sa nazýva "kolinearita", pretože redundantné X stĺpce môžu byť reprezentované ako súčet viacerých neredundantných stĺpcov. LINEST skontroluje kolinearitu a odstráni všetky nadbytočné X stĺpce z regresného modelu, ak ich nájde. Vymazané X stĺpce možno identifikovať vo výstupe funkcie LINREGRESE podľa faktora 0 a hodnoty SE 0. Odstránenie jedného alebo viacerých stĺpcov ako nadbytočných zmení hodnotu df, pretože závisí od počtu X stĺpcov skutočne použitých na prediktívne účely. Ďalšie informácie o výpočte df nájdete nižšie v príklade 4. Keď sa df zmení v dôsledku odstránenia nadbytočných stĺpcov, zmenia sa aj sey a F. Kolinearita sa často neodporúča. Mal by sa však použiť, ak niektoré zo stĺpcov X obsahujú 0 alebo 1 ako indikátor, ktorý naznačuje, či je predmet experimentu v samostatná skupina... Ak const = TRUE alebo vynechané, LINREGRESE vloží ďalší stĺpec X na simuláciu priesečníka. Ak existuje stĺpec s hodnotami 1 pre mužov a 0 pre ženy a existuje aj stĺpec s hodnotami 1 pre ženy a 0 pre mužov, potom sa posledný stĺpec odstráni, pretože jeho hodnoty môžu byť získané zo stĺpca s „ukazovateľom mužského pohlavia“.

Výpočet df pre prípady, keď stĺpce X nie sú z modelu odstránené z dôvodu kolinearity, je nasledujúci: ak existuje k stĺpcov známych_x a hodnota const = TRUE alebo nie je špecifikovaná, potom df = n - k - 1. const = FALSE, potom df = n - k. V oboch prípadoch odstránenie X stĺpcov v dôsledku kolinearity zvýši hodnotu df o 1.

Vzorce, ktoré vracajú polia, musia byť zadané ako vzorce poľa.

Keď zadáte pole konštánt, napríklad známe_x, použite bodkočiarku na oddelenie hodnôt na rovnakom riadku a dvojbodku na oddelenie riadkov. Oddeľovacie znaky sa líšia v závislosti od možností nastavených v okne Jazyk a štandardy na ovládacom paneli.

Treba poznamenať, že y-hodnoty predpovedané regresnou rovnicou nemusia byť správne, ak sú mimo rozsahu y-hodnôt, ktoré boli použité na definovanie rovnice.

Hlavný algoritmus použitý vo funkcii LINEST, sa líši od hlavného algoritmu funkcií INCLINE a ODDIEL... Rozdiely medzi algoritmami môžu viesť k rôznym výsledkom pre nedefinované a kolineárne údaje. Napríklad, ak dátové body známe_y sú 0 a dátové body známe_x sú 1, potom:

Funkcia LINEST vráti hodnotu rovnajúcu sa 0. Algoritmus funkcie LINEST sa používa na vrátenie platných hodnôt pre kolineárne dáta, v takom prípade je možné nájsť aspoň jednu odpoveď.

Funkcie SLOPE a INTERCEPT vrátia # DIV / 0! Chyba. Algoritmus funkcií SLOPE a INTERCEPT sa používa na vyhľadávanie iba jednej odpovede, v tomto prípade ich môže byť niekoľko.

Okrem výpočtu štatistík pre iné typy regresie možno funkciu LINEST použiť na výpočet rozsahov pre iné typy regresie zadaním funkcií x a y ako radov x a y pre funkciu LINREGRESE. Napríklad nasledujúci vzorec:

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

funguje tak, že má jeden stĺpec hodnôt Y a jeden stĺpec hodnôt X na výpočet aproximácie ku kocke (polynóm 3. stupňa) nasledujúceho tvaru:

Vzorec je možné zmeniť na výpočet iných typov regresie, ale v niektorých prípadoch sú potrebné úpravy výstupných hodnôt a iných štatistík.