Lista Suspensa em Cascata no Excel (Validação Condicional)
Nesse post vamos te mostrar o que é e como você pode construir uma Lista Suspensa em Cascata no Excel para suas análises e planilhas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos.
O que você aprende neste vídeo
Resposta rápida: Lista em cascata é validação de dados em duas etapas. Primeiro você nomeia, na aba de opções, o intervalo de cargos de cada área com o nome exato da área. Depois cria a validação da coluna de cargo com =INDIRETO(C2), sem cifrão: o INDIRETO converte o texto da célula ao lado em referência, e a falta do travamento faz cada linha olhar a própria área.
Neste vídeo (21 min):
- 0:00 - O que é lista em cascata: a segunda lista muda conforme a escolha da primeira
- 1:00 - A base do exemplo, e a aba de opções com uma coluna de cargos por área
- 2:00 - O objetivo: escolher a área e ver apenas os cargos daquela área
- 2:40 - Validação de dados na guia Dados, e o que ela restringe
- 3:30 - Tipo Lista, e a fonte apontando o intervalo das áreas
- 5:00 - A lista pronta rejeitando texto digitado errado
- 5:30 - Nomeando intervalos: cada coluna de cargos ganha o nome da área
- 6:30 - A caixa de nome, ao lado da barra de fórmulas, é onde o apelido é dado
- 7:40 - Nomeando as demais áreas, e o cuidado com o nome escrito diferente
- 8:40 - Validação na coluna de cargo, e a primeira tentativa apontando a célula ao lado
- 9:40 - O erro clássico: a lista sai com a palavra, e não com os cargos
- 10:40 - INDIRETO: a função que converte o texto da célula em referência de intervalo
- 12:00 - Funcionou numa linha e repetiu nas outras: o sintoma do intervalo travado
- 17:30 - Tirando o cifrão da referência, e cada linha passando a olhar a própria área
Trechos do vídeo:
- Validação de dados é a ferramenta do Excel para restringir quais informações a gente consegue colocar na célula.
- Eu não quero a palavra marketing: eu quero o intervalo que eu nomeei de marketing.
- O cifrão prendia a fórmula na célula C2; sem ele, cada linha olha a área que está na própria linha.
Para baixar a planilha utilizada nesta publicação, clique aqui!
Resposta rápida: A lista suspensa em cascata no Excel é uma validação de dados em que a segunda lista só mostra as opções ligadas ao item escolhido na primeira. Para montar, nomeie cada intervalo de opções com o nome exato da categoria no Gerenciador de Nomes e use =INDIRETO(C2) como fonte da segunda validação. Nome de intervalo não aceita espaço.
O que é uma Lista Suspensa em Cascata?
Uma lista suspensa nada mais é do que uma seleção prévia de alguns dados para que o usuário possa em seguida selecionar somente essas opções da lista, assim evitando erros e informações incorretas ao registrar uma informação.
A lista em cascata é uma combinação dessa lista suspensa, ou seja, quando selecionarmos uma opção na primeira lista, o usuário terá uma lista específica para aquela seleção, desta forma é possível descer em alguns níveis dependendo da necessidade do usuário.
Quando utilizar uma Validação de Dados Condicional?
Uma lista neste formato é utilizada quando o usuário precisa categorizar suas informações. Neste caso exemplo temos áreas de trabalho e dentro de cada área teremos seus respectivos cargos, portanto neste caso ao selecionar uma das áreas de vendas na célula seguinte só teremos as opções de cargo daquela área em específico.
Desta forma o usuário não se confunde nem erra na hora de preencher certos dados, então além de facilitar a seleção de dados é possível evitar erros ao preencher essas informações.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Como criar uma Lista Suspensa em Cascata?
Inicialmente vamos analisar a planilha inicial que temos com as informações dos funcionários de uma empresa.
É possível observar que temos algumas informações importantes sobre cada um dos funcionários, no entanto temos uma segunda aba onde temos os cargos específicos de cada uma das áreas que podem diferir das outras áreas.
É a partir dessas informações que vamos criar a lista suspensa em cascata. Lembrando que se trata de um exemplo e o usuário poderá modificar para adaptar a sua necessidade.
Nesta segunda aba é possível observar que temos 6 diferentes áreas e alguns cargos diferentes para cada uma delas. Então o objetivo é ao selecionar uma área na planilha o Excel nos habilitar somente os cargos disponíveis para aquela área em específico.
Para iniciar com a criação dessa lista o primeiro passo é selecionar todas as informações da coluna C (para isso o usuário poderá selecionar a primeira célula e em seguida pressionar CTRL+SHIFT+SETA PARA BAIXO) que é referente a área dos funcionários e ir até a opção de Validação de Dados que se encontra na guia Dados.
Feito isso o Excel irá abrir uma nova janela para que possamos configurar essa validação de dados.
Nesta janela vamos alterar a parte de Permitir para Lista e em seguida na parte de Fonte, vamos até a aba Opções e selecionar somente as áreas da empresa que se encontra na primeira linha de cada coluna.
Feito isso todas as células da coluna de área ficarão com uma seta ao selecionar a célula, e ao clicar nessa seta teremos somente as opções de cargos que foram selecionadas. Desta forma o usuário não precisa escrever manualmente e não consegue inserir um dado diferente do que foi colocado na lista.
Isso é muito importante, pois evita erros na hora do cadastro e evita erros em fórmulas caso o usuário esteja utilizando alguma para fazer alguma análise dentro da planilha, ou seja, caso o usuário esteja analisando todas as áreas e alguém escreveu algum nome errado.
Esse nome errado deixaria de ser contabilizado podendo gerar um erro, desta forma evitamos esse tipo de erro e limitamos o usuário a escolher somente o que está na lista e nada mais.
Para dar seguimento ao processo de lista em cascata vamos precisar “informar” ao Excel quais serão as informações que ele precisa buscar, para isso vamos primeiramente ir até a guia opções, em seguida vamos selecionar o primeiro intervalo de cargos.
Feito isso vamos agora renomear esse intervalo, ou seja, vamos dizer ao Excel que esse intervalo tem um nome específico, que é exatamente o nome da área que esses cargos se encontram. Então onde temos A2 logo ao lado esquerdo da barra de fórmulas e acima do número 1, vamos alterar o nome para Compras.
Feito isso vamos repetir o procedimento para todas as outras áreas, lembrando de selecionar os cargos e em seguida renomear o intervalo para o nome da área analisada.
Ao finalizar de renomear cada um dos intervalos vamos voltar a primeira aba e selecionar todas as informações da coluna D, que é a coluna de cargo para que possamos inserir outra validação de dados.
Neste caso vamos utilizar a fórmula =INDIRETO(C2) para que o Excel possa entender que a palavra que está na coluna de área se trata de um intervalo e não somente da palavra em si que está na célula.
Desta forma o Excel entende que precisa retornar o intervalo que foi renomeado com aquele cargo, assim teremos uma lista somente com as opções de cargo daquela área em específico.
Dentro do parêntese colocamos apenas C2 (sem trancamentos), pois o Excel irá fazer a validação de dados para todo o nosso intervalo, então quando for para a linha seguinte ele irá considerar a célula C3, depois C4 e assim por diante, ou seja, ele já faz isso automaticamente, só precisamos informar a primeira célula do intervalo que terá como referência.
Feito isso teremos nossa validação de dados vinculada a área, no entanto repare que temos alguns triângulos verdes em algumas células, isso indica que o texto que está naquela célula não corresponde com as informações da lista.
Isso ocorre porque ao preencher a lista o usuário preencheu alguns cargos de forma errada, ou seja, como estava sem essa lista o Excel permitiu com que ele fizesse isso. É possível observar que na área de financeiro temos apenas 4 cargos e Estagiário 2 não é um deles, por isso ficou desta forma.
Agora caso o usuário vá cadastrar um novo funcionário e tente colocar financeiro com o cargo de estagiário 2 por exemplo o Excel não irá permitir.
Os próximos registros serão apenas com as opções de cada área conforme foi configurado.
Conclusão – Lista Suspensa em Cascata
Nesta aula foi possível aprender como fazer uma lista suspensa em cascata para que o usuário consiga obter somente as informações relacionadas ao dado anterior, assim além de evitar erros limita o range de opções que o usuário tem para registrar uma nova informação.
Perguntas frequentes
1. Como usar categorias com duas palavras na lista em cascata?
O Gerenciador de Nomes não aceita espaço no nome do intervalo. Cadastre “Recursos Humanos” como Recursos_Humanos e troque a fonte da segunda validação por =INDIRETO(SUBSTITUIR(C2;" ";"_")), que converte o espaço em underline antes de procurar o intervalo.
2. Dá para fazer lista em cascata sem o INDIRETO?
Dá, no Microsoft 365. Em vez de nomear intervalo por intervalo, monte a lista do segundo nível numa célula auxiliar com =FILTRO(cargos;areas=C2) e aponte a validação para o intervalo despejado usando =$H$2#. A lista passa a se atualizar sozinha quando você acrescenta uma opção na base.
3. A lista em cascata funciona com mais de dois níveis?
Funciona. Cada nível repete a mesma lógica: o nome do intervalo do nível seguinte é o valor escolhido no nível anterior. Para um terceiro nível, crie intervalos com o nome exato de cada opção do segundo e use =INDIRETO(D2) na fonte.
4. O que acontece com a segunda célula quando eu troco a categoria?
A validação limita o que pode ser digitado dali em diante, mas não apaga o que já estava na célula — é por isso que aparecem os triângulos verdes de inconsistência. Apague a célula do cargo antes de trocar a área, ou use Dados > Validação de Dados > Circular Dados Inválidos para achar todas as sobras de uma vez.
Apostila Básica de Excel
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Hashtag Treinamentos
Para acessar outras publicações de Excel Avançado, clique aqui!
