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

Antes de efetuar o download do arquivo de exemplo, convido você a socializar conosco.

Escolha uma das opções abaixo. Seguir no twitter, assinar nossa Newsletter...

Isso nos ajuda na divulgação do site e te permite ficar sempre atualizado das novidades. Fico muito grato pela sua colaboração.

Digite seu email:

Você receberá um email para ativar o cadastro, ok!