Extraer máximos por clave y calcular tiempo transcurrido: AGRUPARPOR vs fórmulas clásicas

Interesante caso doble que arrancó con una necesidad muy concreta: dada una tabla con columnas Clave, Secuencia y Rev, obtener el máximo de cada columna numérica por cada clave única. Es decir, una agrupación con función de agregado. Lo que en SQL sería un GROUP BY con MAX.

La tabla de datos tenía este aspecto:

| Clave | Secuencia | Rev |
|-------|-----------|-----|
| 2024CEO524 | 6 | 2 |
| 2024CEO524 | 7 | 3 |
| 2024CEO524 | 16 | 5 |
| 2024CEO576 | 1 | 1 |
| 2024CEO576 | 6 | 2 |
| 2024CEO579 | 1 | 1 |

Un miembro de la comunidad sugirió la vía clásica: tabla dinámica. Sin fórmulas, sin complicaciones. Clave en filas, Secuencia y Rev en valores con función MAX. Limpio y directo.

Nacho propuso resolverlo con AGRUPARPOR, la función nativa de Excel 365 pensada exactamente para esto:

``
=AGRUPARPOR(
Datos[[#Todo];[Clave]];
Datos[[#Todo];[Secuencia]:[Rev]];
MAX;
3;
0
)
`

Una sola fórmula que agrupa por la columna Clave, aplica MAX a las columnas Secuencia y Rev, incluye encabezados (argumento 3) y ordena ascendente (argumento 0). El resultado es una tabla limpia con el máximo de cada columna por clave.

Para quienes no tienen Excel 365 (o prefieren compatibilidad con Excel 2021), Gerson aportó una alternativa elegante con LET, UNICOS, ELEGIR y MAX.SI.CONJUNTO:

`
=LET(
c; Tabla1[Clave];
UNICOS(
ELEGIR(
{1,2,3};
c;
MAX.SI.CONJUNTO(Tabla1[Secuencia]; c; c);
MAX.SI.CONJUNTO(Tabla1[Rev]; c; c)
)
)
)
`

La idea es ingeniosa: ELEGIR({1,2,3},...) construye una matriz virtual de 3 columnas (clave, max secuencia, max rev), y UNICOS se encarga de eliminar duplicados por clave. Sin AGRUPARPOR, sin tablas dinámicas, compatible con versiones anteriores.

Aquí no acabó la cosa. Un miembro de la comunidad planteó una extensión del problema: añadir una columna Fecha / Hora a los datos y calcular el tiempo transcurrido (diferencia entre la primera y última fecha) por cada clave. Los datos ahora incluían campos como Condición, Responsable y un timestamp por cada registro, con múltiples entradas por clave a lo largo de semanas.

Gerson propuso usar AGRUPARPOR combinado con APILARH y ENTERO para agrupar por clave y día, calculando la diferencia entre MAX y MIN de las fechas:

`
=AGRUPARPOR(
APILARH(Tabla2[Clave]; ENTERO(Tabla2[Fecha / Hora]));
Tabla2[Fecha / Hora];
LAMBDA(x; MAX(x) - MIN(x));
;
0
)
`

Nacho lo llevó un paso más allá con una fórmula que resuelve todo en un solo bloque: máximos de Secuencia y Rev + tiempo transcurrido formateado como dd:hh:mm, todo combinando EXCLUIR, AGRUPARPOR, APILARH y LAMBDA:

`
=EXCLUIR(
AGRUPARPOR(
Tabla2[[#Todo];[Clave]];
APILARH(
Tabla2[[#Todo];[Secuencia]:[Rev.]];
Tabla2[[#Todo];[Fecha / Hora]]
);
APILARH(
MAX;
MAX;
LAMBDA(x; TEXTO(MAX(x) - MIN(x); "dd:hh:mm"))
);
;
0
);
1
)
`

El truco está en pasar un array de funciones a AGRUPARPOR mediante APILARH: MAX para Secuencia, MAX para Rev, y un LAMBDA personalizado para calcular y formatear la diferencia de fechas. EXCLUIR(…;1) elimina la fila de encabezados autogenerada.

Gerson también preparó una versión más extendida que desglosa inicio, fin, días y horas por separado, usando LET para no repetir cálculos y SINGLE para obtener el primer valor (mínimo cronológico) de cada grupo.

Tres enfoques distintos para un mismo problema, desde la tabla dinámica clásica hasta fórmulas de una sola celda con AGRUPARPOR`. Un buen ejemplo de cómo Excel 365 permite resolver agrupaciones complejas sin salir de la hoja de cálculo.

El problema: máximos por clave (un GROUP BY con MAX)

El caso arrancó con una necesidad muy concreta: dada una tabla con columnas Clave, Secuencia y Rev, obtener el máximo de cada columna numérica por cada clave única. En SQL sería un GROUP BY con MAX; en Excel, una agrupación con función de agregado.

Un miembro sugirió la vía sin fórmulas: tabla dinámica, con la clave en filas y Secuencia y Rev en valores con función MAX. Limpio y directo. Pero varios quisieron resolverlo con fórmulas, y ahí empezó lo interesante.

Solución con AGRUPARPOR (Excel 365)

Nacho propuso la función nativa pensada justo para esto:

=AGRUPARPOR(
    Datos[[#Todo];[Clave]];
    Datos[[#Todo];[Secuencia]:[Rev]];
    MAX;
    3;
    0
)

Una sola fórmula: agrupa por Clave, aplica MAX a las columnas Secuencia y Rev, incluye encabezados (argumento 3) y ordena ascendente (argumento 0). El resultado es una tabla limpia con el máximo de cada columna por clave.

Alternativa compatible con Excel 2021

Para quien no tiene AGRUPARPOR, Gerson aportó una versión con UNICOS y MAX.SI.CONJUNTO:

La idea es construir una matriz virtual de tres columnas —clave, máximo de Secuencia, máximo de Rev— con ELEGIR usando un índice horizontal de tres columnas (por ejemplo con SECUENCIA(;3)), y envolverlo todo en UNICOS para eliminar las claves repetidas. MAX.SI.CONJUNTO calcula el máximo de cada columna para cada clave. Sin AGRUPARPOR y sin tablas dinámicas, compatible con versiones anteriores.

La extensión: tiempo transcurrido por clave

El caso creció: alguien pidió añadir una columna de fecha y hora y calcular el tiempo transcurrido (diferencia entre el primer y el último registro) por cada clave.

Gerson lo resolvió agrupando por clave y por día, con la diferencia entre el máximo y el mínimo de las fechas:

=AGRUPARPOR(
    APILARH(Tabla2[Clave]; ENTERO(Tabla2[Fecha / Hora]));
    Tabla2[Fecha / Hora];
    LAMBDA(x; MAX(x) - MIN(x));
    ;
    0
)

El detalle bonito es pasar una LAMBDA como función de agregado: AGRUPARPOR no se limita a SUMA o MAX, admite cualquier función que definas.

Nacho lo cerró resolviendo todo en un bloque: máximos de Secuencia y Rev más el tiempo transcurrido formateado como días, horas y minutos, pasando un array de funciones a AGRUPARPOR:

=EXCLUIR(
    AGRUPARPOR(
        Tabla2[[#Todo];[Clave]];
        APILARH(
            Tabla2[[#Todo];[Secuencia]:[Rev.]];
            Tabla2[[#Todo];[Fecha / Hora]]
        );
        APILARH(
            MAX;
            MAX;
            LAMBDA(x; TEXTO(MAX(x) - MIN(x); "dd:hh:mm"))
        );
        ;
        0
    );
    1
)

Aquí está la joya del caso: el tercer argumento de AGRUPARPOR es un APILARH de tres funciones distintas —MAX, MAX y una LAMBDA a medida— que se aplican columna por columna. EXCLUIR(...; 1) quita la fila de encabezados autogenerada.

Funciones clave

  • AGRUPARPOR: agrupa por clave y admite un array de funciones de agregado.
  • MAX.SI.CONJUNTO: máximo condicional, base de la versión compatible.
  • UNICOS y ELEGIR: construyen la tabla resumen sin AGRUPARPOR.
  • LAMBDA: define agregados personalizados, como la diferencia de fechas formateada.
  • APILARH, ENTERO, EXCLUIR, TEXTO: ensamblan y presentan el resultado.

Conclusión

De la tabla dinámica clásica a la fórmula de una sola celda, este caso enseña la potencia real de AGRUPARPOR: no solo agrupa, sino que acepta funciones a medida, incluso varias distintas a la vez. Un GROUP BY con MAX, un cálculo de duración y un formateo, todo en una fórmula. Casos así se cuecen cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel