Páros lineáris regresszió excel példákban. Gyors lineáris regresszió Excelben: Trendvonal

Az MS Excel csomag lehetővé teszi, hogy a lineáris regressziós egyenlet felépítése során a munka nagy részét nagyon gyorsan elvégezze. Fontos megérteni, hogyan kell értelmezni a kapott eredményeket.

A működéshez kiegészítő szükséges Elemző csomag, amelyet a menüpontban engedélyezni kell Szolgáltatás\Bővítmények

Az Excel 2007 programban az elemzőcsomag engedélyezéséhez kattintson a blokkoláshoz gombra Excel-beállítások kattintson a bal felső sarokban lévő gombra, majd a " Excel-beállítások"az ablak alján:

Regressziós modell felépítéséhez ki kell választania az elemet Szolgáltatás\Adatelemzés\Regresszió. (Az Excel 2007-ben ez a mód a blokkban van Adatok/Adatelemzés/Regresszió). Megjelenik egy párbeszédpanel, amelyet ki kell töltenie:

1) Y beviteli intervallum A ¾ hivatkozást tartalmaz a kapott jellemző értékeit tartalmazó cellákra y. Az értékeket oszlopba kell rendezni;

2) X beviteli intervallum A ¾ hivatkozást tartalmaz a faktorértékeket tartalmazó cellákra. Az értékeket oszlopokba kell rendezni;

3) Jel Címkékállítsa be, ha az első cellák tartalmaznak magyarázó szöveget (adatcímkéket);

4) Megbízhatósági szint¾ a megbízhatósági szint, amely alapértelmezés szerint 95%-os. Ha nem elégedett ezzel az értékkel, akkor engedélyeznie kell ezt a jelzőt, és meg kell adnia a szükséges értéket;

5) Jel Nulla állandó szerepel, ha olyan egyenletet kell összeállítani, amelyben a szabad változó ;

6) Kimeneti beállítások határozza meg, hogy az eredményeket hol kell elhelyezni. Alapértelmezett építési mód Új munkalap;

7) Blokk Maradék lehetővé teszi a maradékok kimenetének és grafikonjainak felépítését.

Ennek eredményeként az összes szükséges információt tartalmazó információ megjelenik három blokkba csoportosítva: Regressziós statisztika, Varianciaanalízis, Egyenleg kivonás. Nézzük meg őket közelebbről.

1. Regressziós statisztika:

többszörös R képlettel van meghatározva ( Pearson korrelációs együttható);

R (determinációs együttható);

Normalizált R-négyzetet a képlet számítja ki (többszörös regresszióhoz használják);

standard hiba S képlettel számítjuk ki ;

Megfigyelések ¾ az adatmennyiség n.

2. Varianciaanalízis, vonal Regresszió:

Paraméter df egyenlő m(tényezőkészletek száma x);

Paraméter SS képlet határozza meg;

Paraméter KISASSZONY képlet határozza meg;

Statisztika F képlet határozza meg;

Jelentőség F. Ha a kapott szám meghaladja a -t, akkor a hipotézist elfogadjuk (nincs lineáris kapcsolat), ellenkező esetben a hipotézist elfogadjuk (lineáris kapcsolat van).

3. Varianciaanalízis, vonal Maradék:

Paraméter df egyenlő ;

Paraméter SS képlet határozza meg ;

Paraméter KISASSZONY képlet határozza meg.

4. Varianciaanalízis, vonal Teljes az első két oszlop összegét tartalmazza.

5. Varianciaanalízis, vonal Y kereszteződés tartalmazza az együtthatót, a standard hibát és t-statisztika.

P-érték ¾ a számítottnak megfelelő szignifikancia szintek értéke t- statisztikusok. A STUDIST( függvény határozza meg t-statisztika; ). Ha P-érték meghaladja a -t, akkor a megfelelő változó statisztikailag nem szignifikáns és kizárható a modellből.

alsó 95%És Top 95%¾ az elméleti lineáris regressziós egyenlet együtthatóinak 95 százalékos konfidencia intervallumának alsó és felső határa. Ha az adatbeviteli blokkban a megbízhatósági valószínűség értéke az alapértelmezett értéken maradt, akkor az utolsó két oszlop megduplázza az előzőt. Ha a felhasználó megbízhatósági értéket adott meg, az utolsó két oszlop a megadott megbízhatósági szint alsó és felső határértékét tartalmazza.

6. Varianciaanalízis, a sorok együtthatók értékeit, standard hibákat tartalmazzák, t-statisztikus, P-értékek és konfidencia intervallumok a megfelelő .

7. Blokk Egyenleg kivonás tartalmazza a várható értékeket y(a mi jelölésünkben ez ) és maradékok .

BAN BEN excel Létezik egy még gyorsabb és kényelmesebb módja a lineáris regresszió ábrázolásának (és még a nemlineáris regressziók fő típusainak is, amint azt alább tárgyaljuk). Ez a következőképpen tehető meg:

1) válassza ki az adatokat tartalmazó oszlopokat xÉs Y(ebben a sorrendben legyenek!);

2) hívás Diagram varázslóés válassza ki a csoportban típus – Foltés azonnal nyomja meg Kész;

3) a diagram kijelölésének megszüntetése nélkül válassza ki a megjelenő főmenüpontot Diagram, amelyben ki kell választania az elemet Adjon hozzá egy trendvonalat;

4) a megjelenő párbeszédpanelen Trendvonal a lapon típus választ Lineáris;

5) a lapon Lehetőségek aktiválhatja a kapcsolót Egyenlet megjelenítése a diagramon, amely lehetővé teszi a (4.4) lineáris regressziós egyenlet megtekintését, amelyben a (4.5) együtthatók kiszámításra kerülnek.

6) Ugyanazon a lapon aktiválhatja a kapcsolót Tegye fel a diagramra a közelítési konfidencia értékét (R^2). Ez az érték a (4.3) korrelációs együttható négyzete, és megmutatja, hogy a számított egyenlet mennyire írja le jól a kísérleti függést. Ha R 2 közel van az egységhez, akkor az elméleti regressziós egyenlet jól leírja a kísérleti függőséget (az elmélet jól egyezik a kísérlettel), és ha R 2 közel nullához, akkor ez az egyenlet nem alkalmas a kísérleti függőség leírására (az elmélet nem egyezik a kísérlettel).

A leírt műveletek végrehajtása eredményeként egy diagramot kapunk egy regressziós grafikonnal és annak egyenletével.

§4.3. A nemlineáris regresszió főbb típusai

Parabolikus és polinomiális regresszió.

Parabolikus az érték függősége Y a mérettől x másodfokú függvénnyel kifejezett függőségnek nevezzük (2. rendű parabola):

Ezt az egyenletet ún parabolikus regresszió Y tovább x. Lehetőségek A, b, Val vel hívják parabolikus regressziós együtthatók. A parabolikus regressziós együtthatók kiszámítása mindig körülményes, ezért a számításokhoz számítógép használata javasolt.

A (4.8) parabolikus regresszió egy speciális esete az általánosabb regressziónak, az úgynevezett polinomnak. Polinom az érték függősége Y a mérettől x polinom által kifejezett függőségnek nevezzük n- sorrend:

hol vannak a számok és én (én=0,1,…, n) hívják polinomiális regressziós együtthatók.

Hatvány-regresszió.

Erő az érték függősége Y a mérettől x a forma függőségének nevezzük:

Ezt az egyenletet ún hatványregressziós egyenlet Y tovább x. Lehetőségek AÉs b hívják hatvány regressziós együtthatók.

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

Ez az egyenlet egy egyenest ír le egy síkon ln logaritmikus koordinátatengelyekkel xés ln. Ezért a hatványos regresszió alkalmazhatóságának kritériuma az a követelmény, hogy az empirikus adatok logaritmusának pontjai ln. x iés ln y i voltak a legközelebb az egyeneshez (4,11).

Exponenciális regresszió.

Tájékoztató(vagy exponenciális) az érték függése Y a mérettől x a forma függőségének nevezzük:

(vagy ). (4.12)

Ezt az egyenletet ún exponenciális egyenlet(vagy exponenciális) regresszió Y tovább x. Lehetőségek A(vagy k) És b hívják exponenciális együtthatók(vagy exponenciális) regresszió.

Ha felvesszük a hatványregressziós egyenlet mindkét oldalának logaritmusát, akkor megkapjuk az egyenletet

ln = x ln a+ln b(vagy ln = k x+ln b). (4.13)

Ez az egyenlet egy ln mennyiség logaritmusának lineáris függését írja le egy másik mennyiségtől x. Ezért a hatványregresszió alkalmazhatóságának kritériuma az a követelmény, hogy azonos értékű tapasztalati adatpontok x iés egy másik ln mennyiség logaritmusai y i voltak a legközelebb az egyeneshez (4,13).

Logaritmikus regresszió.

Logaritmikus az érték függősége Y a mérettől x a forma függőségének nevezzük:

=a+b· ln x. (4.14)

Ezt az egyenletet ún Y logaritmikus regressziós egyenlet tovább x. Lehetőségek AÉs b hívják logaritmikus regressziós együtthatók.

Hiperbolikus regresszió.

Hiperbolikus az érték függősége Y a mérettől x a forma függőségének nevezzük:

Ezt az egyenletet ún hiperbolikus regressziós egyenlet Y tovább x. Lehetőségek AÉs b hívják hiperbolikus regressziós együtthatókés a legkisebb négyzetek módszerével határozzák meg. A módszer alkalmazása a következő képletekhez vezet:

A (4.16-4.17) képletekben az összegzés az index felett történik én egytől a megfigyelések számáig n.

Sajnos be excel Nincsenek hiperbolikus regressziós együtthatók kiszámítására szolgáló függvények. Azokban az esetekben, amikor nem ismert, hogy a mért mennyiségek fordított arányossággal állnak összefüggésben, ajánlatos a hiperbolikus regressziós egyenlet helyett hatványregressziós egyenletet keresni, így a excel van egy eljárás a megtalálására. Ha a mért mennyiségek között hiperbolikus függést feltételezünk, akkor annak regressziós együtthatóit ki kell számítani segédszámítási táblázatok és összegzési műveletek segítségével (4.16-4.17) képletekkel.

Regressziós elemzés Microsoft Excelben – a legátfogóbb útmutató az MS Excel használatához az üzleti elemzés területén felmerülő regresszióelemzési problémák megoldására. Konrad Carlberg világosan elmagyarázza az elméleti kérdéseket, amelyek ismerete segít elkerülni sok hibát mind a regressziós elemzés során, mind a mások által végzett elemzések eredményeinek értékelése során. Minden anyag, az egyszerű korrelációktól és t-próbáktól a kovariancia többszörös elemzéséig, valós példákon alapul, és részletes, lépésről lépésre történő eljárások kísérik.

A könyv tárgyalja az Excel regressziós függvényeinek furcsaságait és ellentmondásait, megvizsgálja az egyes lehetőségek és érvek következményeit, és elmagyarázza, hogyan lehet megbízhatóan alkalmazni a regressziós módszereket az orvosi kutatástól a pénzügyi elemzésig terjedő területeken.

Konrad Carlberg. Regressziós elemzés Microsoft Excelben. – M.: Dialektika, 2017. – 400 p.

Jegyzet letöltése vagy formátumban, példák formátumban

1. fejezet: Az adatok változékonyságának felmérése

A statisztikusoknak számos variációs mérőszám áll a rendelkezésükre. Az egyik az egyes értékek átlagtól való eltérésének négyzetes összege. Az Excelben a SQUARE() függvényt használjuk erre. De a varianciát gyakrabban használják. A diszperzió az eltérések négyzetes átlaga. A variancia érzéketlen a vizsgált adatsor értékeinek számára (míg az eltérések négyzetes összege a mérések számával nő).

Az Excel két függvényt kínál, amelyek varianciaértéket adnak vissza: DISP.G() és DISP.V():

• Használja a DISP.G() függvényt, ha a feldolgozandó értékek egy sokaságot alkotnak. Vagyis a tartományban szereplő értékek az egyetlenek, amelyek érdeklik Önt.
• Használja a DISP.B() függvényt, ha a feldolgozandó értékek egy nagyobb sokaságból származó mintát alkotnak. Feltételezzük, hogy vannak további értékek, amelyek varianciáját szintén megbecsülheti.

Ha egy mennyiséget, például átlagot vagy korrelációs együtthatót számítunk ki egy sokaságból, akkor azt paraméternek nevezzük. A minta alapján számított hasonló mennyiséget statisztikának nevezzük. Az eltérések számolása az átlagtól egy adott halmazban kisebb nagyságú négyzetes eltérések összegét kapjuk, mintha bármilyen más értékből számolnánk. Hasonló állítás igaz a varianciára is.

Minél nagyobb a minta mérete, annál pontosabb a számított statisztikai érték. De nincs olyan mintaméret, amely kisebb a populáció méreténél, amelynél biztos lehet benne, hogy a statisztikai érték megegyezik a paraméter értékével.

Tegyük fel, hogy van egy 100 magasságból álló halmaz, amelyek átlaga eltér a népesség átlagától, bármilyen kicsi is a különbség. Egy minta szórásának kiszámításával egy értéket kap, mondjuk 4-et. Ez az érték kisebb, mint bármely más érték, amelyet úgy kaphatunk meg, hogy kiszámítjuk a 100 magassági érték mindegyikének eltérését a minta átlagától eltérő bármely értékhez viszonyítva. , beleértve a valódi átlaghoz viszonyítva is. Ezért a számított variancia különbözik és kisebb lesz attól a szórástól, amelyet akkor kapna, ha valamilyen módon kiderítené és egy populációs paramétert használna a mintaátlag helyett.

A mintára meghatározott átlagos négyzetösszeg alacsonyabb becslést ad a sokaság szórására. Az így számított variancia ún kiszorítottértékelés. Kiderült, hogy a torzítás kiküszöböléséhez és a torzításmentes becsléshez elegendő az eltérések négyzetes összegét elosztani nem n, Ahol n- mintanagyság, és n – 1.

Nagyságrend n – 1 szabadsági fokok számának (számának) nevezzük. Különböző módok vannak ennek a mennyiségnek a kiszámítására, bár ezek mindegyikében vagy ki kell vonni egy bizonyos számot a minta méretéből, vagy meg kell számolni azon kategóriák számát, amelyekbe a megfigyelések tartoznak.

A DISP.G() és DISP.V() függvények közötti különbség lényege a következő:

• A VAR.G() függvényben a négyzetek összegét elosztjuk a megfigyelések számával, és ezért a variancia torzított becslését jelenti, a valódi átlagot.
• A DISP.B() függvényben a négyzetek összegét elosztjuk a megfigyelések számával mínusz 1, azaz. a szabadsági fokok számával, ami pontosabb, elfogulatlanabb becslést ad annak a sokaságnak a szórására, amelyből a mintát vettük.

Szórás szórás, SD) – a variancia négyzetgyöke:

Az eltérések négyzetre emelése a mérési skálát egy másik mérőszámmá alakítja, amely az eredeti négyzete: méter - négyzetméter, dollár - négyzetdollár stb. A szórás a variancia négyzetgyöke, és ezért visszavisz minket az eredeti mértékegységekhez. Amelyik kényelmesebb.

Gyakran szükség van a szórás kiszámítására, miután az adatokat némi manipulációnak vetették alá. És bár ezekben az esetekben az eredmények kétségtelenül szórások, általában ún standard hibák. A standard hibának többféle típusa van, beleértve a mérés standard hibáját, az arány standard hibáját és az átlag standard hibáját.

Tegyük fel, hogy az 50 állam mindegyikében 25 véletlenszerűen kiválasztott felnőtt férfi magassági adatait gyűjtötte össze. Ezután kiszámítja a felnőtt hímek átlagos magasságát az egyes államokban. Az így kapott 50 átlagérték viszont megfigyelésnek tekinthető. Ebből kiszámolhatná a szórását, ami az az átlag standard hibája. Rizs. 1. összehasonlítja 1250 nyers egyéni érték eloszlását (25 férfi magassági adatai az 50 állam mindegyikében) az 50 állam átlagának eloszlásával. Az átlag standard hibájának (vagyis az átlagok szórásának, nem az egyedi megfigyeléseknek) becslésére szolgáló képlet:

ahol az átlag standard hibája; s– az eredeti megfigyelések szórása; n– megfigyelések száma a mintában.

Rizs. 1. Az államonkénti átlagok változása lényegesen kisebb, mint az egyes megfigyelések változása.

A statisztikában van egy egyezmény a görög és latin betűk használatára vonatkozóan a statisztikai mennyiségek ábrázolására. Az általános populáció paramétereit görög betűkkel szokás jelölni, a mintastatisztikát pedig latin betűkkel. Ezért amikor a sokaság szórásáról beszélünk, σ-ként írjuk; ha a minta szórását vesszük figyelembe, akkor az s jelölést használjuk. Ami az átlagokat jelölő szimbólumokat illeti, ezek nem egyeznek meg egymással olyan jól. A népesség átlagát a görög μ betű jelöli. A minta átlagának ábrázolására azonban hagyományosan az X̅ szimbólumot használják.

z-pontszám szórás egységekben fejezi ki egy megfigyelés pozícióját az eloszlásban. Például a z = 1,5 azt jelenti, hogy a megfigyelés 1,5 szórásra van az átlagtól. Term z-pontszám egyéni értékelésekhez használják, pl. az egyes mintaelemekhez rendelt méretekhez. Az ilyen statisztikákra utaló kifejezés (például az állam átlaga) z-pontszám:

ahol X̅ a minta átlaga, μ a sokaság átlaga, egy mintakészlet átlagának standard hibája:

ahol σ a sokaság standard hibája (egyedi mérések), n- minta nagysága.

Tegyük fel, hogy oktatóként dolgozik egy golfklubban. Hosszú időn keresztül mérhette lövései távolságát, és tudja, hogy az átlag 205 yard, a szórás pedig 36 yard. Új klubot ajánlanak neked, azt állítva, hogy az 10 yarddal növeli az ütési távolságodat. A következő 81 klub védnökét megkéri, hogy készítsen próbalövést egy új ütővel, és rögzítse a lengéstávját. Kiderült, hogy az átlagos távolság az új klubbal 215 yard volt. Mekkora a valószínűsége annak, hogy a 10 yardos (215–205) különbség kizárólag a mintavételi hibának köszönhető? Vagy másképpen fogalmazva: Mi a valószínűsége annak, hogy egy kiterjedtebb tesztelés során az új klub nem fogja kimutatni az ütési távolság növekedését a jelenlegi 205 yardos hosszú távú átlaghoz képest?

Ezt egy z-pontszám generálásával ellenőrizhetjük. Az átlag standard hibája:

Aztán z-pontszám:

Meg kell találnunk annak a valószínűségét, hogy a minta átlaga 2,5σ-vel lesz távolabb a sokaság átlagától. Ha kicsi a valószínűség, akkor a különbségek nem a véletlennek köszönhetők, hanem az új klub minőségének. Az Excelnek nincs kész függvénye a z-pontszám valószínűségének meghatározására. Használhatja azonban a =1-NORM.ST.DIST(z-score,TRUE) képletet, ahol a NORM.ST.DIST() függvény a normálgörbe alatti területet adja vissza a z-pontszámtól balra (ábra 2).

Rizs. 2. A NORM.ST.DIST() függvény a görbe alatti területet adja vissza a z-értéktől balra; A kép nagyításához kattintson rá jobb gombbal, és válassza ki Kép megnyitása új lapon

A NORM.ST.DIST() függvény második argumentuma két értéket vehet fel: TRUE – a függvény visszaadja a görbe alatti terület területét az első argumentum által megadott ponttól balra; FALSE – a függvény a görbe magasságát adja vissza az első argumentum által megadott pontban.

Ha a sokaság átlaga (μ) és szórása (σ) nem ismert, a t-értéket kell használni (lásd a részleteket). A z-score és a t-score struktúrák abban különböznek egymástól, hogy a mintaeredményekből kapott s szórással nem a σ populációs paraméter ismert értékét, hanem a t-score-t találjuk meg. A normálgörbe egyetlen alakú, és a t-értékeloszlás alakja a df szabadságfokok számától függően változik. szabadsági fokokat) az általa képviselt mintából. A minta szabadságfokainak száma egyenlő n – 1, Ahol n- mintanagyság (3. ábra).

Rizs. 3. A σ paraméter ismeretlen eseteiben keletkező t-eloszlások alakja eltér a normál eloszlás alakjától

Az Excelnek két függvénye van a t-eloszláshoz, amelyet Student-eloszlásnak is neveznek: a STUDENT.DIST() a görbe alatti területet adja vissza egy adott t-értéktől balra, a STUDENT.DIST.PH() pedig a görbe alatti területet adja vissza a t-értéktől balra. jobb.

2. fejezet Korreláció

A korreláció a rendezett párok halmazának elemei közötti függőség mértéke. Az összefüggést jellemzik Pearson korrelációs együtthatók–r. Az együttható –1,0 és +1,0 közötti értékeket vehet fel.

Ahol S xÉs S y– a változók szórása xÉs Y, S xy- kovariancia:

Ebben a képletben a kovariancia el van osztva a változók szórásával xÉs Y, ezáltal eltávolítja az egységfüggő skálázási hatásokat a kovarianciából. Az Excel a CORREL() függvényt használja. Ennek a függvénynek a neve nem tartalmazza a Г és В minősítő elemeket, amelyeket olyan függvények nevében használnak, mint a STANDARDEV(), VARIANCE() vagy COVARIANCE(). Bár a minta korrelációs együtthatója torzított becslést ad, a torzítás oka más, mint a variancia vagy a szórás esetén.

Az általános korrelációs együttható nagyságától függően (gyakran görög betűvel jelölik ρ ), korrelációs együttható r torzított becslést ad, és a torzítás hatása a minta méretének csökkenésével nő. Ezt a torzítást azonban nem próbáljuk úgy korrigálni, mint például a szórás számításánál, amikor nem a megfigyelések számát, hanem a szabadságfokok számát cseréltük be a megfelelő képletbe. A valóságban a kovariancia kiszámításához használt megfigyelések száma nincs hatással a nagyságra.

A standard korrelációs együttható olyan változókkal való használatra szolgál, amelyek lineáris összefüggésben állnak egymással. Az adatok nemlinearitása és/vagy hibái (outlierek) a korrelációs együttható hibás kiszámításához vezetnek. Az adatproblémák diagnosztizálásához ajánlatos szóródiagramokat készíteni. Ez az egyetlen olyan diagramtípus az Excelben, amely a vízszintes és a függőleges tengelyeket is értéktengelyként kezeli. Egy vonaldiagram az egyik oszlopot kategóriatengelyként határozza meg, ami torzítja az adatok képét (4. ábra).

Rizs. 4. A regressziós egyenesek azonosnak tűnnek, de hasonlítsa össze az egyenleteiket egymással

A vonaldiagram felépítéséhez használt megfigyelések a vízszintes tengely mentén egyenlő távolságra vannak elrendezve. A tengely mentén lévő felosztási címkék csak címkék, nem numerikus értékek.

Bár a korreláció gyakran azt jelenti, hogy ok-okozati összefüggés van, nem használható fel annak bizonyítására, hogy ez a helyzet. A statisztikákat nem arra használjuk, hogy megmutassuk, hogy egy elmélet igaz vagy hamis. A megfigyelési eredmények versengő magyarázatainak kizárásához tegye fel tervezett kísérletek. Statisztikát használnak az ilyen kísérletek során gyűjtött információk összegzésére és annak számszerűsítésére, hogy a meghozott döntés téves lehet a rendelkezésre álló bizonyítékok alapján.

3. fejezet: Egyszerű regresszió

Ha két változó úgy kapcsolódik egymáshoz, hogy a korrelációs együttható értéke meghaladja mondjuk a 0,5-öt, akkor ebben az esetben meg lehet jósolni (bizonyos pontossággal) az egyik változó ismeretlen értékét a másik ismert értékéből. . ábrán látható adatok alapján előrejelzett árértékeket kaphat. Az 5. ábrán látható módon számos lehetséges módszer bármelyikét használhatja, de szinte biztosan nem fogja használni az 5. ábrán látható módszert. 5. Mégis meg kell ismerkednie vele, mert egyetlen más módszer sem teszi lehetővé a korreláció és az előrejelzés közötti kapcsolat olyan egyértelmű bemutatását, mint ez. ábrán. Az 5. a B2:C12 tartományban tíz házból álló véletlenszerű mintát mutat, és adatokat közöl az egyes házak területéről (négyzetméterben) és eladási áráról.

Rizs. 5. Az előre jelzett eladási árak értékei egyenes vonalat alkotnak

Keresse meg az átlagokat, a szórásokat és a korrelációs együtthatót (A14:C18 tartomány). Számítsa ki a terület z-pontszámait (E2:E12). Például az E3 cella a következő képletet tartalmazza: =(B3-$B$14)/$B$15. Számítsa ki az előrejelzett ár z-pontszámait (F2:F12). Például az F3 cella a következő képletet tartalmazza: =ЕЗ*$В$18. Konvertálja a z-pontszámokat dollárárra (H2:H12). Az NZ cellában a képlet a következő: =F3*$C$15+$C$14.

Megjegyzendő, hogy a becsült érték mindig a 0 átlaga felé tolódik el. Minél közelebb van a korrelációs együttható nullához, annál közelebb van a nullához az előre jelzett z-pontszám. Példánkban a terület és az eladási ár közötti korrelációs együttható 0,67, az előrejelzett ár pedig 1,0 * 0,67, azaz. 0,67. Ez az átlag feletti érték többletének felel meg, amely megegyezik a szórás kétharmadával. Ha a korrelációs együttható 0,5 lenne, akkor az előrejelzett ár 1,0 * 0,5 lenne, azaz. 0.5. Ez az átlag feletti érték többletének felel meg, amely csak a szórás felével egyenlő. Amikor a korrelációs együttható értéke eltér az ideális értéktől, pl. nagyobb, mint -1,0 és kisebb, mint 1,0, akkor az előrejelzett változó pontszámának közelebb kell lennie az átlagához, mint a prediktor (független) változó pontszámának a sajátjához. Ezt a jelenséget nevezzük regressziónak az átlaghoz, vagy egyszerűen regressziónak.

Az Excel számos funkcióval rendelkezik egy regressziós egyenes egyenlet (az Excelben trendvonalnak nevezik) együtthatóinak meghatározására. y =kx + b. Meghatározására k funkciót tölt be

=SLOPE(ismert_y_értékek, ismert_x_értékek)

Itt nál nél az előrejelzett változó, és x egy független változó. Szigorúan be kell tartania a változók ezt a sorrendjét. A regressziós egyenes meredeksége, a korrelációs együttható, a változók szórása és a kovariancia szorosan összefügg (6. ábra). Az INTERMEPT() függvény a függőleges tengelyen lévő regressziós egyenes által elfogott értéket adja vissza:

=LIMIT(ismert_y_értékek, ismert_x_értékek)

Rizs. 6. A szórások közötti kapcsolat a kovariancia korrelációs együtthatóvá és a regressziós egyenes meredekségévé konvertálja

Vegye figyelembe, hogy a SLOPE() és INTERCEPT() függvények argumentumaként megadott x és y értékek számának azonosnak kell lennie.

A regressziós elemzésben egy másik fontos mutatót használnak - az R 2-t (R-négyzet), vagy a determinációs együtthatót. Meghatározza, hogy a közötti kapcsolat milyen mértékben járul hozzá az adatok általános változékonyságához xÉs nál nél. Az Excelben van egy CVPIERSON() függvény, amely pontosan ugyanazokat az argumentumokat veszi fel, mint a CORREL() függvény.

Azt mondják, hogy két változó, amelyek között nem nulla korrelációs együttható van, magyarázza a varanciát, vagy megmagyarázza a variancia mértékét. A tipikusan magyarázott variancia százalékban van kifejezve. Így R 2 = 0,81 azt jelenti, hogy két változó varianciájának (szórásának) 81%-a magyarázható. A fennmaradó 19% véletlenszerű ingadozásoknak köszönhető.

Az Excel rendelkezik egy TREND funkcióval, amely megkönnyíti a számításokat. TREND() függvény:

• elfogadja az Ön által megadott ismert értékeket xés ismert értékeket nál nél;
• kiszámítja a regressziós egyenes meredekségét és az állandót (metszet);
• előrejelzett értékeket ad vissza nál nél, amelyet ismert értékekre regressziós egyenlet alkalmazásával határozunk meg x(7. ábra).

A TREND() függvény egy tömbfüggvény (ha még nem találkoztál ilyen függvényekkel, ajánlom).

Rizs. 7. A TREND() függvény használata lehetővé teszi a számítások felgyorsítását és egyszerűsítését a SLOPE() és INTERCEPT() függvénypárhoz képest.

Ha a TREND() függvényt tömbképletként szeretné megadni a G3:G12 cellákban, válassza ki a G3:G12 tartományt, írja be a TREND képletet (NW:S12;V3:B12), nyomja le és tartsa lenyomva a billentyűket és csak ezután nyomja meg a gombot . Vegye figyelembe, hogy a képlet kapcsos zárójelek között van: ( és ). Így jelzi az Excel, hogy ez a képlet tömbképletnek tekinthető. Ne írja be saját maga a zárójeleket: Ha saját maga próbálja beírni őket egy képlet részeként, az Excel normál szöveges karakterláncként kezeli a bevitelt.

A TREND() függvénynek két további argumentuma van: új_értékek_xÉs const. Az első lehetővé teszi a jövőre vonatkozó előrejelzés készítését, a második pedig arra kényszerítheti a regressziós egyenest, hogy áthaladjon az origón (az IGAZ értéke azt mondja az Excelnek, hogy a számított állandót használja, a FALSE érték azt jelzi, hogy az Excel konstans = 0 ). Az Excel lehetővé teszi, hogy regressziós egyenest rajzoljon a grafikonon úgy, hogy az áthaladjon az origón. Kezdje egy szóródiagram rajzolásával, majd kattintson a jobb gombbal az egyik adatsor-jelölőre. Válassza ki az elemet a megnyíló helyi menüben Adjon hozzá egy trendvonalat; Válassz egy lehetőséget Lineáris; ha szükséges, görgessen le a panelen, jelölje be a négyzetet Állítsa be a kereszteződést; Győződjön meg arról, hogy a hozzá tartozó szövegmező 0.0-ra van állítva.

Ha három változója van, és szeretné meghatározni a korrelációt kettő között, miközben kiküszöböli a harmadik befolyását, használhatja részleges korreláció. Tegyük fel, hogy a város főiskolai tanulmányait befejező lakosok százalékos aránya és a város könyvtáraiban lévő könyvek száma közötti kapcsolat érdekli. Ön 50 városra vonatkozóan gyűjtött adatokat, de... A probléma az, hogy mindkét paraméter egy adott város lakóinak jólététől függhet. Természetesen nagyon nehéz további 50 várost találni, amelyet a lakosok pontosan ugyanolyan jóléti szintje jellemez.

A vagyonnak a könyvtári anyagi támogatásra és a kollégium megfizethetőségére gyakorolt ​​hatásának statisztikai módszerekkel történő ellenőrzésével pontosabb számszerűsítést kaphat az érdeklődésre számot tartó változók, nevezetesen a könyvek száma és a diplomások száma közötti kapcsolat erőssége. Az ilyen feltételes korrelációt két változó között, amikor más változók értékei rögzítettek, parciális korrelációnak nevezzük. A kiszámításának egyik módja a következő egyenlet:

Ahol rC.B. . W- a College és a Books változók közötti korrelációs együttható a Wealth változó hatásának (fix értékének) kizárásával; rC.B.- a College és a Books változók közötti korrelációs együttható; rCW- korrelációs együttható a Főiskola és a Jóléti változók között; rb.w.- korrelációs együttható a Könyvek és a Jólét változók között.

Másrészt részleges korreláció számítható a reziduumok elemzése alapján, pl. különbségek az előrejelzett értékek és a tényleges megfigyelések kapcsolódó eredményei között (mindkét módszert a 8. ábra mutatja be).

Rizs. 8. Parciális korreláció, mint a maradékok korrelációja

A korrelációs együtthatók mátrixának (B16:E19) kiszámításának egyszerűsítéséhez használja az Excel elemző csomagot (menü Adat –> Elemzés –> Adatelemzés). Alapértelmezés szerint ez a csomag nem aktív az Excelben. A telepítéshez menjen végig a menün Fájl –> Lehetőségek –> Kiegészítők. A megnyílt ablak alján Lehetőségekexcel megtalálni a mezőt Ellenőrzés, válassza ki Kiegészítőkexcel, kattintson Megy. Jelölje be a bővítmény melletti négyzetet Elemző csomag. Kattintson az A gombra adatelemzés, válassza ki a lehetőséget Korreláció. Adja meg a $B$2:$D$13 beviteli intervallumot, jelölje be a négyzetet Címkék az első sorban, adja meg a $B$16:$E$19 kimeneti intervallumot.

Egy másik lehetőség a félig részleges korreláció meghatározása. Például a magasság és az életkor súlyra gyakorolt ​​hatását kutatja. Így két előrejelző változója van - magasság és életkor, valamint egy előrejelző változó - súly. Ki akarja zárni az egyik prediktorváltozó hatását a másikra, de nem a prediktorváltozóra:

ahol H - Magasság (Magasság), W - Súly (Súly), A - Életkor (Kor); A félig részleges korrelációs együttható indexe zárójeleket használ annak jelzésére, hogy melyik változót távolítják el, és melyik változóból. Ebben az esetben a W(H.A) jelölés azt jelzi, hogy az Életkor változó hatása a Magasság változóból eltűnik, a Súly változóból viszont nem.

Úgy tűnhet, hogy a megvitatott kérdésnek nincs jelentőssége. Végül is az a legfontosabb, hogy mennyire pontosan működik a teljes regressziós egyenlet, míg az egyes változók relatív hozzájárulása a teljes magyarázott varianciahoz másodlagos jelentőségűnek tűnik. Ez azonban nem így van. Amint elkezd azon töprengeni, hogy érdemes-e egyáltalán egy változót többszörös regressziós egyenletben használni, a kérdés fontossá válik. Befolyásolhatja az elemzési modell kiválasztásának helyességének megítélését.

4. fejezet LINEST() függvény

A LINEST() függvény 10 regressziós elemzési statisztikát ad vissza. A LINEST() függvény egy tömbfüggvény. Beírásához válasszon ki egy öt sorból és két oszlopból álló tartományt, írja be a képletet, és kattintson a gombra (9. ábra):

VONAL(B2:B21,A2:A21,IGAZ,IGAZ)

Rizs. 9. LINEST() függvény: a) válassza ki a D2:E6 tartományt, b) írja be a képletet a képletsoron látható módon, c) kattintson

A LINEST() függvény a következőket adja vissza:

• regressziós együttható (vagy meredekség, D2 cella);
• szegmens (vagy konstans, E3 cella);
• a regressziós együttható és a konstans standard hibái (tartomány D3:E3);
• R 2 determinációs együttható a regresszióhoz (D4 cella);
• becslés standard hibája (E4 cella);
• F-teszt a teljes regresszióhoz (D5 cella);
• a maradék négyzetösszeg szabadságfokainak száma (E5 cella);
• regressziós négyzetösszeg (D6 cella);
• maradék négyzetösszeg (E6 cella).

Nézzük meg ezeket a statisztikákat, és hogyan hatnak egymásra.

standard hiba esetünkben a mintavételi hibákra számított szórás. Vagyis ez egy olyan helyzet, amikor az általános sokaságnak van egy statisztikája, a mintának pedig egy másik. Ha a regressziós együtthatót elosztjuk a standard hibával, akkor 2,092/0,818 = 2,559 értéket kapunk. Más szóval, a 2,092-es regressziós együttható két és fél standard hibanyi távolságra van a nullától.

Ha a regressziós együttható nulla, akkor az előrejelzett változó legjobb becslése annak átlaga. Két és fél standard hiba elég nagy, és nyugodtan feltételezhető, hogy a sokaság regressziós együtthatója nem nulla.

A függvény segítségével meghatározhatja a 2,092-es minta regressziós együttható megszerzésének valószínűségét, ha annak tényleges értéke a sokaságban 0,0

STUDENT.DIST.PH (t-kritérium = 2,559; szabadsági fokok száma = 18)

Általában a szabadsági fokok száma = n – k – 1, ahol n a megfigyelések száma, k pedig a prediktor változók száma.

Ez a képlet 0,00987-et ad vissza, vagy 1%-ra kerekítve. Azt mondja meg, hogy ha a sokaság regressziós együtthatója 0%, akkor annak a valószínűsége, hogy 20 fős mintát kapunk, amelyre a becsült regressziós együttható 2,092, szerény 1%.

Az F-teszt (D5 cella a 9. ábrán) ugyanazokat a funkciókat látja el a teljes regresszióval kapcsolatban, mint a t-próba az egyszerű páronkénti regresszió együtthatójával kapcsolatban. Az F-próbát annak tesztelésére használják, hogy egy regresszió R 2 determinációs együtthatója elég nagy-e ahhoz, hogy elvetjük azt a hipotézist, miszerint a sokaságban ennek értéke 0,0, ami azt jelzi, hogy nincs a prediktor és a predikciós változó által megmagyarázott variancia. Ha csak egy előrejelző változó van, az F-próba pontosan egyenlő a t-próba négyzetével.

Eddig az intervallumváltozókat vizsgáltuk. Ha vannak olyan változói, amelyek több értéket is felvehetnek, és egyszerű neveket jelentenek, például Férfi és Nő vagy Hüllő, Kétéltű és Hal, akkor numerikus kódként jelenítse meg őket. Az ilyen változókat nominálisnak nevezzük.

R2 statisztika számszerűsíti a magyarázott variancia arányát.

A becslés standard hibája.ábrán. A 4.9. ábra a Súly változó előrejelzett értékeit mutatja be, amelyeket a Magasság változóval való kapcsolata alapján kapunk. Az E2:E21 tartomány tartalmazza a Súly változó maradékértékeit. Pontosabban, ezeket a maradékokat hibának nevezzük - innen ered a becslés standard hibája.

Rizs. 10. Mind az R 2, mind a becslés standard hibája a regresszióval kapott előrejelzések pontosságát fejezi ki

Minél kisebb a becslés standard hibája, annál pontosabb a regressziós egyenlet, és annál jobban várható, hogy az egyenlet által előállított előrejelzések megegyezzenek a tényleges megfigyeléssel. A becslés standard hibája módot ad ezeknek a várakozásoknak a számszerűsítésére. A bizonyos magasságú emberek 95%-ának súlya a következő tartományba esik:

(magasság * 2,092 – 3,591) ± 2,092 * 21,118

F-statisztika a csoportok közötti variancia és a csoporton belüli variancia aránya. Ezt a nevet George Snedecor statisztikus vezette be Sir tiszteletére, aki a 20. század elején kidolgozta a varianciaanalízist (ANOVA, Analysis of Variance).

Az R 2 determinációs együttható a regresszióhoz tartozó négyzetösszeg arányát fejezi ki. Az érték (1 – R 2) a maradékokhoz – előrejelzési hibákhoz – kapcsolódó négyzetösszeg arányát fejezi ki. Az F-próba a LINEST függvény segítségével (11. ábra F5 cellája), négyzetösszegekkel (G10:J11 tartomány), varianciaarányokkal (G14:J15 tartomány) érhető el. A képletek a csatolt Excel fájlban tanulmányozhatók.

Rizs. 11. F-kritérium számítása

A névleges változók használatakor dummy kódolást alkalmazunk (12. ábra). Az értékek kódolásához célszerű a 0 és 1 értékeket használni. Az F valószínűséget a következő függvény segítségével számítjuk ki:

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

Itt az F.DIST.PH() függvény egy olyan F-kritérium megszerzésének valószínűségét adja vissza, amely engedelmeskedik a központi F-eloszlásnak (13. ábra) két adathalmazra az I2 és I3 cellákban megadott szabadsági fokokkal. amelynek értéke egybeesik a K2 cellában megadott értékkel.

Rizs. 12. Regressziós elemzés álváltozókkal

Rizs. 13. Központi F-eloszlás λ = 0-nál

5. fejezet Többszörös regresszió

Amikor az egy prediktorváltozós egyszerű páros regresszióról a többszörös regresszióra vált, egy vagy több prediktorváltozót ad hozzá. Tárolja a prediktor változók értékeit a szomszédos oszlopokban, például az A és B oszlopban, ha két prediktor, vagy az A, B és C oszlopban, ha három prediktort. Mielőtt beírna egy olyan képletet, amely tartalmazza a LINEST() függvényt, válasszon ki öt sort és annyi oszlopot, ahány prediktorváltozó van, és még egyet a konstanshoz. Két prediktorváltozós regresszió esetén a következő struktúra használható:

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

Hasonlóképpen három változó esetén:

VONAL(A2:A61,B2:D61,;IGAZ)

Tegyük fel, hogy szeretné tanulmányozni az életkor és az étrend lehetséges hatásait az LDL – alacsony sűrűségű lipoproteinek – szintjére, amelyekről úgy tartják, hogy felelősek az ateroszklerotikus plakkok képződéséért, amelyek atherothrombosis-t okoznak (14. ábra).

Rizs. 14. Többszörös regresszió

A többszörös regresszió R 2 értéke (az F13 cellában tükröződik) nagyobb, mint bármely egyszerű regresszió R 2 értéke (E4, H4). A többszörös regresszió több előrejelző változót használ egyszerre. Ebben az esetben az R2 szinte mindig növekszik.

Bármilyen egyszerű lineáris regressziós egyenletnél egy prediktorváltozóval, mindig tökéletes korreláció lesz az előrejelzett értékek és a prediktor változó értékei között, mivel az egyenlet megszorozza a prediktor értékeket egy állandóval, és hozzáad egy másik állandót minden termék. Ez a hatás többszörös regresszióban nem marad fenn.

A LINEST() függvény által visszaadott eredmények megjelenítése többszörös regresszióhoz (15. ábra). A regressziós együtthatók a LINEST() függvény által visszaadott eredmények részeként jelennek meg a változók fordított sorrendjében(A G–H–I a C–B–A-nak felel meg).

Rizs. 15. Az együtthatók és standard hibáik fordított sorrendben jelennek meg a munkalapon.

Az egyetlen prediktorváltozós regressziós elemzésben használt elvek és eljárások könnyen adaptálhatók több prediktorváltozó figyelembevételére. Kiderült, hogy ennek az adaptációnak nagy része a prediktorváltozók egymásra gyakorolt ​​hatásának kiküszöbölésén múlik. Ez utóbbihoz parciális és félparciális korrelációk társulnak (16. ábra).

Rizs. 16. Többszörös regresszió fejezhető ki a maradékok páronkénti regressziójával (a képleteket lásd az Excel fájlban)

Az Excelben vannak olyan függvények, amelyek információt szolgáltatnak a t- és F-eloszlásokról. Azok a függvények, amelyek neve tartalmazza a DIST részt, mint például a STUDENT.DIST() és F.DIST() t-tesztet vagy F-tesztet vesz fel argumentumként, és egy megadott érték megfigyelésének valószínűségét adja vissza. Azok a függvények, amelyek neve tartalmazza az OBR részt, mint például a STUDENT.INV() és F.INR(), valószínűségi értéket vesz fel argumentumként, és a megadott valószínűségnek megfelelő kritériumértéket ad vissza.

Mivel a t-eloszlás kritikus értékeit keressük, amelyek levágják a farokrégióinak éleit, ezért 5%-ot adunk át argumentumként az egyik STUDENT.INV() függvénynek, amely az ennek a valószínűségnek megfelelő értéket adja vissza. (17., 18. ábra).

Rizs. 17. Kétfarkú t-próba

Rizs. 18. Egyfarkú t-próba

Ha döntési szabályt hoz létre az egyoldalas alfa régióra, növeli a teszt statisztikai erejét. Ha egy kísérlet megkezdésekor biztos abban, hogy minden oka megvan arra, hogy pozitív (vagy negatív) regressziós együtthatót várjon, akkor végezzen egy egyfarkú tesztet. Ebben az esetben nagyobb a valószínűsége annak, hogy helyes döntést hoz, amikor elutasítja a nulla regressziós együttható hipotézisét a populációban.

A statisztikusok szívesebben használják ezt a kifejezést irányított teszt kifejezés helyett egyfarkú tesztés kifejezés irányítatlan teszt kifejezés helyett kétfarkú teszt. Az irányított és irányítatlan kifejezéseket részesítjük előnyben, mert inkább a hipotézis típusát hangsúlyozzák, mint az eloszlás farkainak természetét.

A prediktorok hatásának modell-összehasonlításon alapuló megközelítése.ábrán. A 19. ábra egy regressziós elemzés eredményeit mutatja be, amely a Diéta változónak a regressziós egyenlethez való hozzájárulását vizsgálja.

Rizs. 19. Két modell összehasonlítása az eredményeik közötti különbségek tesztelésével

A LINEST() függvény eredményei (H2:K6 tartomány) összefüggenek az általam teljes modellnek nevezett modellel, amely az LDL-változót regresszi a Diéta, Életkor és HDL változókon. A H9:J13 tartomány számításokat mutat be a Diéta előrejelző változó figyelembevétele nélkül. Ezt nevezem limitált modellnek. A teljes modellben az LDL függő változó varianciájának 49,2%-át a prediktor változók magyarázták. A korlátozott modellben az LDL-nek csak 30,8%-a magyarázható az életkor és a HDL változókkal. Az R 2 vesztesége a Diéta változó modellből való kizárása miatt 0,183. A G15:L17 tartományban olyan számításokat végeznek, amelyek azt mutatják, hogy csak 0,0288 a valószínűsége annak, hogy a Diéta változó hatása véletlenszerű. A fennmaradó 97,1%-ban az étrend hatással van az LDL-re.

6. fejezet: Feltételezések és óvintézkedések a regressziós elemzéshez

A „feltevés” kifejezés nincs elég szigorúan definiálva, és használatának módja is azt sugallja, hogy ha a feltételezés nem teljesül, akkor az egész elemzés eredményei legalábbis megkérdőjelezhetőek vagy talán érvénytelenek. Ez valójában nem így van, bár biztosan vannak olyan esetek, amikor egy feltételezés megszegése alapjaiban változtatja meg a képet. Alapfeltevés: a) az Y változó maradékai normálisan eloszlanak a regressziós egyenes mentén bármely X pontban; b) az Y értékek lineárisan függenek az X értékektől; c) a maradékok szórása minden X pontban megközelítőleg azonos; d) a maradékok között nincs függés.

Ha a feltételezések nem játszanak jelentős szerepet, a statisztikusok azt mondják, hogy az elemzés robusztus a feltételezés megsértésére. Különösen, ha regressziót használ a csoportátlagok közötti különbségek tesztelésére, az a feltevés, hogy az Y értékek – és így a maradékok – normálisan eloszlanak, nem játszik jelentős szerepet: a tesztek robusztusak a normalitási feltételezés megsértésére. Fontos az adatok diagramok segítségével történő elemzése. Például benne van a kiegészítőben Adatelemzés eszköz Regresszió.

Ha az adatok nem felelnek meg a lineáris regresszióra vonatkozó feltételezéseknek, akkor a lineáris regresszión kívül más megközelítések is rendelkezésre állnak. Az egyik a logisztikus regresszió (20. ábra). A prediktor változó felső és alsó határa közelében a lineáris regresszió irreális előrejelzéseket eredményez.

Rizs. 20. Logisztikus regresszió

ábrán. A 6.8. ábra két olyan adatelemzési módszer eredményét mutatja be, amelyek célja az éves jövedelem és a lakásvásárlás valószínűsége közötti kapcsolat vizsgálata. Nyilvánvaló, hogy a vásárlás valószínűsége a bevétel növekedésével nő. A diagramok megkönnyítik a különbségek észlelését azon eredmények között, hogy a lineáris regresszió előrejelzi a lakásvásárlás valószínűségét, és az eltérő megközelítéssel elérhető eredmények között.

A statisztikus szóhasználatban a nullhipotézis elvetését, ha az igaz, I. típusú hibának nevezzük.

A kiegészítőben Adatelemzés kényelmes eszközt kínál véletlen számok generálására, lehetővé téve a felhasználó számára az eloszlás kívánt alakjának (például Normál, Binomiális vagy Poisson) megadását, valamint az átlagot és a szórást.

A STUDENT.DIST() család függvényei közötti különbségek. Az Excel 2010-től kezdődően a függvény három különböző formája érhető el, amelyek az eloszlás arányát adják vissza az adott t-teszt értékétől balra és/vagy jobbra. A STUDENT.DIST() függvény az eloszlási görbe alatti területnek a megadott t-teszt értékétől balra eső részét adja vissza. Tegyük fel, hogy 36 megfigyelése van, tehát az elemzés szabadságfokainak száma 34, a t-próba értéke pedig 1,69. Ebben az esetben a képlet

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

0,05, azaz 5% értéket ad vissza (21. ábra). A STUDENT.DIST() függvény harmadik argumentuma lehet IGAZ vagy HAMIS. Ha IGAZ értéke van, a függvény a görbe alatti kumulatív területet adja vissza a megadott t-próbától balra, arányban kifejezve. Ha HAMIS, a függvény a görbe relatív magasságát adja vissza a t-próbának megfelelő pontban. A STUDENT.DIST() függvény más verziói - TANULÓ.TOL.PH() és STUDENT.DIST.2X() - csak a t-teszt értékét és a szabadságfokok számát veszik argumentumként, és nem szükséges megadni egy harmadikat. érv.

Rizs. 21. Az eloszlás bal oldalán lévő sötétebb árnyalatú terület a görbe alatti terület arányának felel meg a nagy pozitív t-teszt értékétől balra

A t-próbától jobbra eső terület meghatározásához használja az alábbi képleteket:

1 — STODENT.DIST (1, 69;34;IGAZ)

DIÁK.KÖR.PH(1,69;34)

A görbe alatti teljes területnek 100%-nak kell lennie, így 1-ből kivonva a függvény által visszaadott t-próba értékétől balra eső terület töredékét kapjuk a t-próba értékétől jobbra eső terület töredékét. Előnyösnek tarthatja, ha közvetlenül megkapja az Önt érdeklő területtört a STUDENT.DIST.PH() függvény segítségével, ahol a PH az eloszlás jobb végét jelenti (22. ábra).

Rizs. 22. 5% alfa régió irányvizsgálathoz

A TANULÓ.ELTOLÓDÁS() vagy a DIÁK.ELTÁLÁS.PH() függvények használata azt jelenti, hogy Ön egy irányított munkahipotézist választott. Az irányított munkahipotézis az alfa érték 5%-ra állításával kombinálva azt jelenti, hogy mind az 5%-ot az eloszlások jobb végébe helyezzük. Csak akkor kell elutasítania a nullhipotézist, ha a kapott t-próbaérték valószínűsége 5% vagy kevesebb. Az irányított hipotézisek általában érzékenyebb statisztikai teszteket eredményeznek (ezt a nagyobb érzékenységet nagyobb statisztikai erőnek is nevezik).

Irányítatlan tesztben az alfa érték ugyanazon az 5%-os szinten marad, de az eloszlás más lesz. Mivel két eredményt kell megengednie, a hamis pozitív eredmény valószínűségét meg kell osztani az eloszlás két vége között. Általánosan elfogadott, hogy ezt a valószínűséget egyenlően osztják el (23. ábra).

Ugyanazt a kapott t-próbaértéket és ugyanannyi szabadsági fokot használva, mint az előző példában, használja a képletet

DIÁK TOVÁBB.2X(1,69;34)

Minden különösebb ok nélkül a STUDENT.DIST.2X() függvény a #SZÁM! hibakódot adja vissza, ha negatív t-teszt értéket ad meg első argumentumként.

Ha a minták eltérő mennyiségű adatot tartalmaznak, használja a csomagban található kétmintás t-próbát különböző varianciákkal Adatelemzés.

7. fejezet: Regresszió használata a csoportátlagok közötti különbségek tesztelésére

Azokat a változókat, amelyek korábban prediktorváltozók néven szerepeltek, ebben a fejezetben kimeneti változóknak nevezzük, és a prediktorváltozók kifejezés helyett a faktorváltozók kifejezést használjuk.

A névleges változó kódolásának legegyszerűbb módja az álkódolás(24. ábra).

Rizs. 24. Dummy kódoláson alapuló regressziós elemzés

Bármilyen álkódolás használatakor a következő szabályokat kell követni:

• Az új adatok számára fenntartott oszlopok számának egyenlőnek kell lennie a faktorszintek számának mínuszával
• Minden vektor egy faktorszintet képvisel.
• Az egyik szinten lévő alanyok, amelyek gyakran a kontrollcsoport, minden vektorban 0-val vannak kódolva.

Az F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) cellákban lévő képlet regressziós statisztikákat ad vissza. Összehasonlításképpen az ábrán. A 24. ábra az eszköz által visszaadott hagyományos ANOVA eredményeit mutatja be. Egyirányú ANOVA kiegészítőket Adatelemzés.

Hatások kódolása. Egy másik típusú kódolásban az ún effektus kódolás, Az egyes csoportok átlagát összehasonlítjuk a csoportátlagok átlagával. Az effektuskódolás ezen aspektusa annak köszönhető, hogy 0 helyett -1-et használnak a csoport kódjaként, amely minden kódvektorban ugyanazt a kódot kapja (25. ábra).

Rizs. 25. Hatáskódolás

Ha álkódolást használunk, a LINEST() által visszaadott állandó érték annak a csoportnak az átlaga, amelyhez az összes vektorban nulla kód van hozzárendelve (általában a referenciacsoportban). Hatáskódolás esetén az állandó egyenlő a teljes átlaggal (J2 cella).

Az általános lineáris modell hasznos módja egy kimeneti változó értékének összetevőinek fogalmi meghatározásának:

Y ij = μ + α j + ε ij

A görög betűk használata ebben a képletben a latin betűk helyett azt a tényt hangsúlyozza, hogy arra a sokaságra vonatkozik, amelyből mintákat vettek, de átírható, jelezve, hogy egy adott sokaságból vett mintákra vonatkozik:

Y ij = Y̅ + a j + e ij

Az ötlet az, hogy minden Y ij megfigyelés a következő három komponens összegeként tekinthető: a nagy átlag, μ; j és j kezelés hatása; e ij érték, amely az egyedi Y ij mennyiségi mutató eltérését jelenti az általános átlag és a j-edik kezelés hatásának együttes értékétől (26. ábra). A regressziós egyenlet célja a maradékok négyzetösszegének minimalizálása.

Rizs. 26. Általános lineáris modell komponenseire bontott megfigyelések

Faktoranalízis. Ha egyidejűleg vizsgáljuk az eredményváltozó és két vagy több tényező kapcsolatát, akkor ebben az esetben faktoranalízisről beszélünk. Egy vagy több tényező hozzáadása az egyirányú ANOVA-hoz növelheti a statisztikai teljesítményt. Az egytényezős varianciaanalízis során az eredményváltozó olyan szórását, amely nem tulajdonítható faktornak, a maradék átlagnégyzetben szerepel. De könnyen lehet, hogy ez a változás egy másik tényezőhöz kapcsolódik. Ekkor ez a változás kivehető az átlagos négyzetes hibából, amelynek csökkenése az F-próba értékeinek növekedéséhez, ezáltal a teszt statisztikai erejének növekedéséhez vezet. Felépítmény Adatelemzés két tényezőt egyidejűleg feldolgozó eszközt tartalmaz (27. ábra).

Rizs. 27. Eszköz Kétirányú varianciaanalízis az Analysis Package ismétlésével

Az ábrán használt ANOVA eszköz azért hasznos, mert visszaadja az eredményváltozó átlagát és szórását, valamint a számláló értékét minden, a tervezésben szereplő csoportra vonatkozóan. Az asztalban Varianciaanalízis két olyan paramétert jelenít meg, amelyek nem szerepelnek az ANOVA eszköz egytényezős verziójának kimenetében. Ügyeljen a variációs forrásokra MintaÉs Oszlopok a 27. és 28. sorban. Változások forrása Oszlopok nemre utal. Változások forrása Minta bármely olyan változóra utal, amelynek értékei különböző sorokat foglalnak el. ábrán. A KursLech1 csoport 27 értéke a 2-6 sorban, a KursLech2 csoport a 7-11 sorban, a KursLechZ csoport pedig a 12-16 sorban található.

A lényeg az, hogy mindkét tényező, a nem (az E28-as cellában az oszlopok címkéje) és a kezelés (minta az E27-es cellában) szerepel az ANOVA-táblázatban, mint variációs források. A férfiak eszközei eltérnek a nők eszközeitől, és ez változatosságot teremt. A három kezelés eszközei is különböznek egymástól, ami egy másik variációs forrást jelent. Van egy harmadik forrás is, az Interaction, amely a Nem és a Kezelés változók együttes hatására vonatkozik.

8. fejezet A kovariancia elemzése

A kovariancia analízis vagy az ANCOVA (Analysis of Covariation) csökkenti a torzítást és növeli a statisztikai teljesítményt. Hadd emlékeztesselek arra, hogy a regressziós egyenlet megbízhatóságának értékelésének egyik módja az F-próba:

F = MS regresszió/MS maradék

ahol MS (Mean Square) az átlagos négyzet, a regressziós és maradék index pedig a regressziós és a maradék komponenseket jelzi. Az MS maradékot a következő képlettel számítjuk ki:

MS Residual = SS Residual / df Residual

ahol SS (Sum of Squares) a négyzetek összege, df pedig a szabadságfokok száma. Ha kovarianciát ad hozzá egy regressziós egyenlethez, akkor a teljes négyzetösszeg egy része nem az SS ResiduaI-ben, hanem az SS-regresszióban szerepel. Ez az SS Residua l, és ezáltal az MS Residual csökkenéséhez vezet. Minél kisebb az MS Residual, annál nagyobb az F-teszt, és annál valószínűbb, hogy elutasítja azt a nullhipotézist, hogy nincs különbség az átlagok között. Ennek eredményeként újra elosztja az eredményváltozó változékonyságát. Az ANOVA-ban, ha a kovarianciát nem vesszük figyelembe, a változékonyság hibává válik. Az ANCOVA-ban azonban a korábban a hibatagnak tulajdonított variabilitás egy része egy kovariánshoz van rendelve, és az SS-regresszió részévé válik.

Vegyünk egy példát, amelyben ugyanazt az adathalmazt először ANOVA-val, majd ANCOVA-val elemzik (28. ábra).

Rizs. 28. Az ANOVA elemzés azt mutatja, hogy a regressziós egyenletből kapott eredmények megbízhatatlanok

A tanulmány az izomerőt fejlesztő fizikai gyakorlatok és az agyi aktivitást serkentő kognitív gyakorlatok (keresztrejtvényfejtés) relatív hatásait hasonlítja össze. Az alanyokat véletlenszerűen két csoportba osztották, így mindkét csoport azonos körülményeknek volt kitéve a kísérlet elején. Három hónap elteltével megmérték az alanyok kognitív teljesítményét. E mérések eredményeit a B oszlop mutatja.

Az A2:C21 tartomány tartalmazza a LINEST() függvénynek átadott forrásadatokat az effektuskódolás segítségével történő elemzés végrehajtásához. A LINEST() függvény eredményei az E2:F6 tartományban vannak megadva, ahol az E2 cella az impakt vektorhoz tartozó regressziós együtthatót jeleníti meg. Az E8 cella t-teszt = 0,93 értéket tartalmaz, és az E9 cella ennek a t-tesztnek a megbízhatóságát teszteli. Az E9 cellában található érték azt jelzi, hogy a kísérletben megfigyelt csoportátlagok közötti különbség előfordulásának valószínűsége 36%, ha a csoportátlagok egyenlőek a populációban. Kevesen tartják ezt az eredményt statisztikailag szignifikánsnak.

ábrán. A 29. ábra azt mutatja, hogy mi történik, ha egy kovariánst adunk az elemzéshez. Ebben az esetben az egyes alanyok életkorát hozzáadtam az adatkészlethez. A kovariánst használó regressziós egyenlet R 2 determinációs együtthatója 0,80 (F4 cella). Az R 2 érték az F15:G19 tartományban, amelyben megismételtem a kovariáns nélkül kapott ANOVA eredményeket, csak 0,05 (F17 cella). Ezért a kovariánst tartalmazó regressziós egyenlet sokkal pontosabban jósolja meg a kognitív pontszám változó értékeit, mint az impakt vektor önmagában történő használata. Az ANCOVA esetében az F5 cellában megjelenő F-teszt értékének véletlen megszerzésének valószínűsége kisebb, mint 0,01%.

Rizs. 29. Az ANCOVA egészen más képet hoz vissza

Ismeretes, hogy hasznos különféle tevékenységi területeken, beleértve az ökonometriát is, ahol ezt a segédprogramot a munkában használják. Alapvetően a gyakorlati és laboratóriumi órák minden tevékenysége Excelben történik, ami nagyban megkönnyíti a munkát azáltal, hogy részletes magyarázatot ad bizonyos műveletekről. Így az egyik „Regresszió” elemzőeszközt arra használjuk, hogy a legkisebb négyzetek módszerével grafikont válasszunk ki egy megfigyelési halmazhoz. Nézzük meg, mi ez a programeszköz, és milyen előnyei vannak a felhasználók számára. Az alábbiakban rövid, de egyértelmű utasításokat is talál a regressziós modell felépítéséhez.

A regresszió fő feladatai és típusai

A regresszió az adott változók közötti kapcsolatot reprezentálja, ezáltal lehetővé teszi e változók jövőbeni viselkedésének előrejelzését. A változók különféle periodikus jelenségek, beleértve az emberi viselkedést is. Az Excel ilyen típusú elemzése arra szolgál, hogy elemezze egy vagy több változó értékének egy adott függő változóra gyakorolt ​​hatását. Például a bolti eladásokat számos tényező befolyásolja, beleértve a választékot, az árakat és az üzlet elhelyezkedését. Az Excelben történő regressziónak köszönhetően a meglévő értékesítések eredményei alapján meghatározhatja ezen tényezők mindegyikének befolyásának mértékét, majd a kapott adatokat alkalmazhatja egy másik hónapra vagy egy másik közeli üzletre vonatkozó eladások előrejelzésére.

A regressziót általában egy egyszerű egyenletként mutatják be, amely felfedi két változócsoport közötti kapcsolatok összefüggéseit és erősségeit, ahol az egyik csoport függő vagy endogén, a másik pedig független vagy exogén. Ha van egy csoport egymással összefüggő mutató, akkor az Y függő változót az érvelés logikája alapján határozzuk meg, a többi pedig független X változóként működik.

A regressziós modell felépítésének fő feladatai a következők:

1. Szignifikáns független változók (X1, X2, ..., Xk) kiválasztása.
2. A funkció típusának kiválasztása.
3. Becslések készítése együtthatókra.
4. Konfidenciaintervallumok és regressziós függvények felépítése.
5. A számított becslések és a megszerkesztett regressziós egyenlet jelentőségének ellenőrzése.

A regressziós elemzésnek többféle típusa van:

• páros (1 függő és 1 független változó);
• többszörös (több független változó).

Kétféle regressziós egyenlet létezik:

1. Lineáris, amely a változók közötti szigorú lineáris kapcsolatot szemlélteti.
2. Nemlineáris – Hatványokat, törteket és trigonometrikus függvényeket tartalmazó egyenletek.

Útmutató a modell elkészítéséhez

Egy adott konstrukció Excelben való végrehajtásához kövesse az alábbi utasításokat:

További számításokhoz használja a „Linear()” függvényt, amely megadja az Y értékeket, az X értékeket, a Const és a Statisztikát. Ezt követően a "Trend" függvény segítségével határozza meg a regressziós egyenes pontkészletét - Y értékek, X értékek, új értékek, állandó. A megadott paraméterek segítségével számítsa ki az együtthatók ismeretlen értékét, a feladat adott feltételei alapján.

KORRELÁCIÓS ÉS REGRESSZIÓS ELEMZÉS INKISASSZONY EXCEL

1. Hozzon létre egy forrásadatfájlt MS Excelben (például 2. táblázat)

2. A korrelációs mező felépítése

Korrelációs mező létrehozásához a parancssorban válassza a menüt Beszúrás/diagram. A megjelenő párbeszédpanelen válassza ki a diagram típusát: Folt; Kilátás: Szórványrajz, amely lehetővé teszi az értékpárok összehasonlítását (22. ábra).

22. ábra - A diagram típusának kiválasztása

23. ábra - Az ablak nézete tartomány és sorozat kiválasztásakor
25. ábra – Ablaknézet, 4. lépés

2. A helyi menüben válassza ki a parancsot Adjon hozzá egy trendvonalat.

3. A megjelenő párbeszédpanelen válasszuk ki a grafikon típusát (példánkban lineáris) és az egyenlet paramétereit a 26. ábrán látható módon.

Kattintson az OK gombra. Az eredményt a 27. ábra mutatja.

27. ábra – A munkatermelékenység tőke-munka aránytól való függésének korrelációs mezője

Hasonlóképpen korrelációs mezőt szerkesztünk a munkatermelékenységnek a berendezésváltási aránytól való függésére. (28. ábra).

28. ábra – A munkatermelékenység korrelációs mezője

a berendezések cserearányáról

3. A korrelációs mátrix felépítése.

Korrelációs mátrix felépítése a menüben Szolgáltatás választ Adatelemzés.

Adatelemző eszköz használata Regresszió, a regressziós statisztika, a varianciaanalízis és a konfidenciaintervallumok eredményein kívül maradékokat és grafikonokat kaphat a regressziós egyenes illesztéséről, a reziduumokról és a normálvalószínűségről. Ehhez ellenőriznie kell az elemzési csomaghoz való hozzáférést. A főmenüben válassza a lehetőséget Szolgáltatás/Kiegészítők. Jelölje be a négyzetet Elemző csomag(29. ábra)

30. ábra – Párbeszédpanel Adatelemzés

Az OK gomb megnyomása után a megjelenő párbeszédablakban adja meg a beviteli intervallumot (A2:D26 példánkban), a csoportosítást (esetünkben oszlopok szerint) és a kimeneti paramétereket, a 31. ábra szerint.

31. ábra – Párbeszédpanel Korreláció

A számítási eredményeket a 4. táblázat tartalmazza.

4. táblázat – Korrelációs mátrix

	1. oszlop	2. oszlop	3. oszlop
1. oszlop
2. oszlop
3. oszlop

EGY TÉNYEZŐS REGRESSZIÓS ELEMZÉS

A REGRESSZIÓS ESZKÖZ HASZNÁLATA

A munkatermelékenységnek a menüben szereplő tőke-munka aránytól való függésének regressziós elemzése Szolgáltatás választ Adatelemzésés adja meg az elemző eszközt Regresszió(32. ábra).

33. ábra – Párbeszédpanel Regresszió