Como facer unha lista despregable en Excel

A lista despregábel é unha ferramenta incriblemente útil que pode axudar a facer máis cómodo traballar coa información. Permite conter varios valores nunha cela á vez, cos que podes traballar, como con calquera outra. Para seleccionar o que necesitas, só tes que facer clic na icona da frecha, despois de que se mostrará unha lista de valores uXNUMXbuXNUMXbis. Despois de seleccionar un específico, a cela énchese automaticamente con ela e as fórmulas recalcúlanse en función del.

Excel ofrece moitos métodos diferentes para xerar un menú despregable e, ademais, permítelle personalizalos de forma flexible. Analizemos estes métodos con máis detalle.

Proceso de creación de listas

Para xerar un menú emerxente, faga clic nos elementos do menú ao longo da ruta "Datos" - "Validación de datos". Abrirase un cadro de diálogo onde cómpre atopar a pestana "Parámetros" e premer nela se non se abriu antes. Ten moitas opcións de configuración, pero o elemento "Tipo de datos" é importante para nós. De todos os significados, "Lista" é o correcto.

O número de métodos polos que se introduce a información na lista emerxente é bastante grande.

1. Indicación independente dos elementos da lista separados por punto e coma no campo "Fonte" situado na mesma pestana da mesma caixa de diálogo.
   

   

2. Indicación previa de valores. O campo Orixe contén o intervalo onde está dispoñible a información requirida.
   

   

3. Especificando un intervalo nomeado. Un método que repite o anterior, pero só é necesario nomear previamente o rango.
   

   

Calquera destes métodos producirá o resultado desexado. Vexamos os métodos para xerar listas despregábeis en situacións da vida real.

Baseado nos datos da lista

Digamos que temos unha táboa que describe os tipos de froitas diferentes.

Para crear unha lista nun menú despregable baseado neste conxunto de información, cómpre facer o seguinte:

1. Seleccione a cela reservada para a lista futura.
2. Busca a pestana Datos na cinta. Alí prememos en "Verificar datos".
   

   

3. Busca o elemento "Tipo de datos" e cambia o valor a "Lista".
   

   

4. No campo que indica a opción "Fonte", introduza o intervalo desexado. Teña en conta que deben especificarse referencias absolutas para que ao copiar a lista, a información non cambie.
   

Ademais, hai unha función para xerar listas á vez en máis dunha cela. Para logralo, debes seleccionalos todos e realizar os mesmos pasos descritos anteriormente. De novo, cómpre asegurarse de que se escriben referencias absolutas. Se o enderezo non ten un sinal de dólar xunto aos nomes das columnas e das filas, debes engadilos premendo a tecla F4 ata que o signo $ estea xunto aos nomes das columnas e das filas.

Con gravación manual de datos

Na situación anterior, a lista escribiuse destacando o intervalo necesario. Este é un método cómodo, pero ás veces é necesario rexistrar manualmente os datos. Isto permitirá evitar a duplicación de información no caderno de traballo.

Supoñamos que estamos ante a tarefa de crear unha lista que conteña dúas opcións posibles: si e non. Para realizar a tarefa, é necesario:

1. Fai clic na cela da lista.
2. Abre "Datos" e alí atopa a sección "Comprobación de datos" que nos coñece.
   

   

3. De novo, seleccione o tipo "Lista".
   

   

4. Aquí cómpre introducir “Si; Non” como fonte. Vemos que a información introdúcese manualmente mediante un punto e coma para a enumeración.

Despois de facer clic en Aceptar, temos o seguinte resultado.

A continuación, o programa creará automaticamente un menú despregable na cela apropiada. Toda a información que o usuario especificou como elementos na lista emerxente. As regras para crear unha lista en varias celas son similares ás anteriores, coa única excepción de que debes especificar a información manualmente mediante un punto e coma.

Creación dunha lista despregable mediante a función OFFSET

Ademais do método clásico, é posible usar a función ELIMINACIÓNpara xerar menús despregables.

Abrimos a folla.

Para usar a función da lista despregable, cómpre facer o seguinte:

1. Seleccione a cela de interese onde quere colocar a lista futura.
2. Abra a pestana "Datos" e a xanela "Validación de datos" en secuencia.
   

   

3. Establecer "Lista". Isto faise do mesmo xeito que os exemplos anteriores. Finalmente, utilízase a seguinte fórmula: =COMPLEMENTO(A$2$;0;0;5). Introducímolo onde se especifican as celas que se usarán como argumento.

A continuación, o programa creará un menú cunha lista de froitas.

A sintaxe para isto é:

=OFFSET(referencia,desfase_liña,desplazamento_columna,[altura],[ancho])

Vemos que esta función ten 5 argumentos. En primeiro lugar, indícase o enderezo da primeira cela que se vai compensar. Os dous argumentos seguintes especifican cantas filas e columnas hai que compensar. Falando de nós, o argumento Altura é 5 porque representa a altura da lista. 

Lista despregable en Excel con substitución de datos (+ usando a función OFFSET)

No caso dado ELIMINACIÓN permitido crear un menú emerxente situado nun rango fixo. A desvantaxe deste método é que despois de engadir o elemento, terás que editar a fórmula ti mesmo.

Para crear unha lista dinámica con soporte para introducir nova información, debes:

1. Seleccione a cela de interese.
2. Expande a pestana "Datos" e fai clic en "Validación de datos".
3. Na xanela que se abre, seleccione de novo o elemento "Lista" e especifique a seguinte fórmula como fonte de datos: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Prema en Aceptar.

Isto contén unha función COUNTIF, para determinar inmediatamente cantas celas se enchen (aínda que ten un número moito maior de usos, só o escribimos aquí para un propósito específico).

Para que a fórmula funcione normalmente, é necesario rastrexar se hai celas baleiras no camiño da fórmula. Non deberían ser.

Lista despregable con datos doutra folla ou ficheiro de Excel

O método clásico non funciona se precisa obter información doutro documento ou mesmo dunha folla contida no mesmo ficheiro. Para iso utilízase a función INDIRECTO, que permite introducir no formato correcto unha ligazón a unha cela situada noutra folla ou en xeral: un ficheiro. Debes facer o seguinte:

1. Activa a cela onde colocamos a lista.
2. Abrimos a fiestra que xa coñecemos. No mesmo lugar onde anteriormente indicamos fontes para outros rangos, indícase unha fórmula no formato =INDIRECT(“[Lista1.xlsx]Folla1!$A$1:$A$9”). Por suposto, en lugar de Lista1 e Folla1, podes inserir os nomes dos teus libros e follas, respectivamente. 

Atención! O nome do ficheiro indícase entre corchetes. Neste caso, Excel non poderá utilizar o ficheiro que está actualmente pechado como fonte de información.

Tamén hai que ter en conta que o propio nome do ficheiro só ten sentido se o documento requirido se atopa no mesmo cartafol que aquel onde se inserirá a lista. De non ser así, deberá especificar o enderezo deste documento na súa totalidade.

Creación de listas desplegables dependentes

Unha lista dependente é aquela cuxos contidos se ven afectados pola elección do usuario noutra lista. Supoñamos que temos unha táboa aberta diante de nós que contén tres rangos, cada un dos cales recibiu un nome.

Debe seguir estes pasos para xerar listas cuxo resultado se vexa afectado pola opción seleccionada noutra lista.

1. Crea a primeira lista con nomes de rango.
   

   

2. No punto de entrada da fonte, os indicadores necesarios resáltanse un por un.
   

   

3. Crea unha 2a lista en función do tipo de planta que elixiu a persoa. Alternativamente, se especificas árbores na primeira lista, a información da segunda lista será "carballo, carpe, castiñeiro" e máis aló. É necesario anotar a fórmula no lugar de entrada da fonte de datos =INDIRECTO(E3). E3 – cela que contén o nome do intervalo 1.=INDIRECT(E3). E3 – cela co nome da lista 1.

Agora todo está listo.

Como seleccionar varios valores dunha lista despregable?

Ás veces non é posible dar preferencia a un só valor, polo que hai que seleccionar máis dun. A continuación, cómpre engadir unha macro ao código da páxina. Usando a combinación de teclas Alt + F11 ábrese o Editor de Visual Basic. E alí insírese o código.

Sub folla de traballo privada_Change (ByVal Target As Range)

    En Error Resume Next

    Se non se cruza (obxectivo, intervalo («Е2:Е9»)) é nada e destino.Cell.Count = 1 Entón

        Application.EnableEvents = Falso

        Se Len (Target.Offset (0, 1)) = 0 Entón

            Obxectivo.Desfase (0, 1) = Obxectivo

        outro

            Target.End (xlToRight) .Offset (0, 1) = Obxectivo

        End If

        Destino.ClearContents

        Application.EnableEvents = Verdadero

    End If

End Sub 

Para que o contido das celas se mostre a continuación, inserimos o seguinte código no editor.

Sub folla de traballo privada_Change (ByVal Target As Range)

    En Error Resume Next

    Se non se cruza (obxectivo, intervalo («Н2:К2»)) é nada e destino.Cell.Count = 1 Entón

        Application.EnableEvents = Falso

        Se Len (Target.Offset (1, 0)) = 0 Entón

            Obxectivo.Desfase (1, 0) = Obxectivo

        outro

            Target.End (xlDown) .Offset (1, 0) = Obxectivo

        End If

        Destino.ClearContents

        Application.EnableEvents = Verdadero

    End If

End Sub

E, finalmente, este código úsase para escribir nunha cela.

Sub folla de traballo privada_Change (ByVal Target As Range)

    En Error Resume Next

    Se non se cruza (obxectivo, intervalo («C2:C5»)) é nada e destino.Cell.Count = 1, entón

        Application.EnableEvents = Falso

        newVal = Obxectivo

        Aplicación.Desfacer

        oldval = Obxectivo

        Se Len (oldval) <> 0 E oldval <> newVal Entón

            Obxectivo = Obxectivo & «,» & newVal

        outro

            Obxectivo = newVal

        End If

        Se Len (newVal) = 0 Entón Target.ClearContents

        Application.EnableEvents = Verdadero

    End If

End Sub

Os intervalos son editables.

Como facer unha lista despregable cunha busca?

Neste caso, debes empregar inicialmente un tipo de lista diferente. Ábrese a pestana "Desenvolvedor", despois de que cómpre facer clic ou tocar (se a pantalla é táctil) no elemento "Inserir" - "ActiveX". Ten unha caixa combinada. Solicitarase que debuxe esta lista, despois de que se engadirá ao documento.

Ademais, configúrase mediante propiedades, onde se especifica un intervalo na opción ListFillRange. A cela onde se mostra o valor definido polo usuario configúrase mediante a opción LinkedCell. A continuación, só precisa anotar os primeiros caracteres, xa que o programa suxerirá automaticamente posibles valores.

Lista despregable con substitución automática de datos

Tamén hai unha función de que os datos substitúense automaticamente despois de engadirse ao intervalo. É doado facelo:

1. Crea un conxunto de celas para a lista futura. No noso caso, este é un conxunto de cores. Seleccionámolo.
   

   

2. A continuación, debe ser formateado como unha táboa. Debe facer clic no botón do mesmo nome e seleccionar o estilo da táboa.
   

   

   

A continuación, cómpre confirmar este intervalo premendo o botón "Aceptar".

Seleccionamos a táboa resultante e dámoslle un nome a través do campo de entrada situado na parte superior da columna A.

Isto é todo, hai unha táboa e pódese usar como base para unha lista despregable, para a que precisa:

1. Seleccione a cela onde se atopa a lista.
2. Abre o diálogo de validación de datos.
   

   

3. Establecemos o tipo de datos en "Lista" e como valores damos o nome da táboa a través do signo =.
   

   

   

Todo, a cela está lista, e os nomes das cores móstranse nela, como necesitabamos orixinalmente. Agora podes engadir novas posicións simplemente escribilas nunha cela situada un pouco máis abaixo inmediatamente despois da última.

Esta é a vantaxe da táboa, que o intervalo aumenta automaticamente cando se engaden novos datos. En consecuencia, esta é a forma máis conveniente de engadir unha lista.

Como copiar a lista despregable?

Para copiar, abonda con usar a combinación de teclas Ctrl + C e Ctrl + V. Así, a lista despregable copiarase xunto co formato. Para eliminar o formato, cómpre empregar un pegado especial (no menú contextual, esta opción aparece despois de copiar a lista), onde se establece a opción "condicións nos valores".

Seleccione todas as celas que conteñan unha lista despregable

Para realizar esta tarefa, debes usar a función "Seleccionar un grupo de celas" no grupo "Buscar e seleccionar".

Despois diso, abrirase un cadro de diálogo, onde debes seleccionar os elementos "Todos" e "Estes mesmos" no menú "Validación de datos". O primeiro elemento selecciona todas as listas e o segundo selecciona só aquelas que son similares a certas.