Un lector preguntaba por una fórmula para obtener la fecha inmediata anterior a otra dada, según una condición:
Tal y como se muestra en la imagen:
El objetivo es claro, para cada fecha y artículo del segundo rango D3:E8 debemos recuperar la fecha correspondiente anterior más próxima del primer rango A3:B8.
Para ello construimos la siguiente matricial que insertamos en F3:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
(recuerda validarla presionado Ctrl+Mayuscula+Enter!!)
A continuación copiaremos y pegaremos al resto de celdas F4:F8.
¿Cómo funciona esta fórmula matricial?.
Nos basamos en el cálculo de diferencias entre fechas para llegar a la más próxima anterior; esto es, con la parte más profunda de la fórmula, llegamos a conseguir unas fechas para aquellos casos en los que coincide el artículo.. así discriminamos las fechas que no nos interesan:
SI($A$3:$A$8=D3;$B$3:$B$8;HOY())
Sobre esta fecha obtenida aplicamos un nuevo condicional para calcular la diferencia con la fecha de referencia:
=SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY()))
discriminando además aquellas diferencias negativas (solo nos quedamos con diferencias positivas); esto es así por que buscamos sólo fechas anteriores (una diferencia negativa indicaría que la fecha es posterior).!!
Si de esas diferencias obtenidas nos quedamos con el valor mínimo:
MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
y ese valor mínimo lo restamos a la fecha de referencia, obtenemos la Fecha buscada:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
Es decir, sobre el ejemplo de la imagen (fecha de referencia 15/07/2016), desde la fecha de referencia nos desplazamos 10 días antes... lo que nos lleva a la fecha de origen: 05/07/2016
Tal como se muestra en la imagen:
Tal y como se muestra en la imagen:
El objetivo es claro, para cada fecha y artículo del segundo rango D3:E8 debemos recuperar la fecha correspondiente anterior más próxima del primer rango A3:B8.
Para ello construimos la siguiente matricial que insertamos en F3:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
(recuerda validarla presionado Ctrl+Mayuscula+Enter!!)
A continuación copiaremos y pegaremos al resto de celdas F4:F8.
¿Cómo funciona esta fórmula matricial?.
Nos basamos en el cálculo de diferencias entre fechas para llegar a la más próxima anterior; esto es, con la parte más profunda de la fórmula, llegamos a conseguir unas fechas para aquellos casos en los que coincide el artículo.. así discriminamos las fechas que no nos interesan:
SI($A$3:$A$8=D3;$B$3:$B$8;HOY())
Sobre esta fecha obtenida aplicamos un nuevo condicional para calcular la diferencia con la fecha de referencia:
=SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY()))
discriminando además aquellas diferencias negativas (solo nos quedamos con diferencias positivas); esto es así por que buscamos sólo fechas anteriores (una diferencia negativa indicaría que la fecha es posterior).!!
Si de esas diferencias obtenidas nos quedamos con el valor mínimo:
MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
y ese valor mínimo lo restamos a la fecha de referencia, obtenemos la Fecha buscada:
=E3-MIN(SI(E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())>0;E3-SI($A$3:$A$8=D3;$B$3:$B$8;HOY())))
Es decir, sobre el ejemplo de la imagen (fecha de referencia 15/07/2016), desde la fecha de referencia nos desplazamos 10 días antes... lo que nos lleva a la fecha de origen: 05/07/2016
Tal como se muestra en la imagen:
| [...] necesito hallar la fecha de compra de unos consumos, tiene que ser la próxima anterior[...] |
Excel
via [EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS] [http://ift.tt/2cR4l6R]
September 19, 2016 at 09:06PM
No hay comentarios.:
Publicar un comentario