Blog

Postado em em 25 de agosto de 2019

Validação de Dados Personalizada – É possível fazer tudo isso!?

Nessa publicação vamos mostrar como utilizar a ferramenta de Valição de Dados! A grande novidade é que vamos te mostrar como fazer a Validação de Dados Personalizada!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para baixar a planilha utilizada nessa aula clique aqui!

O que é a Validação de Dados Personalizada?

É uma validação de dados que é definida pelo usuário, ou seja, o usuário é quem vai dizer ao Excel qual é a fórmula que deverá ser seguida para essa validação. Então essa validação de dados personalizada é um “extra”, pois o Excel já possui algumas opções e caso essas opções não atendem as necessidades do usuário ele poderá criar a sua própria, por isso o nome de personalizada.

 

Quando utilizar esse tipo de Validação?

Essa validação será utilizada quando as opções padrões não forem suficientes para atender as necessidades do usuário, portanto será possível criar uma nova validação. Neste caso vamos querer colocar um limite de gastos em uma planilha para que não seja possível exceder o estipulado.

Planilha inicial para a validação de dados

Planilha inicial para a validação de dados

 

Nesta tabela é possível observar o valor disponível, que será o máximo e também o critério da validação de dados, os meses do ano de 2019 e seus respectivos gastos e por fim o somatório desses meses. Esse somatório não poderá ultrapassar o limite do valor disponível.

 

Como utilizar a Validação de Dados Personalizada?

Para acessar uma publicação onde explico como fazer uma Validação de Dados comum, acesse: https://www.hashtagtreinamentos.com/lista-suspensa-excel-como-fazer-validacao-de-dados-no-excel/

Neste caso primeiro passo é selecionar o intervalo de células que vai de B3 até B14, que corresponde ao intervalo dos valores gastos em cada um dos meses. Em seguida ir até a guia Dados e selecionar a opção de Validação de Dados. Dentro dessa janela será escolhida a opção personalizado e será colocada a fórmula de análise. Essa fórmula é uma comparação da soma dos gastos de todos os meses com o valor disponível, ou seja, caso essa validação retorne um valor falso o Excel irá mostrar um erro.

Fórmula personalizada para a validação de dados com um valor limite

Fórmula personalizada para a validação de dados com um valor limite

 

Essa fórmula faz essa simples comparação da soma dos valores no intervalo selecionado com o valor disponível que se encontra na célula B1. Portanto não teremos erro se esse valor for menor ou igual a R$80.000,00 por exemplo, caso seja maior o Excel vai retornar um erro.

Erro gerado pelo Excel ao exceder o valor estipulado

Erro gerado pelo Excel ao exceder o valor estipulado

 

Essa é a mensagem padrão do Excel quando ocorre um erro na parte de validação de dados, ou seja, ao colocar R$5.000,00 na célula B8 o valor da soma excede o valor disponível, portanto como está maior do que o valor que foi colocado na restrição ele não irá aceitar esse valor. Desta forma o usuário deve clicar em cancelar e tentar colocar outro valor.

Dentro da janela de validação de dados é possível alterar essa mensagem de erro. Vale lembrar que se o usuário fechou a janela deverá selecionar todas as células novamente e selecionar a opção de validação de dados novamente. Dentro dessa janela basta mudar para a aba Alerta de erro.

Alteração da mensagem de erro para explicar melhor ao usuário

Alteração da mensagem de erro para explicar melhor ao usuário

 

Desta forma quando o valor for excedido essa mensagem é que irá aparecer ao usuário.

Verificação do erro modificado pelo usuário

Verificação do erro modificado pelo usuário

 

Então foi possível observar como fazer uma validação de dados de forma restringir o valor máximo em um intervalo definido.

 

Evitar itens duplicados

Outra utilização para a validação de dados é para evitar itens duplicados, ou seja, em listas grandes as vezes é possível com que o usuário não se lembre ou não consiga verificar sempre se já escreveu algum nome, item, marca… desta forma é possível utilizar a validação de dados para que não corra esse erro e o usuário não tenha esses dados repetidos.

O primeiro passo para essa validação é selecionar os dados, neste caso será selecionada toda a coluna A (basta clicar em cima da letra A que o Excel irá selecionar toda a coluna) em seguida abrir novamente a validação de dados e por fim selecionar a opção personalizada. Dentro dessa opção será colocada uma fórmula para contar se aquele item já apareceu mais de uma vez, se isso for verdadeiro o Excel irá retornar um erro.

Validação de dados para verificar se um valor está repetido

Validação de dados para verificar se um valor está repetido

 

A fórmula CONT.SE é exatamente para fazer essa contagem, se o nome aparecer mais de uma vez o resultado será verdadeiro, se for verdadeiro quer dizer que tem mais de um nome dentro da coluna A, e, portanto, esse nome é repetido e não deve ser inserido novamente.

O usuário poderá modificar a mensagem novamente como foi feito no exemplo anterior para que a mensagem de erro seja mais explicativa ao usuário e ele possa entender o porquê daquele erro e assim checar que realmente já possui um nome repetido.

 

Remover Duplicatas

Nesta parte vamos ver como remover os itens duplicados que podem ter sido adicionados por algum erro ou pela falta da validação de dados do exemplo anterior. O Excel possui uma ferramenta específica para essa finalidade, ou seja, é uma ferramenta para verificar se há itens duplicados e removê-los deixando apenas as ocorrências únicas.

Vamos utilizar essa ferramenta na seguinte tabela a fim de remover os itens repetidos.

Tabela com valores repetidos a serem removidos

Tabela com valores repetidos a serem removidos

 

Quando temos uma tabela pequena é fácil fazer essa verificação, no entanto é possível ter planilhas com centenas de dados e isso seria praticamente impossível de se fazer manualmente, por isso vamos utilizar a ferramenta Remover Duplicatas que se encontra na guia Dados.

Seleção da ferramenta Remover Duplicatas

Seleção da ferramenta Remover Duplicatas

 

Primeiramente deve-se selecionar o intervalo onde será feita essa remoção de itens duplicados e em seguida basta selecionar a ferramenta.

Janela aberta ao selecionar a ferramenta Remover Duplicatas

Janela aberta ao selecionar a ferramenta Remover Duplicatas

 

Será aberta a janela para a seleção das colunas caso tenha mais de uma e ao pressionar OK o Excel irá efetuar a remoção dos valores duplicados e irá retornar uma mensagem de quantos valores duplicados foram encontrados e removidos e quantos valores exclusivos restaram.

Informativo dos valores que foram removidos e dos valores únicos que sobraram

Informativo dos valores que foram removidos e dos valores únicos que sobraram

 

Desta forma foram encontrados e removidos 4 valores, que são os 4 últimos nomes da lista. Feito isso sobraram apenas 9 valores exclusivos.

Essa ferramenta é muito utilizada quando temos planilhas com uma grande quantidade de dados e precisamos saber, por exemplo, quantos e quais são os itens exclusivos, ou seja, de 100 linhas de uma planilha de vendas queremos saber quantos produtos diferentes foram vendidos. Para isso bastaria copiar essa coluna para outra planilha e utilizar essa ferramenta. Desta forma seria possível verificar quantos produtos no total foram vendidos dentre as 100 vendas.

Você sabe o que é o Nível Impressionador do nosso Curso de Excel? É um nível que está acima do avançado! Quer saber mais? Só clicar no link!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?


Quer participar de uma aula 100% Online e Gratuita de VBA na quarta-feira às 20h?

Preencha seu e-mail abaixo!