El formato condicional de Excel puede mostrar íconos junto a los valores, como flechas que indican si una cifra aumentó o disminuyó, o semáforos que alertan sobre el cumplimiento de una meta. Por defecto los íconos se asignan por percentiles, de modo que casi siempre hay que configurar los límites. ¿Cómo se hace y cómo se calculan la variación y el porcentaje de cumplimiento?
Calcular la variación y el cumplimiento.
Antes de aplicar los íconos hay que tener el valor que van a representar. Los dos casos más frecuentes son estos:
- Variación porcentual: cuánto cambió una cifra de un periodo a otro, =(C5-B5)/B5, con las ventas del año anterior en B5 y las del actual en C5.
- Porcentaje de cumplimiento: qué parte de la meta se alcanzó, =C5/E5, con las ventas reales en C5 y la meta en E5. Un resultado de 104,5 % indica que se superó la meta.
Ambas celdas deben tener formato de porcentaje. Los cálculos con porcentajes se explican en el artículo sobre las fórmulas para sacar porcentajes en Excel.
- Liquidador de pensiones en Colpensiones
- Liquidador de intereses propiedad horizontal
- Proyección y planeación pensional
Flechas de aumento o disminución.
Para mostrar una flecha verde hacia arriba si la variación es positiva, un guion amarillo si es cero y una flecha roja hacia abajo si es negativa:
- Seleccionar el rango de variaciones, por ejemplo D5:D10.
- Ir a «Inicio», «Formato condicional», «Conjuntos de íconos» y elegir las tres flechas de colores.
- Con el rango seleccionado, ir a «Formato condicional», «Administrar reglas», seleccionar la regla y hacer clic en «Editar regla».
- En la parte inferior, cambiar el «Tipo» de cada ícono a «Número».
- Para la flecha verde, elegir el operador «>» y el valor 0; para la amarilla, «>=» y 0. La roja se aplica a todo lo demás, es decir, a los valores negativos.
- Si se prefiere un guion para el valor cero, se cambia el ícono amarillo en la lista desplegable de íconos. Aceptar.
El cambio de «Tipo» es el paso clave. Por defecto Excel usa «Porcentual», que no es el valor de la celda sino su posición en el rango: el tercio superior recibe la flecha verde aunque todas las variaciones sean negativas.
Semáforo de alertas de cumplimiento.
Con el mismo procedimiento se arman las alertas de cumplimiento. Supongamos esta regla:
| Nivel | Condición | Ícono |
|---|---|---|
| Cumple | 100 % o más | Verde |
| Cumplimiento moderado | Desde 80 % y menos de 100 % | Amarillo |
| Incumple | Menos de 80 % | Rojo |
Se selecciona el rango de cumplimiento, se elige el conjunto de íconos de semáforo y, al editar la regla, se cambia el «Tipo» a «Número»: el verde con «>=» y 1, y el amarillo con «>=» y 0,8. Los valores se escriben como decimales (1 y 0,8), no como 100 y 80, porque un porcentaje es un número dividido entre 100.
Si además se quiere el estado en texto, para filtrar o para un informe, se agrega una columna con SI anidados:
=SI(F5>=1;"Cumple";SI(F5>=0,8;"Moderado";"Incumple"))El orden de las condiciones importa: primero se pregunta por el nivel más alto, porque un cumplimiento de 105 % también es mayor o igual que 0,8.
Mostrar solo el ícono.
En la misma ventana de edición de la regla está la casilla «Mostrar icono únicamente», que oculta el número y deja solo el ícono. Es útil en tableros, pero conviene dejar el valor visible cuando el informe se va a leer en detalle.
El símbolo de variación.
Para escribir el símbolo delta (Δ), que se usa para indicar variación, puede insertarse desde «Insertar», «Símbolo», o con la función UNICAR:
=UNICAR(916)
Del mismo modo, =UNICAR(9650) devuelve un triángulo hacia arriba (▲) y =UNICAR(9660), uno hacia abajo (▼), que pueden combinarse con la variación en una fórmula, sin formato condicional. Otros símbolos y flechas se explican en el artículo sobre las flechas en Excel.
Descargar el ejemplo en Excel.
El archivo tiene una tabla de ventas de seis vendedores con la variación frente al año anterior, marcada con flechas, y el porcentaje de cumplimiento de la meta, marcado con semáforos configurados por número. Incluye también la columna de estado en texto y la variación con triángulos de UNICAR. Descargar archivo.
Preguntas frecuentes.
A continuación respondemos las preguntas frecuentes de nuestros lectores.
¿Cómo pongo flechas de aumento o disminución en Excel?
Con «Formato condicional», «Conjuntos de íconos», flechas, y editando la regla para que el «Tipo» sea «Número» con límite 0.
¿Por qué las flechas no corresponden a los valores?
Porque la regla usa el «Tipo» porcentual, que asigna los íconos por posición en el rango. Hay que cambiarlo a «Número».
¿Cuál es la fórmula del porcentaje de cumplimiento?
El valor real dividido entre la meta: =real/meta, con formato de porcentaje.
¿Cómo hago una alerta de colores en Excel?
Con un conjunto de íconos de semáforo, o con reglas de formato condicional que cambien el color de relleno según el valor.
¿Cómo escribo el símbolo de variación Δ?
Con =UNICAR(916) o desde «Insertar», «Símbolo».
Twitter
Facebook
WhatsApp
LinkedIn