Tecnologia

Ξ 19 comentários

EXCEL: Você sabe o que é e como utilizar a “Validação de Dados”?

publicado por Gilberto Gomes

validacao-300x164

Quantas foram as vezes em que fui chamado para ver um “pau” que estava dando em alguma planilha e, ao analisar, pude concluir com facilidade que o problema era uma determinada célula que esperava uma data e foi preenchida com um valor simples, ou um texto, ou uma data em formato maluco? Ou um campo de valor que, ao invés de digitar um zero, o usuário colocou a letra “o”, ou um “-“? Inúmeras! Incontáveis! Quanto dinheiro vi as empresas perdendo, tempo de seus funcionários e horas de consultoria, para, ao final, chegarmos à conclusão de que, se as pessoas tomassem mais cuidado com as entradas de dados, nenhum problema teria ocorrido? não dá para saber.

Apresentarei abaixo um recurso extremamente simples que, se observado e implementado, pode reduzir, e muito, as chances de falhas nas planilhas. Vejamos a seguir como funciona o recurso validação de dados. Para este artigo, criei uma planilha simples, que com poucos campos, exemplifica bem o uso disso:

Planilha de Exemplo

Nosso primeiro passo será limitar o range de valores que poderá ser utilizado para o campo código. Para efetuar essa tarefa, vá no menu dados e em seguida validação de dados.

Acessando validação de dados

Aparecerá a tela de definição da validação. Todas as células aceitam, por definição, qualquer valor em seu preenchimento.

 

Caixa de validação de dados

Para começarmos a definir a validação, precisamos, primeiramente, alterar o valor do campo permitir para número inteiro, e definiremos mínimo e máximo como 1 e 100 respectivamente. Assim, qualquer valor fora dessa faixa será recusado.

Definindo a faixa de valores válidos para o campo código

a tela de definição da validação de dados possui três “tabs”. A primeira é a de definição da regra, a segunda é a de mensagem de entrada, ilustrada abaixo, e a terceira é a de mensagem de erro.

 

Definindo mensagem de entrada do campo

Na tela acima, acabamos de definir qual será a mensagem de entrada exibida quando o usuário estiver editando a célula código.

A terceira TAB é a que define qual será o comportamento do EXCEL em caso de valor inválido para o campo. são três os comportamentos possíveis:

  1. PARAR – impede valores fora do estabelecido. Proteção total.
  2. AVISO – alerta sobre valor inválido e dá ao usuário a opção de aceitar o valor mesmo assim.
  3. INFORMAÇÕES – apenas informa que a regra foi quebrada, mas aceita a entrada.

Comportamentos disponíveis para o tratamento de erros.

Para este exercício, utilizamos parar para garantirmos a faixa de valores de 1 a 100. Se o valor informado estiver fora do estabelecido, a mensagem abaixo será exibida e o valor rejeitado.

Definindo mensagem de erro, em caso de dados inválidos

Notem na tela abaixo que, com a célula código selecionada, uma mensagem de entrada é exibida, orientando o usuário quanto ao preenchimento:

Campo CÓDIGO, já com a validação ativa. Note a mensagem de entrada

Se, ainda assim, o valor for informado errado a mensagem de erro que definimos será exibida e esse será rejeitado.

 

Mensagem de erro, quando um dado inválido foi informado.

Abaixo, definimos as regras para validação do campos data de nascimento. Escolhemos o tipo como data e definimos uma faixa aceitável.

Definindo regras para o campo data de nascimento

Agora, definimos os valores aceitáveis para o campo sexo. Por se tratar de uma lista com duas opções, coloquei diretamente na caixa de definição, separando as opções masculino e feminino por ponto e vírgula (;).  Se fosse um número maior de opções, poderíamos colocá-las em células na própria planilha e informar o range. Por exemplo, “=j2:j6”, onde 5 itens seriam inseridos na lista.

Definindo lista de valores válidos para o campo sexo

Vejam abaixo a planilha já apresentando um objeto que possibilita a escolha do preenchimento da célula. Em caso de usuário digitar diretamente na célula, sem passar pelo seletor, a validação será executada da mesma maneira e o valor rejeitado, caso esteja assim definido.

Planilha exibindo a lista de sexo para o preenchimento da célula

Definimos aqui uma faixa aceitável de salários.

Definindo faixa válida para o campo salário

Feito! definimos as regras de entrada de dados para os nossos campos. Dessa maneira, sempre teremos certeza de que os dados informados estão dentro daquilo que definimos inicialmente, quando do planejamento da planilha.

Formulário final, com todas as regras implementadas

Espero ter conseguido mostrar a vocês a importância de ativar a validação dos dados de entrada de uma planilha e também o quanto é fácil fazer isso tudo funcionar.

Clique aqui e baixe uma cópia desse exercício pronto.

Autor

Administrador de empresas e analista de sistemas, 25 anos de experiência em consultoria, treinamentos e palestras, Microsoft Certified Trainer ID# 11905, especialista em Office, atuando ativamente em consultorias de automação. Palestrante em mais de 50 eventos apresentados, demonstrando os produtos Office em Universidades (UFMG, FUMEC, Faculdade de Ciências Médicas) e em grandes empresas (Usiminas, Açominas, BEMGE, Itambé, AMBEV); Prefeituras e outros Órgãos Públicos. ( www.excelline.net )

Gilberto Gomes

Comentários

19 Comments

  • Obrigado. Artigo muito útil!

    • Que bom que o artigo lhe foi útil.

      À disposição,

      Gilberto

  • Gostei muito do artigo,vou utilizar.

    Abraços

    • Que bom que o artigo lhe foi útil.

      À disposição,

      Gilberto

  • “Validação de Dados” nunca foi tão simples! Parabéns pelo belo e didático artigo.

    • Muito Obrigado Lívia,

      precisando de informações adicionais, basta solicitar.

      Gilberto

  • ESPETACULAR! BEM EXPLICADO E EXEMPLIFICADO! PARABÉNS, GILBERTO GOMES!!

    • Muito Obrigado Wilisa,

      precisando de informações adicionais, basta solicitar.

      À disposição,

      Gilberto

  • Muitooo bom Gilberto! Estou de olho….

    • Muito Obrigado Gustavo,

      precisando de informações adicionais, basta solicitar.

      À disposição,

      Gilberto

  • Espetacular mesmo hein????

  • Muito Obrigado Sérgio,

    O Excel é uma ferramenta surpreendente. Faz coisas que até Deus duvida. Precisando de informações adicionais, basta solicitar.

    À disposição,

    Gilberto

  • Ótimo, super útil
    só queria saber como faz pra encontrar automatico na outra célula
    exemplo de uma lista de códigos
    ac001 – vestido
    sp001 – sapato
    rp – blusina branca

    se eu digitar na célula de código o código por ex ac001 na celula do lado, que seria a descrição do item, tem como aparecer automático depois de um enter no código a descrição e o valor da peça??

    • Bom Dia Adriele,

      A função que deverá ser utilizada para resolver seu problema é a PROCV. Suponhamos que você tenha a seguinte lista:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7
      8
      9
      10

      Para criarmos uma fórmula que, a partir de um código informado em uma célula,
      busque valores relacionados (pesquisar, por exemplo, o valor para um determinado código,
      utilizamos utilizaremos a função PROCV da seguinte maneira:

      = PROCV ( ELEMENTO_PROCURADO ;
      MATRIZ_DE_CONSULTA ;
      NÚMERO_DA_COLUNA ;
      LISTA_ORDENADA)

      BUSCANDO O NOME DO PRODUTO:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7 CÓDIGO: PD001 =PROCV(B7; A3:C5; 2; FALSO)
      8
      9 OBS: A COLUNA DE NÚMERO 2 É A QUE CONTÉM O NOME
      10

      BUSCANDO O VALOR DO PRODUTO:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7 CÓDIGO: PD001 =PROCV(B7; A3:C5; 3; FALSO)
      8
      9 OBS: A COLUNA DE 3 É A QUE CONTÉM O VALOR
      10 OBS: O NÚMERO 3 NÃO TEM RELAÇÃO COM A COLUNA C
      11 ELA É A POSIÇÃO DO CAMPO DESEJADO NA MATRIZ,
      12 E NÃO NA PLANILHA

      Espero ter conseguido ajudar.

      À Disposição,

      Gilberto Gomes
      gilberot.gomes@excelline.net

    • Bom Dia Adriele,

      A função que deverá ser utilizada para resolver seu problema é a PROCV. Suponhamos que você tenha a seguinte lista:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7
      8
      9
      10

      Para criarmos uma fórmula que, a partir de um código informado em uma célula,
      busque valores relacionados (pesquisar, por exemplo, o valor para um determinado código,
      utilizamos utilizaremos a função PROCV da seguinte maneira:

      = PROCV ( ELEMENTO_PROCURADO ;
      MATRIZ_DE_CONSULTA ;
      NÚMERO_DA_COLUNA ;
      LISTA_ORDENADA)

      BUSCANDO O NOME DO PRODUTO:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7 CÓDIGO: PD001 =PROCV(B7; A3:C5; 2; FALSO)
      8
      9 OBS: A COLUNA DE NÚMERO 2 É A QUE CONTÉM O NOME
      10

      BUSCANDO O VALOR DO PRODUTO:

      A B C

      1 COD PRODUTO VALOR
      2 ————————-
      3 PD001 CAMISA 75
      4 PD002 CALÇA 85
      5 PD002 MEIA 15
      6
      7 CÓDIGO: PD001 =PROCV(B7; A3:C5; 3; FALSO)
      8
      9 OBS: A COLUNA DE 3 É A QUE CONTÉM O VALOR
      10 OBS: O NÚMERO 3 NÃO TEM RELAÇÃO COM A COLUNA C
      11 ELA É A POSIÇÃO DO CAMPO DESEJADO NA MATRIZ,
      12 E NÃO NA PLANILHA

      Espero ter conseguido ajudar.

      À Disposição,

      Gilberto Gomes
      gilberot.gomes@excelline.net

  • Bom dia,
    Criei uma lista suspensa a partir dos dados de uma coluna.
    Exemplo:
    1 Municípios
    2 Belo Horizonte
    3 Belo Horizonte
    4 Uberlândia
    5 Uberlândia
    Porém, há municípios duplicados. Eu preciso que apareça na minha lista suspensa apenas uma vez cada município.
    Como faço?

    Obrigada.

    • Oi Gabriela,

      neste caso você precisa ter uma lista sem repetições. Crie uma nova área com os municípios distintos, dê um nome a ela, e utilize como origem da sua validação. Qualquer dificuldade, se você quiser e precisar, mande seu arquivo para ggomesbrazil@yahoo.com que tentarei ajustar para você.

      Gilberto

You must be logged in to post a comment.

Busca

Patrocínio

Publicidade



Siga-nos!

Newsletter: Inscreva-se

Para se inscrever em nossa newsletter preencha o formulário.

Artigos Recentes