Faça uma lista suspensa de dependentes dinâmicos no Excel (passo a passo)
Aqui neste tutorial, apresentaremos passo a passo como criar uma lista suspensa dependente que exibe opções dependendo dos valores selecionados na primeira lista suspensa. Ou seja, faremos uma lista de validação de dados do Excel com base no valor de outra lista.
Faça uma lista suspensa dependente dinâmica
10s para fazer uma lista suspensa dependente com uma ferramenta útil
Faça uma lista suspensa dependente dinâmica no Excel 2021 ou no Excel 365
Algumas perguntas que você pode fazer sobre este tutorial
Faça o download gratuito do arquivo de amostra
Vídeo: Criar uma lista suspensa dependente do Excel
Faça uma lista suspensa dependente dinâmica
Etapa 1: digite as entradas para as listas suspensas
1. Primeiro, digite as entradas que deseja que apareçam nas listas suspensas, cada lista em uma coluna separadamente.
Perceber que os itens na primeira coluna (Produto) serão como nomes do Excel para as listas de dependentes posteriormente. Por exemplo, aqui Frutas e Legumes serão os Nomes da coluna B2:B5 e C2:C6 separadamente.
Veja a imagem:
2. Em seguida, crie tabelas para cada lista de dados.
Selecione o intervalo de colunas A1:A3, clique em inserção > mesa, em seguida, na caixa de diálogo Criar tabela, marque Minha tabela tem cabeçalhos caixa de seleção. Clique OK.
Em seguida, repita esta etapa para criar tabelas para as outras duas listas.
Você pode visualizar todas as tabelas e a referência aos intervalos no Gerenciador de Nomes (pressione Ctrl + F3 para abri-lo).
Etapa 2: criar nomes de intervalo
Nesta etapa, você precisa criar Names para a lista principal e cada lista dependente.
1. Selecione os itens que aparecem na lista principal (A2: A3).
2. Em seguida, vá para o Caixa de nome qual ao lado Barra de Fórmula.
3. Digite o nome nele, aqui o nomeia como Produto.
4. Pressione Entrar chave para concluir.
Em seguida, repita as etapas acima para criar Nomes separadamente para cada lista dependente.
Aqui nomeia a segunda coluna (B2:B5) como Fruta e a terceira coluna (C2:C6) como Vegetal.
Você pode visualizar todos os nomes de intervalos no Gerenciador de Nomes (pressione Ctrl + F3 para abri-lo).
Etapa 3: adicionar a lista suspensa principal
Em seguida, adicione a lista suspensa principal (Produto), que é uma lista suspensa de validação de dados normal, não uma lista suspensa dependente.
1. Em primeiro lugar, crie uma tabela.
Selecione uma célula (E1) e digite o cabeçalho da primeira coluna (Produto) e vá para a próxima célula da coluna (F1), digite o cabeçalho da segunda coluna (item). Você adicionará a lista suspensa a esta tabela.
Em seguida, selecione esses dois cabeçalhos (E1 e F1), clique em inserção guia, e selecione mesa no grupo Tabelas.
Na caixa de diálogo Criar tabela, marque Minha tabela tem cabeçalhos caixa e clique OK.
2. Selecione a célula E2 ao qual você deseja inserir a lista suspensa principal, clique em Data guia e vá para Ferramentas de dados grupo para clicar Validação de dados > Validação de dados.
3. Na caixa de diálogo Validação de dados,
- Escolha Lista no Permitir seção,
- Digite a fórmula abaixo em fonte barra, Produto é Nome da lista principal,
- Clique OK.
=Product
Você pode ver que a lista suspensa principal foi criada.
Etapa 4: adicionar lista suspensa dependente
1. Selecione a célula F2 ao qual você deseja adicionar a lista suspensa de dependentes, clique em Data guia e vá para o grupo Ferramentas de dados para clicar Validação de dados > Validação de dados.
2. Na caixa de diálogo Validação de dados,
- Escolha Lista no Permitir seção,
- Digite a fórmula abaixo em fonte barra, E2 é a célula que contém a lista suspensa principal.
- Clique OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Se o E2 estiver vazio (você não selecionar nenhum item na lista suspensa principal), você verá uma mensagem como abaixo, clique em Sim para continuar.
Agora a lista suspensa de dependentes foi criada.
Etapa 5: Testar a lista suspensa dependente.
1. Selecionar Fruta na lista suspensa principal (E2) e vá para a lista suspensa de dependentes (F2) para clicar no ícone de seta, veja se os itens de frutas estão na lista e selecione um item na lista suspensa dependente.
2. Pressione Aba tecla para iniciar uma nova linha na tabela de entrada de dados, selecione Vegetal, e vá para a próxima célula à direita, veja se os itens vegetais estão na lista e selecione um item na lista suspensa dependente.
- Se não houver nenhum item selecionado na lista suspensa principal (coluna Produto), a lista suspensa dependente (coluna Item) não funcionará.
- Se você deseja redefinir ou limpar o conteúdo da lista suspensa de dependentes após selecionar alterado, vá para este artigo Como limpar a célula da lista suspensa dependente após selecionar as alterações no Excel?, apresenta um código VBA para ajudá-lo.
- Se você deseja criar uma lista suspensa de 3 níveis, este artigo Como criar uma lista suspensa dependente de vários níveis no Excel? Ajudará você.
10s para fazer uma lista suspensa dependente com uma ferramenta útil
Kutools for Excel fornece uma ferramenta poderosa para tornar uma lista suspensa dependente mais fácil e rápida, vejamos:
Antes de seguir os passos abaixo, por favor clique para baixar o Kutools for Excel para teste gratuito de 30 dias primeiramente.
Etapa 1: digite as entradas da lista suspensa
Em primeiro lugar, organize seus dados conforme a captura de tela abaixo:
Etapa 2: aplicando a ferramenta Kutools
1. Selecione os dados que você criou, clique em Kutools guia e clique em Lista suspensa para exibir o submenu, clique em Lista suspensa dinâmica.
2. Na lista suspensa de dependentes
- Verifique o Modo B que corresponda ao seu modo de dados,
- Selecione os faixa de saída, a coluna do intervalo de saída deve ser igual à coluna do intervalo de dados,
- Clique Ok.
Agora a lista suspensa dependente foi criada.
- O Modo B suporta a criação de uma lista suspensa de terceiro ou mais níveis:
- Se seus dados estiverem organizados como mostra a captura de tela abaixo, você precisará usar o Modo A, o Modo A suporta apenas a criação de uma lista suspensa dependente de 2 níveis.
- Mais detalhes sobre como usar o Kutools para criar uma lista suspensa dependente, visite Neste tutorial .
Faça uma lista suspensa dependente dinâmica no Excel 2021 ou no Excel 365
Se você estiver no Excel 2021 ou no Excel 365, existe outra maneira de criar rapidamente uma lista suspensa dependente dinâmica usando novas funções UNIQUE e FILTRO.
Supondo que seus dados de origem estejam organizados conforme a captura de tela mostrada, siga as etapas abaixo para criar a lista suspensa dinâmica.
Etapa 1: usando a fórmula para obter itens para a lista suspensa principal
Selecione uma célula, por exemplo, célula G3, e usando as funções ÚNICO e FILTRO para extrair os valores únicos da Produto lista que será a fonte da lista suspensa principal e pressione Entrar chave.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Etapa 2: criar a lista suspensa principal
1. Selecione uma célula que deseja colocar na lista suspensa principal, por exemplo, célula D3, clique em Data guia e vá para Ferramentas de dados grupo para clicar Validação de dados > Validação de dados.
2. Na caixa de diálogo Validação de dados,
- Escolha Lista no Permitir seção,
- Digite a fórmula abaixo em fonte Bar,
- Clique OK.
=$G$3#
Agora a lista suspensa principal é criada.
Etapa 3: usando a fórmula para obter itens para a lista suspensa dependente
Selecione uma célula, por exemplo, célula H3, usando a função FILTER para filtrar os itens com base no valor na célula D3 (o item selecionado na lista suspensa principal), pressione Entrar chave.
=FILTER(B3:B20, A3:A20=D3)
Etapa 4: criar a lista suspensa dependente
1. Selecione uma célula que colocará a lista suspensa dependente, por exemplo, célula E3, clique em Data guia e vá para Ferramentas de dados grupo para clicar Validação de dados > Validação de dados.
2. Na caixa de diálogo Validação de dados,
- Escolha Lista no Permitir seção,
- Digite a fórmula abaixo em fonte Bar,
- Clique OK.
=$H$3#
Agora a lista suspensa dependente foi criada com sucesso.
Ao adicionar novos itens ou fazer algumas alterações em A3:A20, a lista suspensa será atualizada automaticamente.
Classificar lista suspensa em ordem alfabética
Se você deseja organizar os itens na lista suspensa em ordem alfabética, pode usar a fórmula abaixo para a tabela de preparação.Para o menu suspenso principal (a fórmula na célula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Para o menu suspenso dependente (a fórmula na célula H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Agora, ambas as listas suspensas são classificadas alfabeticamente de A a Z.
Para obter a classificação alfabética de Z a A, use a fórmula abaixo:
Para o menu suspenso principal (a fórmula na célula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Para o menu suspenso dependente (a fórmula na célula H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Algumas perguntas que você pode fazer:
1. Por que inserir uma tabela para cada lista de dados?
Inserir uma tabela para a lista de dados ajudará você a atualizar automaticamente a lista suspensa com base nas alterações na lista de dados. Por exemplo, adicionando 'Outros' na primeira lista de dados, a lista suspensa principal será adicionada automaticamente com 'Outros'.
2. Por que usar uma tabela para colocar listas suspensas?
Quando você pressiona a tecla Tab para adicionar uma nova linha à tabela, as listas suspensas também serão adicionadas automaticamente na nova linha.
3. Como funciona a função INDIRETA?
INDIRETO A função é usada para converter uma string de texto em uma referência válida.
4. Como funciona a fórmula INDIRETO(SUBSTITUIR(E2&F2," ",""))?
Em primeiro lugar, SUBSTITUTO A função substitui o texto por outro texto. Aqui costumava retirar os espaços dos nomes combinados (E2 e F2). Então INDIRETO A função converte a string de texto (o conteúdo combinado por E2 e F2) em uma referência válida.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Índice
- Vídeo: Criar uma lista suspensa dependente do Excel
- Faça uma lista suspensa dependente dinâmica
- 10s para fazer uma lista suspensa dependente
- Faça uma lista suspensa dependente dinâmica no Excel 2021/365
- Perguntas frequentes
- Artigos Relacionados
- As melhores ferramentas de produtividade para escritório
- Comentários