Neste artigo você aprenderá como criar uma lista suspensa condicionada no Excel.
Digamos que você tenha uma lista de estados e queira que ao mudar o estado apareça em outra lista apenas as cidades relacionadas a este.
Você aprenderá como criar esta solução de 3 maneiras diferentes neste artigo.
Lista Suspensa Condicionada no Excel 1
Nos nossos exemplos iremos usar duas listas bases, uma com a Cidade e a UF e outra somente com as UFs sem repetição.
A primeira etapa é criar uma lista com os itens da tabela de UFs, lista 2.
Selecione uma célula e clique em Dados->Validação de dados e selecione Lista e selecione o intervalo selecionado da tabela 2.
Agora já temos uma lista com as UFs.
Usaremos agora a seguinte fórmula para listar apenas as cidades daquela UF:
=DESLOC(tCidades[[#Cabeçalhos];[Cidade]]; CORRESP(‘Solução 1′!$I$7;tCidades[UF];0);; CONT.SE(tCidades[UF];’Solução 1’!$I$7))
Nesta fórmula estamos deslocando então a célula de cabeçalho da coluna de cidade e deslocando para baixo conforme a célula identificada pela função Corresp aonde encontramos a UF na célula I7.
E em Cont.se temos a quantidade de cidades que há naquela UF, então é redimensionado aumentando a quantidade de itens da lista conforme esta quantidade.
Esta função adicionamos então no gerenciador de nomes.
Clique em Fórmulas->Gerenciador de nomes e adicione a fórmula acima e coloque um nome, no caso Sol1Cidades.
Clique na célula aonde iremos colocar a lista da validação de dados e digite =Sol1Cidades conforme o nome definido.
Clique em Dados->Validação de Dados, e seleciones a opção Lista.
Por fim temos então a nossa lista com a UF e a cidade conforme o estado selecionado.
Lista Suspensa Condicionada no Excel 2
O problema da seleção anterior é que ela funciona para apenas uma célula, logo, se tiver a necessidade de usar em uma lista ou tabela uma solução é a abaixo.
Usando o mesmo esquema de validação de dados anterior.
A primeira etapa é criar uma lista com os itens da tabela de UFs, lista 2.
Selecione uma célula e clique em Dados->Validação de dados e selecione Lista e selecione o intervalo selecionado da tabela 2.
Agora já temos uma lista com as UFs.
Usaremos agora a seguinte fórmula para listar apenas as cidades daquela UF com o nome solCidades2.
=DESLOC(tCidades[[#Cabeçalhos];[Cidade]]; CORRESP(‘Solução 2′!$C$5;tCidades[UF];0);; CONT.SE(tCidades[UF];’Solução 2’!$C$5))
A única diferença que temos é que a fórmula está apontando para C5. Quando alteramos o valor desta célula muda a lista de dados automaticamente.
Agora iremos inserir um código VBA para que ao trocar de célula mude o valor da célula C5.
Pressione ALT+F11 e clique duas vezes sobre a planilha na árvore VBE e coloque o código abaixo.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Solucao2.Range("C5").Value = Cells(Target.Row, 2).Value
End If
End Sub
No código é verificando se a coluna é a terceira e se for a célula C5 recebe o valor que temos na coluna 2 correspondente.
Com isso, quando selecionamos uma linha aonde está RJ a célula C5 mudar para RJ e por conseguinte a fórmula passa a listar somente as cidades do Rio de Janeiro.
Lista Suspensa Condicionada no Excel 3
A terceira solução que temos para a lista suspensa é o uso de fórmulas de matrizes dinâmicas.
Seguindo nosso exemplo, para a lista de cidades selecione uma célula qualquer para listarmos os dados.
A primeira etapa é colocar em uma célula a função:
=INDIRETO(“E”&CÉL(“lin”))
Nela nós teremos então o retorno do valor da linha relacionada da coluna E, por exemplo, se a linha mudada foi a 5, será retornado a informação da célula E5, se for da linha 7 retornará a informação de E7.
Para a lista de cidades utilizamos a seguinte fórmula:
=FILTRO(tCidades[Cidade];(tCidades[UF]=$C$8))
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”.
Com isso temos então o retorno de uma lista apenas das cidades que são daquela UF.
Por fim clique em Dados->Validação de Dados e selecione em permitir Lista e na fonte digite =$B$13#, que retornará então as informações daquela tabela.
Com isso temos que ao mudar informação em uma linha será retornada uma lista com as cidades da linha da UF.
Download Planilha de Lista Suspensa Condicionada no Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
