Como fazer uma lista suspensa no Excel

A lista suspensa é uma ferramenta incrivelmente útil que pode ajudar a tornar o trabalho com informações mais confortável. Possibilita conter vários valores em uma célula de uma só vez, com os quais você pode trabalhar, como com qualquer outra. Para selecionar o que você precisa, basta clicar no ícone de seta, após o qual uma lista de valores uXNUMXbuXNUMXbis é exibida. Após selecionar um específico, a célula é preenchida automaticamente com ele e as fórmulas são recalculadas com base nele.

O Excel fornece muitos métodos diferentes para gerar um menu suspenso e, além disso, permite personalizá-los com flexibilidade. Vamos analisar esses métodos com mais detalhes.

Processo de criação da lista

Para gerar um menu pop-up, clique nos itens de menu ao longo do caminho “Dados” – “Validação de dados”. Uma caixa de diálogo será aberta onde você precisa encontrar a guia “Parâmetros” e clicar nela se não tiver sido aberta antes. Tem muitas configurações, mas o item “Tipo de dados” é importante para nós. De todos os significados, “Lista” é o correto.

O número de métodos pelos quais as informações são inseridas na lista pop-up é bastante grande.

1. Indicação independente de elementos da lista separados por ponto e vírgula no campo “Fonte” localizado na mesma aba da mesma caixa de diálogo.
   

   

2. Indicação preliminar de valores. O campo Origem contém o intervalo em que as informações necessárias estão disponíveis.
   

   

3. Especificando um intervalo nomeado. Um método que repete o anterior, mas é necessário apenas nomear preliminarmente o intervalo.
   

   

Qualquer um desses métodos produzirá o resultado desejado. Vejamos os métodos para gerar listas suspensas em situações da vida real.

Com base nos dados da lista

Digamos que temos uma tabela descrevendo os tipos de frutas diferentes.

Para criar uma lista em um menu suspenso com base nesse conjunto de informações, você precisa fazer o seguinte:

1. Selecione a célula reservada para a lista futura.
2. Encontre a guia Dados na faixa de opções. Lá, clicamos em “Verificar dados”.
   

   

3. Encontre o item “Data Type” e mude o valor para “List”.
   

   

4. No campo que indica a opção “Fonte”, insira o intervalo desejado. Observe que as referências absolutas devem ser especificadas para que, ao copiar a lista, as informações não sejam alteradas.
   

Além disso, existe uma função para gerar listas de uma só vez em mais de uma célula. Para conseguir isso, você deve selecioná-los todos e executar as mesmas etapas descritas anteriormente. Novamente, você precisa certificar-se de que as referências absolutas sejam escritas. Se o endereço não tiver um cifrão ao lado dos nomes das colunas e das linhas, você precisará adicioná-los pressionando a tecla F4 até que o sinal $ esteja próximo aos nomes das colunas e das linhas.

Com gravação manual de dados

Na situação acima, a lista foi escrita destacando o intervalo necessário. Este é um método conveniente, mas às vezes é necessário registrar os dados manualmente. Isso permitirá evitar a duplicação de informações na pasta de trabalho.

Suponha que nos deparamos com a tarefa de criar uma lista contendo duas opções possíveis: sim e não. Para realizar a tarefa, é necessário:

1. Clique na célula para a lista.
2. Abra "Dados" e encontre a seção "Verificação de dados" familiar para nós.
   

   

3. Novamente, selecione o tipo “Lista”.
   

   

4. Aqui você precisa digitar “Sim; Não” como fonte. Vemos que as informações são inseridas manualmente usando um ponto e vírgula para enumeração.

Após clicar em OK, temos o seguinte resultado.

Em seguida, o programa criará automaticamente um menu suspenso na célula apropriada. Todas as informações que o usuário especificou como itens na lista pop-up. As regras para criar uma lista em várias células são semelhantes às anteriores, com a única exceção de que você deve especificar as informações manualmente usando um ponto e vírgula.

Criando uma lista suspensa usando a função OFFSET

Além do método clássico, é possível usar a função DISPOSIÇÃOpara gerar menus suspensos.

Vamos abrir a folha.

Para usar a função para a lista suspensa, você precisa fazer o seguinte:

1. Selecione a célula de interesse onde deseja colocar a lista futura.
2. Abra a guia “Dados” e a janela “Validação de dados” em sequência.
   

   

3. Defina “Lista”. Isso é feito da mesma forma que os exemplos anteriores. Por fim, utiliza-se a seguinte fórmula: =DESVIO(A$2$;0;0;5). Entramos nele onde são especificadas as células que serão usadas como argumento.

Em seguida, o programa criará um menu com uma lista de frutas.

A sintaxe para isso é:

=OFFSET(referência,line_offset,column_offset,[altura],[largura])

Vemos que esta função tem 5 argumentos. Primeiro, o primeiro endereço de célula a ser deslocado é fornecido. Os próximos dois argumentos especificam quantas linhas e colunas devem ser deslocadas. Falando em nós, o argumento Altura é 5 porque representa a altura da lista. 

Lista suspensa no Excel com substituição de dados (+ usando a função OFFSET)

No caso dado DISPOSIÇÃO permitido criar um menu pop-up localizado em um intervalo fixo. A desvantagem deste método é que após adicionar o item, você mesmo terá que editar a fórmula.

Para criar uma lista dinâmica com suporte para inserir novas informações, você deve:

1. Selecione a célula de interesse.
2. Expanda a guia "Dados" e clique em "Validação de dados".
3. Na janela que se abre, selecione novamente o item “Lista” e especifique a seguinte fórmula como fonte de dados: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Clique em OK.

Este contém uma função COUNTIF, para determinar imediatamente quantas células são preenchidas (embora tenha um número muito maior de usos, apenas escrevemos aqui para um propósito específico).

Para que a fórmula funcione normalmente, é necessário rastrear se existem células vazias no caminho da fórmula. Eles não deveriam ser.

Lista suspensa com dados de outra planilha ou arquivo do Excel

O método clássico não funciona se você precisar obter informações de outro documento ou mesmo de uma planilha contida no mesmo arquivo. Para isso, utiliza-se a função INDIRETO, que permite inserir no formato correto um link para uma célula localizada em outra planilha ou em geral – um arquivo. Você precisa fazer o seguinte:

1. Ative a célula onde colocamos a lista.
2. Abrimos a janela que já conhecemos. No mesmo local onde anteriormente indicamos fontes para outras faixas, uma fórmula é indicada no formato =INDIRETO(“[Lista1.xlsx]Planilha1!$A$1:$A$9”). Naturalmente, em vez de List1 e Sheet1, você pode inserir os nomes do livro e da planilha, respectivamente. 

Atenção! O nome do arquivo é fornecido entre colchetes. Nesse caso, o Excel não poderá usar o arquivo que está fechado no momento como fonte de informação.

Deve-se notar também que o próprio nome do arquivo só faz sentido se o documento requerido estiver localizado na mesma pasta onde a lista será inserida. Caso contrário, você deve especificar o endereço deste documento na íntegra.

Criando listas suspensas dependentes

Uma lista dependente é aquela cujo conteúdo é afetado pela escolha do usuário em outra lista. Suponha que temos uma tabela aberta à nossa frente que contém três intervalos, cada um com um nome.

Você precisa seguir estas etapas para gerar listas cujo resultado é afetado pela opção selecionada em outra lista.

1. Crie a 1ª lista com nomes de intervalo.
   

   

2. No ponto de entrada da fonte, os indicadores necessários são destacados um a um.
   

   

3. Crie uma segunda lista dependendo do tipo de planta que a pessoa escolheu. Alternativamente, se você especificar árvores na primeira lista, as informações na segunda lista serão “carvalho, carpa, castanheiro” e além. É necessário anotar a fórmula no local de entrada da fonte de dados =INDIRETO(E3). E3 – célula contendo o nome do intervalo 1.=INDIRETO(E3). E3 – célula com o nome da lista 1.

Agora está tudo pronto.

Como selecionar vários valores de uma lista suspensa?

Às vezes não é possível dar preferência a apenas um valor, portanto, mais de um deve ser selecionado. Então você precisa adicionar uma macro ao código da página. Usando a combinação de teclas Alt + F11 abre o Editor do Visual Basic. E o código é inserido lá.

Sub-Planilha Privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Е2:Е9»)) é Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        Se Len (Deslocamento Alvo (0, 1)) = 0 Então

            Alvo.Deslocamento (0, 1) = Alvo

        outro

            Alvo.End (xlToRight) .Offset (0, 1) = Alvo

        Se acabar

        Target.ClearContents

        Application.EnableEvents = True

    Se acabar

End Sub 

Para que o conteúdo das células seja mostrado abaixo, inserimos o seguinte código no editor.

Sub-Planilha Privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) é Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        Se Len (Deslocamento Alvo (1, 0)) = 0 Então

            Alvo.Deslocamento (1, 0) = Alvo

        outro

            Alvo.End (xlDown) .Offset (1, 0) = Alvo

        Se acabar

        Target.ClearContents

        Application.EnableEvents = True

    Se acabar

End Sub

E, finalmente, esse código é usado para escrever em uma célula.

Sub-Planilha Privada_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) é Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        newVal = Alvo

        Aplicativo.Desfazer

        oldval = alvo

        Se Len (oldval) <> 0 E oldval <> newVal Então

            Alvo = Alvo & «,» & newVal

        outro

            Alvo = newVal

        Se acabar

        Se Len (newVal) = 0 Então Target.ClearContents

        Application.EnableEvents = True

    Se acabar

End Sub

Os intervalos são editáveis.

Como fazer uma lista suspensa com uma pesquisa?

Nesse caso, você deve usar inicialmente um tipo diferente de lista. A guia "Desenvolvedor" é aberta, após a qual você precisa clicar ou tocar (se a tela for sensível ao toque) no elemento "Inserir" - "ActiveX". Tem uma caixa de combinação. Você será solicitado a desenhar esta lista, após a qual ela será adicionada ao documento.

Além disso, ele é configurado por meio de propriedades, onde um intervalo é especificado na opção ListFillRange. A célula onde o valor definido pelo usuário é exibido é configurada usando a opção LinkedCell. Em seguida, você só precisa anotar os primeiros caracteres, pois o programa irá sugerir automaticamente os possíveis valores.

Lista suspensa com substituição automática de dados

Há também uma função que os dados são substituídos automaticamente após serem adicionados ao intervalo. É fácil fazer isso:

1. Crie um conjunto de células para a lista futura. No nosso caso, este é um conjunto de cores. Nós o selecionamos.
   

   

2. Em seguida, ele precisa ser formatado como uma tabela. Você precisa clicar no botão com o mesmo nome e selecionar o estilo da tabela.
   

   

   

Em seguida, você precisa confirmar este intervalo pressionando o botão “OK”.

Selecionamos a tabela resultante e damos um nome a ela através do campo de entrada localizado no topo da coluna A.

É isso aí, existe uma tabela e ela pode ser usada como base para uma lista suspensa, para a qual você precisa:

1. Selecione a célula onde a lista está localizada.
2. Abra a caixa de diálogo Validação de dados.
   

   

3. Definimos o tipo de dados como “Lista”, e como valores damos o nome da tabela através do sinal =.
   

   

   

Tudo, a célula está pronta, e os nomes das cores são mostrados nela, como precisávamos originalmente. Agora você pode adicionar novas posições simplesmente escrevendo-as em uma célula localizada um pouco abaixo imediatamente após a última.

Esta é a vantagem da tabela, que o intervalo aumenta automaticamente quando novos dados são adicionados. Assim, esta é a maneira mais conveniente de adicionar uma lista.

Como copiar a lista suspensa?

Para copiar, basta usar a combinação de teclas Ctrl + C e Ctrl + V. Assim, a lista suspensa será copiada junto com a formatação. Para remover a formatação, você precisa usar uma pasta especial (no menu de contexto, esta opção aparece após copiar a lista), onde a opção “condições nos valores” é definida.

Selecione todas as células que contêm uma lista suspensa

Para realizar esta tarefa, você deve usar a função “Selecionar um grupo de células” no grupo “Localizar e selecionar”.

Após isso, uma caixa de diálogo será aberta, onde você deverá selecionar os itens “Todos” e “Estes mesmos” no menu “Validação de dados”. O primeiro item seleciona todas as listas e o segundo seleciona apenas aquelas que são semelhantes a determinadas.