FILTRAR con ELEGIR y BUSCARX para consultas cruzadas entre tablas

Interesante problema planteado por Héctor sobre cómo construir una consulta dinámica que combine columnas de una tabla con datos cruzados de otra tabla, todo filtrado por rango de fechas.

La solución usa FILTRAR como base, pero el truco está en ELEGIR con un array constante {1,2,3,4,5,6,7,8,9} para construir una matriz virtual de 9 columnas, donde la mayoría vienen directamente de la tabla de viajes pero una de ellas se obtiene mediante BUSCARX cruzando con otra tabla:

``
=SI.ERROR(
FILTRAR(
ELEGIR(
{1,2,3,4,5,6,7,8,9},
tbeViaje[Fecha],
tbeViaje[Viaje],
tbeViaje[ECO],
tbeViaje[Operador],
SI.ERROR(BUSCARX(tbeViaje[Viaje], tbeViajeCP[Viaje], tbeViajeCP[Cliente], ""), ""),
tbeViaje[Descripcion],
tbeViaje[Modalidad],
tbeViaje[Origen],
tbeViaje[Destino]),
(tbeViaje[Fecha]>=A3)*(tbeViaje[Fecha]<=B3)),
"")
`

El patrón ELEGIR con array constante permite montar una matriz personalizada seleccionando y reordenando columnas a voluntad, e incluso intercalando columnas calculadas como el BUSCARX que trae el nombre del cliente desde otra tabla. El filtro final por fechas aplica sobre toda la matriz construida.

Miki también aporta un truco complementario: usar UNICOS() en una hoja auxiliar y vincular el resultado con #` (operador de derrame) para crear validaciones de datos que se actualizan automáticamente.

El problema: una consulta que mezcla dos tablas y filtra por fechas

Héctor plantea algo que en una base de datos sería una consulta trivial y en Excel no lo parece tanto: quiere un listado que combine la mayoría de las columnas de una tabla de viajes con un dato que vive en otra tabla (el nombre del cliente), todo ello filtrado por un rango de fechas que el usuario elige en dos celdas.

Por separado, cada pieza es fácil. FILTRAR sabe filtrar por fechas y BUSCARX sabe traer el cliente. Lo difícil es que el resultado salga como una única tabla, en el orden de columnas que él quiere, y con la columna traída de fuera intercalada en su sitio y no pegada al final.

La solución: construir la tabla antes de filtrarla

La fórmula completa que sale del hilo es esta:

=SI.ERROR(
    FILTRAR(
        ELEGIR(
            {1;2;3;4;5;6;7;8;9};
            tbeViaje[Fecha];
            tbeViaje[Viaje];
            tbeViaje[ECO];
            tbeViaje[Operador];
            SI.ERROR(BUSCARX(tbeViaje[Viaje]; tbeViajeCP[Viaje]; tbeViajeCP[Cliente]; ""); "");
            tbeViaje[Descripcion];
            tbeViaje[Modalidad];
            tbeViaje[Origen];
            tbeViaje[Destino]);
        (tbeViaje[Fecha]>=A3)*(B3>=tbeViaje[Fecha]));
    "")

Impone respeto, pero la idea es de una sola línea: primero se monta una tabla virtual con las columnas que quieres, y luego se filtra esa tabla. Nada más.

La pieza clave: ELEGIR con una constante matricial

El truco está en el primer argumento de ELEGIR. Normalmente le pasas un número y te devuelve una de las opciones. Aquí se le pasa una fila con los números del 1 al 9, así que devuelve las nueve opciones a la vez, una por columna.

El resultado es una matriz de nueve columnas que no existe en ninguna hoja: es una tabla virtual, montada al vuelo, con exactamente las columnas que quieres y en el orden que quieres.

Y esto es lo que lo hace tan potente:

  • Puedes reordenar columnas cambiando el orden en que las listas.
  • Puedes omitir las que no te interesan.
  • Puedes repetir una columna si la necesitas dos veces.
  • Y sobre todo, puedes intercalar columnas calculadas entre las originales.

Esa quinta opción es justo eso: en lugar de una columna de la tabla, va un BUSCARX completo que trae el cliente desde la otra tabla. Como devuelve un valor por cada fila, encaja en la matriz igual que cualquier columna real.

En Excel moderno hay una función pensada específicamente para esto, ELEGIRCOLS, que selecciona y reordena columnas de una tabla. Pero ELEGIRCOLS solo puede coger columnas que ya existen. Cuando necesitas intercalar una columna calculada, el patrón de ELEGIR con constante matricial sigue siendo el que vale.

El filtro y los dos SI.ERROR

El segundo argumento de FILTRAR es la condición de fechas:

(tbeViaje[Fecha]>=A3)*(B3>=tbeViaje[Fecha])

Dos comparaciones multiplicadas, que es como se escribe un Y lógico sobre matrices: la multiplicación solo da 1 cuando las dos condiciones se cumplen en esa fila. La segunda comparación está escrita al revés — la celda a la izquierda — porque leerla como "B3 es mayor o igual que la fecha" dice exactamente lo mismo que "la fecha es menor o igual que B3".

Y hay dos SI.ERROR haciendo trabajos distintos, algo que merece la pena distinguir:

  • El interior, alrededor del BUSCARX, cubre los viajes que no tienen cliente en la otra tabla. Sin él, una sola fila sin correspondencia contaminaría toda la matriz con un error.
  • El exterior, envolviendo todo, cubre el caso de que el filtro no devuelva ninguna fila. FILTRAR da un error cuando no encuentra nada, y así el usuario ve una celda vacía en vez de un mensaje raro.

Conviene notar que FILTRAR tiene un tercer argumento propio para el caso de "sin resultados", que es la forma más limpia de resolver esa segunda situación.

El complemento de Miki: listas desplegables que se actualizan solas

Miki añade un truco pequeño y muy rentable para acompañar a esta consulta: poner UNICOS sobre una columna en una hoja auxiliar y usar ese resultado, referenciado con el operador de derrame, como origen de una validación de datos.

El efecto es que el desplegable crece y encoge solo según los datos. Cuando aparece un operador nuevo en la tabla, ya está en la lista sin que nadie toque la validación.

Encaja perfectamente con este caso: si las celdas de filtro son desplegables dinámicos, toda la consulta se mantiene sola.

Funciones clave

  • ELEGIR con constante matricial — monta una tabla virtual seleccionando, reordenando e intercalando columnas. El corazón del caso.
  • ELEGIRCOLS — la alternativa moderna para seleccionar y reordenar, cuando no necesitas columnas calculadas.
  • FILTRAR — filtra la matriz ya construida. Su segundo argumento admite cualquier condición booleana por filas.
  • BUSCARX — trae el dato de la otra tabla, devolviendo un valor por fila.
  • SI.ERROR — usado en dos niveles distintos, para las búsquedas sin resultado y para el filtro vacío.
  • UNICOS con el operador de derrame — origen de validaciones de datos que se actualizan solas.

Conclusión

La idea que hay que llevarse es la del orden de las operaciones: construye primero la tabla que quieres ver, y fíltrala después. Intentarlo al revés — filtrar cada trozo y luego pegarlos — es lo que convierte estos problemas en un lío de rangos descuadrados.

Y el patrón de ELEGIR con una constante matricial es de los que se usan mucho más de lo que parece en cuanto lo tienes en la cabeza: cada vez que necesites una tabla con columnas de varios sitios, ahí está.

Consultas cruzadas como esta, montadas solo con fórmulas y sin salir de la hoja, aparecen constantemente en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel