Listas dependentes Excel – Validação em cascata

Listas dependentes Excel – Validação em cascata

Veremos como criar caixas de listagem com listas dependentes no Excel, a validação em cascata.

Por exemplo, você tem um cadastro e precisa que o cliente preencha a UF e a Cidade neste cadastro.

Cada UF tem as suas respectivas cidades e você quer que seja preenchido corretamente, por isso deseja permitir que ele selecione apenas as cidades da UF selecionada por ele.

Desta forma o cadastro ficará correto, pois o próprio Excel se encarregará de bloquear entradas de dados incorretas.

Vejamos como fazer isto em qualquer versão do Excel.

Preparar a pasta de trabalho

Para o nosso trabalho nós criamos três planilhas:

  • Dados: Nesta planilha nós colocamos as tabelas e listas de dados com informações que são utilizadas na pasta de trabalho.
  • Cálculos: Aqui são realizados os cálculos auxiliares.
  • Validação: Na planilha de validação, é a parte aonde os dados serão utilizados ao cliente.

Planilha Dados

Listas dependentes Excel 1

Temos então duas tabelas, uma com a lista de UF única, vista acima em E9.

E a outra tabela nós temos Cidades e UFs em B9 aonde a lista de UF está classificada, isto é obrigatório para que a técnica funcione.

Planilha Cálculos

Na planilha Cálculos nós temos as fórmulas auxiliares que são utilizadas na planilha.

No caso desta planilha nós colocamos ali a UF que deverá ser filtrada na tabela de Validação.

Listas dependentes Excel 2

Este valor em C9 será utilizado nas fórmulas de cálculos. Clique em C9 e altere o nome da fórmula para UFSelecionada.

Fórmulas de Gerenciador de Nomes

Agora precisamos criar os intervalos nomeados auxiliares que serão utilizados nas fórmulas.

Listas dependentes Excel 3

Primeiro criaremos no gerenciador de nomes a lista de UF, clique em Novo e Digite UF no nome e em Refere-se a selecione a coluna da Tabela UF conforme na figura.

Este intervalo nomeado será utilizado para criar uma lista de validação com as UFs para o cliente.

Crie um novo intervalo e nomeado como Cidades e preencha com a seguinte fórmula: =DESLOC(Dados!$B$9;CORRESP(UFSelecionada;Tabela3[UF];0);;CONT.SE(Tabela3[UF];UFSelecionada)).

Listas dependentes Excel 4

A fórmula funciona da seguinte forma:

=DESLOC(Dados!$B$9;CORRESP(UFSelecionada;Tabela3[UF];0);;CONT.SE(Tabela3[UF];UFSelecionada))

A ideia da função é selecionar a célula inicial da coluna Cidade, esta célula será deslocada conforme o uso da função Corresp que identifica a linha aonde está a UF que está definida em UFSelecionada.

Desta forma a célula B9 é deslocada para a próxima célula aonde está a primeira UF que está listada conforme a que estiver definida na tabela planilha Cálculos.

Depois por fim é alterada a quantidade de registros que existem com a UF selecionada, com a quantidade de estados que existem na tabela de Cidades e UFs.

Utilizando as listas de validação em cascata

Na planilha Validação é aonde realizamos nossos testes.

Para criar a validação de listas dependentes realize o seguinte processo:

Selecione aonde serão escolhidas as UFs e clique em Dados->Validação de dados.

Depois selecione a opção de Validação Listar e digite =UF na fonte.

Listas dependentes Excel 5

Veja que ao clicar na célula as UFs são listadas. Agora trataremos das Cidades.

Selecione aonde serão escolhidas as Cidades, na coluna ao lado da UF e clique em Dados->Validação de dados.

Depois selecione a opção de Validação Listar e digite =Cidade na fonte.

Listas dependentes Excel 6

Agora a lista de cidades está sendo exibida ao selecionar na célula da lista de Cidades.

Mas a lista de cidades é sempre a mesma, iremos agora tratar de como alterar a UF na planilha Cálculos.

Como alterar a UF na fórmula de Validação em Cascata

Para alterar a UF e fazer com que a lista de Cidades seja alterada pressione ALT+F11 e clique duas vezes sobre a planilha Validação.

Listas dependentes Excel 7

Agora utilizaremos um código para que ao mudar a célula o valor em UFSelecionada seja alterado automaticamente em Calculos. Digite o seguinte código VBA:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Calculos.Range("ufselecionada").Value = Cells(Target.Row, 2).Value
    End If
End Sub

No código acima, nós temos o evento do VBA que identifica se ao alterar a célula na planilha Validação sejam alterados os valores na célula Calculos.Range(“ufSelecionada”).

Este valor é mudado apenas se a Target.Column for igual a 3, ou seja a coluna aonde está a nossa lista de cidades.

Assim, o valor que estiver digitado na coluna de UF irá alterar a lista de cidades automaticamente.

Conclusão listas encadeadas no Excel

Com esta aplicação podemos criar quantas listas de validações precisarmos, funcionando em versões mais antigas no Excel.

Validação de dados com Listas em cascata Excel

Download

Faça o download da planilha de exemplo e para analisar a fórmula preencha o formulário de e-mail e faça o download. E no vídeo no topo do artigo você pode assistir esta explicação.

Baixe a planilha