Vamos a explicar como llevar a cabo una regresión lineal utilizando las funciones que para ello proporciona Excel. Para ello vamos a resolver, a modo de ejemplo, el problema siguiente (tomado de un examen):
A fin de estudiar la relación entre el peso de los Yorkshire Terrier adultos y su altura a la cruz, se pesaron y midieron un grupo de ejemplares, obteniéndose los datos mostrados a continuación
Determinar la recta que mejor ajuste los datos. Calcula además, el coeficiente de correlación lineal así como las medias, varianzas y desviaciones típicas marginales y la covarianza de los datos.
Comenzaremos introduciendo los datos en nuestra hoja Excel y anotando los cálculos que hemos de realizar. Nos quedará algo similar a lo que vemos en la imagen.
 |
| Fuente: Elaboración propia. |
Para calcular las medias de ambas variables vamos a utilizar la función PROMEDIO de Excel. Nos situamos en E3 e introducimos la formula; como muestra la imagen no hay más que comenzar con un signo "=", escribir "promedio(" y seleccionar el rango de datos correspondiente a los valores del peso. Finalizamos pulsando la tecla "Entrar" y obtenemos el resultado.
 |
| Fuente: Elaboración propia. |
Repetiremos la operación para obtener la media de las altura (celda E8) y después, obtendremos la varianza para lo que utilizaremos la función VAR.P. Esta función calcula la varianza poblacional de un conjunto de datos. Simplemente nos colocaremos en la celda correspondiente, en nuestro caso E4 y procedemos a escribir la fórmula, "=var.p(". Seleccionamos el rango de los datos, A3:A14 y pulsamos "Entrar", obteniendo el resultado.
 |
| Fuente: Elaboración propia. |
Igual que antes, repetimos la operación para los datos de la altura, en la celda E9. Para el cálculo de la desviación típica la función a utilizar es DESVEST.P. Aplicándola a los datos de pesos y alturas, como muestra la figura más abajo, permite obtener los valores de ambas desviaciones típicas poblacionales.
 |
| Fuente: Elaboración propia. |
Para obtener la covarianza de los datos utilizaremos la función COVARIANCE.P de Excel que devuelve la covarianza poblacional de los mismos. El formato de estas función es el siguiente:
= COVARIANCE.P(rango de las X; rango de las Y)
La aplicación en nuestro caso puede apreciarse en la imagen inferior.
 |
| Fuente: Elaboración propia. |
Nos queda ahora obtener la recta de regresión de Y (Altura) sobre X (Peso) que tomará la forma:
Y = a + b·X
Para nuestro proposito utilizaremos la función ESTIMACION.LINEAL. Esta función tiene cuatro argumentos según el formato:
= ESTIMACION.LINEAL(valores de Y; valores de X; const; stats)
Los dos primeros son los rangos donde se ubican los valores de Y y de X respectivamente y son los únicos que utilizaremos para resolver nuestro problema. El argumento const puede tomar los valores verdadero o falso e indica, en caso de tomar el valor falso, que el valor de a se fuerza a ser cero. Por ultimo, el argumento stats indica a Excel si ha de devolver estadísticas adicionales o no según tome el valor verdadero o el valor falso pero, en nuestro caso, no los necesitamos así que nuestra función quedará de la forma siguiente:
= ESTIMACION.LINEAL(valores de Y; valores de X)
Ahora bien, ESTIMACION.LINEAL es una función matricial, esto es, devuelve una matriz con los resultados por lo que necesitaremos combinarlos con la función INDICE para obtener los resultados que buscamos. Así para obtener el valor de b utilizaremos la formula:
= INDICE(ESTIMACION.LINEAL(valores de X; valores de Y);1;1)
Y para obtener el valor de a utilizaremos la siguiente formula:
= INDICE(ESTIMACION.LINEAL(valores de X; valores de Y);1;2)
Podemos ver el resultado en la animación siguiente:
 |
| Fuente: Elaboración propia. |
= COEF.DE.CORREL (valores de X; valores de Y)
 |
| Fuente: Elaboración propia. |
Espero que os haya gustado.
@Mis Apuntres de Excel.
