En l 'anàlisi estadística, el desacord entre els membres d'un conjunt de dades es mostra quina diferència hi ha entre els punts de dades d'una línia de tendència, també coneguda com a línia de regressió. Com més alta sigui la variància, més es troben els punts de dades. L'estudi de l'anàlisi de la variància mostra quines parts de la variància es poden explicar per les característiques de les dades i que es poden atribuir a factors aleatoris. La part de la variància que no es pot explicar s'anomena variància residual.
Ús de fulls de càlcul d'Excel per calcular la variació residual
La fórmula per calcular la variància residual implica nombrosos càlculs complexos. Per a petits conjunts de dades, el procés de calcular la variància residual a mà pot ser tediós. Per als conjunts de dades grans, la tasca pot ser esgotadora. Si feu servir un full de càlcul Excel, només heu d'introduir els punts de dades i seleccionar la fórmula correcta. El programa gestiona els càlculs complexos i proporciona un resultat ràpidament.
Punts de dades
Obriu un nou full de càlcul d'Excel i introduïu els punts de dades en dues columnes. Les línies de regressió requereixen que cada punt de dades tingui dos elements. Normalment, els estadístics etiqueten aquests elements "X" i "Y". Per exemple, Generic Insurance Co. vol trobar la variància residual de l'alçada i el pes dels seus empleats. La variable X representa l'alçada i la variable Y representa el pes. Introduïu les altures a la columna A i els pesos a la columna B.
Trobar la mitjana
El significar representa la mitjana de cada element del conjunt de dades. En aquest exemple, l'assegurança genèrica vol trobar la mitjana, la desviació estàndard i la covariància de les altures i pesos de 10 treballadors. La mitjana de les altures enumerades a la columna A es pot trobar introduint la funció "= MITJANA (A1: A10)" a la cel·la F1. La mitjana dels pesos que figuren a la columna B es pot trobar introduint la funció "= MITJANA (B1: B10)" a la cel·la F3.
Trobar la desviació estàndard i la covariança
El desviació estàndar mesura quina diferència hi ha entre els punts de dades que s'estenen des de la mitjana. El covariància mesura quant varien els dos elements del punt de dades. La desviació estàndard de les altures es troba introduint la funció "= STDEV (A1: A10)" a la cel·la F2. La desviació estàndard dels pesos es troba introduint la funció "= STDEV (B1: B10)" a la cel·la F4. La covariància entre altures i pesos es troba introduint la funció "= COVAR (A1: A10; B1: B10)" a la cel·la F5.
Trobar la línia de regressió
El línia de regressió representa una funció lineal que segueix la tendència dels punts de dades. La fórmula de la línia de regressió és així: Y = aX + b.
L'usuari pot trobar els valors de "a" i "b" mitjançant els càlculs dels mitjans, les desviacions estàndard i la covariança. El valor de "b" representa el punt en què la línia de regressió intercepta l'eix Y. El valor es pot trobar prenent la covariància i dividint-lo pel quadrat de la desviació estàndard dels valors X. La fórmula d'Excel entra a la cel·la F6 i es veu així: = F5 / F2 ^ 2.
El valor de "a" representa el pendent de la línia de regressió. La fórmula d'Excel entra a la cel·la F7 i es veu així: = F3-F6 * F1.
Per veure la fórmula de la línia de regressió, introduïu aquesta concatenació de cadenes a la cel·la F8:
= CONCATENAT ("Y ="; RONDA (F6; 2); "X"; SI (SIGN (F7) = 1; "+"; "-"); ABS (RONDA (F7; 2)))
Calcula els valors de Y
El següent pas implica el càlcul dels valors Y de la línia de regressió dels valors X donats en el conjunt de dades. La fórmula per trobar els valors de Y entra a la columna C i es veu així:
= $ F $ 6 * A (i) + $ F $ 7
On A (i) és el valor de la columna A de la fila (i). Les fórmules es veuen així en el full de càlcul:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, i així successivament
Les entrades de la columna D mostren les diferències entre els valors esperats i els valors reals de Y. Les fórmules són així:
= B (i) -C (i), On B (i) i C (i) són els valors de Fila (i) a les columnes B i C, respectivament.
Trobar la variància residual
El fórmula per a la variància residual entra a la cel·la F9 i es veu així:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
On SUMSQ (D1: D10) és la suma dels quadrats de les diferències entre els valors de Y reals i esperats, i (COUNT (D1: D10) -2) és el nombre de punts de dades, menys 2 per graus de llibertat a la dades.
