Neste artigo é demonstrado como fazer uma validação de dados tripla e dinâmica no Excel, fazendo assim uma validação dados com lista em cascata.
Olá pessoal, vamos mais um artigo interessante onde vou abordar a Validação de Dados Dinâmica e Tripla, e se possível compartilhar e curtir este artigo agradecerei muito!
Vamos ao desafio:
A “Validação de Dados” dentro do Excel é uma ferramenta muito interessante, mais ainda quando conseguimos juntar conceitos das formulas dinâmicas
Criando a validação de dados em cascata
Para fazer a primeira Validação de Dados das “Universidades” é necessário criar uma coluna auxiliar para que traga somente os valores digitados de maneira elegante, do primeiro registro para o último registro, caso tenha algum registro inserido no meio ele irá inserir e manter a elegância da apresentação.
Segue a Formula na Célula R2:
=SEERRO(ÍNDICE($A$1:$N$1;0;MENOR(ÍNDICE((($A$1:$N$1<>””) * COL($A$1:$N$1))*1;0);CONTAR.VAZIO($A$1:$N$1)+LINS($R$2:R2)));””)
Ou a formula em inglês
=IFERROR(INDEX($A$1:$N$1;0;SMALL(INDEX((($A$1:$N$1<>””) * COLUMN($A$1:$N$1))*1;0); COUNTBLANK($A$1:$N$1)+ROWS($R$2:R2)));””)
Arrastei neste exemplo até a célula R20, para caso inserisse novo valor ele já insere conforme a imagem acima.
Agora, é possível vincular esses resultados na primeira validação de dados que será na célula B2
Lembrando que para colocar uma fórmula na Validação de Dados é só ir no Menu Dados->Validação de Dados->Validação de Dados->Configurações-> Permitir->Lista e depois em Fonte: você deverá colocar a formula nesse espaço em branco.
Segue a Fórmula:
=DESLOC($R$2;0;0;CONT.SE($R:$R;”>a”);1) Ou em inglês =OFFSET($R$2;0;0;COUNTIF($R:$R;”>a”);1)
Utilizei o CONT.SE(R:R;”>a”) pois como CONT.VALORES ele retorna os valores com formulas e o CONT.SE(R:R;”>a”) ele só irá retornar 2.
Como esse é um recurso também muito interessante, falarei dele em um outro artigo. Vamos continuar,
Para a Segunda Validação de Dados na célula B3 agora que começa as aplicações interessantes dentro do Excel
Segue a Fórmula:
=DESLOC($A$1;1;CORRESP($B$2;$1:$1;0)-1;1; CONT.SE(DESLOC($A$1;1;CORRESP($B$2;$1:$1;0)-1;1;6) ; “>a”))
ou em inglês =OFFSET($A$1;1;MATCH($B$2;$1:$1;0)-1;1; COUNTIF(OFFSET($A$1;1;MATCH($B$2;$1:$1;0)-1;1;6); “>a”))
A estratégia desta fórmula é a busca dinâmica dos intervalos da Universidade 1 e 2 e contar em um intervalo de 6 colunas (o que pode ser ajustado), resultando somente o intervalo preenchido 3 Colunas – Universidade 1 e 4 Colunas – Universidade 2
E por último a 3° Validação de Dados que está na célula B4
Segue a Fórmula:
=DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2; CONT.VALORES(DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0) -2;100;1));1)
ou em inglês =OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2; COUNTA(OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3; OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2;100;1));1)
Neste deixei também um intervalo de 200 linhas o que é possível alterar caso tenha mais registros.
E para terminar e alertar o usuário se está correto ou não é só fazer uma condição de busca de 1 ou 0 com formatação condicional, segue a formula na célula C3:
=SE(ÉERROS(CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0));0;1)
ou em inglês =IF(ISERROR(MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0));0;1)
E na Célula C4:
=SE(ÉERROS(CORRESP(B4;DESLOC($A$1;2;CORRESP(B2;1:1;0) +CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2; CONT.VALORES(DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2;100;1));1);0));0;1)
ou em inglês
=IF(ISERROR(MATCH(B4;OFFSET($A$1;2;MATCH(B2;1:1;0)+ MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2; COUNTA(OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3; OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2;100;1));1);0));0;1)
E então? Gostou do artigo? Se gostou, compartilhe com seus contatos e vamos juntos explorar essa ferramenta maravilhosa que é o Excel!
Você tem uma outra maneira de fazer a Validação de Dados Tripla? Escreva para mim aqui embaixo nos comentários!
OBS: Obrigado meu grande amigo Alessandro Trovato pelas dicas e revisões no texto
Abraços
Baixe a planilhaVeja mais artigos em guiadoexcel.com.br
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:
