Excel

Cómo hacer listas desplegables dependientes en Excel

How Make Dependent Dropdown Lists Excel

Ver también: Descripción general de la validación de datos | Fórmulas de validación de datos

Una de las características más útiles de la validación de datos es la capacidad de crear una lista desplegable que permite a los usuarios seleccionar un valor de una lista predefinida. Las listas desplegables facilitan que los usuarios ingresen solo datos que cumplan con sus requisitos.

Ejemplo de lista desplegable creada con validación de datos



Las listas desplegables son fáciles de crear y usar. Pero una vez que empiece a utilizar menús desplegables para sus hojas de cálculo, inevitablemente se encontrará con un desafío: ¿cómo puede hacer que los valores de una lista desplegable dependan de los valores de otra? En otras palabras, ¿cómo se puede hacer que una lista desplegable sea dinámica?

cómo agregar fecha y hora en Excel

Aquí hay unos ejemplos:

  • una lista de ciudades que depende del país seleccionado
  • una lista de sabores que depende del tipo de helado
  • una lista de modelos que depende del fabricante
  • una lista de alimentos que depende de la categoría

Este tipo de listas se llaman menús desplegables dependientes , ya que la lista depende de otro valor. Se crean con validación de datos, utilizando una fórmula personalizada basada en el Función INDIRECTA y rangos con nombre . Esto puede parecer complicado, pero en realidad es muy simple y un gran ejemplo de cómo se puede usar INDIRECT.

Siga leyendo para ver cómo crear listas desplegables dependientes en Excel.

Ejemplo de menú desplegable dependiente

En el ejemplo que se muestra a continuación, la columna B proporciona un menú desplegable para la categoría de alimentos y la columna C proporciona opciones en la categoría elegida. Si el usuario selecciona 'Fruta', verá una lista de frutas, si selecciona 'Nuez', verá una lista de nueces, y si selecciona 'Vegetal', verá una lista de verduras.

lista desplegable regular con rango horizontal

lista desplegable dependiente con fórmula personalizada e INDIRECTO

La validación de datos en la columna B usa esta fórmula personalizada:

|_+_|

Y la validación de datos en la columna C usa esta fórmula de corte:

|_+_|

Donde la hoja de trabajo contiene los siguientes rangos con nombre:

categoría = E4: G4
vegetal = F5: F10
tuerca = G5: G9
fruta = E5: E11

Como funciona esto

La clave de esta técnica es rangos con nombre + la función INDIRECTA. INDIRECTO acepta valores de texto e intenta evaluarlos como referencias de celda. Por ejemplo, INDIRECTO tomará el texto 'A1' y lo convertirá en una referencia real:

|_+_|

De manera similar, INDIRECTO convertirá el texto 'A1: A10' en el rango A1: A10 dentro de otra función:

|_+_|

A primera vista, esta construcción puede resultarle molesta o incluso inútil. ¿Por qué complicar una fórmula simple y agradable con INDIRECT?

Tenga la seguridad, hay un método para la locura :)

La belleza de INDIRECT es que te permite usar texto exactamente como una referencia de celda . Esto proporciona dos beneficios clave:

  1. Puede ensamblar una referencia de texto dentro de una fórmula, lo cual es útil para ciertos tipos de referencias dinámicas .
  2. Puede seleccionar valores de texto en una hoja de trabajo y usarlos como una referencia de celda en una fórmula.

En el ejemplo de esta página, combinamos la última idea con rangos con nombre para crear listas desplegables dependientes. INDIRECTO asigna texto a un rango con nombre, que luego se resuelve en una referencia válida. Entonces, en este ejemplo, estamos recogiendo los valores de texto en la columna B y usando INDIRECT para convertirlos en referencias de celda haciendo coincidir rangos con nombre existentes, como este:

cómo sumar y restar fechas en Excel
|_+_|

B5 se resuelve en el texto 'nut' que se resuelve en el rango G5: G9.

Cómo configurar listas desplegables dependientes

Esta sección describe cómo configurar las listas desplegables dependientes que se muestran en el ejemplo.

1. Cree las listas que necesita. En el ejemplo, cree una lista de frutas, nueces y verduras en una hoja de trabajo.

crear las listas necesarias para la validación de datos

2. Cree rangos con nombre para cada lista: categoría = E4: G4, vegetal = F5: F10, nuez = G5: G9 y fruta = E5: E11.

crear los rangos con nombre necesarios para la validación de datos

Importante: los valores de E4, F4 y G4 deben coincidir con los tres últimos rangos mencionados anteriormente (vegetales, nueces y frutas). En otras palabras, debe asegurarse de que los rangos con nombre que creó tengan un nombre que coincida con los valores que aparecerán en la lista desplegable Categoría.

Nota: Si los rangos con nombre son nuevos para usted, ver esta pagina .

3. Cree y pruebe una regla de validación de datos para proporcionar una lista desplegable para Categoría utilizando la siguiente fórmula personalizada:

|_+_|

regla de validación de datos para la categoría

Nota: para que quede claro, el rango con nombre 'categoría' se usa solo por motivos de legibilidad y conveniencia; no es necesario usar un rango con nombre aquí. También tenga en cuenta que la validación de datos con una lista funciona bien con rangos horizontales y verticales; ambos se presentarán como un menú desplegable vertical.

4. Cree una regla de validación de datos para la lista desplegable dependiente con una fórmula personalizada basada en la función INDIRECTA:

|_+_|

regla de validación de datos para menú desplegable de alimentos

En esta fórmula, INDIRECTO simplemente evalúa los valores en la columna B como referencias, lo que los vincula a los rangos nombrados previamente definidos.

Excel fórmula si entre dos números entonces

5. Pruebe las listas desplegables para asegurarse de que respondan dinámicamente a los valores de la columna B.

validación de datos de prueba para la lista de alimentos

Nota: el enfoque que estamos adoptando aquí no distingue entre mayúsculas y minúsculas. El rango con nombre se llama 'tuerca' y el valor en B6 es 'Tuerca' pero la función INDIRECTA se resuelve correctamente en el rango con nombre, aunque el caso sea diferente.

Lidiar con los espacios

Los rangos con nombre no permiten espacios, por lo que la convención habitual es utilizar caracteres de subrayado en su lugar. Entonces, por ejemplo, si desea crear un rango con nombre para helado , usarías helado. Esto funciona bien, pero las listas desplegables dependientes se romperán si intentan asignar 'helado' a 'helado_crema'. Para solucionar este problema, puede utilizar una fórmula personalizada más sólida para la validación de datos:

|_+_|

Esta fórmula todavía usa INDIRECTO para vincular el valor de texto en A1 a un rango con nombre, pero antes de que se ejecute INDIRECTO, la función SUSTITUIR reemplaza todos los espacios con guiones bajos. Si el texto no contiene espacios, SUBSTITUTE no tiene ningún efecto.

Archivo de práctica

El archivo de ejemplo se adjunta a continuación; compruébelo para ver cómo funciona. Si desea aprender la técnica usted mismo, le recomiendo que cree un archivo propio. La mejor forma de aprender es haciendo.

Autor Dave Bruns Adjuntos Archivo menú desplegable dependiente.xlsx


^