Las reglas son fórmulas TRUE/FALSE

La idea

Tres funciones de este capítulo parecen no tener relación: resaltar celdas, filtrar filas y validar lo que escribe la gente. Comparten un mismo mecanismo. Cada una es una fórmula que Excel evalúa para una sola fila o celda y que responde TRUE o FALSE. TRUE significa "aplica el color", "conserva la fila" o "acepta la entrada".

Cuando ves eso, los cuadros de diálogo pierden su misterio. Mayor que, menor que, por encima del promedio, "Usar una fórmula que determine las celdas para aplicar formato": todas son =E2>500 con una etiqueta más amable.

Por qué existe

Una hoja en la que otras personas escriben y leen necesita reglas, porque nadie revisa cuatro mil filas a ojo. El color hace el barrido. Un filtro reduce la lista a lo que preguntas. La validación detiene el valor malo en la puerta, lo que sale más barato que encontrarlo después en un informe.

La razón para escribirlas como fórmulas, y no para pulsar valores predefinidos, es que una fórmula se puede probar. Pon primero la regla en una columna libre y rellénala hacia abajo: una columna de TRUE y FALSE muestra exactamente qué filas se iluminarán, y puedes comprobar tres de ellas a mano antes de comprometerte con la regla.

Cómo funciona por dentro

Escribe la regla para la celda superior izquierda

Cuando defines una regla de formato condicional o de validación sobre un bloque, Excel evalúa tu fórmula una vez por celda, desplazando las referencias relativas sobre la marcha, exactamente como al rellenar hacia abajo. Por eso la fórmula se escribe siempre como si fuera para la primera celda del rango. Selecciona E2:E38, escribe =E2>500, y Excel prueba E3>500 para la fila 3, y así sucesivamente.

Valen las reglas del primer capítulo: las partes relativas siguen a la celda, $ congela lo que debe quedarse. =E2>AVERAGE($E$2:$E$38) compara cada celda con el mismo rango fijo. Una regla de "usar una fórmula" sobre toda una cuadrícula suele necesitar referencias mixtas, por ejemplo para resaltar toda la fila cuando una columna cumple una condición: =$E2>500 congela la columna y deja que la fila se mueva, así que cada celda de la fila recibe la misma respuesta.

Filtros y ordenaciones con una columna de criterio

Un filtro conserva las filas donde una condición es TRUE y oculta las demás, sin cambiarlas. Una forma fiable de construir un filtro que puedas comprobar es una columna auxiliar con la condición como fórmula, y luego filtrar por TRUE. En una ordenación, el cuadro de diálogo ordena según el orden de los niveles que listes, así que "por categoría y luego por ingresos" significa que los ingresos solo desempatan dentro de cada categoría. Ordenar una sola columna por separado desbarata las filas, así que selecciona todo el bloque o trabaja en una Tabla.

Los tipos de datos deciden qué regla aplica

Antes de escribir una regla, pregúntate de qué tipo es la columna. Los números pueden ser discretos (conteos) o continuos (precios); las categorías pueden ser nominales (canal) u ordinales (pequeño, mediano, grande, que tienen un orden); y algunas columnas, como order_id, parecen números pero son etiquetas. Promediar un id no tiene sentido, y ordenar alfabéticamente una categoría nominal sugiere un orden que no existe.

La validación es una puerta con agujeros

La validación se ejecuta cuando alguien escribe en la celda.

No comprueba lo que se pega sobre la celda, y no corrige datos antiguos de forma retroactiva. Trátala como la primera línea de defensa y añade una columna que aplique las mismas reglas a cada fila como auditoría TRUE/FALSE. Las filas que fallan son las que un formulario validado habría rechazado.

Las Tablas hacen crecer los rangos

Un rango normal es un rectángulo fijo. Una Tabla es un rango que Excel vigila: tiene nombre, fila de encabezado y formato automático, y crece cuando escribes debajo. Las fórmulas pueden referirse a las columnas por nombre (Lines[line_total]), así que cubren todas las filas, también las añadidas más tarde. El formato condicional, la validación y las fórmulas que definas en una columna de una Tabla se extienden solos a las filas nuevas.

Las segmentaciones de datos (slicers) son botones que filtran una Tabla. Son una forma más cómoda de ejecutar el filtro que ya entiendes.

Comprobar una regla antes de fiarte

Ejecuta la regla sobre unas filas que puedas juzgar a ojo: una que claramente pasa, una que claramente falla y una justo en el límite. La fila del límite es donde se equivocan las reglas. ¿"Entre 5 y 500" permite el 500? >= y > dan respuestas distintas ahí, y quien define la regla necesita saber cuál quería el negocio. Anota la decisión junto a la celda del umbral, para que la siguiente persona no tenga que adivinar. Guarda también los umbrales en sus propias celdas, bloqueadas con $, de modo que cambiar la regla sea cambiar un número y no editar la fórmula.

Errores comunes

"El formato condicional cambia los datos"

Solo cambia cómo se ve una celda. Una celda roja guarda el mismo valor. Para contar o sumar las celdas resaltadas necesitas la misma condición escrita como fórmula, con COUNTIF o una columna auxiliar, porque las funciones no pueden ver los colores.

"La validación garantiza datos limpios"

Detiene los errores escritos en la celda. Los valores pegados, los archivos importados y todo lo introducido antes de que existiera la regla se la saltan. Audita con una columna de fórmulas y trata la validación como una cortesía hacia quien escribe.

"Una Tabla es solo un rango con estilo"

El estilo es accesorio. Lo que importa es que la Tabla conoce sus propios límites. Las fórmulas que se refieren a ella por nombre siguen siendo correctas cuando llegan y salen filas. Esa única propiedad elimina el error de "mi total se dejó la última fila".