Validação em cascata com matrizes dinâmicas

Neste artigo você verá como realizar a validação em cascata utilizando lista de dados com matrizes dinâmicas.

Vejamos um exemplo clássico. Em uma lista de dados você quer selecionar a UF e quer que seja apresentada a lista apenas das cidades daquele Estado na coluna ao lado para que possa ser selecionado.

Criando uma validação de dados em cascata passo-a-passo

Atenção: Esta validação de dados em cascata funcionará apenas no Office 365, pois alguns recursos só estão disponíveis nesta versão do Excel. Temos outro artigo demonstrando como Validação de dados em cascata.

O primeiro passo é você criar a sua lista de dados em formato organizado de tabela. O que isso significa?

O formato de tabela significa que uma coluna terá um mesmo formato, com dados consistentes e que representem o mesmo tipo de informação. Por exemplo UF e Cidade.

Não é necessário, mas é recomendável que você transforme os seus dados em formato de tabela.

Para isso, coloque um cabeçalho sobre cada coluna representando os seus dados e pressione ALT+T+TA ou simplesmente vá na guia de acesso em Inserir->Tabelas.

No nosso exemplo iremos selecionar a UF e na coluna ao lado desejamos que o cliente possa selecionar a cidade.

Neste caso, nós temos então as UFs "repetidas" na coluna UF e precisamos de uma lista única.

Para isso utilizamos a seguinte fórmula, que pode ser colocada em qualquer célula da sua planilha:

=CLASSIFICAR(ÚNICO(Tabela3[UF]);1)

Nesta fórmula utilizamos duas funções, que por acaso são novas no Excel.

A função ÚNICO cria uma lista única de dados, removendo todos os repetidos da sua lista e apresentando abaixo dos dados. Veja mais em nossos artigos sobre Matrizes dinâmicas.

Utilizamos também a função CLASSIFICAR, que realiza a classificação dos dados desta lista, gerando por fim então, uma lista única de UFs de forma classificada.

Esta lista está agora pronta para que possamos utilizar em nossa planilha.

Clique na guia Dados->Validação de dados e selecione a primeira célula da sua lista de UF, aonde você colocou a fórmula acima.

Ao final digite #, isso é essencial, fará com que o intervalo seja entendido como um intervalo de matriz dinâmica, permitindo com isso que os dados sejam todos apresentados na validação, assim como aparecem na planilha.

Agora você já tem uma lista com a validação aonde você pode selecionar a UF, ela será apresentada como no gif no começo do artigo ou no vídeo. Vamos agora à lista de cidades.

Criando a lista de cidades com validação em cascata

Seguindo nosso exemplo, para a lista de cidades selecione uma célula qualquer para listarmos os dados.

Para a lista de cidades utilizamos a seguinte fórmula:

=FILTRO(Tabela3[Cidade];(Tabela3[UF]=INDIRETO("B"&CÉL("lin"))))

Nesta fórmula utilizamos 3 fórmulas, mas são bem simples de entender.

A função FILTRO é uma nova função do Office 365, ela realiza o filtro de dados à partir da coluna Tabela3[Cidade], filtrando quando a coluna Tabela3[UF] for igual ao endereço da célula B e o número da linha que está ativa.

No vídeo nós explicamos detalhadamente como funciona, mas a função INDIRETO monta uma referência dinamicamente, utilizando o texto B e utilizando a função CÉL para retornar o número da linha com o parâmetro "lin".

Isto permite que ao selecionar uma UF na coluna B, que é aonde colocamos as UFs, sejam listadas somente as cidades da mesma, pois o filtro usa esta informação para retornar estes dados.

Por fim basta selecionar a célula aonde gostaria de selecionar as cidades, altere para o tamanho que desejar e clique em Dados->Validação de dados.

Selecione a opção Lista e selecione a célula aonde colocou a fórmula da cidade.

Digite ao final da referência #, isso fará com que a célula seja entendida como uma fórmula de matriz dinâmica e retorne os dados todos desta lista.

Conclusão sobre listas de validação encadeadas ou em cascata

As novas fórmulas de matrizes dinâmicas vieram para facilitar muito o nosso trabalho.

Com elas fórmulas complexas ou que exigiriam mais manobras com funções matriciais ou mesmo VBA ficam bem mais simples.

Neste exemplo utilizamos as fórmulas de matrizes dinâmicas para retornar apenas duas listas encadeadas, mas o modelo é facilmente replicável para 2, 3, 4 ou mais listas, sem pesar na planilha e sem aumentar a complexidade de forma demasiada.

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.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: