Carregar uma listagem de acordo com o item selecionado na primeira lista – Parte 1

Olá pessoas Excelentes…
Já falamos,  anteriormente aqui no site, uma forma de restringir a digitação de algum conteúdo numa célula, inclusive, exibindo mensagens personalizadas de erro e tudo mais. Recomendo que você leia esse artigo antes de prosseguir.
Hoje, vamos aprender mais uma utilidade da ferramenta de validação de dados, que pode nos ajudar em diversos casos, dependendo do resultado que precisemos obter.
A validação de dados numa célula da planilha do Excel pode ser configurada de diversas maneiras. Nesta dica, iremos trabalhar com o tipo Lista, ou seja, definiremos uma lista de valores possíveis para ser escolhido pelo usuário, como numa listagem de seleção. Mas, vamos personalizar um pouquinho mais: programaremos uma segunda lista para exibir uma listagem de itens de acordo com o que for selecionado numa primeira lista.

Vejamos como realizar esta façanha.

Iniciando o projeto

Para obtermos o resultado similar ao da figura anterior, primeiramente, precisamos definir os dados que comporão as listagens que popularão as caixas de seleção das células. Temos que criar uma planilha chamada Bases, por exemplo. Não se preocupem… Deixei a planilha pronta para vocês baixarem lá no final do artigo! 🙂

Isto é apenas um exemplo. Geralmente, você já vai possuir seus dados listados em alguma planilha e podem estar dispostos de outra maneira. Não tem problema, você vai adaptar sua fórmula de validação de dados de acordo com ela. Em poucos casos serão necessárias muitas alterações em sua estrutura.
Em nosso exemplo, criamos listas de carros para locação de acordo com a sua categoria (usaremos no exemplo 1) e listas de cidades para cada estado (exemplo 2). A idéia é que, quando o usuário selecionar uma categoria na lista, a outra exiba apenas os carros daquela categoria, ou, se o usuário selecionar um determinado estado, exiba-se apenas as cidades daquele estado.

Exemplo 1 – Gerando uma lista de carros por categoria selecionada

Configurar a primeira lista

Para criarmos a validação para preencher a lista da célula com as categorias, realizaremos os procedimentos abaixo, que servirão também para o segundo exemplo, alterando-se apenas as referências. Veja.
1° passo: Crie uma planilha chamada Locadora de Imóveis, clique na célula C2.
2° passo: Depois selecione a aba Dados e clique em Validação de Dados.

3° passo: Na janela que vai surgir, selecione a aba Configurações. No critério de validação Permitir, escolha Lista e em Fonte selecione o intervalo que contem as categorias – que no nosso exemplo é o intervalo de C1 até F1 – digitando =Bases!$C$1:$F$1. Dê OK.

Você perceberá que no lado direito da célula apareceu uma setinha para que você possa selecionar um valor presente na lista que acabamos de definir.
Se fossemos, simplesmente, definir uma lista de todos os carros da locadora, repetiríamos estes passos acima, contudo, os itens mostrados seriam sempre os mesmos, independente da categoria que fosse selecionada. Para que a segunda lista esteja vinculada a primeira realizemos os seguintes passos.
Configurando a segunda listagem com base na primeira
Vá na planilha Bases e crie uma tabela Modelos da mesma altura que a maior categoria (a que possui mais itens na lista). E criaremos uma fórmula matricial que preencherá essa lista com os itens da categoria escolhida.
Selecione todas as linhas da tabela Modelos, digite a fórmula e tecle CTRL+SHIFT+ENTER:

=SEERRO(SE(DESLOC($C$2:$C$10;0; CORRESP(‘Locadora de Imóveis’!$C$2;$C$1:$F$1;0)-1)=0;””; DESLOC($C$2:$C$10;0; CORRESP(‘Locadora de Imóveis’!$C$2;$C$1:$F$1;0)-1));””)

Essa fórmula irá mover a referência para o lado de acordo com a categoria escolhida.
Agora volte para a planilha Locadora, selecione a célula C4 e repita os passos 2 e 3, alterando apenas os valores para Fonte.

=DESLOC(Bases!$H$2:$H$10;0;0; CONT.VALORES(Bases!$H$2:$H$10) – CONTAR.VAZIO(Bases!$H$2:$H$10))

Neste artigo não vou me ater em explicar as funções utilizadas acima, mas, caso queira aprender um pouco mais sobre elas, recomendo meu eBook 7 Fórmulas do Excel que Podem Salvar sua Vida, uma coletânea das 7 funções mais utilizadas no Excel pela maioria dos usuários e nos mais diversos segmentos. E, vocês podem ler as diversas maneiras de contar no Excel.
Agora quando você selecionar uma categoria, a lista de carros correspondente será carregada na segunda lista.
 
Bem, meus amigos leitores, o artigo está ficando grande, por isso vou dividí-lo em duas partes. Na segunda parte, mostrarei outra aplicação útil para essa funcionalidade: exibir a lista de cidades para um determinado estado selecionado pelo usuário.
Então, se você gostou, siga a gente no twitter,  compartilha em seu twitter ou facebook, divulga para os amigos, comenta ali abaixo. Ajuda bastante na divulgação do blog.
Um abraço.
 

UPDATE: 16/10/2012

Para atender ao feedback de uma leitora, disponibilizei uma cópia do exemplo utilizado neste artigo, mas na versão do Excel 2003, ok.
Podem baixar aqui.
Link de Download para versão Excel 2003

10 comentários em “Carregar uma listagem de acordo com o item selecionado na primeira lista – Parte 1”

  1. Puxa, estou mega desapontada, pos não consegui de jeito nenhum fazer funcionar. Minha versão do excel é mais antiga (2003) e acredito que não tenha essa função. Tem como preparar esse esquema para a versão 2003?
    Abs

    Responder
    • Olá, Patrícia!
      Não fique desapontada não… 🙁
      Sempre há uma solução!!! =)
      No caso, o arquivo que disponibilizei realmente não funciona no Excel 2003, mas, a funcionalidade é PLENAMENTE possível de utilizar em todas as versões.
      As fórmulas que utilizam a função SEERRO, devem ser adaptadas com a combinação das funções SE e ÉERROS.
      Essa montagem fica bem grande, pois repete-se a fórmula principal de resultado (uma vez no teste condicional e outra vez no retorno do resultado).
      Contudo, resolve-se o problema. Isso que importa.
      Disponibilizei uma cópia do exemplo utilizado neste artigo, mas na versão do Excel 2003, ok.
      Pode baixar aqui.
      Link de Download para versão Excel 2003
      Abç

      Responder
  2. Show de bola…
    estava há algum tempo a procura de uma solução para este problema e até agora só tinha visto explicações e formas muito mais complexas de se resolver isto..
    parabéns!!!

    Responder
  3. Por gentileza,
    Venho procurando na internet uma solução para um problema que está me fazendo quebrar a cabeça, vou tentar explicar de uma forma clara e simples.
    Preciso selecionar vários clientes a partir de uma data. Tenho uma planilha que chamo de base, que tem todos os clientes e os dias de visitas. Tenho uma outra que chamo de “sistematica” de visita onde todos os dias eu preciso preencher manualmente todos os clientes q visitei.
    Há alguma possibilidade de eu colocar apenas o dia e automaticamente ser preenchido os clientes que tenho que visitar.
    Muito obrigado.
    Mário

    Responder

Deixe uma resposta para Reinaldo Coral Cancelar resposta