Cree una lista desplegable dependiente dinámica en Excel de una manera fácil

El tutorial muestra cómo crear una lista desplegable de Excel dependiendo de otra celda utilizando nuevas funciones de matriz dinámica.

Crear una lista desplegable sencilla en Excel es fácil. Crear 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 gran cantidad de pasos, un montón de fórmulas diferentes y un puñado de limitaciones relacionadas con entradas de varias palabras, celdas en blanco, etc.

Esa era la mala noticia. La buena noticia es que esos métodos fueron diseñados para versiones predinámicas de Excel. ¡La introducción de matrices dinámicas en Excel 365 lo ha cambiado todo! Con las nuevas funciones de matriz dinámica, crear una lista desplegable de múltiples dependencias es cuestión de minutos, si no de segundos. Sin trucos, sin advertencias, sin tonterías. Sólo soluciones rápidas, sencillas 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 de 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 únicos de las frutas 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, siga estos pasos:

1. Obtenga elementos 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 UNIQUE en su forma más simple: proporcione la lista de frutas para el primer argumento (formación) y omitir 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 clave, los resultados pasan a las siguientes celdas automáticamente.

>2. Crea el menú desplegable principal.

Para crear su lista desplegable principal, configure una regla de Validación de datos de Excel de esta manera:

• Seleccione una celda en la que desea que aparezca el menú desplegable (D3 en nuestro caso).
• Sobre el Datos pestaña, en la Herramientas de datos grupo, haga clic Validación de datos.
• En el Validación de datos cuadro de diálogo, haga lo siguiente:
  • Bajo Permitirseleccionar Lista.
  • En el Fuente , ingrese la referencia a la salida del rango de derrame mediante la fórmula ÚNICA. Para esto, escriba la etiqueta hash justo después de la referencia de la celda, así: =$G$3#

    Esto se denomina referencia de rango de derrame y esta sintaxis se refiere a todo el rango independientemente de cuánto se expanda o contraiga.

  • Hacer clic DE ACUERDO para cerrar el diálogo.

  >

¡Su lista desplegable principal está lista!

>3. Obtenga elementos para la lista desplegable de dependientes

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 con la ayuda de otra función de matriz dinámica llamada FILTRO:

=FILTER(B3:B15, A3:A15=D3)

Donde B3:B15 son los datos de origen para su menú desplegable dependiente, A3:A15 son los datos de origen para su menú desplegable principal y D3 es la celda del menú desplegable principal.

Para asegurarse de que la fórmula funcione correctamente, puede seleccionar algún valor en la primera lista desplegable y observar los resultados devueltos por FILTRO. ¡Perfecto! 🙂

>4.Haga que el dependiente sea desplegable

Para crear la segunda lista desplegable, configure los criterios de validación de datos exactamente como lo hizo para el primer menú desplegable en el paso 2. Pero esta vez, haga referencia al rango de derrame devuelto por la función FILTRO: =$H$3#
>

¡Eso es todo! Su lista desplegable dependiente de Excel está lista para usar.
>

Cómo crear una lista desplegable de múltiples dependientes en Excel

En el ejemplo anterior, hicimos una lista desplegable dependiendo de otra celda. Pero, ¿qué pasa si necesita una jerarquía de varios niveles, es decir, 3?tercero desplegable dependiendo de los 2Dakota del Norte lista, o incluso un 4th desplegable dependiendo de los 3tercero lista. ¿Es eso posible? Sí, puedes configurar cualquier cantidad de listas de dependientes (una cantidad razonable, por supuesto :).

Para este ejemplo, hemos colocado estados/provincias en la columna C y ahora buscamos agregar el menú desplegable correspondiente en G3:
>

Para crear una lista desplegable de múltiples dependientes en Excel, esto es lo que debe hacer:

1. Configure el primer menú desplegable

La lista desplegable principal se crea exactamente con los mismos pasos que en el ejemplo anterior (consulte los pasos 1 y 2 anteriores). La única diferencia es la referencia del rango de derrame que ingresa en el Fuente caja.

Esta vez, la fórmula ÚNICA está en E8 y la lista desplegable principal estará en E3. Entonces, seleccionas E3, haces clic Validación de datosy proporcione esta referencia: =$E$8#

>2. Configura el segundo menú desplegable

Como habrás notado, ahora la columna B contiene múltiples apariciones de los mismos exportadores. Pero solo quieres nombres únicos en tu lista desplegable, ¿verdad? Para omitir todas las apariciones duplicadas, ajuste la función ÚNICA alrededor de su 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 del menú desplegable.

Después de eso, utilice la siguiente referencia de rango de derrame para los criterios de validación de datos: =$F$8#

>3. Configura el tercer menú desplegable

Para reunir los artículos para los 3.tercero lista desplegable, utilice la fórmula FILTRO con múltiples criterios. El primer criterio compara toda la lista de frutas con el valor seleccionado en el 1calle menú desplegable (A3:A15=E3), mientras que el segundo criterio compara la lista de exportadores con la selección en los 2Dakota del Norte menú desplegable (B3:B15=F3). La fórmula completa va al G8:

=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))

Si va a agregar más menús desplegables dependientes (4th5th, 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, anide la fórmula FILTRO en la función ÚNICA como hicimos en el paso anterior:

=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

Lo último que debe hacer es crear una regla de validación de datos más con esto Fuente referencia: =$G$8#
>

¡Su lista desplegable de múltiples dependientes está lista para comenzar!
>

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 están formateados como tabla de Excel, entonces sí, una lista desplegable dinámica analizada en los ejemplos anteriores se expandirá automáticamente sin ningún esfuerzo de su parte porque las tablas de Excel son expandibles por su naturaleza.

Si por alguna razón usar una tabla de Excel no es una opción, puedes hacer que tu lista desplegable sea expandible de esta manera:

• A incluir nuevos datos automáticamente a medida que se agrega a la lista de fuentes, agregue algunas celdas adicionales a las matrices a las que se hace referencia en sus fórmulas.
• A excluir celdas en blancoconfigure las fórmulas para ignorar las celdas vacías hasta que se llenen.

Teniendo 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.

Fórmula para el menú desplegable principal

Con los nombres de frutas en A3:A15, agregamos 5 celdas adicionales a la matriz para atender posibles nuevas entradas. Además, incorporamos la función FILTRO en UNIQUE para extraer valores únicos sin espacios en blanco.

Teniendo en cuenta lo anterior, la fórmula en G3 toma esta forma:

=UNIQUE(FILTER(A3:A20, A3:A20<>»»))

Fórmula para el menú desplegable dependiente

La fórmula en G3 no necesita muchos ajustes: simplemente amplíe las matrices con algunas celdas más:

=FILTER(B3:B20, A3:A20=D3)

El resultado es una lista desplegable dependiente expandible completamente dinámica:

>Cómo ordenar la lista desplegable alfabéticamente

¿Quiere organizar su lista desplegable alfabéticamente sin recurrir a los datos de origen? ¡El nuevo Excel dinámico también tiene una función especial para esto! En su tabla de preparación de datos, simplemente ajuste la función ORDENAR alrededor de sus 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, puede simplemente anidar sus fórmulas existentes en el formación argumento de SORT, omitiendo todos los demás argumentos que son opcionales.

Para el menú desplegable principal (la fórmula en G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>»»)))

Para el menú desplegable dependiente (la fórmula en H3):

=SORT(FILTER(B3:B20, A3:A20=D3))

¡Hecho! Ambas listas desplegables se ordenan alfabéticamente de la A a la Z.

>Para ordenar de Z a A

Para ordenar en orden descendente, debe configurar el 3tercero argumento (Orden de clasificación) de la función ORDENAR a -1.

Para el menú desplegable principal (la fórmula en G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>»»)), 1, -1)

Para el menú desplegable dependiente (la fórmula en H3):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

Esto ordenará tanto los datos de la tabla de preparación como los elementos de las listas desplegables de Z a A:
>

Así es como se crea una lista desplegable dinámica en Excel con la ayuda de las nuevas funciones de matriz dinámica. A diferencia de los métodos tradicionales, este enfoque funciona perfectamente para entradas de una o varias palabras y se ocupa de las celdas en blanco. ¡Gracias por leer y esperamos verte en nuestro blog la próxima semana!

Libro de práctica para descargar.

Lista desplegable dependiente de Excel (archivo .xlsx)

Usted también podría estar interesado en