Cómo crear un sistema de alertas con formato condicional y función SI
Este instructivo técnico está diseñado para profesionales de la ingeniería industrial, analistas de datos y gestores de operaciones que necesitan automatizar la detección de desviaciones críticas en sus procesos de negocio utilizando las capacidades avanzadas de Microsoft Excel.
Definición del problema
En el ámbito de la gestión de la cadena de suministro (Supply Chain Management), el control de inventarios es vital. Un desabastecimiento (stockout) o un exceso de inventario inmoviliza capital y detiene la producción. El reto operativo es monitorear continuamente el nivel de existencias de diferentes componentes críticos y generar una señal visual inmediata (una alerta) cuando el stock caiga por debajo de un umbral de seguridad predefinido, o cuando se acerque a un nivel de reorden. Depender de revisiones manuales es propenso a errores y genera retrasos críticos en la toma de decisiones operativas.
Nuestro objetivo es transformar una hoja de datos estática en un panel de control dinámico que señale automáticamente las situaciones anómalas.
Explicación técnica
Para resolver este problema, combinaremos dos herramientas fundamentales de Excel: la función lógica SI y el Formato Condicional.
- Función SI (IF): Esta función permite realizar una prueba lógica y devolver un valor si la prueba es VERDADERA y otro valor si es FALSA. En nuestro caso, la prueba será: «Si el Stock Actual es menor o igual al Stock Mínimo, entonces mostrar ‘ALERTA CRÍTICA'».
- Formato Condicional: Esta herramienta aplica automáticamente un formato específico (color de fondo, fuente, etc.) a una celda o rango de celdas si se cumple una condición definida. En lugar de solo mostrar texto, usaremos el formato condicional para pintar la celda de rojo o amarillo, proporcionando una señal visual instantánea sin necesidad de leer el texto de la celda.
La lógica general es: SI(Condición_Stock <= Stock_Minimo, "Alerta", "OK"). Luego, aplicaremos el formato condicional a la celda que contiene este resultado.
Guía paso a paso
Paso 1: Configuración de la base de datos de inventario
Primero, debemos estructurar los datos de inventario. Crearemos columnas para identificar el producto, su stock actual y el umbral de seguridad.
Acción: Ingresa los siguientes datos en las celdas A1 a C5.
Resultado: Tendremos una tabla base con los datos de los componentes.
| A | B | C | |
|---|---|---|---|
| 1 | Producto | Stock Actual | Stock Mínimo |
| 2 | Tornillo M8 | 1500 | 2000 |
| 3 | Rodamiento 6205 | 450 | 500 |
| 4 | Acople Z-10 | 80 | 100 |
| 5 | Sello Hidráulico | 3200 | 1500 |
Paso 2: Implementación de la función SI para generar el estado de alerta
Crearemos una nueva columna (Columna D) para que Excel determine el estado de cada producto basándose en la comparación entre el Stock Actual (Columna B) y el Stock Mínimo (Columna C).
Acción: En la celda D1, escribe el encabezado «Estado». En la celda D2, introduce la siguiente fórmula: =SI(B2<=C2; "ALERTA CRÍTICA"; "OK"). Arrastra esta fórmula hacia abajo hasta la celda D5.
Por qué: La función evalúa si el valor en B2 es menor o igual al valor en C2. Si es cierto, devuelve «ALERTA CRÍTICA»; si no, devuelve «OK».
Resultado: La columna D mostrará el estado lógico de cada componente.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Producto | Stock Actual | Stock Mínimo | Estado |
| 2 | Tornillo M8 | 1500 | 2000 | ALERTA CRÍTICA |
| 3 | Rodamiento 6205 | 450 | 500 | ALERTA CRÍTICA |
| 4 | Acople Z-10 | 80 | 100 | ALERTA CRÍTICA |
| 5 | Sello Hidráulico | 3200 | 1500 | OK |
Paso 3: Aplicación del Formato Condicional para la Alerta Crítica (Rojo)
Ahora, haremos que la celda se pinte de rojo automáticamente cuando la función SI devuelva «ALERTA CRÍTICA».
Acción: Selecciona el rango de celdas D2:D5. Ve a la pestaña Inicio > Formato Condicional > Nueva Regla. Selecciona «Utilice una fórmula que determine las celdas para aplicar formato». Ingresa la fórmula: =$D2="ALERTA CRÍTICA". Haz clic en Formato… y selecciona un relleno de color rojo claro.
Por qué: Usamos el signo de dólar ($) para fijar la columna D, asegurando que la regla se aplique a todas las celdas seleccionadas, pero que la evaluación de la condición siempre se haga sobre la celda de la fila actual (D2, D3, D4, etc.).
Resultado: Las celdas D2, D3 y D4 se colorearán automáticamente de rojo.
Paso 4: Creación de una Alerta de Advertencia (Amarillo)
Para refinar el sistema, añadiremos una advertencia para cuando el stock esté cerca del mínimo, pero aún no sea crítico (ej. entre 80% y 100% del mínimo).
Acción: Manteniendo el rango D2:D5 seleccionado, vuelve a Formato Condicional > Nueva Regla. Selecciona «Utilice una fórmula…». Ingresa la fórmula: =Y($D2="OK"; B2<=C2*1.1). (Nota: Esta fórmula es más compleja y requiere que la columna D contenga el texto «OK» pero que el stock esté cerca del límite). Para simplificar, usaremos una regla basada en el valor numérico de B2.
Alternativa Simplificada (Recomendada): Crea una columna E con la fórmula: =SI(B2<=C2*1.2; "ADVERTENCIA"; "OK"). Luego, aplica el formato condicional a E2:E5: =$E2="ADVERTENCIA" y aplica formato amarillo.
Resultado: El sistema ahora distingue entre estado normal, advertencia y alerta crítica mediante colores distintos.
Ejercicio propuesto
Extiende el sistema de control de inventario. Añade una nueva columna, «Días de Stock Restante», calculada como =B2/Consumo_Diario (asume un consumo diario fijo de 100 unidades para todos los productos). Luego, aplica un tercer nivel de formato condicional: si los «Días de Stock Restante» son menores a 5, pinta la celda de color naranja. Esto te obligará a anidar o encadenar reglas de formato condicional basadas en cálculos derivados.
Errores habituales
- Error de Referencia Absoluta en Formato Condicional: El error más común es olvidar el signo de dólar ($) al definir la fórmula en el Formato Condicional (ej. usar `=D2=»ALERTA CRÍTICA»` en lugar de `=$D2=»ALERTA CRÍTICA»`). Solución: Siempre fija la columna (ej. $D2) para que la regla se aplique correctamente a todas las filas seleccionadas.
- Confusión entre SI y Formato Condicional: Intentar hacer que el formato cambie basándose en el texto de la celda, pero sin usar la función SI. Solución: La función SI debe generar el *valor* (el texto «ALERTA»), y el Formato Condicional debe leer ese *valor* para aplicar el *estilo*. Son pasos secuenciales.
- Fórmulas de SI anidadas complejas: Al intentar manejar tres o más estados (OK, Advertencia, Crítico) en una sola función SI, los usuarios cometen errores de sintaxis. Solución: Para múltiples condiciones, es más limpio usar la función
SI.CONJUNTO(IFS) en lugar de anidar múltiplesSI(Condición1; Resultado1; SI(Condición2; Resultado2; ResultadoFinal)).
Deja una respuesta