Reorganizar tablas mensuales: cruzar por persona buscando en vertical y en horizontal

Nuevo caso interesante de la comunidad. Juan tenía varias tablas mensuales (a veces más de una en el mismo mes) y quería reorganizarlas por persona y por mes, agrupando los casos en los que una misma persona aparece repetida dentro de un mes. Lo tenía medio resuelto buscando en vertical, pero al añadir la búsqueda en horizontal (desempatando por nombres concatenados) la fórmula se rompía.

Su punto de partida era un REDUCE con BUSCARX que recorría filas y apilaba resultados:

``
=REDUCE("";SECUENCIA(FILAS(A26:A28));LAMBDA(i;y;APILARV(i;BUSCARX(B67:D67;B22:G22;INDICE(B22:G22;;y)))))
`

A partir de ahí salieron tres enfoques distintos, y la comparativa es lo más jugoso del caso.

Enfoque matricial con INDICE + COINCIDIRX + BYCOL

Alejandro planteó cruzar filas y columnas a la vez. COINCIDIRX localiza la fila por persona y, para dar con la columna correcta, compara la firma concatenada de cada bloque con BYCOL(...;CONCAT):

`
=INDICE(B8:G16;COINCIDIRX(J3:J5;A8:A16);COINCIDIRX(BYCOL(B3:G5;CONCAT);BYCOL(B36:G38;CONCAT)))
`

Enfoque con FILTRAR + ELEGIRCOLS

John lo abordó filtrando primero las filas que interesan con CONTAR.SI y, después, reordenando las columnas para que casen con el orden deseado usando ELEGIRCOLS y COINCIDIRX:

`
=FILTRAR(A8:G16;CONTAR.SI(J3:J5;A8:A16))
`

`
=ELEGIRCOLS(FILTRAR(A8:G16;CONTAR.SI(J3:J5;A8:A16));COINCIDIRX(A36:G36;A3:G3))
`

Enfoque compacto con LET + MMULT

Para montar la matriz agrupada por persona y mes en una sola fórmula, John tiró de MMULT con una matriz booleana (comparando firmas concatenadas con BYCOL y CONCAT) y lo envolvió todo en LET para no repetir expresiones:

`
=LET(H;APILARH;f;H(C5:C8;G5:J8);u;UNICOS(f;1);H(""&E5:E11;APILARV(u;MMULT(H(BUSCARV(E9:E11;A9:C14;3;);G9:J11);N(ENCOL(BYCOL(f&"|";CONCAT))=BYCOL(u&"|";CONCAT))))))
`

Tres caminos para el mismo objetivo: el matricial con INDICE y COINCIDIRX, el de FILTRAR con ELEGIRCOLS para reordenar columnas, y el compacto con MMULT` para construir la matriz agrupada de una sola tacada. Se incluyen los ficheros originales con el planteamiento.

El problema: cruzar por persona buscando a la vez en vertical y en horizontal

Juan llegó a la comunidad con un lío muy típico cuando trabajas con datos mensuales: tenía varias tablas por mes (a veces más de una en el mismo mes) y quería reorganizarlas por persona, agrupando los casos en los que una misma persona se repite dentro de un mes.

Lo tenía medio resuelto buscando en vertical, pero en cuanto añadía la búsqueda en horizontal (desempatando por nombres concatenados) la fórmula se rompía. Su punto de partida era un REDUCE con BUSCARX que recorría filas y las iba apilando:

=REDUCE("";SECUENCIA(FILAS(A26:A28));LAMBDA(i;y;APILARV(i;BUSCARX(B67:D67;B22:G22;INDICE(B22:G22;;y)))))

Funciona para el caso sencillo, pero se queda corto cuando necesitas casar filas y columnas al mismo tiempo. De ahí salieron tres enfoques distintos, y la comparativa es lo más jugoso del caso.

Enfoque 1: matricial con INDICE + COINCIDIRX + BYCOL

Alejandro planteó cruzar filas y columnas a la vez. COINCIDIRX localiza la fila por persona y, para dar con la columna correcta, compara la firma concatenada de cada bloque usando BYCOL(...;CONCAT):

=INDICE(B8:G16;COINCIDIRX(J3:J5;A8:A16);COINCIDIRX(BYCOL(B3:G5;CONCAT);BYCOL(B36:G38;CONCAT)))

La idea clave es tratar cada columna como un texto único. Al colapsar las cabeceras con BYCOL y CONCAT, COINCIDIRX puede emparejar bloques que ocupan varias filas como si fueran una sola clave. Elegante y sin columnas auxiliares.

Enfoque 2: FILTRAR + ELEGIRCOLS para reordenar columnas

John lo abordó en dos tiempos. Primero filtra las filas que interesan con CONTAR.SI:

=FILTRAR(A8:G16;CONTAR.SI(J3:J5;A8:A16))

Y después reordena las columnas para que casen con el orden deseado, apoyándose en ELEGIRCOLS y COINCIDIRX:

=ELEGIRCOLS(FILTRAR(A8:G16;CONTAR.SI(J3:J5;A8:A16));COINCIDIRX(A36:G36;A3:G3))

Este camino es muy legible: se lee de fuera hacia dentro y cada función hace una cosa. Ideal si vas a mantener la fórmula o explicársela a alguien más adelante.

Enfoque 3: compacto con LET + MMULT

Para montar la matriz agrupada por persona y mes en una sola tacada, John tiró de MMULT con una matriz booleana (otra vez comparando firmas concatenadas con BYCOL y CONCAT) y lo envolvió todo en LET para no repetir expresiones:

=LET(H;APILARH;f;H(C5:C8;G5:J8);u;UNICOS(f;1);H(""&E5:E11;APILARV(u;MMULT(H(BUSCARV(E9:E11;A9:C14;3;);G9:J11);N(ENCOL(BYCOL(f&"|";CONCAT))=BYCOL(u&"|";CONCAT))))))

MMULT con una matriz de ceros y unos es un truco potentísimo para agrupar y sumar en una sola operación: donde la firma coincide hay un 1, y la multiplicación matricial hace el resto. LET mantiene la fórmula compacta guardando APILARH y los rangos intermedios en variables.

Funciones clave

  • REDUCE — recorre una secuencia acumulando un resultado; el punto de partida de Juan.
  • BUSCARX y BUSCARV — la búsqueda de toda la vida, aquí en vertical.
  • COINCIDIRX — devuelve la posición de una coincidencia; el corazón del cruce fila/columna.
  • BYCOL + CONCAT — colapsan cada columna en una firma de texto para emparejar bloques multifila.
  • FILTRAR + CONTAR.SI — seleccionan solo las filas que cumplen la condición.
  • ELEGIRCOLS — reordena o selecciona columnas por posición.
  • MMULT + N — agrupan mediante una matriz booleana en una sola operación.
  • LET — nombra subexpresiones para no repetirlas y ganar legibilidad.

Conclusión

Tres caminos para el mismo objetivo: el matricial con INDICE y COINCIDIRX, el de FILTRAR con ELEGIRCOLS para reordenar columnas, y el compacto con MMULT para construir la matriz agrupada de golpe. No hay uno "correcto": el primero es directo, el segundo es el más legible y el tercero el más potente cuando necesitas agrupar.

Casos como este salen cada semana en la comunidad de Influexcel, donde alguien plantea un problema real y entre varios miembros aparecen enfoques que ni habías considerado. Puedes descargar los ficheros originales con el planteamiento y las tres soluciones para trastear con ellas.

Más casos con estas funciones

Más contenido de Excel en InflueXcel