Neste artigo, aprenderemos como criar uma lista suspensa dinâmica no Microsoft Excel.
Como sabemos, o recurso de validação de dados melhora a eficiência da entrada de dados no Excel e reduz erros e erros de digitação. É usado para restringir o usuário quanto ao tipo de dados que podem ser inseridos no intervalo. No caso de qualquer entrada inválida, mostra uma mensagem e permite ao usuário inserir os dados com base na condição especificada.
Mas uma lista suspensa dinâmica no Excel é uma maneira mais conveniente de selecionar dados, sem fazer alterações na fonte. Em outras palavras, digamos que você atualize a lista frequentemente que você obteve na lista suspensa. E você está pensando que, se fizer alguma alteração na lista, precisará modificar a validação de dados todas as vezes para obter a lista suspensa atualizada.
Mas, é aqui que o drop down dinâmico entra em cena, e é a melhor opção para selecionar dados sem fazer nenhuma alteração na validação de dados. É muito semelhante à validação de dados normal. No entanto, quando você atualiza a lista, a lista suspensa dinâmica muda para acomodar essa ação, enquanto a lista suspensa normal não.
Então, vamos dar um exemplo e entender como criamos uma lista suspensa dinâmica: -
Temos uma lista de produtos na coluna A e teremos a lista suspensa dinâmica de produtos na célula D9.
Nome da tabela com função indireta
Primeiro, vamos criar uma tabela; siga as etapas fornecidas abaixo:-
- Selecione o intervalo A8: A16
- Vá para Inserir Aba e clique em Tabela
- Depois de clicar na opção “Mesa”, uma janela de Mesa aparece
- Em seguida, selecione o intervalo para o qual queremos inserir a tabela A8: A17
- Clique em OK
- Agora, clicamos em OK
- Você pode ver que este intervalo foi convertido em tabela, e o cabeçalho desta tabela também tem a opção de filtro suspenso
Observação: - Se adicionarmos algum produto ou item ao final da lista, a tabela se expandirá automaticamente para incorporar os novos produtos ou itens.
Agora criamos a lista suspensa dinâmica na célula D9, siga as etapas abaixo:-
- Selecione a célula D9
- Abra a caixa de diálogo de Validação de Dados, pressionando a tecla ALT + D + L
- Na lista suspensa Permitir, selecione Lista
- E então insira esta função = INDIRETO (“Tabela1”) na guia de fonte
- Clique em OK
Observação: - Ao clicar em OK, no Excel, aparece uma janela informando que há algo errado com a entrada. Isso porque o Excel não aceita nenhuma tabela autoexpansível diretamente na Validação de Dados.
Agora adicione novos produtos, na lista de produtos.
Podemos ver na imagem acima que o novo produto adicionado está aparecendo na lista suspensa.
2WL Exemplo:-
Neste exemplo, aprenderemos como dar o nome da tabela como nome de intervalo
Já temos o nome da tabela, mas aqui temos que definir o nome desta tabela para obter a lista suspensa dinâmica; siga os passos abaixo: -
- Selecione a célula D10
- Vá para o intervalo da tabela e, exceto o cabeçalho, selecionamos o intervalo do primeiro ao último produto
- Vá para a caixa de nome e digite o nome curto “tablerange”, pressione Enter
- Depois de pressionar Enter, vemos que nada mudou na caixa de nome
- Clique na opção da lista suspensa para ver todos os intervalos nomeados disponíveis
- Na lista suspensa, podemos ver o nome, que acabamos de definir para esta tabela, também aparece
- Agora, vamos para a Validação de Dados, e em “Fonte”, entramos no “intervalo de tabelas”
Nota: - Se você não se lembrar que nome deu a esse intervalo, você pode pressionar a tecla F3 e uma janela aparecerá para sugerir todos os intervalos nomeados disponíveis.
- Agora vá para a guia “Mensagem de entrada” e, no título, digitamos “Selecione o produto” e, em seguida, no corpo da mensagem, escrevemos: “Selecione seu produto da lista”
- Agora, vá para a guia “Alerta de erro”, e lá no título, escrevemos “Produto inválido”, e na mensagem de erro, digitamos “Você digitou o produto errado
- Clique em OK
- Célula D10 contendo a mensagem de entrada junto com a lista suspensa
- Agora, quando adicionarmos qualquer produto na lista, ele aparecerá na lista suspensa automaticamente
Mas o que acontece quando pulamos uma célula após a última célula e adicionamos um novo produto ou item? Você pode ver, desta vez, o intervalo da tabela não foi expandido e, de fato, o produto recém-adicionado está em um formato geral. Então, será mostrado na lista suspensa ou não? Para verificar isso, quando vamos para a célula D10 e verificamos a lista suspensa, podemos ver a mesma lista suspensa antiga sem nenhum produto novo. É porque o intervalo da tabela não encontrou nada após a última célula e, portanto, o intervalo não foi expandido.
3rd Exemplo:-
Nos próximos dois métodos, aprenderemos como podemos tornar nossa lista suspensa mais dinâmica usando as funções OFFSET e COUNTA.
Siga as etapas abaixo:-
- Selecione a célula D11 e pressione ALT + D + L
- A caixa de diálogo de validação de dados abrirá
- Agora selecione a lista na opção “Permitir”
- Em seguida, na opção Fonte, insira a fórmula abaixo: -
= OFFSET ($ A $ 9,0,0, CONT.valores ($ A: $ A), 1)
Explicação da Fórmula: - Selecionamos A9, que é o primeiro produto da linha, e digitamos 0 no 2WL argumento, pois não queremos mover a linha do ponto de partida; então novamente 0 no 3rd argumento, pois aqui não queremos nenhuma mudança no número de colunas, bem como do ponto de partida. E então inserimos a função CONT.valores e selecionamos a coluna A. Este argumento verificará a altura em número de linhas para retornar a contagem não em branco. Ele irá expandir o intervalo quando quaisquer alterações forem feitas no intervalo.
E, o último argumento “Width” é um argumento opcional. É a largura em número de colunas. Podemos pular ou digitar 1 aqui por enquanto. Se pularmos, ele irá, por padrão, considerar a largura do intervalo retornado que fornecemos no argumento e, em seguida, fecharemos os parênteses.
- Após clicar em OK, podemos ver uma lista suspensa na célula D11
- Ele mostra a lista incluindo os produtos em branco e, em seguida, os produtos que adicionamos
4º Exemplo:-
Neste exemplo, usaremos a função para definir o nome.
Para definir o nome do intervalo, siga as etapas abaixo: -
- Pressione CTRL + F3, a caixa de diálogo do Gerenciador de Nomes aparecerá
- Clique em Novo
- Defina o nome do intervalo “ProdName” e insira a fórmula abaixo: -
= OFFSET ('Lista suspensa dinâmica com DV'! $ A $ 9,0,0, COUNTA ('Lista suspensa dinâmica com DV'! $ A: $ A))
- Clique em OK
- Abra a caixa de diálogo de validação de dados pressionando a tecla Alt + D + L
- Selecione a lista na lista suspensa Permitir
- Insira = ProdName na guia Source
- Clique em OK
- Agora, se adicionarmos algo na lista, o mesmo aparecerá na lista
Então, é assim que você pode obter a lista dinâmica de qualquer produto ou item com diferentes métodos de validação de dados. É tudo por agora. No próximo vídeo desta série, explicaremos como criar a lista suspensa dependente com diferentes métodos no Excel.
Clique no link do vídeo para obter uma referência rápida sobre como usá-lo. Inscreva-se em nosso novo canal e continue aprendendo conosco!
Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook.
Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail