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.