O que é validação de dados no Excel? Um guia abrangente para você adicionar, editar e remover regras de validação de dados no Excel

Compreender e implementar os princípios de validação de dados é essencial para analistas de dados juniores e especialistas experientes. Neste tutorial, exploraremos como realizar a validação de dados no Excel. Abordaremos as várias técnicas de validação de dados disponíveis e demonstraremos como adicionar, editar e remover regras de validação de dados.

Se você quiser revisar os conceitos básicos do Excel antes de começarmos, incluindo as principais fórmulas, operadores, funções matemáticas e muito mais, explore nossa Folha de consulta de fórmulas do Excel.

O que é validação de dados no Excel?

A validação de dados no Excel refere-se a um recurso específico que controla os valores que podem ser inseridos em uma célula. Ele oferece uma série de técnicas que podem ser usadas para verificar os valores existentes ou criar regras que se aplicarão a eles.

Para localizar o recurso de validação de dados no Excel, você deve seguir estas etapas:

1. Abra a guia Dados.
2. Vá para o grupo Ferramentas de dados.
3. Clique no botão Validação de dados.

Abrindo o recurso de validação de dados abrir o recurso de validaçãoAbrindo o recurso de validação de dados

A seguinte janela pop-up é exibida na tela.

Janela pop-up Validação de dados

Mais adiante neste tutorial, exploraremos como configurar essa janela com mais detalhes, inclusive como modificar e remover nossas regras de validação de dados. Primeiro, vamos aprender mais sobre a validação de dados. 

Por que a validação de dados é importante?

A validação de dados é o processo de verificação da precisão e da consistência dos dados que foram ou serão colocados em um banco de dados para armazenamento e processamento posterior. Com a validação de dados, garantimos que os dados estejam em conformidade com critérios específicos e predefinidos. Esses critérios podem incluir o tipo de dados correto, valores dentro de um intervalo especificado e elementos de dados com o comprimento adequado.

A validação robusta de dados é imprescindível por vários motivos. Ele protege contra erros de entrada de dados e garante que as informações usadas nas análises sejam confiáveis e válidas. Vamos dar uma olhada mais de perto nos principais motivos para a validação de dados.

Qualidade dos dados

A validação de dados impede que valores imprecisos ou inconsistentes sejam armazenados no banco de dados. Isso reduz eventuais erros, aumenta a qualidade geral dos dados e aprimora o significado da análise de dados.

A importância de ter dados de alta qualidade está perfeitamente encapsulada no conceito GIGO, que é popular na ciência da computação e significa garbage-in (entrada de lixo) e garbage-out (saída de lixo). Essencialmente, a análise de dados com falhas leva a resultados errôneos e enganosos.

Vamos ver alguns exemplos em que as regras de validação de dados não foram aplicadas e nossos dados tinham valores estranhos.

• Registros da universidade: Uma universidade não pode ter um número decimal de alunos; somente números inteiros são válidos.
• Registros médicos: A temperatura máxima do corpo de uma pessoa não pode ser superior a 50 °C.
• Inscrições por idade: Um campo de idade deve aceitar apenas valores realistas, normalmente entre 0 e 120.
• Endereços de e-mail: Um campo de e-mail deve seguir um formato específico, que inclui o símbolo "@". 
• Contagens de inventário: As quantidades de produtos em um sistema de estoque não devem ser negativas.

Lembre-se de que, embora a validação de dados garanta a integridade dos dados, ela não garante a correção absoluta. Por exemplo, em uma tabela para cães e gatos, uma regra de validação pode garantir que somente entradas do tipo "gato" ou "cachorro" sejam permitidas, mas não pode impedir que um gato seja rotulado como cachorro por engano.

Integridade e conformidade dos dados

A validação de dados é fundamental para manter a integridade dos dados e garantir a conformidade com as normas e os padrões do setor.

No setor de saúde, a conformidade com a HIPAA é obrigatória para proteger as informações dos pacientes. Da mesma forma, as empresas que operam na UE devem aderir ao GDPR, garantindo a privacidade e a segurança dos dados dos indivíduos. 

Limpeza e pré-processamento de dados

Um princípio famoso afirma que os analistas de dados gastam aproximadamente 80% de seu tempo preparando os dados para uso e apenas 20% na análise em si. Como os analistas de dados geralmente gastam uma quantidade desproporcional de seu tempo limpando e pré-processando dados, as regras de validação de dados aplicadas no início do fluxo de trabalho podem economizar muito tempo.  

Tomada de decisões

A análise de dados validados leva a descobertas confiáveis e perspicazes que, por sua vez, ajudam a tomar decisões mais informadas e orientadas por dados. A longo prazo, a validação de dados economiza o tempo e o dinheiro da empresa e resulta em um negócio mais saudável, econômico e lucrativo.

Diferentes técnicas de validação de dados no Excel

O Excel tem diferentes abordagens de validação de dados para atender a diferentes necessidades. Por conveniência, vamos dividi-los em grupos e caracterizar cada grupo.

Restringir o tipo de dados e o intervalo/comprimento

No Excel, você pode selecionar entre os seguintes tipos de dados do Excel: Whole number, Decimal, Date, Time, e Text.

Vamos agora criar uma tabela para que você considere a aplicabilidade de determinadas condições lógicas aos nossos diferentes tipos de dados. As condições lógicas incluem: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, e less than or equal to.

• Entre e não entre: Essas condições são geralmente usadas com valores numéricos, datas e horas. Quando aplicado a text, between e not between, refere-se ao comprimento, ou número de caracteres, em uma célula.

• Igual a e não igual a: Eles podem se aplicar a todos os tipos de dados. Para text, ele verifica se há correspondências exatas.

• Maior que, Menor que, Maior que ou igual a, e Menor que ou igual a: Essas condições são adequadas para os tipos de dados whole number, date e time, mas não são diretamente aplicáveis a text, a menos que você considere o comprimento do texto ou, em alguns casos específicos, a ordem alfabética.

Criação de listas suspensas

Às vezes, pode ser necessário limitar as opções em determinadas células do Excel a um conjunto predefinido de valores. Vamos considerar nosso exemplo anterior em que apenas cães e gatos são entradas válidas em uma tabela. A melhor maneira de criar uma regra de validação de dados para evitar que crocodilos e outros animais entrem em nossa tabela é criar uma lista suspensa para cada célula relevante. Vamos explorar as etapas detalhadas.

1. Digite os itens que você deseja que apareçam na lista suspensa. Você também pode classificar os itens da lista selecionando a lista inteira, clicando com o botão direito do mouse, selecionando Classificar e escolhendo um método.

Inserindo os Inserindo os itens de uma lista suspensa

2. Converta sua lista em uma tabela do Excel. Selecionamos qualquer célula em seu intervalo e pressionamos Ctrl+T. Essa etapa é importante porque, se você adicionar ou remover itens da sua lista, todas as listas suspensas dependentes serão atualizadas automaticamente.

você podede um menu suspenso em uma tabela do Convertendo a lista de um menu suspenso em uma tabela

3. Selecione a célula ou células do Excel na planilha onde você deseja adicionar a lista suspensa.
4. Abra a janela pop-up Validação de dados. Abra a guia Dados. Vá para o grupo Ferramentas de dados e clique no botão Validação de dados.
5. Na guia Configurações, no campo Permitir , selecione Lista. Deixe a caixa suspensa In-cell marcada. Deixe a caixa Ignorar espaço em branco marcada se a célula ou células do Excel onde você deseja adicionar a lista suspensa puderem permanecer vazias; caso contrário, desmarque-a:

Definir uma lista suspensa como um método de validação de dados

6. Coloque o cursor na caixa Fonte, selecione o intervalo da lista e pressione OK. Você precisa deixar de fora a linha do cabeçalho para evitar que ela seja uma opção de seleção.

Selecionando a fonte para uma lista suspensa

7. Selecione a célula ou células do Excel para as quais você criou essa validação de dados. Agora você verá um triângulo suspenso à direita dessa célula. Depois de clicar no triângulo, você pode selecionar o valor necessário para essa célula.

Abrindo uma lista suspensa abrirAbrindo uma lista suspensa em uma célula

Aplicação de fórmulas personalizadas

• alcance: Aqui estamos nos referindo ao intervalo de células do Excel ao qual queremos aplicar a validação de dados baseada em fórmulas personalizadas.
• uppermost_cell: Aqui estamos nos referindo à primeira célula, ou à célula mais alta, do nosso intervalo.

Somente números ou texto

O objetivo das duas fórmulas a seguir é verificar se o conteúdo de uma célula específica é um número ou um texto. É útil quando você precisa diferenciar entre valores numéricos e não numéricos, ou se precisa diferenciar entre valores de texto e não texto, respectivamente.

=ISNUMBER(uppermost_cell)

=ISTEXT(uppermost_cell)

Somente texto em letras maiúsculas ou minúsculas

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))

Não há duplicatas

=COUNTIF(range, uppermost_cell)<=1

Valores que começam apenas com uma substring específica

As fórmulas a seguir verificam se os primeiros n caracteres de uma cadeia de caracteres são uma correspondência exata. Isso retornará um valor TRUE ou FALSE. 

=EXACT(LEFT(uppermost_cell, n), substring)

Somente dias de semana ou finais de semana

=WEEKDAY(uppermost_cell, 2)<=5

=WEEKDAY(uppermost_cell, 2)>5

Somente datas passadas

=uppermost_cell<TODAY()

Sem espaços à esquerda ou à direita

=uppermost_cell=TRIM(uppermost_cell)

Sem espaços

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")

Evitar que você exceda o total

=SUM(range)<=total_cell

Para aprimorar rapidamente seu nível de conhecimento com fórmulas no Excel, considere a possibilidade de fazer um curso de Análise de Dados no Excel bem completo e orientado para a prática.

Como usar a validação de dados no Excel

Para maior clareza, dividi esta seção em duas subseções. A primeira subseção mostrará a você como configurar a pasta de trabalho para incorporar regras de validação de dados. A segunda seção explicará como aplicar essas regras a células que já contêm dados. Vamos dar uma olhada. 

Como você pode configurar uma pasta de trabalho do Excel para regras de validação de dados?

1. Selecione a célula ou células do Excel em que você deseja validar os dados.
2. Abra a janela pop-up Validação de dados. Abra a guia Dados. Vá para o grupo Ferramentas de dados e clique no botão Validação de dados.
3. Na guia Configurações, no campo Permitir, selecione o método necessário, que, por padrão, é definido como Qualquer valor (o que significa que não há validação de dados).

Selecionando o método necessário

4. Deixe a caixa Ignorar espaço em branco marcada se a célula ou células do Excel em que você deseja fazer a validação de dados puderem permanecer vazias; caso contrário, desmarque-a.

Manuseio de células em branco

5. Se você tiver selecionado o método List, escolha uma regra predefinida relevante para o seu tipo de dados. Se você tiver selecionado o método Custom (Personalizado ), insira uma fórmula personalizada.

Seleção de uma condição lógica para restringir o intervalo/comprimento dos dados

Inserir os limites inferior e superior para restringir o intervalo/comprimento dos dados

Aplicação de uma fórmula personalizada

6. Na guia Mensagem de entrada, você pode definir um título e uma mensagem de entrada. Por padrão, não há mensagem de entrada. 

Definir uma mensagem de entrada para solicitar ao usuário o tipo certo de valores

Verificação da aparência da mensagem de entrada

7. Na guia Error Alert (Alerta de erro), você pode definir o estilo da mensagem de erro(Stop, Warning ou Information), o título e a mensagem de erro. Por padrão, uma mensagem de erro é do estilo Stop.

Definir uma mensagem de erro

Depois de clicar em OK, você pode testar o funcionamento selecionando qualquer célula vazia do Excel à qual essa validação de dados se aplica e inserindo um valor inválido. Para fins de comparação, aqui está um exemplo de mensagem de erro em cada um de nossos estilos. 

Você recebe uma mensagem de erro do estilo Stop ao inserir um valor inválido

Você recebe uma mensagem de erro do tipo Aviso ao inserir um valor inválido

Você recebe uma mensagem de erro do estilo Informações ao inserir um valor inválido

A mensagem de erro padrão ao inserir um valor inválido

Observação: As mensagens de erro dos estilos Warning ou Information não impedem que as pessoas insiram dados inválidos, mas as mensagens de erro do estilo Stop sim.

Como usar a validação de dados no Excel nas células já preenchidas

E se algumas das células do Excel em que você deseja fazer a validação de dados já estiverem preenchidas com valores? O recurso de validação de dados permite a validação de dados de entrada e pós-entrada. 

1. Selecione as células do Excel em que você deseja fazer a validação de dados. Algumas dessas células já conterão valores.

Selecionar as células para validação de dados, incluindo as que já contêm valores

2. Abra a janela pop-up Validação de dados. Abra a guia Dados. Vá para o grupo Ferramentas de dados e abra o botão Validação de dados. 
3. Na guia Settings (Configurações ), no campo Allow (Permitir ), selecione o método necessário, que, por padrão, é definido como Any value (Qualquer valor).

Definir a validação de dados para as células selecionadas

3. Selecione qualquer célula do Excel dentre aquelas às quais você aplicou essa validação de dados, abra a guia Dados, clique na seta suspensa à direita do botão Validação de dados do grupo Ferramentas de dados e selecione Círculo de dados inválidos.

Como circundar dados inválidos no Excel

Dados inválidos circulados no Excel

4. Agora você pode modificar cada valor inválido para que ele satisfaça os critérios impostos. Depois de fazer isso, o círculo ao redor do valor desaparecerá automaticamente. É importante lembrar que, se você simplesmente excluir um valor inválido, o círculo permanecerá.
5. Para remover os círculos de validação, selecione qualquer célula do Excel dentre aquelas às quais você aplicou essa validação de dados, abra a guia Dados, vá para o grupo Ferramentas de dados, abra o botão Validação de dados e selecione Limpar círculos de validação. 

Remoção de círculos de validação de dados inválidos

Como editar a validação de dados no Excel

1. Selecione qualquer célula do Excel que já tenha validação de dados aplicada.
2. Abra a janela pop-up Validação de dados. Abra a guia Dados. Vá para o grupo Ferramentas de dados e clique no botão Validação de dados.
3. Faça as modificações que você precisar em qualquer uma das guias da janela Validação de dados. Se necessário, revise as instruções da seção Como usar a validação de dados no Excel.
4. Vá para a guia Configurações e marque Aplicar essas alterações a todas as outras células com as mesmas configurações.
5. Pressione OK.

Aplicar as modificações de validação de dados a todas as outras células com as mesmas configurações

Como remover a validação de dados no Excel

1. Selecione qualquer célula do Excel que já tenha validação de dados aplicada.
2. Abra a janela pop-up Validação de dados. Abra a guia Dados. Vá para o grupo Ferramentas de dados e clique no botão Validação de dados.
3. Vá para a guia Configurações e marque Aplicar essas alterações a todas as outras células com as mesmas configurações.
4. Clique no botão Clear All (Limpar tudo ).
5. Pressione OK.

Remoção da validação de dados

Conclusão

Este tutorial abordou a definição de validação de dados, seus benefícios, a implementação no Excel, as várias técnicas disponíveis e como usar, editar e remover a validação de dados no Excel.

Se você quiser desbloquear todo o poder do Excel e aprender a preparar, visualizar e analisar dados por meio de exercícios práticos, bem como descobrir alguns recursos avançados, experimente o Excel Fundamentals, um programa de habilidades abrangente e fácil de usar para iniciantes. Além disso, considere cursos especializados, como Preparação de dados no Excel ou Modelagem financeira no Excel, que oferecem treinamento focado para aprofundar seus conhecimentos. Esses recursos ajudarão você a progredir da validação básica de dados até o domínio dos recursos avançados do Excel.