La función ESTIMACION.LINEAL es una de las herramientas de análisis de regresión más potentes de Excel. Calcula las estadísticas de una línea recta que se ajusta a los datos observados mediante el método de los mínimos cuadrados. La función devuelve una matriz (un conjunto de valores) que describe esta línea, lo que permite predecir valores futuros basándose en los datos existentes.

Dado que ESTIMACION.LINEAL devuelve una matriz de valores, debe ser introducida como una fórmula de matriz. En las versiones modernas de Excel con matrices dinámicas, la fórmula «desbordará» automáticamente en las celdas adyacentes. En versiones anteriores, es necesario seleccionar el rango de celdas de salida, escribir la fórmula y presionar Ctrl+Mayús+Entrar.

Sintaxis

=ESTIMACION.LINEAL(conocido_y, [conocido_x], [constante], [estadísticas])

La función utiliza la ecuación de la recta: y = m*x + b (para un único rango de valores x) o y = m1*x1 + m2*x2 + ... + b (para múltiples rangos de valores x).

• conocido_y: Es el conjunto de valores «y» que ya se conocen de la relación y = mx + b. Es un argumento obligatorio.
• conocido_x: Un rango opcional de valores «x» conocidos. Si se omite, Excel asume que es una matriz de la forma {1, 2, 3, …} del mismo tamaño que conocido_y. Es un argumento opcional.
• constante: Un valor lógico que especifica si la constante «b» (la intersección con el eje y) debe ser igual a 0.
  • Si es VERDADERO o se omite, «b» se calcula normalmente.
  • Si es FALSO, «b» se establece en 0, y los valores «m» se ajustan para que la ecuación sea y = mx.

  Es un argumento opcional.

• estadísticas: Un valor lógico que determina si se deben devolver estadísticas de regresión adicionales.
  • Si es FALSO o se omite, la función solo devuelve los coeficientes «m» y la constante «b».
  • Si es VERDADERO, devuelve una matriz con estadísticas adicionales como el error estándar, el coeficiente de determinación R², etc.

  Es un argumento opcional.

El formato de la matriz de salida (con estadísticas = VERDADERO):

Cuando estadísticas es VERDADERO, la función devuelve una matriz de 5 filas y un número de columnas igual al número de variables independientes + 1. Para una sola variable x, la matriz es de 5×2:

Ejemplos

Ejemplo 1: Regresión lineal simple

Imaginemos que tenemos datos sobre las ventas mensuales de un producto. Queremos encontrar la línea de tendencia para predecir ventas futuras.

Mes

Para obtener la pendiente (m) y la intersección (b), seleccionamos dos celdas adyacentes (por ejemplo, D2:E2), escribimos la siguiente fórmula y (en versiones antiguas de Excel) la confirmamos con Ctrl+Mayús+Entrar.

=ESTIMACION.LINEAL(B2:B6, A2:A6)

Resultado esperado:

• En D2 aparecerá la pendiente (m): 132. Esto significa que, en promedio, las ventas aumentan 132 € cada mes.
• En E2 aparecerá la intersección (b): 1214. Este sería el valor teórico de las ventas en el mes 0.

Ejemplo 2: Obtener estadísticas de regresión completas

Usando los mismos datos del ejemplo anterior, ahora queremos obtener todas las estadísticas de regresión. Para ello, seleccionamos un rango de 5 filas por 2 columnas (por ejemplo, D2:E6) y usamos la siguiente fórmula de matriz.

=ESTIMACION.LINEAL(B2:B6, A2:A6, VERDADERO, VERDADERO)

Resultado esperado (matriz 5×2):

El valor 0.9881 (celda D4) es el coeficiente R², que indica que el 98.81% de la variación en las ventas se puede explicar por la variable «Mes», lo que sugiere un modelo de predicción muy fuerte.

Ejemplo 3: Regresión lineal múltiple

Ahora, además del mes, añadimos una segunda variable: el gasto en publicidad. Queremos ver cómo ambas variables influyen en las ventas.

Mes

La fórmula ahora usará un rango de dos columnas para el argumento `conocido_x`.

=ESTIMACION.LINEAL(C2:C6, A2:B6)

Resultado esperado: La fórmula devolverá 3 valores en una fila: la pendiente para el Gasto en Publicidad (m2), la pendiente para el Mes (m1) y la intersección (b).

Observaciones

Es crucial entender que ESTIMACION.LINEAL es una función de matriz. En Excel 365 o 2021, la fórmula se «derramará» automáticamente al rango de celdas necesario. En Excel 2019 y versiones anteriores, debe seleccionar previamente el rango de celdas donde se mostrará el resultado, escribir la fórmula y presionar Ctrl+Mayús+Entrar para introducirla como una fórmula de matriz.

Errores comunes

• #¡VALOR!: Ocurre si los argumentos constante o estadísticas no son valores lógicos (VERDADERO o FALSO).
• #N/A: Ocurre si los rangos conocido_y y conocido_x no tienen el mismo número de puntos de datos.
• #¡REF!: Ocurre si los argumentos conocido_y o conocido_x contienen rangos no válidos. También puede aparecer si los datos de entrada no permiten calcular un resultado válido (por ejemplo, si todos los valores de conocido_x son idénticos, lo que se conoce como colinealidad perfecta).

Disponibilidad por versión de Excel

La función ESTIMACION.LINEAL está disponible en todas las versiones de Excel desde Excel 2003 y posteriores.

Compatibilidad

Funciones Relacionadas

• TENDENCIA: Devuelve valores a lo largo de una tendencia lineal. Es útil para realizar predicciones basadas en los resultados de ESTIMACION.LINEAL.
• PENDIENTE: Calcula únicamente la pendiente de la línea de regresión lineal. Es equivalente al primer valor devuelto por ESTIMACION.LINEAL.
• INTERSECCION.EJE: Calcula únicamente el punto donde la línea de regresión se intercepta con el eje Y (la constante b).
• ESTIMACION.LOGARITMICA: Calcula una curva exponencial que se ajusta a los datos y devuelve una matriz de valores que describe la curva.
• COEFICIENTE.R2: Calcula el cuadrado del coeficiente de correlación producto-momento de Pearson (R²), una de las estadísticas clave que devuelve ESTIMACION.LINEAL.