Función LINEST Excel - Estadísticas de regresión lineal

Descargar libro de trabajo de ejemplo

Descarga el libro de trabajo de ejemplo

Este tutorial demuestra cómo utilizar el Función Excel LINEST en Excel para calcular estadísticas sobre una línea de tendencia.

Descripción general de la función LINEST

La función LINEST Calcula estadísticas sobre una línea de tendencia ajustada a puntos de datos conocidos utilizando el método de mínimos cuadrados.

Para usar la función de hoja de cálculo de Excel LINEST, seleccione una celda y escriba:

(Observe cómo aparecen las entradas de la fórmula)

Sintaxis y entradas de la función LINEST

1	= LINEST (conocido_ys, conocido_xs, const, stats)

conocidos_y's - Una matriz de valores Y conocidos.

conocido_x - Una matriz de valores X conocidos.

constante - OPCIONAL. Valor lógico que indica si se debe calcular B (la intersección en y = mx + b) utilizando el método de mínimos cuadrados (VERDADERO u Omitido) o establecer manualmente B = 0 (FALSO).

estadisticas - OPCIONAL. Devuelve estadísticas adicionales (VERDADERO) o solo devuelve m (pendiente) yb (intersección) (FALSO u Omitido)

¿Qué es LINEST?

La función LINEST en Excel es una función que se utiliza para generar estadísticas de regresión para un modelo de regresión lineal. LINEST es una fórmula de matriz y se puede usar sola o con otras funciones para calcular estadísticas específicas sobre el modelo.

La regresión lineal es un método estadístico que se utiliza para predecir datos siguiendo una línea recta utilizando datos conocidos. La regresión se utiliza para predecir valores como el crecimiento de las ventas, los requisitos de inventario o las tendencias simples del mercado.

LINEST es como PRONÓSTICO en el sentido de que logra un resultado similar, pero con mucha más información sobre su modelo de regresión, así como la opción de ajustar más de una variable independiente.

Supongamos que tengo una tabla de datos con X y y valores donde X es la variable independiente y y es la variable dependiente:

Quiero saber cuál es la ecuación de regresión de los datos anteriores. Usando LINEST:

1	= LINEST (B3: B7, C3: C7, VERDADERO, FALSO)

El valor de la intersección con el eje y aquí es equivalente a 0, en notación científica.

La ecuación de la recta es y = 2x + 0. Tenga en cuenta que LINEST devuelve ambos la pendiente y la intersección de la línea. Para devolver ambos valores, la fórmula debe ingresarse como una fórmula de matriz. Más sobre fórmulas de matriz más adelante.

Cómo usar LINEST

La función LINEST toma cuatro argumentos:

1	= LINEST (años conocidos, x conocidos, const, estadísticas)

Dónde,

Argumento	Descripción
conocidos_y's y conocido_x	Es el X y y datos en su tabla de datos
constante	Opción VERDADERO / FALSO para determinar si la intersección con el eje y debe forzarse a 0 o calcularse normalmente
estadisticas	Opción VERDADERO / FALSO si se deben devolver estadísticas de regresión adicionales

Usando nuestro primer ejemplo, la función se escribe como:

1	= LINEST (B3: B7, C3: C7, VERDADERO, FALSO)

Cuando el estadisticas se establece en TRUE, la organización de las estadísticas de regresión es la siguiente:

Quizás se esté preguntando qué significa cada variable.

Estadística	Descripción
metronorte	Coeficientes de pendiente para x variables
B	intersección con el eje y
senorte	Error estándar para cada coeficiente de pendiente
seB	Error estándar para la intersección con el eje y
r2	Coeficiente de determinación
sey	Error estándar para el y estimar
F	El estadístico F (para determinar si la relación de las variables ocurre por casualidad)
DF	Grados de libertad
ssreg	Suma de cuadrados de regresión
ssresid	Suma de cuadrados residual

Las principales estadísticas a comprender son los coeficientes de pendiente, la intersección con el eje y y el coeficiente de determinación o la r² valor del modelo.

Usando el ejemplo anterior y seleccionando VERDADERO para el estadisticas parámetro:

Las celdas resaltadas muestran la pendiente = 2, intersección = 0 y r² = 1.

El r² El valor es un indicador de la fuerza de la correlación del modelo. Se puede considerar como un indicador de ajuste. Una r baja² valor significaría una mala correlación entre sus variables dependientes e independientes, y lo contrario es cierto para r alto² valores, con r² = 1 es un ajuste perfecto.

En las versiones posteriores a enero de 2022 de Excel en Microsoft 365 (anteriormente Office 365), las matrices dinámicas han cambiado la forma en que se evalúan las fórmulas de matriz. Ya no es necesario usar CTRL + MAYÚS + ENTRAR o resaltar el área de celdas que ocupará la matriz. Simplemente ingrese la fórmula y haga clic en Enter y las celdas resultantes se “derramarán” en la matriz.

En el resto de este artículo, haremos referencia al uso de LINEST con respecto a las matrices dinámicas en Microsoft 365 Excel.

Pronóstico con LINEST (regresión simple)

La combinación de las funciones LINEST y SUM se puede utilizar para predecir el valor de una variable dependiente y, dado conocido X y y datos. A continuación se muestra un ejemplo que muestra lo que y el valor será cuando x = 14.

1	= SUMA (LINEST (C3: C7, B3: B7) * {14,1})

El modelo viene en la forma y = mx + b. Esto es lo mismo que y = a + bx, solo que es una forma diferente de representar la ecuación. Un consejo a tener en cuenta para las ecuaciones lineales es la variable junto a X es siempre la pendiente, y la variable que sigue a un signo más o menos es siempre la intersección, independientemente de las letras utilizadas en la ecuación.

Usando la fórmula: = SUM (LINEST (C3: C7, B3: B7) * {14,1}) devuelve el resultado de 28. Dado que este es un resultado único, no es necesario ingresar como una matriz.

El final de la fórmula anterior * {14,1} especifica la variable independiente que se utilizará para predecir la variable dependiente, en este caso, 14.

Podemos verificar esto ingresando x = 14 en la ecuación de la línea, y = 2x + 0.

Pronóstico con LINEST (regresión lineal múltiple)

La siguiente tabla de datos proviene de la página LINEST del sitio web de soporte de Microsoft.

En algunos casos, hay más de una variable independiente que debe tenerse en cuenta al crear un modelo de regresión lineal. Esto se llama regresión lineal múltiple (es decir, múltiples variables independientes). Si quiero estimar el costo de un edificio de oficinas, cosas como el espacio del piso, el número de entradas al edificio, la antigüedad del edificio y el número de oficinas serían parte de la ecuación. Veamos un ejemplo.

Escribiendo la fórmula LINEST en la celda G29 y ejecutándola, obtenemos:

1	= LINEST (E3: E13, A3: D13, VERDADERO, VERDADERO)

El modelo viene en la forma:

Recuerde que la matriz de resultados LINEST está en orden inverso a la ecuación. En el ejemplo anterior, 52,317.8 es nuestra intersección, by 27.6 es nuestra m₁ o el valor de la pendiente de la variable Espacio del suelo, X₁.

Usando la función LINEST con los datos proporcionados, nuestro modelo de regresión es:

Con una r² valor de 0,997, que indica un modelo fuerte o altamente correlacionado. Con el modelo, ahora puede predecir cuál será el valor tasado de un edificio de oficinas en función de cualquier combinación de las variables independientes anteriores.

Consejos LINEST

1. Asegúrese de tener la versión más actualizada de Microsoft 365 para utilizar LINEST con matrices dinámicas. Es posible que deba habilitar el canal actual de Office Insider (vista previa) para utilizar funciones de matriz dinámica. En la página de la cuenta:
   
2. Si tiene una versión que no es de Microsoft 365, tendrá que usar el método heredado CTRL + MAYÚS + ENTRAR (CSE) para evaluar fórmulas de matriz.
3. Si usa el método heredado, el número de columnas para resaltar al ingresar una función de matriz LINEST es siempre el número de X variables en sus datos más 1. El número de filas para seleccionar para la matriz es 5.
4. Si va a compartir su versión de Excel habilitada para matriz dinámica con alguien que esté usando una versión que no sea de Microsoft 365, use el método CSE heredado para evitar problemas de compatibilidad.

¿Interesado en más previsiones?

Consulte nuestros otros artículos sobre pronóstico con funciones de suavizado exponencial, TENDENCIA, CRECIMIENTO y LOGEST.

LINEST función en Google Sheets

La función LINEST funciona exactamente igual en Google Sheets que en Excel.

Ejemplos de LINEST en VBA

También puede usar la función LINEST en VBA. Escribe:
application.worksheetfunction.linest (known_ys, known_xs, const, stats)

Ejecutando la siguiente declaración de VBA

1	Range ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8"))

producirá los siguientes resultados

Para los argumentos de la función (conocidos_y, etc.), puede ingresarlos directamente en la función o definir variables para usar en su lugar.

Regrese a la lista de todas las funciones en Excel