Menús desplegables dependientes con FILTRO

Esta publicación muestra cómo crear múltiples menús desplegables dependientes usando la función FILTRO. También se conocen como menús desplegables en cascada o condicionales, donde las opciones en un menú desplegable dependen de la selección realizada en un menú desplegable anterior. La técnica presentada le permite crear tantos menús desplegables como necesite y no se necesita codificación VBA.

Nota: dependiendo de su versión de Excel, es posible que no tenga acceso a las funciones FILTRAR, ÚNICO u ORDENAR. De lo contrario, consulte esta publicación que usa funciones heredadas o esta publicación que usa segmentaciones.

Objetivo

Antes de entrar en la mecánica, aclaremos nuestro objetivo. Nos gustaría crear una serie de menús desplegables dependientes, donde las opciones disponibles en un menú desplegable dependen de la selección realizada en un menú desplegable anterior.

Por ejemplo, nos gustaría que el usuario elija un tipo de camiseta. Dependiendo del Tipo de camisa seleccionado, el usuario verá varias opciones de Estilo. Dependiendo de la opción de Estilo seleccionada, se muestran las opciones de Tamaño correspondientes, y así sucesivamente. Quizás algo como esto:

Todas estas opciones de configuración provienen de una única tabla, que almacena todas las combinaciones válidas. Quizás algo como esto (Tabla 1):

Para crear esto, usaremos algunas funciones (ORDENAR, ÚNICO, FILTRAR) junto con la validación de datos.

Video

Narrativo

Construiremos esto siguiendo estos pasos:

• Crear una lista desplegable
• Repita para cada menú desplegable
• Recuperar información relacionada

Hagámoslo.

Crear una lista desplegable

Realizaremos tres pasos para cada menú desplegable:

• Crea la lista de opciones
• Crear el menú desplegable en la celda
• Has una elección

En última instancia, repetiremos estos pasos para cada menú desplegable posterior.

Crea la lista de opciones

Para crear la lista de opciones, escribiremos una fórmula en un área no utilizada de la hoja de trabajo.

Comencemos con el primer menú desplegable, que en nuestro caso es Tipo.

El primer menú desplegable no tiene dependencias y, por lo tanto, incluirá la misma lista de opciones todo el tiempo. Dado que la tabla de productos se llama Tabla1 y el primer menú desplegable debe proporcionar una lista única de todos los elementos que se encuentran en la columna Tipo, usaremos la siguiente fórmula:

=ÚNICO(Tabla1[Tipo])

Escribimos la fórmula en la celda B17 y pulsamos Enter. Vemos que la fórmula arroja múltiples resultados:

Si queremos que la lista esté ordenada, podemos incluir la función ORDENAR en nuestra fórmula de esta manera:

=ORDENAR(ÚNICO(Tabla1[Tipo]))

Ahora nuestra lista de opciones está ordenada, así:

Con nuestra lista de opciones lista, solo necesitamos colocarlas en un menú desplegable dentro de la celda. Para esto, usaremos la validación de datos.

Crear el menú desplegable en la celda

Seleccionamos la celda de entrada y usamos Datos Validación de datos . Permitimos una lista y configuramos la fuente en =$B$17# así:

Nota: el # le dice a Excel que incluya todos los resultados devueltos por la fórmula.

La celda de entrada ahora contiene un menú desplegable con la lista de opciones.

Has una elección

Aunque técnicamente no necesitamos hacer una selección del menú desplegable en este momento, el siguiente paso será más fácil si lo hacemos. Entonces, seleccionamos la celda de entrada y elegimos cualquier opción para que la celda no esté vacía, así:

Nota: la razón por la que hacemos una selección es para evitar errores de fórmula que podrían llevarnos a creer que nuestra fórmula es incorrecta.

Repita para cada menú desplegable

Luego, realizamos estos pasos nuevamente para cada menú desplegable posterior. Necesitamos escribir una fórmula para crear la lista de opciones, usar la validación de datos para crear el menú desplegable y luego hacer una selección para que la celda no esté vacía.

Pero hay un problema. Cada lista posterior de opciones depende de la elección realizada en la celda de entrada anterior. Para dar cabida a esta dependencia, usaremos la función FILTRO.

Estilo desplegable

La siguiente lista de opciones es para Estilo. Las opciones de Estilo dependen de la selección en la celda de entrada Tipo. Entonces, para crear nuestra lista de opciones de Estilo, usamos esta fórmula:

=ORDENAR(ÚNICO(FILTRO(Tabla1[Estilo],Tabla1[Tipo]=C6)))

Básicamente, esto le dice a Excel que devuelva una lista ordenada de valores de estilo únicos donde la columna Tipo es igual al Tipo seleccionado en C6. Escribimos la fórmula en C17 y pulsamos Enter:

Para obtener esa lista de opciones en la celda de entrada Estilo, usamos una vez más la Validación de datos. Esta vez, la fuente es =$C$17#. Expandimos el menú desplegable Estilo resultante y hacemos una selección para que la celda no quede en blanco:

La siguiente celda de entrada es para Tamaño.

Tamaño desplegable

Para Tamaño, nuestra lista de opciones depende de los valores seleccionados para Tipo y Estilo.

Entonces, nuestra función FILTRO mostrará la lista de Tamaños donde coinciden los valores de Tipo y Estilo. Para usar la lógica AND cuando tenemos múltiples condiciones, usamos un operador de multiplicación * como este:

=ORDENAR(ÚNICO(  FILTRO(Tabla1[Tamaño],(Tabla1[Estilo]=C7)*(Tabla1[Tipo]=C6))))

Nota: para usar la lógica OR en lugar de la lógica AND, usaríamos el operador de suma + en lugar del operador de multiplicación.

Escribimos la fórmula en la celda D17 y pulsamos Enter:

Para crear el menú desplegable, usamos Validación de datos y configuramos la fuente =$D$17#. Ampliamos el menú desplegable resultante y hacemos una selección, como esta:

Hasta ahora todo bien… sólo nos queda un último menú desplegable.

Menú desplegable de colores

El último menú desplegable de esta ilustración es el color, pero puedes continuar usando esta misma técnica para tantos menús desplegables como sea necesario.

Nuestra fórmula para crear las opciones incluye condiciones de Tipo, Estilo y Tamaño:

=ORDENAR(ÚNICO(  FILTRO(Tabla1[Color],    (Tabla1[Tamaño]=C8)*(Tabla1[Estilo]=C7)*(Tabla1[Tipo]=C6)    )))

Escribimos la fórmula en E17 y pulsamos Enter:

Usamos la validación de datos para crear el menú desplegable y configuramos la fuente =$E$17#. Hacemos una selección:

Ahora que hemos creado los menús desplegables dependientes, opcionalmente podemos devolver valores relacionados.

Recuperar información relacionada

En nuestro caso, una vez que el usuario haya seleccionado el Tipo, Estilo, Tamaño y Color, nos gustaría que Excel devuelva el Precio relacionado.

Una vez más, pasaremos a la función FILTRO. Escribimos la siguiente fórmula en C10:

=FILTRO(Tabla1[Precio],  (Tabla1[Color]=C9)*(Tabla1[Tamaño]=C8)*(Tabla1[Estilo]=C7)*(Tabla1[Tipo]=C6))

Le damos Enter y bam:

Nota: si la tabla de productos contenía varias filas coincidentes, podemos agregar los resultados con una función auxiliar como SUM, MIN, MAX, TEXTJOIN, AVERAGE, COUNT, etc.

Si ves algún #CALC! errores, probablemente significa que las celdas de entrada dependientes están en blanco o que las opciones seleccionadas no tienen valores relacionados. Esto podría suceder si cambia la selección en la primera celda de entrada y no selecciona opciones válidas de los menús desplegables actualizados para las celdas siguientes.

Conclusión

Creo que logramos nuestro objetivo. Creamos una serie de menús desplegables dependientes. Usamos algunas funciones (FILTER, UNIQUE, ORDENAR) y validación de datos. Déjame saber lo que piensas publicando un comentario a continuación… ¡gracias!

Archivo de muestra

FiltroDropDowns.xlsxDescargar