Laske Excelin avulla epälineaarisen funktion regressiokertoimet. Korrelaatio- ja regressioanalyysi MS EXCELissä

MS Excel -paketin avulla voit tehdä suurimman osan työstä erittäin nopeasti, kun muodostat lineaarisen regressioyhtälön. On tärkeää ymmärtää, miten saatuja tuloksia tulkitaan.

Vaatii lisäosan toimiakseen Analyysipaketti, joka on otettava käyttöön valikkokohdassa Palvelu\Lisäosat

Voit ottaa analyysipaketin käyttöön Excel 2007:ssä napsauttamalla Siirry estämään Excel-asetukset napsauttamalla vasemmassa yläkulmassa olevaa painiketta ja sitten " Excel-asetukset"ikkunan alareunassa:

Regressiomallin luomiseksi sinun on valittava kohde Palvelu\Tietojen analyysi\Regressio. (Excel 2007:ssä tämä tila on lohkossa Data/Data-analyysi/Regressio). Näyttöön tulee valintaikkuna, joka sinun on täytettävä:

1) Syöttöväli Y¾ sisältää linkin soluihin, jotka sisältävät tuloksena olevan ominaisuuden arvot y. Arvot on järjestettävä sarakkeeseen;

2) Syöttöväli X¾ sisältää linkin soluihin, jotka sisältävät tekijäarvoja. Arvot on järjestettävä sarakkeisiin;

3) Allekirjoita Tunnisteet aseta, jos ensimmäiset solut sisältävät selittävää tekstiä (tietotunnisteita);

4) Luotettavuustaso¾ on luottamustaso, jonka oletuksena pidetään 95 %. Jos tämä arvo ei sovi sinulle, sinun on otettava tämä lippu käyttöön ja syötettävä vaadittu arvo;

5) Allekirjoita Vakio-nolla on mukana, jos on tarpeen muodostaa yhtälö, jossa vapaa muuttuja on ;

6) Lähtöasetukset määrittää, mihin tulokset sijoitetaan. Oletuksena koontitila Uusi laskentataulukko;

7) Estä Ylijäämät voit sisällyttää jäännöstulokset ja niiden kaavioiden rakentamisen.

Tämän seurauksena näyttöön tulee tiedot, jotka sisältävät kaikki tarvittavat tiedot ja ryhmitellään kolmeen lohkoon: Regressiotilastot, Varianssianalyysi, Tasapainon poistaminen. Katsotaanpa niitä tarkemmin.

1. Regressiotilastot:

useita R määräytyy kaavalla ( Pearsonin korrelaatiokerroin);

R (determinaatiokerroin);

Normalisoitu R-neliö lasketaan kaavalla (käytetään moninkertaiseen regressioon);

Normaali virhe S lasketaan kaavalla ;

Havainnot ¾ on tiedon määrä n.

2. Varianssianalyysi, rivi Regressio:

Parametri df on yhtä suuri m(tekijäjoukkojen määrä x);

Parametri SS määritetään kaavalla ;

Parametri MS määritetään kaavalla ;

Tilastot F määritetään kaavalla ;

Merkitys F. Jos saatu luku on suurempi kuin , hypoteesi hyväksytään (ei ole lineaarista suhdetta), muuten hypoteesi hyväksytään (on lineaarinen suhde).

3. Varianssianalyysi, rivi Loput:

Parametri df yhtä suuri kuin ;

Parametri SS määräytyy kaavan mukaan ;

Parametri MS määräytyy kaavan mukaan.

4. Varianssianalyysi, rivi Kokonais sisältää kahden ensimmäisen sarakkeen summan.

5. Varianssianalyysi, rivi Y-risteys sisältää kertoimen, keskivirheen ja t-tilastot.

P-arvo ¾ on laskettua vastaavien merkitsevyystasojen arvo t-tilastomiehet. Määritetään funktiolla STUDIST( t-tilastot; ). Jos P-arvo ylittää, silloin vastaava muuttuja on tilastollisesti merkityksetön ja voidaan jättää mallin ulkopuolelle.

Pohja 95 % Ja Top 95 %¾ ovat teoreettisen lineaarisen regressioyhtälön kertoimien 95 prosentin luottamusvälin ala- ja ylärajat. Jos tietojen syöttölohkon luottamustodennäköisyysarvo jätettiin oletusarvoonsa, kaksi viimeistä saraketta kopioivat edelliset. Jos käyttäjä on syöttänyt oman luottamusarvonsa, kahdessa viimeisessä sarakkeessa on määritetyn luottamustason ala- ja yläraja-arvot.

6. Varianssianalyysi, rivit sisältävät kertoimien arvot, standardivirheet, t-tilastotieteilijä, P-arvot ja luottamusvälit vastaavalle .

7. Estä Tasapainon poistaminen sisältää ennustetut arvot y(käsityksessämme tämä on ) ja jäännökset .

Lineaarisen regression rakentaminen, sen parametrien ja niiden merkityksen arviointi voidaan suorittaa paljon nopeammin Excel-analyysipaketilla (Regression). Tarkastellaan saatujen tulosten tulkintaa yleisessä tapauksessa ( k selittävät muuttujat) esimerkin 3.6 mukaisesti.

Taulukossa regressiotilastot seuraavat arvot annetaan:

Useita R – moninkertainen korrelaatiokerroin;

R- neliö– determinaatiokerroin R 2 ;

Normalisoitu R - neliö- säädetty R 2 vapausasteiden lukumäärän mukaan säädettynä;

Normaali virhe– regression standardivirhe S;

Havainnot - havaintojen määrä n.

Taulukossa Varianssianalyysi annetaan:

1. Sarake df - vapausasteiden lukumäärä yhtä suuri kuin

merkkijonoa varten Regressio df = k;

merkkijonoa varten Loputdf = n – k – 1;

merkkijonoa varten Kokonaisdf = n– 1.

2. Sarake SS – neliöityjen poikkeamien summa on yhtä suuri kuin

merkkijonoa varten Regressio ;

merkkijonoa varten Loput ;

merkkijonoa varten Kokonais .

3. Sarake MS kaavan määrittämät varianssit MS = SS/df:

merkkijonoa varten Regressio– tekijädispersio;

merkkijonoa varten Loput– jäännösvarianssi.

4. Sarake F – laskettu arvo F-kriteeri lasketaan kaavalla

F = MS(regressio)/ MS(loppu).

5. Sarake Merkitys F – laskettua merkitsevyystason arvo F-tilastot .

Merkitys F= FDIST( F- tilastot, df(regressio), df(loput)).

Jos merkitystä F < стандартного уровня значимости, то R 2 on tilastollisesti merkitsevä.

Tämä taulukko näyttää:

1. Kertoimet– kertoimen arvot a, b.

2. Vakiovirhe– regressiokertoimien keskivirheet S a, Sb.

3. t- tilastot– lasketut arvot t -kriteerit lasketaan kaavalla:

t-statistic = kertoimet/standardivirhe.

4.R-arvo (merkitys t) on laskettua merkitsevyystason arvo t- tilastot.

R-arvo = TUTKIJA(t-tilastot, df(loput)).

Jos R-merkitys< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. Pohja 95 % ja ylä 95 %– 95 ​​%:n luottamusvälin ala- ja ylärajat teoreettisen lineaarisen regressioyhtälön kertoimille.

Taulukossa MUUN PERUUTTAMINEN ilmoitettu:

sarakkeessa Havainto– havainnon numero;

sarakkeessa ennustettu y – riippuvan muuttujan lasketut arvot;

sarakkeessa Ylijäämät e – riippuvan muuttujan havaittujen ja laskettujen arvojen välinen ero.

Esimerkki 3.6. Elintarvikekustannuksista on tietoa (tavanomaiset yksiköt). y ja tulot asukasta kohden x yhdeksälle perheryhmälle:

Excel-analyysipaketin (Regression) tulosten avulla analysoimme ruokakustannusten riippuvuutta asukasta kohden lasketuista tuloista.

Regressioanalyysin tulokset kirjoitetaan yleensä muodossa:

jossa regressiokertoimien keskivirheet on merkitty suluissa.

Regressiokertoimet A = 65,92 ja b= 0,107. Viestinnän suunta välillä y Ja x määrittää regressiokertoimen etumerkin b= 0,107, so. yhteys on suora ja positiivinen. Kerroin b= 0,107 osoittaa, että asukaskohtaisten tulojen kasvaessa 1 tavanomaista. yksiköitä ruokakustannukset nousevat 0,107 tavanomaista yksikköä. yksiköitä

Arvioidaan tuloksena olevan mallin kertoimien merkitys. Kertoimien merkitys ( a, b) on tarkistanut t-testata:

P-arvo ( a) = 0,00080 < 0,01 < 0,05

P-arvo ( b) = 0,00016 < 0,01 < 0,05,

siksi kertoimet ( a, b) ovat merkittäviä 1 %:n tasolla ja vielä enemmän 5 %:n merkitsevyystasolla. Siten regressiokertoimet ovat merkittäviä ja malli on riittävä alkuperäiseen dataan.

Regressioestimoinnin tulokset ovat yhteensopivia paitsi saatujen regressiokertoimien arvojen kanssa, myös niiden tietyn joukon kanssa (luottamusväli). 95 %:n todennäköisyydellä kertoimien luottamusvälit ovat (38,16 – 93,68) a ja (0,0728 – 0,142) varten b.

Mallin laatua arvioidaan determinaatiokertoimella R 2 .

Suuruus R 2 = 0,884 tarkoittaa, että tulokerroin asukasta kohti voi selittää 88,4 % ruokakulujen vaihtelusta (hajonnasta).

Merkitys R 2 on tarkastanut F- testi: merkitys F = 0,00016 < 0,01 < 0,05, следовательно, R 2 on merkitsevä 1 %:n tasolla ja vielä enemmän 5 %:n merkitsevyystasolla.

Parittaisen lineaarisen regression tapauksessa korrelaatiokerroin voidaan määritellä seuraavasti . Saatu korrelaatiokertoimen arvo osoittaa, että ruokamenojen ja asukasta kohden laskettujen tulojen välinen suhde on hyvin läheinen.

IN Excel On olemassa vielä nopeampi ja kätevämpi tapa piirtää lineaarista regressiota (ja jopa epälineaaristen regressioiden päätyypit, kuten alla käsitellään). Tämä voidaan tehdä seuraavasti:

1) valitse sarakkeet, joissa on tietoja X Ja Y(niiden pitäisi olla tässä järjestyksessä!);

2) soita Ohjattu kaaviotoiminto ja valitse ryhmästä Tyyppi – Kohta ja paina heti Valmis;

3) poistamatta kaavion valintaa, valitse näkyviin tuleva päävalikon kohta Kaavio, josta sinun tulee valita kohde Lisää trendiviiva;

4) näkyviin tulevassa valintaikkunassa Trendiviiva välilehdellä Tyyppi valita Lineaarinen;

5) välilehdellä Vaihtoehdot voit aktivoida kytkimen Näytä yhtälö kaaviossa, jonka avulla voit nähdä lineaarisen regressioyhtälön (4.4), jossa kertoimet (4.5) lasketaan.

6) Samassa välilehdessä voit aktivoida kytkimen Aseta approksimaatioluotettavuusarvo (R^2) kaavioon. Tämä arvo on korrelaatiokertoimen (4.3) neliö ja se osoittaa kuinka hyvin laskettu yhtälö kuvaa kokeellista riippuvuutta. Jos R 2 on lähellä yksikköä, niin teoreettinen regressioyhtälö kuvaa kokeellista riippuvuutta hyvin (teoria sopii hyvin kokeeseen), ja jos R 2 on lähellä nollaa, silloin tämä yhtälö ei sovellu kuvaamaan kokeellista riippuvuutta (teoria ei ole samaa mieltä kokeen kanssa).

Kuvattujen toimien suorittamisen seurauksena saat kaavion, jossa on regressiokaavio ja sen yhtälö.

§4.3. Epälineaarisen regression päätyypit

Parabolinen ja polynomiregressio.

Parabolinen arvon riippuvuus Y koosta alkaen X kutsutaan riippuvuudeksi, joka ilmaistaan ​​neliöfunktiolla (2. asteen paraabeli):

Tätä yhtälöä kutsutaan parabolinen regressioyhtälö Y päällä X. Vaihtoehdot A, b, Kanssa kutsutaan paraboliset regressiokertoimet. Parabolisten regressiokertoimien laskeminen on aina työlästä, joten on suositeltavaa käyttää tietokonetta laskelmissa.

Parabolisen regression yhtälö (4.8) on erikoistapaus yleisemmälle regressiolle, jota kutsutaan polynomiksi. Polynomi arvon riippuvuus Y koosta alkaen X kutsutaan polynomilla ilmaistuksi riippuvuudeksi n- järjestys:

missä on numerot ja i (i=0,1,…, n) kutsutaan polynomiregressiokertoimet.

Tehon regressio.

Tehoa arvon riippuvuus Y koosta alkaen X kutsutaan muodon riippuvuudeksi:

Tätä yhtälöä kutsutaan potenssiregressioyhtälö Y päällä X. Vaihtoehdot A Ja b kutsutaan tehon regressiokertoimet.

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

Tämä yhtälö kuvaa suoraa tasossa, jolla on logaritminen koordinaattiakseli ln x ja ln. Siksi potenssiregression sovellettavuuden kriteerinä on vaatimus, että empiirisen datan logaritmien pisteet ln x i ja ln y i olivat lähimpänä suoraa (4.11).

Eksponentiaalinen regressio.

Ohjeellinen(tai eksponentiaalinen) arvon riippuvuus Y koosta alkaen X kutsutaan muodon riippuvuudeksi:

(tai ). (4.12)

Tätä yhtälöä kutsutaan eksponentiaalinen yhtälö(tai eksponentiaalinen) regressio Y päällä X. Vaihtoehdot A(tai k) Ja b kutsutaan eksponentiaaliset kertoimet(tai eksponentiaalinen) regressio.

Jos otamme potenssiregressioyhtälön molempien puolten logaritmin, saamme yhtälön

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

Tämä yhtälö kuvaa yhden suuren ln logaritmin lineaarista riippuvuutta toisesta suureesta x. Siksi potenssiregression sovellettavuuden kriteerinä on vaatimus, että empiiriset datapisteet ovat samanarvoisia x i ja toisen suuren ln logaritmit y i olivat lähimpänä suoraa (4,13).

Logaritminen regressio.

Logaritminen arvon riippuvuus Y koosta alkaen X kutsutaan muodon riippuvuudeksi:

=a+b· ln x. (4.14)

Tätä yhtälöä kutsutaan logaritminen regressioyhtälö Y päällä X. Vaihtoehdot A Ja b kutsutaan logaritmiset regressiokertoimet.

Hyperbolinen regressio.

Hyperbolinen arvon riippuvuus Y koosta alkaen X kutsutaan muodon riippuvuudeksi:

Tätä yhtälöä kutsutaan hyperbolinen regressioyhtälö Y päällä X. Vaihtoehdot A Ja b kutsutaan hyperboliset regressiokertoimet ja määritetään pienimmän neliösumman menetelmällä. Tämän menetelmän soveltaminen johtaa kaavoihin:

Kaavoissa (4.16-4.17) summaus suoritetaan indeksin yli i yhdestä havaintojen määrään n.

Valitettavasti sisään Excel ei ole funktioita, jotka laskevat hyperbolisia regressiokertoimia. Tapauksissa, joissa ei tiedetä, että mitatut suureet liittyvät toisiinsa käänteisellä suhteellisella suhteella, on suositeltavaa etsiä tehoregressioyhtälö hyperbolisen regressioyhtälön sijaan, joten Excel Sen löytämiseksi on menettelytapa. Jos oletetaan mitattujen suureiden välille hyperbolinen riippuvuus, sen regressiokertoimet on laskettava apulaskentataulukoilla ja summausoperaatioilla kaavoilla (4.16-4.17).

Regressioanalyysi Microsoft Excelissä - Kattavin opas MS Excelin käyttämiseen yritysanalytiikan alan regressioanalyysiongelmien ratkaisemiseen. Konrad Carlberg selittää selkeästi teoreettiset kysymykset, joiden tunteminen auttaa välttämään monia virheitä sekä itse suoritettaessa regressioanalyysiä että arvioidessaan muiden tekemien analyysien tuloksia. Kaikki materiaali yksinkertaisista korrelaatioista ja t-testeistä moninkertaiseen kovarianssianalyysiin perustuu tosielämän esimerkkeihin, ja siihen liittyy yksityiskohtaisia ​​vaiheittaisia ​​menettelyjä.

Kirjassa käsitellään Excelin regressiofunktioiden kummallisuuksia ja ristiriitoja, tarkastellaan kunkin vaihtoehdon ja argumentin vaikutuksia ja selitetään, kuinka regressiomenetelmiä voidaan soveltaa luotettavasti lääketieteellisestä tutkimuksesta talousanalyysiin.

Konrad Carlberg. Regressioanalyysi Microsoft Excelissä. – M.: Dialektiikka, 2017. – 400 s.

Lataa muistiinpano muodossa tai muodossa, esimerkit muodossa

Luku 1: Tietojen vaihtelun arviointi

Tilastotyöntekijöillä on käytössään monia variaatiomittareita. Yksi niistä on yksittäisten arvojen keskiarvosta poikkeamien neliöityjen summa. Excelissä tähän käytetään SQUARE()-funktiota. Mutta varianssia käytetään useammin. Dispersio on neliöityjen poikkeamien keskiarvo. Varianssi ei ole herkkä tutkittavan tietojoukon arvojen lukumäärälle (kun taas neliöityjen poikkeamien summa kasvaa mittausten määrän myötä).

Excel tarjoaa kaksi funktiota, jotka palauttavat varianssin: DISP.G() ja DISP.V():

• Käytä DISP.G()-funktiota, jos käsiteltävät arvot muodostavat populaation. Toisin sanoen alueen sisältämät arvot ovat ainoita arvoja, joista olet kiinnostunut.
• Käytä DISP.B()-funktiota, jos käsiteltävät arvot muodostavat otoksen suuremmasta populaatiosta. Oletetaan, että on lisäarvoja, joiden varianssin voit myös arvioida.

Jos jokin suure, kuten keskiarvo tai korrelaatiokerroin, lasketaan populaatiosta, sitä kutsutaan parametriksi. Vastaavaa otoksen perusteella laskettua määrää kutsutaan tilastoksi. Poikkeamien laskeminen keskiarvosta tietyssä joukossa saat pienemmän suuruuden neliöpoikkeamien summan kuin jos lasket ne mistä tahansa muusta arvosta. Samanlainen väite pätee varianssiin.

Mitä suurempi otoskoko on, sitä tarkempi on laskettu tilastoarvo. Mutta ei ole populaatiokokoa pienempää otoskokoa, jonka osalta voit olla varma, että tilastollinen arvo vastaa parametrin arvoa.

Oletetaan, että sinulla on 100 korkeuden joukko, joiden keskiarvo eroaa väestön keskiarvosta riippumatta siitä, kuinka pieni ero on. Laskemalla näytteen varianssin saat arvon, esimerkiksi 4. Tämä arvo on pienempi kuin mikään muu arvo, joka voidaan saada laskemalla kunkin 100 korkeusarvon poikkeama suhteessa mihin tahansa muuhun arvoon kuin otoksen keskiarvoon. , mukaan lukien suhteessa todelliseen väestön keskiarvoon. Siksi laskettu varianssi on erilainen ja pienempi kuin varianssi, jonka saisit, jos jollakin tavalla saisit selville ja käyttäisit populaatioparametria otoskeskiarvon sijaan.

Otokselle määritetty keskimääräinen neliösumma antaa pienemmän arvion populaation varianssista. Tällä tavalla laskettua varianssia kutsutaan siirretty arviointi. Osoittautuu, että harhan eliminoimiseksi ja puolueettoman arvion saamiseksi riittää jakaa neliöpoikkeamien summa, ei n, Missä n- näytteen koko ja n-1.

Suuruus n-1 kutsutaan vapausasteiden lukumääräksi (lukumääräksi). On olemassa erilaisia ​​tapoja laskea tämä määrä, vaikka niissä kaikissa joko vähennetään jokin luku otoskoosta tai lasketaan niiden luokkien lukumäärä, joihin havainnot kuuluvat.

DISP.G()- ja DISP.V()-funktioiden eron olemus on seuraava:

• Funktiossa VAR.G() neliöiden summa jaetaan havaintojen määrällä ja edustaa siten varianssin, todellisen keskiarvon, puolueellista estimaattia.
• DISP.B()-funktiossa neliöiden summa jaetaan havaintojen määrällä miinus 1, ts. vapausasteiden lukumäärällä, mikä antaa tarkemman, puolueettoman arvion sen populaation varianssista, josta otos on otettu.

Standardipoikkeama keskihajonta, SD) – on varianssin neliöjuuri:

Poikkeamien neliöinti muuttaa mitta-asteikon toiseksi mittariksi, joka on alkuperäisen neliö: metrit - neliömetrit, dollarit - neliödollareiksi jne. Keskihajonta on varianssin neliöjuuri, ja siksi se vie meidät takaisin alkuperäisiin mittayksiköihin. Kumpi on kätevämpi.

Keskihajonnan laskeminen on usein tarpeen sen jälkeen, kun tietoja on käsitelty jonkin verran. Ja vaikka näissä tapauksissa tulokset ovat epäilemättä keskihajontoja, niitä yleensä kutsutaan vakiovirheet. Keskivirheitä on useita, mukaan lukien mittauksen standardivirhe, mittasuhteiden standardivirhe ja keskiarvon standardivirhe.

Oletetaan, että olet kerännyt pituusdataa 25 satunnaisesti valitulta aikuiselta mieheltä kussakin 50 osavaltiossa. Seuraavaksi lasket aikuisten miesten keskipituuden kussakin osavaltiossa. Tuloksena saatuja 50 keskiarvoa voidaan puolestaan ​​pitää havaintoja. Tästä voit laskea niiden keskihajonnan, joka on keskiarvon standardivirhe. Riisi. 1. vertaa 1 250 raa'an yksittäisen arvon (25 miehen pituustiedot kussakin 50 osavaltiossa) jakautumista 50 osavaltion keskiarvojen jakaumaan. Kaava keskiarvon keskivirheen (eli keskiarvojen keskihajonnan, ei yksittäisten havaintojen) arvioimiseksi:

missä on keskiarvon standardivirhe; s– alkuperäisten havaintojen keskihajonta; n– havaintojen määrä otoksessa.

Riisi. 1. Keskiarvojen vaihtelu tilasta toiseen on huomattavasti pienempi kuin yksittäisten havaintojen vaihtelu.

Tilastoissa on yleissopimus kreikkalaisten ja latinalaisten kirjainten käyttämisestä tilastollisten määrien esittämiseen. Yleisen väestön parametrit on tapana merkitä kreikkalaisilla kirjaimilla ja näytetilastot latinalaisilla kirjaimilla. Siksi, kun puhutaan perusjoukon keskihajonnasta, kirjoitamme sen muodossa σ; jos otetaan huomioon otoksen keskihajonta, niin käytämme merkintää s. Mitä tulee keskiarvoja osoittaviin symboleihin, ne eivät sovi keskenään niin hyvin. Väestön keskiarvo on merkitty kreikkalaisella kirjaimella μ. Kuitenkin symbolia X̅ käytetään perinteisesti edustamaan otoskeskiarvoa.

z-pisteet ilmaisee havainnon paikan jakaumassa keskihajonnan yksiköissä. Esimerkiksi z = 1,5 tarkoittaa, että havainto on 1,5 keskihajonnan päässä keskiarvosta. Termi z-pisteet käytetään yksittäisiin arviointeihin, ts. yksittäisille näyteelementeille määritetyille mitoille. Termi, jota käytetään viittaamaan tällaisiin tilastoihin (kuten osavaltion keskiarvo) z-pisteet:

missä X̅ on otoksen keskiarvo, μ on perusjoukon keskiarvo, on näytejoukon keskiarvojen keskivirhe:

missä σ on perusjoukon keskivirhe (yksittäiset mittaukset), n– näytteen koko.

Oletetaan, että työskentelet golfklubin ohjaajana. Olet pystynyt mittaamaan laukaustesi etäisyyttä pitkän ajanjakson aikana ja tiedät, että keskiarvo on 205 jaardia ja keskihajonta 36 jaardia. Sinulle tarjotaan uutta mailaa väittäen, että se lisää lyöntietäisyyttäsi 10 jaardilla. Pyydät jokaista seuraavaa 81 seuran suojelijaa ottamaan koelaukauksen uudella mailalla ja kirjaamaan heidän swing-matkansa. Kävi ilmi, että keskimääräinen etäisyys uuteen mailaan oli 215 jaardia. Millä todennäköisyydellä 10 jaardin (215 – 205) ero johtuu pelkästään näytteenottovirheestä? Tai toisin sanoen: Mikä on todennäköisyys, että laajemmissa testeissä uusi maila ei osoita iskuetäisyyden kasvua nykyisen pitkän aikavälin 205 jaardin keskiarvon yli?

Voimme tarkistaa tämän luomalla z-pisteen. Keskiarvon standardivirhe:

Sitten z-pisteet:

Meidän on löydettävä todennäköisyys, että otoskeskiarvo on 2,5σ:n päässä väestön keskiarvosta. Jos todennäköisyys on pieni, erot eivät johdu sattumasta, vaan uuden seuran laadusta. Excelissä ei ole valmiita funktioita z-pisteiden todennäköisyyden määrittämiseen. Voit kuitenkin käyttää kaavaa =1-NORM.JAKAUMA(z-pisteet,TOSI), jossa NORM.ST.JAKAUMA()-funktio palauttaa normaalikäyrän alla olevan alueen z-pisteen vasemmalla puolella (kuva 2).

Riisi. 2. NORM.ST.JAKAUMA()-funktio palauttaa käyrän alla olevan alueen z-arvon vasemmalla puolella; Voit suurentaa kuvaa napsauttamalla sitä hiiren kakkospainikkeella ja valitsemalla Avaa kuva uudessa välilehdessä

NORM.ST.DIST()-funktion toisella argumentilla voi olla kaksi arvoa: TOSI – funktio palauttaa käyrän alla olevan alueen ensimmäisen argumentin määrittämän pisteen vasemmalla puolella; FALSE – funktio palauttaa käyrän korkeuden ensimmäisen argumentin määrittämässä pisteessä.

Jos perusjoukon keskiarvoa (μ) ja keskihajontaa (σ) ei tunneta, käytetään t-arvoa (katso yksityiskohdat). Z-score- ja t-score-rakenteet eroavat toisistaan ​​siinä, että otostuloksista saatua keskihajontaa s käytetään t-pisteen löytämiseen populaatioparametrin σ tunnetun arvon sijaan. Normaalikäyrällä on yksi muoto, ja t-arvojakauman muoto vaihtelee riippuen vapausasteiden lukumäärästä df. vapausasteita) sen edustamasta näytteestä. Näytteen vapausasteiden lukumäärä on yhtä suuri kuin n-1, Missä n- näytekoko (kuva 3).

Riisi. 3. Tapauksissa, joissa parametria σ ei tunneta esiintyvien t-jakaumien muoto poikkeaa normaalijakauman muodosta

Excelissä on kaksi funktiota t-jakaumaa varten, joita kutsutaan myös Student-jakaumaksi: STUDENT.JAKAUMA() palauttaa käyrän alla olevan alueen tietyn t-arvon vasemmalla puolella ja STUDENT.JAKAUMA.PH() palauttaa alueen oikein.

Luku 2. Korrelaatio

Korrelaatio on järjestettyjen parien joukon elementtien välisen riippuvuuden mitta. Korrelaatio on karakterisoitu Pearsonin korrelaatiokertoimet–r. Kertoimen arvot voivat olla -1,0 - +1,0.

Jossa S x Ja S y– muuttujien keskihajonnat X Ja Y, S xy– kovarianssi:

Tässä kaavassa kovarianssi jaetaan muuttujien keskihajonnalla X Ja Y, mikä poistaa yksikkökohtaiset skaalausvaikutukset kovarianssista. Excel käyttää CORREL()-funktiota. Tämän funktion nimi ei sisällä määrittäviä elementtejä Г ja В, joita käytetään funktioiden, kuten STANDARDEV(), VARIANCE() tai COVARIANCE() nimissä. Vaikka otoksen korrelaatiokerroin antaa harhaanjohtavan arvion, syy harhaan on erilainen kuin varianssin tai keskihajonnan tapauksessa.

Riippuen yleisen korrelaatiokertoimen suuruudesta (merkitty usein kreikkalaisella kirjaimella ρ ), korrelaatiokerroin r tuottaa puolueellisen arvion, jossa harhan vaikutus kasvaa otoskoon pienentyessä. Emme kuitenkaan yritä korjata tätä harhaa samalla tavalla kuin teimme esimerkiksi keskihajonnan laskennassa, kun vastaavaan kaavaan ei korvattu havaintojen lukumäärää, vaan vapausasteiden lukumäärä. Todellisuudessa kovarianssin laskemiseen käytettyjen havaintojen määrällä ei ole vaikutusta suuruuteen.

Standardikorrelaatiokerroin on tarkoitettu käytettäväksi muuttujien kanssa, jotka liittyvät toisiinsa lineaarisella suhteella. Epälineaarisuuden ja/tai virheiden esiintyminen tiedoissa (outliers) johtaa korrelaatiokertoimen virheelliseen laskemiseen. Dataongelmien diagnosoimiseksi on suositeltavaa luoda sirontakaavioita. Tämä on ainoa kaaviotyyppi Excelissä, joka käsittelee sekä vaaka- että pystyakselia arvoakseleina. Viivakaavio määrittelee yhden sarakkeista kategoria-akseliksi, mikä vääristää datan kuvaa (kuva 4).

Riisi. 4. Regressiosuorat näyttävät samalta, mutta vertaavat niiden yhtälöitä keskenään

Viivakaavion muodostamisessa käytetyt havainnot on järjestetty tasaetäisyydelle vaaka-akselia pitkin. Tämän akselin jakotunnisteet ovat vain nimikkeitä, eivät numeerisia arvoja.

Vaikka korrelaatio tarkoittaa usein syy-seuraus-suhdetta, sitä ei voida käyttää todistamaan, että näin on. Tilastoja ei käytetä osoittamaan, onko teoria totta vai tarua. Jos haluat sulkea pois kilpailevat selitykset havaintotuloksista, laita suunnitelluista kokeiluista. Tilastoja käytetään tiivistämään tällaisten kokeiden aikana kerätyt tiedot ja kvantifioimaan todennäköisyys, että tehty päätös saattaa olla virheellinen käytettävissä olevan todisteen perusteella.

Luku 3: Yksinkertainen regressio

Jos kaksi muuttujaa liittyvät toisiinsa niin, että korrelaatiokertoimen arvo ylittää esimerkiksi 0,5, niin tässä tapauksessa voidaan ennustaa (jollain tarkkuudella) toisen muuttujan tuntematon arvo toisen tunnetusta arvosta. . Saadaksesi ennustetut hinta-arvot kuvassa esitettyjen tietojen perusteella. 5, voit käyttää mitä tahansa useista mahdollisista menetelmistä, mutta et melkein varmasti käytä kuvassa 1 esitettyä menetelmää. 5. Kannattaa kuitenkin tutustua siihen, sillä mikään muu menetelmä ei anna korrelaation ja ennustamisen välistä yhteyttä niin selkeästi esille kuin tämä. Kuvassa Kuva 5 alueella B2:C12 näyttää satunnaisen otoksen kymmenestä talosta ja sisältää tiedot kunkin talon pinta-alasta (neliöjaloissa) ja sen myyntihinnasta.

Riisi. 5. Ennustetut myyntihinnat muodostavat suoran viivan

Etsi keskiarvot, keskihajonnat ja korrelaatiokerroin (alue A14:C18). Laske alueen z-pisteet (E2:E12). Esimerkiksi solu E3 sisältää kaavan: =(B3-$B$14)/$B$15. Laske ennustehinnan z-pisteet (F2:F12). Esimerkiksi solu F3 sisältää kaavan: =ЕЗ*$В$18. Muunna z-pisteet dollarihinnoiksi (H2:H12). Solussa NZ kaava on: =F3*$C$15+$C$14.

Huomaa, että ennustetulla arvolla on aina taipumus siirtyä kohti nollan keskiarvoa. Mitä lähempänä nollaa korrelaatiokerroin on, sitä lähempänä nollaa ennustettu z-piste on. Esimerkissämme pinta-alan ja myyntihinnan välinen korrelaatiokerroin on 0,67 ja ennustehinta on 1,0 * 0,67, ts. 0,67. Tämä vastaa keskiarvon yläpuolella olevan arvon ylitystä, joka on yhtä suuri kuin kaksi kolmasosaa keskihajonnasta. Jos korrelaatiokerroin olisi 0,5, niin ennustehinta olisi 1,0 * 0,5, ts. 0.5. Tämä vastaa keskiarvon yläpuolella olevan arvon ylitystä, joka on vain puoli standardipoikkeamaa. Aina kun korrelaatiokertoimen arvo poikkeaa ihanteellisesta arvosta, ts. suurempi kuin -1,0 ja pienempi kuin 1,0, ennustetun muuttujan pistemäärän tulisi olla lähempänä sen keskiarvoa kuin ennustavan (riippumattoman) muuttujan pistemäärän omaansa. Tätä ilmiötä kutsutaan regressioksi keskiarvoon tai yksinkertaisesti regressioksi.

Excelissä on useita toimintoja regressioviivayhtälön kertoimien määrittämiseen (Excelissä sitä kutsutaan trendiviivaksi) y =kx + b. Määrittämään k palvelee toimintoa

=SLOPE(tunnetut_y_arvot, tunnetut_x_arvot)

Tässä klo on ennustettu muuttuja, ja X– riippumaton muuttuja. Sinun on noudatettava tarkasti tätä muuttujien järjestystä. Regressioviivan kaltevuus, korrelaatiokerroin, muuttujien keskihajonnat ja kovarianssi liittyvät läheisesti toisiinsa (kuva 6). INTERMEPT()-funktio palauttaa arvon, jonka regressioviiva katkaisee pystyakselilla:

=RAJA(tunnetut_y_arvot, tunnetut_x_arvot)

Riisi. 6. Keskihajonnan välinen suhde muuntaa kovarianssin korrelaatiokertoimeksi ja regressioviivan kulmakertoimeksi

Huomaa, että SLOPE()- ja INTERCEPT()-funktioiden argumentteina annettujen x- ja y-arvojen määrän on oltava sama.

Regressioanalyysissä käytetään toista tärkeää indikaattoria - R 2 (R-neliö) tai determinaatiokerrointa. Se määrittää, mikä osuus tietojen yleiseen vaihteluun on suhteella X Ja klo. Excelissä sille on funktio nimeltä CVPIERSON(), joka ottaa täsmälleen samat argumentit kuin CORREL()-funktio.

Kahden muuttujan, joiden välillä on nollasta poikkeava korrelaatiokerroin, sanotaan selittävän varianssia tai niillä on selitetty varianssi. Tyypillisesti selitetty varianssi ilmaistaan ​​prosentteina. Niin R 2 = 0,81 tarkoittaa, että 81 % kahden muuttujan varianssista (hajonnasta) on selitetty. Loput 19 % johtuu satunnaisista vaihteluista.

Excelissä on TREND-toiminto, joka helpottaa laskelmia. TREND()-funktio:

• hyväksyy antamasi tunnetut arvot X ja tunnetut arvot klo;
• laskee regressioviivan kulmakertoimen ja vakion (leikkauspisteen);
• palauttaa ennustetut arvot klo, joka määritetään soveltamalla regressioyhtälöä tunnettuihin arvoihin X(Kuva 7).

TREND()-funktio on taulukkofunktio (jos et ole aiemmin törmännyt tällaisiin funktioihin, suosittelen).

Riisi. 7. TREND()-funktion avulla voit nopeuttaa ja yksinkertaistaa laskelmia verrattuna SLOPE()- ja INTERCEPT()-funktioiden käyttöön.

Jos haluat syöttää TREND()-funktion taulukkokaavana soluihin G3:G12, valitse alue G3:G12, kirjoita kaava TREND(NW:C12;B3:B12), pidä näppäimiä painettuna. ja vasta sen jälkeen paina näppäintä . Huomaa, että kaava on suljettu aaltosulkeisiin: ( ja ). Näin Excel kertoo, että tämä kaava nähdään taulukkokaavana. Älä kirjoita sulkuja itse: Jos yrität kirjoittaa ne itse osana kaavaa, Excel käsittelee syöttämääsi tavallisena tekstimerkkijonona.

TREND()-funktiolla on kaksi muuta argumenttia: uudet_arvot_x Ja konst. Ensimmäinen antaa sinun tehdä ennusteen tulevaisuutta varten ja toinen voi pakottaa regressioviivan kulkemaan origon läpi (arvo TOSI kertoo Excelin käyttämään laskettua vakiota, arvo FALSE kertoo Excelin käyttämään vakiota = 0 ). Excelin avulla voit piirtää kaavioon regressioviivan siten, että se kulkee origon läpi. Aloita piirtämällä sirontadiagrammi ja napsauta sitten hiiren kakkospainikkeella jotakin datasarjan merkintää. Valitse kohde avautuvasta pikavalikosta Lisää trendiviiva; valitse vaihtoehto Lineaarinen; vieritä tarvittaessa paneelia alaspäin, valitse valintaruutu Aseta risteys; Varmista, että siihen liittyvä tekstiruutu on asetettu arvoon 0.0.

Jos sinulla on kolme muuttujaa ja haluat määrittää kahden niistä korrelaation samalla kun eliminoit kolmannen vaikutuksen, voit käyttää osittainen korrelaatio. Oletetaan, että olet kiinnostunut yliopiston suorittaneiden kaupungin asukkaiden prosentuaalisen osuuden ja kaupungin kirjastoissa olevien kirjojen määrän välisestä suhteesta. Keräsit tietoja 50 kaupungista, mutta... Ongelmana on, että molemmat parametrit voivat riippua tietyn kaupungin asukkaiden hyvinvoinnista. Tietenkin on erittäin vaikea löytää muita 50 kaupunkia, joille on ominaista täsmälleen sama asukkaiden hyvinvointi.

Käyttämällä tilastollisia menetelmiä valvomaan varallisuuden vaikutusta sekä kirjaston taloudelliseen tukeen että korkeakoulujen kohtuuhintaisuuteen, voit saada tarkemman kvantifioinnin sinua kiinnostavien muuttujien välisen suhteen vahvuudesta, nimittäin kirjojen ja kirjojen lukumäärän. valmistuneet. Tällaista ehdollista korrelaatiota kahden muuttujan välillä, kun muiden muuttujien arvot ovat kiinteät, kutsutaan osittaiseksi korrelaatioksi. Yksi tapa laskea se on käyttää yhtälöä:

Jossa rC.B. . W- College- ja Books-muuttujien välinen korrelaatiokerroin ilman varallisuusmuuttujan vaikutusta (kiinteä arvo); rC.B.- muuttujien College ja Books välinen korrelaatiokerroin; rCW- College- ja Welfare-muuttujien välinen korrelaatiokerroin; rB.W.- Korrelaatiokerroin muuttujien Kirjat ja Hyvinvointi välillä.

Toisaalta osakorrelaatio voidaan laskea jäännösanalyysin perusteella, ts. ennustettujen arvojen ja niihin liittyvien todellisten havaintojen tulosten väliset erot (molemmat menetelmät on esitetty kuvassa 8).

Riisi. 8. Osittainen korrelaatio jäännösten korrelaationa

Korrelaatiokertoimien matriisin (B16:E19) laskemisen yksinkertaistamiseksi käytä Excel-analyysipakettia (valikko Data –> Analyysi –> Tietojen analyysi). Oletuksena tämä paketti ei ole aktiivinen Excelissä. Asenna se siirtymällä valikon läpi Tiedosto –> Vaihtoehdot –> Lisäosat. Avatun ikkunan alareunassa VaihtoehdotExcel löytää kenttä Ohjaus, valitse LisäosatExcel, napsauta Mennä. Valitse lisäosan vieressä oleva valintaruutu Analyysipaketti. Napsauta A tietojen analysointi, valitse vaihtoehto Korrelaatio. Määritä $B$2:$D$13 syöttöväliksi, valitse ruutu Etiketit ensimmäisellä rivillä, määritä tulosteväliksi $B$16:$E$19.

Toinen mahdollisuus on määrittää puoliosittainen korrelaatio. Tutkit esimerkiksi pituuden ja iän vaikutuksia painoon. Näin ollen sinulla on kaksi ennustemuuttujaa - pituus ja ikä ja yksi ennustemuuttuja - paino. Haluat sulkea pois yhden ennustajamuuttujan vaikutuksen toiseen, mutta et ennustajamuuttujaan:

missä H – pituus, W – paino, A – ikä; Puoliokäyttää sulkeita osoittamaan, mikä muuttuja poistetaan ja mistä muuttujasta. Tässä tapauksessa merkintä W(H.A) osoittaa, että Ikä-muuttujan vaikutus poistetaan Pituus-muuttujasta, mutta ei Paino-muuttujasta.

Saattaa vaikuttaa siltä, ​​että keskusteltavalla ei ole merkittävää merkitystä. Loppujen lopuksi tärkeintä on se, kuinka tarkasti kokonaisregressioyhtälö toimii, kun taas ongelma yksittäisten muuttujien suhteellisista osuuksista selitettyyn kokonaisvarianssiin näyttää olevan toissijainen. Tämä ei kuitenkaan ole kaukana siitä. Kun alat miettiä, kannattaako muuttujaa ylipäätään käyttää moninkertaisessa regressioyhtälössä, ongelmasta tulee tärkeä. Se voi vaikuttaa analyysimallin valinnan oikeellisuuden arviointiin.

Luku 4. LINEST()-funktio

LINEST()-funktio palauttaa 10 regressiotilastoa. LINEST()-funktio on taulukkofunktio. Syötä se valitsemalla alue, jossa on viisi riviä ja kaksi saraketta, kirjoittamalla kaava ja napsauttamalla (Kuva 9):

RIVI(B2:B21,A2:A21,TOSI,TOSI)

Riisi. 9. LINEST()-funktio: a) valitse alue D2:E6, b) syötä kaava kaavapalkissa näkyvällä tavalla, c) napsauta

LINEST()-funktio palauttaa:

• regressiokerroin (tai kulmakerroin, solu D2);
• segmentti (tai vakio, solu E3);
• regressiokertoimen ja vakion standardivirheet (alue D3:E3);
• määrityskerroin R2 regressiolle (solu D4);
• estimaatin standardivirhe (solu E4);
• F-testi täydelliselle regressiolle (solu D5);
• jäännösneliösumman vapausasteiden lukumäärä (solu E5);
• neliöiden regressiosumma (solu D6);
• neliöiden jäännössumma (solu E6).

Katsotaanpa kutakin näistä tilastoista ja niiden vuorovaikutusta.

Normaali virhe meidän tapauksessamme se on näytteenottovirheille laskettu keskihajonta. Eli tämä on tilanne, jossa yleisellä populaatiolla on yksi tilasto ja otoksella toinen. Kun regressiokerroin jaetaan keskivirheellä, saadaan arvo 2,092/0,818 = 2,559. Toisin sanoen regressiokerroin 2,092 on kahden ja puolen standardivirheen päässä nollasta.

Jos regressiokerroin on nolla, niin ennustetun muuttujan paras estimaatti on sen keskiarvo. Kaksi ja puoli standardivirhettä on melko suuri, ja voit turvallisesti olettaa, että perusjoukon regressiokerroin on nollasta poikkeava.

Voit määrittää todennäköisyyden saada otoksen regressiokerroin 2,092, jos sen todellinen arvo populaatiossa on 0,0 funktiolla

STUDENT.DIST.PH (t-kriteeri = 2,559; vapausasteiden lukumäärä = 18)

Yleisesti ottaen vapausasteiden lukumäärä = n – k – 1, missä n on havaintojen määrä ja k on ennustajamuuttujien lukumäärä.

Tämä kaava palauttaa arvon 0,00987 tai pyöristettynä 1 prosenttiin. Se kertoo meille, että jos väestön regressiokerroin on 0%, niin todennäköisyys saada 20 ihmisen otos, jonka arvioitu regressiokerroin on 2,092, on vaatimaton 1%.

F-testi (solu D5 kuviossa 9) suorittaa samat toiminnot suhteessa täydelliseen regressioon kuin t-testi yksinkertaisen parittaisen regression kertoimen suhteen. F-testillä testataan, onko regression determinaatiokerroin R 2 riittävän suuri hylätäkseen hypoteesin, jonka mukaan populaatiossa sen arvo on 0,0, mikä osoittaa, ettei ennustajan ja ennustetun muuttujan selittämää varianssia ole. Kun ennustajamuuttujia on vain yksi, F-testi on täsmälleen yhtä suuri kuin t-testi neliöitynä.

Toistaiseksi olemme tarkastelleet intervallimuuttujia. Jos sinulla on muuttujia, jotka voivat ottaa useita arvoja ja jotka edustavat yksinkertaisia ​​nimiä, esimerkiksi Mies ja nainen tai Matelija, Sammakkoeläin ja Kala, esitä ne numerokoodina. Tällaisia ​​muuttujia kutsutaan nimellisiksi.

R2 tilastot kvantifioi selitetyn varianssin osuuden.

Arvioinnin standardivirhe. Kuvassa Kuvassa 4.9 on esitetty Paino-muuttujan ennustetut arvot, jotka on saatu sen suhteen perusteella Korkeusmuuttujaan. Alue E2:E21 sisältää Paino-muuttujan jäännösarvot. Tarkemmin sanottuna näitä jäännöksiä kutsutaan virheiksi - tästä johtuu termi arvioinnin standardivirhe.

Riisi. 10. Sekä R 2 että estimaatin keskivirhe ilmaisevat regressiolla saatujen ennusteiden tarkkuuden

Mitä pienempi estimaatin keskivirhe on, sitä tarkempi on regressioyhtälö ja sitä paremmin odotat yhtälön tuottaman ennusteen vastaavan todellista havaintoa. Arvioinnin keskivirhe tarjoaa tavan kvantifioida nämä odotukset. Tietyn pituisten ihmisten paino on 95 prosenttia:

(korkeus * 2,092 – 3,591) ± 2,092 * 21,118

F-tilasto on ryhmien välisen varianssin suhde ryhmän sisäiseen varianssiin. Tämän nimen otti käyttöön tilastotieteilijä George Snedecor sirin kunniaksi, joka kehitti varianssianalyysin (ANOVA, Analysis of Variance) 1900-luvun alussa.

Determinaatiokerroin R 2 ilmaisee osuuden regressioon liittyvien neliöiden kokonaissummasta. Arvo (1 – R 2) ilmaisee jäännösten eli ennustevirheiden osuuden neliöiden kokonaissummasta. F-testi voidaan saada käyttämällä LINEST-funktiota (solu F5 kuvassa 11), käyttämällä neliösummaa (alue G10:J11), käyttämällä varianssisuhteita (alue G14:J15). Kaavoja voi tutkia liitteenä olevasta Excel-tiedostosta.

Riisi. 11. F-kriteerin laskeminen

Nimellismuuttujia käytettäessä käytetään dummy-koodausta (kuva 12). Arvojen koodaamiseen on kätevää käyttää arvoja 0 ja 1. Todennäköisyys F lasketaan funktiolla:

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

Tässä funktio F.JAKAUMA.PH() palauttaa todennäköisyyden saada F-kriteeri, joka noudattaa keskeistä F-jakaumaa (kuva 13) kahdelle datajoukolle soluissa I2 ja I3 annettujen vapausasteiden lukumäärällä. , jonka arvo on sama kuin solussa K2 annettu arvo.

Riisi. 12. Regressioanalyysi valemuuttujien avulla

Riisi. 13. Keski-F-jakauma kohdassa λ = 0

Luku 5. Multiple Regression

Kun siirryt yksinkertaisesta parittaisesta regressiosta, jossa on yksi ennustajamuuttuja, useaan regressioon, lisäät yhden tai useampia ennustajamuuttujia. Tallenna ennustajamuuttujien arvot vierekkäisiin sarakkeisiin, kuten sarakkeisiin A ja B, jos kyseessä on kaksi ennustajaa, tai A, B ja C, jos kyseessä on kolme ennustajaa. Ennen kuin syötät kaavan, joka sisältää LINEST()-funktion, valitse viisi riviä ja niin monta saraketta kuin on ennustajamuuttujia sekä yksi lisää vakiolle. Kun kyseessä on regressio kahdella ennustajamuuttujalla, voidaan käyttää seuraavaa rakennetta:

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

Samoin kolmen muuttujan tapauksessa:

LINEST(A2:A61,B2:D61,;TOSI)

Oletetaan, että haluat tutkia iän ja ruokavalion mahdollisia vaikutuksia LDL-tasoihin – matalatiheyksisiin lipoproteiineihin, joiden uskotaan olevan vastuussa ateroskleroottisten plakkien muodostumisesta, jotka aiheuttavat aterotromboosia (kuva 14).

Riisi. 14. Moninkertainen regressio

Moninkertaisen regression R2 (heijastettu solussa F13) on suurempi kuin minkä tahansa yksinkertaisen regression R2 (E4, H4). Moninkertainen regressio käyttää useita ennustajamuuttujia samanaikaisesti. Tässä tapauksessa R2 kasvaa lähes aina.

Jokaisessa yksinkertaisessa lineaarisessa regressioyhtälössä, jossa on yksi ennustajamuuttuja, ennustettujen arvojen ja ennustajamuuttujan arvojen välillä on aina täydellinen korrelaatio, koska yhtälö kertoo ennustaja-arvot yhdellä vakiolla ja lisää toisen vakion jokainen tuote. Tämä vaikutus ei säily moninkertaisessa regressiossa.

LINEST()-funktion moninkertaiselle regressiolle palauttamien tulosten näyttäminen (kuva 15). Regressiokertoimet tulostetaan osana LINEST()-funktion palauttamia tuloksia muuttujien käänteisessä järjestyksessä(G–H–I vastaa C–B–A).

Riisi. 15. Kertoimet ja niiden keskivirheet näkyvät laskentataulukolla käänteisessä järjestyksessä.

Yhden ennustajamuuttujan regressioanalyysissä käytetyt periaatteet ja menettelyt ovat helposti mukautettavissa useiden ennustajamuuttujien huomioon ottamiseksi. Osoittautuu, että suuri osa tästä mukauttamisesta riippuu ennakoivien muuttujien vaikutuksen eliminoimisesta toisiinsa. Jälkimmäinen liittyy osittaisiin ja puoliosittaisiin korrelaatioihin (kuva 16).

Riisi. 16. Moninkertainen regressio voidaan ilmaista residuaalien pariregressiolla (katso kaavat Excel-tiedostosta)

Excelissä on funktioita, jotka antavat tietoa t- ja F-jakaumista. Funktiot, joiden nimet sisältävät EKA-osan, kuten OPPILAS.JAKAUMA() ja F.JAKAUMA(), ottavat argumenttina t-testin tai F-testin ja palauttavat todennäköisyyden tietyn arvon havaitsemiseen. Funktiot, joiden nimet sisältävät OBR-osan, kuten STUDENT.INV() ja F.INR(), ottavat argumentiksi todennäköisyysarvon ja palauttavat määritettyä todennäköisyyttä vastaavan kriteerin arvon.

Koska etsimme t-jakauman kriittisiä arvoja, jotka leikkaavat sen pyrstöalueiden reunat, välitämme 5 % argumentiksi yhdelle STUDENT.INV()-funktioista, joka palauttaa tätä todennäköisyyttä vastaavan arvon. (Kuvat 17, 18).

Riisi. 17. Kaksisuuntainen t-testi

Riisi. 18. Yksisuuntainen t-testi

Luomalla päätössäännön yksisuuntaiselle alfa-alueelle lisäät testin tilastollista tehoa. Jos lähdet kokeiluun ja olet varma, että sinulla on kaikki syyt odottaa positiivista (tai negatiivista) regressiokerrointa, sinun tulee suorittaa yhden pyrstön testi. Tässä tapauksessa todennäköisyys, että teet oikean päätöksen hylkääessäsi hypoteesin nollaregressiokertoimesta populaatiossa, on suurempi.

Tilastomiehet käyttävät mieluummin termiä suunnattu testi termin sijaan yhden hännän testi ja termi ohjaamaton testi termin sijaan kaksipyrstötesti. Termit suunnattu ja suuntaamaton ovat suositeltavia, koska ne korostavat hypoteesin tyyppiä eikä jakauman hännän luonnetta.

Mallien vertailuun perustuva lähestymistapa ennustajien vaikutuksen arvioimiseen. Kuvassa Kuva 19 esittää tulokset regressioanalyysistä, joka testaa ruokavaliomuuttujan osuutta regressioyhtälössä.

Riisi. 19. Vertaa kahta mallia testaamalla eroja niiden tuloksissa

LINEST()-funktion tulokset (alue H2:K6) liittyvät siihen, mitä kutsun täydelliseksi malliksi, joka regressoi LDL-muuttujan ruokavalio-, ikä- ja HDL-muuttujien kohdalla. Alue H9:J13 esittää laskelmia ottamatta huomioon ennustajamuuttujaa Ruokavalio. Kutsun tätä rajoitetuksi malliksi. Täysmallissa 49,2 % riippuvan muuttujan LDL:n varianssista selittyi ennustemuuttujilla. Rajoitetussa mallissa vain 30,8 % LDL:stä selittyy ikä- ja HDL-muuttujilla. R 2:n menetys, joka johtuu Diet-muuttujan jättämisestä pois mallista, on 0,183. Alueella G15:L17 tehdään laskelmia, jotka osoittavat, että on vain todennäköisyys 0,0288, että Ruokavalio-muuttujan vaikutus on satunnainen. Lopussa 97,1 %:lla ruokavaliolla on vaikutusta LDL:ään.

Luku 6: Regressioanalyysin oletukset ja varoitukset

Termiä "oletus" ei ole määritelty riittävän tarkasti, ja sen käyttötapa viittaa siihen, että jos olettamus ei täyty, koko analyysin tulokset ovat vähintäänkin kyseenalaisia ​​tai mahdollisesti virheellisiä. Näin ei todellisuudessa ole, vaikka varmasti on tapauksia, joissa olettamuksen rikkominen muuttaa kuvaa perusteellisesti. Perusoletukset: a) Y-muuttujan residuaalit ovat normaalisti jakautuneet mihin tahansa pisteeseen X regressioviivaa pitkin; b) Y-arvot ovat lineaarisesti riippuvaisia ​​X-arvoista; c) jäännösten dispersio on suunnilleen sama kussakin pisteessä X; d) jäämien välillä ei ole riippuvuutta.

Jos olettamuksilla ei ole merkittävää roolia, tilastotieteilijät sanovat, että analyysi on luotettava oletuksen rikkomiseen. Erityisesti, kun käytät regressiota ryhmien keskiarvojen välisten erojen testaamiseen, oletus, että Y-arvot - ja siten jäännökset - ovat normaalijakautuneita, ei näytä merkittävää roolia: testit ovat vankkoja normaalisuusoletuksen rikkomisen suhteen. On tärkeää analysoida tietoja kaavioiden avulla. Esimerkiksi sisällytetty lisäosaan Tietojen analyysi työkalu Regressio.

Jos tiedot eivät täytä lineaarisen regression oletuksia, käytettävissäsi on muita lähestymistapoja kuin lineaarinen regressio. Yksi niistä on logistinen regressio (kuva 20). Lähellä ennustajamuuttujan ylä- ja alarajoja lineaarinen regressio tuottaa epärealistisia ennusteita.

Riisi. 20. Logistinen regressio

Kuvassa Kuvassa 6.8 on esitetty tulokset kahdesta data-analyysimenetelmästä, joiden tarkoituksena on tutkia vuositulon ja asunnon ostotodennäköisyyden suhdetta. On selvää, että ostosten tekemisen todennäköisyys kasvaa tulojen kasvaessa. Kaavioista on helppo havaita erot tulosten välillä, että lineaarinen regressio ennustaa asunnon ostamisen todennäköisyyden, ja tulosten välillä, joita saatat saada käyttämällä erilaista lähestymistapaa.

Tilastotieteen kielessä nollahypoteesin hylkäämistä, kun se on todella totta, kutsutaan tyypin I virheeksi.

Lisäosassa Tietojen analyysi tarjoaa kätevän työkalun satunnaislukujen luomiseen, jolloin käyttäjä voi määrittää jakauman halutun muodon (esimerkiksi normaali, binomiaalinen tai Poisson) sekä keskiarvon ja keskihajonnan.

Erot STUDENT.DIST()-perheen funktioiden välillä. Excel 2010:stä alkaen käytettävissä on kolme erilaista funktion muotoa, jotka palauttavat jakauman osuuden annetun t-testin arvon vasemmalle ja/tai oikealle puolelle. Funktio STUDENT.DIST() palauttaa jakaumakäyrän alla olevan alueen murto-osan määrittämäsi t-testiarvon vasemmalla puolella. Oletetaan, että sinulla on 36 havaintoa, joten analyysin vapausasteiden lukumäärä on 34 ja t-testin arvo = 1,69. Tässä tapauksessa kaava

STUDENT.DIST(+1.69;34,TRUE)

palauttaa arvon 0,05 eli 5 % (Kuva 21). Funktion STUDENT.DIST() kolmas argumentti voi olla TOSI tai EPÄTOSI. Jos arvoksi on asetettu TOSI, funktio palauttaa käyrän alla olevan kumulatiivisen alueen määritetyn t-testin vasemmalla puolella ilmaistuna suhteessa. Jos se on EPÄTOSI, funktio palauttaa käyrän suhteellisen korkeuden t-testiä vastaavassa pisteessä. Muut funktion STUDENT.JAKAUMA() versiot - OPPILAS.JAKAUMA.PH() ja OPPILAS.JAKAUMA.2X() - ottavat argumentteina vain t-testin arvon ja vapausasteiden lukumäärän eivätkä vaadi kolmannen määrittämistä. argumentti.

Riisi. 21. Jakauman vasemman hännän tummempi varjostettu alue vastaa suuren positiivisen t-testin arvon vasemmalla puolella olevan käyrän alla olevan alueen osuutta

Määritä t-testin oikealla puolella oleva alue käyttämällä yhtä kaavoista:

1 — STIODENT.JAKAUMA (1, 69;34; TOSI)

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

Koko käyrän alla olevan alueen tulee olla 100 %, joten funktion palauttaman t-testin arvon vasemmalla puolella olevan alueen murto-osuuden vähentäminen 1:stä antaa t-testin arvon oikealla puolella olevan alueen murto-osan. Saatat pitää parempana hankkia sinua kiinnostava pinta-alamurto suoraan funktiolla STUDENT.DIST.PH(), jossa PH tarkoittaa jakauman oikeaa häntää (kuva 22).

Riisi. 22. 5 % alfa-alue suuntatestiä varten

Funktioiden STUDENT.JAKAUMA() tai OPPILAS.JAKAUMA.PH() käyttö tarkoittaa, että olet valinnut suuntatyöhypoteesin. Suuntatyöhypoteesi yhdistettynä alfa-arvon asettamiseen 5 %:iin tarkoittaa, että sijoitat kaikki 5 % jakaumien oikeaan päähän. Sinun on hylättävä nollahypoteesi vain, jos saadun t-testin arvon todennäköisyys on 5 % tai vähemmän. Suuntahypoteesit johtavat yleensä herkempiin tilastollisiin testeihin (tätä suurempaa herkkyyttä kutsutaan myös suuremmaksi tilastolliseksi tehoksi).

Ohjaamattomassa testissä alfa-arvo pysyy samalla 5 %:n tasolla, mutta jakauma on erilainen. Koska sinun on sallittava kaksi tulosta, väärän positiivisen tuloksen todennäköisyys on jaettava jakauman kahden hännän kesken. On yleisesti hyväksyttyä jakaa tämä todennäköisyys tasaisesti (kuva 23).

Käytä samaa saatua t-testin arvoa ja samaa vapausasteiden määrää kuin edellisessä esimerkissä, käytä kaavaa

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

Ilman erityistä syytä STUDENT.DIST.2X()-funktio palauttaa virhekoodin #NUM, jos sille annetaan negatiivinen t-testiarvo sen ensimmäisenä argumenttina.

Jos näytteet sisältävät eri määriä dataa, käytä paketissa olevaa kahden otoksen t-testiä eri varianssien kanssa Tietojen analyysi.

Luku 7: Regression käyttö ryhmän keskiarvojen välisten erojen testaamiseen

Muuttujia, jotka esiintyivät aiemmin nimellä ennustajamuuttujat, kutsutaan tässä luvussa tulosmuuttujiksi ja termiä tekijämuuttujat käytetään termin ennustajamuuttujat sijasta.

Yksinkertaisin tapa koodata nimellinen muuttuja on dummy koodaus(Kuva 24).

Riisi. 24. Dummy-koodaukseen perustuva regressioanalyysi

Kun käytetään mitä tahansa valekoodausta, on noudatettava seuraavia sääntöjä:

• Uusille tiedoille varattujen sarakkeiden lukumäärän on oltava yhtä suuri kuin tekijätasojen lukumäärä miinus
• Jokainen vektori edustaa yhtä tekijätasoa.
• Yhdellä tasolla olevat kohteet, joka on usein kontrolliryhmä, on koodattu 0:lla kaikissa vektoreissa.

Solujen F2:H6 =LINEST(A2:A22,C2:D22,;TOSI) kaava palauttaa regressiotilastot. Vertailun vuoksi kuvassa. Kuva 24 esittää työkalun palauttaman perinteisen ANOVA:n tulokset. Yksisuuntainen ANOVA lisäosat Tietojen analyysi.

Tehosteiden koodaus. Toisessa koodaustyypissä kutsutaan efektien koodaus, kunkin ryhmän keskiarvoa verrataan ryhmän keskiarvoon. Tämä vaikutuskoodauksen näkökohta johtuu -1:n käytöstä 0:n sijaan koodina ryhmälle, joka vastaanottaa saman koodin kaikissa koodivektoreissa (kuva 25).

Riisi. 25. Tehosteiden koodaus

Käytettäessä valekoodausta LINEST():n palauttama vakioarvo on sen ryhmän keskiarvo, jolle on osoitettu nollakoodi kaikissa vektoreissa (yleensä kontrolliryhmä). Efektikoodauksen tapauksessa vakio on yhtä suuri kuin kokonaiskeskiarvo (solu J2).

Yleinen lineaarinen malli on hyödyllinen tapa käsitteellistää tulosmuuttujan arvon komponentit:

Y ij = μ + α j + ε ij

Kreikkalaisten kirjainten käyttö tässä kaavassa latinalaisten kirjainten sijaan korostaa sitä tosiasiaa, että se viittaa populaatioon, josta näytteet on otettu, mutta se voidaan kirjoittaa uudelleen osoittamaan, että se viittaa näytteisiin, jotka on otettu tietystä populaatiosta:

Y ij = Y̅ + a j + e ij

Ajatuksena on, että jokainen havainto Y ij voidaan nähdä kolmen seuraavan komponentin summana: suuri keskiarvo, μ; hoidon j ja j vaikutus; arvo e ij, joka edustaa yksittäisen kvantitatiivisen indikaattorin Y ij poikkeamaa yleisen keskiarvon ja j:nnen käsittelyn vaikutuksen yhteisarvosta (kuva 26). Regressioyhtälön tavoitteena on minimoida residuaalien neliösumma.

Riisi. 26. Yleisen lineaarisen mallin komponenteiksi hajotetut havainnot

Tekijäanalyysi. Jos tulosmuuttujan ja kahden tai useamman tekijän välistä suhdetta tutkitaan samanaikaisesti, niin tässä tapauksessa puhutaan tekijäanalyysin käytöstä. Yhden tai useamman tekijän lisääminen yksisuuntaiseen ANOVAan voi lisätä tilastollista tehoa. Yksisuuntaisessa varianssianalyysissä tulosmuuttujan varianssi, jota ei voida liittää tekijään, sisällytetään jäännöskeskineliöön. Mutta voi hyvinkin olla, että tämä vaihtelu liittyy toiseen tekijään. Sitten tämä vaihtelu voidaan poistaa keskineliövirheestä, jonka pieneneminen johtaa F-testin arvojen nousuun ja siten testin tilastollisen tehon kasvuun. Päällirakenne Tietojen analyysi sisältää työkalun, joka käsittelee kahta tekijää samanaikaisesti (kuva 27).

Riisi. 27. Työkalu Kaksisuuntainen varianssianalyysi analyysipaketin toistoilla

Tässä kuvassa käytetty ANOVA-työkalu on hyödyllinen, koska se palauttaa tulosmuuttujan keskiarvon ja varianssin sekä laskurin arvon jokaiselle suunnitteluun sisältyvälle ryhmälle. Taulukossa Varianssianalyysi näyttää kaksi parametria, joita ei ole ANOVA-työkalun yksitekijäversion lähdössä. Kiinnitä huomiota vaihtelun lähteisiin Näyte Ja Sarakkeet riveillä 27 ja 28. Vaihtelun lähde Sarakkeet viittaa sukupuoleen. Vaihtelun lähde Näyte viittaa mihin tahansa muuttujaan, jonka arvot ovat eri riveillä. Kuvassa 27 arvoa KursLech1-ryhmälle on riveillä 2-6, KursLech2-ryhmä on riveillä 7-11 ja KursLechZ-ryhmä riveillä 12-16.

Pääasia on, että molemmat tekijät, sukupuoli (nimi Sarakkeet solussa E28) ja hoito (otsikko Näyte solussa E27), sisältyvät ANOVA-taulukkoon vaihtelun lähteinä. Miesten keinot ovat erilaisia ​​kuin naisten keinot, ja tämä luo vaihtelua. Myös näiden kolmen hoidon keinot eroavat toisistaan, mikä tarjoaa toisen vaihtelun lähteen. On myös kolmas lähde, Vuorovaikutus, joka viittaa muuttujien sukupuoli ja hoito yhteisvaikutukseen.

Luku 8. Kovarianssianalyysi

Kovarianssianalyysi tai ANCOVA (kovariaatioanalyysi) vähentää harhaa ja lisää tilastollista tehoa. Muistutan, että yksi tapa arvioida regressioyhtälön luotettavuutta on F-testit:

F = MS-regressio/MS-jäännös

jossa MS (Mean Square) on keskineliö, ja regressio- ja jäännösindeksit osoittavat vastaavasti regression ja jäännöskomponentin. MS-jäännösarvo lasketaan kaavalla:

MS Residual = SS Residual / df Residual

missä SS (Sum of Squares) on neliöiden summa ja df on vapausasteiden lukumäärä. Kun lisäät kovarianssin regressioyhtälöön, osa neliöiden kokonaissummasta ei sisälly SS ResiduaI:hen vaan SS-regressioon. Tämä johtaa SS Residua l:n ja siten MS Residuaalin vähenemiseen. Mitä pienempi MS-jäännös on, sitä suurempi on F-testi ja sitä todennäköisemmin hylkäät nollahypoteesin, jonka mukaan keskiarvojen välillä ei ole eroa. Tämän seurauksena jaat uudelleen tulosmuuttujan vaihtelun. ANOVAssa, kun kovarianssia ei oteta huomioon, vaihtelu muuttuu virheeksi. Mutta ANCOVAssa osa aiemmin virhetermille annetusta vaihtelusta määrätään kovariaatille ja siitä tulee osa SS-regressiota.

Tarkastellaan esimerkkiä, jossa sama tietojoukko analysoidaan ensin ANOVA:lla ja sitten ANCOVA:lla (kuva 28).

Riisi. 28. ANOVA-analyysi osoittaa, että regressioyhtälöstä saadut tulokset ovat epäluotettavia

Tutkimuksessa verrataan lihasvoimaa parantavan liikunnan ja aivotoimintaa stimuloivan kognitiivisen harjoituksen (ristisanatehtävän) suhteellisia vaikutuksia. Koehenkilöt jaettiin satunnaisesti kahteen ryhmään niin, että molemmat ryhmät altistettiin samoille olosuhteille kokeen alussa. Kolmen kuukauden kuluttua koehenkilöiden kognitiivinen suorituskyky mitattiin. Näiden mittausten tulokset on esitetty sarakkeessa B.

Alue A2:C21 sisältää lähdedatan, joka on siirretty LINEST()-funktiolle analyysin suorittamiseksi tehostekoodausta käyttäen. LINEST()-funktion tulokset annetaan alueella E2:F6, jossa solu E2 näyttää vaikutusvektoriin liittyvän regressiokertoimen. Solu E8 sisältää t-testin = 0,93, ja solu E9 testaa tämän t-testin luotettavuuden. Solun E9 sisältämä arvo osoittaa, että todennäköisyys kohdata tässä kokeessa havaittu ero ryhmien keskiarvojen välillä on 36 %, jos ryhmän keskiarvot ovat samat populaatiossa. Harvat pitävät tätä tulosta tilastollisesti merkitsevänä.

Kuvassa Kuva 29 näyttää, mitä tapahtuu, kun lisäät analyysiin kovariaatin. Tässä tapauksessa lisäsin tietojoukkoon kunkin kohteen iän. Kovariaattia käyttävän regressioyhtälön määrityskerroin R 2 on 0,80 (solu F4). R2-arvo alueella F15:G19, jossa toistan ilman kovariaattia saadut ANOVA-tulokset, on vain 0,05 (solu F17). Siksi kovariaatin sisältävä regressioyhtälö ennustaa kognitiivisen pistemäärän muuttujan arvot paljon tarkemmin kuin pelkkä vaikutusvektorin käyttö. ANCOVA:n tapauksessa todennäköisyys saada solussa F5 näytettävä F-testiarvo sattumalta on alle 0,01 %.

Riisi. 29. ANCOVA tuo takaisin täysin erilaisen kuvan

Se tunnetaan hyödylliseksi useilla toiminta-aloilla, mukaan lukien ekonometria, jossa tätä apuohjelmaa käytetään työssä. Pohjimmiltaan kaikki käytännön ja laboratoriotuntien toiminnot suoritetaan Excelissä, mikä helpottaa huomattavasti työtä tarjoamalla yksityiskohtaiset selitykset tietyistä toimista. Siten yhtä analyysityökaluista "Regressio" käytetään kaavion valitsemiseen havaintojen joukolle pienimmän neliösumman menetelmällä. Katsotaanpa, mikä tämä ohjelmatyökalu on ja mitä hyötyä siitä on käyttäjille. Alta löydät myös lyhyet mutta selkeät ohjeet regressiomallin rakentamiseen.

Päätehtävät ja regression tyypit

Regressio edustaa annettujen muuttujien välistä suhdetta, mikä mahdollistaa näiden muuttujien tulevan käyttäytymisen ennustamisen. Muuttujat ovat erilaisia ​​jaksoittaisia ​​ilmiöitä, mukaan lukien ihmisen käyttäytyminen. Tämän tyyppistä Excel-analyysiä käytetään analysoimaan yhden tai useamman muuttujan arvojen vaikutusta tiettyyn riippuvaan muuttujaan. Esimerkiksi liikkeen myyntiin vaikuttavat useat tekijät, kuten valikoima, hinnat ja myymälän sijainti. Excelin regression ansiosta voit määrittää kunkin näistä tekijöistä vaikutuksen asteen olemassa olevan myynnin tulosten perusteella ja sitten käyttää saatuja tietoja arvioimaan myyntiä toiselle kuukaudelle tai toiselle lähellä sijaitsevalle myymälälle.

Tyypillisesti regressio esitetään yksinkertaisena yhtälönä, joka paljastaa suhteet ja suhteiden vahvuudet kahden muuttujaryhmän välillä, jolloin toinen ryhmä on riippuvainen tai endogeeninen ja toinen on riippumaton tai eksogeeninen. Jos on joukko toisiinsa liittyviä indikaattoreita, riippuva muuttuja Y määritetään päättelylogiikan perusteella ja loput toimivat itsenäisinä X-muuttujina.

Regressiomallin rakentamisen päätehtävät ovat seuraavat:

1. Merkittävien riippumattomien muuttujien valinta (X1, X2, ..., Xk).
2. Toiminnon tyypin valinta.
3. Arvioiden rakentaminen kertoimille.
4. Luottamusvälien ja regressiofunktioiden rakentaminen.
5. Laskettujen arvioiden ja konstruoidun regressioyhtälön merkitsevyyden tarkistaminen.

Regressioanalyysiä on useita:

• parillinen (1 riippuvainen ja 1 riippumaton muuttuja);
• useita (useita riippumattomia muuttujia).

Regressioyhtälöitä on kahden tyyppisiä:

1. Lineaarinen, kuvaa tiukkaa lineaarista suhdetta muuttujien välillä.
2. Epälineaarinen - Yhtälöt, jotka voivat sisältää potenssien, murto-osien ja trigonometristen funktioiden.

Ohjeet mallin rakentamiseen

Suorittaaksesi tietyn rakenteen Excelissä, sinun on noudatettava ohjeita:

Käytä lisälaskentaa varten "Linear()"-funktiota, joka määrittää Y-arvot, X-arvot, vakio- ja tilastotiedot. Tämän jälkeen määritä regressioviivan pistejoukko "Trendi"-funktiolla - Y-arvot, X-arvot, uudet arvot, vakio. Laske annettujen parametrien avulla kertoimien tuntematon arvo tehtävän annettujen ehtojen perusteella.