I statistisk analys, den variation bland medlemmar i en dataset visar hur långt ifrån varandra datapunkterna är från en trendlinje, även känd som a regressionslinjen. Ju högre variansen desto mer spridda datapunkterna är. Studien av variansanalysen visar vilka delar av variansen som kan förklaras av dataens egenskaper, och som kan hänföras till slumpmässiga faktorer. Den del av variansen som inte kan förklaras kallas restvariationen.
Använda Excel-kalkylblad för att beräkna återstående varians
Formeln för att beräkna restvariationen innefattar många komplexa beräkningar. För små dataset kan processen för beräkning av restvariationen för hand vara tråkig. För stora dataset kan uppgiften vara ansträngande. Genom att använda ett Excel-kalkylblad behöver du bara ange datapunkterna och välja rätt formel. Programmet hanterar de komplexa beräkningarna och ger ett resultat snabbt.
Datapunkter
Öppna ett nytt Excel-kalkylblad och ange datapunkterna i två kolumner. Regressionslinjer kräver att varje datapunkt har två element. Statistiker märker typiskt dessa element "X" och "Y." Till exempel vill Generic Insurance Co. hitta den resterande variansen av höjden och vikten hos sina anställda. X-variabeln representerar höjden och Y-variabeln representerar vikten. Ange höjderna i kolumn A och vikterna i kolumn B.
Hitta medelvärdet
De betyda representerar medelvärdet för varje element i datasatsen. I detta exempel vill Generic Insurance hitta medelvärdet, standardavvikelsen och kovariansen på 10 anställda höjder och vikter. Medelvärdet av de höjder som anges i kolumn A kan hittas genom att ange funktionen "= AVERAGE (A1: A10)" i cell F1. Medelvärdet av de vikter som anges i kolumn B kan hittas genom att ange funktionen "= AVERAGE (B1: B10)" i cell F3.
Hitta standardavvikelsen och Covariance
De standardavvikelse mäter hur långt ifrån varandra datapunkter sprids från medelvärdet. De kovarians mäter hur mycket datapunktens två delar ändras tillsammans. Standardavvikelsen för höjderna hittas genom att mata in funktionen "= STDEV (A1: A10)" i cell F2. Standardvikten för vikterna hittas genom att mata in funktionen "= STDEV (B1: B10)" i cell F4. Kovariansen mellan höjder och vikter hittas genom att mata in funktionen "= COVAR (A1: A10; B1: B10)" i cell F5.
Hitta regressionslinjen
De regressionslinjen representerar en linjär funktion som följer utvecklingen av datapunkterna. Formeln för regressionslinjen ser så här ut: Y = aX + b.
Användaren kan hitta värdena för "a" och "b" med hjälp av beräkningarna för medel, standardavvikelser och kovarians. Värdet för "b" representerar den punkt där regressionslinjen avlyssnar Y-axeln. Värdet kan hittas genom att ta kovariansen och dela den med kvadraten av standardavvikelsen för X-värdena. Excel-formeln går in i cell F6 och ser så här ut: = F5 / F2 ^ 2.
Värdet för "a" representerar lutningen på regressionslinjen. Excel-formeln går in i cell F7 och ser så här ut: = F3-F6 * F1.
För att se formeln för regressionsraden, ange den här strängkonkatationen i cell F8:
= CONCATENATE ("Y ="; RUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Beräkna Y-värden
Nästa steg innebär att beräkna Y-värdena på regressionslinjen för de givna X-värdena i datamängden. Formeln för att hitta Y-värdena går in i kolumn C och ser så här ut:
= $ F $ 6 * A (i) + $ F $ 7
Där A (i) är värdet för kolumn A i rad (i). Formlerna ser så här ut i kalkylbladet:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, och så vidare
Posterna i kolumn D visar skillnaderna mellan de förväntade och faktiska värdena för Y. Formlerna ser så här ut:
= B (i) -C (i), Där B (i) och C (i) är värdena i rad (i) i kolumnerna B respektive C.
Hitta restvariationen
De formel för restvariation går in i cell F9 och ser så här ut:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Där SUMSQ (D1: D10) är summan av kvadraterna för skillnaderna mellan de faktiska och förväntade Y-värdena och (COUNT (D1: D10) -2) är antalet datapunkter, minus 2 för frihetsgrader i data.
