Imagen: Mariakray, iStock/Getty Images

Es fácil resaltar un registro en Microsoft Excel usando el formato condicional. Normalmente, la condición se compara con un valor existente en el conjunto de datos. ¿Es este valor mayor que, menor que o igual a "esto" o "eso"? Puede resaltar el valor, parte del registro o todo el registro. Incluso puede resaltar un valor diferente basado en otro, ambos en el conjunto de datos. Lo que no puedes hacer fácilmente es cambiar la condición. Le mostraré cómo agregar una celda de entrada en Excel a la que se hace referencia en la regla de formato condicional, lo que le permitirá cambiar una condición sobre la marcha, sin cambiar la regla en sí.

Para implementar esta técnica, necesita datos, una lista única de valores de filtro, una verificación de validación de datos y una regla condicional. Esto puede sonar complejo, pero no lo es. Supongo que conoce la funcionalidad básica, como insertar filas, crear un objeto de tabla, ordenar datos, etc.

Uso Microsoft Excel en Office 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores. Puede trabajar con sus propios datos o descargar el archivo de demostración .xlsx y .xls. Esta técnica no es apropiada para la edición del navegador.

APRENDE MÁS: Precios y características de Office 365 para consumidores

Índice

Cómo configurarlo

El conjunto de datos simple presentado en Figura A tiene el formato de un objeto de tabla y almacena 45 filas de información del producto (que copié de la base de datos de Access Northwind). Usé un objeto Tabla porque quiero que toda la técnica sea lo más dinámica posible, pero no es necesario usar un objeto Tabla. (Para crear una tabla, haga clic en cualquier parte del conjunto de datos, haga clic en la pestaña Insertar, haga clic en Tabla en el grupo Tablas y luego haga clic en Aceptar).

Figura A

Crearemos una expresión de formato condicional dinámico para resaltar registros específicos en este conjunto de datos.

Suponga que desea resaltar productos cuyo valor de Unidades en stock es menor o igual que el valor del Nivel de pedido del producto; sin embargo, no desea mostrarlos todos, desea mostrar los productos que cumplen esta condición por categorías. En otras palabras, desea ver las bebidas o condimentos que deben pedirse.

Los filtros incorporados no pueden ayudar, ni tampoco un formato condicional por sí mismo. Pero puede combinar un control de lista con un formato condicional para crear un formato condicional Después siempre que sea de otro modo.

Cómo organizar la lista

Lo primero que necesitamos es un control que muestre categorías en una lista desplegable. El valor seleccionado se almacenará en la celda subyacente y la regla condicional se referirá a esta celda. ¡Magia (casi)!
La lista debe ser un conjunto de valores únicos. Puede ingresar la lista de categorías manualmente (es bastante fácil obtenerlas todas de nuestro conjunto de datos simple), o puede dejar que Excel lo haga por usted, asegurándose de que no se haya perdido ninguna. Para ello, utilizará la función de filtro avanzado. En este punto, no puede copiar un conjunto de filtros avanzados a otra hoja. La funcionalidad solo se copia en la hoja activa (más o menos). Si comienza con la hoja de destino en lugar de la hoja de origen, funciona bien. Tendrá sentido en un minuto.
Puede copiar la lista en cualquier lugar que desee, pero elija una ubicación separada. Elegí una hoja específicamente dedicada a listas de este tipo, y se llamó acertadamente: Listas. Copiaremos un solo conjunto de categorías del conjunto de datos a la hoja Listas de la siguiente manera.

  1. Tenga en cuenta el rango de categorías: lo necesitará en un momento. Para nuestros datos de demostración, es $G$2:$G$46.

  2. Seleccione una ubicación aislada para la lista. Seleccione B2 en la hoja llamada Listas.

  3. Haga clic en la pestaña Datos, luego haga clic en Avanzado en el grupo Ordenar y filtrar para mostrar el cuadro de diálogo Filtro avanzado.

  4. En Configuración de acción, haga clic en Copiar a otra ubicación.

  5. Para el rango de Lista, ingrese $G$2 :$G$46 o Table1[Category] si está utilizando un objeto Tabla. O haga clic en la pestaña Hoja y resalte manualmente los valores.

  6. En el rango Copiar a, ingrese la celda ancla de la lista única. En este caso, es Lists !$B$2.

  7. Haga clic en la opción Solo registros únicos (Figura B), luego haga clic en Aceptar.

  8. Ordene la lista resultante si desea que la lista desplegable muestre una lista alfabética. Asigne a la lista una celda de encabezado y formatéela como un objeto de tabla (Figura C).

Figura B

Identifique el listado original y la ubicación del listado único.

Figura C

La función de filtro avanzado de Excel crea una lista única de valores de categoría.

El siguiente paso es incrustar un control de lista que mostrará el conjunto único de categorías que acaba de crear.

Cómo controlar la lista

Necesitamos un control de lista que permita a los usuarios seleccionar una categoría específica, y el lugar más lógico es arriba del encabezado de la categoría. Para hacer esto, inserte algunas filas arriba del conjunto de datos. (Seleccione una fila, haga clic con el botón derecho en la selección y elija Insertar. Seleccioné tres filas). Con filas en blanco sobre el conjunto de datos, está listo para agregar el control de lista de la siguiente manera.

  1. Seleccione G1. Luego haga clic en la pestaña Datos y elija Validación de datos de la lista desplegable Validación de datos en el grupo Herramientas de datos.

  2. En la lista desplegable Permitir del cuadro de diálogo resultante, seleccione Lista.

  3. En el cuadro de diálogo fuente, identifique la lista de categorías (Figura D) y haga clic en Aceptar. El control resultante se muestra en Figura E.

Figura D

Cree un control de validación de datos con los valores de categoría.

Figura E

Este control de lista muestra una única lista de categorías.

Cuando selecciona una categoría de la lista Validación de datos, ese valor se almacena en la celda G1. Deberá hacer referencia a esta celda en la regla de formato condicional, que vendrá a continuación.

Puede recordar que mencioné que el conjunto de datos y la lista son objetos de tabla. He aquí por qué: si actualiza la lista de categorías únicas, la lista de validación de datos se actualizará automáticamente. No es obligatorio que la técnica funcione, pero definitivamente es una buena ventaja. Ahora pasemos a la regla de formato condicional.

Cómo usar el formato condicional

El requisito básico es simple: queremos resaltar los registros cuando es necesario ordenarlos. Podemos escribir esto como una expresión simple:

Unidades en Stock

Cuando la cantidad en stock es menor o igual que la cantidad del umbral de pedido, la expresión es verdadera; de lo contrario, la expresión es falsa. ¡Pero espera hay mas! También hay que tener en cuenta la categoría. Queremos resaltar solo los registros donde esta expresión es verdadera, y el valor de categoría del registro coincide con el de G1. Podemos escribir esto como otra expresión verdadera o falsa:

Categoría=valor seleccionado en la comprobación de validación de datos

Al usar el operador AND, podemos combinar las dos condiciones para crear una expresión que devuelva verdadero solamente cuando los dos las condiciones son verdaderas:

=Y($D5

El último paso es agregar esta expresión de formato condicional de la siguiente manera.

  1. Seleccione el conjunto de datos: B5:G49. Si no desea resaltar toda la fila, resalte las columnas que desea resaltar.

  2. En la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y seleccione Nueva regla en 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 esta fórmula: AND ($ D5. (El punto es gramatical, no es parte de la fórmula).

  5. Haga clic en Formato. En el siguiente panel, haga clic en la pestaña Relleno, elija un color y haga clic en Aceptar. Figura F muestra la fórmula y el formato. Haga clic en Aceptar para volver a la hoja de cálculo.

Figura F

Establezca el formato condicional que se aplicará cuando la expresión devuelva verdadero.

Como puedes ver en figura g, la selección en el control de validación de datos es Condimentos y la regla condicional resalta dos registros: Jarabe de anís y Chang deben reorganizarse. Use la verificación de validación de datos para cambiar el valor de la categoría y observe cómo la regla resalta registros diferentes (o tal vez ninguno).

figura g

El formato condicional destaca los productos por categoría que deben reorganizarse.

Sobre la marcha

La capacidad de modificar un valor evaluado por una regla de formato condicional le brinda una gran flexibilidad. Si implementa esta técnica, comparta su experiencia en la sección de comentarios.

Envíame tu pregunta sobre Microsoft Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantías. No envíe archivos a menos que se le solicite; las solicitudes de ayuda iniciales que lleguen con archivos adjuntos se eliminarán sin ser leídas. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se comunique conmigo, sea lo más específico posible. Por ejemplo, "Por favor, resuelva los problemas de mi libro de trabajo y corrija lo que está mal" probablemente no obtendrá una respuesta, pero "¿Puede decirme por qué esta fórmula no arroja los resultados esperados?" fuerza. Mencione la aplicación y la versión que está utilizando. TechRepublic no me reembolsa por mi tiempo o experiencia cuando ayudo a los lectores, ni cobro una tarifa por los lectores a los que ayudo. Puede ponerse en contacto conmigo en [email protected]