Regrese v Excelu: rovnice, příklady. Lineární regrese

Regresní analýza v aplikaci Microsoft Excel je nejkomplexnějším průvodcem používání MS Excel k řešení problémů s regresní analýzou v business intelligence. Konrad Carlberg srozumitelně vysvětluje teoretickou problematiku, jejíž znalost vám pomůže vyhnout se mnoha chybám jak při vlastní regresní analýze, tak při vyhodnocování výsledků analýz prováděných jinými lidmi. Veškerý materiál, od jednoduchých korelací a t-testů až po vícenásobnou analýzu kovariance, je založen na skutečných příkladech a je doprovázen podrobným popisem příslušných postupných postupů.

Tato kniha pojednává o výhodách a nevýhodách regresních funkcí Excelu, zkoumá důsledky použití každé z jejich možností a argumentů a vysvětluje, jak spolehlivě aplikovat regresní techniky v oblastech od lékařského výzkumu po finanční analýzu.

Konrad Carlberg. Regresní analýza v aplikaci Microsoft Excel. - M.: Dialektika, 2017. - 400 s.

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

Kapitola 1. Odhad variability dat

Statistici mají k dispozici mnoho ukazatelů variace (variability). Jedním z nich je součet čtverců odchylek jednotlivých hodnot od průměru. Excel k tomu používá funkci SQUADROT(). Častěji se však používá disperze. Rozptyl je průměr druhých mocnin odchylek. Rozptyl není citlivý na počet hodnot ve studovaném souboru dat (zatímco součet čtverců odchylek se zvyšuje s počtem měření).

Excel nabízí dvě funkce, které vracejí rozptyl: VARP.D() a VARP.V():

Pokud hodnoty, které mají být zpracovány, tvoří soubor, použijte funkci VAR.G(). To znamená, že hodnoty obsažené v rozsahu jsou jediné hodnoty, které vás zajímají.
Pokud hodnoty, které mají být zpracovány, tvoří vzorek z větší populace, použijte funkci VAR.V(). Předpokládá, že existují další hodnoty, jejichž rozptyl můžete také odhadnout.

Pokud se hodnota, jako je průměr nebo korelační koeficient, vypočítává na základě obecné populace, pak se nazývá parametr. Podobná hodnota vypočítaná na základě vzorku se nazývá statistika. Odpočítávání odchylek z průměru v této sadě dostanete součet druhých mocnin odchylek o menší částku, než kdybyste je počítali z jakékoli jiné hodnoty. Podobné tvrzení platí pro rozptyl.

Čím větší je velikost vzorku, tím přesnější je vypočítaná hodnota statistiky. Neexistuje ale žádný vzorek menší než velikost populace, u kterého si můžete být jisti, že hodnota statistiky je stejná jako hodnota parametru.

Řekněme, že máte sadu 100 výšek, jejichž průměr se liší od průměru populace, ať je rozdíl jakkoli malý. Když vypočítáte rozptyl pro vzorek, dostanete nějakou hodnotu, řekněme 4. Tato hodnota je menší než jakákoli jiná hodnota, kterou lze získat výpočtem odchylky každé ze 100 hodnot růstu od jakékoli hodnoty jiné než je průměr vzorku včetně skutečného průměru pro běžnou populaci. Vypočítaný rozptyl se tedy bude lišit, a to v menší míře, od rozptylu, který byste získali, kdybyste nějak znali a použili nikoli výběrový průměr, ale parametr populace.

Střední součet čtverců stanovený pro vzorek poskytuje nižší odhad rozptylu populace. Takto vypočítaný rozptyl se nazývá přemístěno hodnocení. Ukazuje se, že k odstranění zkreslení a získání nezkresleného odhadu stačí vydělit součet čtverců odchylek nikoli n, Kde n je velikost vzorku a n-1.

Hodnota n-1 se nazývá počet (počet) stupňů volnosti. Existují různé způsoby, jak vypočítat tuto hodnotu, i když všechny zahrnují buď odečtení určitého čísla od velikosti vzorku, nebo počítání počtu kategorií, do kterých pozorování spadají.

Podstata rozdílu mezi funkcemi DISP.G() a DISP.V() je následující:

Ve funkci VARI.G() je součet čtverců dělen počtem pozorování, a proto představuje zkreslený odhad rozptylu, skutečný průměr.
Ve funkci VAR.B() se součet čtverců vydělí počtem pozorování mínus 1, tzn. počtem stupňů volnosti, což poskytuje přesnější, nezkreslený odhad rozptylu populace, ze které byl vzorek odebrán.

standardní odchylka (anglicky) standardní odchylka, SD) je druhá odmocnina rozptylu:

Kvadrating odchylek převádí měřítko na jinou metriku, která je druhou mocninou té původní: metry – na metry čtvereční, dolary – na dolary čtvereční atd. Směrodatná odchylka je druhá odmocnina rozptylu, a tak nás vrací k původním jednotkám. Což je pohodlnější.

Často je nutné vypočítat směrodatnou odchylku poté, co byla data podrobena nějaké manipulaci. A přestože jsou v těchto případech výsledky nepochybně směrodatné odchylky, běžně se jim říká standardní chyby. Existuje několik typů standardních chyb, včetně standardní chyby měření, standardní chyby proporce a standardní chyby průměru.

Řekněme, že shromažďujete údaje o výšce 25 náhodně vybraných dospělých mužů v každém z 50 států. Dále vypočítáte průměrnou výšku dospělých mužů v každém státě. Výsledných 50 středních hodnot lze zase považovat za pozorování. Z toho byste mohli vypočítat jejich směrodatnou odchylku, což je standardní chyba průměru. Rýže. 1. umožňuje porovnat rozložení 1250 původních individuálních hodnot (údaje o výšce 25 mužů v každém z 50 států) s rozložením průměrných hodnot 50 států. Vzorec pro odhad směrodatné chyby průměru (tj. směrodatná odchylka průměrů, nikoli jednotlivých pozorování):

kde je standardní chyba průměru; s je standardní odchylka původních pozorování; n je počet pozorování ve vzorku.

Rýže. 1. Kolísání průměrných hodnot mezi jednotlivými stavy je mnohem menší než variace jednotlivých pozorování

Ve statistice existuje konvence týkající se použití řeckých a latinských písmen k označení statistických veličin. Je obvyklé označovat parametry obecné populace řeckými písmeny a vzorové statistiky latinkou. Pokud tedy mluvíme o směrodatné odchylce populace, zapíšeme ji jako σ; pokud se uvažuje směrodatná odchylka vzorku, použijeme označení s. Co se týče symbolů pro průměry, tak ty spolu tak dobře nesouhlasí. Populační průměr se označuje řeckým písmenem μ. Nicméně, symbol X̅ se tradičně používá k reprezentaci výběrového průměru.

z-skóre vyjadřuje pozici pozorování v rozdělení v jednotkách směrodatné odchylky. Například z = 1,5 znamená, že pozorování je 1,5 standardní odchylky od průměru směrem k vyšším hodnotám. Období z-skóre slouží k individuálním hodnocením, tzn. pro měření přiřazená jednotlivým prvkům vzorku. Pro takové statistiky (např. státní průměr) se používá termín. z-hodnota:

kde X je střední hodnota vzorku, μ je střední hodnota obecné populace, je standardní chyba průměru souboru vzorků:

kde σ je standardní chyba obecné populace (jednotlivá měření), n je velikost vzorku.

Předpokládejme, že jste golfový instruktor. Dosah úderu jste mohli měřit již dlouhou dobu a víte, že průměr je 205 yardů a standardní odchylka je 36 yardů. Byl vám nabídnut nový hůl s tvrzením, že zvýší váš dosah o 10 yardů. Požádáte každého z dalších 81 návštěvníků klubu, aby vyzkoušeli nový klub a zaznamenali svůj rozsah. Ukázalo se, že průměrný dosah úderu s novou holí je 215 yardů. Jaká je pravděpodobnost, že rozdíl 10 yardů (215 - 205) je způsoben pouze chybou vzorku? Nebo jinak řečeno, jaká je pravděpodobnost, že při větším testu nový klub nevykáže nárůst dostřelu oproti současnému dlouhodobému průměru 205 yardů?

Můžeme to otestovat vygenerováním z-hodnoty. Standardní chyba průměru:

Pak z-hodnota:

Potřebujeme najít pravděpodobnost, že výběrový průměr bude 2,5σ vzdálen od průměru populace. Pokud je pravděpodobnost malá, tak rozdíly nejsou způsobeny náhodou, ale kvalitou nového klubu. V Excelu neexistuje žádná hotová funkce pro určení pravděpodobnosti z-skóre. Můžete však použít vzorec =1-NORM.ST.DIST(hodnota z, TRUE), kde NORM.ST.DIST() vrátí oblast pod normální křivkou nalevo od hodnoty z (obrázek 2). .

Rýže. 2. Funkce NORM.S.DIST() vrátí oblast pod křivkou nalevo od hodnoty z; Chcete-li obrázek zvětšit, klikněte na něj pravým tlačítkem a vyberte Otevřít obrázek na nové kartě

Druhý argument funkce NORM.S.DIST() může nabývat dvou hodnot: TRUE - funkce vrací oblast pod křivkou vlevo od bodu určeného prvním argumentem; FALSE - Funkce vrací výšku křivky v bodě daném prvním argumentem.

Nejsou-li známy průměr (μ) a směrodatná odchylka (σ) základního souboru, použije se t-hodnota (viz ). Struktury skóre z a t se liší v tom, že k nalezení hodnoty t se používá standardní odchylka s získaná z výsledků vzorku, spíše než známá hodnota parametru populace σ. Normální křivka má jediný tvar a tvar rozložení t-hodnot se liší v závislosti na počtu stupňů volnosti df (z angl. stupně svobody) vzorku, který představuje. Počet stupňů volnosti vzorku je n-1, Kde n- velikost vzorku (obr. 3).

Rýže. 3. Tvar t-rozdělení, která vznikají, když je parametr σ neznámý, se liší od tvaru normálního rozdělení

Excel má dvě funkce pro t-rozdělení, nazývané také Studentovo t-rozdělení: STUDENT.DIST() vrací oblast pod křivkou vlevo od dané t-hodnoty a STUDENT.DIST.RT() vpravo.

Kapitola 2. Korelace

Korelace je míra závislosti mezi prvky množiny uspořádaných párů. Charakterizuje se korelace Pearsonovy korelační koeficienty– r. Koeficient může nabývat hodnot v rozsahu od -1,0 do +1,0.

Kde S x A Sy jsou standardní odchylky proměnných X A Y, Sexy- kovariance:

V tomto vzorci se kovariance dělí standardními odchylkami proměnných X A Y, čímž se z kovariance odstraní efekty škálování související s jednotkou. Excel používá funkci CORREL(). Název této funkce neobsahuje kvalifikační prvky G a C, které se používají v názvech funkcí jako STDEV(), VARV() nebo COVARIANCE(). Přestože výběrový korelační koeficient poskytuje zkreslený odhad, důvod zkreslení je jiný než v případě rozptylu nebo směrodatné odchylky.

V závislosti na velikosti obecného korelačního koeficientu (často označovaného řeckým písmenem ρ ), korelační koeficient r poskytuje zkreslený odhad, přičemž účinek zkreslení se zvyšuje s klesající velikostí vzorku. Přesto se nesnažíme toto zkreslení korigovat stejným způsobem, jako jsme to dělali například při výpočtu směrodatné odchylky, kdy jsme do odpovídajícího vzorce dosazovali nikoli počet pozorování, ale počet stupňů volnosti. Ve skutečnosti nemá počet pozorování použitých k výpočtu kovariance žádný vliv na velikost.

Standardní korelační koeficient je navržen pro použití s proměnnými, které spolu souvisí lineárním vztahem. Přítomnost nelinearity a/nebo chyb v datech (odlehlé hodnoty) vedou k nesprávnému výpočtu korelačního koeficientu. Pro diagnostiku problémů s daty se doporučují bodové grafy. Toto je jediný typ grafu v Excelu, který zachází s horizontální i vertikální osou jako s osou hodnot. Spojnicový graf naopak definuje jeden ze sloupců jako osu kategorií, což zkresluje obraz dat (obr. 4).

Rýže. 4. Regresní přímky se zdají být stejné, ale porovnejte jejich rovnice mezi sebou

Pozorování použitá k vytvoření spojnicového grafu jsou ve stejné vzdálenosti podél vodorovné osy. Popisky dělení podél této osy jsou pouze popisky, nikoli číselné hodnoty.

Zatímco korelace často znamená, že existuje kauzální vztah, nelze ji použít jako důkaz, že tomu tak je. Statistiky se nepoužívají k prokázání, zda je teorie pravdivá nebo nepravdivá. Vyloučit konkurenční vysvětlení výsledků pozorování plánované experimenty. Statistiky se také používají ke shrnutí informací shromážděných během takových experimentů a ke kvantifikaci pravděpodobnosti, že rozhodnutí může být chybné vzhledem k důkazní základně.

Kapitola 3 Jednoduchá regrese

Pokud spolu dvě proměnné souvisí tak, že hodnota korelačního koeficientu je větší než řekněme 0,5, pak je možné předpovědět (s určitou přesností) neznámou hodnotu jedné proměnné ze známé hodnoty druhé. Chcete-li získat předpokládané hodnoty ceny, na základě údajů uvedených na obr. 5, můžete použít některý z několika možných způsobů, ale téměř jistě nepoužijete ten, který je znázorněn na obr. 5. Přesto byste si ji měli přečíst, protože žádný jiný způsob nemůže prokázat vztah mezi korelací a predikcí tak jasně jako tento. Na Obr. 5, v rozsahu B2:C12, je náhodný vzorek deseti domů a poskytuje údaje o ploše každého domu (ve čtverečních stopách) a jeho prodejní ceně.

Rýže. 5. Předpovědi prodejních cen tvoří přímku

Najděte průměry, směrodatné odchylky a korelační koeficient (rozsah A14:C18). Vypočítejte plošné z-skóre (E2:E12). Například buňka E3 obsahuje vzorec: =(B3-$B$14)/$B$15. Vypočítejte z-skóre prognózované ceny (F2:F12). Například buňka F3 obsahuje vzorec: =E3*$B$18. Převeďte z-skóre na dolarové ceny (H2:H12). V buňce HZ je vzorec: =F3*$C$15+$C$14.

Všimněte si, že predikovaná hodnota má vždy tendenci se posouvat směrem k průměru, který je 0. Čím blíže je korelační koeficient nule, tím blíže je predikované z-skóre nule. V našem příkladu je korelační koeficient mezi plochou a prodejní cenou 0,67 a prognózní cena je 1,0*0,67, tzn. 0,67. To odpovídá překročení hodnoty nad průměrnou hodnotu rovnající se dvěma třetinám směrodatné odchylky. Pokud by byl korelační koeficient roven 0,5, pak by prognózovaná cena byla 1,0 * 0,5, tzn. 0,5. To odpovídá překročení hodnoty nad průměrnou hodnotu rovnající se pouze polovině směrodatné odchylky. Kdykoli se hodnota korelačního koeficientu liší od ideální, tzn. větší než -1,0 a menší než 1,0, odhad prediktorové proměnné by měl být blíže její střední hodnotě než odhad prediktorové (nezávislé) proměnné její vlastní. Tento jev se nazývá regrese k průměru nebo jednoduše regrese.

V Excelu existuje několik funkcí pro určení koeficientů rovnice regresní čáry (v Excelu se nazývá trendová čára) y=kx + b. Pro určení k slouží funkci

=SLOPE(známé_y-hodnoty; známé_x-hodnoty)

Tady na je predikovaná proměnná a X je nezávislá proměnná. Toto pořadí proměnných musíte striktně dodržovat. Sklon regresní přímky, korelační koeficient, směrodatné odchylky proměnných a kovariance spolu úzce souvisí (obr. 6). Funkce INTERCEPT() vrací hodnotu oříznutou regresní přímkou na svislé ose:

= INTERCUT(známé_y-hodnoty; známé_x-hodnoty)

Rýže. 6. Poměr mezi standardními odchylkami převádí kovarianci na korelační koeficient a sklon regresní přímky

Všimněte si, že počet hodnot x a y poskytnutých funkcím SLOPE() a INTERCEPT() jako argumenty musí být stejný.

V regresní analýze se používá další důležitý ukazatel - R 2 (R-kvadrát), neboli koeficient determinace. Určuje, jaký příspěvek k celkové variabilitě dat má vztah mezi X A na. Excel má pro něj funkci QVPIRSON(), která přebírá přesně stejné argumenty jako funkce CORREL().

Dvě proměnné s nenulovým korelačním koeficientem mezi nimi údajně vysvětlují rozptyl nebo mají rozptyl vysvětleny. Vysvětlený rozptyl je obvykle vyjádřen v procentech. Tak R 2 = 0,81 znamená, že je vysvětleno 81 % rozptylu (rozptyl) dvou proměnných. Zbývajících 19 % je způsobeno náhodnými výkyvy.

Excel má funkci TREND, která zjednodušuje výpočty. Funkce TREND():

přebírá známé hodnoty, které poskytujete X a známé hodnoty na;
vypočítá sklon regresní přímky a konstanty (segmentu);
vrací prediktivní hodnoty na určeno aplikací regresní rovnice na známé hodnoty X(obr. 7).

Funkce TREND() je funkce pole (pokud jste se s takovými funkcemi ještě nesetkali, doporučuji).

Rýže. 7. Použití funkce TREND() umožňuje urychlit a zjednodušit výpočty ve srovnání s použitím dvojice funkcí SLOPE() a INTERCEPT()

Chcete-li zadat funkci TREND() jako maticový vzorec v buňkách G3:G12, vyberte rozsah G3:G12, zadejte vzorec TREND (SZ:S12;VZ:B12), stiskněte a podržte klávesy a teprve poté stiskněte klávesu . Všimněte si, že vzorec je uzavřen ve složených závorkách: ( a ). Takto vám Excel říká, že tento vzorec je vnímán jako maticový vzorec. Závorky nevkládejte sami: pokud se je pokusíte zadat sami jako součást vzorce, Excel bude váš vstup považovat za normální textový řetězec.

Funkce TREND() má další dva argumenty: new_values_x A konst. První umožňuje sestavit předpověď pro budoucnost a druhý může přinutit regresní přímku projít počátkem (hodnota TRUE říká Excelu, aby použil vypočítanou konstantu, hodnota FALSE - konstanta = 0). Excel umožňuje nakreslit regresní čáru do grafu tak, aby procházela počátkem. Začněte vykreslením bodového grafu a poté klikněte pravým tlačítkem na jednu ze značek datové řady. Vyberte položku v kontextové nabídce, která se otevře. Přidat trendovou linii; Vyberte možnost Lineární; v případě potřeby posuňte panel dolů a zaškrtněte políčko Postavte křižovatku; ujistěte se, že jeho přidružené textové pole je nastaveno na 0,0.

Pokud máte tři proměnné a chcete určit korelaci mezi dvěma z nich a vyloučit vliv třetí, můžete použít částečná korelace. Předpokládejme, že vás zajímá vztah mezi procentem obyvatel města, kteří dokončili vysokou školu, a počtem knih v městských knihovnách. Nasbírali jste data pro 50 měst, ale... Problém je, že oba tyto parametry mohou záviset na pohodě obyvatel konkrétního města. Je samozřejmě velmi obtížné najít dalších 50 měst, která se vyznačují naprosto stejnou úrovní blahobytu obyvatel.

Použitím statistických metod k odstranění vlivu blahobytu jak na podporu knihoven, tak na vysokoškolské vzdělání, byste mohli lépe kvantifikovat vztah mezi proměnnými, které vás zajímají, konkrétně počtem knih a počtem absolventů. Tato podmíněná korelace mezi dvěma proměnnými, kdy jsou hodnoty ostatních proměnných pevné, se nazývá částečná korelace. Jedním ze způsobů, jak to vypočítat, je použít rovnici:

Kde rCB . W- korelační koeficient mezi proměnnými College (College) a Books (Books) s vyloučeným vlivem (fixní hodnotou) proměnné Wealth (Wealth); rCB- korelační koeficient mezi proměnnými College a Books; rCW- korelační koeficient mezi proměnnými College a Welfare; rb.w.- korelační koeficient mezi proměnnými Books a Welfare.

Na druhou stranu parciální korelaci lze vypočítat na základě reziduální analýzy, tzn. rozdíly mezi předpokládanými hodnotami a jejich souvisejícími skutečnými pozorováními (obě metody jsou znázorněny na obrázku 8).

Rýže. 8. Parciální korelace jako reziduální korelace

Pro zjednodušení výpočtu matice korelačních koeficientů (B16: E19) použijte analytický balíček Excel (menu Data –> Analýza –> Analýza dat). Ve výchozím nastavení není tento balíček v Excelu aktivní. Chcete-li jej nainstalovat, přejděte do nabídky Soubor –> Možnosti –> doplňky. Ve spodní části okna, které se otevře Možnostivynikat najít pole Řízení, vyberte doplňkyvynikat, klikněte Jít. Zaškrtněte políčko vedle doplňku Balíček analýzy. Klikněte na A analýza dat, Vyberte možnost Korelace. Zadejte $B$2:$D$13 jako vstupní interval, zaškrtněte políčko Štítky na prvním řádku, zadejte $B$16:$E$19 jako výstupní interval.

Další možností je definovat semiparciální korelaci. Zkoumáte například vliv výšky a věku na váhu. Máte tedy dvě predikční proměnné, výšku a věk, a jednu prediktivní proměnnou, váhu. Chcete vyloučit vliv jedné proměnné prediktoru na jinou, ale ne vliv na proměnnou prediktoru:

kde H - výška (výška), W - hmotnost (váha), A - věk (věk); Index semi-parciálního korelačního koeficientu používá závorky k označení, která proměnná je eliminována az které proměnné. V tomto případě zápis W(H.A) označuje, že vliv proměnné Věk je odstraněn z proměnné Výška, ale nikoli z proměnné Hmotnost.

Člověk by mohl nabýt dojmu, že diskutovaná problematika není nijak zásadní. Ostatně nejdůležitější je, jak přesně obecná regresní rovnice funguje, přičemž problém relativních příspěvků jednotlivých proměnných k celkovému vysvětlenému rozptylu se zdá být druhořadý. To však není tento případ. Jakmile začnete přemýšlet o tom, zda použít nebo nepoužít nějakou proměnnou v rovnici vícenásobné regrese, problém se stává důležitým. Může ovlivnit posouzení správnosti výběru modelu pro analýzu.

Kapitola 4. Funkce LINREGRESE().

Funkce LINREGRESE() vrátí 10 statistik regresní analýzy. Funkce LINREGRESE() je maticová funkce. Chcete-li jej zadat, vyberte rozsah obsahující pět řádků a dva sloupce, zadejte vzorec a stiskněte (obr. 9):

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

Rýže. 9. Funkce LINREGRESE(): a) vyberte rozsah D2:E6, b) zadejte vzorec, jak je znázorněno na řádku vzorců, c) klikněte

Funkce LINREGRESE() vrací:

regresní koeficient (nebo sklon, buňka D2);
segment (nebo konstanta, buňka E3);
standardní chyby regresního koeficientu a konstant (rozsah D3:E3);
koeficient determinace R 2 pro regresi (buňka D4);
standardní chyba odhadu (buňka E4);
F-test pro úplnou regresi (buňka D5);
počet stupňů volnosti pro zbytkový součet čtverců (buňka E5);
regresní součet čtverců (buňka D6);
zbytkový součet čtverců (buňka E6).

Podívejme se na každou z těchto statistik a jejich vzájemné působení.

standardní chyba v našem případě je to směrodatná odchylka vypočtená pro výběrové chyby. To znamená, že jde o situaci, kdy obecná populace má jednu statistiku a vzorek jinou. Vydělením regresního koeficientu směrodatnou chybou získáte hodnotu 2,092/0,818 = 2,559. Jinými slovy, regresní koeficient 2,092 je dvě a půl standardní chyby od nuly.

Pokud je regresní koeficient nulový, pak nejlepším odhadem predikované proměnné je její průměr. Dvě a půl standardní chyby je poměrně velké číslo a můžete bezpečně předpokládat, že regresní koeficient pro populaci má nenulovou hodnotu.

Pravděpodobnost získání výběrového regresního koeficientu 2,092, pokud je jeho skutečná hodnota v základním souboru 0,0, můžete určit pomocí funkce

STUDENT.DIST.PH (t-test = 2,559; počet stupňů volnosti = 18)

Obecně platí, že počet stupňů volnosti = n - k - 1, kde n je počet pozorování a k je počet prediktorových proměnných.

Tento vzorec vrátí hodnotu 0,00987 nebo zaokrouhlenou nahoru na 1 %. Říká nám toto: pokud je regresní koeficient pro populaci 0 %, pak pravděpodobnost získání vzorku 20 lidí, pro který je vypočtená hodnota regresního koeficientu 2,092, je skromné 1 %.

F-test (buňka D5 na obrázku 9) plní stejnou funkci ve vztahu k plné regresi jako t-test ve vztahu k jednoduchému párovému regresnímu koeficientu. F-test se používá k testování, zda je koeficient determinace R 2 pro regresi skutečně dostatečně velký, aby zamítl hypotézu, že má v populaci hodnotu 0,0, což ukazuje na absenci rozptylu vysvětlovaného prediktorem a prediktorovou proměnnou. . Pokud existuje pouze jedna prediktorová proměnná, F-test se přesně rovná druhé mocnině t-testu.

Dosud jsme uvažovali intervalové proměnné. Pokud máte proměnné, které mohou nabývat více hodnot, což jsou jednoduchá jména, jako je muž a žena nebo plaz, obojživelník a ryba, zastupujte je jako číselný kód. Takové proměnné se nazývají nominální.

statistika R2 kvantifikuje podíl vysvětleného rozptylu.

Směrodatná chyba odhadu. Na Obr. Tabulka 4.9 ukazuje předpokládané hodnoty proměnné Hmotnost, získané na základě jejího vztahu s proměnnou Výška. Rozsah E2:E21 obsahuje hodnoty zbytků pro proměnnou Hmotnost. Přesněji řečeno, tato rezidua se nazývají chyby - proto následuje termín standardní chyba odhadu.

Rýže. 10. R 2 i směrodatná chyba odhadu vyjadřují přesnost předpovědí získaných pomocí regrese

Čím menší je standardní chyba odhadu, tím přesnější je regresní rovnice a tím více očekáváte, že jakákoli předpověď z rovnice bude odpovídat skutečnému pozorování. Standardní chyba odhadu poskytuje způsob, jak tato očekávání kvantifikovat. Hmotnost 95 % lidí s určitou výškou bude v rozmezí:

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

F-statistika je poměr meziskupinového rozptylu k vnitroskupinovému rozptylu. Tento název zavedl statistik George Snedecor na počest Sira, který na počátku 20. století vyvinul analýzu rozptylu (ANOVA, Analysis of Variance).

Koeficient determinace R 2 vyjadřuje podíl celkového součtu čtverců souvisejících s regresí. Hodnota (1 - R 2) vyjadřuje podíl celkového součtu čtverců spojených s rezidui - chybami predikce. F-test lze získat pomocí funkce LINEST (buňka F5 na obr. 11), pomocí součtů čtverců (rozsah G10:J11), pomocí zlomků rozptylu (rozsah G14:J15). Vzorce lze studovat v přiloženém souboru Excel.

Rýže. 11. Výpočet F-kritéria

Při použití nominálních proměnných se používá dummy kódování (obr. 12). Pro kódování hodnot je vhodné použít hodnoty 0 a 1. Pravděpodobnost F se vypočítá pomocí funkce:

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

Zde funkce F.DIST.RT() vrací pravděpodobnost získání F-testu následujícího po centrálním F-rozdělení (obr. 13) pro dva datové sady se stupni volnosti uvedenými v buňkách I2 a I3, jejichž hodnota je stejná jako hodnota uvedená v buňce K2.

Rýže. 12. Regresní analýza pomocí fiktivních proměnných

Rýže. 13. Centrální F-rozdělení pro λ = 0

Kapitola 5 Vícenásobná regrese

Když přejdete z jednoduché párové regrese s jednou proměnnou prediktoru k vícenásobné regresi, přidáte jednu nebo více proměnných prediktoru. Uložte hodnoty proměnných prediktoru do sousedních sloupců, jako jsou sloupce A a B pro dva prediktory nebo A, B a C pro tři prediktory. Před zadáním vzorce, který obsahuje funkci LINREGRESE(), vyberte pět řádků a tolik sloupců, kolik je proměnných prediktoru, plus jeden pro konstantu. V případě regrese se dvěma prediktorovými proměnnými lze použít následující strukturu:

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

Podobně v případě tří proměnných:

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

Řekněme, že chcete studovat možný vliv věku a stravy na hladiny LDL, lipoproteinů s nízkou hustotou, o kterých se předpokládá, že jsou zodpovědné za tvorbu aterosklerotických plátů, které způsobují aterotrombózu (obrázek 14).

Rýže. 14. Vícenásobná regrese

R2 vícenásobné regrese (zobrazené v buňce F13) je větší než R2 libovolné jednoduché regrese (E4, H4). Vícenásobná regrese využívá více proměnných prediktoru současně. V tomto případě se R2 téměř vždy zvyšuje.

Pro jakoukoli jednoduchou lineární regresní rovnici s jednou proměnnou prediktoru bude vždy existovat dokonalá korelace mezi hodnotami prediktoru a hodnotami proměnných prediktoru, protože v takové rovnici se hodnoty prediktoru vynásobí jednou konstantou a přidá se další konstanta. ke každému produktu. Tento efekt není zachován při vícenásobné regresi.

Zobrazení výsledků vrácených funkcí LINREGRESE() pro vícenásobnou regresi (obrázek 15). Regresní koeficienty se zobrazují jako součást výsledků vrácených funkcí LINREGRESE() v opačném pořadí proměnných(G–H–I odpovídá C–B–A).

Rýže. 15. Koeficienty a jejich směrodatné chyby jsou na pracovním listu zobrazeny v opačném pořadí.

Principy a postupy používané v regresní analýze s jedinou prediktorovou proměnnou lze snadno přizpůsobit tak, aby zohledňovaly více prediktorových proměnných. Ukazuje se, že velká část této adaptace závisí na eliminaci vlivu prediktorových proměnných na sebe navzájem. Ten je spojen se soukromými a polosoukromými korelacemi (obr. 16).

Rýže. 16. Vícenásobnou regresi lze vyjádřit pomocí párové regrese reziduí (viz vzorce v souboru Excel)

V Excelu jsou funkce, které poskytují informace o t- a F-distribucích. Funkce, jejichž názvy obsahují část DIST, například STUDENT.DIST() a F.DIST(), berou jako argument t- nebo F-test a vrací pravděpodobnost dodržení zadané hodnoty. Funkce, jejichž názvy obsahují část OBR, jako například STUDENT.INV() a F.INV(), berou jako argument hodnotu pravděpodobnosti a vrací hodnotu kritéria odpovídající zadané pravděpodobnosti.

Protože hledáme kritické hodnoty t-rozdělení, které odřízne okraje jeho koncových oblastí, předáme 5 % jako argument jedné z funkcí STUDENT.INV(), která vrátí hodnotu odpovídající této pravděpodobnosti. (obr. 17, 18).

Rýže. 17. Dvoustranný t-test

Rýže. 18. Jednostranný t-test

Zavedením rozhodovacího pravidla v případě jednostranné alfa oblasti zvýšíte statistickou sílu testu. Pokud jste si při zahájení experimentu jisti, že máte všechny důvody očekávat pozitivní (nebo negativní) regresní koeficient, měli byste provést jednostranný test. V tomto případě bude vyšší pravděpodobnost, že se rozhodnete správně a zamítnete hypotézu nulového regresního koeficientu v populaci.

Statistici raději používají tento termín řízený test místo termínu test jednoho ocasu a termín neřízený test místo termínu dvoustranný test. Termíny směrový a nesměrový jsou vhodnější, protože zdůrazňují spíše typ hypotézy než povahu koncových bodů rozdělení.

Přístup k hodnocení vlivu prediktorů na základě srovnání modelů. Na Obr. 19 ukazuje výsledky regresní analýzy, která testuje příspěvek proměnné Dieta k regresní rovnici.

Rýže. 19. Porovnání dvou modelů kontrolou rozdílů v jejich výsledcích

Výsledky LINREGRESE() (rozsah H2:K6) souvisí s tím, co nazývám úplným modelem, který regresuje proměnnou LDL na stravě, věku a HDL. V rozsahu H9:J13 jsou výpočty uvedeny bez zohlednění predikční proměnné Dieta. Říkám tomu limitovaný model. V úplném modelu je 49,2 % rozptylu v LDL závislé proměnné vysvětleno prediktorovými proměnnými. V omezeném modelu je pouze 30,8 % LDL vysvětleno věkem a HDL. Ztráta R 2 v důsledku vyloučení proměnné Dieta z modelu je 0,183. V rozsahu G15:L17 byly provedeny výpočty, které ukazují, že pouze s pravděpodobností 0,0288 je vliv proměnné Dieta náhodný. Ve zbývajících 97,1 % má dieta vliv na LDL.

Kapitola 6. Předpoklady a upozornění týkající se regresní analýzy

Pojem „předpoklad“ není striktně definován a způsob jeho použití naznačuje, že pokud předpoklad není splněn, jsou výsledky celé analýzy přinejmenším sporné nebo možná neplatné. Ve skutečnosti tomu tak není, i když jistě existují případy, kdy porušení předpokladu zásadně změní obraz. Hlavní předpoklady jsou: a) rezidua proměnné Y jsou normálně distribuována v libovolném bodě X podél regresní přímky; b) hodnoty Y jsou lineárně závislé na hodnotách X; c) rozptyl reziduí je přibližně stejný v každém bodě X; d) mezi zbytky není žádný vztah.

Pokud předpoklady nehrají významnou roli, hovoří statistici o robustnosti analýzy ve vztahu k porušení předpokladu. Zejména když použijete regresi k testování rozdílů mezi průměry skupin, nezáleží na předpokladu, že hodnoty Y – a tedy i rezidua – jsou normálně rozděleny: testy jsou odolné proti porušení předpokladu normality. Je důležité analyzovat data pomocí grafů. Například součástí doplňku Analýza dat nástroj Regrese.

Pokud data neodpovídají předpokladům lineární regrese, máte k dispozici jiné nelineární přístupy. Jednou z nich je logistická regrese (obr. 20). V blízkosti horní a dolní meze prediktorové proměnné vede lineární regrese k nerealistickým předpovědím.

Rýže. 20. Logistická regrese

Na Obr. Obrázek 6.8 ukazuje výsledky dvou metod analýzy dat zaměřené na zkoumání vztahu mezi ročním příjmem a pravděpodobností koupě domu. Je zřejmé, že pravděpodobnost nákupu se bude zvyšovat s rostoucím příjmem. Grafy usnadňují zjištění rozdílů mezi výsledky předpovídajícími pravděpodobnost koupě domu pomocí lineární regrese a výsledky, které byste mohli získat pomocí jiného přístupu.

Ve statistické řeči se odmítnutí nulové hypotézy, když je ve skutečnosti pravdivá, nazývá chyba I. typu.

V doplňku Analýza dat je nabízen praktický nástroj pro generování náhodných čísel, který umožňuje uživateli specifikovat požadovaný tvar rozdělení (například Normální, Binomický nebo Poissonův), stejně jako střední a standardní odchylku.

Rozdíly mezi funkcemi rodiny STUDENT.DIST(). Počínaje Excelem 2010 jsou k dispozici tři různé formy funkce, které vracejí zlomek rozdělení vlevo a/nebo vpravo od dané hodnoty t-testu. Funkce STUDENT.DIST() vrátí poměr plochy pod distribuční křivkou nalevo od zadané hodnoty t-testu. Řekněme, že máte 36 pozorování, takže počet stupňů volnosti k analýze je 34 a hodnota t-testu je 1,69. V tomto případě vzorec

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

vrátí hodnotu 0,05 nebo 5 % (obrázek 21). Třetí argument STUDENT.DIST() může být TRUE nebo FALSE. Pokud je nastaveno na hodnotu TRUE, funkce vrací kumulativní plochu pod křivkou nalevo od daného t-testu, vyjádřenou jako zlomek. Pokud je NEPRAVDA, funkce vrátí relativní výšku křivky v bodě odpovídajícím t-testu. Jiné verze funkce STUDENT.DIST() - STUDENT.DIST.PX() a STUDENT.DIST.2X() - berou jako argumenty pouze hodnotu t-testu a počet stupňů volnosti a nevyžadují třetí argument .

Rýže. 21. Tmavší zastíněná oblast na levém konci distribuce odpovídá podílu plochy pod křivkou nalevo od velké pozitivní hodnoty t-testu.

K určení oblasti napravo od t-testu použijte jeden ze vzorců:

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

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

Celková plocha pod křivkou musí být 100 %, takže odečtením zlomku plochy nalevo od hodnoty t-testu vrácené funkcí od 1 dostaneme zlomek plochy napravo od hodnoty t-testu. Možná bude výhodnější přímo získat část plochy, která vás zajímá, pomocí funkce STUDENT.DIST.RH(), kde RH znamená pravý konec rozdělení (obr. 22).

Rýže. 22. 5% alfa plocha pro směrový test

Použití funkcí STUDENT.DIST() nebo STUDENT.DIST.PH() znamená, že jste vybrali řízenou pracovní hypotézu. Směrová pracovní hypotéza v kombinaci s nastavením hodnoty alfa na 5 % znamená, že všech 5 % umístíte na pravý konec distribuce. Nulu hypotézu budete muset zamítnout pouze v případě, že pravděpodobnost získání hodnoty vašeho t-testu je 5 % nebo méně. Směrové hypotézy obvykle vedou k citlivějším statistickým testům (tato větší citlivost je také označována jako větší statistická síla).

U neřízeného testu zůstává hodnota alfa na stejné úrovni 5 %, ale rozložení bude jiné. Protože musíte povolit dva výsledky, pravděpodobnost falešně pozitivního výsledku musí být rozdělena mezi dva konce rozdělení. Obecně se uznává, že tato pravděpodobnost je rozdělena rovnoměrně (obr. 23).

Pomocí stejné získané hodnoty t-testu a stejného počtu stupňů volnosti jako v předchozím příkladu použijte vzorec

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

Z žádného konkrétního důvodu vrací funkce STUDENT.DIST.2X() chybový kód #NUM!, pokud je jako první argument uvedena záporná hodnota t-testu.

Pokud vzorky obsahují různé počty dat, použijte dvouvzorkový t-test s různými rozptyly, který je součástí balení Analýza dat.

Kapitola 7 Použití regrese k testování rozdílů mezi průměry skupin

Proměnné dříve označované jako prediktivní proměnné budou v této kapitole označovány jako výsledné proměnné a místo prediktorových proměnných bude použit termín faktorové proměnné.

Nejjednodušší přístup ke kódování nominální proměnné je falešné kódování(obr. 24).

Rýže. 24. Regresní analýza založená na fiktivním kódování

Při použití fiktivního kódování jakéhokoli druhu je třeba dodržovat následující pravidla:

Počet sloupců vyhrazených pro nová data se musí rovnat počtu úrovní faktoru mínus
Každý vektor představuje jednu úroveň faktoru.
Subjekty na jedné úrovni, což je často kontrolní skupina, obdrží kód 0 na všech vektorech.

Vzorec v buňkách F2:H6 =LINEST(A2:A22;C2:D22;;TRUE) vrátí statistiku regrese. Pro srovnání na Obr. 24 ukazuje výsledky tradiční analýzy rozptylu vrácené nástrojem Jednosměrná analýza rozptylu nástavby Analýza dat.

Kódování efektů. V jiném typu kódování tzv kódování efektů, průměr každé skupiny se porovná s průměrem průměrů skupiny. Tento aspekt kódování efektů je způsoben použitím -1 místo 0 jako kódu pro skupinu, která přijímá stejný kód ve všech kódových vektorech (obrázek 25).

Rýže. 25. Kódování efektů

Při použití fiktivního kódování je hodnota konstanty vrácená funkcí LINREGRESE() průměrem skupiny přiřazených nulových kódů ve všech vektorech (obvykle kontrolní skupina). V případě kódování efektů je konstanta rovna celkovému průměru (buňka J2).

Obecný lineární model je užitečný způsob, jak konceptualizovat hodnotové složky výsledné proměnné:

Y ij = μ + α j + ε ij

Použití řeckých písmen místo latinských písmen v tomto vzorci zdůrazňuje skutečnost, že se vztahuje na populaci, ze které jsou vzorky čerpány, ale může být přepsáno tak, aby označovalo, že se týká vzorků odebraných z publikované populace:

Y ij = Y̅ + a j + e ij

Myšlenka je taková, že každé pozorování Y ij lze považovat za součet následujících tří složek: celkový průměr, μ; efekt zpracování jaj; hodnotu e ij , která představuje odchylku jednotlivého kvantitativního ukazatele Y ij od kombinované hodnoty celkového průměru a efektu j-tého ošetření (obr. 26). Cílem regresní rovnice je minimalizovat součet čtverců reziduí.

Rýže. 26. Pozorování rozložená na součásti obecného lineárního modelu

Faktorová analýza. Pokud se zkoumá vztah mezi výslednou proměnnou a dvěma nebo více faktory současně, pak se v tomto případě hovoří o použití faktorové analýzy. Přidání jednoho nebo více faktorů k jednosměrné analýze rozptylu může zvýšit statistickou sílu. V jednosměrné ANOVA je variace ve výsledné proměnné, kterou nelze přisoudit faktoru, zahrnuta do zbytkového průměru čtverce. Může se však stát, že tato odchylka souvisí s jiným faktorem. Pak lze tuto odchylku odstranit ze střední kvadratické chyby, jejíž snížení vede ke zvýšení hodnot F-testu, a tím ke zvýšení statistické síly testu. nástavba Analýza dat obsahuje nástroj, který zajišťuje zpracování dvou faktorů současně (obr. 27).

Rýže. 27. Nástroj Dvoucestná analýza rozptylu s balíčkem analýzy opakování

Nástroj analýzy rozptylu použitý na tomto obrázku je užitečný v tom, že vrací střední hodnotu a rozptyl výsledné proměnné a také hodnotu čítače pro každou skupinu zahrnutou v návrhu. Stůl Analýza rozptylu zobrazí dva parametry, které nejsou ve výstupu jednosměrné verze nástroje ANOVA. Věnujte pozornost zdrojům variací Vzorek A sloupců v řádcích 27 a 28. Zdroj variace sloupců odkazuje na pohlaví. Zdroj variace Vzorek odkazuje na jakoukoli proměnnou, jejíž hodnoty zaujímají různé řádky. Na Obr. 27, hodnoty pro skupinu CourseLech1 jsou na řádcích 2-6, skupina CourseLech2 jsou na řádcích 7-11 a skupina CourseLech3 jsou na řádcích 12-16.

Hlavním bodem je, že jak pohlaví (označené Sloupce v buňce E28), tak Léčba (označené Vzorek v buňce E27) jsou zahrnuty v tabulce ANOVA jako zdroje variací. Průměry pro muže se liší od průměrů pro ženy, což vytváří zdroj odchylek. Průměry pro tři ošetření se také liší – zde je další zdroj odchylek. Existuje také třetí zdroj, Interaction, který odkazuje na kombinovaný účinek proměnných Gender a Léčba.

Kapitola 8

Analýza kovariance neboli ANCOVA (Analýza kovariance) snižuje zkreslení a zvyšuje statistickou sílu. Dovolte mi připomenout, že jedním ze způsobů, jak posoudit spolehlivost regresní rovnice, jsou F-testy:

F = MS regrese/MS reziduální

kde MS (střední čtverec) je střední čtverec a indexy Regrese a Residual označují regresní a reziduální složky. MS Reziduum se vypočítá pomocí vzorce:

MS Residual = SS Residual / df Residual

kde SS (součet čtverců) je součet čtverců a df je počet stupňů volnosti. Když do regresní rovnice přidáte kovarianci, určitá část celkového součtu čtverců není zahrnuta v SS ResiduaI , ale v SS Regression . To vede ke snížení SS Residual l, a tudíž MS Residual. Čím menší je MS Residual, tím větší je F-test a tím je pravděpodobnější, že zamítnete nulovou hypotézu, že mezi průměry není žádný rozdíl. V důsledku toho přerozdělíte volatilitu výsledné proměnné. V ANOVA, když se nebere v úvahu kovariance, variabilita jde do chyby. Ale v ANCOVA je část variability dříve připisovaná chybě přiřazena kovariátu a stává se součástí SS regrese.

Vezměme si příklad, kdy je stejný soubor dat analyzován nejprve pomocí ANOVA a poté pomocí ANCOVA (obrázek 28).

Rýže. 28. Analýza ANOVA ukazuje, že výsledky získané pomocí regresní rovnice jsou nespolehlivé

Studie porovnává relativní účinky fyzického cvičení, které rozvíjí svalovou sílu, a kognitivního cvičení (křížovky), které aktivuje mozkovou aktivitu. Subjekty byly náhodně rozděleny do dvou skupin, takže na začátku experimentu byly obě skupiny ve stejných podmínkách. Po třech měsících byly měřeny kognitivní charakteristiky subjektů. Výsledky těchto měření jsou uvedeny ve sloupci B.

Rozsah A2:C21 obsahuje počáteční data předaná funkci LINREGRESE() k provedení analýzy pomocí kódování efektů. Výsledky funkce LINREGRESE() jsou zobrazeny v rozsahu E2:F6, kde buňka E2 zobrazuje regresní koeficient spojený s vektorem dopadu. Buňka E8 obsahuje t-test = 0,93 a buňka E9 testuje spolehlivost tohoto t-testu. Hodnota v buňce E9 udává, že pravděpodobnost, že se setkáte s rozdílem mezi průměry skupiny pozorovanými v tomto experimentu, je 36 %, pokud jsou průměry skupiny v populaci stejné. Jen málokdo považuje tento výsledek za statisticky významný.

Na Obr. Obrázek 29 ukazuje, co se stane, když se do analýzy přidá kovariát. V tomto případě jsem do datové sady přidal věk každého subjektu. Koeficient determinace R 2 pro regresní rovnici, která používá kovariát, je 0,80 (buňka F4). Hodnota R2 v rozsahu F15:G19, ve kterém jsem reprodukoval výsledky ANOVA získané bez použití kovariátu, je pouze 0,05 (buňka F17). Proto regresní rovnice, která zahrnuje kovariát, předpovídá hodnoty proměnné Kognitivní skóre mnohem přesněji než použití samotného vektoru dopadu. U ANCOVA je pravděpodobnost náhodného získání hodnoty F-testu zobrazené v buňce F5 menší než 0,01 %.

Rýže. 29. ANCOVA přináší úplně jiný obrázek

Ukazuje vliv některých hodnot (nezávislých, nezávislých) na závisle proměnnou. Například, jak závisí počet ekonomicky aktivního obyvatelstva na počtu podniků, mzdách a dalších parametrech. Nebo: jak zahraniční investice, ceny energií atd. ovlivňují výši HDP.

Výsledek analýzy umožňuje stanovit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí, činit manažerská rozhodnutí.

Regrese se děje:

lineární (y = a + bx);

parabolický (y = a + bx + cx 2);

exponenciální (y = a * exp(bx));

Mocnina (y = a*x^b);

hyperbolický (y = b/x + a);

logaritmické (y = b * ln(x) + a);

exponenciální (y = a * b^x).

Zvažte příklad vytvoření regresního modelu v Excelu a interpretaci výsledků. Vezměme si lineární typ regrese.

Úkol. U 6 podniků byla analyzována průměrná měsíční mzda a počet zaměstnanců, kteří odešli. Je třeba určit závislost počtu zaměstnanců v důchodu na průměrné mzdě.

Lineární regresní model má následující podobu:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

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

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

Excel má vestavěné funkce, které lze použít k výpočtu parametrů lineárního regresního modelu. Ale doplněk Analysis ToolPak to udělá rychleji.

Aktivujte si výkonný analytický nástroj:

1. Klikněte na tlačítko "Office" a přejděte na záložku "Excel Options". "Doplňky".

2. Níže pod rozevíracím seznamem v poli „Správa“ bude nápis „Doplňky aplikace Excel“ (pokud tam není, klikněte na zaškrtávací políčko vpravo a vyberte). A tlačítko Go. Klikněte.

3. Otevře se seznam dostupných doplňků. Vyberte "Analytický balíček" a klikněte na OK.

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

Nyní se budeme zabývat přímo regresní analýzou.

1. Otevřete nabídku nástroje Analýza dat. Vyberte "Regrese".

2. Otevře se nabídka pro výběr vstupních hodnot a možností výstupu (kde zobrazit výsledek). V polích pro výchozí údaje uvádíme rozsah popisovaného parametru (Y) a faktor, který jej ovlivňuje (X). Zbytek může a nemusí být dokončen.

3. Po kliknutí na OK program zobrazí výpočty na novém listu (můžete zvolit interval zobrazení na aktuálním listu nebo přiřadit výstup do nového sešitu).

V první řadě věnujeme pozornost R-kvadrátu a koeficientům.

R-kvadrát je koeficient determinace. V našem příkladu je to 0,755 nebo 75,5 %. To znamená, že vypočtené parametry modelu vysvětlují vztah mezi studovanými parametry ze 75,5 %. Čím vyšší je koeficient determinace, tím lepší je model. Dobré - nad 0,8. Špatná - méně než 0,5 (takovou analýzu lze stěží považovat za rozumnou). V našem příkladu - "není špatné".

Koeficient 64,1428 ukazuje, jaké bude Y, pokud se všechny proměnné v uvažovaném modelu rovnají 0. To znamená, že hodnotu analyzovaného parametru ovlivňují i další faktory, které nejsou v modelu popsány.

Koeficient -0,16285 ukazuje váhu proměnné X na Y. To znamená, že průměrná měsíční mzda v rámci tohoto modelu ovlivňuje počet odcházejících s váhou -0,16285 (to je malá míra vlivu). Znak „-“ označuje negativní dopad: čím vyšší plat, tím méně odvykání. Což je spravedlivé.

Jedním z ukazatelů popisujících kvalitu sestrojeného modelu ve statistice je koeficient determinace (R^2), kterému se také říká aproximační hodnota spolehlivosti. Lze jej použít k určení úrovně přesnosti předpovědi. Pojďme zjistit, jak můžete tento ukazatel vypočítat pomocí různých nástrojů aplikace Excel.

V závislosti na úrovni koeficientu determinace je obvyklé rozdělit modely do tří skupin:

0,8 - 1 - kvalitní model;
0,5 - 0,8 - model přijatelné kvality;
0 - 0,5 - nekvalitní model.

V druhém případě kvalita modelu ukazuje na nemožnost jeho použití pro prognózování.

Jak Excel vypočítá zadanou hodnotu, závisí na tom, zda je regrese lineární nebo ne. V prvním případě můžete funkci použít QVPIRSON a ve druhém budete muset použít speciální nástroj z analytického balíčku.

Metoda 1: výpočet koeficientu determinace pro lineární funkci

Nejprve si pojďme zjistit, jak najít koeficient determinace pro lineární funkci. V tomto případě se tento ukazatel bude rovnat druhé mocnině korelačního koeficientu. Spočítejme si to pomocí vestavěné funkce Excel na příkladu konkrétní tabulky, která je uvedena níže.

Metoda 2: výpočet koeficientu determinace v nelineárních funkcích

Ale výše uvedená možnost pro výpočet požadované hodnoty může být aplikována pouze na lineární funkce. Co je třeba udělat pro jeho výpočet v nelineární funkci? Tuto možnost má i Excel. To lze provést pomocí nástroje "Regrese", který je součástí balení "Analýza dat".

Před použitím tohoto nástroje byste jej však měli aktivovat sami "Analytický balíček" která je ve výchozím nastavení v Excelu zakázána. Přesun na kartu "Soubor" a poté položku projděte "Možnosti".

V okně, které se otevře, přejděte do sekce "Doplňky" procházením levé svislé nabídky. Ve spodní části pravé části okna je pole "Řízení". Ze seznamu dostupných podsekcí vyberte název "Doplňky aplikace Excel..." a poté klikněte na tlačítko "Jít..." umístěna napravo od pole.

Otevře se okno doplňků. V jeho centrální části je seznam dostupných doplňků. Zaškrtněte políčko vedle pozice "Analytický balíček". Následuje kliknutí na tlačítko OK na pravé straně rozhraní okna.

Balíček nářadí "Analýza dat" v aktuální instanci Excelu bude aktivován. Přístup k němu se nachází na pásu karet v záložce "Data". Přejděte na zadanou kartu a klikněte na tlačítko "Analýza dat" ve skupině nastavení "Analýza".

Okno je aktivováno "Analýza dat" se seznamem specializovaných nástrojů pro zpracování informací. Vyberte položku z tohoto seznamu. "Regrese" a klikněte na tlačítko OK.

Poté se otevře okno nástroje "Regrese". První sada nastavení "Vstupní data". Zde ve dvou polích musíte zadat adresy rozsahů, kde se nacházejí hodnoty argumentu a funkce. Umístěte kurzor do pole "Interval vstupu Y" a vyberte obsah sloupce na listu "Y". Poté, co se v okně zobrazí adresa pole "Regrese", umístěte kurzor do pole "Interval vstupu Y" a stejným způsobem vyberte buňky sloupce "X".
O možnostech "Označit" A "Konstantní nula" nezaškrtávejte políčka. Zaškrtávací políčko lze nastavit vedle parametru "Úroveň spolehlivosti" a v protějším poli uveďte požadovanou hodnotu odpovídajícího indikátoru (standardně 95 %).

Ve skupině "Možnosti výstupu" je třeba určit, ve které oblasti se zobrazí výsledek výpočtu. Existují tři možnosti:
- Oblast na aktuálním listu;
- Další list;
- Další kniha (nový soubor).
Zastavme naši volbu u první možnosti, aby počáteční data a výsledek byly umístěny na stejném listu. Umístěte přepínač vedle parametru "Interval ukončení". Umístěte kurzor do pole vedle této položky. Klikneme levým tlačítkem myši na prázdný prvek na listu, který se má stát levou horní buňkou výstupní tabulky výsledků výpočtu. Adresa tohoto prvku by měla být zvýrazněna v poli okna "Regrese".

Skupiny parametrů "Zůstává" A "Normální pravděpodobnost" jsou ignorovány, protože nejsou důležité pro řešení problému. Poté klikněte na tlačítko OK umístěný v pravém horním rohu okna "Regrese".

Program počítá na základě dříve zadaných dat a zobrazí výsledek v určeném rozsahu. Jak vidíte, tento nástroj zobrazuje na listu poměrně velké množství výsledků pro různé parametry. V kontextu aktuální lekce nás ale zajímá ukazatel "R-čtverec". V tomto případě se rovná 0,947664, což charakterizuje vybraný model jako model dobré kvality.

Metoda 3: koeficient determinace pro trendovou linii

Kromě výše uvedených možností lze koeficient determinace zobrazit přímo pro spojnici trendu v grafu postaveném na listu Excel. Pojďme zjistit, jak to lze provést na konkrétním příkladu.

Máme graf založený na tabulce argumentů a hodnot funkce, která byla použita pro předchozí příklad. Postavme k tomu trendovou linii. Levým tlačítkem myši klikneme na libovolné místo konstrukční plochy, na které je graf umístěn. V tomto případě se na pásu karet zobrazí další sada karet - "Práce s grafy". Přejděte na kartu "Rozvržení". Klikněte na tlačítko "Trendová linie", který se nachází v panelu nástrojů "Analýza". Zobrazí se nabídka s výběrem typu trendové čáry. Volbu zastavíme na typu, který odpovídá konkrétní úloze. Zvolme možnost pro náš příklad "Exponenciální aproximace".

Excel vytvoří trendovou čáru ve formě další černé křivky přímo na vykreslovací rovině.

Nyní je naším úkolem zobrazit samotný koeficient determinace. Klikněte pravým tlačítkem na trendovou čáru. Kontextová nabídka je aktivována. Volbu v něm zastavíme v bodě "Formát trendové čáry...".

Pro přechod do okna Formát čáry trendu lze provést alternativní akci. Kliknutím levým tlačítkem myši na ni vyberte linii trendu. Přesun na kartu "Rozvržení". Klikněte na tlačítko "Trendová linie" v bloku "Analýza". V seznamu, který se otevře, klikněte na úplně poslední položku v seznamu akcí - "Další možnosti trendové čáry...".

Po kterékoli z výše uvedených akcí se otevře okno formátu, ve kterém můžete provést další nastavení. Chcete-li provést náš úkol, musíte zaškrtnout políčko vedle položky "Dejte do diagramu hodnotu spolehlivosti aproximace (R^2)". Nachází se úplně dole v okně. To znamená, že tímto způsobem zapneme zobrazení koeficientu determinace na ploše stavby. Pak nezapomeňte stisknout tlačítko "Zavřít" ve spodní části aktuálního okna.

Hodnota spolehlivosti přiblížení, tedy hodnota koeficientu určení, bude zobrazena na listu v oblasti konstrukce. V tomto případě je tato hodnota, jak vidíme, rovna 0,9242, což aproximaci charakterizuje jako model dobré kvality.

Naprosto přesně tak můžete nastavit zobrazení koeficientu determinace pro jakýkoli jiný typ trendové čáry. Typ trendové čáry můžete změnit tak, že přejdete přes tlačítko na pásu karet nebo z místní nabídky do okna parametrů, jak je uvedeno výše. Pak již v samotném okně ve skupině "Budování trendové linie" můžete přejít na jiný typ. Zároveň nezapomínejte ovládat to v blízkosti bodu "Dejte do diagramu hodnotu spolehlivosti aproximace" zaškrtávací políčko bylo zaškrtnuto. Po dokončení výše uvedených kroků klikněte na tlačítko "Zavřít" v pravém dolním rohu okna.

U lineárního typu má trendová linie již hodnotu aproximační spolehlivosti 0,9477, což charakterizuje tento model jako ještě spolehlivější než trendová linie exponenciálního typu, kterou jsme uvažovali dříve.

Přepínáním mezi různými typy trendových čar a porovnáním jejich aproximačních hodnot spolehlivosti (koeficient determinace) lze tedy najít variantu, jejíž model nejpřesněji popisuje prezentovaný graf. Nejspolehlivější bude varianta s nejvyšším koeficientem determinace. Na jeho základě můžete sestavit nejpřesnější předpověď.
Například pro náš případ se nám podařilo experimentálně zjistit, že polynomický typ trendové čáry druhého stupně má nejvyšší úroveň spolehlivosti. Koeficient determinace je v tomto případě roven 1. To znamená, že zadaný model je absolutně spolehlivý, což znamená úplnou eliminaci chyb.

To ale zároveň vůbec neznamená, že tento typ trendové linie bude nejspolehlivější i pro jiný graf. Optimální volba typu trendové čáry závisí na typu funkce, na základě které byl graf sestaven. Pokud uživatel nemá dostatečné znalosti k tomu, aby „od oka“ odhadl nejlepší možnost, pak jediným způsobem, jak určit nejlepší předpověď, je porovnat koeficienty determinace, jak ukazuje příklad výše.

Je známo, že je užitečný v různých oblastech činnosti, včetně takové disciplíny, jako je ekonometrie, kde se tento softwarový nástroj používá v práci. V zásadě se všechny akce praktických a laboratorních tříd provádějí v aplikaci Excel, což značně usnadňuje práci a poskytuje podrobné vysvětlení určitých akcí. Takže jeden z analytických nástrojů "Regrese" se používá k výběru grafu pro sadu pozorování pomocí metody nejmenších čtverců. Podívejme se, jaký je tento nástroj programu a jaký je jeho přínos pro uživatele. Níže je také stručný, ale srozumitelný návod na sestavení regresního modelu.

Hlavní úkoly a typy regrese

Regrese je vztah mezi danými proměnnými, díky kterému je možné určit předpověď budoucího chování těchto proměnných. Proměnné jsou různé periodické jevy, včetně lidského chování. Tato analýza Excel se používá k analýze dopadu hodnot jedné nebo více proměnných na konkrétní závislou proměnnou. Například prodej v obchodě je ovlivněn několika faktory, včetně sortimentu, cen a umístění obchodu. Pomocí regrese v Excelu můžete určit míru vlivu každého z těchto faktorů na základě výsledků stávajících prodejů a získaná data pak aplikovat na prognózu prodejů na další měsíc nebo pro jiný obchod umístěný poblíž.

Typicky je regrese prezentována jako jednoduchá rovnice, která odhaluje závislosti a sílu asociace mezi dvěma skupinami proměnných, kde jedna skupina je závislá nebo endogenní a druhá je nezávislá nebo exogenní. Pokud existuje skupina vzájemně souvisejících ukazatelů, závislá proměnná Y je určena na základě logiky uvažování a zbytek funguje jako nezávislé X-proměnné.

Hlavní úkoly vytváření regresního modelu jsou následující:

Výběr významných nezávislých proměnných (Х1, Х2, …, Xk).
Výběr typu funkce.
Konstrukce odhadů pro koeficienty.
Konstrukce intervalů spolehlivosti a regresní funkce.
Kontrola významnosti vypočítaných odhadů a sestrojené regresní rovnice.

Existuje několik typů regresní analýzy:

párová (1 závislá a 1 nezávislá proměnná);
násobek (několik nezávislých proměnných).

Existují dva typy regresních rovnic:

Lineární, ilustrující striktní lineární vztah mezi proměnnými.
Nelineární – rovnice, které mohou zahrnovat mocniny, zlomky a goniometrické funkce.

Návod na stavbu modelu

Chcete-li provést danou konstrukci v aplikaci Excel, musíte postupovat podle pokynů:

Pro další výpočet by měla být použita funkce "Linear()", která specifikuje Y Values, X Values, Const a statistiky. Poté pomocí funkce "Trend" určete množinu bodů na regresní přímce - Y-values, X-values, New values, Const. Pomocí daných parametrů vypočítejte neznámou hodnotu koeficientů na základě daných podmínek úlohy.

Regresní a korelační analýza - statistické metody výzkumu. Toto jsou nejběžnější způsoby, jak ukázat závislost parametru na jedné nebo více nezávislých proměnných.

Níže se na konkrétních praktických příkladech podíváme na tyto dvě mezi ekonomy velmi oblíbené analýzy. Uvedeme také příklad získání výsledků při jejich kombinaci.

Regresní analýza v Excelu

Výsledek analýzy umožňuje stanovit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí, činit manažerská rozhodnutí.

Regrese se děje:

lineární (y = a + bx);
parabolický (y = a + bx + cx 2);
exponenciální (y = a * exp(bx));
mocnina (y = a*x^b);
hyperbolický (y = b/x + a);
logaritmické (y = b * ln(x) + a);
exponenciální (y = a * b^x).

Zvažte příklad vytvoření regresního modelu v Excelu a interpretaci výsledků. Vezměme si lineární typ regrese.

Úkol. U 6 podniků byla analyzována průměrná měsíční mzda a počet zaměstnanců, kteří odešli. Je třeba určit závislost počtu zaměstnanců v důchodu na průměrné mzdě.

Lineární regresní model má následující podobu:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

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

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

Excel má vestavěné funkce, které lze použít k výpočtu parametrů lineárního regresního modelu. Ale doplněk Analysis ToolPak to udělá rychleji.

Aktivujte si výkonný analytický nástroj:

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

Nyní se budeme zabývat přímo regresní analýzou.

V první řadě věnujeme pozornost R-kvadrátu a koeficientům.

Korelační analýza v Excelu

Korelační analýza pomáhá určit, zda existuje vztah mezi ukazateli v jednom nebo dvou vzorcích. Například mezi dobou provozu stroje a náklady na opravy, cenou zařízení a dobou provozu, výškou a hmotností dětí atd.

Pokud existuje vztah, pak zda zvýšení jednoho parametru vede ke zvýšení (pozitivní korelace) nebo snížení (negativní) druhého. Korelační analýza pomáhá analytikovi určit, zda hodnota jednoho ukazatele může předpovědět možnou hodnotu jiného ukazatele.

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

Zvažte, jak použít Excel k nalezení korelačního koeficientu.

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

Úkol: Určete, zda existuje vztah mezi provozní dobou soustruhu a náklady na jeho údržbu.

Umístěte kurzor do libovolné buňky a stiskněte tlačítko fx.

V kategorii "Statistické" vyberte funkci CORREL.
Argument "Pole 1" - první rozsah hodnot - čas stroje: A2: A14.
Argument "Pole 2" - druhý rozsah hodnot - náklady na opravy: B2:B14. Klepněte na tlačítko OK.

Chcete-li určit typ připojení, musíte se podívat na absolutní číslo koeficientu (každý obor činnosti má svou vlastní stupnici).

Pro korelační analýzu několika parametrů (více než 2) je výhodnější použít "Data Analysis" (doplněk "Analysis Package"). V seznamu musíte vybrat korelaci a určit pole. Všechno.

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

Korelační-regresní analýza

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

Příklad:

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