Buscar fechas de entregas para producto

Para el siguiente ejemplo, tenemos un modelo, donde tenemos una serie de productos, cada producto tiene una fecha de venta.

Tenemos dos columnas más, una con el nombre del producto y la fecha de entrega.

El problema, es que se ha ido entrando las ventas, pero, no las entregas de forma ordenada, por ejemplo, tenemos la venta del producto 3.

Si nos fijamos en las fechas de entrega, vemos que solo hay una fecha de entrega, que no se encuentra a continuación del producto3 de fechas de ventas, además, solo tenemos una fecha de entrega, y, se han vendido dos productos3, si nos fijamos en las fechas de ventas, vemos que solo puede la venta realizada en 02/03/2023 con la fecha de entrega 30/04/2023, porque la venta realizada en 02/05/2023, no puede ser entregada en 30/04/2023, en la fecha 30/04/2023 aún no se realizó la venta.

Fijémonos en el producto4, en las fechas de ventas.

Estos productos solo pudieron ser entregados en la fecha 13/03/2023, porque en la fecha 16/01/2023, aun no se realizó la venta.

Pues, en esto va a consistir el ejemplo, en buscar las fechas de entregas de cada producto, teniendo en cuenta, que, si tenemos diferentes fechas de entregas, debemos de buscar la más aproximada a la fecha de venta.

Como un producto ha podido ser vendido mas de una vez, y, tenemos mas de una fecha de entrega, el ejemplo, lo voy a realizar en filas, para poder copiar la expresión, en caso contrario, no podre arrastrar la expresión, porque dicha expresión, va a devolver más de un valor.

Empecemos…

Tenemos que trabajar con los valores únicos de los productos del rango A2:A20, igual que en otros ejemplos, vamos a usar LET, donde vamos a ir creando variables para ir dividiendo los pasos.

En la celda F2, ponemos LET, creamos una variable, y, nos traemos los valores únicos de productos.

Probamos dicha variable.

=LET(a;UNICOS(A2:A20);a)

El calculo de la siguiente variable, lo vamos a realizar en una celda aparte para ver su comportamiento, después, la añadiremos a LET.

Vamos a usar la función ENCONTRAR, para buscar el primer producto en el rango A2:A20, porque vamos a trabajar por filas, para acceder al primer producto de los productos únicos.

Ponemos la función ENCONTRAR, como argumento texto buscado, usamos la función INDICE, donde como argumento matriz, son los productos únicos, como argumento número de fila, vamos a usar la función FILA, como argumento ponemos F1, aunque podemos poner cualquier columna, pero, siempre debe de empezar desde 1, de esta manera, al arrastrar iremos tomando el siguiente producto, omitimos el argumento numero de columna, y, de forma predeterminada, tomara 1 columna, cerramos paréntesis de INDICE.

=ENCONTRAR(INDICE(F2#;FILA(F1))

Como argumento dentro del texto, seleccionamos los productos únicos.

Cerramos paréntesis.

=ENCONTRAR(INDICE(F2#;FILA(F1));F2#)

Aceptamos, y, obtenemos 1, que indica la posición de la primera letra del producto, que es donde encontró coincidencia, y, error para el resto de los productos, vemos que aparece dos 1, porque en el rango A2:A20, hay dos productos3.

Preguntamos con el condicional SI, como argumento prueba lógica, es la función ENCONTRAR, pero, no la igualamos a nada, y, de forma predeterminada comparara con aquellos valores que no son error.

=SI(ENCONTRAR(INDICE(F2#;FILA(F1));A2:A20)

Como argumento valor si verdadero, ponemos el rango A2:A20, como argumento valor si falso, ponemos un texto en blanco, cerramos paréntesis.

=SI(ENCONTRAR(INDICE(F2#;FILA(F1));A2:A20);B2:B20;"")

Aceptamos, tenemos fecha y hora donde era 1, y, error donde no hay coincidencia.

Lo ponemos en fecha corta.

Usamos la función SI.ERROR para quitar el error.

=SI.ERROR(SI(ENCONTRAR(INDICE(F2#;FILA(F1));A2:A20);B2:B20;"");"")

Pues este calculo es el valor de la variable “b” de la función LET, donde sustituimos F2# por la variable “a”.

Fijamos A2:A20, y, B2:B20.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(F1));$A$2:$A$20);$B$2:$B$20;"");"");b)

Tenemos el mismo resultado, ya tenemos las fechas donde se vendió el producto3.

La siguiente variable, el calculo es el mismo que el anterior, pero, buscamos el primer producto en el rango C2:C20, y, como argumento valor si verdadero del condicional SI, ponemos el rango D2:D20.

Fijamos los rangos de búsquedas.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");c)

Obtenemos la o las fechas de entregas para el producto3.

Ya tenemos las fechas de ventas y de entregas para el producto3.

Creamos otra variable, usamos la función APILARH, como argumento matriz1, es la variable “b”, pero debemos de traerla sin los blancos, por lo que vamos a usar la función FILTRAR, donde filtramos la variable “b”, siempre que dicha variable sea diferente a blanco.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"")

Como argumento matriz2, usamos BUSCARX, como argumento valor buscado vuelve a ser la variable “b” sin blancos.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"")

Como argumento matriz de búsqueda, es la variable “c” sin blancos.

LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"")

Como argumento matriz devuelta, vuelve a ser la variable “c” sin blancos.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"")

Como argumento si no se encuentra, ponemos entre comillas dobles, Sin entrega.

LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"");"Sin entrega";1

Como argumento modo de coincidencia, seleccionamos 1, es decir, coincidencia exacta, o, siguiente elemento mayor.

Cerramos paréntesis.

Probamos la variable.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"");"Sin entrega";1));d)

Aceptamos, y, obtenemos una matriz desbordada de dos filas y dos columnas, en la primera columna, tenemos las fechas de ventas, en la segunda columna, tenemos las fechas de entregas, pero, vemos que para la fecha 02/05/2023, obtenemos sin entrega, porque la única fecha que tenemos es 30/04/2023.

Como argumento calculo de LET, ponemos APILARH, como argumento matriz1, es el primer producto, para ello, volvemos a usar INDICE.

= LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"");"Sin entrega";1));APILARH(INDICE(a;FILA(A1))

Como argumento matriz2, como vamos a trabajar en columnas, usamos la función ENFILA, que nos pondrá los valores en una fila, y, como argumento es la variable “d”.

Cerramos paréntesis.

=LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"");"Sin entrega";1));APILARH(INDICE(a;FILA(A1));ENFILA(d)))

Aceptamos, y, podemos ver en una matriz desbordada en horizontal, el nombre del producto la primera fecha de venta, la primera fecha de entrega, la segunda fecha de venta, y, la segunda fecha de entrega.

Si nos fijamos en las fechas de ventas y de entregas del producto9, el resultado sería el siguiente.

Vayamos a la hoja de cálculo, y, veamos los resultados.

Podemos ver que son los mismos resultados.

Pero, tenemos algunos errores, porque no tienen una fecha de entrega.

Después del signo igual, usamos la función SI.ERROR, como argumento valor es la expresión anterior, como argumento valor si error, ponemos entre comillas dobles, Sin entrega.

Cerramos paréntesis.

=SI.ERROR(LET(a;UNICOS($A$2:$A$20);b;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$A$2:$A$20);$B$2:$B$20);"");c;SI.ERROR(SI(ENCONTRAR(INDICE(a;FILA(A1));$C$2:$C$20);$D$2:$D$20);"");d;APILARH(FILTRAR(b;b<>"");BUSCARX(FILTRAR(b;b<>"");FILTRAR(c;c<>"");FILTRAR(c;c<>"");"Sin entrega";1));APILARH(INDICE(a;FILA(A1));ENFILA(d)));"Sin entrega")

Aceptamos, arrastramos, y, donde había error, ahora, aparece Sin entrega.

Seleccionamos todos los resultados, desplegamos formato condicional, desde la pestaña de inicio, desplegamos reglas para resaltar celdas, y, seleccionamos es igual.

Se abre la ventana es igual a, en la ventana de izquierda ponemos Sin entrega, en la ventana de la derecha, he seleccionado un relleno verde con texto verde oscuro.

Aceptamos.

Vamos a dar un formato al modelo.

Vamos a aplicar un formato condicional al modelo, lo haremos por columnas impares, para que queden resaltadas las columnas de fecha de venta, para ello, en una celda, ponemos la función COLUMNA, y, como argumento, seleccionamos el rango G2:P2.

=COLUMNA(G2:P2)

Obtenemos una matriz desbordada en horizontal con el número de cada columna.

Vamos a resaltar las columnas impares, que corresponde a las columnas de fecha de venta, para ello, usamos la función ES.IMPAR, y, como argumento es la función anterior.

=ES.IMPAR(COLUMNA(G2:P2))

Obtenemos una matriz desbordada en horizontal con VERDADERO donde es impar y FALSO donde no lo es.

Pues, esta es la expresión para usar en formato condicional, ya sabemos que debemos de usar una formula en formato condicional que nos devuelva un valor booleano.

Seleccionamos las fechas.

Vamos a la pestaña de inicio, desplegamos formato condicional, seleccionamos nueva regla.

Seleccionamos la ultima opción, y, en la ventana de dar formato, pegamos la expresión.

Damos el formato que más nos interese, aceptamos, y, vemos las columnas de fecha de venta con dicho formato.

Pero, vemos que las celdas donde no hay fecha de venta, también, aparece resaltada, y, vamos a dejarla en blanco, para ello, volvemos a seleccionar el modelo, añadimos una nueva regla, y, en la ventana de dar formato, ponemos:

No seleccionamos ningún formato.

Aceptamos, y, ya lo tenemos.

Si queremos ver los productos en horizontal, transponemos todo el modelo, pero, se transpone sin formato.

Debemos de volver a dar formato.

Vemos que donde no hay valores, aparece cero, vamos a la celda donde hemos puesto TRANSPONER, vamos a preguntar que, si el resultado de TRANSPONER es blanco, que ponga blanco, en caso contrario, que devuelva la función TRANSPONER.

=SI(TRANSPONER(F1:P11)="";"";TRANSPONER(F1:P11))

Aceptamos, y, solo tenemos los datos.

Volvemos a dar formato condicional para resaltar los valores Sin entrega, lo hacemos igual que antes.

En este caso, tenemos que resaltar filas alternas, no columnas, la expresión es la misma que antes, pero cambiamos COLUMNA por FILA, pero, para resaltar las fechas de ventas, usamos la función ES.PAR.

Aceptamos, y, ya lo tenemos.

Igual que antes vamos a quitar el resaltado de las celdas que no tienen fecha de venta, siendo la expresión la misma que antes.

En formato, seleccionamos sin relleno.

Por último, vamos a crear una lista desplegable con los productos, después, seleccionamos un producto, y, obtendremos las fechas de ventas y entregas del producto seleccionado.

La lista desplegable la haremos en la celda G25, vamos a la pestaña de datos, dentro de herramientas de datos, seleccionamos validación de datos.

Desplegamos permitir, seleccionamos lista, y, en la ventana origen, seleccionamos los productos únicos.

Aceptamos.

Seleccionamos un producto.

En la celda H25, usamos BUSCARX, como argumento valor buscado, es el producto seleccionado, como argumento matriz de búsqueda, seleccionamos el rango G13:P13, como argumento matriz devuelta, seleccionamos el rango G14:P23.

Cerramos paréntesis.

=BUSCARX(G25;G13:P13;G14:P23)

Aceptamos, y, tenemos una matriz desbordada en vertical con las fechas del producto seleccionado.

Vamos a poner delante de las fechas el encabezado, para ello, ponemos LET, creamos una variable, como valor de la variable, preguntamos que si el resultado de BUSCARX es diferente a blanco.

=LET(a;SI(BUSCARX(G25;G13:P13;G14:P23)<>""

Como argumento valor si verdadero, seleccionamos el rango F14:F23, concatenamos con dos puntos, concatenamos con la función BUSCARX, como argumento valor si falso, ponemos un texto en blanco, cerramos paréntesis del condicional SI.

=LET(a;SI(BUSCARX(G25;G13:P13;G14:P23)<>"";F14:F23&": "&BUSCARX(G25;G13:P13;G14:P23);"")

Como argumento cálculo de LET, filtramos la variable “a”, siempre que dicha variable sea diferente a blanco.

=LET(a;SI(BUSCARX(G25;G13:P13;G14:P23)<>"";F14:F23&": "&BUSCARX(G25;G13:P13;G14:P23);"");FILTRAR(a;a<>""))

Obtenemos las fechas del producto seleccionado.

Vemos que las fechas han perdido el formato, pues, vamos a usar la función TEXTO para que nos devuelva el formato dd/mm/aaaa.

=LET(a;SI(BUSCARX(G25;G13:P13;G14:P23)<>"";F14:F23&": "&TEXTO(BUSCARX(G25;G13:P13;G14:P23);"dd/mm/aaaa");"");FILTRAR(a;a<>""))

Aceptamos, y, ya lo tenemos.

Nos falta poner los bordes de forma dinamica, para ello, vamos a seleccionar el producto de más fechas.

Seleccionamos las fechas, desplegamos formato condicional, seleccionamos nueva regla, seleccionamos la última opción, en la ventana dar formato, ponemos:

Hacemos clic en formato, seleccionamos la pestaña borde, y, marcamos contorno.

Aceptamos, y, ya tenemos los bordes dinámicos.

Miguel Angel Franco