Criar uma Lista Suspensa Dependente Dinâmica no Excel (Passo a Passo)
Neste tutorial, vamos apresentar passo a passo como criar uma lista suspensa dependente que exibe opções com base nos valores selecionados na primeira lista suspensa. Em outras palavras, criaremos uma lista de validação de dados no Excel com base no valor de outra lista.
Criar uma lista suspensa dependente dinâmica
Crie uma lista suspensa dependente em 10 segundos com uma ferramenta prática
Criar uma lista suspensa dependente dinâmica no Excel 2021, Excel 365 e versões mais recentes
Algumas perguntas que você pode ter sobre este tutorial
Baixe gratuitamente o arquivo de exemplo 
Vídeo: Criar uma lista suspensa dependente no Excel
Criar uma lista suspensa dependente dinâmica
Passo 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 separada.
Observe que os itens na primeira coluna (Produto) servirão como nomes do Excel para as listas dependentes posteriormente. Por exemplo, aqui Fruta e Vegetal serão os Nomes para as colunas B2:B5 e C2:C6 separadamente.
Veja a captura de tela:
2. Em seguida, crie tabelas para cada lista de dados.
Selecione o intervalo da coluna A1:A3, clique em "Inserir" > "Tabela", depois na caixa de diálogo Criar Tabela, marque a caixa de seleção "Minha tabela tem cabeçalhos". Clique em "OK".
Em seguida, repita este passo 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).
Passo 2: Criar Nomes de Intervalo
Nesta etapa, você precisa criar "Nomes" para a lista principal e cada lista dependente.
1. Selecione os itens que aparecem na lista principal ("A2:A3").
2. Depois, vá para a "Caixa de Nome" ao lado da "Barra de Fórmulas".
3. Digite o nome nela, aqui o nome será "Produto".
4. Pressione a tecla "Enter" para concluir.
Depois, 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 intervalo no Gerenciador de Nomes (pressione "Ctrl" + "F3" para abri-lo).
Passo 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. Primeiro, 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"). Esta tabela conterá as listas suspensas.
Depois, selecione esses dois cabeçalhos ("E1" e "F1"), clique na guia "Inserir" e selecione "Tabela" no grupo Tabelas.
Na caixa de diálogo Criar Tabela, marque a caixa "Minha tabela tem cabeçalhos" e clique em "OK".
2. Selecione a célula "E2" onde você deseja inserir a lista suspensa principal, clique na guia "Dados" e vá para o grupo Ferramentas de Dados para clicar em "Validação de Dados" > "Validação de Dados".
3. Na caixa de diálogo Validar Dados,
- Escolha "Lista" na seção "Permitir",
- Digite a fórmula abaixo na barra "Fonte", Produto é o Nome da lista principal,
- Clique em "OK".
=Product
Você pode ver que a lista suspensa principal foi criada.
Passo 4: Adicionar Lista Suspensa Dependente
1. Selecione a célula "F2" onde você deseja adicionar a lista suspensa dependente, clique na guia "Dados", e vá para o grupo Ferramentas de Dados para clicar em "Validação de Dados" > "Validação de Dados".
2. Na caixa de diálogo Validar Dados,
- Escolha "Lista" na seção "Permitir",
- Digite a fórmula abaixo na barra "Fonte", E2 é a célula que contém a lista suspensa principal.
- Clique em "OK".
=INDIRECT(SUBSTITUTE(E2," ","_"))
Se o E2 estiver vazio (você não selecionou nenhum item na lista suspensa principal), você verá uma mensagem pop-up como abaixo, clique em "Sim" para continuar.
Agora a lista suspensa dependente foi criada.
Passo 5: Testar a Lista Suspensa Dependente.
1. Selecione "Fruta" na lista suspensa principal ("E2"), depois vá para a lista suspensa dependente ("F2") para clicar no ícone de seta, veja se os itens de frutas estão na lista, então selecione um item da lista suspensa dependente.
2. Pressione a tecla "Tab" 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 de vegetais estão na lista, então selecione um item da lista suspensa dependente.
- Se nenhum item for selecionado na lista suspensa principal (coluna Produto), a lista suspensa dependente (coluna Item) não funcionará.
- Se você quiser redefinir ou limpar o conteúdo da lista suspensa dependente após alterar a seleção, por favor, visite este artigo Como limpar a célula da lista suspensa dependente após a seleção ser alterada no Excel?, ele apresenta um código VBA para ajudá-lo.
- Se você quiser criar uma lista suspensa de 3 níveis, este artigo irá ajudá-lo: Como criar uma lista suspensa dependente de vários níveis no Excel?.
Crie uma lista suspensa dependente em 10 segundos com uma ferramenta prática
O "Kutools for Excel" oferece uma ferramenta poderosa para criar uma lista suspensa dependente de forma mais fácil e rápida:
Passo 1: Digite as Entradas Para a Lista Suspensa
Primeiro, organize seus dados conforme mostrado na captura de tela abaixo:
Passo 2: Aplicando a ferramenta Kutools
1. Selecione os dados que você criou, clique na guia "Kutools", e clique em "Lista suspensa" para exibir o submenu, clique em "Lista Suspensa Dinâmica".
2. Na "Lista Suspensa Dependente":
- Marque o "Modo B" que corresponde ao seu modo de dados,
- Selecione o "intervalo de saída", a coluna do intervalo de saída deve ser igual à coluna do intervalo de dados,
- Clique em "Ok".
Agora a lista suspensa dependente foi criada.
- O "Modo B" suporta a criação de um terceiro nível ou mais em uma lista suspensa:
- Se seus dados estão organizados conforme mostrado na captura de tela abaixo, você precisa usar o "Modo A", que só suporta a criação de uma lista suspensa dependente de 2 níveis.
- Para mais detalhes sobre como usar o Kutools para criar uma lista suspensa dependente, por favor, visite este tutorial.
Criar uma lista suspensa dependente dinâmica no Excel 2021, Excel 365 e versões mais recentes
Se você está usando o Excel 365, Excel 2021 ou versões mais recentes, há outra maneira de criar rapidamente uma lista suspensa dependente dinâmica usando as novas funções "ÚNICO" e "FILTRO".
Suponha que seus dados de origem estão organizados conforme mostrado na captura de tela, siga as etapas abaixo para criar a lista suspensa dinâmica.
Passo 1: Usando fórmula para obter itens para a lista suspensa principal
Selecione uma célula, por exemplo, célula G3, e use as funções ÚNICO e FILTRO para extrair os valores únicos da lista "Produto" que será a fonte da lista suspensa principal, e pressione a tecla "Enter".
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Passo 2: Criar a lista suspensa principal
1. Selecione uma célula onde você deseja colocar a lista suspensa principal, por exemplo, célula "D3", clique na guia "Dados", e vá para o grupo Ferramentas de Dados para clicar em "Validação de Dados" > "Validação de Dados".
2. Na caixa de diálogo "Validação de Dados",
- Escolha "Lista" na seção "Permitir",
- Digite a fórmula abaixo na barra "Fonte",
- Clique em "OK".
=$G$3#
Agora a lista suspensa principal foi criada.
Passo 3: Usando fórmula para obter itens para a lista suspensa dependente
Selecione uma célula, por exemplo, célula H3, usando a função FILTRO para filtrar os itens com base no valor na célula "D3" (o item selecionado na lista suspensa principal), pressione a tecla "Enter".
=FILTER(B3:B20, A3:A20=D3)
Passo 4: Criar a lista suspensa dependente
1. Selecione uma célula que colocará a lista suspensa dependente, por exemplo, célula "E3", clique na guia "Dados", e vá para o grupo Ferramentas de Dados para clicar em "Validação de Dados" > "Validação de Dados".
2. Na caixa de diálogo "Validação de Dados",
- Escolha "Lista" na seção "Permitir",
- Digite a fórmula abaixo na barra "Fonte",
- Clique em "OK".
=$H$3#
Agora a lista suspensa dependente foi criada com sucesso.
Quando você adicionar novos itens ou fizer algumas alterações em A3:A20, as listas suspensas serão atualizadas automaticamente.
Classificar lista suspensa em ordem alfabética
Se você quiser organizar os itens na lista suspensa em ordem alfabética, pode usar a fórmula abaixo na tabela de preparação.Para a lista suspensa principal (a fórmula na célula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Para a lista suspensa dependente (a fórmula na célula H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Agora ambas as listas suspensas estão classificadas em ordem alfabética de A a Z.
Para classificar em ordem alfabética de Z a A, use a fórmula abaixo:
Para a lista suspensa principal (a fórmula na célula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Para a lista suspensa 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á automaticamente adicionada 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 INDIRETO?
A função INDIRETO é usada para converter uma string de texto em uma referência válida.
4. Como funciona a fórmula INDIRETO(SUBSTITUIR(E2&F2," ",""))?
Primeiro, a função SUBSTITUIR substitui o texto por outro texto. Aqui ela é usada para remover os espaços dos nomes combinados (E2 e F2). Depois, a função INDIRETO converte a string de texto (os conteúdos combinados por E2 e F2) em uma referência válida.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência sem igual. Kutools para Excel oferece mais de300 recursos avançados para aumentar sua produtividade e economizar tempo.Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface de abas para o Office e facilita muito seu trabalho
- Habilite a edição e leitura com abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas abas da mesma janela, em vez de novas janelas.
- Aumente sua produtividade em50% e reduza centenas de cliques do mouse todos os dias!
Índice
- Vídeo: Criar uma lista suspensa dependente no Excel
- Criar uma lista suspensa dependente dinâmica
- 10s para criar uma lista suspensa dependente
- Criar uma lista suspensa dependente dinâmica no Excel 365/2021/Nova
- Perguntas Frequentes
- Artigos Relacionados
- As Melhores Ferramentas de Produtividade para o Office
- Comentários