Acumulados por años

Tenemos una serie de fechas, cada fecha tiene asignado un almacén y una venta.

Debemos de calcular el acumulado por año.

En la celda D2, usamos LET, creamos una variable, seleccionamos el rango A2:A16, creamos otra variable, y, seleccionamos el rango A2:C16.

=LET(a;A2:A16;b;A2:C16

Creamos otra variable, nos traemos el año de la variable “a”.

=LET(a;A2:A16;b;A2:C16;c;AÑO(a);c)

Nos quedamos con los valores únicos.

=LET(a;A2:A16;b;A2:C16;c;UNICOS(AÑO(a));c)

Lo ponemos en horizontal con la funcion ENFILA.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));c)

Creamos otra variable, usamos la funcion REDUCE, como argumento valor inicial, ponemos 0, como argumento array, ponemos la variable “c”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;REDUCE(0;c;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2 usamos la funcion FILTRAR, como argumento array, ponemos la variable “b”, como argumento Include, nos traemos el año de la variable “a” e igualamos a la variable “y”.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));d)

Tenemos una matriz desbordada de 13 columnas, con la fecha, almacén, y, ventas para cada año.

La primera columna está de más, usamos la funcion EXCLUIR, como argumento matriz, es la funcion REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);d)

Vamos a sustituir los errores por 0, para ello, usamos la funcion SI.ERROR, como argumento valor es la funcion EXCLUIR, como argumento valor si error, ponemos 0.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);d)

Vemos que las cantidades se encuentran cada tres columnas, pues, usamos la funcion ELEGIRCOLS, como argumento matriz, ponemos la variable “d, como argumento número columna1, ponemos 3, como numero columna2, ponemos 6, como numero columna3, ponemos 9, y, como numero columna4, ponemos 12.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);e)

Creamos otra variable, y, transponemos la variable “e”.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);f)

Para calcular el acumulado, vamos a usar la funcion SCAN, lo haremos por columnas, para que el acumulado se cree correctamente por columnas, vamos a crear otra variable, usamos la función APILARH, como argumento matriz1, ponemos la variable “f”, como argumento matriz2, ponemos 0, este cero, nos va a valer para reiniciar el acumulado.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);g;APILARH(f;0);g)

Creamos otra variable, usamos la funcion SCAN, como argumento valor inicial, ponemos 0, como argumento array, ponemos la variable “g”, como argumento funcion, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);g;SI.ERROR(APILARH(f;0);0);h;SCAN(0;g;LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “y” es igual a cero, como argumento valor si verdadero, ponemos 0 multiplicado por la variable x, y, sumamos con la variable “y”, es decir, que se reinicie el acumulador, como argumento valor si falso, ponemos la suma de la variable “x” y la variable “y”.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);g;SI.ERROR(APILARH(f;0);0);h;SCAN(0;g;LAMBDA(x;y;SI(y=0;0*x+y;x+y)));h)

Aceptamos, y, vemos que tenemos realizado el acumulado por años.

Creamos otra variable, usamos la funcion ENCOL, y, como argumento ponemos la variable “h”.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);g;SI.ERROR(APILARH(f;0);0);h;SCAN(0;g;LAMBDA(x;y;SI(y=0;0*x+y;x+y)));i;ENCOL(h);i)

Como argumento calculo de LET, filtramos la variable “i”, siempre que dicha variable sea diferente a cero.

=LET(a;A2:A16;b;A2:C16;c;ENFILA(UNICOS(AÑO(a)));d;SI.ERROR(EXCLUIR(REDUCE(0;c;LAMBDA(x;y;APILARH(x;FILTRAR(b;AÑO(a)=y))));;1);0);e;ELEGIRCOLS(d;3;6;9;12);f;TRANSPONER(e);g;SI.ERROR(APILARH(f;0);0);h;SCAN(0;g;LAMBDA(x;y;SI(y=0;0*x+y;x+y)));i;ENCOL(h);FILTRAR(i;i<>0))

Pues, ya lo tenemos.

Miguel Angel Franco