Dividir datos en grupos

Tenemos los siguientes nombres, y, ventas.

Debemos de dividir los datos en grupos secuencialmente de modo que la suma de la venta en un grupo no exceda de 20 y sea lo más cercana posible a 20 en un grupo.

El valor máximo puede contener la Venta de 20.

Empecemos…

Vamos a usar la funcion SCAN para crear un acumulado, en una celda, ponemos la funcion SCAN, como argumento valor inicial, ponemos 0, como argumento array, ponemos el rango B2:B6 (ventas).

=SCAN(0;B2:B26

Como argumento funcion, ponemos LAMBDA, creamos dos variables.

=SCAN(0;B2:B26;LAMBDA(x;y

Como argumento calculo de LAMBDA, preguntamos si el resultado de sumar las variables “x” e “y” es mayor a 20, es ese caso, que nos devuelva la variable “y”, en caso contrario, que devuelva la suma de la variable “y” más la variable “x”.

=SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))

Obtenemos una matriz desbordada con el acumulado, excepto, cuando es mayor a 20, que se reinicia.

Veamos cómo funciona.

Cuando entramos en la expresión, valor inicial vale 0, y, el argumento array es el primer valor del rango B2:B26.

Ahora, valor inicial, vale 7, y, el argumento array, es el segundo valor del rango B2:B26.

Valor inicial, ahora, vale 12, y, array vale 7, la operación seria:

Valor inicial vale 19 y array vale 8, la suma es mayor a 20.

Entonces, la condición “x+y>20”, se cumple, por lo que nos devuelve el valor de la variable “b”, que es 8.

Así con el resto de los valores.

Vamos a usar otra funcion SCAN, como argumento valor inicial, ponemos 0, como argumento array, es la funcion SCAN anterior.

=SCAN(0;SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))

Igualamos al rango B2:B26.

=SCAN(0;SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))=B2:B26

Como argumento funcion ponemos LAMBDA, creamos dos variables, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “b” es igual a VERDADERO, en ese caso, sumamos 1 a la variable “a”, en caso contrario, ponemos la variable “a”.

=SCAN(0;SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))=B2:B26;LAMBDA(a;b;SI(b;1+a;a)))

Obtenemos una matriz desbordada con una secuencia de números empezando desde el numero 1, cada numero se repite el numero de veces mientras no se cumpla la condición del primer acumulado.

Veamos de donde salen estos valores, el argumento array, es la funcion:

SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))=B2:B26

Donde tenemos una matriz desbordada con unos y ceros.

Cuando entramos en la funcion, el argumento valor inicial, vale cero, y, el argumento array vale el primer valor de la matriz desbordada con unos y ceros, en este caso, es el valor 1.

Por lo que la condición de que la variable “b” es igual a VERDADERO se cumple.

Ahora, la variable “a” vale 1, y, la variable “b” vale 0, en este caso, la variable “b” no es igual a VERDADERO, por lo que toma el valor de la variable “a”, que es 1.

La variable “a”, vale 1, y, la variable “b” vale 0, tampoco se cumple la condición, por lo que la variable “a” sigue valiendo 1.

Ahora, la variable “a” vale 1, y, la variable “b”, también, vale 1.

Se cumple la condición, entonces, a la variable “a” que vale 1, se le suma 1, por lo que pasa a valer 2.

Entonces, se cumple la condición, por lo que a la variable “a” que vale 1, se le suma 1, por lo que ahora vale 2.

El siguiente valor de la variable “b” vale cero, no se cumple la condición, por lo que la variable “a”, sigue valiendo 2.

El siguiente valor de la variable “b” es 1, se cumple la condición, por lo que a la variable “a” que vale 2, se le suma 1, ahora vale 3.

Estos resultados son los que tenemos hasta ahora.

Si nos fijamos en los resultados obtenidos, vemos que son los mismos.

Volvemos a la expresión, después del signo igual, ponemos la funcion APILARH, como argumento matriz1, seleccionamos el rango A2:A26, como argumento matriz2, seleccionamos el rango B2:B26, como argumento matriz3, entre comillas dobles, ponemos Grupo, y, concatenamos con la expresión anterior.

=APILARH(A2:A26;B2:B26;"Grupo"&SCAN(0;SCAN(0;B2:B26;LAMBDA(x;y;SI(x+y>20;y;y+x)))=B2:B26;LAMBDA(a;b;SI(b;1+a;a))))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco