Validando Células com Filtros

Esta postagem responde a uma solicitação do leitor do ExcelMax® "Rui" (conforme comentário postado na postagem: 'Funções: Procv, Desloc, Índice e Corresp').

Criar uma Validação de Células com Filtros:

Nosso objetivo é que ao escolhermos um determinado País (coluna países), na coluna que contém os Estados (UF), só será possível escolher um dos Estados referente ao país escolhido.

Primeiro, criamos uma lista com os Países (sem repeti-los).

Em seguida, criamos uma lista com os Países e os respectivos Estados. Um ponto importante a ser observado aqui é que os países devem estar agrupados para que o efeito de validação funcione corretamente.

Outra coisa importante a ser feita para um bom funcionamento da validação com filtros é dar nome aos intervalos que serão utilizados.

Para isso, podemos utilizar as funções DESLOC e CONT.SE. Estas funções, quando utilizadas para dar nome aos intervalos, os torna, de certa forma, inteligentes. Veja nas explicações a seguir o porque disso.

Primeiro, nomeamos o intervalo referente a lista de Países. Selecione este intervalo: 'D2:D21'. Em seguida, acionamos o 'Gerenciador de Nomes' que se encontra na Guia Fórmulas.

Na figura abaixo, vemos o 'Gerenciador de Nomes' com os nomes dos três intervalos: País (intervalo 'D2:D21'), SG_Países (intervalo 'F2:F21') e Estados (intervalo 'G2:G21').

Explicação dos três nomes:

País (intervalo 'D2:D21'):

• =DESLOC(Plan1!$D$1;1;0;CONT.SE(Plan1!$D$2:$D$21;"<>"&"");1)

SG_Países (intervalo 'F2:F21'):

• =DESLOC(Plan1!$F$1;1;0;CONT.SE(Plan1!$F$2:$F$21;"<>"&"");1)

Estados (intervalo 'G2:G21'): 

•  =DESLOC(Plan1!$G$1;1;0;CONT.SE(Plan1!$G$2:$G$21;"<>"&"");1)

Nos três nomes, utilizamos as mesmas funções com alteração apenas nos intervalos selecionados.

A função DESLOC: retorna uma referência de um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células.

• Sintaxe: DESLOC(ref, lins, cols, [altura], [largura])

A função CONT.SE: conta o número de células dentro de um intervalo que atendem a um único critério especificado. 

• Sintaxe: CONT.SE(intervalo, critérios)

Obs.: É a função CONT.SE inserida dentro da função DESLOC ([altura]) que a torna de certa forma inteligente. Isso acontece porque a área que a função DESLOC retorna, terá o tamanho de itens (linhas) que a função CONT.SE retornar.

A função: '=CONT.SE(Plan1!$G$2:$G$21;"<>"&"")' conta no intervalo especificado somente as linhas que forem diferentes de vazio ("<>"&"").

A seguir, selecionamos o intervalo na coluna 'A'. Este intervalo será utilizado para selecionar o País desejado. Ativamos a opção 'Validação de Dados' que se encontra na Guia Dados. O critério de validação a ser escolhido é: Lista. No campo Fonte, digitamos o nome que definimos anteriormente: '=País'. Observe a figura abaixo.

Isso permitirá que apenas os Países digitados na lista 'País' sejam digitados ou selecionados. 

Para finalizar, devemos aplicar a validação de dados à coluna 'B' (repita os passos da orientação anterior utilizando (no campo fonte) a fórmula abaixo).  

• =DESLOC(Estados;CORRESP(A2;SG_Países;0)-1;0;CONT.SE(SG_Países;A2);1)

Com esta validação, será possível escolher apenas um dos Estados referente ao país escolhido.

O link abaixo permite fazer o download do exemplo.

Download: Validar dados com Filtro.

Se persistir alguma dúvida fiquem a vontade para postar comentários.