Cómo hacer una lista desplegable en Excel

La lista desplegable es una herramienta increíblemente útil que puede ayudar a que trabajar con información sea más cómodo. Permite contener varios valores en una celda a la vez, con los que puede trabajar, como con cualquier otro. Para seleccionar el que necesita, simplemente haga clic en el icono de la flecha, después de lo cual se muestra una lista de valores uXNUMXbuXNUMXbis. Después de seleccionar uno específico, la celda se llena automáticamente con él y las fórmulas se vuelven a calcular en función de él.

Excel proporciona muchos métodos diferentes para generar un menú desplegable y, además, le permite personalizarlos de manera flexible. Analicemos estos métodos con más detalle.

Proceso de creación de listas

Para generar un menú emergente, haga clic en los elementos del menú a lo largo de la ruta "Datos" - "Validación de datos". Se abrirá un cuadro de diálogo donde debe encontrar la pestaña "Parámetros" y hacer clic en ella si no se ha abierto antes. Tiene muchas configuraciones, pero el elemento "Tipo de datos" es importante para nosotros. De todos los significados, "Lista" es el correcto.

La cantidad de métodos mediante los cuales se ingresa información en la lista emergente es bastante grande.

1. Indicación independiente de los elementos de la lista separados por punto y coma en el campo “Fuente” ubicado en la misma pestaña del mismo cuadro de diálogo.
   

   

2. Indicación preliminar de valores. El campo Fuente contiene el rango donde está disponible la información requerida.
   

   

3. Especificación de un rango con nombre. Un método que repite el anterior, pero solo es necesario nombrar preliminarmente el rango.
   

   

Cualquiera de estos métodos producirá el resultado deseado. Veamos métodos para generar listas desplegables en situaciones de la vida real.

Basado en datos de la lista.

Digamos que tenemos una tabla que describe los tipos de diferentes frutas.

Para crear una lista en un menú desplegable basado en este conjunto de información, debe hacer lo siguiente:

1. Seleccione la celda reservada para la lista futura.
2. Busque la pestaña Datos en la cinta. Allí damos clic en “Verificar datos”.
   

   

3. Busque el elemento "Tipo de datos" y cambie el valor a "Lista".
   

   

4. En el campo que indica la opción "Fuente", ingrese el rango deseado. Tenga en cuenta que las referencias absolutas deben especificarse para que al copiar la lista, la información no se desplace.
   

Además, existe una función para generar listas a la vez en más de una celda. Para lograr esto, debe seleccionarlos todos y realizar los mismos pasos que se describieron anteriormente. Nuevamente, debe asegurarse de que se escriban referencias absolutas. Si la dirección no tiene un signo de dólar junto a los nombres de las columnas y las filas, debe agregarlos presionando la tecla F4 hasta que el signo $ esté junto a los nombres de las columnas y las filas.

Con registro de datos manual

En la situación anterior, la lista se escribió resaltando el rango requerido. Este es un método conveniente, pero a veces es necesario registrar manualmente los datos. Esto permitirá evitar la duplicación de información en el libro de trabajo.

Supongamos que nos enfrentamos a la tarea de crear una lista que contenga dos opciones posibles: sí y no. Para llevar a cabo la tarea, es necesario:

1. Haga clic en la celda de la lista.
2. Abra "Datos" y allí encontrará la sección "Verificación de datos" que nos resulta familiar.
   

   

3. Nuevamente, seleccione el tipo "Lista".
   

   

4. Aquí debe ingresar “Sí; No” como la fuente. Vemos que la información se ingresa manualmente usando un punto y coma para la enumeración.

Después de hacer clic en Aceptar, tenemos el siguiente resultado.

A continuación, el programa creará automáticamente un menú desplegable en la celda correspondiente. Toda la información que el usuario ha especificado como elementos en la lista emergente. Las reglas para crear una lista en varias celdas son similares a las anteriores, con la única excepción de que debes especificar la información manualmente usando un punto y coma.

Crear una lista desplegable usando la función OFFSET

Además del método clásico, es posible utilizar la función ELIMINACIÓNpara generar menús desplegables.

Abramos la hoja.

Para usar la función para la lista desplegable, debe hacer lo siguiente:

1. Seleccione la celda de interés donde desea colocar la lista futura.
2. Abra la pestaña "Datos" y la ventana "Validación de datos" en secuencia.
   

   

3. Establecer "Lista". Esto se hace de la misma manera que en los ejemplos anteriores. Finalmente, se utiliza la siguiente fórmula: =DESPLAZAMIENTO(A$2$;0;0;5). Lo ingresamos donde se especifican las celdas que se utilizarán como argumento.

Luego el programa creará un menú con una lista de frutas.

La sintaxis para esto es:

=DESPLAZAMIENTO(referencia,desplazamiento_línea,desplazamiento_columna,[alto],[ancho])

Vemos que esta función tiene 5 argumentos. En primer lugar, se proporciona la dirección de la primera celda que se compensará. Los siguientes dos argumentos especifican cuántas filas y columnas compensar. Hablando de nosotros, el argumento Height es 5 porque representa el alto de la lista. 

Lista desplegable en Excel con sustitución de datos (+ usando la función OFFSET)

En el caso dado ELIMINACIÓN permite crear un menú emergente ubicado en un rango fijo. La desventaja de este método es que después de agregar el elemento, deberá editar la fórmula usted mismo.

Para crear una lista dinámica con soporte para ingresar nueva información, debe:

1. Seleccione la celda de interés.
2. Expanda la pestaña "Datos" y haga clic en "Validación de datos".
3. En la ventana que se abre, seleccione el elemento "Lista" nuevamente y especifique la siguiente fórmula como fuente de datos: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Haga clic en Aceptar.

Esto contiene una función COUNTIF, para determinar de inmediato cuántas celdas se llenan (aunque tiene una cantidad mucho mayor de usos, solo lo escribimos aquí para un propósito específico).

Para que la fórmula funcione normalmente, es necesario rastrear si hay celdas vacías en la ruta de la fórmula. No deberían serlo.

Lista desplegable con datos de otra hoja o archivo de Excel

El método clásico no funciona si necesita obtener información de otro documento o incluso una hoja contenida en el mismo archivo. Para ello se utiliza la función INDIRECT, que le permite ingresar en el formato correcto un enlace a una celda ubicada en otra hoja o, en general, un archivo. Necesitas hacer lo siguiente:

1. Activar la celda donde colocamos la lista.
2. Abrimos la ventana que ya conocemos. En el mismo lugar donde anteriormente indicamos fuentes para otros rangos, se indica una fórmula en el formato =INDIRECTO(“[Lista1.xlsx]Hoja1!$A$1:$A$9”). Naturalmente, en lugar de List1 y Sheet1, puede insertar los nombres de su libro y hoja, respectivamente. 

¡Atención! El nombre del archivo se da entre corchetes. En este caso, Excel no podrá utilizar el archivo que actualmente está cerrado como fuente de información.

También se debe tener en cuenta que el nombre del archivo en sí solo tiene sentido si el documento requerido se encuentra en la misma carpeta en la que se insertará la lista. De lo contrario, debe especificar la dirección de este documento en su totalidad.

Creación de menús desplegables dependientes

Una lista dependiente es aquella cuyo contenido se ve afectado por la elección del usuario en otra lista. Supongamos que tenemos una tabla abierta frente a nosotros que contiene tres rangos, a cada uno de los cuales se le ha dado un nombre.

Debe seguir estos pasos para generar listas cuyo resultado se ve afectado por la opción seleccionada en otra lista.

1. Cree la primera lista con nombres de rango.
   

   

2. En el punto de entrada de la fuente, los indicadores requeridos se resaltan uno por uno.
   

   

3. Cree una segunda lista según el tipo de planta que la persona haya elegido. Alternativamente, si especifica árboles en la primera lista, entonces la información en la segunda lista será "roble, carpe, castaño" y más. Es necesario anotar la fórmula en el lugar de entrada de la fuente de datos =INDIRECTO(E3). E3 – celda que contiene el nombre del rango 1.=INDIRECTO(E3). E3 – celda con el nombre de la lista 1.

Ahora todo esta listo.

¿Cómo seleccionar múltiples valores de una lista desplegable?

A veces no es posible dar preferencia a un solo valor, por lo que se debe seleccionar más de uno. Luego, debe agregar una macro al código de la página. Con la combinación de teclas Alt + F11 se abre el Editor de Visual Basic. Y el código se inserta allí.

Sub hoja de trabajo privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Å2:Å9»)) No es nada y Target.Cells.Count = 1 Entonces

        Application.EnableEvents = False

        Si Len (Target.Offset (0, 1)) = 0 Entonces

            Objetivo.Desplazamiento (0, 1) = Objetivo

        más

            Destino.Final (xlToRight) .Desplazamiento (0, 1) = Destino

        Si terminar

        Objetivo.ClearContents

        Application.EnableEvents = True

    Si terminar

End Sub 

Para que el contenido de las celdas se muestre a continuación, insertamos el siguiente código en el editor.

Sub hoja de trabajo privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) No es nada y Target.Cells.Count = 1 Entonces

        Application.EnableEvents = False

        Si Len (Target.Offset (1, 0)) = 0 Entonces

            Objetivo.Desplazamiento (1, 0) = Objetivo

        más

            Destino.Final (xlDown) .Desplazamiento (1, 0) = Destino

        Si terminar

        Objetivo.ClearContents

        Application.EnableEvents = True

    Si terminar

End Sub

Y finalmente, este código se usa para escribir en una celda.

Sub hoja de trabajo privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) No es nada y Target.Cells.Count = 1 Entonces

        Application.EnableEvents = False

        nuevoVal = Objetivo

        Aplicación.Deshacer

        oldval = Objetivo

        Si Len (oldval) <> 0 And oldval <> newVal Entonces

            Objetivo = Objetivo & «,» & newVal

        más

            Destino = valor nuevo

        Si terminar

        Si Len (newVal) = 0 Entonces Target.ClearContents

        Application.EnableEvents = True

    Si terminar

End Sub

Los rangos son editables.

¿Cómo hacer una lista desplegable con una búsqueda?

En este caso, debe utilizar inicialmente un tipo diferente de lista. Se abre la pestaña "Desarrollador", después de lo cual debe hacer clic o tocar (si la pantalla es táctil) en el elemento "Insertar" - "ActiveX". Tiene un cuadro combinado. Se le pedirá que dibuje esta lista, después de lo cual se agregará al documento.

Además, se configura a través de propiedades, donde se especifica un rango en la opción ListFillRange. La celda donde se muestra el valor definido por el usuario se configura mediante la opción LinkedCell. A continuación, solo necesita anotar los primeros caracteres, ya que el programa sugerirá automáticamente los posibles valores.

Lista desplegable con sustitución automática de datos

También hay una función en la que los datos se sustituyen automáticamente después de que se agregan al rango. Es fácil hacer esto:

1. Cree un conjunto de celdas para la lista futura. En nuestro caso, se trata de un conjunto de colores. Lo seleccionamos.
   

   

2. A continuación, debe formatearse como una tabla. Debe hacer clic en el botón del mismo nombre y seleccionar el estilo de tabla.
   

   

   

A continuación, debe confirmar este rango presionando el botón "OK".

Seleccionamos la tabla resultante y le damos un nombre a través del campo de entrada ubicado en la parte superior de la columna A.

Eso es todo, hay una tabla y se puede usar como base para una lista desplegable, para lo cual necesita:

1. Seleccione la celda donde se encuentra la lista.
2. Abra el cuadro de diálogo Validación de datos.
   

   

3. Establecemos el tipo de dato en “Lista”, y como valores le damos el nombre de la tabla a través del signo =.
   

   

   

Todo, la celda está lista y los nombres de los colores se muestran en ella, como lo necesitábamos originalmente. Ahora puede agregar nuevas posiciones simplemente escribiéndolas en una celda ubicada un poco más abajo inmediatamente después de la última.

Esta es la ventaja de la tabla, que el rango aumenta automáticamente cuando se agregan nuevos datos. En consecuencia, esta es la forma más conveniente de agregar una lista.

¿Cómo copiar la lista desplegable?

Para copiar, es suficiente usar la combinación de teclas Ctrl + C y Ctrl + V. Entonces, la lista desplegable se copiará junto con el formato. Para eliminar el formato, debe usar un pegado especial (en el menú contextual, esta opción aparece después de copiar la lista), donde está configurada la opción "condiciones sobre los valores".

Seleccione todas las celdas que contengan una lista desplegable

Para realizar esta tarea, debe usar la función "Seleccionar un grupo de celdas" en el grupo "Buscar y seleccionar".

Después de eso, se abrirá un cuadro de diálogo, donde debe seleccionar los elementos "Todos" y "Estos mismos" en el menú "Validación de datos". El primer elemento selecciona todas las listas, y el segundo selecciona solo aquellas que son similares a ciertas.