Análisis predictivo en Excel: qué es y cómo empezar con datos reales

El análisis predictivo en Excel consiste en usar datos históricos para anticipar valores futuros: ventas del próximo trimestre, demanda del mes que viene, gasto del trimestre siguiente. Aquí lo entenderás como pronosticar una serie de ventas de una papelería que vende cuadernos, bolígrafos y mochilas. Al terminar sabrás sacar una previsión con PRONOSTICO.LINEAL (FORECAST.LINEAR), con la hoja de previsión de Excel y con una regresión hecha a mano con ESTIMACION.LINEAL (LINEST). Todo funciona en Excel para Microsoft 365, 2024, 2021, 2019 y 2016; la hoja de previsión necesita 2019 o posterior.

Qué es el análisis predictivo y dónde llega Excel de verdad

Predecir es mirar hacia atrás para adelantar lo que viene: coges los datos que ya tienes —las ventas de los últimos meses, el consumo eléctrico del último año, las visitas a tu tienda— y, a partir de ahí, calculas cuánto cabe esperar en el futuro. Cuando los datos están limpios y el patrón es razonablemente estable, lo puedes hacer sin salir del libro.

Con Excel llegas a lo que cubre una hoja de cálculo bien usada: tendencia lineal, suavizado de la serie con medias móviles, regresión simple y la hoja de previsión automática que genera un gráfico con bandas de confianza. Lo que NO hace Excel de serie es clasificación (¿este cliente se va a dar de baja?), clustering (¿qué productos se compran juntos?), modelos no lineales tipo ARIMA, Prophet o redes neuronales, ni predicciones sobre series con cientos de miles de observaciones en tiempo real. Para eso necesitas Python en Excel, R, Power BI con sus modelos de Azure Machine Learning, SPSS o RapidMiner.

El ejemplo que vas a ver es el de una papelería que vende material de oficina y mochilas. Tiene 24 meses de ventas (de enero de 2024 a diciembre de 2025) y nota: en agosto de cada año, con la vuelta al cole, las ventas suben bastante. Esa estacionalidad es la que vamos a detectar y, hasta donde Excel llega, a modelar.

La hoja de ventas con la que vas a trabajar

Abre un libro nuevo y crea una hoja que se llame Ventas. Ahí vas a escribir la serie: 24 meses en la columna A y el importe de cada mes en la columna B. Elige estos importes: reflejan una tendencia suave al alza con un pico claro cada año en agosto (vuelta al cole).

1. En la celda A1 escribe Mes y en B1 Ventas.
2. En A2 escribe 01/01/2024 y en B2 1.250. En A3 01/02/2024, B3 1.340. Sigue con los demás meses hasta llegar a A25 con 01/12/2025. La columna B contiene los importes siguientes.
3. Selecciona A2:A25 y aplica formato de fecha corta (Ctrl+1 > Fecha > 14/03/2024, según el idioma).
4. Selecciona B2:B25 y aplica formato moneda sin símbolo (Ctrl+1 > Número > Moneda, desmarcando el símbolo).
5. Selecciona B2:B25, ve a Fórmulas > Nombres definidos > Definir nombre y crea el nombre Ventas que apunte a =Ventas!$B$2:$B$25. Con Ctrl+F3 lo compruebas.

Pronosticar con la función PRONOSTICO.LINEAL y la línea de tendencia

PRONOSTICO.LINEAL (FORECAST.LINEAR) extiende una recta ajustada a los datos conocidos para devolver el valor esperado en un punto nuevo. La fórmula pide los valores conocidos de Y, los valores conocidos de X y el X del que quieres predecir Y.

1. En A26 escribe 01/01/2026 y en B26 =PRONOSTICO.LINEAL($B$2:$B$25;$A$2:$A$25;A26).
2. Copia la fórmula hacia abajo hasta A30 (seis meses previstos: enero a junio de 2026).
3. En C26 escribe =PROMEDIO(B23:B25) y cópiala hasta C30: así tienes la media móvil de los últimos tres meses como referencia.
4. Selecciona A2:B30 e Insertar > Gráficos > Gráficos de dispersión con líneas.

=PRONOSTICO.LINEAL($B$2:$B$25;$A$2:$A$25;A26)
=PROMEDIO(B23:B25)

PRONOSTICO.LINEAL solo ve tendencia, no estacionalidad: para enero de 2026 da 1.803, sin el pico de agosto que ya sabes que existe. Por eso es útil compararla con la media móvil de los últimos tres meses: si las dos se parecen, la tendencia explica la serie; si difieren mucho, hay estacionalidad.

Dibujar la línea de tendencia en el gráfico

Una línea de tendencia es la recta que mejor se ajusta a los puntos del gráfico. Añadírsela te enseña visualmente cómo de limpia es la tendencia y te deja ver la ecuación y el coeficiente R² al lado.

1. Clic en cualquier punto de la serie de ventas del gráfico.
2. Clic derecho > Agregar línea de tendencia > Lineal.
3. En el panel de la derecha marca Presentar ecuación en el gráfico y Presentar valor R cuadrado en el gráfico.

Suavizar la serie y detectar la estacionalidad con el promedio móvil

La media móvil (PROMEDIO sobre una ventana de meses) suaviza los picos y deja ver la tendencia de fondo. Una ventana de 3 meses quita los dientes pequeños; una de 12 meses quita toda la estacionalidad anual y deja la curva larga.

1. En C2 escribe Media 3m y en D2 Media 12m.
2. En C4 escribe =PROMEDIO(B2:B4) y arrastra el controlador de relleno hasta C25.
3. En D13 escribe =PROMEDIO(B2:B13) y arrastra hasta D25.
4. Selecciona A2:A25 y, manteniendo Ctrl, añade C2:C25 y D2:D25.
5. Inserta un gráfico de líneas y compáralo con el de la serie original.

=PROMEDIO(B2:B4)
=PROMEDIO(B2:B13)

Si la curva de la media 12 sube con el tiempo, hay tendencia al alza. Si la curva de la media 3 tiene dientes regulares cada 12 meses, hay estacionalidad. En esta serie verás las dos cosas.

Prever varios periodos a la vez con la hoja de previsión de Excel

La hoja de previsión es la herramienta integrada que, con un clic, genera un gráfico con la serie histórica, la previsión y unas bandas de confianza del 95 %. Está en Excel 2016 en adelante, pero el gráfico con bandas mejorado se incorporó en 2019.

1. Selecciona A1:B25 (los 24 meses con sus importes).
2. Ve a Datos > Previsión > Hoja de previsión.
3. En el cuadro elige crear un nuevo libro o una nueva hoja dentro del actual y pulsa Crear.

Aparece un libro o una hoja nueva con un gráfico: la parte en negro es lo real, la azul oscuro es la previsión y las dos líneas azul claro son las bandas del 95 % (arriba y abajo).

Elegir el inicio y el fin de la previsión

El cuadro Hoja de previsión deja mover el inicio del histórico y el final de la previsión. Sirve para comparar dos escenarios: por ejemplo, empezar la previsión en julio (cuando ya se ve la vuelta al cole) o alargarla a 24 meses.

Leer el gráfico: valor real, previsión y bandas de confianza

En el gráfico, la línea negra gruesa son los valores reales (los 24 meses que ya tienes). La línea azul oscuro gruesa es la previsión: los 12 meses siguientes calculados por el modelo. Las dos líneas azul claro finas, una por arriba y otra por abajo, son las bandas del 95 %: el rango en el que Excel cree que caerá el valor real con un 95 % de probabilidad.

Cuanto más te alejas en el tiempo, más anchas son las bandas: la incertidumbre crece con el tiempo, y eso es normal. Si el pico de agosto se repite cada año en los datos, el modelo lo recoge en la previsión; si no se repite, no.

Variables explicativas con TENDENCIA y ESTIMACION.LINEAL

ESTIMACION.LINEAL (LINEST) devuelve los coeficientes de una regresión lineal por mínimos cuadrados. Es la versión matricial de la regresión: con ella obtienes la pendiente y la ordenada en el origen de la recta, además de los estadísticos R², error típico y los grados de libertad.

1. En E1 escribe MesNum, en E2 1, en E3 2 y arrastra hasta E25 con 24.
2. En F1 escribe Intersección, en G1 Pendiente.
3. En F2:G2 escribe =ESTIMACION.LINEAL(B2:B25;E2:E25;VERDADERO;VERDADERO), pero como ESTIMACION.LINEAL devuelve dos valores (pendiente e intersección), tienes que escribirla como fórmula matricial.
4. Selecciona G2:H2, escribe =ESTIMACION.LINEAL(B2:B25;E2:E25;VERDADERO;VERDADERO) y confirma con Ctrl+Mayús+Entrar (Mac: Cmd+Mayús+Entrar).
5. Para predecir enero de 2026 (MesNum 25), en B26 escribe =H2+G2*E26 (pendiente × MesNum + intersección).

=ESTIMACION.LINEAL(B2:B25;E2:E25;VERDADERO;VERDADERO)
=H2+G2*E26

La pendiente (6,4) indica que, según el modelo, cada mes las ventas suben 6,4 unidades. La intersección (1.745) es el valor teórico del MesNum 0. Para MesNum 25 (enero de 2026) la previsión es 1.745 + 6,4 × 25 = 1.905.

Por qué tu predicción no cuadra con el modelo

Las predicciones fallan por tres razones típicas cuando los datos son mensuales: huecos en la serie, fechas mal reconocidas como texto y series con demasiado ruido o con estacionalidad fuerte que la tendencia lineal no recoge. Cuando veas bandas de confianza enormes o números que no cuadran, vuelve a la hoja y revisa estos puntos antes de tocar el modelo.

La serie tiene un hueco o un valor en blanco

Si borras por accidente el valor de B15 (marzo de 2025), la fórmula =PRONOSTICO.LINEAL($B$2:$B$25;$A$2:$A$25;A26) devuelve #¡NUM!. La función necesita que X e Y tengan el mismo número de puntos y que todos sean números: una celda en blanco rompe ese requisito.

=SI.ERROR(PRONOSTICO.LINEAL($B$2:$B$25;$A$2:$A$25;A26);0)

Tienes dos arreglos: rellenar la celda vacía con el dato real (o una estimación), o envolver la fórmula en SI.ERROR para que devuelva 0 o un texto y no rompa la hoja. Rellenar el hueco es siempre mejor.

Fechas confundidas con texto en el eje X

Si la columna A la escribes como enero 2024, febrero 2024, etc., la hoja de previsión trata los meses como categorías, no como fechas, y no puede detectar que son 12 y luego 24. El resultado es una previsión plana que no respeta la periodicidad.

1. Selecciona A2:A25 y aplica formato de fecha (Ctrl+1 > Fecha).
2. Si están en texto, usa Datos > Herramientas de datos > Texto en columnas con Fecha en el paso 3 para convertirlas.
3. Vuelve a lanzar la hoja de previsión.

R² muy bajo y bandas de confianza enormes

Si al añadir la línea de tendencia el R² sale por debajo de 0,4 y al usar la hoja de previsión las bandas del 95 % cubren casi toda la vertical del gráfico, el problema es que la tendencia lineal sola no explica la serie. Casi siempre es por estacionalidad fuerte que no hemos incluido, o por valores atípicos (un mes con una promoción puntual que disparó las ventas).

Vuelve a la media móvil de 12 meses: si su curva sube con claridad, hay tendencia de fondo pero la serie tiene dientes estacionales que la recta no pilla. En ese caso, PRONOSTICO.LINEAL solo no basta: conviene modelar la estacionalidad por separado (multiplicar la tendencia por un índice estacional) o usar la hoja de previsión, que sí la recoge automáticamente.

Lo que Excel no hace y cuándo conviene otra herramienta

Excel es una hoja de cálculo, no un entorno de modelado estadístico. Cuando el problema se separa de una recta con una ventana móvil, deja de ser la herramienta ideal.

• Modelos no lineales. Si la serie crece de forma exponencial o tiene un comportamiento con asíntotas, saturación o efectos umbral, Excel no tiene herramientas nativas para ajustarlos. Python en Excel (con scikit-learn o statsmodels) o R cubren esto con nls(), glm() o prophet().
• Clasificación y clustering. Predecir si un cliente se va a dar de baja (clasificación binaria) o agrupar productos por patrones de compra (clustering) son tareas de aprendizaje automático. Excel no tiene k-means, árboles de decisión ni regresión logística no requiere Python en Excel, Azure Machine Learning, SPSS o RapidMiner.
• Series muy largas. Con más de unas decenas de miles de observaciones la hoja se vuelve lenta. Power BI con un modelo de Azure Machine Learning, o Python con pandas, manejan millones de filas sin despeinarse.
• Predicción en tiempo real. Si necesitas reentrenar el modelo cada hora con datos que llegan por una API, Excel no es el sitio. Power BI con un flujo de datos en streaming, Azure Machine Learning o un servicio en la nube son las opciones razonables.

Python en Excel (Microsoft 365, con licencia que lo habilite) abre la puerta a pandas, statsmodels y scikit-learn directamente desde una celda de Excel con =PY(...). No es para todo el mundo y requiere licencia específica, pero existe.

Lo que ya sabes hacer con el análisis predictivo en Excel

• Dibujar una línea de tendencia lineal sobre un gráfico y leer su ecuación y R².
• Sacar una previsión rápida con PRONOSTICO.LINEAL (=PRONOSTICO.LINEAL(Y;X;nuevoX)), copiada y comparada con la media móvil de los últimos tres meses.
• Suavizar la serie con medias móviles de 3 y 12 meses para distinguir tendencia de estacionalidad.
• Generar una hoja de previsión con Datos > Previsión > Hoja de previsión, eligiendo 12 meses por delante y leyendo las bandas de confianza del 95 %.
• Calcular los coeficientes de una regresión lineal con ESTIMACION.LINEAL como fórmula matricial y predecir con =intersección + pendiente × MesNum.

El siguiente movimiento es cruzar esta serie con variables externas (precio medio, número de clientes activos, días hábiles del mes) usando INDICE y COINCIDIR para traer el dato y multiplicarlo por su coeficiente; eso convierte la predicción en una regresión múltiple con la misma técnica que ya has usado aquí.

Dudas al usar análisis predictivo en Excel