Análisis prescriptivo de datos en Excel: guía para decidir

El análisis prescriptivo convierte datos históricos en una recomendación accionable: no solo explica qué ocurrió ni qué podría ocurrir, sino qué conviene hacer. En este tutorial construirás, paso a paso, la hoja de un pequeño inventario que termina recomendando «Reponer», «Revisar» o «Mantener» para cada producto.

El flujo funciona en Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024 y Microsoft 365, en Excel para Mac y para la web. Una hoja puede ser un punto de decisión operativo construido con fórmulas, filtros y formato condicional; el modelo exhaustivo de optimización puede vivir fuera de Excel.

Qué es el análisis prescriptivo y qué decisión toma

El análisis descriptivo resume lo que pasó con las ventas. El predictivo estima qué podría pasar a continuación. El prescriptivo responde a otra pregunta: ¿qué acción tomar, con los recursos y límites disponibles?

En el libro de este tutorial, una tienda tiene cuatro productos con ventas, coste, margen y stock. La pregunta no es solo qué producto vende más, sino cuál conviene reponer, cuál necesita una revisión y cuál puede mantenerse sin cambios. La hoja «Ventas» reúne los datos; «Decisiones» compara las alternativas; las reglas de negocio convierten cada combinación de ventas, margen y stock en una acción.

Lo que se obtiene al terminar es una recomendación operativa auditable. Cada fórmula se puede revisar en la barra de fórmulas, cada regla está escrita en una celda y cada resultado se puede comprobar. Excel no sustituye a un modelo predictivo o a un optimizador, pero permite construir la base descritiva, ordenar prioridades y documentar cómo se decide.

Cómo funciona el flujo prescriptivo

El flujo tiene cinco piezas: objetivo, datos, análisis, optimización y decisión. En un libro de Excel, cada una ocupa un lugar concreto.

• Objetivo: definir qué resultado se busca, por ejemplo priorizar la reposición de productos con margen suficiente.
• Datos: reunir ventas, coste, stock y demás información disponible.
• Análisis: calcular margen, orden y prioridad para entender qué productos destacan.
• Optimización: contrastar alternativas con restricciones, como presupuesto, capacidad o existencias.
• Decisión: aplicar reglas explícitas que traduzcan el análisis en una acción.

Objetivo, datos y restricciones

Antes de escribir fórmulas, conviene fijar qué significa «priorizar» y qué recursos limitan la decisión.

La hoja debe usar fechas y valores coherentes, una fila por venta, nombres de producto consistentes y ningún importe escrito como texto. Si faltan datos, la regla no debe inventarlos: será mejor señalarlos para revisión.

Del resultado analítico a la acción recomendada

Un resultado analítico, como «este producto tiene el mayor margen», no es todavía una decisión. La acción aparece al combinarlo con restricciones y prioridades:

• Primero, comprobar que las ventas superen el umbral definido.
• Después, verificar que el margen cumple la regla de reposición.
• A continuación, contrastar el stock disponible con el tamaño del pedido propuesto.
• Finalmente, asignar «Revisar» cuando los datos sean ambiguos owhen falte información.

Así, la recomendación no depende de una intuición aislada, sino de reglas visibles. En una organización más grande, estas reglas pueden combinarse con programación lineal, árboles de decisión o simulaciones. La documentación de Actian sobre análisis prescriptivo explica ese enfoque ampliado.

Preparar los datos de ventas en una hoja de Excel

Crea un libro llamado Inventario.xlsx y una hoja Ventas. Escribe estos datos desde A1 hasta D5. La primera fila tendrá los encabezados en negrita.

Antes de continuar:

1. Selecciona el rango A1:D5.
2. En Inicio > Estilos, aplica un estilo de tabla para que los encabezados standout.
3. Comprueba que las fechas de la columna A son fechas y que los importes de C y D son números.
4. Ordena cada columna por su encabezado para detectar filas desordenadas.
5. Revisa que el nombre de cada producto sea idéntico en todas las filas; «Cable» y «Cable USB» serían productos distintos para esta tabla.

Calcular ventas, costes y margen con fórmulas

Amplía la hoja con tres columnas: Margen, % Margen y Prioridad. En E1, F1 y G1 escribe esos encabezados.

En E2 calcula el margen restando el coste a las ventas:

=D2-C2

En F2 calcula el porcentaje de margen:

=(D2-C2)/D2

Arrastra el controlador de relleno de E2 y F2 hasta la fila 5. Después aplica el formato de porcentaje a F2:F5.

Detectar qué productos conviene priorizar

En H1 añade Venta por producto y en H2 escribe la fórmula siguiente:

=SUMAR.SI($B$2:$B$5;B2;$C$2:$C$5)

Copia H2 hasta H5. Esta fórmula suma las ventas de cada producto con SUMAR.SI (SUMIF), una función disponible en las versiones indicadas.

Ahora, en G2, obtén la posición del producto según sus ventas acumuladas. K.ESIMO.MAYOR (LARGE) devuelve el k-ésimo mayor valor:

=K.ESIMO.MAYOR($H$2:$H$5;K2)

En I1 escribe Posición y en I2 escribe 1; en J1 escribe k y en J2 escribe 2. Copia G2 hasta G5 y J2 hasta J5.

La prioridad identifica qué producto lidera en ventas. No decide por sí sola qué reponer: también intervienen el margen y el stock. Además, si dos productos tienen las mismas ventas, K.ESIMO.MAYOR puede devolver el mismo valor; por eso la posición no debe presentarse como un orden único sin revisar empates.

Comparar alternativas con una tabla de decisiones

Crea una hoja Decisiones con los campos Producto, Margen, Stock, Inversión, Acción y Nota. Copia desde la hoja Ventas los productos y márgenes. Añade el stock y la inversión propuestos:

Para enlazar los datos, usa en B2:

=BUSCARV(A2;Ventas!$B$2:$E$5;4;FALSO)

BUSCARV (VLOOKUP) recupera el margen de la hoja Ventas. Arrastra la fórmula hasta B5. La columna de inversión será el coste indicado en esa misma hoja:

=BUSCARV(A2;Ventas!$B$2:$E$5;3;FALSO)

Ordenar productos por margen y volumen

Vuelve a la hoja Ventas y añade una columna Venta por producto si todavía no la tienes. Ahora:

1. Selecciona A1:H5.
2. Pulsa Ctrl+Mayús+L para activar el filtro.
3. Abre el filtro de % Margen y elige Ordenar de mayor a menor.
4. Comprueba que Monitor queda primero con 35,00 %, seguido de Ratón con 60,00 %.

Ordenar no elimina datos: solo cambia su presentación. Si más adelante se añade un producto, comprueba que el rango de la tabla incluya la fila nueva.

Resumir los datos filtrados

En la hoja Decisiones, crea un pequeño resumen debajo de la tabla. En A8 escribe Resumen visible y en B8 usa SUBTOTALES (SUBTOTAL):

=SUBTOTALES(109;B2:B5)

En B9 resume la inversión visible:

=SUBTOTALES(109;D2:D5)

excel
=$E2="Reponer"

1. Elige un relleno claro y pulsa Aceptar.

Para localizar errores ya escritos en la hoja, usa Datos > Herramientas de datos > Validación de datos > Rodear con un círculo datos no válidos. Esta función marca valores que incumplen una regla existente; no detecta por sí sola todos los errores de negocio.

Qué parte del análisis prescriptivo ocurre fuera de Excel

Excel cubre bien la capa operativa: limpiar, calcular, ordenar, resumir y aplicar reglas visibles. Sin embargo, un análisis prescriptivo completo puede necesitarOptimización matemática, simulación, aprendizaje automático o datos en tiempo real. La documentación de Actian describe ese proceso ampliado con datos de múltiples fuentes y modelos de optimización.

Por tanto, la hoja no debe presentarse como un modelo predictivo si solo contiene ventas históricas. Tampoco es un optimizador si las reglas se han escrito a mano para cuatro productos. Su valor está en convertir criterios, datos y restricciones en decisiones revisables.

Hay varios motivos para salir de Excel: el volumen de datos, la conexión con sistemas externos, la necesidad de simular millones de escenarios o la ejecución de modelos entrenados. Power Query, integrado desde Excel 2016, puede ayudar a preparar datos de varias fuentes; Power BI, Python o un modelo de optimización pueden aportar el resto.

La frontera práctica es sencilla: si la decisión se explica con un conjunto reducido de reglas y debe quedar documentada, Excel es adecuado. Si requiere estimar la mejor combinación entre miles de alternativas y restricciones, conviene usar un optimizador y exportar después sus resultados al informe.

Lo que ya sabes hacer con el análisis prescriptivo

• Has convertido ventas y costes en margen y porcentaje de margen con fórmulas de fila.
• Has comparado prioridades de venta con SUMAR.SI, K.ESIMO.MAYOR y BUSCARV.
• Has construido una tabla de decisiones con stock, inversión, reglas y resúmenes visibles con SUBTOTALES.
• Has hecho visibles las recomendaciones prioritarias mediante formato condicional y has registrado controles de calidad.
• Sabes distinguir una hoja operativa en Excel de un modelo predictivo u optimizador más avanzado.

Siguiente paso: revisa los umbrales con el equipo que conoce el negocio, añade una columna de inversión propuesta y define qué presupuesto máximo debe respetar la acción «Reponer».

Dudas al usar el análisis prescriptivo en Excel