✅ Criando uma lista suspensa de dependentes no Excel (tutorial passo a passo)

Índice

Criação de uma lista suspensa de dependentes no Excel

Assistir ao vídeo - Criando uma lista suspensa de dependentes no Excel

Uma lista suspensa do Excel é um recurso útil ao criar formulários de entrada de dados ou painéis do Excel.

Ele mostra uma lista de itens como um menu suspenso em uma célula, e o usuário pode fazer uma seleção nesse menu. Isso pode ser útil quando você tem uma lista de nomes, produtos ou regiões que geralmente precisa inserir em um conjunto de células.

Abaixo está um exemplo de uma lista suspensa do Excel:

No exemplo acima, usei os itens em A2: A6 para criar um menu suspenso em C3.

Ler: Aqui está um guia detalhado sobre como criar uma Lista suspensa do Excel.

Às vezes, no entanto, você pode querer usar mais de uma lista suspensa no Excel de forma que os itens disponíveis em uma segunda lista suspensa dependam da seleção feita na primeira lista suspensa.

Elas são chamadas de listas suspensas dependentes no Excel.

Abaixo está um exemplo do que quero dizer com uma lista suspensa dependente no Excel:

Você pode ver que as opções no menu suspenso 2 dependem da seleção feita no menu suspenso 1. Se eu selecionar 'Frutas' no menu suspenso 1, os nomes das frutas serão exibidos, mas se eu selecionar vegetais no menu suspenso 1, então eu Os nomes dos vegetais são mostrados no menu suspenso 2.

Isso é chamado de lista suspensa condicional ou dependente no Excel.

Criação de uma lista suspensa de dependentes no Excel

Aqui estão as etapas para criar uma lista suspensa dependente no Excel:

Selecione a célula onde deseja a primeira lista suspensa (principal).
Vá para Dados -> Validação de dados. Isso abrirá a caixa de diálogo de validação de dados.
Na caixa de diálogo de validação de dados, na guia de configurações, selecione Lista.
No campo Origem, especifique o intervalo que contém os itens que devem ser mostrados na primeira lista suspensa.
Clique OK. Isso criará o menu suspenso 1.
Selecione todo o conjunto de dados (A1: B6 neste exemplo).
Vá para Fórmulas -> Nomes definidos -> Criar a partir da seleção (ou você pode usar o atalho de teclado Control + Shift + F3).
Na caixa de diálogo ‘Criar nome a partir da seleção’, marque a opção Linha superior e desmarque todas as outras. Isso cria 2 intervalos de nomes ('Frutas' e 'Vegetais'). Frutas com nome de intervalo referem-se a todas as frutas da lista e Vegetais com nome de intervalo refere-se a todos os vegetais da lista.
Clique OK.
Selecione a célula onde deseja a lista suspensa Dependente / Condicional (E3 neste exemplo).
Vá para Dados -> Validação de dados.
Na caixa de diálogo Validação de dados, na guia de configuração, certifique-se de que Listar está selecionado.
No campo Fonte, insira a fórmula = INDIRETO (D3). Aqui, D3 é a célula que contém o menu suspenso principal.
Clique OK.

Agora, quando você faz a seleção na Lista suspensa 1, as opções listadas na Lista suspensa 2 são atualizadas automaticamente.

Baixe o arquivo de exemplo

Como é que isso funciona? - A lista suspensa condicional (na célula E3) refere-se a = INDIRETO (D3). Isso significa que quando você seleciona 'Frutas' na célula D3, a lista suspensa no E3 se refere ao intervalo nomeado 'Frutas' (por meio da função INDIRETA) e, portanto, lista todos os itens nessa categoria.

Nota importante: Se a categoria principal tiver mais de uma palavra (por exemplo, 'Frutas da estação' em vez de 'Frutas'), você precisará usar a fórmula = INDIRETO (SUBSTITUTAR (D3, ”“, ”_”)), em vez de função INDIRETA simples mostrada acima.

A razão para isso é que o Excel não permite espaços em intervalos nomeados. Portanto, quando você cria um intervalo nomeado usando mais de uma palavra, o Excel insere automaticamente um sublinhado entre as palavras. Por exemplo, quando você cria um intervalo nomeado com ‘Frutas da estação’, ele será nomeado Temporada_Frutas no back-end. Usar a função SUBSTITUTE dentro da função INDIRECT garante que os espaços está convertido em sublinhados.

Redefinir / limpar conteúdo da lista suspensa dependente automaticamente

Depois de fazer a seleção e alterar a lista suspensa pai, a lista suspensa dependente não mudaria e, portanto, seria uma entrada errada.

Por exemplo, se você selecionar ‘Frutas’ como a categoria e, em seguida, selecionar Maçã como o item e, em seguida, voltar e alterar a categoria para ‘Vegetais’, a lista suspensa dependente continuará a mostrar Maçã como o item.

Você pode usar o VBA para garantir que o conteúdo da lista suspensa dependente seja redefinido sempre que a lista suspensa principal for alterada.

Aqui está o código VBA para limpar o conteúdo de uma lista suspensa dependente:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

O crédito por este código vai para este tutorial de Debra sobre como limpar listas suspensas dependentes no Excel quando a seleção é alterada.

Veja como fazer esse código funcionar:

Copie o código VBA.
Na pasta de trabalho do Excel onde você tem a lista suspensa dependente, vá para a guia Desenvolvedor e, dentro do grupo ‘Código’, clique em Visual Basic (você também pode usar o atalho de teclado - ALT + F11).
Na janela do Editor VB, à esquerda no explorador de projetos, você veria todos os nomes das planilhas. Clique duas vezes naquele que contém a lista suspensa.
Cole o código na janela de código à direita.
Feche o Editor VB.

Agora, sempre que você alterar a lista suspensa principal, o código VBA será disparado e limpará o conteúdo da lista suspensa dependente (conforme mostrado abaixo).

Se você não é fã de VBA, também pode usar um truque simples de formatação condicional que destacará a célula sempre que houver uma incompatibilidade. Isso pode ajudá-lo a ver e corrigir visualmente a incompatibilidade (conforme mostrado abaixo).

Aqui estão as etapas para destacar incompatibilidades nas listas suspensas dependentes:

Selecione a célula que possui a (s) lista (s) suspensa (s) dependente (s).
Vá para Home -> Formatação Condicional -> Nova Regra.
Na caixa de diálogo Nova regra de formatação, selecione "Usar uma fórmula para determinar quais células formatar".
No campo de fórmula, insira a seguinte fórmula: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
Defina o formato.
Clique OK.

A fórmula usa a função PROCV para verificar se o item na lista suspensa dependente é o da categoria principal ou não. Se não for, a fórmula retornará um erro. Isso é usado pela função ISERROR para retornar TRUE, que informa a formatação condicional para destacar a célula.

Você também pode gostar dos seguintes tutoriais do Excel: