Cómo aplicar formato condicional en Excel para comparar valores de fila a fila

El formato condicional en Microsoft Excel ha existido por mucho tiempo, pero encuentro 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 a 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 que compare filas que comparar columnas. En este artículo, utilizaremos una regla para comparar valores de fila a fila. Pero el quid de la cuestión es que, cada vez que compares un valor con otro, ya sea por columnas o por filas, es posible que no obtengas los resultados que esperas.

Estoy utilizando Microsoft 365 en un sistema Windows 10 de 64 bits. (Trata de no actualizar a Windows 11 hasta que se solucionen todos los problemas). Excel Online mostrará los formatos condicionales existentes y te permitirá agregar reglas incorporadas. Sin embargo, no puedes aplicar expresiones como reglas. Para tu comodidad, puedes descargar los archivos de demostración .xlsx y .xls.

El requisito de formato

Mantengamos esto lo más simple posible por ahora. Supongamos que quieres saber cuándo una comisión parece ser un poco más baja de lo habitual. La Figura A muestra una hoja de seguimiento simple para ventas y comisiones de cinco empleados utilizando un objeto Tabla, aunque también puedes hacer lo mismo utilizando un rango de datos normal. Supongamos que quieres saber si un porcentaje es menor de lo esperado, en comparación con la comisión de la fila anterior. Ahora, esto realmente no nos dará el análisis que realmente necesitaríamos en esta situación, pero nos da un ejemplo un poco forzado de comparar valores de fila a fila. Lidiaremos con la falla lógica más adelante.

Figura A

Para lograr este requisito simple, resaltaremos los montos de comisión que son un 25% más bajos que la fila anterior:

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

Figura B

Enseñanza y Aprendizaje con Microsoft: Herramientas y Lecciones para Profesores de todos los Niveles

Figura C

Con una revisión rápida, todo parece estar bien, excepto el valor en E11. Al compararlo 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. Recuerda, no estamos comparando el conjunto completo de valores de comisión en su totalidad. Los estamos comparando de una fila a otra. Sí, no funciona tan bien como nos gustaría porque es un poco forzado, a propósito, para que puedas ver lo fácil que es pensar que tienes la lógica correcta, solo para encontrar un error.

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

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

Cómo modificar la regla

Al agregar una celda de entrada, puedes actualizar rápidamente el porcentaje utilizado en la regla condicional. Hagámoslo ahora:

1. Haz clic en la pestaña Inicio, haz clic en Formato condicional en el grupo Estilos y luego elige Administrar reglas en la lista desplegable.
2. En el menú desplegable Mostrar reglas de formato, elige Esta hoja de cálculo.
3. En el panel resultante, selecciona la regla y elige Editar regla en la fila del menú superior.
4. Haz clic dentro de la regla y presiona F2 para poder editarla fácilmente.
5. Reemplaza el valor 0.25 con una referencia absoluta a $C$1 (Figura D).
6. Haz clic en Aceptar dos veces para aplicar los cambios y volver a la hoja.

Figura D

Guía de Examen de Certificación Lotus Notes: Desarrollo de Aplicaciones y Administración del Sistema

La regla no funcionará correctamente hasta que ingreses un valor de entrada en C1, así que ingresa .25 ahora. La regla resalta los mismos registros que antes. Cambia .25 a .10 para ver cómo se actualiza. Como se puede ver en Figura E, la regla incluye un registro más, fila 4. A medida que disminuyas el porcentaje, es posible que aumentes el número de registros resaltados y viceversa. Si quieres resaltar registros que excedan el registro siguiente en un porcentaje específico, simplemente cambia el signo < en la regla por >. De hecho, podrías utilizar ambas reglas, pero podrías elegir un color diferente, como el verde, para resaltar comisiones más altas de lo habitual.

Figura E

Cómo corregir la lógica

Tal como está, la regla no funciona como queremos porque estamos comparando una fila con otra e ignorando el conjunto completo, es decir, la comisión promedio. Recomiendo que utilices 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 considerar es cómo incluir la comisión promedio de manera rápida y sencilla en nuestra nueva regla. Podríamos agregarlo directamente a la regla, pero terminaríamos con una regla compleja. La forma más fácil es ingresar la función de promedio en C2:

=PROMEDIO(Table12[Comisión])

Si no estás utilizando un objeto Tabla, ingresa:

Cómo gestionar eficazmente grandes volúmenes de correo electrónico

=PROMEDIO(E4:E14)

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

1. Selecciona el conjunto de datos y utiliza el formato condicional para agregar una nueva regla, como hiciste anteriormente.
2. Ingresa la expresión =$E4<($C$2-($C$2*$C$1)). Recuerda, el punto no está incluido en la expresión y la referencia absoluta/relativa es importante.
3. Haz clic en el botón Formato, aplica un color de relleno (Yo elegí el amarillo) y haz 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 de fila a fila al hacerlo, pero esto suele suceder. Por eso comenzamos con un ejemplo tan forzado. Esto podría suceder de la misma manera al comparar columna con columna.

Figura F

Como antes, puedes cambiar el porcentaje en C1 para actualizar toda la vista. Además, si deseas ver comisiones que excedan el promedio en un porcentaje específico (C1), cambia el signo < en la regla por >. Finalmente, debido a que estamos utilizando un objeto Tabla, todo es dinámico. La función PROMEDIO() se actualizará automáticamente a medida que modifiques, agregues o elimines registros. Si estás utilizando un rango de datos normal, eso no sucederá.

Es posible que te preguntes por qué comencé con un ejemplo de fila a 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 crítica. Además, al igual que con las reglas más simples, a veces no son adecuadas y debes replantear tu primer intento. No hay nada de malo en eso; lo hago todo el tiempo.

Integración de StarOffice: calendarios

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Cómo aplicar formato condicional en Excel para comparar valores de fila a fila , tenemos lo ultimo en tecnología 2023.