Ir para o conteúdo principal

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

lista suspensa dependente do documento 1 1 1

Faça o download gratuito do arquivo de amostra amostra de doc


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:

lista suspensa dependente do documento 1 2

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.

lista suspensa dependente do documento 1 3

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).

lista suspensa dependente do documento 1 4

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.

lista suspensa dependente do documento 1 5

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.

lista suspensa dependente do documento 1 15

lista suspensa dependente do documento 1 6

Você pode visualizar todos os nomes de intervalos no Gerenciador de Nomes (pressione Ctrl + F3 para abri-lo).

lista suspensa dependente do documento 1 7

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.

lista suspensa dependente do documento 1 8

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.

lista suspensa dependente do documento 1 9

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

lista suspensa dependente do documento 1 10

Você pode ver que a lista suspensa principal foi criada.

lista suspensa dependente do documento 1 11

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," ","_"))

lista suspensa dependente do documento 1 12

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.

lista suspensa dependente do documento 1 13

Agora a lista suspensa de dependentes foi criada.

lista suspensa dependente do documento 1 14

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.

1 gif

Notas:

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:

kte gif 1

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:

doc kutools lista suspensa dinâmica 1

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.

doc kutools lista suspensa dinâmica 2

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.

doc kutools lista suspensa dinâmica 3

Agora a lista suspensa dependente foi criada.

doc kutools lista suspensa dinâmica 4

Tips:
  • O Modo B suporta a criação de uma lista suspensa de terceiro ou mais níveis:
    lista suspensa dinâmica doc kutools 5 1
  • 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.
    doc kutools lista suspensa dinâmica 6
  • Mais detalhes sobre como usar o Kutools para criar uma lista suspensa dependente, visite Neste tutorial .

Kutools for Excel

Teste gratuito de 30 dias com recursos completos, sem necessidade de cartão de crédito.

Mais de 300 poderosos recursos e funções avançadas para Excel.

Não precisa de nenhuma habilidade especial, economizando horas todos os dias.

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<>""))
Note: Com os produtos em A3:A12, adicionamos 8 células extras ao array para atender possíveis novas entradas. Além disso, incorporamos a função FILTER em UNIQUE para extrair valores exclusivos sem espaços em branco.

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#
Note: isso é chamado de referência de intervalo de derramamento e essa sintaxe se refere a todo o intervalo, independentemente de quanto ele se expande ou contrai.

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)
Note: Se houver um espaço em branco na lista suspensa principal, a fórmula retornará a zeros.

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#
Note: isso é chamado de referência de intervalo de derramamento e essa sintaxe se refere a todo o intervalo, independentemente de quanto ele se expande ou contrai.

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.

Tips:

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.

lista suspensa dependente do documento 365 8

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'.

atualização da lista suspensa dependente do documento

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

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

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...

Descrição


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations