Rellenar fechas que faltan

Jaime Franco Jimenez
9 oct 2023
4 Min. de lectura

Tenemos un modelo donde en la primera columna, tenemos una serie de almacenes, y, en una segunda columna, una serie de fechas asociadas a cada almacén.

Si hay una discontinuidad en las fechas de un almacén, debemos de rellenar las fechas que faltan, por ejemplo, la primera fecha para el almacén A es 01/05/2023, vemos que la siguiente fecha no es secuencial, aparece la fecha 03/05/2023, así hasta llegar a la fecha 09/05/2023, es decir, para el almacena A la primera fecha es 01/05/2023 y la ultima es 09/05/2023, pues, debemos de rellenar con los días faltantes, es decir, desde el día 01/05/2023 al 09/05/2023.

Así con el resto de los almacenes.

Empecemos…

En la celda D2, usamos LET, creamos una variable, usamos BYROW, como argumento array, usamos la funcion UNICOS, y, como argumento seleccionamos el rango A2:A14, vamos a obtener los valores únicos de los almacenes, como argumento funcion, ponemos LAMBDA, creamos una variable, usamos LET, creamos una variable.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m

Usamos el condicional SI, como argumento prueba lógica, preguntamos si el rango A2:A14 (almacenes) es igual a la variable “x”, en ese caso, que nos devuelva el rango B2:B14 (fechas), en caso contrario, que devuelva un texto en blanco.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"")

Si probamos el condicional SI en una celda, y, cambiamos la variable “x”, por los valores únicos del rango A2:A14.

=SI(A2:A14=UNICOS(A2:A14);B2:B14;"")

Obtenemos una matriz desbordada donde solo tenemos la primera fecha como coincidencia, en el resto aparecen errores.

Esto es debido a que el condicional SI no puede comparar con cada valor único, por eso, lo hemos hecho con la funcion BYROW, donde hemos almacenado en una variable los valores únicos de almacenes, porque con la funcion BYROW, si podemos comparar cada valor.

Creamos otra variable, usamos la funcion MAX, como argumento ponemos la variable “m”, restamos con la fecha mínima de la variable “m”, y, sumamos 1, como argumento calculo de LET, ponemos la variable n, cerramos paréntesis.

Probamos variable “a”.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));a)

Con esta funcion, comparamos cada fecha del rango A2:A14 con cada valor de la variable “x”, que son los almacenes únicos, cuando la variable “x” es igual al almacén A, obtenemos las fechas:

En la variable “n”, restamos la fecha máxima del almacén A, 09/05/2023 con la fecha mínima, 01/05/2023, lo que nos devuelve 8 días, porque la fechas 01/05/2023 no se cuenta, por eso sumamos 1, y, nos devuelve 9.

Ahora, la variable “x” vale el almacén B, y, realiza el mismo procedimiento, por lo que obtenemos una matriz desbordada con los días que debe de haber en cada almacén.

Creamos otra variable, la vamos a realizar por partes para poder entenderla, nos traemos los valores únicos del rango A2:A14.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;UNICOS(A2:A14);b)

Usamos la funcion REPETIR, como argumento texto, es la funcion UNICOS, como argumento numero de veces, ponemos la variable “a”.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;REPETIR(UNICOS(A2:A14);a);b)

Tenemos una matriz desbordada donde se ha repetido cada almacén el número de veces que indica la variable “a”.

Concatenamos la expresión anterior.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;CONCAT(REPETIR(UNICOS(A2:A14);a));b)

Tenemos una celda todos los almacenes.

Usamos la funcion EXTRAE, como argumento texto es la expresión anterior, como argumento posición inicial, usamos SECUENCIA, como argumento filas, usamos SUMA, y, sumamos la variable “a”, como argumento número de caracteres, ponemos 1.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;EXTRAE(CONCAT(REPETIR(UNICOS(A2:A14);a));SECUENCIA(SUMA(a));1);b)

Obtenemos las veces que deben de aparecer cada almacén.

Creamos otra variable, usamos MAP, como argumento array, usamos SECUENCIA, como argumento filas, usamos la funcion FILAS y como argumento ponemos la variable “b”, vamos a obtener una matriz desbordada en vertical, como la variable “b” consta de 23 filas, pues, desde el numero 1 al número 23.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;EXTRAE(CONCAT(REPETIR(UNICOS(A2:A14);a));SECUENCIA(SUMA(a));1);c;MAP(SECUENCIA(FILAS(b))

Como argumento funcion, ponemos LAMBDA, creamos una variable, ponemos la funcion SUMA, como argumento, ponemos el doble signo negativo, abrimos un paréntesis, ponemos la funcion TOMAR, como argumento matriz, ponemos la variable “b”, como argumento filas, ponemos la variable “x”.

Ponemos el signo igual, la funcion ELEGIRFILAS, como argumento matriz, ponemos la variable “b”, como argumento numero de fila1, ponemos la variable “x”.

Cerramos paréntesis.

=LET(a;BYROW(UNICOS(A2:A14);LAMBDA(x;LET(m;SI(A2:A14=x;B2:B14;"");n;MAX(m)-MIN(m)+1;n)));b;EXTRAE(CONCAT(REPETIR(UNICOS(A2:A14);a));SECUENCIA(SUMA(a));1);c;MAP(SECUENCIA(FILAS(b));LAMBDA(x;SUMA(--(TOMAR(b;x)=ELEGIRFILAS(b;x)))));c)

Obtenemos la secuencia que debemos de seguir con cada almacén.

Pero, veamos como funcion TOMAR y ELEGIRFILAS.

La funcion TOMAR toma el primer valor de la variable “b”, que es el almacén A.

La funcion ELEGIRFILAS, toma la primera fila de la variable “b”, por lo que nos devuelve el almacén A.

En la celda F2, tenemos los almacenes las veces que se deben de repetir.

=TOMAR(F2#;1)=ELEGIRFILAS(F2#;1)

Nos devuelve VERDADERO, porque ambos valores son iguales, como tenemos el doble signo negativo, nos devuelve 1, el cual sumamos, y, nos devuelve 1.

Ahora, con la funcion TOMAR, tomamos los dos primeros valores de la variable “b”.

=TOMAR(F2#;2)

E igualamos a la segunda fila de la variable “b”.

=ELEGIRFILAS(F2#;2)

Como las dos A devueltas por la funcion TOMAR coincide con el resultado de la funcion ELEGIRFILAS, nos devuelve dos unos, que al sumarlos nos devuelve 2.

Vamos a la fila 11, donde está el almacén B.

=TOMAR(F2#;10)

Como resultado tenemos una matriz desbordada donde tenemos A en todas las celdas, excepto, en la última celda, que tenemos B.

La funcion ELEGIRFILAS, nos devuelve B.

=ELEGIRFILAS(F2#;10)

Al realizar la comparación, tenemos FALSO en todas las celdas, excepto en la última, que tenemos VERDADERO.

Cuando ponemos el doble digno negativo, tenemos un solo 1.

Y, al sumarlo nos devuelve 1, quiere decir que, al cambiar de almacén, se reinicia el contador, podíamos decir.

Nos queda rellenar las fechas de forma secuencial para cada almacén.

Creamos otra variable, vamos a usar la funcion SCAN, como argumento valor inicial, ponemos 0, como argumento array, usamos el condicional SI, como argumento prueba lógica, preguntamos si la variable “c” es igual a 1.

Como argumento valor si verdadero, usamos la funcion BUSCARV, como argumento valor buscado, ponemos la variable “b”, como argumento matriz_tabla, seleccionamos el rango A2:B14, como argumento numero de columna, ponemos 2, y, como argumento rango, o, tipo de coincidencia, ponemos exacta.

Como argumento valor si falso, ponemos un texto en blanco.

Como argumento funcion de SCAN, ponemos LAMBDA, creamos dos variables, usamos el condicional SI, preguntamos si la variable “y” es igual a blanco, en ese caso, que nos devuelva la variable “x” mas 1, en caso contrario, que devuelva la variable “y”.

Tenemos las fechas secuenciales para cada almacén.

Usamos el argumento calculo de LET, usamos APILARH, como argumento matriz1, ponemos la variable “b”, como argumento matriz2, ponemos la variable “e”.

Ya tenemos nuestro ejercicio resuelto.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Rellenar fechas que faltan

Entradas recientes

Comentarios