Criar Listas Suspensas Dependentes com Uma Fórmula no Excel

Criar listas suspensas dependentes com uma fórmula no Excel

Neste artigo você aprenderá como criar listas suspensas dependentes no Excel usando uma fórmula em cada campo dependente. Também chamadas de listas em cascata, elas permitem que as opções de uma lista mudem conforme a seleção realizada anteriormente.

Na planilha do exemplo, você seleciona uma empresa, escolhe um departamento dessa empresa e, por último, seleciona uma pessoa vinculada ao departamento. Essa estrutura pode ser aplicada em várias linhas, com seleções independentes em cada uma delas.

O que é uma lista suspensa dependente no Excel?

Uma lista suspensa dependente é uma lista de seleção cujas opções dependem do valor escolhido em outra célula. Por exemplo, ao selecionar uma empresa, a lista seguinte apresenta os departamentos relacionados a ela.

No nosso exemplo, a seleção funciona em três níveis:

• Empresa: permite selecionar uma empresa da base de dados.
• Departamento: apresenta os departamentos da empresa selecionada.
• Nome: apresenta as pessoas da empresa e do departamento selecionados.

Esse recurso é útil em cadastros de funcionários, formulários, controles de pedidos e outras planilhas nas quais uma escolha precisa limitar as opções seguintes.

Como criar listas suspensas dependentes com uma fórmula no Excel

A planilha utiliza a validação de dados para criar as listas suspensas. Nos campos dependentes, uma combinação das funções DESCARTAR, PEGAR e CORRESPX identifica e retorna o trecho da base correspondente às seleções da linha.

Na documentação e em versões em inglês, essas funções aparecem como DROP, TAKE e XMATCH, respectivamente.

Passo 1: organize os dados da lista principal e da lista dependente

Na aba Listas dependentes, a base está organizada nas colunas B, C e D, com os cabeçalhos na linha 3 e os registros entre as linhas 4 e 152.

Coluna	Campo	Intervalo da base
B	Empresa	B4:B152
C	Departamento	C4:C152
D	Nome	D4:D152

A organização da base é essencial: mantenha todos os registros de cada empresa juntos e, dentro de cada empresa, mantenha os registros de cada departamento juntos. Uma forma de fazer isso é classificar toda a base primeiro por Empresa e depois por Departamento.

Selecione as três colunas ao classificar os dados. Classificar apenas uma coluna pode separar os nomes das empresas e dos departamentos correspondentes.

Essa fórmula localiza a primeira e a última ocorrência de uma seleção e retorna tudo o que está entre elas. Por isso, os registros correspondentes precisam formar um bloco contínuo.

Passo 2: crie a primeira lista suspensa com validação de dados

Na área de seleção, os campos estão nas colunas H, I e J:

• H: Empresa.
• I: Departamento.
• J: Nome.

Para criar a lista de empresas:

1. Selecione o intervalo H4:H8.
2. Acesse Dados → Validação de Dados.
3. Na opção Permitir, selecione Lista.
4. No campo Fonte, informe o intervalo abaixo.
5. Clique em OK.

=$B$4:$B$152

Essa é a fonte utilizada na planilha. Como uma empresa pode aparecer em várias linhas da base, a lista de empresas também pode apresentar opções repetidas.

Se desejar uma lista sem repetições, uma melhoria é criar uma relação auxiliar com a função ÚNICO e utilizar o intervalo resultante na validação de dados.

Passo 3: configure a lista de departamentos dependente da empresa

Selecione I4:I8, mantendo I4 como célula ativa. Acesse Dados → Validação de Dados, escolha Lista e insira esta fórmula no campo Fonte:

=DESCARTAR(PEGAR($C$4:$C$152;CORRESPX($H4;$B$4:$B$152;0;-1));CORRESPX($H4;$B$4:$B$152;0)-1)

A fórmula utiliza a empresa selecionada em H4 para encontrar o início e o fim do bloco correspondente na base. Em seguida, retorna os departamentos desse bloco.

Observe que a referência $H4 fixa a coluna H, mas permite que a linha varie. Assim, a lista de I5 considera a empresa de H5, a lista de I6 considera H6 e assim por diante.

Passo 4: configure a lista de nomes dependente da empresa e do departamento

Selecione J4:J8, mantendo J4 como célula ativa. Na validação de dados, escolha Lista e utilize esta fórmula no campo Fonte:

=DESCARTAR(PEGAR($D$4:$D$152;CORRESPX($H4&$I4;$B$4:$B$152&$C$4:$C$152;0;-1));CORRESPX($H4&$I4;$B$4:$B$152&$C$4:$C$152;0)-1)

Nesse caso, a fórmula considera dois campos: a empresa selecionada em H4 e o departamento selecionado em I4. O resultado é a relação de nomes vinculados a essa combinação.

Para testar, selecione uma empresa em H4, escolha um departamento em I4 e abra a lista de nomes em J4.

Importante: ao alterar a empresa, limpe o departamento e o nome anteriormente selecionados. Ao alterar apenas o departamento, limpe o nome. A validação de dados atualiza as opções disponíveis, mas não apaga automaticamente os valores já preenchidos.

Como funciona a fórmula da lista suspensa dependente?

A fórmula trabalha em três etapas: encontra a última ocorrência da seleção, mantém os dados até essa posição e remove as linhas anteriores à primeira ocorrência.

CORRESPX: encontra a primeira e a última ocorrência

Na lista de departamentos, esta parte encontra a primeira ocorrência da empresa selecionada:

=CORRESPX($H4;$B$4:$B$152;0)

O argumento 0 exige uma correspondência exata. O resultado é a posição da empresa dentro do intervalo B4:B152, e não o número da linha da planilha.

Esta outra parte encontra a última ocorrência:

=CORRESPX($H4;$B$4:$B$152;0;-1)

O argumento -1 determina que a pesquisa seja realizada do último registro para o primeiro.

PEGAR: mantém os dados até a última ocorrência

=PEGAR($C$4:$C$152;CORRESPX($H4;$B$4:$B$152;0;-1))

A função PEGAR retorna as primeiras linhas do intervalo de departamentos até a posição da última ocorrência da empresa. Nesse momento, o resultado ainda pode incluir departamentos de empresas anteriores.

DESCARTAR: remove as linhas anteriores ao bloco desejado

A função DESCARTAR remove do início do resultado a quantidade de linhas indicada por:

CORRESPX($H4;$B$4:$B$152;0)-1

Subtrair 1 preserva a primeira linha correspondente à empresa selecionada.

Por exemplo, se uma empresa começa na posição 30 e termina na posição 44, PEGAR mantém as primeiras 44 linhas e DESCARTAR remove as 29 primeiras. Restam as posições 30 a 44, que formam o bloco da empresa.

Como a fórmula combina empresa e departamento

Na lista de nomes, o operador & une os valores de empresa e departamento:

$H4&$I4

A fórmula também une esses campos na base:

$B$4:$B$152&$C$4:$C$152

Assim, CORRESPX procura a combinação dos dois campos, permitindo distinguir departamentos com o mesmo nome em empresas diferentes.

Como melhoria para outras bases, você pode inserir um separador entre os campos, usando $H4&"|"&$I4 na seleção e $B$4:$B$152&"|"&$C$4:$C$152 na base. Isso reduz o risco de combinações diferentes produzirem o mesmo texto concatenado.

Para que serve a fórmula da célula F10?

A célula F10 contém a mesma fórmula utilizada para retornar os departamentos da empresa selecionada em H4:

=DESCARTAR(PEGAR($C$4:$C$152;CORRESPX($H4;$B$4:$B$152;0;-1));CORRESPX($H4;$B$4:$B$152;0)-1)

Nessa célula, o resultado aparece verticalmente, ocupando as células abaixo. Essa área permite visualizar o retorno da fórmula. As validações de I4:I8, porém, utilizam a fórmula diretamente e não fazem referência a F10.

Exemplo prático: listas dependentes de empresa, departamento e funcionário

Na planilha, a seleção de Beta Logística e Recursos Humanos corresponde aos nomes Camila Mendes e Rafaela Pinto.

Empresa selecionada	Departamento selecionado	Nomes disponíveis
Beta Logística	Recursos Humanos	Camila Mendes e Rafaela Pinto
Horizonte Construções	Jurídico	Eduarda Rocha e Rodrigo Moreira
Ômega Energia	Compras	Paulo Araújo e Thiago Rodrigues

A mesma lógica pode ser adaptada para listas de categoria e produto, estado e cidade ou marca e modelo. Basta ajustar as colunas e os critérios utilizados nas fórmulas.

Como criar listas suspensas dependentes em várias linhas

Para cada linha utilizar suas próprias seleções, mantenha os intervalos da base fixos e a linha das células de seleção relativa.

• $B$4:$B$152: fixa o intervalo de empresas.
• $C$4:$C$152: fixa o intervalo de departamentos.
• $D$4:$D$152: fixa o intervalo de nomes.
• $H4: fixa a coluna da empresa e permite variar a linha.
• $I4: fixa a coluna do departamento e permite variar a linha.

Na planilha disponibilizada, as validações estão configuradas de H4 até J8. Para utilizar mais linhas, copie as células com as validações e use Colar Especial → Validação no novo intervalo. Depois, teste as listas de uma das linhas adicionadas.

Como atualizar a lista suspensa dependente ao adicionar novos dados

As fórmulas da planilha utilizam intervalos fixos até a linha 152. Alterações dentro desses intervalos são consideradas pelas listas, mas registros inseridos abaixo deles exigem a atualização das referências.

1. Inclua os novos registros nas colunas Empresa, Departamento e Nome.
2. Classifique toda a base por Empresa e depois por Departamento.
3. Atualize a fonte da lista de empresas.
4. Atualize os intervalos das fórmulas de departamentos e nomes.
5. Atualize também a fórmula de F10, caso utilize essa área de demonstração.

Por exemplo, se a base passar a terminar na linha 200, substitua as referências B4:B152, C4:C152 e D4:D152 pelas referências até a linha 200.

Como melhoria, a base pode ser convertida em uma Tabela do Excel e as fórmulas adaptadas para referências estruturadas. Isso facilita a manutenção quando novos registros são adicionados.

Por que a lista suspensa dependente não funciona?

• Registros fora de ordem: a fórmula retorna o trecho entre a primeira e a última ocorrência. Se os registros estiverem espalhados, poderá incluir opções de outros grupos.
• Seleção vazia ou inexistente: CORRESPX pode retornar um erro quando não encontra a empresa ou a combinação de empresa e departamento.
• Intervalos com tamanhos diferentes: mantenha as colunas da base com o mesmo início e fim nas fórmulas.
• Referências totalmente fixas: utilizar $H$4 em todas as linhas faz com que elas considerem sempre a empresa da linha 4.
• Novos registros fora da fonte: dados abaixo da linha 152 não entram nos intervalos originais.
• Versão sem as funções necessárias: verifique se o Excel reconhece PEGAR, DESCARTAR e CORRESPX.
• Área ocupada abaixo de F10: a fórmula de demonstração precisa de espaço livre para exibir seu resultado.

As opções repetidas não significam necessariamente um erro: as fórmulas originais não removem duplicidades. Um departamento com duas pessoas cadastradas pode aparecer duas vezes na lista de departamentos.

Perguntas frequentes sobre listas suspensas dependentes no Excel

Lista suspensa dependente e lista em cascata são a mesma coisa?

Neste contexto, sim. Os dois termos descrevem listas nas quais uma seleção determina as opções disponíveis no campo seguinte.

É possível criar três listas suspensas dependentes?

Sim. A planilha utiliza três níveis: Empresa, Departamento e Nome. A segunda lista depende da empresa, enquanto a terceira depende da combinação de empresa e departamento.

É necessário utilizar VBA?

Não é necessário utilizar VBA para montar as listas deste exemplo. Elas utilizam fórmulas e validação de dados. A limpeza automática das seleções posteriores, porém, não é realizada pelas fórmulas apresentadas.

A fórmula funciona com dados fora de ordem?

Este método exige que os registros de cada grupo estejam juntos. Para utilizá-lo, organize a base por Empresa e Departamento antes de configurar as listas.

Por que os departamentos aparecem repetidos?

Porque a fórmula retorna os registros da base sem eliminar duplicidades. Se houver várias pessoas no mesmo departamento, esse departamento poderá aparecer mais de uma vez.

Em quais versões do Excel esse método funciona?

As funções PEGAR e DESCARTAR, identificadas como TAKE e DROP na documentação da Microsoft, estão disponíveis no Excel para Microsoft 365 e no Excel 2024. O Excel 2021 não possui essas duas funções, portanto o método apresentado precisa de adaptação nessa versão.

Ao trocar a empresa, o departamento e o nome são apagados?

Não. As listas passam a considerar a nova seleção, mas os valores preenchidos anteriormente permanecem nas células. Limpe os campos seguintes e faça uma nova seleção para manter os dados consistentes.

Baixe grátis a planilha de listas suspensas dependentes no Excel

Baixe a planilha do exemplo para consultar as fórmulas e testar as listas em cascata de Empresa, Departamento e Nome em várias linhas.

Preencha o formulário abaixo para receber o link de download no seu e-mail.

Resumo: como criar listas dependentes no Excel

Organize a base por Empresa e Departamento, crie a lista principal com validação de dados e utilize as fórmulas com DESCARTAR, PEGAR e CORRESPX nos campos dependentes. Mantenha as referências de linha relativas para aplicar as listas em várias linhas e atualize os intervalos quando a base crescer.

Referências: Função TAKE — Microsoft, Função DROP — Microsoft e Função CORRESPX — Microsoft.