Menú desplegable de validación de datos de fecha

En esta publicación, crearemos dos celdas desplegables de validación de datos que brindan la posibilidad de seleccionar fechas de origen y destino en función de las fechas de transacción almacenadas en los datos de origen. Esto garantizará que las selecciones de fechas se encuentren dentro de un rango válido de fechas, es decir, aquellos meses con datos en la tabla. Esta técnica es dinámica, de modo que a medida que se agregan o eliminan transacciones, es fácil actualizar las listas desplegables para reflejar las opciones de fechas válidas basadas en las fechas de las transacciones.

Objetivo

Antes de profundizar en la mecánica, revisamos nuestro objetivo.

Exportamos datos de nuestro sistema de contabilidad de forma regular y los utilizamos para generar algunos informes. Nos gustaría brindarle al usuario la posibilidad de seleccionar una fecha Desde y Hasta según las fechas de las transacciones.

Considere la exportación de transacciones a continuación.

Según los datos de la tabla, queremos proporcionar dos menús desplegables que permitan al usuario seleccionar una fecha Desde y una fecha Hasta, de la siguiente manera.

Las transacciones en la tabla comienzan el 11/01/2015. Por lo tanto, queremos que nuestro menú desplegable Fecha inicial comience con el 1/1/2015 e incluya el primer día del mes para todos los meses con datos, como 1/2/2015 y 1/3/2015. Si, en el próximo período, los datos incluyen transacciones de diciembre de 2014, nos gustaría que el menú desplegable Fecha inicial incluya una opción de fecha de inicio del 1/12/2014. La misma lógica para Hasta la fecha, queremos que el menú desplegable incluya el último día del mes para todos los meses que tengan transacciones, por ejemplo, 31/01/2015, 28/02/2015 y 31/03/2015.

¿Suena bien? Muy bien, pasemos a la mecánica.

Detalles

Para reanudar nuestro plan de juego, almacenaremos las transacciones de datos en una tabla, crearemos dos columnas auxiliares en la tabla que se completarán automáticamente a medida que se agreguen nuevas transacciones, crearemos una tabla dinámica intermedia para proporcionar dinámicamente una fila para cada combinación de fechas. Desde y Hasta, utilice referencias con nombre dinámicos para capturar los valores de la tabla dinámica y configure la validación de datos para proporcionar el menú desplegable en la celda.

Procedamos paso a paso.

Colina baja

Es muy importante almacenar las transacciones de datos en una tabla (Insertar Tabla). En este caso, garantizará que cualquier transacción nueva pegada en períodos futuros fluya automáticamente a través del libro de trabajo, ya que las tablas se expanden automáticamente para incluir nuevas filas de datos. Además, las tablas completan automáticamente las fórmulas de las columnas calculadas… esto garantizará que nuestras columnas auxiliares completen automáticamente cualquier nueva fila de datos pegada en la tabla en el futuro.

columnas auxiliares

Crearemos dos nuevas columnas auxiliares. Nos aseguraremos de colocarlos a la derecha de los datos exportados para que en períodos futuros podamos pegar nuevas transacciones en la tabla a medida que aparezcan. Excel completará automáticamente las fórmulas a través de las nuevas transacciones.

Las columnas auxiliares proporcionarán el último día del mes y el primer día del mes para cada fecha de transacción. Llamaremos a las nuevas columnas EOM y BOM para fin de mes y comienzo de mes.

La función que usaremos para calcular el valor de EOM es la función EOMONTH. La función EOMONTH es probablemente mi función de fecha favorita y devuelve el último día del mes. Incluso nos permite sumar o reiniciar meses para poder avanzar o retroceder si es necesario. La fórmula para la columna EOM es:

=EOMES([@Fecha],0)

Dónde:

• [@Date] es la referencia de la tabla estructurada para la columna de fecha de la fila actual
• 0 es el número de meses a sumar o restablecer de la fecha de la transacción; 0 ya que queremos el último día del mes de transacción

Los resultados se muestran a continuación.

Usaremos una fórmula similar para calcular la columna BOM. El único inconveniente es que, dado que la función EOMONTH devuelve el último día del mes, necesitaremos agregar un día a su resultado para calcular el primer día del mes siguiente. Entonces, la fórmula para la columna BOM es la siguiente:

=EOMES([@Fecha],-1)+1

Dónde:

• [@Date] es el valor de la fecha
• -1 resta un mes desde la fecha de la transacción

Los resultados se muestran a continuación.

Dado que los datos se almacenan en una tabla, Excel completará automáticamente estas columnas auxiliares para cualquier fila de datos nueva.

Tabla dinámica

Con nuestras columnas auxiliares en su lugar, es hora de crear la tabla dinámica. La tabla dinámica generará dinámicamente una fila para cada EOM y BOM únicos. Insertamos el campo BOM en el área de filas, el campo EOM en el área de filas, cambiamos el diseño del informe a tabular, eliminamos subtotales y totales generales, eliminamos el formato de encabezados de fila, eliminamos los botones +/- y los encabezados. de campo como se muestra a continuación.

La tabla dinámica genera los valores Desde y Hasta que necesitamos. Cada vez que se pegan nuevas transacciones en la tabla, podemos actualizar la tabla dinámica según sea necesario haciendo clic en el botón Actualizar (Herramientas de tabla dinámica Actualizar). Además, podemos ordenar la tabla dinámica en orden ascendente por el campo BOM para garantizar que las nuevas filas del informe aparezcan en orden cronológico (active la tabla dinámica y luego use el comando Inicio Ordenar).

Referencias nombradas

Ahora, necesitamos configurar dos referencias con nombre que podamos usar en la fuente de la lista de validación de datos. Los nombres deben adaptarse dinámicamente a las dimensiones de la tabla dinámicamente y aumentar o disminuir en consecuencia.

Hay numerosas formas de crear un rango con nombre dinámico e ilustraré una de esas posibilidades. Si prefiere otro método, compártalo publicando un comentario a continuación.

Para la columna Desde fecha, usaremos el Administrador de nombres (Fórmulas Administrador de nombres) para configurar un nuevo nombre, dd_datefrom. El nombre incluye el rango que se extiende desde la celda B7 en la hoja de trabajo Listas hasta la última celda en la tabla dinámica con la siguiente fórmula:

=¡Lista!$B$7:ÍNDICE(¡Lista!$B:$B,MATCH(MAX(¡Lista!$B:$B),Lista!$B:$B,0),1)

Dónde:

• ¡Lista!$B$7 es la primera celda del rango
• INDEX(List!$B:$B,MATCH(MAX(List!$B:$B),List!$B:$B,0),1) determina la última celda de la columna de la tabla dinámica al devolver una referencia de celda en columna B para el valor máximo de la tabla dinámica. (Suponiendo que la tabla dinámica esté ordenada en orden ascendente, el valor máximo de la tabla dinámica será el último valor de la tabla dinámica). Hemos explorado las funciones ÍNDICE y COINCIDIR numerosas veces antes; No dude en consultar las publicaciones de blog relacionadas que se enumeran a continuación para obtener información adicional.

La captura de pantalla del nombre se muestra a continuación como referencia.

Configuramos una fórmula similar para el nombre dd_dateto, de la siguiente manera.

=¡Lista!$C$7:ÍNDICE(¡Lista!$C:$C,MATCH(MAX(¡Lista!$C:$C),Lista!$C:$C,0),1)

Con los nombres dinámicos configurados, todo lo que queda es configurar las celdas desplegables con la función de validación de datos.

Validación de datos

Seleccionamos la celda de entrada Desde fecha y usamos Validación de datos (Datos Validación de datos) para permitir una Lista igual al nombre dd_datefrom, como se muestra a continuación.

Seleccionamos la celda de entrada Fecha de destino y configuramos la validación de datos para permitir una lista igual a dd_dateto.

Ahora, las listas desplegables de validación de datos contienen fechas basadas en las fechas de la tabla de transacciones como se muestra a continuación.

A medida que se agregan nuevas transacciones a la tabla, simplemente actualizamos la tabla dinámica intermedia para recuperar las opciones desplegables actualizadas. Los rangos dinámicos con nombre los incorporan y los introducidos en las celdas de entrada de validación de datos.

Conclusión

Hay muchas formas de realizar cualquier tarea determinada en Excel, por lo tanto, si tiene un método alternativo o preferido para cualquiera de los pasos presentados anteriormente, compártalo publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de muestra: FechasDeTo
• Publicaciones de índice
• Publicaciones de PARTIDO