Hacer una lista desplegable dependiente dinámica en Excel (paso a paso)
Aquí, en este tutorial, presentaremos paso a paso cómo crear una lista desplegable dependiente que muestre opciones según los valores seleccionados en la primera lista desplegable. En otras palabras, haremos una lista de validación de datos de Excel basada en el valor de otra lista.
Hacer una lista desplegable dependiente dinámica
10s para hacer una lista desplegable dependiente con una herramienta útil
Haga una lista desplegable dependiente dinámica en Excel 2021 o Excel 365
Algunas preguntas que puede hacer sobre este tutorial
Descarga gratis el archivo de muestra 
Video: Hacer una lista desplegable dependiente de Excel
Hacer una lista desplegable dependiente dinámica
Paso 1: escriba las entradas para las listas desplegables
1. En primer lugar, escriba las entradas que desea que aparezcan en las listas desplegables, cada lista en una columna por separado.
Aviso que los artículos en la primera columna (Producto) serán como nombres de Excel para las listas dependientes más adelante. Por ejemplo, aquí Frutas y Verduras serán los Nombres para las columnas B2:B5 y C2:C6 por separado.
Ver captura de pantalla:
2. Luego cree tablas para cada lista de datos.
Seleccione el rango de columna A1:A3, haga clic en recuadro > Mesa, luego en el cuadro de diálogo Crear tabla, marque Mi mesa tiene encabezados caja. Hacer clic OK.
Luego repita este paso para crear tablas para las otras dos listas.
Puede ver todas las tablas y la referencia a rangos en el Administrador de nombres (presione Ctrl + F3 Para abrirlo).
Paso 2: crear nombres de rango
En este paso, debe crear nombres para la lista principal y cada lista dependiente.
1. Seleccione los elementos que aparecen en la lista principal (A2: A3).
2. Luego vaya al Nombre de Caja que al lado Barra de formulas.
3. Escriba el nombre en él, aquí lo nombra como Producto.
4. Prensa Participar clave para completar.
Luego, repita los pasos anteriores para crear nombres por separado para cada lista dependiente.
Aquí nombra la segunda columna (B2:B5) como Fruta, y la tercera columna (C2:C6) como Verdura.
Puede ver todos los nombres de rango en el Administrador de nombres (presione Ctrl + F3 Para abrirlo).
Paso 3: agregue la lista desplegable principal
A continuación, agregue la lista desplegable principal (Producto), que es una lista desplegable normal de validación de datos, no una lista desplegable dependiente.
1. En primer lugar, cree una tabla.
Seleccione una celda (E1) y escriba el encabezado de la primera columna (Producto), y muévase a la siguiente celda de la columna (F1), escriba el encabezado de la segunda columna (Asunto). Agregará la lista desplegable a esta tabla.
Luego seleccione estos dos encabezados (E1 y F1), haga clic en recuadro pestaña y seleccione Mesa en el grupo Tablas.
En el cuadro de diálogo Crear tabla, marque Mi mesa tiene encabezados caja, y haga clic OK.
2. Seleccionar celda E2 en el que desea insertar la lista desplegable principal, haga clic en Datos pestaña e ir a Herramientas de datos grupo para hacer clic Validación de datos > Validación de datos.
3. En el cuadro de diálogo Validación de datos,
- Elige Lista existentes Permitir sección,
- Escriba debajo de la fórmula en Fuente barra, Producto es Nombre de la lista principal,
- Haga Clic en OK.
=Product
Puede ver que se ha creado la lista desplegable principal.
Paso 4: Agregar lista desplegable dependiente
1. Seleccionar celda F2 a la que desea agregar la lista desplegable dependiente, haga clic en Datos y vaya al grupo Herramientas de datos para hacer clic en Validación de datos > Validación de datos.
2. En el cuadro de diálogo Validación de datos,
- Elige Lista existentes Permitir sección,
- Escriba la siguiente fórmula en Fuente barra, E2 es la celda que contiene la lista desplegable principal.
- Haga Clic en OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Si el E2 está vacío (no selecciona ningún elemento en la lista desplegable principal), verá un mensaje emergente como el siguiente, haga clic en Sí para continuar.
Ahora se ha creado la lista desplegable dependiente.
Paso 5: pruebe la lista desplegable de dependientes.
1. Seleccionar Fruta en la lista desplegable principal (E2), luego vaya a la lista desplegable dependiente (F2) para hacer clic en el ícono de la flecha, ver si los artículos de frutas están en la lista, luego seleccione un artículo de la lista desplegable dependiente.
2. Prensa Tab audio para comenzar una nueva fila en la tabla de entrada de datos, seleccione Vegetaly muévase a la siguiente celda a la derecha, vea si los elementos vegetales están en la lista, luego seleccione un elemento de la lista desplegable dependiente.
- Si no hay ningún elemento seleccionado en la lista desplegable principal (columna Producto), la lista desplegable dependiente (columna Elemento) no funcionará.
- Si desea restablecer o borrar el contenido de la lista desplegable dependiente después de seleccionar cambiado, vaya a este artículo ¿Cómo borrar la celda de la lista desplegable dependiente después de seleccionar cambiado en Excel?, introduce un código VBA para ayudarte.
- Si desea crear una lista desplegable de 3 niveles, este artículo ¿Cómo crear una lista desplegable dependiente de varios niveles en Excel? Te ayudará.
10s para hacer una lista desplegable dependiente con una herramienta útil
Kutools for Excel proporciona una herramienta poderosa para hacer que una lista desplegable dependiente sea más fácil y rápida, veamos:
Antes de seguir los pasos a continuación, por favor haga clic para descargar Kutools for Excel para una prueba gratuita de 30 días en primer lugar.
Paso 1: escriba las entradas para la lista desplegable
En primer lugar, organice sus datos como se muestra a continuación:
Paso 2: Aplicación de la herramienta Kutools
1. Seleccione los datos que ha creado, haga clic en Kutools pestaña y haga clic La lista desplegable para mostrar el submenú, haga clic en Lista desplegable dinámica.
2. En la lista desplegable de dependientes
- Asegúrate de leer Modo B que coincida con su modo de datos,
- Seleccione rango de salida, la columna del rango de salida debe ser igual a la columna del rango de datos,
- Haga Clic en Ok.
Ahora se ha creado la lista desplegable dependiente.
- El modo B admite la creación de una tercera o más listas desplegables de niveles:
- Si sus datos están organizados como se muestra en la siguiente captura de pantalla, debe usar el Modo A, el Modo A solo admite la creación de una lista desplegable dependiente de 2 niveles.
- Más detalles sobre cómo usar Kutools para crear una lista desplegable dependiente, visite este tutorial .
Haga una lista desplegable dependiente dinámica en Excel 2021 o Excel 365
Si está en Excel 2021 o Excel 365, hay otra forma de crear rápidamente una lista desplegable dependiente dinámica mediante el uso de nuevas funciones UNIQUE y FILTRO.
Suponiendo que sus datos de origen estén organizados como se muestra en la captura de pantalla, siga los pasos a continuación para crear la lista desplegable dinámica.
Paso 1: usar la fórmula para obtener elementos para la lista desplegable principal
Seleccione una celda, por ejemplo, la celda G3, y use las funciones ÚNICO y FILTRO para extraer los valores únicos del Producto lista que será la fuente de la lista desplegable principal, y presione Participar clave.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Paso 2: crea la lista desplegable principal
1. Seleccione una celda en la que desee colocar la lista desplegable principal, por ejemplo, celda D3, haga clic Datos pestaña y vaya a Herramientas de datos grupo para hacer clic Validación de datos > Validación de datos.
2. En el cuadro de diálogo Validación de datos,
- Elige Lista existentes Permitir sección,
- Escriba debajo de la fórmula en Fuente bar,
- Haga Clic en OK.
=$G$3#
Ahora se crea la lista desplegable principal.
Paso 3: uso de la fórmula para obtener elementos para la lista desplegable dependiente
Seleccione una celda, por ejemplo, la celda H3, utilizando la función FILTRO para filtrar los elementos según el valor de la celda D3 (el elemento seleccionado en la lista desplegable principal), presione Participar clave.
=FILTER(B3:B20, A3:A20=D3)
Paso 4: Crear la lista desplegable de dependientes
1. Seleccione una celda que colocará la lista desplegable dependiente, por ejemplo, celda E3, haga clic Datos pestaña y vaya a Herramientas de datos grupo para hacer clic Validación de datos > Validación de datos.
2. En el cuadro de diálogo Validación de datos,
- Elige Lista existentes Permitir sección,
- Escriba debajo de la fórmula en Fuente bar,
- Haga Clic en OK.
=$H$3#
Ahora la lista desplegable dependiente se crea correctamente.
Cuando agrega nuevos elementos o realiza algunos cambios en A3: A20, la lista desplegable se actualizará automáticamente.
Ordenar la lista desplegable alfabéticamente
Si desea organizar alfabéticamente los elementos de la lista desplegable, puede utilizar la siguiente fórmula para la tabla de preparación.Para el menú desplegable principal (la fórmula en la celda G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Para el menú desplegable dependiente (la fórmula en la celda H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Ahora ambas listas desplegables se ordenan alfabéticamente de la A a la Z.
Para ordenarlos alfabéticamente de la Z a la A, utilice la siguiente fórmula:
Para el menú desplegable principal (la fórmula en la celda G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Para el menú desplegable dependiente (la fórmula en la celda H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Algunas preguntas que puede hacer:
1. ¿Por qué insertar una tabla para cada lista de datos?
Insertar una tabla para la lista de datos lo ayudará a actualizar automáticamente la lista desplegable en función de los cambios en la lista de datos. Por ejemplo, al agregar 'Otros' en la primera lista de datos, la lista desplegable principal se agregará automáticamente con 'Otros'.
2. ¿Por qué usar una tabla para colocar listas desplegables?
Cuando presiona la tecla Tab para agregar una nueva línea a la tabla, las listas desplegables también se agregarán automáticamente en la nueva línea.
3. ¿Cómo funciona la función INDIRECTA?
INDIRECT La función se utiliza para convertir una cadena de texto en una referencia válida.
4. ¿Cómo funciona la fórmula INDIRECTO(SUSTITUTO(E2&F2," ",""))?
En primer lugar, SUSTITUIR función reemplaza el texto con otro texto. Aquí solía quitar los espacios de los nombres combinados (E2 y F2). Después INDIRECT La función convierte la cadena de texto (los contenidos combinados por E2 y F2) en una referencia válida.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
Tabla de contenidos.
- Video: Hacer una lista desplegable dependiente de Excel
- Hacer una lista desplegable dependiente dinámica
- 10s para hacer una lista desplegable dependiente
- Haga una lista desplegable dependiente dinámica en Excel 2021/365
- Preguntas Frecuentes
- Artículos relacionados
- Las mejores herramientas de productividad de oficina
- Comentarios