En el análisis estadístico, la diferencia entre los miembros de un conjunto de datos muestra qué tan separados están los puntos de datos de una línea de tendencia, también conocida como línea de regresión. Cuanto mayor es la varianza, más dispersos están los puntos de datos. El estudio del análisis de varianza muestra qué partes de la varianza pueden explicarse por las características de los datos y cuáles pueden atribuirse a factores aleatorios. La porción de la varianza que no se puede explicar se llama la varianza residual.
Uso de hojas de cálculo de Excel para calcular la variación residual
La fórmula para calcular la varianza residual implica numerosos cálculos complejos. Para conjuntos de datos pequeños, el proceso de calcular la variación residual a mano puede ser tedioso. Para grandes conjuntos de datos, la tarea puede ser agotadora. Al utilizar una hoja de cálculo de Excel, solo necesita ingresar los puntos de datos y seleccionar la fórmula correcta. El programa maneja los cálculos complejos y entrega un resultado rápidamente.
Puntos de datos
Abra una nueva hoja de cálculo de Excel e ingrese los puntos de datos en dos columnas. Las líneas de regresión requieren que cada punto de datos tenga dos elementos. Los estadísticos suelen etiquetar estos elementos como "X" e "Y". Por ejemplo, Generic Insurance Co. desea encontrar la variación residual de la altura y el peso de sus empleados. La variable X representa la altura y la variable Y representa el peso. Introduzca las alturas en la columna A y los pesos en la columna B.
Encontrar la media
los media representa el promedio de cada elemento en el conjunto de datos. En este ejemplo, Generic Insurance quiere encontrar el promedio, la desviación estándar y la covarianza de 10 alturas y pesos de los empleados. El promedio de las alturas enumeradas en la Columna A se puede encontrar ingresando la función "= MEDIA (A1: A10)" en la celda F1. El promedio de los pesos enumerados en la Columna B se puede encontrar ingresando la función "= MEDIA (B1: B10)" en la celda F3.
Encontrar la desviación estándar y la covarianza
los desviación estándar mide la distancia entre los puntos de datos y la propagación de la media. los covarianza mide cuánto cambian juntos los dos elementos del punto de datos. La desviación estándar de las alturas se encuentra ingresando la función "= STDEV (A1: A10)" en la celda F2. La desviación estándar de los pesos se encuentra ingresando la función "= STDEV (B1: B10)" en la celda F4. La covarianza entre las alturas y los pesos se encuentra ingresando la función "= COVAR (A1: A10; B1: B10)" en la celda F5.
Encontrando la línea de regresión
los línea de regresión Representa una función lineal que sigue la tendencia de los puntos de datos. La fórmula para la línea de regresión se ve así: Y = aX + b.
El usuario puede encontrar los valores de "a" y "b" utilizando los cálculos de los medios, las desviaciones estándar y la covarianza. El valor para "b" representa el punto donde la línea de regresión intercepta el eje Y. El valor se puede encontrar tomando la covarianza y dividiéndola por el cuadrado de la desviación estándar de los valores de X. La fórmula de Excel va a la celda F6 y se ve así: = F5 / F2 ^ 2.
El valor para "a" representa la pendiente de la línea de regresión. La fórmula de Excel va a la celda F7 y se ve así: = F3-F6 * F1.
Para ver la fórmula de la línea de regresión, ingrese esta concatenación de cadenas en la celda F8:
= CONCATENADO ("Y ="; RONDA (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (RONDA (F7; 2)))
Calcular los valores de Y
El siguiente paso consiste en calcular los valores Y en la línea de regresión para los valores X dados en el conjunto de datos. La fórmula para encontrar los valores de Y entra en la columna C y se ve así:
= $ F $ 6 * A (i) + $ F $ 7
Donde A (i) es el valor para la columna A en la fila (i). Las fórmulas se ven así en la hoja de cálculo:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, y así sucesivamente
Las entradas en la Columna D muestran las diferencias entre los valores esperados y los valores reales para Y. Las fórmulas se ven así:
= B (i) -C (i), Donde B (i) y C (i) son los valores en la Fila (i) en las Columnas B y C, respectivamente.
Encontrar la variación residual
los fórmula para la varianza residual Entra en la celda F9 y se ve así:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Donde SUMSQ (D1: D10) es la suma de los cuadrados de las diferencias entre los valores de Y reales y esperados, y (COUNT (D1: D10) -2) es el número de puntos de datos, menos 2 para los grados de libertad en el datos.
