El tutorial muestra cómo crear una lista desplegable de Excel dependiendo de otra celda mediante el uso de 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 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 se diseñaron para versiones predinámicas de Excel. ¡La introducción de matrices dinámicas en Excel 365 ha cambiado todo! Con las nuevas funciones de matrices dinámicas, crear una lista desplegable dependiente múltiple 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, siga estos pasos:
1. Obtener 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 Ú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 solo bien para nosotros:
=UNIQUE(A3:A15)
La fórmula va a G3 y, después de presionar la tecla Intro, los resultados se transfieren automáticamente a las siguientes celdas.
2. Crea el menú desplegable principal
Para hacer 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).
- En la pestaña Datos, en el grupo Herramientas de datos, haga clic en Validación de datos.
- En el cuadro de diálogo Validación de datos, haga lo siguiente:
- En Permitir, seleccione Lista.
- En el cuadro Fuente, ingrese la referencia a la salida del rango de derrame por 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 se contraiga.
- Haga clic en Aceptar para cerrar el cuadro de diálogo.
¡Tu lista desplegable principal está lista!
3. Obtener 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 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. Haz el menú desplegable dependiente
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.
Consejos y notas:
- tener el nuevas entradas incluidas en la lista desplegable automáticamente, formatee sus datos de origen como una tabla de Excel. O puede incluir algunas celdas en blanco en sus fórmulas como se muestra en este ejemplo.
- Si sus datos originales contienen lagunas, puede filtrar espacios en blanco mediante el uso esta solución.
- A ordenar alfabéticamente elementos de un menú desplegable, envuelva sus fórmulas en la función ORDENAR como se explica en este ejemplo.
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é sucede si necesita una jerarquía de varios niveles, es decir, un tercer menú desplegable según la segunda lista, o incluso un cuarto menú desplegable según la tercera lista? ¿Es eso posible? 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 hacer 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 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 dispersión que ingresa en el cuadro Fuente.
Esta vez, la fórmula ÚNICA está en E8, y la lista desplegable principal estará en E3. Por lo tanto, selecciona E3, hace clic en Validación de datos y proporciona esta referencia: =$E$8#
2. Configure el segundo menú desplegable
Como habrá notado, ahora la columna B contiene varias apariciones de los mismos exportadores. Pero solo desea nombres únicos en su lista desplegable, ¿verdad? Para omitir todas las ocurrencias duplicadas, ajuste la función ÚNICA alrededor de su fórmula de 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 dispersión para los criterios de validación de datos: =$F$8#
3. Configure el tercer menú desplegable
Para reunir los elementos de la tercera lista desplegable, utilice la fórmula FILTRO con varios criterios. El primer criterio compara toda la lista de frutas con el valor seleccionado en el primer menú desplegable (A3:A15=E3), mientras que el segundo criterio compara la lista de exportadores con la selección del segundo menú desplegable (B3:B15=F3). La fórmula completa va a G8:
=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))
Si va a agregar más menús desplegables dependientes (4º, 5º, etc.), lo más probable es que la columna C contenga varias apariciones del mismo elemento. Para evitar que los duplicados entren 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 esta referencia de fuente: =$G$8#
¡Su lista desplegable de múltiples dependientes está lista!
Consejo. De manera similar, puede obtener artículos para desplegables posteriores. Suponiendo que la columna D contiene los datos de origen para su cuarta 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 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, puede expandir su lista desplegable de esta manera:
- A incluir 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 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 las frutas en A3:A15, agregamos 5 celdas adicionales a la matriz para atender posibles nuevas entradas. 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 el menú desplegable dependiente
La fórmula en G3 no necesita muchos ajustes, solo extienda 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 SORT 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 argumento de matriz 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 establecer el tercer argumento (sort_order) de la función SORT en -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 en la tabla de preparación como los elementos en 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 leernos y esperamos verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)