I statistisk analyse, den varians blandt medlemmer af et datasæt viser, hvor langt fra hinanden datapunkterne er fra en trendlinie, også kendt som a regressionslinje. Jo højere variansen er, desto mere spredt datapunkterne er. Undersøgelsen af variansanalysen viser, hvilke dele af variansen der kan forklares ved dataens egenskaber, og som kan tilskrives tilfældige faktorer. Den del af variansen, som ikke kan forklares, kaldes den resterende varians.
Brug Excel-regneark til at beregne restvariant
Formlen til beregning af restvariant indebærer talrige komplekse beregninger. For små datasæt kan processen med at beregne den resterende varians for hånd være kedelig. For store datasæt kan opgaven være udmattende. Ved at bruge et Excel-regneark skal du kun indtaste datapunkterne og vælge den rigtige formel. Programmet håndterer de komplekse beregninger og leverer et resultat hurtigt.
Datapunkter
Åbn et nyt Excel-regneark og indtast datapunkterne i to kolonner. Regressionslinjer kræver, at hvert datapunkt har to elementer. Statistikere mærker typisk disse elementer "X" og "Y." For eksempel ønsker Generic Insurance Co. at finde den resterende varians af medarbejdernes højde og vægt. X-variablen repræsenterer højden, og Y-variablen repræsenterer vægten. Indtast højderne i kolonne A og vægten i kolonne B.
Find den gennemsnitlige
Det betyde repræsenterer gennemsnittet for hvert element i datasættet. I dette eksempel ønsker Generic Insurance at finde gennemsnittet, standardafvigelsen og kovariansen på 10 medarbejderes højder og vægte. Gennemsnittet af de højder, der er anført i kolonne A, findes ved at indtaste funktionen "= AVERAGE (A1: A10)" i celle F1. Gennemsnittet af vægten i kolonne B kan findes ved at indtaste funktionen "= AVERAGE (B1: B10)" i celle F3.
Find standardafvigelsen og Covariance
Det standardafvigelse måler, hvor langt væk datapunkterne spredes fra gennemsnittet. Det kovarians måler, hvor meget de to elementer i datapunktet ændres sammen. Standardafvigelsen af højderne findes ved at indtaste funktionen "= STDEV (A1: A10)" i celle F2. Standardafvigelsen af vægten findes ved at indtaste funktionen "= STDEV (B1: B10)" i celle F4. Kovariansen mellem højder og vægte findes ved at indtaste funktionen "= COVAR (A1: A10; B1: B10)" i celle F5.
Finde regressionslinjen
Det regressionslinje repræsenterer en lineær funktion, der følger udviklingen i datapunkterne. Formlen for regressionslinjen ser sådan ud: Y = aX + b.
Brugeren kan finde værdierne for "a" og "b" ved at bruge beregningerne for midlerne, standardafvigelserne og kovariansen. Værdien for "b" repræsenterer det punkt, hvor regressionslinjen afbryder Y-aksen. Værdien kan findes ved at tage kovariansen og dividere den ved kvadratet af standardafvigelsen af X-værdierne. Excel-formlen går ind i celle F6 og ser sådan ud: = F5 / F2 ^ 2.
Værdien for "a" repræsenterer hældningen af regressionslinjen. Excel-formlen går ind i celle F7 og ser sådan ud: = F3-F6 * F1.
For at se formlen for regressionslinjen skal du indtaste denne strengforening i celle F8:
= CONCATENATE ("Y ="; RUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (RUND (F7; 2)))
Beregn Y-værdier
Det næste trin involverer beregning af Y-værdierne på regressionslinjen for de givne X-værdier i datasættet. Formlen til at finde Y-værdierne går ind i kolonne C og ser sådan ud:
= $ F $ 6 * A (i) + $ F $ 7
Hvor A (i) er værdien for kolonne A i række (i). Formlerne ser sådan ud i regnearket:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, og så videre
Indtastningerne i kolonne D viser forskellene mellem de forventede og faktiske værdier for Y. Formlerne ser sådan ud:
= B (i) -C (i), Hvor B (i) og C (i) er værdierne i række (i) i kolonne B og C.
Find den resterende variant
Det formel for restvariation går ind i Cell F9 og ser sådan ud:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Hvor SUMSQ (D1: D10) er summen af kvadraterne af forskellene mellem de faktiske og forventede Y-værdier, og (COUNT (D1: D10) -2) er antallet af datapunkter, minus 2 for frihedsgrader i data.
