Funciones window en Excel: el total del grupo en cada fila con LAMBDA y BYROW

Funciones window en Excel: el total del grupo en cada fila con LAMBDA y BYROW

En SQL se llaman funciones window: columnas que, para cada fila, traen un agregado calculado sobre un grupo mayor. El total de ese cliente al lado del saldo de la línea, sin perder el detalle ni agrupar la tabla.

Excel no las tiene de forma nativa, así que se fabrican con LAMBDA. Y sirven para exactamente lo que parece: repartos, prorrateos, porcentajes sobre el total del grupo y descuentos acumulados aplicados línea a línea.

La función recibe dos parámetros —la columna que define el grupo y la columna a acumular— y por dentro combina BYROW con SUMAR.SI.CONJUNTO:

``
=LAMBDA(dimension;valores;
BYROW(dimension;LAMBDA(x;
SUMAR.SI.CONJUNTO(valores;dimension;x)
))
)
`

Dos detalles que explican por qué está montada así:

- BYROW solo admite una función de un parámetro, de ahí la LAMBDA(x; ...) anidada dentro de la principal. Es el patrón habitual: una LAMBDA externa que define la API de tu función y otra interna que es el cálculo por fila.
- Para probarla antes de guardarla, se le añaden los argumentos entre paréntesis al final. Una vez funciona, se copia sin esa parte al Administrador de nombres y ya se puede llamar por su nombre.

Cambiando SUMAR.SI.CONJUNTO por PROMEDIO.SI.CONJUNTO, MAX.SI.CONJUNTO o CONTAR.SI.CONJUNTO` tienes la misma función para otras agregaciones.

Hay un cálculo que aparece constantemente en trabajo real y que Excel no resuelve de forma directa: poner, en cada línea de una tabla, un total calculado sobre un grupo mayor. El saldo de la fila junto al total de ese cliente. La venta del mes junto al total del año.

Con una tabla dinámica agrupas y pierdes el detalle. Con SUMAR.SI.CONJUNTO arrastrado celda a celda funciona, pero deja una columna de fórmulas que hay que mantener. En SQL esto tiene nombre propio —funciones window— y en Excel se puede fabricar con LAMBDA.

Para qué sirve realmente

No es un capricho de nomenclatura. Tener el total del grupo en cada fila es el paso previo obligatorio de un montón de cálculos:

  • Repartos y prorrateos: el peso de la línea es su valor dividido entre el total de su grupo.
  • Porcentajes de participación sobre el cliente, el centro o el periodo.
  • Descuentos acumulados: aplicar un tramo que depende de lo que ese cliente lleva comprado en total, pero calculándolo línea a línea.

Sin esa columna, todos esos cálculos obligan a un rodeo por una tabla auxiliar.

La función

Recibe dos parámetros: la columna que define el grupo y la columna a acumular.

=LAMBDA(dimension;valores;
    BYROW(dimension;LAMBDA(x;
        SUMAR.SI.CONJUNTO(valores;dimension;x)
    ))
)

Se lee de dentro hacia fuera. BYROW recorre la columna de dimensión fila a fila y, en cada una, entrega su valor —el código de cliente de esa línea— a la función interna. Esa función hace un SUMAR.SI.CONJUNTO de la columna de valores filtrando por ese código. El resultado es una matriz con el total del grupo repetido en cada fila que le pertenece.

El detalle que descoloca: dos LAMBDA anidadas

Es lo que hace que esta fórmula parezca más complicada de lo que es. BYROW exige que su segundo argumento sea una función de un solo parámetro, así que no se le puede pasar directamente un SUMAR.SI.CONJUNTO: hay que envolverlo en su propia LAMBDA(x; ...).

Queda entonces un patrón que se repite en casi toda función personalizada que itere:

  • La LAMBDA externa define la interfaz de tu función: qué parámetros pide.
  • La LAMBDA interna es el cálculo que se ejecuta en cada fila, y su parámetro (x por convención) es el valor de la fila en curso.

Una vez lo has visto una vez, deja de estorbar.

Cómo probarla antes de guardarla

Escribir una LAMBDA directamente en el Administrador de nombres es escribir a ciegas. El método que evita eso es montarla en una celda y ejecutarla añadiéndole los argumentos entre paréntesis al final:

=LAMBDA(dimension;valores; ... )(A2:A100;D2:D100)

Ese segundo paréntesis le pasa los rangos reales y la fórmula devuelve el resultado ahí mismo, con lo que se puede depurar como cualquier otra. Cuando funciona, se copia todo menos ese último paréntesis y se pega en Fórmulas > Administrador de nombres con el nombre que quieras. A partir de ese momento se llama como una función más del libro.

Variantes que salen gratis

La estructura no depende de que la agregación sea una suma. Cambiando la función interna tienes toda la familia:

  • PROMEDIO.SI.CONJUNTO — la media del grupo en cada fila
  • MAX.SI.CONJUNTO / MIN.SI.CONJUNTO — el extremo del grupo
  • CONTAR.SI.CONJUNTO — cuántas líneas tiene el grupo al que pertenece la fila

También se puede añadir un tercer parámetro para pasar la operación deseada y tener una sola función configurable.

Funciones clave

  • LAMBDA — define la función y sus parámetros
  • BYROW — recorre fila a fila; solo acepta funciones de un parámetro, de ahí la LAMBDA anidada
  • SUMAR.SI.CONJUNTO — la agregación por grupo, con el valor de la fila como criterio
  • Administrador de nombres — donde la función pasa a ser parte del libro

Conclusión

Lo que se está construyendo aquí no es una fórmula, es vocabulario. Una vez la función tiene nombre, el cálculo desaparece de la hoja y lo que queda escrito es la intención. Y como todo se apoya en BYROW con una LAMBDA interna, el mismo esqueleto sirve para cualquier cálculo que necesites resolver fila a fila mirando al conjunto.

LAMBDA y BYROW requieren Microsoft 365.

Más casos con estas funciones

Más contenido de Excel en InflueXcel