Crear menús desplegables dependientes con rangos de derrames
Hace un tiempo, escribí sobre la creación de menús desplegables dependientes (también conocidos como en cascada, dinámicos o condicionales) mediante la validación de datos. Aquí es donde tiene un menú desplegable principal, y las opciones en el menú desplegable secundario relacionado dependen de la selección realizada en el primer menú desplegable. Bueno, este proceso se volvió MUCHO más fácil con la introducción de funciones de matriz dinámica y rangos de derrame. Esta es la primera publicación de una serie que explora las funciones de matriz dinámica y los rangos de derrames… abróchate el cinturón porque será un viaje divertido
Objetivo
Nuestro objetivo es crear dos (o más) menús desplegables, donde las opciones disponibles dependen de la selección realizada en los menús desplegables anteriores.
Por ejemplo, queremos permitir que el usuario seleccione una región de un menú desplegable, como se muestra a continuación.
Luego, según la región seleccionada, el menú desplegable de representantes debería mostrar solo los representantes correspondientes:
Si se selecciona una región diferente, las opciones en el menú desplegable deben reflejar los representantes de esa región:
Podemos configurar esto usando funciones de matriz dinámica y rangos de derrame.
Nota: dependiendo de su versión de Excel, es posible que no tenga las funciones de matriz dinámica como se muestra a continuación. Al momento de escribir este artículo, está disponible solo mediante suscripción a O365 y solo si su canal de actualización está configurado en Insiders Fast. Estas actualizaciones se lanzarán a los usuarios de O365 con el tiempo. Estas capacidades no se distribuirán a los usuarios de licencias perpetuas de versiones anteriores de Excel (como Excel 2019, 2016, etc.). Si desea acceder al canal de actualización Insiders Fast, consulte las opciones de Actualización en Excel. Si no tiene acceso a estas funciones pero aún desea crear menús desplegables dinámicos, consulte esta publicación que utiliza funciones que han estado disponibles durante los últimos 10 años.
Video
Narrativo
Exploraremos las funciones de matriz dinámica y los rangos de derrame, y cómo configurarlos para crear menús desplegables dependientes, siguiendo los pasos a continuación:
- Crear tabla de opciones de selección
- Crear listas de opciones dinámicas
- Crear menús desplegables
¡Hagamos esto!
Crear tabla de opciones de selección
El primer paso es relativamente fácil… simplemente crea una tabla que almacena todas las opciones. La primera columna contendrá las opciones para el primer menú desplegable, la segunda columna para el segundo menú desplegable, y así sucesivamente. Algo como esto:
Asegúrese de que esto esté guardado en una tabla (Insertar Tabla) y anote el nombre de la tabla en la pestaña Herramientas de tabla, o mejor aún, asigne a la tabla un nombre descriptivo usando el campo Herramientas de tabla Nombre de tabla . Llamé a mi mesa Choices .
Nota: Puede crear columnas adicionales para menús desplegables adicionales si es necesario.
Crear listas de opciones dinámicas
Aquí es donde el proceso se vuelve divertido y utiliza matrices dinámicas y rangos de derrame. Estos términos son relativamente nuevos para los usuarios de Excel mientras escribo esta publicación, así que comencemos con una breve descripción general.
Funciones de matriz dinámica
Generalmente estamos acostumbrados a escribir fórmulas en Excel que devuelven un único valor. Quizás una suma, promedio, mínimo, máximo o recuento de algún rango de números. Pero Microsoft renovó recientemente el motor de cálculo de Excel e introdujo varias funciones nuevas que devuelven uno o más valores. ¿Esperar lo?
Sí… estas “funciones de matriz dinámica” recientemente introducidas devuelven una matriz dinámica. Una matriz dinámica… ¿qué? Para simplificar, piense en una matriz como una forma de almacenar múltiples valores. Dinámico simplemente significa que la cantidad de valores devueltos puede cambiar a medida que cambian los argumentos.
Por ejemplo, la función SUMA devuelve un valor único… la hemos estado usando durante décadas. Pero ahora tenemos un puñado de funciones nuevas que pueden devolver múltiples valores, tal vez una lista como 1, 2, 3, 4 y 5. Pero… Jeff… tiempo de espera. Si una función devuelve múltiples valores, ¿cómo se pueden mostrar en una sola celda? Ah… pregunta reveladora, que nos lleva a nuestro siguiente tema, los rangos de derrame.
Rangos de derrames
Si una fórmula devuelve varios valores, se “desbordan” en las celdas debajo de la fórmula. Y ese rango de derrame es dinámico. Entonces, la próxima vez que Excel calcule la fórmula, si se devuelven más valores, el rango de derrame aumenta para incluir los valores.
Si desea obtener más información sobre las funciones de matrices dinámicas y los rangos de derrame, consulte el libro Excel Dynamic Arrays Straight to the Point de Bill Jelen , que se puede descargar de forma gratuita durante un breve periodo de tiempo. Cubre funciones como ORDENAR, ORDENAR, FILTRAR, ÚNICO, SECUENCIA y mucho más. Más información aquí:
- https://www.mrexcel.com/products/excel-dynamic-arrays-straight-to-the-point/
Además, aquí hay un excelente video para verdaderos expertos en Excel que desean comprender mejor el motor de cálculo y las matrices:
- https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2140
Solicitud
Ahora que tenemos un poco de información sobre las funciones de matriz dinámica y los rangos de derrame, apliquémoslas a la tarea que nos ocupa.
Nuestro objetivo en este paso es crear un rango de derrames para cada menú desplegable. Es decir, crear dos listas que contendrán las opciones que se mostrarán en los dos menús desplegables.
Para crear la lista de opciones para el menú desplegable Región principal, necesitamos una lista única de los valores en la columna Región de la tabla de opciones.
Esto se puede lograr con la función UNIQUE, de la siguiente manera:
=ÚNICO(Opciones[Región])
La función ÚNICA devuelve una matriz dinámica de los valores únicos en la columna Región de la tabla. Dado que la fórmula devuelve más de un valor, los resultados se extienden a las celdas debajo de la fórmula, como se muestra a continuación.
Usaremos otra función de matriz dinámica para crear la lista de representantes, pero primero, creemos el menú desplegable Región.
Usaremos la validación de datos para crear los menús desplegables. Comencemos con el menú desplegable Región.
Menú desplegable de región
Seleccionamos la celda desplegable deseada y usamos el comando Datos Validación de datos . Esto abre el cuadro de diálogo Validación de datos, donde optamos por Permitir una lista y luego, en el campo Fuente, seleccionamos la celda que contiene nuestra fórmula de matriz dinámica. En este punto, el campo Fuente contendrá una referencia a una sola celda, como esta:
=$B$13
Pero eso no es lo que realmente queremos. No queremos que nuestro menú desplegable de validación de datos contenga un solo valor de celda… queremos que incluya todos los valores en el rango de derrame. Entonces, para hacer eso agregamos un # al final de la referencia. El signo almohadilla/almohadilla se utiliza para crear una referencia de derrame, como esta:
=$B$13#
Esto se muestra en el cuadro de diálogo siguiente.
Después de hacer clic en Aceptar, vemos que nuestro menú desplegable de Región muestra una lista de opciones de Región… ¡excelente!
Hasta aquí todo bien… ahora tenemos que abordar el menú desplegable Representante.
Lista de elección de representantes
Necesitamos crear la lista de opciones para el menú desplegable de Representantes secundario.
Para hacer esto, usaremos otra función de matriz dinámica. Si pensamos en esto por un momento, nos daremos cuenta de que lo que necesitamos es una lista de representantes para la región seleccionada. Si estuviéramos mirando la tabla de Opciones, ¿cómo lograríamos esto? Por ejemplo, mire la siguiente tabla y piense en una forma de obtener una lista de representantes de la Región Este:
Podríamos aplicar un filtro… es decir, usar el control de filtro Región y seleccionar Este. Los resultados se muestran a continuación.
Bueno, eso es EXACTAMENTE lo que hace la siguiente función de matriz dinámica, y se llama apropiadamente FILTRO.
La función FILTRO aplica un filtro a un rango y devuelve los resultados filtrados.
En nuestro caso, queremos los valores en la columna Rep donde la Región es igual a Este (o la región actualmente seleccionada). Suponiendo que nuestra selección de Región (por ejemplo, Este) está almacenada en C6 , la fórmula es:
=FILTRO(Opciones[Representante],Opciones[Región]=C6,"Ninguno")
Notarás que el argumento final “Ninguno” nos permite proporcionar un valor en caso de que la función no devuelva ningún valor.
Pulsamos Enter y Excel muestra los representantes en la región seleccionada. A medida que cambia la región, también cambia la lista de representantes. Ahora, solo necesitamos colocar ese rango de derrame en un menú desplegable de validación de datos.
Menú desplegable de representantes
Con el rango de derrame de repeticiones creado, simplemente agregamos el menú desplegable secundario. Seleccionamos la celda deseada, y Datos Validación de datos . Una vez más permitimos una Lista y la Fuente es la celda de fórmula de matriz dinámica seguida de #, como se muestra a continuación.
Una vez completado esto, ahora tenemos un menú desplegable de Región principal, y las opciones desplegables de Representantes secundarios dependen de la región seleccionada. ¡Hurra!
Si tiene otros consejos relacionados sobre menús desplegables dependientes o funciones de matriz dinámica, compártalos publicando un comentario a continuación… ¡gracias!
Si necesita tener más de dos menús desplegables, no hay problema… el archivo de muestra tiene una hoja con tres menús desplegables.
Descargue el archivo de muestra: DependentDropDowns.xlsx
Deja una respuesta