I statistisk analyse, den varians blant medlemmer av et datasett viser hvor langt fra hverandre datapunktene er fra en trendlinje, også kjent som a regresjonslinje. Jo høyere variansen er, jo mer spredt datapunktene er. Studien av variansanalysen viser hvilke deler av variansen som kan forklares av egenskapene til dataene, og som kan tilskrives tilfeldige faktorer. Delen av variansen som ikke kan forklares kalles restvarianten.
Bruke Excel-regneark for å beregne gjenværende variasjon
Formelen for å beregne gjenværende varians innebærer en rekke komplekse beregninger. For små datasett kan prosessen med å beregne gjenværende variansen for hånd være kjedelig. For store datasett kan oppgaven være utmattende. Ved å bruke et Excel-regneark, trenger du bare å skrive inn datapunktene og velge riktig formel. Programmet håndterer de komplekse beregningene og gir et resultat raskt.
Datapunkter
Åpne et nytt Excel-regneark og skriv inn datapunktene i to kolonner. Regresjonslinjer krever at hvert datapunkt har to elementer. Statistikere merker vanligvis disse elementene "X" og "Y." For eksempel ønsker Generic Insurance Co. å finne den resterende variansen av høyden og vekten til sine ansatte. X-variabelen representerer høyden, og Y-variabelen representerer vekten. Skriv inn høydene i kolonne A og vekter i kolonne B.
Finne Mean
De mener representerer gjennomsnittet for hvert element i datasettet. I dette eksemplet ønsker Generic Insurance å finne gjennomsnittet, standardavviket og kovariansen på 10 medarbeiders høyder og vekter. Gjennomsnittet av høyder oppført i kolonne A kan bli funnet ved å skrive inn funksjonen "= AVERAGE (A1: A10)" i celle F1. Gjennomsnittet av vektene som er oppført i kolonne B kan bli funnet ved å skrive inn funksjonen "= AVERAGE (B1: B10)" i celle F3.
Finne Standard Avvik og Covariance
De standardavvik måler hvor langt fra hverandre datapunktene er spredt fra gjennomsnittet. De kovarians måler hvor mye de to elementene i datapunktet endres sammen. Standardavviket til høydene er funnet ved å skrive inn funksjonen "= STDEV (A1: A10)" i celle F2. Standardavviket av vekter er funnet ved å skrive inn funksjonen "= STDEV (B1: B10)" i celle F4. Kovariansen mellom høyder og vekter er funnet ved å skrive inn funksjonen "= COVAR (A1: A10; B1: B10)" i celle F5.
Finne regresjonslinjen
De regresjonslinje representerer en lineær funksjon som følger utviklingen av datapunktene. Formelen for regresjonslinjen ser slik ut: Y = aX + b.
Brukeren kan finne verdiene for "a" og "b" ved å bruke beregningene for middel, standardavvik og kovarians. Verdien for "b" representerer punktet der regresjonslinjen avskjærer Y-aksen. Verdien kan bli funnet ved å ta kovariansen og dividere den ved kvadratet av standardavviket til X-verdiene. Excel-formelen går inn i celle F6 og ser slik ut: = F5 / F2 ^ 2.
Verdien for "a" representerer helling av regresjonslinjen. Excel-formelen går inn i celle F7 og ser slik ut: = F3-F6 * F1.
For å se formelen for regresjonslinjen, skriv inn denne strengkonsentrasjonen i celle F8:
= CONCATENATE ("Y ="; RUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Beregn Y-verdier
Det neste trinnet omfatter å beregne Y-verdiene på regresjonslinjen for de oppgitte X-verdiene i datasettet. Formelen for å finne Y-verdiene går inn i kolonne C og ser slik ut:
= $ F $ 6 * A (i) + $ F $ 7
Hvor A (i) er verdien for Kolonne A i rad (i). Formlene ser slik ut i regnearket:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, og så videre
Oppføringene i kolonne D viser forskjellene mellom de forventede og faktiske verdiene for Y. Formlene ser slik ut:
= B (i) -C (i), Hvor B (i) og C (i) er verdiene i rad (i) i kolonne B og C.
Finne gjenværende variasjon
De formel for restvariasjon går inn i Cell F9 og ser slik ut:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Hvor SUMSQ (D1: D10) er summen av kvadratene av forskjellene mellom de faktiske og forventede Y-verdiene, og (COUNT (D1: D10) -2) er antall datapunkter, minus 2 for frihetsgrader i data.
