Filtrado multi-checkbox con comparación perpendicular

El problema: 50 checkboxes y un solo FILTRAR

Joan Recasens planteó el caso en plena Navidad, y es de los que se entienden al instante porque todo el mundo ha querido montar algo así alguna vez.

Tiene una lista de registros y una columna de checkboxes. Quiere que la tabla muestre todos los registros que coincidan con al menos uno de los checkboxes marcados. Marcas "b" y "c", aparecen los registros de "b" y los de "c". Desmarcas "b", desaparecen los suyos.

Con tres o cuatro opciones lo resolverías escribiendo las condiciones a mano dentro de FILTRAR, sumando los criterios con el operador de suma. El problema es que pueden ser hasta 50 checkboxes, y además dinámicos: hoy hay doce, mañana veinte. Escribir cincuenta condiciones no es una solución, es una condena a mantenimiento perpetuo.

La estructura del fichero es sencilla: los checkboxes están en una columna devolviendo verdadero o falso, con su letra asociada al lado, y la tabla de datos tiene un identificador y una letra por fila.

La solución de Leo: comparación perpendicular

Iván (AprendizDeExcel) apuntó la técnica en cuanto leyó el problema, y Leo la desarrolló entera:

`` =FILTRAR( F5:G14; BYROW( G5:G14 = ENFILA(SI(B5:B8; C5:C8; z); 3); O ) ) ``

Cuatro líneas para cincuenta criterios. Vamos por partes, porque cada nivel tiene su gracia.

1. Generar errores a propósito

SI(B5:B8; C5:C8; z) recorre los checkboxes: si está marcado devuelve su letra, y si no... devuelve z. Que no es una variable ni un texto, es un nombre que no existe. Excel intenta resolverlo, no lo encuentra y genera un error de nombre.

Provocar un error a propósito parece raro hasta que ves el paso siguiente.

2. ENFILA con el parámetro 3 barre los errores

ENFILA(...; 3) hace dos cosas. Convierte el resultado en una fila horizontal y, con ese tercer argumento, descarta los errores al aplanar.

Ahí está el truco completo: los checkboxes desmarcados generaron errores, y ENFILA los tira. Lo que queda es una fila limpia con solo las letras de los checkboxes marcados, del tamaño exacto que haga falta. Sin huecos, sin ceros, sin cadenas vacías que luego habría que filtrar.

Es un filtrado sin FILTRAR, usando el sistema de errores como mecanismo de descarte.

3. La comparación perpendicular

Aquí ocurre lo importante: G5:G14 = ENFILA(...).

A la izquierda hay un vector vertical (la columna de letras de la tabla). A la derecha, un vector horizontal (las letras marcadas). Cuando Excel compara una columna con una fila, no las empareja uno a uno: genera una matriz bidimensional con todas las combinaciones posibles.

Si tienes 10 registros y 3 checkboxes marcados, sale una matriz de 10 por 3 llena de verdaderos y falsos, donde cada celda responde a "¿la letra de este registro es igual a esta opción marcada?".

Todas las comparaciones, en una sola operación.

4. BYROW con O colapsa la matriz

BYROW(...; O) recorre esa matriz fila a fila y aplica la función O a cada una. Devuelve verdadero si alguna de las comparaciones de esa fila acertó.

El resultado es una columna de valores lógicos, uno por registro, que es exactamente lo que FILTRAR necesita como segundo argumento.

Y lo mejor: si mañana hay 50 checkboxes, la fórmula no cambia. Solo cambia el ancho de la matriz intermedia.

La variante con ELEGIRFILAS

El fichero incluye una segunda versión que aplica el mismo principio perpendicular pero trabajando con índices de fila en lugar de con una máscara lógica:

`` =LET( _Filtro; ENFILA(FILTRAR(C5:C8; B5:B8)); ELEGIRFILAS( F4:G14; ENCOL( SI(G4:G14 = _Filtro; SECUENCIA(FILAS(F4:F14)); z); 3 ) ) ) ``

Aquí _Filtro obtiene las letras marcadas con un FILTRAR normal. Luego, en vez de colapsar la matriz de comparación a valores lógicos, se sustituye cada coincidencia por el número de fila correspondiente (generado con SECUENCIA) y cada no coincidencia por el error del nombre inexistente.

ENCOL con el parámetro 3 aplana y descarta los errores otra vez, dejando una lista limpia de índices de fila. ELEGIRFILAS los usa para extraer los registros.

Mismo resultado, camino distinto. Útil cuando lo que necesitas son las posiciones y no solo las filas.

El apunte de rendimiento

Iván confirmó algo que merece la pena subrayar: esta solución es "infinitamente más rápida" que un filtro convencional cuando hay muchos datos.

El motivo es que la comparación perpendicular resuelve todas las combinaciones en una única operación matricial, mientras que encadenar condiciones obliga a Excel a evaluar cada criterio por separado sobre toda la columna y luego combinarlos. Con 50 criterios y miles de filas, la diferencia se nota.

Funciones clave

• Comparación perpendicular — cruzar un vector vertical con uno horizontal genera una matriz con todas las combinaciones; la base de todo filtrado multicriterio moderno.
• BYROW con O — colapsa esa matriz a una columna de valores lógicos, uno por registro.
• ENFILA y ENCOL con el parámetro de omisión — aplanan una matriz descartando errores; el mecanismo que elimina las opciones no marcadas.
• Errores provocados — devolver un nombre inexistente marca los elementos a descartar para que el aplanado los tire.
• FILTRAR — recibe la máscara ya calculada y se limita a aplicarla.
• ELEGIRFILAS con SECUENCIA — la alternativa que trabaja con índices en lugar de máscaras.

Conclusión

Este caso es la mejor introducción posible a la comparación perpendicular, porque el problema es cotidiano y la solución no tiene sustituto razonable: cincuenta condiciones escritas a mano no son mantenibles, y ninguna función de Excel hace esto de forma nativa.

El patrón se generaliza a cualquier filtrado con criterios variables: filtrar pedidos por varios clientes seleccionados, productos por varias categorías, empleados por varios departamentos. Siempre es lo mismo, cambia lo que hay a cada lado del signo igual.

En la comunidad de InflueXcel estas técnicas circulan justo así: alguien plantea un problema práctico, otro nombra la técnica y un tercero escribe la fórmula. En un par de horas hay dos versiones funcionando y una nota sobre cuál rinde mejor.

Más casos con estas funciones

• LET
• FILTRAR
• SECUENCIA
• BYROW
• ELEGIRFILAS

Más contenido de Excel en InflueXcel

• Filtrar filas con todos los valores a cero: 4 enfoques con FILTRAR y BYROW Caso
  Juan tiene una tabla grande donde muchas filas contienen solo ceros y necesita filtrarlas para quedarse solo con las que tienen datos reales
• Por qué BYROW y ENCOL solo devuelven una columna y cómo solucionarlo Caso
  Interesante problema técnico planteado por un miembro de la comunidad: aplica DIVIDIRTEXTO a una columna creada con ENCOL esperando obtener
• Buscar múltiples palabras simultáneamente con FILTRAR y BYROW Caso
  Juan consigue filtrar una tabla buscando una palabra con HALLAR, pero cuando intenta buscar dos palabras a la vez pasando un rango (K16:L16)
• Filtrar con múltiples condiciones usando matrices booleanas y BYROW Caso
  Interesante problema planteado por un miembro sobre cómo filtrar datos que cumplan varias condiciones simultáneas usando operaciones matrici
• Filtro multicriteria dinámico con LET, FILTRAR y LAMBDA Caso
  Hector comparte con la comunidad una fórmula avanzada para filtrar una tabla de productos/servicios por múltiples criterios opcionales (clav
• Cuando BYROW sobra: FILTRAR por una sola columna Caso
  Nuevo reto de Excel resuelto por la comunidad con un apunte que muchos usuarios agradecen. Oscar comparte que ha conseguido filtrar una tabl
• ¿MAP o BYROW? Descubre cuándo usar cada una Tutorial
  ¿MAP o BYROW? Descubre cuándo usar cada una y cómo estas funciones pueden transformar tu forma de trabajar en Excel. En este vídeo aprenderá
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• LISTAS DESPLEGABLES DINÁMICAS: 3 Métodos para conseguirlas Tutorial
  Unas buenas listas desplegables mejoran la usabilidad de tu hoja de cálculo y disminuyen drásticamente los errores al introducir nueva infor
• Generar identificadores únicos a partir de valores repetidos: 6 enfoques distintos Caso
  Problema clásico que genera una lluvia de soluciones: a partir de una columna con valores repetidos (10, 10, 20, 20...), crear un identifica