La función ESTIMACION.LINEAL es una herramienta de análisis de regresión que calcula la línea recta que mejor se ajusta a un conjunto de datos, utilizando el método de los «mínimos cuadrados». Devuelve una matriz de valores que describe esta línea, y opcionalmente, puede proporcionar estadísticas de regresión adicionales.
Esta función es increíblemente versátil, ya que no solo sirve para regresiones lineales simples, sino que también puede adaptarse para analizar regresiones polinómicas, exponenciales y de otros tipos.
Sintaxis
=ESTIMACION.LINEAL(conocido_y, [conocido_x], [constante], [estadísticas])La función devuelve una matriz de valores. Si tu versión de Excel es compatible con matrices dinámicas (Excel 365, Excel 2021), el resultado se «derramará» automáticamente en las celdas adyacentes. En versiones anteriores, debes seleccionar el rango de celdas de salida, escribir la fórmula y presionar Ctrl + Mayús + Entrar para introducirla como una fórmula de matriz.
- conocido_y: Es el conjunto de valores «y» que ya conoces en la relación y = mx + b. Debe ser una sola columna o fila. Obligatorio.
- conocido_x: Es un conjunto opcional de valores «x» que conoces. Si se omite, Excel asume que es una matriz del mismo tamaño que `conocido_y` con la secuencia {1, 2, 3, …}. Opcional.
- constante: Es un valor lógico que especifica si la constante «b» (la intersección con el eje y) debe ser igual a 0.
- VERDADERO (o si se omite): La constante «b» se calcula normalmente.
- FALSO: Se fuerza que «b» sea 0, y los valores de la pendiente «m» se ajustan para que la ecuación sea y = mx.
Opcional.
- estadísticas: Es un valor lógico que determina si se devuelven estadísticas de regresión adicionales.
- VERDADERO: La función devuelve una matriz con estadísticas adicionales (error estándar, coeficiente de determinación R², etc.).
- FALSO (o si se omite): Solo devuelve los coeficientes de la pendiente («m») y la constante de intersección («b»).
Opcional.
Ejemplos
Ejemplo 1: Regresión lineal simple
Supongamos que tenemos datos de ventas mensuales (conocido_y) y el gasto en publicidad correspondiente (conocido_x). Queremos encontrar la pendiente y la intersección de la línea de tendencia.
| A (Gasto Pub.) | B (Ventas) | |
|---|---|---|
| 1 | 1000 | 15000 |
| 2 | 1200 | 18000 |
| 3 | 1500 | 24000 |
| 4 | 1800 | 28000 |
| 5 | 2000 | 31000 |
=ESTIMACION.LINEAL(B1:B5, A1:A5)Resultado esperado: La función devolverá una matriz horizontal de 1×2. La primera celda contendrá la pendiente (m), aproximadamente 16.31, y la segunda la intersección (b), aproximadamente -1585.37. Esto significa que la ecuación de la línea es Ventas ≈ 16.31 * Gasto – 1585.37.
Ejemplo 2: Regresión lineal con estadísticas completas
Usando los mismos datos del ejemplo anterior, ahora queremos obtener un análisis estadístico completo.
=ESTIMACION.LINEAL(B1:B5, A1:A5, VERDADERO, VERDADERO)Resultado esperado: La función devolverá una matriz de 5×2. Para entender los valores, aquí tienes una guía de lo que representa cada celda:
| 16.31 (Pendiente) | -1585.37 (Intersección) |
| 1.39 (Error estándar de m) | 2184.97 (Error estándar de b) |
| 0.97 (Coeficiente de determinación R²) | 1923.4 (Error estándar de y) |
| 137.85 (Estadística F) | 3 (Grados de libertad) |
| 5.11E+08 (Suma de cuadrados de la regresión) | 1.11E+07 (Suma de cuadrados residual) |
Ejemplo 3: Regresión polinómica
ESTIMACION.LINEAL puede ajustarse a una curva polinómica (ej. y = m3*x³ + m2*x² + m1*x + b) tratando las potencias de x como variables independientes.
| A (x) | B (y) | |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 8 |
| 3 | 3 | 27 |
| 4 | 4 | 65 |
| 5 | 5 | 124 |
Para ajustar un polinomio de grado 3 (cúbico), usamos la siguiente fórmula:
=ESTIMACION.LINEAL(B1:B5, A1:A5^{1,2,3})Resultado esperado: Devolverá los coeficientes para x³, x², x y la intersección ‘b’. En este caso, serán aproximadamente {0.98, 0.14, -0.12, -0.003}, que se acerca a la función y = x³.
Observaciones
El principal desafío al usar ESTIMACION.LINEAL es comprender que devuelve una matriz. La introducción de matrices dinámicas en las versiones más recientes de Excel ha simplificado enormemente su uso, ya que los resultados se expanden automáticamente al rango necesario.
Errores comunes
- #¡VALOR!: Ocurre si los rangos `conocido_y` y `conocido_x` no tienen el mismo número de puntos de datos.
- #¡REF!: Puede ocurrir en versiones antiguas de Excel si intentas introducir la fórmula de matriz en un rango de celdas demasiado pequeño para contener todos los resultados.
- #N/A: No hay errores #N/A documentados para esta función, pero podría aparecer si los datos de entrada contienen este error.
Disponibilidad por versión de Excel
La función ESTIMACION.LINEAL está disponible en todas las versiones modernas de Excel, incluyendo Excel 2007, 2010, 2013, 2016, 2019, 2021 y Microsoft 365. El comportamiento de la función cambió significativamente con la introducción de las matrices dinámicas en Microsoft 365 y Excel 2021, eliminando la necesidad de usar Ctrl+Mayús+Entrar.
Compatibilidad
| Software | Compatibilidad | Notas |
|---|---|---|
| Microsoft Excel | ✔️ | Funcionalidad completa. |
| Google Sheets | ✔️ | La función se llama LINEST. |
| LibreOffice Calc | ✔️ | Soporta tanto ESTIMACION.LINEAL como LINEST. |
| OpenOffice Calc | ✔️ | Funciona de manera similar a LibreOffice Calc. |
| WPS Office Spreadsheets | ✔️ | Compatible. La función se llama LINEST. |
| Apple Numbers | ✔️ | Compatible. La función se llama LINEST. |
Funciones Relacionadas
- PENDIENTE: Calcula únicamente la pendiente de la línea de regresión lineal.
- INTERSECCION.EJE: Calcula únicamente el punto donde la línea de regresión corta el eje Y (la constante ‘b’).
- TENDENCIA: Devuelve valores a lo largo de una tendencia lineal. Es útil para realizar proyecciones.
- ESTIMACION.LOGARITMICA: Calcula una curva exponencial que se ajusta a los datos y devuelve una matriz de valores que describe la curva.
- COEF.DE.CORREL: Calcula el coeficiente de correlación entre dos conjuntos de datos para determinar la fuerza de su relación lineal.