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
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
Baldini Fabio
