Menú desplegable de validación de datos únicos a partir de datos de tabla duplicados

En esta publicación, exploraremos un método para generar un menú desplegable que contiene una lista única de opciones derivadas de una columna de la tabla con valores duplicados.

Objetivo

Antes de comenzar con la mecánica, echamos un vistazo a lo que intentamos lograr.

Contamos con una tabla de datos que se exporta desde nuestro sistema contable. Contiene el representante, la fecha de la transacción y el monto, como se muestra a continuación.

En otra hoja, queremos que el usuario pueda seleccionar un representante de un menú desplegable.

Queremos que la lista desplegable contenga una lista única de representantes de la tabla.

Los objetivos de nuestra solución son que son rápidos y fáciles de mantener a lo largo del tiempo, incluso cuando puedan aparecer nuevos representantes en la tabla de datos, que no usamos macros de VBA, que podemos mover el libro a otras ubicaciones de carpetas en períodos. futuros. , y que no necesitamos actualizar ninguna fórmula en períodos futuros.

¿Listo? Yo también… hagamos esto.

NOTA : Una alternativa a la técnica que se presenta a continuación es utilizar Power Query en lugar de una tabla dinámica como se analiza en esta publicación: https://www.excel-university.com/use-power-query-to-create -a-drop- lista-abajo-sin-duplicados/

técnica

Esta técnica implica crear una lista única de representantes en una hoja de trabajo con una tabla dinámica. La tabla dinámica es un paso intermedio que crea la lista de representantes que podemos usar para alimentar la lista desplegable de validación de datos. De cierto modo, el flujo de datos para esta técnica se puede visualizar de la siguiente manera:

• Tabla Tabla dinámica Referencia con nombre Validación de datos

Sigamos estos pasos uno a la vez.

Colina baja

Primero, almacenamos los datos de origen en una tabla. Esto se logra fácilmente seleccionando el rango normal y luego el icono Insertar Cinta de tabla.

Tabla dinámica

Con la tabla en su lugar, es hora de crear la lista única de opciones. Seleccionamos cualquier celda de la tabla y luego el icono Insertar Tabla dinámica. Creamos una tabla dinámica simple en una hoja nueva colocando el campo que contiene las opciones desplegables en el área Filas y eliminando los encabezados de campo y los totales generales, como se muestra a continuación.

Cada vez que cambie los datos de la tabla subyacente, como agregar nuevas filas en períodos futuros, deberá actualizar la lista de representantes haciendo clic en el botón Actualizar (Herramientas de tabla dinámica Actualizar, o haga clic con el botón derecho y Actualizar). Si desea que la lista se ordene a medida que se actualiza, puede actualizar el orden de clasificación de la tabla dinámica haciendo clic derecho en una celda de repetición y seleccionando la opción Ordenar deseada. Si elimina datos de la tabla y los representantes eliminados aún aparecen en la tabla dinámica, diríjase al cuadro de diálogo Opciones de tabla dinámica y cambie el Número de elementos a conservar por campo a Ninguno.

Ahora que hemos generado una lista única de representantes, es hora de colocarlos en un rango con nombre que pueda usarse con la función de validación de datos.

Referencia nombrada

Ahora, configuraremos un rango con nombre que la función de validación de datos puede utilizar para crear el menú desplegable. Hay muchas formas de crear un rango con nombre dinámico para hacer referencia a los resultados de la tabla dinámica y proporcionaré una de esas soluciones. Si tiene otros, compártalos publicando un comentario a continuación.

La referencia nombrada debe ajustarse dinámicamente a medida que cambie la cantidad de elementos de la tabla dinámica. Configuraremos un nuevo nombre, dd_reps, que utiliza la siguiente fórmula:

=¡Listas!$C$9:INDEX(¡Listas!$C:$C,MATCH("*",¡¡Listas!$C:$C,-1),1)

En resumen, esta fórmula crea una referencia de rango que comienza con la celda C9 en la hoja de cálculo Listas (la primera celda de la tabla dinámica) y continúa hasta la celda devuelta por la función ÍNDICE. La función ÍNDICE devuelve dinámicamente la última celda de datos de la columna C mediante la función COINCIDIR.

Para configurar el nuevo nombre, haga clic en el botón Nuevo nombre en el Administrador de nombres (Fórmulas Administrador de nombres) y luego ingrese la información del nombre como se muestra a continuación.

El nombre resultante, dd_reps, contiene una lista de los resultados de la tabla dinámica, incluso si el número de repeticiones cambia con el tiempo.

Validación de datos

Ahora estamos libres en casa. Todo lo que queda es configurar el cuadro desplegable con la función de validación de datos (Datos Validación de datos).

Simplemente permitimos una lista igual al nombre, dd_reps, como se muestra a continuación.

El menú desplegable resultante contiene una lista de opciones de la tabla dinámica, como se muestra a continuación.

Una vez configurado el libro de trabajo, a medida que se agregan nuevos datos a la tabla en cada período, solo necesita actualizar la tabla dinámica y los nuevos representantes fluirán a través de la tabla dinámica hacia el menú desplegable.

Dado que se trata de Excel, hay muchas formas de realizar cualquier tarea, por lo que el método presentado anteriormente es sólo una de esas soluciones. Si prefieres una solución diferente, nos encantaría escucharla… ¡publica un comentario a continuación y compártalo!

Recursos adicionales

• Archivo de muestra: UniqueDV
• Cómo hacer esto con Power Query en lugar de una tabla dinámica: https://www.excel-university.com/use-power-query-to-create-a-drop-down-list- without-duplicates/
• Publicaciones sobre Mesas
• Publicaciones sobre tablas dinámicas
• Publicaciones sobre validación de datos
• Publicaciones sobre referencias nombradas