Dividir pago entre trimestres

Tenemos las siguientes compañías, con una fecha, y, un pago para cada fecha.

Debemos de crear un informe donde debemos de dividir equitativamente el pago entre el trimestre actual y los próximos 3 trimestres, el modelo debe de quedar como sigue:

Es decir, cada compañía debe de aparece cuatro veces, una vez por trimestre, además, debe de aparecer el numero de trimestre junto con el año, y, la cantidad que corresponde a cada trimestre de cada compañía, que sale de dividir la cantidad de una compañía entre 4.

Empecemos…

En una celda, ponemos LET, creamos una variable, usamos la funcion SECUENCIA, como argumento filas, usamos la funcion CONTARA, como argumento seleccionamos la column A, restamos 1 para saltar el encabezado, ponemos la operación entre paréntesis, y, multiplicamos por 4, que son los trimestres.

=LET(a;SECUENCIA((CONTARA(A:A)-1)*4);a)

Tenemos una matriz desbordada en vertical desde el numero 1 al número 16.

Volvemos a la expresión de la variable “a”, antes de SECUENCIA, ponemos RESIDUO, vamos a calcular el resto de cada valor dividido entre 4, como argumento numero es la funcion SECUENCIA, como argumento numero de divisor ponemos 4, que son los trimestres que hay.

Obtenemos una matriz desbordada en vertical, podemos ver que queda dividida en cuatro grupos, donde cada grupo tiene los valores 1, 2, 3, y, 0.

Vamos a sustituir el valor 0 por el valor 4, para que siga la secuencia, para ello, usamos SUSTITUIR, como argumento texto, ponemos la variable “a”, como argumento texto antiguo, ponemos cero, como argumento texto nuevo, ponemos 4.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;SUSTITUIR(a;0;4);b)

Antes de SUSTITUIR, entre comillas dobles, ponemos “Q” de trimestre en inglés, concatenamos con la funcion SUSTITUIR, concatenamos, entre comillas dobles, con el guion medio y el numero 23, porque trabajamos con el año 2023.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";b)

Estamos preparando el modelo, ahora, tenemos la letra Q junto con el trimestre correspondiente, un guion medio, y, el año.

Creamos otra variable, usamos BYROW, como argumento array, ponemos la variable “a”, como argumento funcion, ponemos LAMBDA, creamos una variable, como argumento calculo de LAMBDA, usamos el condicional SI, preguntamos si la variable “x” es igual a 1, en ese caso, debe de poner la letra A, como argumento valor si falso, volvemos a preguntar, en este caso, si la variable “x” es igual a 2, en ese caso, que ponga la letra B, como argumento valor si falso del segundo condicional SI, preguntamos si la variable “x” es igual a 3, en ese caso, que ponga la letra C, como argumento valor si falso del tercer SI, que ponga la letra D.

Ordenamos.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));c)

Ya tenemos cuatro veces cada compañía, cuatro veces porque son los trimestres que tiene un año.

Ahora debemos de repartir la cantidad de una fecha entre cuatro trimestres, para ello, creamos otra variable, dividimos el rango C2:C5 (pagos) entre 4 trimestres.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;d)

Nos devuelve la cantidad que se debe asignar a cada trimestre de cada compañía.

Ahora, vamos a extraer cada carácter de cada cadena, para ello, creamos otra variable, usamos EXTRAE, como argumento texto ponemos la variable “d”, como argumento posición inicial, usamos SECUENCIA, omitimos le argumento filas, como argumento columnas, ponemos 3, que son los dígitos que consta cada cantidad, como argumento numero de caracteres, ponemos 1.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;e;EXTRAE(d;SECUENCIA(;3);1);e)

Obtenemos cada digito de cada cantidad.

Volvemos a la expresión de la variable “e”, usamos SUSTITUIR, como argumento texto es la funcion EXTRAE, como argumento texto antiguo, es la misma funcion EXTRAE, pero en el argumento columnas de SECUENCIA, ponemos 4, como argumento texto nuevo, ponemos la variable “d”.

¿Por qué cambiamos el argumento columnas del segundo EXTRAE?

Debemos de rellenar cuatro trimestres con el mismo valor, hasta este momento solo tenemos tres valores, al poner una cantidad mas en el argumento columnas del segundo EXTRAE vamos a obtener un error, porque no hay un cuarto valor, y, ese error, nos va a ayudar a obtener el cuarto valor.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;e;SUSTITUIR(EXTRAE(d;SECUENCIA(;3);1);EXTRAE(d;SECUENCIA(;4);1);d);e)

Usamos la funcion SI.ERROR, donde le indicamos que si la expresión anterior nos devuelve un error, que nos devuelva la variable “d”.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;e;SI.ERROR(SUSTITUIR(EXTRAE(d;SECUENCIA(;3);1);EXTRAE(d;SECUENCIA(;4);1);d);d);union;APILARH(c;b);e)

Ya tenemos las cuatro cantidades para cada trimestre de cada compañía.

Usamos la funcion ENCOL, para obtener las cantidades en una sola columna.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;e;ENCOL(SI.ERROR(SUSTITUIR(EXTRAE(d;SECUENCIA(;3);1);EXTRAE(d;SECUENCIA(;4);1);d);d));e)

Creamos una última variable, usamos APILARH, como argumento matriz1, ponemos la variable “c”, como argumento matriz2, ponemos la variable “b”, como argumento matriz3, ponemos la variable “e”.

=LET(a;RESIDUO(SECUENCIA((CONTARA(A:A)-1)*4);4);b;"Q"&SUSTITUIR(a;0;4)&"-23";c;ORDENAR(BYROW(a;LAMBDA(x;SI(x=1;"A";SI(x=2;"B";SI(x=3;"C";"D"))))));d;C2:C5/4;e;ENCOL(SI.ERROR(SUSTITUIR(EXTRAE(d;SECUENCIA(;3);1);EXTRAE(d;SECUENCIA(;4);1);d);d));union;APILARH(c;b;e);union)

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco