Bruk Excel til å beregne regresjonskoeffisientene til en ikke-lineær funksjon. Regresjon og excel

MS Excel-pakken lar deg gjøre det meste av arbeidet veldig raskt når du konstruerer en lineær regresjonsligning. Det er viktig å forstå hvordan de oppnådde resultatene skal tolkes.

Krever et tillegg for å fungere Analysepakke, som må være aktivert i menyelementet Tjeneste\Tillegg

I Excel 2007, for å aktivere analysepakken, må du klikke gå til blokkering Excel-alternativer ved å klikke på knappen i øvre venstre hjørne og deretter " Excel-alternativer"nederst i vinduet:

For å bygge en regresjonsmodell må du velge elementet Tjeneste\Dataanalyse\Regresjon. (I Excel 2007 er denne modusen i blokken Data/Data Analyse/Regresjon). Det vises en dialogboks som du må fylle ut:

1) Inndataintervall Y¾ inneholder en lenke til celler som inneholder verdiene til den resulterende karakteristikken y. Verdiene må ordnes i en kolonne;

2) Inndataintervall X¾ inneholder en kobling til celler som inneholder faktorverdier. Verdiene må ordnes i kolonner;

3) Signer Tagger angi om de første cellene inneholder forklarende tekst (dataetiketter);

4) Pålitelighetsnivå¾ er konfidensnivået, som anses å være 95 % som standard. Hvis du ikke er fornøyd med denne verdien, må du aktivere dette flagget og angi den nødvendige verdien;

5) Signer Konstant-null er inkludert hvis det er nødvendig å konstruere en ligning der den frie variabelen er ;

6) Utgangsalternativer bestemme hvor resultatene skal plasseres. Som standard byggemodus Nytt arbeidsark;

7) Blokker Rester lar deg inkludere produksjonen av rester og konstruksjonen av grafene deres.

Som et resultat, informasjon som inneholder alle nødvendig informasjon og gruppert i tre blokker: Regresjonsstatistikk, Analyse av varianter, Uttak av saldo. La oss se nærmere på dem.

1. Regresjonsstatistikk:

flere R bestemmes av formelen ( Pearson korrelasjonskoeffisient);

R (bestemmelseskoeffisient);

Normalisert R-kvadrat beregnes av formelen (brukes til multippel regresjon);

Standard feil S beregnet med formelen ;

Observasjoner ¾ er mengden data n.

2. Analyse av varianter, linje Regresjon:

Parameter df er lik m(antall faktorsett x);

Parameter SS bestemmes av formelen;

Parameter MS bestemmes av formelen;

Statistikk F bestemmes av formelen;

Betydning F. Hvis det resulterende tallet overstiger , er hypotesen akseptert (det er ingen lineær sammenheng), ellers aksepteres hypotesen (det er en lineær sammenheng).

3. Analyse av varianter, linje Rest:

Parameter df lik ;

Parameter SS bestemmes av formelen ;

Parameter MS bestemmes av formelen.

4. Analyse av varianter, linje Total inneholder summen av de to første kolonnene.

5. Analyse av varianter, linje Y-kryss inneholder koeffisienten, standardfeil og t-statistikk.

P-verdi ¾ er verdien av signifikansnivåene som tilsvarer de beregnede t- statistikere. Bestemmes av funksjonen STUDIST( t-statistikk; ). Hvis P-verdi overstiger , da er den tilsvarende variabelen statistisk insignifikant og kan ekskluderes fra modellen.

Nedre 95 % Og Topp 95 %¾ er de nedre og øvre grensene for 95 prosent konfidensintervaller for koeffisientene til den teoretiske lineære regresjonsligningen. Hvis konfidenssannsynlighetsverdien i datainndatablokken ble stående på standardverdien, vil de to siste kolonnene duplisere de forrige. Hvis brukeren har angitt en konfidensverdi, inneholder de to siste kolonnene de nedre og øvre grenseverdiene for det angitte konfidensnivået.

6. Analyse av varianter, linjer inneholder verdiene av koeffisienter, standardfeil, t-statistiker, P-verdier og konfidensintervaller for de tilsvarende .

7. Blokker Uttak av saldo inneholder de anslåtte verdiene y(i vår notasjon er dette ) og rester .

Regresjonsanalyse i Microsoft Excel- mest komplette guider om å bruke MS Excel til å løse problemer regresjonsanalyse i Business Analytics. Konrad Carlberg forklarer tydelig teoretiske problemstillinger, hvis kunnskap vil hjelpe deg å unngå mange feil både når du utfører regresjonsanalyse selv og når du evaluerer resultatene av analyser utført av andre mennesker. Alt materiale, fra enkle korrelasjoner og t-tester til flere analyser av kovarians, er basert på virkelige eksempler og er ledsaget Detaljert beskrivelse tilsvarende trinnvise prosedyrer.

Boken diskuterer funksjonene og kontroversene knyttet til Excel-funksjoner for å jobbe med regresjon, diskuterer konsekvensene av å bruke hvert alternativ og hvert argument, og forklarer hvordan man bruker pålitelig regresjonsmetoder på områder som spenner fra medisinsk forskning til finansiell analyse.

Konrad Carlberg. Regresjonsanalyse i Microsoft Excel. – M.: Dialektikk, 2017. – 400 s.

Last ned notatet i eller format, eksempler i format

Kapittel 1: Vurdere datavariabilitet

Statistikere har mange variasjonsmål til rådighet. En av dem er summen av kvadrerte avvik av individuelle verdier fra gjennomsnittet. I Excel brukes SQUARE()-funksjonen til dette. Men varians brukes oftere. Dispersjon er gjennomsnittet av kvadrerte avvik. Variansen er ufølsom for antall verdier i datasettet som studeres (mens summen av kvadrerte avvik øker med antall målinger).

Excel tilbyr to funksjoner som returnerer varians: DISP.G() og DISP.V():

Bruk funksjonen DISP.G() hvis verdiene som skal behandles danner en populasjon. Det vil si at verdiene i området er de eneste verdiene du er interessert i.
Bruk funksjonen DISP.B() hvis verdiene som skal behandles danner et utvalg fra en større populasjon. Det antas at det er tilleggsverdier hvis varians du også kan estimere.

Hvis en mengde som en gjennomsnitt eller korrelasjonskoeffisient beregnes fra en populasjon, kalles det en parameter. En tilsvarende mengde beregnet på grunnlag av et utvalg kalles en statistikk. Telle avvik fra gjennomsnittet V dette settet, vil du få en sum av kvadrerte avvik som er mindre enn hvis du telte dem fra noen annen verdi. Et lignende utsagn gjelder for varians.

Jo større utvalgsstørrelsen er, desto mer nøyaktig er den beregnede statistiske verdien. Men det er ingen utvalgsstørrelse som er mindre enn populasjonsstørrelsen som du kan være sikker på at den statistiske verdien samsvarer med parameterverdien for.

La oss si at du har et sett med 100 høyder hvis gjennomsnitt avviker fra gjennomsnittet for befolkningen, uansett hvor liten forskjellen er. Ved å beregne variansen for en prøve, vil du få en verdi, for eksempel 4. Denne verdien er mindre enn noen annen verdi som kan oppnås ved å beregne avviket til hver av 100 høydeverdier i forhold til en annen verdi enn prøvegjennomsnittet , inkludert i forhold til den sanne gjennomsnittlige befolkningen. Derfor vil den beregnede variansen være forskjellig, og mindre, fra variansen du ville fått hvis du på en eller annen måte fant ut og brukte en populasjonsparameter i stedet for et utvalg gjennomsnitt.

Middelsummen av kvadrater bestemt for prøven gir lavere estimat populasjonsvariasjoner. Variansen beregnet på denne måten kalles fortrengt evaluering. Det viser seg at for å eliminere skjevheten og oppnå et objektivt estimat, er det nok å dele summen av kvadrerte avvik ikke med n, Hvor n- prøvestørrelse, og n – 1.

Omfanget n – 1 kalles antall (antall) frihetsgrader. Det er forskjellige måter å beregne denne mengden på, selv om de alle innebærer enten å trekke et tall fra prøvestørrelsen eller telle antall kategorier som observasjonene faller inn under.

Essensen av forskjellen mellom funksjonene DISP.G() og DISP.V() er som følger:

I funksjonen VAR.G() er summen av kvadrater delt på antall observasjoner og representerer derfor et skjevt estimat av variansen, det sanne gjennomsnittet.
I funksjonen DISP.B() er summen av kvadrater delt på antall observasjoner minus 1, dvs. ved antall frihetsgrader, som gir et mer nøyaktig, objektivt estimat av variansen til populasjonen som utvalget ble trukket fra.

Standardavvik standardavvik, SD) – er kvadratroten av variansen:

Kvadring av avvikene forvandler måleskalaen til en annen metrikk, som er kvadratet på den opprinnelige: meter - til kvadratmeter, dollar - til kvadratiske dollar osv. Standardavviket er kvadratroten av variansen, og tar oss derfor tilbake til de opprinnelige måleenhetene. Det som er mest praktisk.

Det er ofte nødvendig å beregne standardavviket etter at dataene har vært utsatt for noe manipulasjon. Og selv om resultatene i disse tilfellene utvilsomt er standardavvik, kalles de vanligvis standard feil. Det finnes flere typer standardfeil, inkludert standard målefeil, standard proporsjonsfeil og standard gjennomsnittsfeil.

La oss si at du samlet inn høydedata for 25 tilfeldig utvalgte voksne menn i hver av de 50 statene. Deretter beregner du gjennomsnittshøyden til voksne menn i hver stat. De resulterende 50 gjennomsnittsverdiene kan i sin tur betraktes som observasjoner. Fra dette kan du beregne standardavviket deres, som er standard feil for gjennomsnittet. Ris. 1. sammenligner fordelingen av 1250 rå individuelle verdier (høydedata for 25 menn i hver av de 50 delstatene) med fordelingen av de 50 delstatsgjennomsnittene. Formelen for å estimere standardfeilen til gjennomsnittet (det vil si standardavviket til gjennomsnittet, ikke individuelle observasjoner):

hvor er standardfeilen til gjennomsnittet; s– standardavvik fra de opprinnelige observasjonene; n– antall observasjoner i utvalget.

Ris. 1. Variasjon i gjennomsnitt fra stat til stat er betydelig mindre enn variasjon i enkeltobservasjoner.

I statistikk er det enighet om bruk av gresk og latinske bokstaver for å angi statistiske mengder. Det er vanlig å betegne parametere for den generelle befolkningen med greske bokstaver, og prøvestatistikk med latinske bokstaver. Derfor, når vi snakker om populasjonsstandardavviket, skriver vi det som σ; hvis standardavviket til prøven vurderes, bruker vi notasjonen s. Når det gjelder symbolene for å angi gjennomsnitt, stemmer de ikke så godt med hverandre. Befolkningsgjennomsnittet er angitt med den greske bokstaven μ. Imidlertid brukes symbolet X̅ tradisjonelt for å representere prøvegjennomsnittet.

z-score uttrykker posisjonen til en observasjon i fordelingen i standardavviksenheter. For eksempel betyr z = 1,5 at observasjonen er 1,5 standardavvik unna gjennomsnittet. Begrep z-score brukes til individuelle vurderinger, dvs. for målinger tilskrevet individuelle elementer prøver. Begrepet som brukes for å referere til slik statistikk (som statens gjennomsnitt) z-score:

hvor X̅ er utvalgets gjennomsnitt, μ er populasjonsmiddelet, er standardfeilen til gjennomsnittet for et sett med utvalg:

hvor σ er standardfeilen til populasjonen (individuelle målinger), n– prøvestørrelse.

La oss si at du jobber som instruktør på en golfklubb. Du har kunnet måle avstanden til skuddene dine over lang tid og vet at gjennomsnittet er 205 yards og standardavviket er 36 yards. Du blir tilbudt en ny kølle, og hevder at den vil øke treffavstanden din med 10 yards. Du ber hver av de neste 81 klubbkundene om å ta et prøveskudd med en ny kølle og registrere svingdistansen deres. Det viste seg at gjennomsnittsdistansen med den nye klubben var 215 yards. Hva er sannsynligheten for at en forskjell på 10 yards (215 – 205) utelukkende skyldes prøvetakingsfeil? Eller for å si det på en annen måte: Hva er sannsynligheten for at den nye klubben i mer omfattende testing ikke vil vise en økning i treffavstand over det eksisterende langsiktige gjennomsnittet på 205 yards?

Vi kan sjekke dette ved å generere en z-score. Standard feil for gjennomsnittet:

Så z-score:

Vi må finne sannsynligheten for at utvalgsgjennomsnittet vil være 2,5σ unna populasjonsgjennomsnittet. Hvis sannsynligheten er liten, så skyldes ikke forskjellene tilfeldigheter, men kvaliteten på den nye klubben. Excel har ikke en ferdig funksjon for å bestemme z-score sannsynlighet. Du kan imidlertid bruke formelen =1-NORM.ST.FORDELING(z-score,TRUE), hvor NORM.ST.FORDELING()-funksjonen returnerer arealet under normalkurven til venstre for z-skåren (figur 2).

Ris. 2. NORM.ST.DIST()-funksjonen returnerer området under kurven til venstre for z-verdien; For å forstørre bildet, klikk på det Høyreklikk mus og velg Åpne bildet i ny fane

Det andre argumentet til NORM.ST.DIST()-funksjonen kan ha to verdier: TRUE – funksjonen returnerer arealet av området under kurven til venstre for punktet spesifisert av det første argumentet; FALSE – funksjonen returnerer høyden på kurven ved punktet spesifisert av det første argumentet.

Hvis populasjonsmiddelverdien (μ) og standardavviket (σ) ikke er kjent, brukes t-verdien (se detaljer). Strukturene for z-score og t-score er forskjellige ved at standardavviket s oppnådd fra prøveresultatene brukes til å finne t-skåren i stedet for den kjente verdien av populasjonsparameteren σ. Normalkurven har en enkelt form, og formen på t-verdifordelingen varierer avhengig av antall frihetsgrader df. grader av frihet) av utvalget den representerer. Antall frihetsgrader for prøven er lik n – 1, Hvor n- prøvestørrelse (fig. 3).

Ris. 3. Formen på t-fordelinger som oppstår i tilfeller hvor parameteren σ er ukjent skiller seg fra formen på normalfordelingen

Excel har to funksjoner for t-fordelingen, også kalt Student-fordelingen: STUDENT.DIST() returnerer arealet under kurven til venstre for en gitt t-verdi, og STUDENT.DIST.PH() returnerer området til Ikke sant.

Kapittel 2. Korrelasjon

Korrelasjon er et mål på avhengighet mellom elementer i et sett med ordnede par. Korrelasjonen er karakterisert Pearson korrelasjonskoeffisienter–r. Koeffisienten kan ta verdier i området fra –1,0 til +1,0.

Hvor Sx Og S y– standardavvik for variabler X Og Y, S xy– kovarians:

I denne formelen er kovariansen delt på standardavvikene til variablene X Og Y, og fjerner dermed enhetsrelaterte skaleringseffekter fra kovariansen. Excel bruker funksjonen CORREL(). Navnet på denne funksjonen inneholder ikke de kvalifiserende elementene Г og В, som brukes i navn på funksjoner som STANDARDEV(), VARIANCE() eller COVARIANCE(). Selv om prøvekorrelasjonskoeffisienten gir et skjevt estimat, er årsaken til skjevheten annerledes enn ved varians eller standardavvik.

Avhengig av størrelsen på den generelle korrelasjonskoeffisienten (ofte betegnet med den greske bokstaven ρ ), korrelasjonskoeffisient r produserer et partisk estimat, hvor effekten av skjevhet øker etter hvert som prøvestørrelsene reduseres. Vi prøver imidlertid ikke å korrigere denne skjevheten på samme måte som vi for eksempel gjorde når vi beregnet standardavviket, da vi ikke erstattet antall observasjoner, men antall frihetsgrader i den tilsvarende formelen. I virkeligheten har antallet observasjoner som brukes til å beregne kovariansen ingen effekt på størrelsen.

Ser ment for bruk med variabler som er relatert til hverandre ved en lineær sammenheng. Tilstedeværelsen av ikke-linearitet og/eller feil i dataene (outliers) fører til feil beregning av korrelasjonskoeffisienten. For å diagnostisere dataproblemer, anbefales det å lage spredningsplott. Dette er den eneste diagramtypen i Excel som behandler både den horisontale og vertikale aksen som verdiakser. Et linjediagram definerer en av kolonnene som kategoriaksen, noe som forvrenger bildet av dataene (fig. 4).

Ris. 4. Regresjonslinjene virker like, men sammenligner ligningene deres med hverandre

Observasjonene som brukes til å konstruere linjediagrammet er arrangert like langt langs den horisontale aksen. Divisjonsetikettene langs denne aksen er bare etiketter, ikke numeriske verdier.

Selv om korrelasjon ofte betyr at det er en årsak-virkning-sammenheng, kan den ikke brukes til å bevise at dette er tilfelle. Statistikk brukes ikke til å vise om en teori er sann eller usann. For å utelukke konkurrerende forklaringer for observasjonsresultater, sett planlagte eksperimenter. Statistikk brukes til å oppsummere informasjonen som er samlet inn under slike eksperimenter og for å kvantifisere sannsynligheten for at beslutningen som er tatt kan være feil gitt det tilgjengelige bevisgrunnlaget.

Kapittel 3: Enkel regresjon

Hvis to variabler er relatert til hverandre, slik at verdien av korrelasjonskoeffisienten overstiger for eksempel 0,5, så er det i dette tilfellet mulig å forutsi (med en viss nøyaktighet) den ukjente verdien til en variabel fra den kjente verdien til den andre . For å få prognoseprisverdier basert på dataene vist i fig. 5, kan hvilken som helst av flere brukes mulige måter, men du vil nesten helt sikkert ikke bruke den som er vist i fig. 5. Likevel bør du gjøre deg kjent med det, fordi ingen annen metode lar deg demonstrere sammenhengen mellom korrelasjon og prediksjon så tydelig som denne. I fig. 5 i området B2:C12 viser et tilfeldig utvalg av ti hus og gir data om arealet av strandhuset (i kvadratfot) og salgsprisen.

Ris. 5. Prognostiserte salgsprisverdier danner en rett linje

Finn gjennomsnitt, standardavvik og korrelasjonskoeffisient (område A14:C18). Beregn areal z-poeng (E2:E12). For eksempel inneholder celle E3 formelen: =(B3-$B$14)/$B$15. Beregn z-skårene til prognoseprisen (F2:F12). For eksempel inneholder celle F3 formelen: =ЕЗ*$В$18. Konverter z-score til dollarpriser (H2:H12). I celle NZ er formelen: =F3*$C$15+$C$14.

Merk at den predikerte verdien alltid har en tendens til å skifte mot gjennomsnittet av 0. Jo nærmere korrelasjonskoeffisienten er null, jo nærmere null er den anslåtte z-skåren. I vårt eksempel er korrelasjonskoeffisienten mellom areal og salgspris 0,67, og prognoseprisen er 1,0 * 0,67, dvs. 0,67. Dette tilsvarer et overskudd av en verdi over gjennomsnittet lik to tredjedeler av et standardavvik. Hvis korrelasjonskoeffisienten var lik 0,5, ville prognoseprisen vært 1,0 * 0,5, dvs. 0,5. Dette tilsvarer et overskudd av en verdi over gjennomsnittet lik bare et halvt standardavvik. Når verdien av korrelasjonskoeffisienten er forskjellig fra den ideelle verdien, dvs. større enn -1,0 og mindre enn 1,0, bør poengsummen til den predikerte variabelen være nærmere dens gjennomsnitt enn poengsummen til den prediktorvariabelen (uavhengige) til sin egen. Dette fenomenet kalles regresjon til gjennomsnittet, eller ganske enkelt regresjon.

Excel har flere funksjoner for å bestemme koeffisientene til en regresjonslinjeligning (kalt en trendlinje i Excel) y =kx + b. For å bestemme k tjener funksjon

=SLOPE(kjente_y_verdier, kjente_x_verdier)

Her på er den predikerte variabelen, og X- uavhengig variabel. Du må strengt følge denne rekkefølgen av variabler. Helningen til regresjonslinjen, korrelasjonskoeffisient, standardavvik for variablene og kovarians er nært beslektet (Figur 6). INTERMEPT()-funksjonen returnerer verdien fanget opp av regresjonslinjen på den vertikale aksen:

=LIMIT(kjente_y_verdier, kjente_x_verdier)

Ris. 6. Forholdet mellom standardavvik konverterer kovariansen til en korrelasjonskoeffisient og helningen til regresjonslinjen

Legg merke til at antall x- og y-verdier gitt som argumenter til funksjonene SLOPE() og INTERCEPT() må være det samme.

Regresjonsanalyse bruker en annen viktig indikator– R 2 (R-kvadrat), eller bestemmelseskoeffisient. Det bestemmer hvilket bidrag til den generelle variasjonen til dataene som gis av forholdet mellom X Og på. I Excel er det en funksjon for den kalt CVPIERSON(), som tar nøyaktig de samme argumentene som CORREL()-funksjonen.

To variabler med en ikke-null korrelasjonskoeffisient mellom seg sies å forklare varians eller har varians forklart. Typisk forklart varians uttrykkes i prosent. Så R 2 = 0,81 betyr at 81 % av variansen (spredningen) av to variabler er forklart. De resterende 19 % skyldes tilfeldige svingninger.

Excel har en TREND-funksjon som gjør beregningene enklere. TREND() funksjon:

godtar de kjente verdiene du oppgir X og kjente verdier på;
beregner helningen til regresjonslinjen og konstanten (skjæringspunktet);
returnerer anslåtte verdier på, bestemt ved å bruke en regresjonsligning på kjente verdier X(Fig. 7).

TREND()-funksjonen er en array-funksjon (hvis du ikke har møtt slike funksjoner før, anbefaler jeg).

Ris. 7. Bruk av TREND()-funksjonen lar deg fremskynde og forenkle beregninger sammenlignet med å bruke et par SLOPE()- og INTERCEPT()-funksjoner

For å angi TREND()-funksjonen som en matriseformel i cellene G3:G12, velg området G3:G12, skriv inn formelen TREND (NW:S12;V3:B12), trykk og hold nede tastene og først etter det trykker du på tasten . Vær oppmerksom på at formelen finnes i tannregulering: ( Og ). Dette er hvordan Excel forteller deg det denne formelen oppfattes nettopp som en matriseformel. Ikke skriv inn parentesene selv: Hvis du prøver å skrive dem inn selv som en del av en formel, vil Excel behandle innspillene dine som en vanlig tekststreng.

TREND()-funksjonen har ytterligere to argumenter: nye_verdier_x Og konst. Den første lar deg lage en prognose for fremtiden, og den andre kan tvinge regresjonslinjen til å gå gjennom origo (en verdi på TRUE forteller Excel å bruke den beregnede konstanten, en verdi på FALSE forteller Excel å bruke en konstant = 0 ). Excel lar deg tegne en regresjonslinje på en graf slik at den går gjennom origo. Start med å tegne et spredningsplott, og høyreklikk deretter på en av dataseriemarkørene. Velg i vinduet som åpnes kontekstmenyen avsnitt Legg til en trendlinje; Velg et alternativ Lineær; om nødvendig, bla nedover panelet, merk av i boksen Sett opp kryss; Sørg for at den tilhørende tekstboksen er satt til 0.0.

Hvis du har tre variabler og du vil bestemme korrelasjonen mellom to av dem mens du eliminerer påvirkningen fra den tredje, kan du bruke delvis korrelasjon. Anta at du er interessert i forholdet mellom prosentandelen av en bys innbyggere som har fullført college og antall bøker i byens biblioteker. Du har samlet inn data for 50 byer, men... Problemet er at begge disse parameterne kan avhenge av trivselen til innbyggerne i en bestemt by. Det er selvsagt svært vanskelig å finne andre 50 byer preget av nøyaktig samme trivsel hos innbyggerne.

Ved å bruke statistiske metoder for å kontrollere rikdommens innflytelse på både bibliotekstøtte og høyskoleoverkommelighet, kunne man oppnå en mer presis kvantifisering av styrken til forholdet mellom variablene av interesse, nemlig antall bøker og antall kandidater. En slik betinget korrelasjon mellom to variabler, når verdiene til andre variabler er faste, kalles partiell korrelasjon. En måte å beregne det på er å bruke ligningen:

Hvor rC.B. . W- Korrelasjonskoeffisient mellom College- og Books-variablene med påvirkning (fast verdi) av Wealth-variabelen ekskludert; rC.B.- korrelasjonskoeffisient mellom variablene College og Books; rCW- korrelasjonskoeffisient mellom høgskolen og velferdsvariablene; rB.W.- korrelasjonskoeffisient mellom variablene Bøker og Velferd.

På den annen side kan partiell korrelasjon beregnes basert på analyse av residualer, d.v.s. forskjeller mellom de predikerte verdiene og de tilhørende resultatene av faktiske observasjoner (begge metodene er presentert i fig. 8).

Ris. 8. Delkorrelasjon som korrelasjon av residualer

For å forenkle beregningen av korrelasjonskoeffisientmatrisen (B16:E19), bruk pakken Excel-analyse(Meny Data –> Analyse –> Dataanalyse). Som standard er ikke denne pakken aktiv i Excel. For å installere den, gå gjennom menyen Fil –> Alternativer –> Tillegg. Nederst i det åpne vinduet Alternativerutmerke finne feltet Kontroll, plukke ut Tilleggutmerke, klikk Gå. Merk av i boksen ved siden av tillegget Analysepakke. Klikk A dataanalyse, velg alternativ Sammenheng. Spesifiser $B$2:$D$13 som inndataintervall, merk av i boksen Etiketter i første linje, spesifiser $B$16:$E$19 som utdataintervall.

En annen mulighet er å bestemme semi-partiell korrelasjon. For eksempel undersøker du effekten av høyde og alder på vekt. Dermed har du to prediktorvariabler - høyde og alder, og en prediktorvariabel - vekt. Du vil ekskludere innflytelsen av en prediktorvariabel på en annen, men ikke på prediktorvariabelen:

hvor H – Høyde, W – Vekt, A – Alder; Den semi-partielle korrelasjonskoeffisientindeksen bruker parenteser for å indikere hvilken variabel som fjernes og fra hvilken variabel. I i dette tilfellet notasjonen W(H.A) indikerer at effekten av aldersvariabelen er fjernet fra høydevariabelen, men ikke fra vektvariabelen.

Det kan se ut til at problemstillingen som diskuteres ikke er av vesentlig betydning. Tross alt er det viktigste hvor nøyaktig det fungerer generell ligning regresjon, mens problemet med individuelle variables relative bidrag til den totale forklarte variansen ser ut til å være av sekundær betydning. Dette er imidlertid ikke tilfelle. Når du begynner å lure på om en variabel er verdt å bruke i en multippel regresjonsligning i det hele tatt, blir problemet viktig. Det kan påvirke vurderingen av riktigheten av valg av modell for analyse.

Kapittel 4. LINEST() funksjon

Funksjonen LINEST() returnerer 10 regresjonsstatistikker. Funksjonen LINEST() er en matrisefunksjon. For å angi det, velg et område som inneholder fem rader og to kolonner, skriv inn formelen og klikk (Fig. 9):

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

Ris. 9. LINEST() funksjon: a) velg området D2:E6, b) skriv inn formelen som vist i formellinjen, c) klikk

Funksjonen LINEST() returnerer:

regresjonskoeffisient (eller helning, celle D2);
segment (eller konstant, celle E3);
standard feil regresjonskoeffisient og konstant (område D3:E3);
bestemmelseskoeffisient R2 for regresjon (celle D4);
standard estimatfeil (celle E4);
F-test for full regresjon (celle D5);
antall frihetsgrader for restsummen av kvadrater (celle E5);
regresjonssum av kvadrater (celle D6);
restsum av kvadrater (celle E6).

La oss se på hver av disse statistikkene og hvordan de samhandler.

Standard feil i vårt tilfelle er det standardavviket beregnet for prøvetakingsfeil. Det vil si at dette er en situasjon der befolkningen generelt har én statistikk, og utvalget har en annen. Ved å dele regresjonskoeffisienten med standardfeilen får du en verdi på 2,092/0,818 = 2,559. Med andre ord, en regresjonskoeffisient på 2,092 er to og en halv standardfeil unna null.

Hvis regresjonskoeffisienten lik null, Det beste estimat den predikerte variabelen er dens gjennomsnitt. To og en halv standardfeil er ganske stort, og du kan trygt anta at regresjonskoeffisienten for populasjonen er fra null.

Du kan bestemme sannsynligheten for å motta prøvetakingskoeffisient regresjon 2,092 hvis den faktiske verdien i populasjonen er 0,0 ved å bruke funksjonen

STUDENT.DIST.PH (t-kriterium = 2,559; antall frihetsgrader = 18)

Generelt er antall frihetsgrader = n – k – 1, hvor n er antall observasjoner og k er antall prediktorvariabler.

Denne formelen returnerer 0,00987, eller avrundet til 1 %. Den forteller oss at hvis regresjonskoeffisienten for populasjonen er 0 %, så er sannsynligheten for å få et utvalg på 20 personer der den estimerte regresjonskoeffisienten er 2,092 beskjedne 1 %.

F-testen (celle D5 i fig. 9) utfører de samme funksjonene i forhold til full regresjon som t-testen i forhold til koeffisienten for enkel parvis regresjon. F-testen brukes for å teste om determinasjonskoeffisienten R 2 for en regresjon er stor nok til å forkaste hypotesen om at den i populasjonen har en verdi på 0,0, noe som indikerer at det ikke er noen varians som forklares av prediktoren og predikterte variabelen. Når det bare er én prediktorvariabel, er F-testen nøyaktig lik t-testen i annen.

Så langt har vi sett på intervallvariabler. Hvis du har variabler som kan ta på seg flere verdier, representerer enkle navn, for eksempel mann og kvinne eller reptil, amfibie og fisk, representerer dem som en numerisk kode. Slike variabler kalles nominelle.

R2 statistikk kvantifiserer andelen av varians forklart.

Standard estimatfeil. I fig. Figur 4.9 presenterer de predikerte verdiene til Vekt-variabelen, oppnådd på grunnlag av forholdet til høyde-variabelen. Området E2:E21 inneholder restverdiene for vektvariabelen. Mer presist kalles disse residualene feil - derav begrepet standard estimeringsfeil.

Ris. 10. Både R 2 og standardfeilen til estimatet uttrykker nøyaktigheten til prognosene oppnådd ved bruk av regresjon

Jo mindre standardfeilen til estimatet er, desto mer nøyaktig er regresjonsligningen, og jo nærmere forventer du at en prediksjon produsert av ligningen samsvarer med den faktiske observasjonen. Standard estimeringsfeil gir en måte å kvantifisere disse forventningene på. Vekten til 95 % av personer med en viss høyde vil være i området:

(høyde * 2.092 – 3.591) ± 2.092 * 21.118

F-statistikk er forholdet mellom varians mellom gruppe og varians innen gruppe. Dette navnet ble introdusert av statistikeren George Snedecor til ære for Sir, som utviklet variansanalyse (ANOVA, Analysis of Variance) på begynnelsen av 1900-tallet.

Bestemmelseskoeffisienten R 2 uttrykker andelen av totalsummen av kvadrater knyttet til regresjonen. Verdien (1 – R 2) uttrykker andelen av totalsummen av kvadrater knyttet til residualer - prognosefeil. F-testen kan oppnås ved å bruke LINJE-funksjonen (celle F5 i fig. 11), ved bruk av kvadratsummer (område G10:J11), ved bruk av variansproporsjoner (område G14:J15). Formlene kan studeres i vedlagte Excel-fil.

Ris. 11. Beregning av F-kriterium

Ved bruk av nominelle variabler brukes dummy-koding (Figur 12). For å kode verdier er det praktisk å bruke verdiene 0 og 1. Sannsynligheten F beregnes ved hjelp av funksjonen:

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

Her returnerer funksjonen F.DIST.PH() sannsynligheten for å oppnå et F-kriterium som følger den sentrale F-fordelingen (fig. 13) for to sett med data med tallene for frihetsgrader gitt i celle I2 og I3, verdien som faller sammen med verdien gitt i celle K2.

Ris. 12. Regresjonsanalyse ved bruk av dummyvariabler

Ris. 13. Sentral F-fordeling ved λ = 0

Kapittel 5. Multippel regresjon

Når du går fra enkel parvis regresjon med én prediktorvariabel til multippel regresjon, legger du til en eller flere prediktorvariabler. Lagre verdiene til prediktorvariablene i tilstøtende kolonner, for eksempel kolonne A og B i tilfelle av to prediktorer, eller A, B og C i tilfelle av tre prediktorer. Før du skriver inn en formel som inkluderer LINEST()-funksjonen, velg fem rader og så mange kolonner som det er prediktorvariabler, pluss én til for konstanten. Ved regresjon med to prediktorvariabler kan følgende struktur brukes:

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

Tilsvarende når det gjelder tre variabler:

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

La oss si at du ønsker å studere mulige effekter av alder og kosthold på LDL-nivåer – lipoproteiner med lav tetthet, som antas å være ansvarlige for dannelsen av aterosklerotiske plakk, som forårsaker aterotrombose (fig. 14).

Ris. 14. Multippel regresjon

R 2 for multippel regresjon (reflektert i celle F13) er større enn R 2 for enhver enkel regresjon (E4, H4). Multippel regresjon bruker flere prediktorvariabler samtidig. I dette tilfellet øker R2 nesten alltid.

For enhver enkel lineær ligning I en regresjon med en prediktorvariabel vil det alltid være en perfekt korrelasjon mellom de predikerte verdiene og verdiene til prediktorvariabelen, siden i en slik ligning multipliseres prediktorverdiene med en konstant og en annen konstant er lagt til hvert produkt. Denne effekten vedvarer ikke ved multippel regresjon.

Viser resultatene returnert av LINEST()-funksjonen for multippel regresjon (Figur 15). Regresjonskoeffisienter sendes ut som en del av resultatene som returneres av LINEST()-funksjonen i omvendt rekkefølge av variabler(G–H–I tilsvarer C–B–A).

Ris. 15. Koeffisienter og deres standardfeil vises i omvendt rekkefølge følge dem på arbeidsarket

Prinsippene og prosedyrene som brukes i regresjonsanalyse for én prediktorvariabel kan enkelt tilpasses for å ta hensyn til flere prediktorvariabler. Det viser seg at mye av denne tilpasningen er avhengig av å eliminere påvirkningen av prediktorvariablene på hverandre. Sistnevnte er assosiert med partielle og semi-partielle korrelasjoner (fig. 16).

Ris. 16. Multippel regresjon kan uttrykkes gjennom parvis regresjon av residualer (se Excel-fil for formler)

I Excel er det funksjoner som gir informasjon om t- og F-fordelinger. Funksjoner hvis navn inkluderer DIST-delen, for eksempel STUDENT.DIST() og F.DIST(), tar en t-test eller F-test som argument og returnerer sannsynligheten for å observere en spesifisert verdi. Funksjoner hvis navn inkluderer OBR-delen, som STUDENT.INV() og F.INR(), tar en sannsynlighetsverdi som et argument og returnerer en kriterieverdi som tilsvarer den angitte sannsynligheten.

Mens vi søker kritiske verdier t-fordelinger som avskjærer kantene på haleområdene, sender vi 5 % som argument til en av STUDENT.INV()-funksjonene, som returnerer verdien som tilsvarer denne sannsynligheten (fig. 17, 18).

Ris. 17. To-hale t-test

Ris. 18. Ensidig t-test

Ved å etablere en beslutningsregel for den enkelthalede alfaregionen øker du testens statistiske kraft. Hvis du, når du starter et eksperiment, er sikker på at du har all grunn til å forvente en positiv (eller negativ) regresjonskoeffisient, bør du utføre en enkelthaletest. I dette tilfellet vil sannsynligheten for at du tar den riktige avgjørelsen ved å avvise hypotesen om en null regresjonskoeffisient i populasjonen være høyere.

Statistikere foretrekker å bruke begrepet rettet test i stedet for begrepet enkelthaletest og termin urettet test i stedet for begrepet to-hale test. Begrepene rettet og urettet er å foretrekke fordi de legger vekt på typen hypotese i stedet for arten til fordelingens haler.

En tilnærming for å vurdere virkningen av prediktorer basert på modellsammenligning. I fig. Figur 19 viser resultatene av en regresjonsanalyse som tester bidraget til Diet-variabelen til regresjonsligningen.

Ris. 19. Sammenligning av to modeller ved å teste forskjeller i resultatene deres

Resultatene av LINEST()-funksjonen (område H2:K6) er relatert til det jeg kaller den fulle modellen, som regresserer LDL-variabelen på diett-, alders- og HDL-variablene. Området H9:J13 presenterer beregninger uten å ta hensyn til prediktorvariabelen Kosthold. Jeg kaller dette den begrensede modellen. I den fullstendige modellen ble 49,2 % av variansen i den avhengige variabelen LDL forklart av prediktorvariablene. I den begrensede modellen er bare 30,8 % av LDL forklart av alders- og HDL-variablene. Tapet i R 2 på grunn av ekskludering av Diet-variabelen fra modellen er 0,183. I området G15:L17 gjøres det beregninger som viser at det kun er en sannsynlighet på 0,0288 for at effekten av Diett-variabelen er tilfeldig. I de resterende 97,1 % har diett en effekt på LDL.

Kapittel 6: Forutsetninger og advarsler for regresjonsanalyse

Begrepet «antakelse» er ikke definert strengt nok, og måten det brukes på antyder at dersom forutsetningen ikke oppfylles, så er resultatene av hele analysen i det minste tvilsomme eller kanskje ugyldige. Dette er faktisk ikke tilfelle, selv om det absolutt finnes tilfeller der brudd på en forutsetning endrer bildet fundamentalt. Grunnleggende antakelser: a) residualene til Y-variabelen er normalfordelt på et hvilket som helst punkt X langs regresjonslinjen; b) Y-verdier er inne lineær avhengighet fra X-verdier; c) dispersjonen av restene er omtrent den samme ved hvert punkt X; d) det er ingen avhengighet mellom restene.

Dersom forutsetninger ikke spiller en vesentlig rolle, sier statistikere at analysen er robust for brudd på forutsetningen. Spesielt når du bruker regresjon for å teste for forskjeller mellom gruppemiddelverdier, spiller ikke antakelsen om at Y-verdiene - og dermed residualene - er normalfordelte, noen vesentlig rolle: testene er robuste for brudd på normalitetsantagelsen. Det er viktig å analysere data ved hjelp av diagrammer. For eksempel inkludert i tillegget Dataanalyse verktøy Regresjon.

Hvis dataene ikke oppfyller forutsetningene om lineær regresjon, er det andre tilnærminger enn lineær regresjon til din disposisjon. En av dem er logistisk regresjon (fig. 20). Nær de øvre og nedre grensene for prediktorvariabelen produserer lineær regresjon urealistiske prediksjoner.

Ris. 20. Logistisk regresjon

I fig. Figur 6.8 viser resultatene av to dataanalysemetoder som tar sikte på å undersøke sammenhengen mellom årsinntekt og sannsynligheten for å kjøpe bolig. Åpenbart vil sannsynligheten for å foreta et kjøp øke med økende inntekt. Diagrammene gjør det enkelt å se forskjellene mellom resultatene som lineær regresjon forutsier sannsynligheten for å kjøpe bolig og resultatene du kan få ved å bruke en annen tilnærming.

På statistikerspråk kalles det å avvise nullhypotesen når den faktisk er sann, en type I-feil.

I tillegget Dataanalyse tilbys hendig verktøyå generere tilfeldige tall, slik at brukeren kan spesifisere ønsket form for distribusjonen (for eksempel Normal, Binomial eller Poisson), samt gjennomsnittet og standardavviket.

Forskjeller mellom funksjoner i STUDENT.DIST()-familien. Begynner med Excel-versjoner 2010, tre forskjellige former for funksjonen er tilgjengelige som returnerer andelen av fordelingen til venstre og/eller til høyre for en gitt t-testverdi. STUDENT.DIST()-funksjonen returnerer brøkdelen av arealet under distribusjonskurven til venstre for t-testverdien du angir. La oss si at du har 36 observasjoner, så antallet frihetsgrader for analysen er 34 og t-testverdien = 1,69. I dette tilfellet formelen

STUDENT.DIST(+1.69;34;TRUE)

returnerer verdien 0,05, eller 5 % (Figur 21). Det tredje argumentet til STUDENT.DIST()-funksjonen kan være TRUE eller FALSE. Hvis satt til TRUE, returnerer funksjonen det kumulative arealet under kurven til venstre for den angitte t-testen, uttrykt som en proporsjon. Hvis den er FALSE, returnerer funksjonen den relative høyden til kurven ved punktet som tilsvarer t-testen. Andre versjoner av STUDENT.DIST()-funksjonen - STUDENT.DIST.PH() og STUDENT.DIST.2X() - tar bare t-testverdien og antall frihetsgrader som argumenter og krever ikke spesifisering av en tredjedel argument.

Ris. 21. Det mørkere skraverte området i venstre hale av fordelingen tilsvarer andelen av området under kurven til venstre for en stor positiv t-testverdi

For å bestemme området til høyre for t-testen, bruk en av formlene:

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

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

Hele arealet under kurven må være 100 %, så å trekke fra 1 brøkdelen av arealet til venstre for t-testverdien som funksjonen returnerer gir brøkdelen av arealet til høyre for t-testverdien. Du kan finne det å foretrekke direkte å få arealbrøken du er interessert i ved å bruke STUDENT.DIST.PH()-funksjonen, der PH betyr høyre hale av fordelingen (fig. 22).

Ris. 22. 5 % alfaregion for retningsbestemt test

Å bruke funksjonene STUDENT.DIST() eller STUDENT.DIST.PH() innebærer at du har valgt en retningsbestemt arbeidshypotese. Den retningsbestemte arbeidshypotesen kombinert med å sette alfaverdien til 5 % gjør at du plasserer alle 5 % i høyre hale av fordelingene. Du må bare forkaste nullhypotesen hvis sannsynligheten for t-testverdien du får er 5 % eller mindre. Retningshypoteser resulterer generelt i mer sensitive statistiske tester (denne større sensitiviteten kalles også større statistisk kraft).

I en urettet test forblir alfaverdien på samme 5 %-nivå, men fordelingen vil være annerledes. Fordi du må tillate to utfall, må sannsynligheten for en falsk positiv fordeles mellom de to delene av fordelingen. Det er generelt akseptert å fordele denne sannsynligheten likt (fig. 23).

Bruk den samme oppnådde t-testverdien og samme antall frihetsgrader som i forrige eksempel, bruk formelen

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

Uten spesiell grunn returnerer funksjonen STUDENT.DIST.2X() feilkoden #NUM! hvis den får en negativ t-testverdi som første argument.

Hvis prøvene inneholder annet nummer data, bruk to-utvalgs t-testen med forskjellige varianser inkludert i pakken Dataanalyse.

Kapittel 7: Bruke regresjon til å teste forskjeller mellom gruppemidler

Variabler som tidligere dukket opp under navnet prediktorvariabler vil i dette kapitlet bli kalt utfallsvariabler, og begrepet faktorvariabler vil bli brukt i stedet for begrepet prediktorvariabler.

Den enkleste tilnærmingen til å kode en nominell variabel er dummy koding(Fig. 24).

Ris. 24. Regresjonsanalyse basert på dummy-koding

Når du bruker dummy-koding av noe slag, bør følgende regler følges:

Antall kolonner reservert for nye data må være lik antall faktornivåer minus
Hver vektor representerer ett faktornivå.
Emner i et av nivåene, som ofte er kontrollgruppen, er kodet 0 i alle vektorer.

Formelen i cellene F2:H6 =LINEST(A2:A22;C2:D22;;SANN) returnerer regresjonsstatistikk. Til sammenligning, i fig. Figur 24 viser resultatene av tradisjonell ANOVA returnert av verktøyet. Enveis ANOVA tillegg Dataanalyse.

Effektkoding. I en annen type koding kalt effektkoding, Gjennomsnittet for hver gruppe sammenlignes med gjennomsnittet av gruppemiddelet. Dette aspektet ved effektkoding skyldes bruken av -1 i stedet for 0 som koden for gruppen, som mottar samme kode i alle kodevektorer (Figur 25).

Ris. 25. Effektkoding

Når dummy-koding brukes, er konstantverdien returnert av LINEST() gjennomsnittet av gruppen som er tildelt nullkoder i alle vektorer (vanligvis referansegruppen). Ved effektkoding er konstanten lik det totale gjennomsnittet (celle J2).

Generell lineær modell - nyttig måte konseptualisering av komponentene i verdien av den resulterende variabelen:

Y ij = μ + α j + ε ij

Bruken av greske bokstaver i denne formelen i stedet for latinske bokstaver understreker det faktum at den refererer til populasjonen som prøver er trukket fra, men den kan skrives om for å indikere at den refererer til prøver trukket fra en gitt populasjon:

Y ij = Y̅ + a j + e ij

Tanken er at hver observasjon Y ij kan sees på som summen av følgende tre komponenter: det store gjennomsnittet, μ; effekt av behandling j, og j; verdi e ij, som representerer avviket til den individuelle kvantitative indikatoren Y ij fra den kombinerte verdien av det generelle gjennomsnittet og effekten j-te behandling(Fig. 26). Målet med regresjonsligningen er å minimere summen av kvadrater av residualene.

Ris. 26. Observasjoner dekomponert til komponenter i en generell lineær modell

Faktor analyse. Hvis forholdet mellom utfallsvariabelen og to eller flere faktorer studeres samtidig, snakker vi i dette tilfellet om å bruke faktoranalyse. Å legge til en eller flere faktorer til en enveis ANOVA kan øke statistisk kraft. I enveis variansanalyse inkluderes varians i utfallsvariabelen som ikke kan tilskrives en faktor i residualmiddelkvadrat. Men det kan godt hende at denne variasjonen har sammenheng med en annen faktor. Deretter kan denne variasjonen fjernes fra middelkvadratfeilen, en reduksjon som fører til en økning i F-testverdiene, og derfor til en økning i testens statistiske styrke. Overbygg Dataanalyse inkluderer et verktøy som behandler to faktorer samtidig (fig. 27).

Ris. 27. Verktøy Toveis variansanalyse med repetisjoner av Analysepakken

ANOVA-verktøyet som brukes i denne figuren er nyttig fordi det returnerer gjennomsnittet og variansen til utfallsvariabelen, samt tellerverdien, for hver gruppe som er inkludert i designet. I bordet Analyse av varianter viser to parametere som ikke er til stede i utdataene til enkeltfaktorversjonen av ANOVA-verktøyet. Vær oppmerksom på kilder til variasjon Prøve Og Kolonner i linje 27 og 28. Kilde til variasjon Kolonner refererer til kjønn. Kilde til variasjon Prøve refererer til enhver variabel hvis verdier opptar ulike strenger. I fig. 27 verdier for KursLech1-gruppen er på linje 2-6, KursLech2-gruppen er på linje 7-11, og KursLechZ-gruppen er på linje 12-16.

Hovedpoenget er at begge faktorene, Kjønn (merke kolonner i celle E28) og Behandling (merke Prøve i celle E27), er inkludert i ANOVA-tabellen som variasjonskilder. Midlene for menn er forskjellige fra midlene for kvinner, og dette skaper en kilde til variasjon. Midlene for de tre behandlingene er også forskjellige, noe som gir en annen kilde til variasjon. Det er også en tredje kilde, Interaksjon, som viser til den kombinerte effekten av variablene Kjønn og Behandling.

Kapittel 8. Analyse av kovarians

Analyse av samvariasjon, eller ANCOVA (Analysis of Covariation), reduserer skjevhet og øker statistisk kraft. La meg minne deg på at en av måtene å vurdere påliteligheten til en regresjonsligning på er F-tester:

F = MS-regresjon/MS-residual

hvor MS (Mean Square) er middelkvadrat, og regresjons- og restindeksene indikerer henholdsvis regresjons- og restkomponentene. MS Residual beregnes ved å bruke formelen:

MS Residual = SS Residual / df Residual

hvor SS (Sum of Squares) er summen av kvadrater, og df er antall frihetsgrader. Når du legger til kovarians til en regresjonsligning, er en del av den totale summen av kvadrater inkludert ikke i SS ResiduaI, men i SS Regresjon. Dette fører til en nedgang i SS-rester, og dermed MS-rester. Jo mindre MS-residualet er, desto større er F-testen og desto mer sannsynlig er det at du forkaster nullhypotesen om ingen forskjell mellom middelene. Som et resultat omfordeler du variabiliteten til utfallsvariabelen. I ANOVA, når kovarians ikke tas i betraktning, blir variabilitet feil. Men i ANCOVA blir en del av variabiliteten som tidligere ble tilskrevet feilbegrepet, tilordnet en kovariat og blir en del av SS-regresjon.

Tenk på et eksempel der det samme datasettet analyseres først med ANOVA og deretter med ANCOVA (Figur 28).

Ris. 28. ANOVA-analyse indikerer at resultatene oppnådd fra regresjonsligningen er upålitelige

Studien sammenligner de relative effektene av fysisk trening, som utvikler muskelstyrke, og kognitiv trening (å gjøre kryssord), som stimulerer hjerneaktivitet. Forsøkspersonene ble tilfeldig fordelt i to grupper slik at begge gruppene ble utsatt for de samme forholdene i begynnelsen av eksperimentet. Etter tre måneder ble forsøkspersonenes kognitive ytelse målt. Resultatene av disse målingene er vist i kolonne B.

Området A2:C21 inneholder kildedataene som sendes til LINEST()-funksjonen for å utføre analyse ved hjelp av effektkoding. Resultatene av LINEST()-funksjonen er gitt i området E2:F6, der celle E2 viser regresjonskoeffisienten assosiert med støtvektoren. Celle E8 inneholder t-test = 0,93, og celle E9 tester påliteligheten til denne t-testen. Verdien i celle E9 indikerer at sannsynligheten for å møte forskjellen mellom gruppe betyr observert i dette eksperimentet, er 36 % hvis gruppemiddelet er like i befolkningen. Få anser dette resultatet for å være statistisk signifikant.

I fig. Figur 29 viser hva som skjer når du legger til en kovariat i analysen. I dette tilfellet la jeg til alderen til hvert emne i datasettet. Bestemmelseskoeffisienten R 2 for regresjonsligningen som bruker kovariaten er 0,80 (celle F4). R 2 -verdien i området F15:G19, der jeg replikerte ANOVA-resultatene oppnådd uten kovariatet, er bare 0,05 (celle F17). Derfor predikerer en regresjonsligning som inkluderer kovariaten verdier for den kognitive poengsum-variabelen mye mer nøyaktig enn å bruke effektvektoren alene. For ANCOVA er sannsynligheten tilfeldig kvittering F-testverdien som vises i celle F5 er mindre enn 0,01 %.

Ris. 29. ANCOVA bringer tilbake et helt annet bilde

Regresjonsanalyse er en av de mest populære metodene for statistisk forskning. Den kan brukes til å fastslå graden av påvirkning av uavhengige variabler på den avhengige variabelen. Microsoft Excel har verktøy utviklet for å utføre denne typen analyser. La oss se på hva de er og hvordan du bruker dem.

Koble til analysepakken

Men for å bruke funksjonen som lar deg utføre regresjonsanalyse, må du først aktivere analysepakken. Først da vil verktøyene som er nødvendige for denne prosedyren vises på Excel-båndet.

Flytt til "Fil"-fanen.
Gå til delen "Innstillinger".
Et vindu åpnes Excel-innstillinger. Gå til "Tillegg" underseksjonen.
Helt nederst i vinduet som åpnes, flytt bryteren i "Kontroll"-blokken til posisjonen " Excel-tillegg"hvis den er i en annen posisjon. Klikk på "Go"-knappen.
Et vindu med tilgjengelige Excel-tillegg åpnes. Merk av i boksen ved siden av "Analysepakke". Klikk på "OK"-knappen.

Nå, når vi går til fanen "Data", på båndet i verktøyblokken "Analyse" vil vi se en ny knapp - "Dataanalyse".

Typer regresjonsanalyse

Det finnes flere typer regresjoner:

parabolsk;
sedate;
logaritmisk;
eksponentiell;
demonstrativ;
hyperbolsk;
lineær regresjon.

Om utførelse siste type Vi vil snakke om regresjonsanalyse i Excel mer detaljert senere.

Lineær regresjon i Excel

Nedenfor er som eksempel en tabell som viser gjennomsnittlig daglig lufttemperatur ute og antall butikkkunder for tilsvarende arbeidsdag. La oss finne ut ved hjelp av regresjonsanalyse nøyaktig hvordan værforhold i form av lufttemperatur kan påvirke oppmøtet til en detaljhandel.

Den generelle lineære regresjonsligningen er som følger: Y = a0 + a1x1 +…+ akhk. I denne formelen betyr Y variabelen som vi prøver å studere påvirkning av faktorer. I vårt tilfelle er dette antallet kjøpere. Verdien av x er de ulike faktorene som påvirker variabelen. Parametrene a er regresjonskoeffisientene. Det vil si at det er de som bestemmer betydningen av en bestemt faktor. Indeksen k angir det totale antallet av disse samme faktorene.

Analyseresultater analyse

Resultatene av regresjonsanalysen vises i form av en tabell på stedet angitt i innstillingene.

En av hovedindikatorene er R-kvadrat. Det indikerer kvaliteten på modellen. I vårt tilfelle er denne koeffisienten 0,705 eller omtrent 70,5%. Dette er et akseptabelt kvalitetsnivå. Avhengighet mindre enn 0,5 er dårlig.

En annen viktig indikator er plassert i cellen i skjæringspunktet mellom raden "Y-kryss" og kolonnen "Koeffisienter". Dette indikerer hvilken verdi Y vil ha, og i vårt tilfelle er dette antall kjøpere, med alle andre faktorer lik null. I denne tabellen gitt verdi tilsvarer 58,04.

Verdien i skjæringspunktet mellom kolonnene "Variabel X1" og "Koeffisienter" viser nivået av avhengighet av Y på X. I vårt tilfelle er dette nivået av avhengighet av antall butikkkunder av temperatur. En koeffisient på 1,31 regnes som en ganske høy påvirkningsindikator.

Som vi kan se, bruker Microsoft-programmer Excel er ganske enkelt å lage en regresjonsanalysetabell. Men bare en trent person kan jobbe med utdataene og forstå essensen.

Vi er glade for at vi kunne hjelpe deg med å løse problemet.

Still spørsmålet ditt i kommentarene, og beskriv essensen av problemet i detalj. Våre spesialister vil prøve å svare så raskt som mulig.

Har denne artikkelen hjulpet deg?

Den lineære regresjonsmetoden lar oss beskrive en rett linje som passer best til en rekke ordnede par (x, y). Ligningen for en rett linje, kjent som den lineære ligningen, er gitt nedenfor:

ŷ - forventet verdi av y for en gitt verdi av x,

x - uavhengig variabel,

a - segment på y-aksen for en rett linje,

b er helningen til den rette linjen.

Figuren nedenfor illustrerer dette konseptet grafisk:

Figuren over viser linjen beskrevet av ligningen ŷ =2+0,5x. Y-skjæringspunktet er punktet der linjen skjærer y-aksen; i vårt tilfelle er a = 2. Linjens helning, b, forholdet mellom stigningen av linjen og lengden på linjen, har en verdi på 0,5. En positiv helning betyr at linjen stiger fra venstre til høyre. Hvis b = 0, er linjen horisontal, noe som betyr at det ikke er noen sammenheng mellom de avhengige og uavhengige variablene. Med andre ord, endring av verdien av x påvirker ikke verdien av y.

ŷ og y er ofte forvekslet. Grafen viser 6 ordnede par med punkter og en linje, i henhold til den gitte ligningen

Denne figuren viser punktet som tilsvarer det ordnede paret x = 2 og y = 4. Merk at forventet verdi av y i henhold til linjen ved X= 2 er ŷ. Vi kan bekrefte dette med følgende ligning:

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

Y-verdien representerer det faktiske punktet og ŷ-verdien er forventet verdi av y ved å bruke en lineær ligning for en gitt verdi av x.

Neste trinn er å bestemme den lineære ligningen som passer best med settet med ordnede par, vi snakket om dette i forrige artikkel, hvor vi bestemte formen til ligningen ved hjelp av minste kvadraters metode.

Bruke Excel til å definere lineær regresjon

For å bruke regresjonsanalyseverktøyet innebygd i Excel, må du aktivere tillegget Analysepakke. Du finner den ved å klikke på fanen Fil -> Alternativer(2007+), i dialogboksen som vises Alternativerutmerke gå til fanen Tillegg. I felt Kontroll velge Tilleggutmerke og klikk Gå. I vinduet som vises, merk av i boksen ved siden av Analysepakke, klikk OK.

I fanen Data i gruppe Analyse vil dukke opp ny knapp Dataanalyse.

For å demonstrere hvordan tillegget fungerer, la oss bruke data fra en tidligere artikkel, der en fyr og en jente deler et bord på badet. Skriv inn dataene fra vårt badekareksempel i kolonne A og B på det tomme arket.

Gå til fanen Data, i gruppe Analyse klikk Dataanalyse. I vinduet som vises Dataanalyse plukke ut Regresjon som vist i figuren og klikk OK.

Angi de nødvendige regresjonsparametrene i vinduet Regresjon, som det vises på bildet:

Klikk OK. Figuren nedenfor viser resultatene som er oppnådd:

Disse resultatene stemmer overens med de vi oppnådde ved å gjøre våre egne beregninger i forrige artikkel.

Regresjonsanalyse er en statistisk forskningsmetode som lar deg vise en bestemt parameters avhengighet av en eller flere uavhengige variabler. I tiden før datamaskinen var bruken ganske vanskelig, spesielt når det gjaldt store datamengder. I dag, etter å ha lært hvordan du bygger regresjon i Excel, kan du løse komplekse statistiske problemer på bare et par minutter. Nedenfor er spesifikke eksempler fra økonomifaget.

Typer regresjon

Dette konseptet i seg selv ble introdusert i matematikk av Francis Galton i 1886. Regresjon skjer:

lineær;
parabolsk;
sedate;
eksponentiell;
hyperbolsk;
demonstrativ;
logaritmisk.

Eksempel 1

La oss vurdere problemet med å bestemme avhengigheten av antall teammedlemmer som slutter gjennomsnittlig lønn ved 6 industribedrifter.

Oppgave. Ved seks virksomheter ble gjennomsnittlig månedslønn og antall ansatte som sluttet frivillig analysert. I tabellform har vi:

For oppgaven med å bestemme avhengigheten av antall sluttarbeidere av gjennomsnittslønnen ved 6 bedrifter, har regresjonsmodellen formen av ligningen Y = a0 + a1×1 +…+аkxk, hvor хi er de påvirkende variablene, ai er regresjonskoeffisientene, og k er antall faktorer.

For denne oppgaven er Y indikatoren på ansatte som slutter, og påvirkningsfaktoren er lønn, som vi betegner med X.

Bruke egenskapene til Excel-regnearkprosessoren

Regresjonsanalyse i Excel må innledes ved å bruke innebygde funksjoner på eksisterende tabelldata. For disse formålene er det imidlertid bedre å bruke det veldig nyttige tillegget "Analysis Pack". For å aktivere den trenger du:

fra "Fil"-fanen, gå til "Alternativer"-delen;
i vinduet som åpnes, velg linjen "Tillegg";
klikk på "Go"-knappen nedenfor, til høyre for "Management"-linjen;
merk av i boksen ved siden av navnet "Analysepakke" og bekreft handlingene dine ved å klikke "Ok".

Hvis alt er gjort riktig, vil den nødvendige knappen vises på høyre side av "Data"-fanen, som ligger over Excel-regnearket.

Lineær regresjon i Excel

Nå som du har alt du trenger for hånden virtuelle instrumenter for å utføre økonometriske beregninger, kan vi begynne å løse problemet vårt. For dette:

Klikk på "Dataanalyse"-knappen;
i vinduet som åpnes, klikk på "Regresjon" -knappen;
i fanen som vises, skriv inn verdiområdet for Y (antall som slutter) og for X (lønnen deres);
Vi bekrefter handlingene våre ved å trykke på "Ok"-knappen.

Som et resultat vil programmet automatisk fylles ut nytt løv bordprosessor regresjonsanalysedata. Merk! Excel lar deg manuelt angi plasseringen du foretrekker for dette formålet. For eksempel kan dette være det samme arket der Y- og X-verdiene er plassert, eller til og med en ny arbeidsbok som er spesielt utviklet for å lagre slike data.

Analyse av regresjonsresultater for R-kvadrat

I Excel-data innhentet under behandlingen av dataene i eksemplet under vurdering har formen:

Først av alt bør du ta hensyn til R-kvadratverdien. Den representerer bestemmelseskoeffisienten. I i dette eksemplet R-kvadrat = 0,755 (75,5 %), dvs. de beregnede parametrene til modellen forklarer avhengigheten mellom de vurderte parametrene med 75,5 %. Jo høyere verdi av bestemmelseskoeffisienten er, anses den valgte modellen som mer anvendelig for spesifikk oppgave. Det anses å korrekt beskrive den virkelige situasjonen når R-kvadratverdien er over 0,8. Hvis R-kvadrat er tcr, forkastes hypotesen om ubetydeligheten til frileddet til den lineære ligningen.

I oppgaven under vurdering for den frie termen, ved bruk av Excel-verktøy, ble det oppnådd at t = 169.20903, og p = 2.89E-12, det vil si at vi har null sannsynlighet for at den korrekte hypotesen om ubetydeligheten til frileddet vil bli forkastet . For koeffisienten for den ukjente t=5,79405, og p=0,001158. Sannsynligheten for at den korrekte hypotesen om insignifikansen av koeffisienten for en ukjent vil bli forkastet er med andre ord 0,12 %.

Dermed kan det hevdes at den resulterende lineære regresjonsligningen er tilstrekkelig.

Problemet med muligheten for å kjøpe en aksjeblokk

Multippel regresjon i Excel utføres ved hjelp av det samme dataanalyseverktøyet. La oss vurdere et spesifikt applikasjonsproblem.

Ledelsen i NNN-selskapet må ta stilling til om det er tilrådelig å kjøpe en 20% eierandel i MMM JSC. Kostnaden for pakken (SP) er 70 millioner amerikanske dollar. NNN-spesialister har samlet inn data om lignende transaksjoner. Det ble besluttet å evaluere verdien av aksjeblokken i henhold til slike parametere, uttrykt i millioner av amerikanske dollar, som:

leverandørgjeld (VK);
årlig omsetningsvolum (VO);
kundefordringer (VD);
kostnad for anleggsmidler (COF).

I tillegg brukes parameteren for bedriftens lønnsrestanser (V3 P) i tusenvis av amerikanske dollar.

Løsning ved hjelp av Excel-regnearkprosessor

Først av alt må du lage en tabell med kildedata. Det ser slik ut:

kall opp "Data Analysis"-vinduet;
velg "Regresjon"-delen;
I boksen "Input interval Y" skriver du inn verdiområdet til de avhengige variablene fra kolonne G;
klikk på det røde pilikonet til høyre for "Input Range X"-vinduet og marker på arket området for alle verdier fra kolonnene B,C,D,F.

Merk elementet "Nytt regneark" og klikk "OK".

Skaff en regresjonsanalyse for et gitt problem.

Studie av resultater og konklusjoner

Vi "samler inn" fra de avrundede dataene presentert ovenfor på tabellarket Excel-prosessor, regresjonsligning:

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

I en mer kjent matematisk form det kan skrives som:

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

Data for MMM JSC er presentert i tabellen:

Setter vi dem inn i regresjonsligningen, får vi et tall på 64,72 millioner amerikanske dollar. Dette betyr at aksjene til MMM JSC ikke er verdt å kjøpe, siden verdien på 70 millioner amerikanske dollar er ganske oppblåst.

Som du kan se, gjorde bruken av Excel-regnearket og regresjonsligningen det mulig å ta en informert beslutning om gjennomførbarheten av en veldig spesifikk transaksjon.

Nå vet du hva regresjon er. Excel-eksemplene diskutert ovenfor vil hjelpe deg med å bestemme praktiske problemer fra økonometrifeltet.

MS Excel-pakken lar deg gjøre det meste av arbeidet veldig raskt når du konstruerer en lineær regresjonsligning. Det er viktig å forstå hvordan de oppnådde resultatene skal tolkes. For å bygge en regresjonsmodell må du velge Verktøy\Dataanalyse\Regresjon (i Excel 2007 er denne modusen i Data/Dataanalyse/Regresjon-blokken). Kopier deretter resultatene til en blokk for analyse.

Endringen i den resulterende karakteristikken y skyldes variasjonen i faktorkarakteristikken x. Andelen av varians forklart av regresjon i den totale variansen til den resulterende karakteristikken karakteriserer bestemmelseskoeffisienten R 2. For et lineært forhold er bestemmelseskoeffisienten lik kvadratet av korrelasjonskoeffisienten:

R 2 = r xy 2 , hvor r xy er korrelasjonskoeffisienten.

For eksempel betyr verdien av R 2 = 0,83 at i 83 % av tilfellene fører endringer i x til endringer i y. Med andre ord er nøyaktigheten av å velge regresjonsligningen høy.

Beregnet for å vurdere kvaliteten på tilpasning av regresjonsligningen. For akseptable modeller foreslås det at bestemmelseskoeffisienten bør være større enn 50 %. Modeller med en bestemmelseskoeffisient over 80 % kan betraktes som ganske gode. Verdien av bestemmelseskoeffisienten R 2 = 1 betyr en funksjonell sammenheng mellom variablene.

Når ikke-lineær regresjon Bestemmelseskoeffisienten beregnes ved hjelp av denne kalkulatoren. Med multippel regresjon kan bestemmelseskoeffisienten finnes gjennom tjenesten Multippel regresjon
Generelt er bestemmelseskoeffisienten funnet av formelen: eller
Regel for å legge til avvik:
,
Hvor - totale mengden kvadratiske avvik;
- summen av kvadrerte avvik på grunn av regresjon ("forklart" eller "faktoriell");
- Restsum av kvadrerte avvik.

Ved å bruke denne online kalkulatoren kan du beregne bestemmelseskoeffisient og dens betydning kontrolleres (Eksempel på løsning).

Bruksanvisning. Spesifiser mengden inndata. Den resulterende løsningen lagres i Word-fil. Det lages også automatisk en mal for å teste løsningen i Excel.