Total por grupos

Jaime Franco Jimenez
1 nov 2023
3 Min. de lectura

Tenemos dos columnas con cantidades.

Vemos que hay celdas en blanco entre grupo de cantidades, entonces, las cantidades que tenemos antes de una celda o celdas en blanco, es un grupo.

Debemos de crear un modelo donde aparezca una columna con cada grupo, cada grupo debe de ir precedido de un numero empezando desde el número 1.

En la segunda columna, debe de aparecer la suma de cada grupo, por ejemplo, para el grupo1, para la primera cantidad debe de aparecer 60, la suma de 40 mas 20, y, en la segunda columna, debe de aparecer 270, la suma de 90 y 180.

Es lo que debemos de realizar para cada grupo.

El modelo debe de aparecer como sigue:

En la celda D2, usamos LET, creamos una variable, usamos SCAN, como argumento valor inicial, ponemos 0, como argumento array, seleccionamos el rango A2:A20.

=LET(xx;SCAN(0;A2:A20

Como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=LET(xx;SCAN(0;A2:A20;LAMBDA(x;y

Como argumento cálculo de LAMBDA, usamos el condicional SI, preguntamos que, si la variable “y” es igual a blanco, que devuelva 0, en caso contrario, que suma la variable “y” más la variable “x”.

Probamos variable.

=LET(xx;SCAN(0;A2:A20;LAMBDA(x;y;SI(y="";0;y+x)));xx)

Obtenemos una acumulado, pero cuando encuentra un cero, el acumulado se reinicia.

Vamos a crear otro acumulado, pero para la segunda columna, por lo que la expresión es la misma, lo único que va a cambiar es el argumento array, que ahora es el rango B2:B20.

LET(xx;SCAN(0;A2:A20;LAMBDA(x;y;SI(y="";0;y+x)));zz;SCAN(0;B2:B20;LAMBDA(x;y;SI(y="";0;y+x)));zz)

Creamos otra variable, usamos la función BYROW, como argumento matriz ponemos la variable “xx”, como argumento función, ponemos LAMBDA, creamos una variable, usamos la función COINCIDIR, como argumento valor buscado, ponemos cero, como argumento matriz buscada, ponemos la variable “x”, como argumento tipo de coincidencia, ponemos exacta.

=LET(xx;SCAN(0;A2:A20;LAMBDA(x;y;SI(y="";0;y+x)));zz;SCAN(0;B2:B20;LAMBDA(x;y;SI(y="";0;y+x)));yy;BYROW(xx;LAMBDA(x;COINCIDIR(0;x;0)))

Ponemos el signo de más, ponemos SECUENCIA, como argumento filas, usamos la función CONTAR, como argumento ponemos la variable “xx”, omitimos el argumento columnas, como argumento inicio, ponemos -1.

Veamos que hace esta expresión.

La función COINCIDIR, nos devuelve la posición donde hay ceros, obtenemos 1 para las celdas donde hay blancos.

La función SECUENCIA nos devuelve una secuencia de números empezando desde el numero -1 hasta el número 17.

El resultado de la expresión completa es:

El resultado de COINCIDIR es:

Y el resultado de SECUENCIA es:

Veamos de donde sale el primer valor que es 2.

El resultado de COINCIDIR es 1, y, la secuencia que le corresponde también es 1, por lo que la suma nos devuelve 2.

Al valor 3, la función COINCIDIR nos devuelve 1, y, la secuencia que le corresponde es 2, y, la suma nos devuelve 3.

Tenemos la fila a rescatar de las variables “xx” y “zz”.

Creamos otra variable, usamos APILARH, como argumento matriz1, ponemos la variable “xx”, como argumento matriz2, ponemos la variable “zz”.

=LET(xx;SCAN(0;A2:A20;LAMBDA(x;y;SI(y="";0;y+x)));zz;SCAN(0;B2:B20;LAMBDA(x;y;SI(y="";0;y+x)));yy;BYROW(xx;LAMBDA(x;COINCIDIR(0;x;0)))+SECUENCIA(19;;-1);a;APILARH(xx;zz);a)

Tenemos una matriz desbordada de dos columnas, en la primera columna tenemos el acumulado de las primeras cantidades, y, en la segunda columna, el acumulado de las segundas cantidades.

Creamos otra variable, usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento numero de fila, ponemos la variable “yy”, como argumento numero de columna, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 2.

En una matriz podemos ver que obtenemos el valor mayor de cada suma, excepto en la última fila.

Sustituimos los errores por cero, para ello, usamos la función SI.ERROR.

Creamos otra variable, usamos la función FILTRAR, como argumento matriz, ponemos la variable “b”, como argumento include, usamos INDICE, como argumento matriz, ponemos la variable “b”, omitimos el argumento número de fila, como argumento numero de columna, ponemos 1, y, comparamos con distinto a 0.

=LET(xx;SCAN(0;A2:A20;LAMBDA(x;y;SI(y="";0;y+x)));zz;SCAN(0;B2:B20;LAMBDA(x;y;SI(y="";0;y+x)));yy;BYROW(xx;LAMBDA(x;COINCIDIR(0;x;0)))+SECUENCIA(19;;-1);a;APILARH(xx;zz);b;SI.ERROR(INDICE(a;yy;SECUENCIA(;2));0);c;FILTRAR(b;INDICE(b;;1)<>0);c)

Ya tenemos la suma de las cantidades, excepto, la última.

Lo siguiente es traernos las ultimas cantidades, para ello, creamos otra variable, usamos la función APILARV, como argumento matriz1, usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento numero de fila, usamos la función FILAS y como argumento ponemos la variable “xx”, omitimos el argumento número de columna.

Hemos obtenido las ultimas cantidades.

Como argumento cálculo de LET, usamos APILARH, como argumento matriz1, entre comillas dobles, ponemos Grupo, concatenamos con la función SECUENCIA, como argumento filas, usamos CONTARA, y, como argumento ponemos la variable “d”, y, dividimos por 2, como argumento matriz2, ponemos la variable “d”.

Aceptamos y ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Total por grupos

Entradas recientes

Comentarios