Cómo crear una lista desplegable dependiente (en cascada) en Excel usando 5 técnicas diferentes

Hasta ahora, en esta serie de Validación de datos, hemos aprendido a crear una lista desplegable normal y una lista desplegable dinámica utilizando diferentes técnicas con validación de datos en Excel.

Y, hoy, en este capítulo, le mostraremos cómo crear una lista desplegable de dependientes en Microsoft Excel, utilizando diferentes métodos.

La lista desplegable dependiente también se conoce como validación de datos en cascada y limita las opciones en una lista desplegable, según el valor seleccionado en la otra celda que contiene la validación de datos. En otras palabras, depende del valor seleccionado en la primera lista desplegable que determina los valores que se mostrarán en la segunda lista desplegable.

Este es un escenario muy común de trabajar con datos grandes o algunos informes dinámicos, donde desea tener 2^{Dakota del Norte} celda muestra una lista que depende del elemento de lista elegido en el primer menú desplegable.

Como sabemos que en Excel, hay muchas formas de realizar una tarea en particular y, de manera similar, hay muchas formas de crear validación de datos dependientes en Excel. Y, hoy, demostraremos 5 técnicas diferentes para crear una lista de validación de datos dependientes.

Los datos sin procesar pueden estar en cualquier orden o formato y cada vez no puede cambiar los datos o el formato para obtener lo que busca.

Entonces, tomamos un conjunto de datos pero en 3 formatos diferentes para obtener la lista desplegable dependiente. Y, como puede ver, nuestros datos están en el lado izquierdo que va de la columna A a la columna E, y tendremos nuestro resultado esperado en el lado derecho que está en las columnas J y K. La columna J tendrá la validación primaria lista mientras que la columna K será dependiente y mostrará los valores en función del valor seleccionado en la columna J.

1^{S t} Ejemplo:-

2^{Dakota del Norte} Ejemplo:-

3^rd Ejemplo:-

1^{S t} Ejemplo:-

Tenemos una lista de productos para cada código de producto desde la columna A8 hasta la E13. Y queremos seleccionar el código de producto en J10, luego, dependiendo del código de producto seleccionado, un nombre de producto en la celda K10.

Primer método:-

El primer método es muy simple y corto, y solo requiere 3 pasos para obtener la lista desplegable dependiente. Sin embargo, solo funciona correctamente hasta que no realice ningún cambio en su rango. Una vez que modifique sus datos, primero tendrá que modificar el rango con nombre para obtener la validación de datos en cascada actualizada.

Siga los pasos que se indican a continuación.:-

Seleccione toda la tabla de A8 a E13

Luego vaya a la pestaña "Fórmulas", luego en la categoría "Nombres definidos", haga clic en "Crear a partir de la selección"
También puede utilizar el método abreviado de teclado CTRL + MAYÚS + F3
Aparecerá el cuadro de diálogo Crear nombres a partir de selecciones

Solicita confirmar qué filas y columnas se utilizarán para crear los nombres de otras filas y columnas. Confirmamos usar la "fila superior" para crear los nombres y desmarcamos la 2^{Dakota del Norte} opción y luego hacemos clic en Aceptar

Nota: - Los espacios y otros caracteres especiales, excepto el subrayado y el punto, no están permitidos como nombres. De forma predeterminada, se convertirá en guión bajo. Por lo tanto, use un carácter de subrayado y un punto para separar las palabras. Además, la primera letra no puede ser un número; debe ser una letra, un guión bajo o una barra invertida.

Ahora, para confirmar que cada rango tiene un nombre, vamos al “Administrador de nombres” (presione CTRL + F3)
Allí podemos ver los 5 rangos con nombre disponibles
Y también podemos ver que cada nombre de rango tiene un guión bajo en lugar de un espacio en blanco en el medio de la cadena.

Ahora, crearemos una lista desplegable.:-

Seleccione la celda J10 y presione ALT ++ D + L para abrir el cuadro de diálogo Validación de datos
Seleccione Lista> luego ingrese el rango A8: E8 en la pestaña Fuente

Haga clic en Aceptar.
Ahora crearemos una lista dependiente en la celda K10
Abra el cuadro de diálogo Validación de datos presionando la tecla ALT + D + L
Seleccione Lista, en fuente ingrese esta función: - = INDIRECTO (SUSTITUTO ($ J $ 10, "", "_"))

En la validación de datos, para crear la lista dependiente, hemos utilizado la función INDIRECTA para devolver el valor basado en la lista de validación de datos primarios. Y, para reemplazar el subrayado con el espacio, usaremos la función SUSTITUIR dentro de la función INDIRECTO.

Haga clic en Aceptar.

Cuando seleccionamos cualquier código de producto en la celda J10, la lista de productos del código de producto seleccionado aparecerá en la celda K10. Por ejemplo: - Hemos seleccionado ETV 501, ahora puede ver que la lista de productos dependientes aparece en la celda K10

Nota: - Siempre que agregue el nombre del producto y el código del producto que no aparecerá en la lista.

Por ejemplo: - Hemos agregado el Producto 26 bajo el código de producto ETV 505, pero cuando seleccionamos el producto ETV 505, el producto agregado no aparece en la lista desplegable.

Entonces, así es como puede crear una lista desplegable dependiente utilizando una técnica simple en solo 3 pasos simples.

2^{Dakota del Norte} Ejemplo:-

En este ejemplo, veremos cómo obtener una lista desplegable dependiente cuando tenga sus datos como se muestra en esta tabla vertical.

Usaremos dos métodos diferentes para crear una lista desplegable dependiente. Ambas son técnicas casi similares. Sin embargo, uno no tiene el rango con nombre y el otro tendrá el rango con nombre.

1^{S t} Método:-

Para hacer lo mismo, usaremos las funciones OFFSET, MATCH & COUNTIF juntas.

Como sabemos que la función OFFSET se usa para crear el rango dinámico, por lo tanto, para crear la lista de “Validación de datos dinámicos”, usamos la función OFFSET para devolver el rango dinámico.

COINCIDIR se utiliza para devolver la posición relativa de un elemento en una lista en Excel. Y aquí nos ayudará a hacer coincidir la categoría seleccionada en la lista desplegable principal en nuestro rango en la hoja, y devolverá un número.

Y CONTAR.SI se utiliza para obtener el recuento de celdas que coinciden con los criterios. Y aquí usaremos esto para contar el número de filas que aparecerán usando la función CONTAR.SI.

Siga los pasos que se indican a continuación.:-

Seleccione la celda J21, en la que crearemos nuestra lista de validación de datos primarios
Presione la tecla ALT + D + L para abrir el cuadro de diálogo Validación de datos
Seleccionar lista de la categoría permitida
Haga clic en la pestaña Fuente y seleccione el rango de B20: B24

Y haga clic en Aceptar

Vaya a la celda K21 y abra el cuadro de diálogo de validación de datos una vez más
Luego seleccionamos Lista y, en la fuente, ingresamos a la siguiente función:
= COMPENSACIÓN ($ E $ 19, PARTIDA ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, CONTAR.SI ($ D $ 20: $ D $ 32, $ J $ 21))

Haga clic en Aceptar.
En la celda K21, podemos ver todos los valores correspondientes del código de producto seleccionado: -

Entonces, así es como puede obtener la lista dependiente tomando referencias de celda en la función.

2^{Dakota del Norte} Método:-

En el siguiente método, usaremos un rango con nombre en la misma función para obtener la validación de datos en cascada. Primero, debemos crear la lista dinámica para el código de producto. En caso de que se agregue algún producto nuevo a los datos, el menú desplegable debe actualizarse para mostrar el mismo.

Para hacer lo mismo, siga los pasos que se indican a continuación.:-

Seleccione B19, luego presione CTRL + F3 para abrir la ventana "Administrador de nombres"
Ahora, hacemos clic en "Nuevo" y aparece el cuadro de diálogo "Definir nombre"
Podemos ver que el nombre ya aparece en el cuadro de nombre, eso se debe a que seleccionamos B9 antes de abrir la ventana "Administrador de nombres". Y, como B19 tiene texto, si queremos, podemos cambiarlo por otro nombre.

Ingrese a continuación la fórmula de mención: -

= DESPLAZAMIENTO ('Lista de descenso dependiente'! $ B $ 20,0,0, COUNTA ('Lista de descenso dependiente'! $ B $ 20: $ B $ 32))

Haga clic en Aceptar.

Como hemos creado una lista dinámica para productos únicos, ahora crearemos un rango dinámico para el rango de código de producto que se encuentra en la columna D.

Siga los mismos pasos que hemos seguido para un producto único: -

Seleccione la celda D19, abra el cuadro de diálogo Definir nombre
Encontrarás que el nombre ya está allí
En referencia, ingrese la siguiente fórmula: -

= OFFSET ('Lista desplegable de dependientes'! $ D $ 20,0,0, COUNTA ('Lista desplegable de dependientes'! $ D $ 20: $ D $ 35))

Haga clic en Aceptar.
Ahora ambos rangos dinámicos están listos. Entonces, vamos a J22 y presionamos "ALT + D + L" y seleccionamos "Lista"
En la fuente, tendremos el rango con nombre que lo definimos para "Código de producto único", por lo que presionamos F3 para ver todos los rangos con nombre disponibles.
Podemos ver el rango denominado "Código de producto único", por lo que hacemos clic en él y luego en Aceptar y presionamos Enter.

En el momento en que presionamos enter, aparece la flecha desplegable en la celda J22, que contiene la lista de códigos de producto únicos

Seleccione la celda K22 y abra el cuadro de diálogo "Validación de datos"
Usaremos la misma función que usamos en el último método pero con rango con nombre
Seleccione la lista, y luego en la fuente, ingrese la siguiente fórmula: -

= COMPENSACIÓN ($ E $ 19, COINCIDIR ($ J $ 22, Código_producto, 0), 0, CONTAR.SI (Código_producto, J22))

Haga clic en Aceptar.
Ahora, tenemos el menú desplegable principal y la lista secundaria de productos.
Seleccione el producto “ETV-101” de J22, y en K22, solo podemos ver los nombres que pertenecen a este producto “ETV-101”. Y, cuando cambiamos cualquier producto ("ETV-103) en J22, K22 muestra los valores correspondientes para ese código

Ahora veremos qué sucede cuando agregamos un nuevo código de producto a la lista. ¿Se actualizarán estas listas desplegables?

Agreguemos un nuevo producto a la lista; Siga los pasos que se indican a continuación: -

Agregue el código de producto en la lista de Unique_Prod_Code

También agregue Product_Code y Product_Name en los datos: -

Ahora verifique la lista desplegable: aparece el código y el nombre del producto agregado

3^rd Ejemplo:-

Tenemos los títulos dinámicos directamente de la tabla y agregaremos nuevos productos a la gama. La tabla tiene el mismo formato que usamos para 1^{S t} método.

4^th Método:-

Siga los pasos que se indican a continuación.:-

Seleccione el título A40: E40
Primero cree el rango dinámico para los títulos, abra el cuadro de diálogo "Definir nombre"
Escriba "Encabezado" en el lugar del nombre, y luego en "se refiere a", ingrese la fórmula mencionada a continuación: -
Ingrese la siguiente función: -
= OFFSET ('Lista desplegable de dependientes'! $ A $ 40 ,,,, COUNTA ('Lista desplegable de dependientes'! $ 40: $ 40))
Haga clic en Aceptar.

El rango dinámico de "rumbo" está listo ahora

Y, ahora crearemos el rango con nombre para cada encabezado, siga los pasos que se indican a continuación: -

Seleccione la tabla de A40 a E50
Atajo de teclado CTRL + MAYÚS + F3
Desmarcamos el 2^{Dakota del Norte} opción
Y, antes de hacer clic en Aceptar, asegúrese de que 1^{S t} la opción "Fila superior" está seleccionada

Ahora estamos listos con ambas gamas

Ahora, prepararemos la lista desplegable de padres.

Seleccione la celda J42
Abra el cuadro de diálogo Validación de datos

Luego, después de seleccionar "Lista", presionamos F3 en la fuente para obtener el rango con nombre para los títulos. Hacemos clic en “Encabezado” y luego en Aceptar y presionamos enter. Tenemos la lista principal en J42 ahora

Para crear la lista de detalles del artículo, seleccione la celda K42
Abra el cuadro de diálogo Validación de datos presionando la tecla ALT + D + L
Seleccione Lista y luego ingrese a la siguiente función en la pestaña Fuente: -
= DESPLAZAMIENTO (INDIRECTO (SUSTITUTO ($ J $ 42, "", "_")) ,,, CONTAR (INDIRECTO (SUSTITUTO ($ J $ 42, "", "_"))))

Haga clic en Aceptar.

Ahora seleccione algún elemento en el J42, digamos que seleccionamos “Elemento 01” y mire la lista desplegable de K42. Y, como los 3 métodos anteriores, también obtuvimos una lista de dependientes.

¿Qué hay de nuevo? En el primer ejemplo, no pudo agregar ningún producto a la lista, pero aquí puede agregar cualquier producto nuevo. Entonces, digamos que agregamos un nuevo producto a este artículo. Vamos a A45, escribimos “ETV-501 Prod 05” y luego volvemos a K42 y aquí está. Puede ver que se ha agregado el nuevo producto.

Ahora agregue algunos productos bajo el nuevo elemento

Cuando seleccionamos “Elemento 06”, vamos a K42 y hacemos clic en la lista desplegable. Sorprendentemente, no sucede nada cuando hacemos clic en la flecha desplegable. Esto se debe a que hemos creado todo lo dinámico y nos hemos olvidado de crear un rango dinámico para la tabla, por lo que los productos no se muestran en la lista secundaria.

Para hacer eso, necesitamos usar diferentes técnicas. Hay dos métodos para hacerlo. Puede crear la tabla o simplemente usar la función DESPLAZAMIENTO solamente. Y, en el siguiente método, usaremos la función OFFSET y veremos el truco para expandir el rango de la tabla también.

Entonces, primero vamos a J43 y presionamos "ALT + D + L"
Seleccionamos “Lista” y, luego en fuente, presionamos F3 y seleccionamos “Encabezado” y hacemos clic en Aceptar y luego presionamos enter

Ahora, vamos a K43, y luego de seleccionar "Lista", vamos a "Fuente" e ingresamos a la función mencionada a continuación.

= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1,1000 , 1)))

Haga clic en Aceptar.

Ahora, regresamos y seleccionamos “Elemento 06 en la celda J43 y regresamos a K43 y hacemos clic en la flecha desplegable. Pero, esta lista de tiempo muestra los productos que hemos agregado para un nuevo artículo. Y seleccionamos el primer producto “ETV-506 Prod 01”.

Así es como puede crear la lista desplegable dependiente utilizando diferentes métodos en cualquier tipo de formato de datos.

Video: Cómo crear una lista desplegable dependiente (en cascada) en Excel usando 5 técnicas diferentes en Microsoft Excel

Haga clic en el enlace del video para obtener una referencia rápida sobre su uso. ¡Suscríbete a nuestro nuevo canal y sigue aprendiendo con nosotros!

Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook.
Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico