Validação de Dados Tripla Dinâmica no Excel

Controle de ordem de produção Excel capa
Gráfico com zoom desloc e exibir e ocultar rótulos no Excel
15 de julho de 2018
Mudar cor da célula ao clicar capa
Mudar cor da célula ao clicar Excel
4 de agosto de 2018
Validação de dados tripla dinâmica no Excel

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:

Validação de dados tripla dinâmica no Excel

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)));””)

Validação de dados tripla dinâmica no Excel 2

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)

Validação de dados tripla dinâmica no Excel 3

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”))

Validação de dados tripla dinâmica no Excel 4

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) 

Validação de dados tripla dinâmica no Excel 5

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)

Validação de dados tripla dinâmica no Excel 6

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

Baldini Fabio

Veja mais artigos em guiadoexcel.com.br


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Básico – 1 ano de acesso

R$99,00

COMPRAR
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR

1 Comentário

  1. Marcos Rieper disse:

    Teste de comentário.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.