Rellenar celdas vacias con fechas

Para el siguiente ejemplo, tenemos un modelo, donde tenemos una columna con centros comerciales, en horizontal, tenemos seis fechas de ventas para cada centro comercial, pero, por error, hay fechas que no se han colocados.

Debemos de crear un nuevo reporte, y, sustituir donde falta una fecha por la fecha de la celda anterior, pero, si es la fecha de la primera celda la que falta, debe de ser sustituida por la celda siguiente.

Empecemos…

En la celda A9, ponemos la función ESBLANCO, como argumento seleccionamos las fechas.

Obtenemos una matriz desbordada con VERDADERO donde es blanco, y, FALSO donde no lo es.

Igual que en otros ejemplos, vamos a usar la función LET, y, vamos dividendo en variables las funciones que vamos a utilizar.

Ponemos LET, creamos una variable, usamos el condicional SI, como argumento prueba lógica, es la función ESBLANCO.

=LET(a;SI(ESBLANCO(B2:G7)

Como argumento valor si verdadero, usamos la función INDICE, como argumento matriz, ponemos las columnas B:G.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G

Como argumento número de fila, usamos la función FILA, y, como argumento ponemos el rango B2:B7, como argumento número de columna, usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 6, que son las seis columnas de fechas, y, restamos 1, para que nos devuelva la fecha anterior.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1)

Como argumento valor si falso, que nos devuelva el rango B2:G7.

Cerramos paréntesis.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7)

Probamos la variable “a”, y, vemos que tenemos las fechas corregidas.

Excepto, la fecha en blanco de la primera columna, porque al pedirle que nos devuelva la celda anterior, para este caso, no hay celda anterior.

Como argumento calculo de LET, ponemos la funcion APILARH, como argumento matriz1, usamos le condicional SI, como argumento prueba logica, usamos la funcion INDICE, como argumento matriz, es la variable “a”, como argumento numero de fila, usamos SECUENCIA, como arguemnto filas, ponemos 6, como argumento numero de columna de INDICE ponemos 1, cerramos parentisis de INDICE, e, igualamos a cero.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);APILARH(SI(INDICE(a;SECUENCIA(6);1)=0

Como argumento valor si verdadero, usamos, de nuevo, INDICE, como argumento matriz, es la variable “a”, como argumento numero de fila, usamos SECUENCIA, como argumento filas, ponemos 6, como argumento numero de columna, ponemos 2.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);APILARH(SI(INDICE(a;SECUENCIA(6);1)=0;INDICE(a;SECUENCIA(6);2)

Como argumento valor si falso, usamos INDICE, como argumento matriz, es la variable “a”, como argumento número de fila, usamos SECUENCIA, como argumento filas, ponemos 6, como argumento numero de columna, ponemos 1.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);APILARH(SI(INDICE(a;SECUENCIA(6);1)=0;INDICE(a;SECUENCIA(6);2);INDICE(a;SECUENCIA(6);1))

Este es el argumento matriz1 de APILARH, estamos sustituyendo la fecha vacía de la primera columna, por la fecha de la derecha, como argumento matriz2, usamos INDICE, como argumento matriz, ponemos la variable “a”, como argumento número de fila, ponemos SECUENCIA, como argumento filas, ponemos 6, como argumento columnas, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos 5, como argumento inicio, ponemos 2, como argumento paso, ponemos 1, nos estamos trayendo todas las fechas, excepto, las fechas de la primera columna.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);APILARH(SI(INDICE(a;SECUENCIA(6);1)=0;INDICE(a;SECUENCIA(6);2);INDICE(a;SECUENCIA(6);1));INDICE(a;SECUENCIA(6);SECUENCIA(;5;2;1))))

Aceptamos, y, vemos todas las fechas corregidas.

Vamos a crear una variable, y, almacenamos la expresion anterior.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);b;APILARH(SI(INDICE(a;SECUENCIA(6);1)=0;INDICE(a;SECUENCIA(6);2);INDICE(a;SECUENCIA(6);1));INDICE(a;SECUENCIA(6);SECUENCIA(;5;2;1)));b)

Vamos a traernos los encabezados.

Creamos otra variable, usamos APILARH, como argumento matriz1, ente comillas dobles, ponemos Fecha, como argumento matriz2, ponemos la variable “b”, probamos la variable.

=LET(a;SI(ESBLANCO(B2:G7);INDICE(B:G;FILA(B2:G7);SECUENCIA(;6)-1);B2:G7);b;APILARH(SI(INDICE(a;SECUENCIA(6);1)=0;INDICE(a;SECUENCIA(6);2);INDICE(a;SECUENCIA(6);1));INDICE(a;SECUENCIA(6);SECUENCIA(;5;2;1)));c;APILARV("fecha";b);c)

Vemos que solo en la primera celda aparece la palabra Fecha.

Usamos la función SI.ERROR, como argumento valor, es la función APILARH, como argumento valor si error, entre comillas dobles, ponemos “Fecha”.

Vemos que ya aparece la palabra Fecha en todos los encabezados.

Nos falta traernos la primera columna, para ello, antes de SI.ERROR, ponemos APILARH, como argumento matriz1, seleccionamos el rango A1:A7, como argumento matriz2, es la función APILARV.

Aceptamos, y, ya lo tenemos.

Para el siguiente ejemplo, vamos a trabajar con el modelo obtenido en el ejemplo anterior, donde hay tres celdas consecutivas vacías.

Esas tres celdas vacías, deben de ser rellenadas con la fecha de la izquierda, lo mismo que para el ejemplo anterior.

En la celda A9, usamos LET, creamos una variable, como expresión pedimos la longitud del rango A1:G7.

=LET(a;LARGO(A1:G7);a)

Obtenemos una matriz desbordada con la longitud de cadena, donde no hay fechas, aparece cero.

Igualamos a cero.

=LET(a;LARGO(A1:G7)=0;a)

Tenemos una matriz desbordada con FALSO donde no es cero, y, VERDADERO donde lo es.

Preguntamos con el condicional SI, que, si la longitud es igual a cero, que nos devuelva la columna desde A1:G1, en caso contrario, que nos devuelva un texto en blanco.

=SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);"")

Tenemos una matriz desbordada con blanco donde no es cero, y, la columna correspondiente donde es cero.

Vamos a quitar los blancos, para ello, debemos de trabajar con los datos en filas, por lo que vamos a hacer uso del a función ENCOL, cono argumento, usamos la función FILTRAR, como argumento array, es la expresión anterior, como argumento include, sigue siendo la expresión anterior, ponemos el símbolo de comparación indistinto, y, dobles comillas dobles.

Probamos la variable.

=LET(a;TRANSPONER(FILTRAR(ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""));ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""))<>""));a)

Tenemos solo los valores.

En una celda aparte, vamos a realizar el calculo que restaremos a la expresión anterior.

Preguntamos si la longitud del rango A1:G7 es igual a cero, que ponga uno, en caso contrario, que ponga 0.

=SI(LARGO(A1:G7)=0;1;0)

Tenemos una matriz desbordada con cero donde la longitud no es cero, y, 1 donde lo es.

Sumamos los resultados.

=SUMA(SI(LARGO(A1:G7)=0;1;0))

Nos devuelve 3.

Usamos la función SECUENCIA, omitimos el argumento filas, como argumento columnas, es la expresión anterior.

=SECUENCIA(;SUMA(SI(LARGO(A1:G7)=0;1;0)))

Tenemos una matriz desbordada en horizontal desde el valor 1 al valor 3.

Anteriormente, obtuvimos los valores 3, 4, y, 5, si a 3 restamos 1, nos devuelve 2, si a 4, restamos 2, nos devuelve 2, y, si a 5, restamos 3, también, nos devuelve 2.

El valor 2 es la columna para rescatar la fecha.

Vamos a la función LET, y, realizamos la resta.

=LET(a;TRANSPONER(FILTRAR(ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""));ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""))<>""))-SECUENCIA(;SUMA(SI(LARGO(A1:G7)=0;1;0)));a)

Obtenemos una matriz desbordada de tres columnas con el número 2.

Solo vamos a necesitar el primer valor.

Creamos otra variable, preguntamos si el blanco el rango A1:G7.

=LET(a;TRANSPONER(FILTRAR(ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""));ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""))<>""))-SECUENCIA(;SUMA(SI(LARGO(A1:G7)=0;1;0)));b;SI(ESBLANCO(A1:G7)

Como argumento valor si verdadero, usamos INDICE, como argumento matriz, es el rango A1:G7, como argumento número de fila, es la fila en el rango A1:A7.

=LET(a;TRANSPONER(FILTRAR(ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""));ENCOL(SI(LARGO(A1:G7)=0;COLUMNA(A1:G1);""))<>""))-SECUENCIA(;SUMA(SI(LARGO(A1:G7)=0;1;0)));b;SI(ESBLANCO(A1:G7);INDICE(A1:G7;FILA(A1:A7)

Como argumento numero de columna, volvemos a usar la función INDICE, como argumento matriz, es la variable “a”, como argumento numero de fila, ponemos 1, como argumento numero de columna, ponemos 1, es decir, de la matriz donde teníamos los tres 2, vamos a rescatar el primer 2.

Como argumento valor si falso del condicional SI, ponemos el rango A1:G7, y, probamos la variable.

Ya lo tenemos.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Rellenar celdas vacias con fechas

Entradas recientes

Comments