Imagen: iStockphoto/filmfoto

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, a veces necesitamos comparar valores de fila a fila. La buena noticia es que implementar una regla de formato de comparación de filas no es más difícil que comparar columnas. En este artículo, usaremos una regla para comparar valores entre filas. Pero el punto crucial es que cada vez que compare un valor con otro, ya sea por columnas o filas, es posible que no obtenga los resultados esperados.

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

Estoy usando Microsoft 365 en un sistema Windows 10 de 64 bits. (Intente retrasar 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.

Índice
  • El requisito de formato
  • Cómo editar la regla
  • Cómo corregir la lógica
  • El requisito de formato

    Mantengamos esto lo más simple posible por ahora. Suponga que desea saber cuándo una comisión parece un poco más baja de lo habitual. Figura A muestra una hoja de seguimiento de comisiones y ventas simple para cinco empleados usando un objeto Table, aunque podría hacer lo mismo usando un rango de datos normal. Suponga que desea saber si un porcentaje es más bajo de lo que esperaba, en comparación con la comisión de línea 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 artificial para comparar valores de una fila a otra. Nos ocuparemos de la falla lógica más adelante.

    Figura A

    Comparemos las comisiones de línea a línea.

    Para cumplir con este simple requisito, destaquemos los montos de comisión que están un 25% por debajo de la línea de arriba:

    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, luego elija Nueva regla de la lista desplegable.
    3. En el panel superior, seleccione Usar fórmula para determinar a qué celdas dar formato.
    4. En el panel inferior, ingrese la expresión =$E4<($E5 - ($E5 *0.25)). Este punto que sigue a los caracteres )) es un signo de 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 más adelante.

    Figura B

    La regla y el formato.

    Figura C

    El formato destaca cuatro líneas.

    Con una revisión rápida, todo se ve bien excepto por el valor E11. En comparación con las otras comisiones, esto parece bastante consistente. El problema es el valor con el que se compara: $500 en E12. La regla funciona como debería. Recuerde que no estamos comparando el conjunto completo de valores de comisión como un todo. Los comparamos fila a fila. 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 laguna.

    Es importante tener en cuenta que la referencia absoluta de la columna E es esencial si desea formatear toda la celda. Además, la línea 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 cambiar la regla real.

    Cómo editar la regla

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

    1. Haga clic en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos, luego elija Administrar reglas en la lista desplegable.
    2. En la lista desplegable Mostrar reglas de formato, elija Esta hoja de cálculo.
    3. En el panel resultante, seleccione la regla y elija Editar regla en la línea de menú anterior.
    4. Haga clic dentro de la regla y presione F2 para poder 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 nueva regla aún no funciona correctamente porque no hay ningún valor en C1.

    La regla no funcionará correctamente hasta que ingrese un valor de entrada en C1, así que ingrese 0.25 ahora. La regla resalta los mismos registros que antes. Cambie .25 a .10 para que pueda ver cómo se actualiza. Como puedes ver en Figura Ela regla incluye un registro adicional, la línea 4. Cuando usted más bajo 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 reemplace el signo < dans la règle par >. De hecho, puede usar ambas reglas, pero puede elegir un color diferente, como el verde, para resaltar comisiones más altas de lo habitual.

    Figura E

    Al reducir el porcentaje, aumenta el potencial para agregar nuevos registros.

    Cómo corregir la lógica

    Tal como está, la regla no funciona de la manera que nos gustaría porque estamos comparando una fila con otra e ignorando todo. De lo contrario, el medio comisión. Le recomiendo que utilice la opción Administrar reglas para eliminar la regla actual antes de continuar. No es absolutamente necesario, pero hará que sea más fácil ver los nuevos resultados.

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

    =AVERAGE(Table12[Commission])

    Si no está utilizando un objeto Tabla, ingrese

    =AVERAGE(E4:E14)

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

    1. Seleccione el conjunto de datos y use el formato condicional para agregar una nueva regla, como lo hizo anteriormente.
    2. Introducir expresión =$E4<($C$2-($C$2*$C$1)). Recuerde que el punto no está incluido en la expresión y que 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 solo destaca dos registros. La nueva lógica es más precisa para nuestras necesidades. Pero hemos eliminado por completo la referencia línea por línea al hacerlo, pero eso sucede mucho. Es por eso que comencé con un ejemplo tan artificial. Esto podría suceder fácilmente al comparar columna con columna.

    Figura F

    Comparar cada valor con la comisión promedio nos brinda una imagen más precisa.

    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), reemplace el < dans la règle par >. Finalmente, dado que estamos usando un objeto Tabla, todo es dinámico. La función PROMEDIO() se actualizará automáticamente cuando modifique, agregue o elimine registros. Si está utilizando un rango de datos normal, esto no sucederá.

    Quizás se pregunte por qué comencé con un ejemplo línea por línea para eliminarlo por completo. Primero, quería un ejemplo súper simple para presentar la idea de evaluar una regla de formato por líneas. Como mencioné dos veces, el SEO es clave. Además, como ocurre con las reglas más sencillas, a veces simplemente no son adecuadas y es necesario replantearse el primer esfuerzo. No hay nada de malo en eso; Lo hago todo el tiempo.