El tutorial muestra cómo crear una lista desplegable de Excel basada en otra celda utilizando nuevas funciones de matriz dinámica.
Crear una lista desplegable simple en Excel es fácil. Hacer un menú desplegable en cascada de varios niveles siempre ha sido un desafío. El tutorial vinculado anteriormente describe cuatro enfoques diferentes, cada uno de los cuales incluye una cantidad increíble de pasos, un montón de fórmulas diferentes y un puñado de limitaciones relacionadas con entradas de varias palabras, celdas vacías, etc.
Esa era la mala noticia. La buena noticia es que esos métodos se diseñaron para versiones predinámicas de Excel. ¡La introducción de matrices dinámicas en Excel 365 cambió todo! Con las nuevas funciones de matriz dinámica, crear una lista desplegable multidependiente es cuestión de minutos, si no de segundos. Sin trucos, sin advertencias, sin tonterías. Solo soluciones rápidas, directas y fáciles de seguir.
Cómo hacer una lista desplegable dinámica en Excel
Este ejemplo demuestra el enfoque general para crear una lista desplegable en cascada en Excel mediante el uso de las nuevas funciones de matriz dinámica.
Supongamos que tiene una lista de frutas en la columna A y exportadores en la columna B. Una complicación adicional es que los nombres de las frutas no están agrupados sino dispersos en la columna. El objetivo es colocar los nombres de frutas únicos en el primer menú desplegable y, según la selección del usuario, mostrar los exportadores relevantes en el segundo menú desplegable.
Para crear una lista desplegable dependiente dinámica en Excel, realice estos pasos:
1. Obtener artículos para la lista desplegable principal
Para empezar, extraeremos todos los nombres de frutas diferentes de la columna A. Esto se puede hacer usando la función ÚNICA en su forma más simple: proporcione la lista de frutas para el primer argumento (matriz) y omita los argumentos opcionales restantes, ya que sus valores predeterminados funcionan bien para nosotros:
=UNIQUE(A3:A15)
La fórmula va a G3, y después de presionar el botón ingresar escriba los resultados automáticamente se desbordan en las siguientes celdas.
2. Crea el menú desplegable principal
Para crear la lista desplegable principal, configure una regla de validación de datos de Excel como esta:
- Seleccione una celda donde desea que aparezca el menú desplegable (D3 en nuestro caso).
- En datos pestaña, en herramientas de datos grupo, haga clic Fecha de validación.
- En el Fecha de validación cuadro de diálogo, haga lo siguiente:
- Bajo la PermitirSeleccionar Lista.
- En el Fuente cuadro, introduzca la referencia a la salida del intervalo de drenaje por la fórmula ÚNICA. Para hacer esto, escriba la etiqueta hash inmediatamente después de la referencia de la celda, así: =$G$3#
Esto se denomina referencia de rango de desbordamiento, y esta sintaxis se refiere a todo el rango, sin importar cuánto se expanda o se contraiga.
- Hacer clic Bien para cerrar el diálogo.
¡Tu lista desplegable principal está lista!
3. Obtenga elementos para el menú desplegable dependiente
Para obtener entradas para el menú desplegable secundario, filtraremos los valores en la columna B según el valor seleccionado en el primer menú desplegable. Esto se puede hacer usando otra función de matriz dinámica llamada FILTRO:
=FILTER(B3:B15, A3:A15=D3)
Donde B3:B15 son los datos de origen para el menú desplegable dependiente, A3:A15 son los datos de origen para el menú desplegable principal y D3 es la celda desplegable principal.
Para asegurarse de que la fórmula funciona correctamente, puede seleccionar un valor de la primera lista desplegable y observar los resultados devueltos por FILTRO. ¡Perfecto! 🙂
4. Haz que el menú desplegable sea dependiente
Para crear la segunda lista desplegable, configure los criterios de validación de datos exactamente como lo hizo para la primera lista desplegable en el paso 2. Pero esta vez, haga referencia al rango de drenaje devuelto por la función FILTRO: =$H$3 #
¡Esto es! Su lista desplegable dependiente de Excel está lista para usar.
Consejos y notas:
- Tener nuevas entradas incluidas en la lista desplegable automático, formatee los datos de origen como una tabla de Excel. O puede incluir algunas celdas en blanco en sus fórmulas, como se demuestra en este ejemplo.
- Si sus datos originales contienen lagunas, puede filtrar los espacios vacíos a través del uso esta solución.
- a ordenar alfabéticamente elementos de un menú desplegable, envuelva las fórmulas en la función ORDENAR como se explica en este ejemplo.
Cómo crear múltiples listas desplegables dependientes en Excel
En el ejemplo anterior, hicimos una lista desplegable basada en otra celda. Pero si necesita una jerarquía de varios niveles, es decir, un 3calle menú desplegable dependiendo de 2cuando lista, o incluso un 4el menú desplegable según 3calle lista. ¿Puede? Sí, puede configurar cualquier número de listas dependientes (un número razonable, por supuesto :).
Para este ejemplo, hemos colocado estados/provincias en la columna C, y ahora buscamos agregar un menú desplegable correspondiente en G3:
Para crear una lista desplegable de múltiples dependencias en Excel, esto es lo que debe hacer:
1. Configure el primer menú desplegable
La lista desplegable principal se crea con exactamente los mismos pasos que en el ejemplo anterior (consulte los pasos 1 y 2 anteriores). La única diferencia es la referencia del intervalo de drenaje que pones Fuente caja.
Esta vez, la fórmula ÚNICA está en E8 y el menú desplegable principal estará en E3. Así que seleccione E3, haga clic en Fecha de validacióny proporcione esta referencia: =$E$8#
2. Configurar el segundo menú desplegable
Como habrá notado, la columna B ahora contiene varias apariciones de los mismos exportadores. Pero solo quieres nombres únicos en tu menú desplegable, ¿verdad? Para excluir todas las ocurrencias duplicadas, incluya la función ÚNICA alrededor de la fórmula FILTRO e ingrese esta fórmula actualizada en F8:
=UNIQUE(FILTER(B3:B15, A3:A15=E3))
Donde B3:B15 son los datos de origen para el segundo menú desplegable, A3:A15 son los datos de origen para el primer menú desplegable y E3 es la primera celda desplegable.
Después de eso, use la siguiente referencia de rango de consumo para los criterios de validación de datos: =$F$8#
3. Configurar el tercer desplegable
Para recoger los artículos para 3calle lista desplegable, use la fórmula FILTRO con varios criterios. El primer criterio verifica toda la lista de frutas con el valor seleccionado en 1Calle menú desplegable (A3:A15=E3), mientras que el segundo criterio compara la lista de exportadores con la selección de los 2cuando menú desplegable (B3:B15=F3). La fórmula completa va a G8:
=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))
Si planea agregar varios menús desplegables dependientes (4el5el, etc.), lo más probable es que la columna C contenga varias apariciones del mismo elemento. Para evitar que entren duplicados en la tabla de preparación y, en consecuencia, en el tercer menú desplegable, coloque la fórmula FILTRO en la función ÚNICO, como hicimos en el paso anterior:
=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))
Lo último que debe hacer es crear otra regla de validación de datos con este Fuente referencia: =$G$8#
¡Tu lista desplegable multidependiente está lista!
Consejo. Del mismo modo, puede obtener artículos para menús desplegables posteriores. Suponiendo que la columna D contiene los datos de origen para 4el lista desplegable, puede ingresar la siguiente fórmula en H8 para recuperar los elementos correspondientes:
=UNIQUE(FILTER(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))
Cómo hacer una lista desplegable expandible en Excel
Después de crear un menú desplegable, su primera preocupación puede ser qué sucede cuando agrega nuevos elementos a los datos de origen. ¿La lista desplegable se actualizará automáticamente? Si sus datos originales tienen el formato de una tabla de Excel, entonces sí, una lista desplegable dinámica discutida en los ejemplos anteriores se expandirá automáticamente sin ningún esfuerzo de su parte, ya que las tablas de Excel son escalables por naturaleza.
Si, por alguna razón, usar una tabla de Excel no es una opción, puede hacer que la lista desplegable se extienda así:
- a incluye nuevos datos automáticamente a medida que se agrega a la lista de origen, agregue algunas celdas adicionales a las matrices a las que se hace referencia en sus fórmulas.
- a excluir celdas vacíasconfigurar fórmulas para ignorar las celdas vacías hasta que se llenen.
Con estos dos puntos en mente, ajustemos las fórmulas en nuestra tabla de preparación de datos. Las reglas de validación de datos no requieren ningún ajuste.
La fórmula para el menú desplegable principal
Con los nombres de las frutas en A3:A15, agregamos 5 celdas adicionales a la matriz para acomodar posibles entradas nuevas. Además, incorporamos la función FILTRO en ÚNICO para extraer valores únicos sin espacios en blanco.
Dado lo anterior, la fórmula en G3 toma esta forma:
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Fórmula para menú desplegable dependiente
La fórmula en G3 no necesita mucha modificación, simplemente expanda las matrices con más celdas:
=FILTER(B3:B20, A3:A20=D3)
El resultado es una lista desplegable dependiente completamente dinámica y extensible:
Cómo ordenar la lista desplegable alfabéticamente
¿Quiere organizar su lista desplegable alfabéticamente sin recurrir a los datos de origen? ¡El nuevo Dynamic Excel también tiene una función especial para eso! En la tabla de preparación de datos, simplemente incluya la función SORT alrededor de las fórmulas existentes.
Las reglas de validación de datos se configuran exactamente como se describe en los ejemplos anteriores.
Para ordenar de la A a la Z
Dado que el orden de clasificación ascendente es la opción predeterminada, simplemente puede anidar sus fórmulas existentes en matriz argumento SORT, omitiendo todos los demás argumentos que son opcionales.
Para menú desplegable principal (fórmula de G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Para menú desplegable dependiente (fórmula de H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
¡Terminado! Ambas listas desplegables están ordenadas alfabéticamente de la A a la Z.
Para ordenar de Z a A
Para ordenar en orden descendente, debe configurar 3calle argumento (Orden de clasificación) de la función ORDENAR a -1.
Para menú desplegable principal (fórmula de G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Para menú desplegable dependiente (fórmula de H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Esto ordenará tanto los datos en la tabla de preparación como los elementos en las listas desplegables de Z a A:
Consejo. Otra forma rápida y fácil de ingresar información en hojas de cálculo de Excel es un formulario de ingreso de datos.
Aquí se explica cómo crear una lista desplegable dinámica en Excel usando las nuevas funciones de matriz dinámica. A diferencia de los métodos tradicionales, este enfoque funciona perfectamente para entradas de una sola palabra y de varias palabras y se ocupa de las celdas vacías. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Libro de ejercicios para descargar.
Lista vertical dependiente de Excel (archivo .xlsx)
