Hvordan gjøre regresjonsanalyse i Excel. Regresjonsanalyse i excel
Regresjonsanalyse V Microsoft Excel- mest komplette guider om bruk av MS Excel til å løse regresjonsanalyseproblemer innen forretningsanalyse. Konrad Carlberg forklarer tydelig teoretiske problemstillinger, hvis kunnskap vil hjelpe deg med å 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 det sanne gjennomsnittet. 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 få 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. Eksistere forskjellige måter beregning av denne mengden, selv om alle involverer 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) – ja Kvadratrot fra spredning:
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. Hvilket er mer 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 varianter standard feil, inkludert standard målefeil, standard proporsjonsfeil, standard feil for gjennomsnittet.
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 bruken 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; Klikk på det for å forstørre bildet 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 totale variabiliteten til dataene er gitt 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 forklart varians. 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:C12;B3:B12), trykk og hold nede tastene
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, og eliminere innflytelsen 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) til 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 ligning i det hele tatt. multippel regresjon, 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
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);
- standardfeil for 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 dens forhold 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 R2 uttrykker andelen totale mengden kvadrater assosiert med regresjon. 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 å bruke 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)
På samme måte for 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 til 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 med enkelt 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 er sannsynligheten for at du aksepterer riktig løsning, å avvise hypotesen om en null regresjonskoeffisient i populasjonen, vil 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 fullstendige 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 tilfellet, selv om det sikkert er 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 lineært avhengige av 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 øvre og nedre grense for prediktorvariabelen lineær regresjon fører til urealistiske prognoser.
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.
I 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 tilgjengelige forskjellige former en funksjon 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 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 kontrollgruppen). 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 sammenhengen mellom utfallsvariabelen og to eller flere faktorer samtidig studeres, snakker vi i dette tilfellet om å bruke faktor analyse. Å 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 restmiddelkvadrat. Men det kan godt hende at denne variasjonen har sammenheng med en annen faktor. Deretter kan denne variasjonen fjernes fra den gjennomsnittlige kvadratfeilen, 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 kovarians, 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 jo 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 tilordnes en del av variabiliteten tidligere tilskrevet feiltermen til 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 % dersom 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
Det er kjent for å være nyttig i ulike aktivitetsfelt, inkludert en slik disiplin som økonometri, hvor dette programvareverktøyet brukes i arbeid. I utgangspunktet er alle handlinger praktiske og laboratorieklasser utført i Excel, noe som i stor grad letter arbeidet, gir detaljerte forklaringer visse handlinger. Dermed brukes et av analyseverktøyene "Regresjon" for å velge en graf for et sett med observasjoner ved bruk av minste kvadraters metode. La oss se på hva det er dette verktøyet programmet og hva er dets fordeler for brukerne. Nedenfor er også et kort men klare instruksjoner konstruksjon regresjonsmodell.
Hovedoppgaver og typer regresjon
Regresjon representerer forholdet mellom gitte variabler, og gjør det dermed mulig å forutsi den fremtidige oppførselen til disse variablene. Variabler er forskjellige periodiske fenomener, inkludert menneskelig atferd. Denne Excel-analysen brukes til å analysere virkningen på en spesifikk avhengig verdivariabel en eller flere variabler. For eksempel påvirkes salget i en butikk av flere faktorer, inkludert sortiment, priser og beliggenhet til butikken. Takket være regresjon i Excel, kan du bestemme graden av påvirkning av hver av disse faktorene basert på resultatene av eksisterende salg, og deretter bruke dataene som er oppnådd for å forutsi salg for en annen måned eller for en annen butikk i nærheten.
Vanligvis presenteres regresjon som en enkel ligning som avslører sammenhengene og styrken til sammenhenger mellom to grupper av variabler, der en gruppe er avhengig eller endogen og den andre er uavhengig eller eksogen. Hvis det er en gruppe med innbyrdes beslektede indikatorer, bestemmes den avhengige variabelen Y basert på resonnementets logikk, og resten fungerer som uavhengige X-variabler.
Hovedoppgavene for å bygge en regresjonsmodell er som følger:
- Utvalg av signifikante uavhengige variabler (X1, X2, ..., Xk).
- Velge type funksjon.
- Konstruere estimater for koeffisienter.
- Konstruksjon av konfidensintervaller og regresjonsfunksjoner.
- Kontroll av betydningen av de beregnede estimatene og den konstruerte regresjonsligningen.
Det finnes flere typer regresjonsanalyse:
- paret (1 avhengig og 1 uavhengig variabel);
- multiple (flere uavhengige variabler).
Det er to typer regresjonsligninger:
- Lineær, illustrerende streng lineær forbindelse mellom variabler.
- Ikke-lineær - ligninger som kan inkludere potenser, brøker og trigonometriske funksjoner.
Instruksjoner for å bygge en modell
For å utføre en gitt konstruksjon i Excel, må du følge instruksjonene:
For ytterligere beregning, bruk "Linear()"-funksjonen, som spesifiserer Y-verdier, X-verdier, Konst og statistikk. Etter dette bestemmer du settet med punkter på regresjonslinjen ved å bruke "Trend"-funksjonen - Y-verdier, X-verdier, nye verdier, Konst. Bruk de gitte parameterne, beregne den ukjente verdien av koeffisientene basert på gitte forhold tildelt oppgave.
Viser påvirkningen av noen verdier (uavhengig, uavhengig) på den avhengige variabelen. For eksempel, hvordan avhenger antall økonomisk aktive befolkning av antall bedrifter, lønn og andre parametere. Eller: hvordan påvirker utenlandske investeringer, energipriser osv. nivået på BNP.
Resultatet av analysen lar deg fremheve prioriteringer. Og basert på hovedfaktorene, forutsi og planlegge utvikling prioriterte områder, ta ledelsesbeslutninger.
Regresjon skjer:
lineær (y = a + bx);
· parabolsk (y = a + bx + cx 2);
· eksponentiell (y = a * exp(bx));
· potens (y = a*x^b);
· hyperbolsk (y = b/x + a);
logaritmisk (y = b * 1n(x) + a);
· eksponentiell (y = a * b^x).
La oss se på et eksempel på å bygge en regresjonsmodell i Excel og tolke resultatene. La oss ta lineær type regresjon.
Oppgave. Ved 6 virksomheter ble gjennomsnittlig månedslønn og antall sluttarbeidere analysert. Det er nødvendig å bestemme avhengigheten av antall sluttansatte på gjennomsnittslønnen.
Den lineære regresjonsmodellen ser slik ut:
Y = a 0 + a 1 x 1 +...+a k x k.
Der a er regresjonskoeffisienter, x er påvirkende variabler, er k antall faktorer.
I vårt eksempel er Y indikatoren på å slutte med ansatte. Påvirkningsfaktoren er lønn (x).
Excel har innebygde funksjoner som kan hjelpe deg med å beregne parametrene til en lineær regresjonsmodell. Men tillegget "Analysis Package" vil gjøre dette raskere.
Vi aktiverer et kraftig analyseverktøy:
1. Klikk på "Office"-knappen og gå til ""-fanen Excel-alternativer" "Tillegg".
2. Nederst, under rullegardinlisten, i feltet "Administrasjon" vil det være påskriften " Excel-tillegg» (hvis den ikke er der, klikk på avmerkingsboksen til høyre og velg). Og "Go"-knappen. Klikk.
3. En liste over tilgjengelige tillegg åpnes. Velg "Analysepakke" og klikk OK.
Når det er aktivert, vil tillegget være tilgjengelig i fanen Data.
La oss nå gjøre selve regresjonsanalysen.
1. Åpne menyen til "Data Analysis"-verktøyet. Velg "Regresjon".
2. En meny åpnes for å velge inngangsverdier og utdataalternativer (hvor resultatet skal vises). I feltene for de første dataene angir vi rekkevidden til parameteren som beskrives (Y) og faktoren som påvirker den (X). Resten kan ikke fylles ut.
3. Etter å ha klikket OK, vil programmet vise beregningene på et nytt ark (du kan velge et intervall som skal vises på gjeldende ark eller tilordne utdata til en ny arbeidsbok).
Først av alt tar vi hensyn til R-kvadrat og koeffisienter.
R-kvadrat er bestemmelseskoeffisienten. I vårt eksempel - 0,755, eller 75,5%. Dette betyr at de beregnede parameterne til modellen forklarer 75,5 % av sammenhengen mellom de studerte parameterne. Jo høyere bestemmelseskoeffisient, jo bedre kvalitetsmodell. Bra - over 0,8. Dårlig – mindre enn 0,5 (en slik analyse kan neppe anses som rimelig). I vårt eksempel - "ikke dårlig".
Koeffisienten 64.1428 viser hva Y vil være hvis alle variabler i modellen som vurderes er lik 0. Det vil si at verdien av den analyserte parameteren også påvirkes av andre faktorer som ikke er beskrevet i modellen.
Koeffisienten -0,16285 viser vekten av variabel X på Y. Det vil si at gjennomsnittlig månedslønn innenfor denne modellen påvirker antall sluttere med en vekt på -0,16285 (dette er en liten grad av påvirkning). "-"-tegnet indikerer en negativ innvirkning: jo høyere lønn, jo færre slutter. Som er rettferdig.
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 å trykke på knappen til venstre øverste hjørne, og deretter knappen 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 vises 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 for 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 det 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 sin egen 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 tilsvarende .
7. Blokker Uttak av saldo inneholder de anslåtte verdiene y(i vår notasjon er dette ) og rester .
KORELLASJON OG REGRESJONSANALYSE IMS UTMERKE
1. Lag en kildedatafil i MS Excel (for eksempel tabell 2)
2. Konstruksjon av korrelasjonsfeltet
Å konstruere et korrelasjonsfelt i kommandolinje velg meny Sett inn/Diagram. Velg diagramtypen i dialogboksen som vises: Få øye på; utsikt: Spredningsplott, slik at du kan sammenligne verdipar (fig. 22).
Figur 22 – Velge en diagramtype
Figur 23– Vindusvisning når du velger et område og rader
Figur 25 – Vindusvisning, trinn 4
2. Velg kommandoen i kontekstmenyen Legg til en trendlinje.
3. I dialogboksen som vises, velg graftypen (lineær i vårt eksempel) og ligningsparametrene, som vist i figur 26.
Klikk OK. Resultatet er presentert i figur 27.
Figur 27 – Korrelasjonsfelt for arbeidsproduktivitets avhengighet av kapital-arbeidsforhold
På samme måte konstruerer vi et korrelasjonsfelt for arbeidsproduktivitetens avhengighet av utstyrsskiftforholdet. (Figur 28).
 |
Figur 28 – Korrelasjonsfelt for arbeidsproduktivitet
på utstyrsutskiftningsraten
3. Konstruksjon av korrelasjonsmatrisen.
For å bygge en korrelasjonsmatrise i menyen Service velge Dataanalyse.
Ved hjelp av et dataanalyseverktøy Regresjon, i tillegg til resultatene av regresjonsstatistikk, analyse av varians og konfidensintervaller, kan du få residualer og grafer for tilpasning av regresjonslinjen, residualer og normal sannsynlighet. For å gjøre dette må du sjekke tilgangen til analysepakken. I hovedmenyen velger du Service/Tillegg. Merk av i boksen Analysepakke(Figur 29)
Figur 30 – Dialogboks Dataanalyse
Etter å ha klikket OK, i dialogboksen som vises, spesifiser inndataintervallet (i vårt eksempel A2:D26), gruppering (i vårt tilfelle etter kolonner) og utdataparametere, som vist i figur 31.
 |
Figur 31 – Dialogboks Sammenheng
Beregningsresultatene er presentert i tabell 4.
Tabell 4 – Korrelasjonsmatrise
Kolonne 1 | Kolonne 2 | Kolonne 3 |
|
Kolonne 1 | |||
Kolonne 2 | |||
Kolonne 3 |
ENFAKTOR REGRESJONSANALYSE
BRUKE REGRESJONSVERKTØYET
Å gjennomføre en regresjonsanalyse av arbeidsproduktivitetens avhengighet av kapital-arbeidsforholdet i menyen Service velge Dataanalyse og spesifiser analyseverktøyet Regresjon(Figur 32).
Figur 33 – Dialogboks Regresjon