Cómo usar el formato condicional para comparar valores de fila a fila, o no, en Excel

Una regla de formato fila por fila es tan fácil de aplicar como una columna por columna en Microsoft Excel, pero es posible que no obtenga los resultados que esperaba.

El formato condicional en Microsoft Excel existe desde hace mucho tiempo, pero creo que la mayoría de las reglas evalúan columnas; rara vez es tan simple, pero es justo decir que la mayoría de las reglas condicionales comparan valores de una columna con otra. Es inherente a la forma en que estructuramos los datos. Sin embargo, ocasionalmente necesitamos comparar valores de una fila a otra. La buena noticia es que no es más difícil implementar una regla de formato de comparación de filas que comparar columnas. En este artículo, usaremos una regla para comparar valores de una fila a otra. Pero el quid es que cada vez que compara un valor con otro, ya sea por columnas o filas, es posible que no obtenga los resultados que espera.

VER: 83 consejos de Excel que todo usuario debe dominar (TechRepublic)

Uso Microsoft 365 en un sistema Windows 10 de 64 bits. (Intente posponer la actualización a Windows 11 hasta que se resuelvan todos los problemas). Excel Online mostrará los formatos condicionales existentes y le permitirá agregar reglas integradas. Sin embargo, no puede aplicar expresiones como reglas. Para su comodidad, puede descargar los archivos de demostración .xlsx y .xls.

El requisito de formato

Mantengamos esto lo más simple posible por ahora. Supongamos que desea saber cuándo una comisión parece un poco más baja de lo habitual. Figura A muestra una hoja de seguimiento simple de ventas y comisiones para cinco empleados usando un objeto Tabla, aunque puede hacer lo mismo usando un rango de datos normal. Supongamos que desea saber si un porcentaje es más bajo de lo esperado, en comparación con la comisión en la fila de arriba. Ahora, esto realmente no nos dará el análisis que realmente necesitaríamos en esta situación, pero nos da un ejemplo un poco artificial para comparar valores de una fila a otra. Nos ocuparemos de la falla lógica más tarde.

Figura A

Para cumplir con este simple requisito, resaltemos los montos de las comisiones que son un 25 % más bajos que la fila anterior:

1. Seleccione el conjunto de datos, en este caso, B4:E14. Si desea resaltar solo la celda de comisión, seleccione solo esa columna de valores. Pero para este ejemplo, quiero resaltar todo el registro.
2. Haga clic en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y luego elija Nueva regla en la lista desplegable.
3. En el panel superior, seleccione Usar una fórmula para determinar a qué celdas dar formato.
4. En el panel inferior, ingrese la expresión =$E4<($E5 - ($E5 *0.25)). El punto que sigue a los caracteres )) es puntuación y no forma parte de la expresión.
5. Haga clic en el botón Formato, haga clic en la pestaña Relleno y luego elija un color de resaltado. Elijo rojo porque estamos buscando valores más bajos.
6. Haga clic en Aceptar para volver al primer panel, que muestra la regla y el formato (Figura B).
7. Haga clic en Aceptar para aplicar el formato y volver a la hoja, que se muestra en Figura C. Por ahora, no se preocupe por las celdas de la fila 1. Las usaremos en un momento.

Figura B

Figura C

Con una revisión rápida, todo parece estar bien, excepto por el valor en E11. En comparación con las otras comisiones, parece estar en línea. El problema es el valor con el que se compara: $500 en E12. La regla está funcionando como debería. Recuerde, no estamos comparando el conjunto completo de valores de comisión como un todo. Los estamos comparando de una fila a otra. Sí, no funciona tan bien como nos gustaría porque está un poco artificial, a propósito, por lo que puede ver lo fácil que es pensar que tiene la lógica correcta, solo para encontrar una falla.

Es importante tener en cuenta que la referencia absoluta para la columna E es esencial si desea formatear la celda completa. Del mismo modo, la fila debe ser relativa.

En este punto, es posible que se pregunte acerca de las celdas formateadas en la fila 1. C1 es una celda de entrada. Al hacer referencia al porcentaje de esta manera, puede cambiar el porcentaje sobre la marcha sin modificar la regla real.

Cómo modificar la regla

Al agregar una celda de entrada, puede actualizar rápidamente el porcentaje utilizado en la regla condicional. Hagamos eso ahora:

1. Haga clic en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y luego seleccione Administrar reglas en la lista desplegable.
2. En el menú desplegable Mostrar reglas de formato, elija Esta hoja de trabajo.
3. En el panel resultante, seleccione la regla y elija Editar regla en la fila de menú anterior.
4. Haga clic dentro de la regla y presione F2 para que pueda editarla fácilmente.
5. Reemplace el valor 0.25 con una referencia absoluta a $C$1 (Figura D).
6. Haga clic en Aceptar dos veces para aplicar y volver a la hoja.

Figura D

La regla no funcionará correctamente hasta que ingrese un valor de entrada en C1, así que ingrese .25 ahora. La regla resalta los mismos registros que antes. Cambie .25 a .10 para que pueda verlo actualizado. Como puedes ver en Figura Ela regla incluye un registro más, la fila 4. A medida que más bajo el porcentaje, puede aumentar el número de registros resaltados y viceversa. Si desea resaltar los registros que superan el registro a continuación en un porcentaje específico, simplemente cambie el signo < en la regla a >. De hecho, podría emplear ambas reglas, pero podría elegir un color diferente, como el verde, para resaltar las comisiones más altas de lo habitual.

Figura E

Cómo corregir la lógica

Tal como está, la regla no funciona de la manera que quisiéramos porque estamos comparando una fila con otra e ignorando el todo; de lo contrario, el promedio comisión. Le recomiendo que utilice la opción Administrar reglas para eliminar la regla actual antes de continuar. No es absolutamente necesario, pero será más fácil ver los nuevos resultados.

Lo primero que debemos tener en cuenta es cómo incluir la comisión media de forma rápida y sencilla en nuestra nueva regla. Podríamos agregarlo a la regla directamente, pero terminaremos con una regla compleja. La forma más fácil es ingresar la función de promedio en C2

=AVERAGE(Table12[Commission])

Si no está usando un objeto Tabla, ingrese

=AVERAGE(E4:E14)

Ahora, arreglemos nuestra lógica usando una nueva regla:

1. Seleccione el conjunto de datos y use Formato condicional para agregar una nueva regla, como lo hizo anteriormente.
2. Ingrese la expresión =$E4<($C$2-($C$2*$C$1)). Recuerde, el período no está incluido en la expresión y la referencia absoluta/relativa es importante.
3. Haga clic en el botón Formato, aplique un color de relleno (elegí amarillo) y haga clic en Aceptar dos veces para volver a la hoja.

Como puedes ver en Figura F, esta nueva regla resalta solo dos registros. La nueva lógica es más precisa para nuestras necesidades. Pero hemos eliminado por completo la referencia fila por fila al hacerlo, pero esto sucede a menudo. Es por eso que comencé con un ejemplo tan artificial. Esto podría suceder fácilmente al comparar columna con columna.

Figura F

Como antes, puede cambiar el porcentaje en C1 para actualizar la vista completa. Además, si desea ver comisiones que superan el promedio en un porcentaje específico (C1), cambie el < en la regla a >. Finalmente, debido a que estamos usando un objeto Tabla, todo es dinámico. La función PROMEDIO() se actualizará automáticamente a medida que modifique, agregue o elimine registros. Si está utilizando un rango de datos normal, eso no sucederá.

Quizás se pregunte por qué comencé con un ejemplo fila por fila solo para eliminarlo por completo. Primero, quería un ejemplo súper fácil para introducir la idea de evaluar una regla de formato por filas. Como mencioné dos veces, la referencia es fundamental. Además, como ocurre con las reglas más sencillas, a veces no son adecuadas y es necesario replantearse el primer esfuerzo. No hay nada de malo en eso; Lo hago todo el tiempo.