Ver video: crear una lista desplegable de dependientes en Excel
Una lista desplegable de Excel es una característica útil cuando está creando formularios de entrada de datos o paneles de Excel.
Muestra una lista de elementos como un menú desplegable en una celda, y el usuario puede hacer una selección en el menú desplegable. Esto podría ser útil cuando tiene una lista de nombres, productos o regiones que a menudo necesita ingresar en un conjunto de celdas.
A continuación se muestra un ejemplo de una lista desplegable de Excel:
En el ejemplo anterior, he usado los elementos en A2: A6 para crear un menú desplegable en C3.
Leer: Aquí hay una guía detallada sobre cómo crear una lista desplegable de Excel.
A veces, sin embargo, es posible que desee utilizar más de una lista desplegable en Excel de modo que los elementos disponibles en una segunda lista desplegable dependan de la selección realizada en la primera lista desplegable.
Estos se denominan listas desplegables dependientes en Excel.
A continuación se muestra un ejemplo de lo que quiero decir con una lista desplegable dependiente en Excel:
Puede ver que las opciones en el menú desplegable 2 dependen de la selección realizada en el menú desplegable 1. Si selecciono 'Frutas' en el menú desplegable 1, se muestran los nombres de las frutas, pero si selecciono Verduras en el menú desplegable 1, entonces Me muestran los nombres de las verduras en el menú desplegable 2.
Esto se denomina lista desplegable condicional o dependiente en Excel.
Crear una lista desplegable de dependientes en Excel
Estos son los pasos para crear una lista desplegable dependiente en Excel:
- Seleccione la celda donde desea la primera lista desplegable (principal).
- Vaya a Datos -> Validación de datos. Esto abrirá el cuadro de diálogo de validación de datos.
- En el cuadro de diálogo de validación de datos, dentro de la pestaña de configuración, seleccione Lista.
- En el campo Fuente, especifique el rango que contiene los elementos que se mostrarán en la primera lista desplegable.
- Haga clic en Aceptar. Esto creará el menú desplegable 1.
- Seleccione el conjunto de datos completo (A1: B6 en este ejemplo).
- Vaya a Fórmulas -> Nombres definidos -> Crear a partir de la selección (o puede usar el atajo de teclado Control + Mayús + F3).
- En el cuadro de diálogo "Crear nombre a partir de la selección", marque la opción Fila superior y desmarque todas las demás. Al hacer esto, se crean dos rangos de nombres ("Frutas" y "Verduras"). El rango de frutas con nombre se refiere a todas las frutas de la lista y el rango de verduras con nombre se refiere a todas las verduras de la lista.
- Haga clic en Aceptar.
- Seleccione la celda donde desea la lista desplegable Dependiente / Condicional (E3 en este ejemplo).
- Vaya a Datos -> Validación de datos.
- En el cuadro de diálogo Validación de datos, dentro de la pestaña de configuración, asegúrese de que Lista esté seleccionada.
- En el campo Fuente, ingrese la fórmula = INDIRECTO (D3). Aquí, D3 es la celda que contiene el menú desplegable principal.
- Haga clic en Aceptar.
Ahora, cuando realiza la selección en el menú desplegable 1, las opciones enumeradas en la lista desplegable 2 se actualizarán automáticamente.
Descargar el archivo de ejemplo
¿Como funciona esto? - La lista desplegable condicional (en la celda E3) se refiere a = INDIRECTO (D3). Esto significa que cuando selecciona "Frutas" en la celda D3, la lista desplegable en E3 se refiere al rango denominado "Frutas" (a través de la función INDIRECTA) y, por lo tanto, enumera todos los elementos de esa categoría.
Nota IMPORTANTE: Si la categoría principal tiene más de una palabra (por ejemplo, 'Frutas de temporada' en lugar de 'Frutas'), entonces debe usar la fórmula = INDIRECTO (SUSTITUIR (D3, ”“, ”_”)), en lugar de función INDIRECTA simple mostrada arriba.
- La razón de esto es que Excel no permite espacios en rangos con nombre. Entonces, cuando crea un rango con nombre con más de una palabra, Excel inserta automáticamente un guión bajo entre las palabras. Por ejemplo, cuando crea un rango con nombre con "Frutas de temporada", se llamará Season_Fruits en el backend. El uso de la función SUSTITUIR dentro de la función INDIRECTO asegura que los espacios son convertido en guiones bajos.
Restablecer / borrar el contenido de la lista desplegable dependiente automáticamente
Cuando haya realizado la selección y luego cambie el menú desplegable principal, la lista desplegable dependiente no cambiará y, por lo tanto, será una entrada incorrecta.
Por ejemplo, si selecciona "Frutas" como la categoría y luego selecciona Apple como el elemento, y luego regresa y cambia la categoría a "Verduras", el menú desplegable dependiente continuará mostrando Apple como el elemento.
Puede usar VBA para asegurarse de que el contenido de la lista desplegable dependiente se restablezca siempre que se cambie la lista desplegable principal.
Aquí está el código VBA para borrar el contenido de una lista desplegable dependiente:
Private Sub Worksheet_Change (ByVal Target As Range) En caso de error Reanudar Siguiente If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
El crédito por este código es para este tutorial de Debra sobre cómo borrar listas desplegables dependientes en Excel cuando se cambia la selección.
A continuación se explica cómo hacer que este código funcione:
- Copie el código VBA.
- En el libro de trabajo de Excel donde tiene la lista desplegable dependiente, vaya a la pestaña Desarrollador, y dentro del grupo "Código", haga clic en Visual Basic (también puede usar el atajo de teclado - ALT + F11).
- En la ventana del editor de VB, a la izquierda en el explorador de proyectos, verá todos los nombres de las hojas de trabajo. Haga doble clic en el que tiene la lista desplegable.
- Pegue el código en la ventana de código a la derecha.
- Cierre el editor de VB.
Ahora, cada vez que cambie la lista desplegable principal, se activará el código VBA y se borrará el contenido de la lista desplegable dependiente (como se muestra a continuación).
Si no eres fanático de VBA, también puedes usar un simple truco de formato condicional que resaltará la celda siempre que haya una discrepancia. Esto puede ayudarlo a ver y corregir visualmente la falta de coincidencia (como se muestra a continuación).
Estos son los pasos para resaltar las discrepancias en las listas desplegables dependientes:
- Seleccione la celda que tiene las listas desplegables dependientes.
- Vaya a Inicio -> Formato condicional -> Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, seleccione "Usar una fórmula para determinar qué celdas formatear".
- En el campo de fórmula, ingrese la siguiente fórmula: = ESERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
- Configura el formato.
- Haga clic en Aceptar.
La fórmula utiliza la función BUSCARV para comprobar si el elemento de la lista desplegable dependiente es el de la categoría principal o no. Si no es así, la fórmula devuelve un error. Esto es utilizado por la función ESERROR para devolver VERDADERO que le dice al formato condicional para resaltar la celda.
También le pueden gustar los siguientes tutoriales de Excel:
- Extraiga datos según una selección de lista desplegable.
- Creación de una lista desplegable con sugerencias de búsqueda.
- Seleccione varios elementos de una lista desplegable.
- Cree múltiples listas desplegables sin repetición.
- Ahorre tiempo con formularios de entrada de datos en Excel.
