terça-feira, 29 de maio de 2012

Dr. Planilha: Como Ocultar Erros em uma Fórmula do #Excel #dica

Realmente é um tanto indigesto quando uma fórmula nos retorna valores inválidos gerando um erro no Excel, como os listados a seguir: #DIV/0!, #N/D, #NOME?, #NULO!, #NÚM!, #REF! e #VALOR! . Geralmente isso ocorrerá em situações onde as células envolvidas no cálculo não foram preenchidas ainda ou, em determinada situação, não possuem valores válidos para o cálculo em questão. O fato é que isso é mais comum do que parece e deixa nossas planilhas verdadeiramente feias e anti-profissionais.

Eu tenho como prática o uso de duas funções como "remédio" para tratar e ocultar os erros do Excel, a função "SE" e a função "ÉERROS", trabalhando conjuntamente. Explicando rapidamente o que são estas duas funções:

ÉERROS: essa função retorna VERDADEIRO quando informamos à ela uma célula ou função que gera um erro.

SE: esta função nos permite realizar testes e tomar ações em função do resultado, VERDADEIRO ou FALSO, dos testes.

Resumindo, o que faremos é o seguinte, usaremos "ÉERROS" para sinalizar o erro e usando o "SE" trataremos  a situação incômoda alimentando a célula com um valor mais informativo, no lugar dos códigos de erro.

Usarei uma situação muito comum do quotidiano para ilustrar nosso artifício, cálculo de percentual. Em um relatório de vendas por vendedor compararemos em percentual quanto as vendas do mês atual variaram em função das vendas do mesmo vendedor no período anterior. Vejamos como isso ficaria:

A fórmula usada para calcular a variação das vendas do vendedor José (para os demais é só copiar e colar):

=(C2/B2)-1

Observe que nosso #DIV/0! apareceu na variação das vendas do José, obviamente porque nesse caso ele não teve vendas no período anterior e por isso temos uma divisão por zero. Mas essa é uma situação mais que comum, o vendedor pode ter começado a trabalhar na empresa no mês de maio, ou esteve de férias no mês de abril.

Pois bem, trataremos o erro da seguinte forma, alimentaremos a função ÉERROS com nossa fórmula de variação e isso nos retornará VERDADEIRO ou FALSO. Esta ainda não é a solução definitiva, mas vamos fazer um teste para ver como ficaria.

=ÉEROS(D2)

Adicione essa fórmula na célula E2 e depois copie e cole para todos os vendedores e vejamos o resultado, deve ficar parecido com o que é apresentado na imagem a seguir.

Veja só, ÉERROS identificou que há um problema no resultado do cálculo da célula D2 e nas demais não. Inicialmente era isso que queríamos, agora vamos usar essa informação para tratar o resultado final que será exibido na célula. E para mantermos um histórico que que estamos fazendo e facilitar o entendimento da solução, vamos criar uma nova coluna chamada "Variação OK", que conterá nosso cálculo de variação já tratado.

Antes de começar a tratar o resultado final, vamos entender melhor como trabalha a função "SE", dissecando-a.


teste_lógico é uma pergunta que deverá retornar como resposta os valores VERDADEIRO ou FALSO. Nós já temos nossa pergunta que foi construída usando a função ÉERROS.


[valor_se_verdadeiro] é o que desejamos que seja preenchido na célula caso nosso teste lógico retorne VERDADEIRO. No nosso caso, VERDADEIRO significa que a variação está com erro, então temos que mostrar alguma coisa diferente de #DIV/0!, que tal um hífen para ilustrar que a comparação não pode ser feita para aqueles dois períodos?


[valor_se_falso] é o que queremos mostrar caso nosso teste lógico retorne FALSO, ou seja, a variação não está com erro.

Para finalizar, veja como fica a fórmula pronta para o vendedor José, preencha célula F2 com o seguinte conteúdo:

=SE(ÉERROS(D2);"-";D2) 

Para replicar para os demais vendedores é só copiar e colar nas células abaixo que representam os demais vendedores. O resultado final ficará parecido com o que vemos abaixo.

Perfect! Ou quase... é claro que não queremos manter todas essas colunas a mais, apenas a que apresenta a variação. Isso é fácil de resolver, faremos uma pequena modificação na nossa fórmula e poderemos colar na célula D2.

O que foi que fizemos? Apenas substituímos o "D2" da fórmula final que trata o erro, pela fórmula que está na célula D2, ou seja, ao invés de usarmos o nome da célula (D2) para testar o cálculo, agora estamos usando o próprio cálculo que está na célula D2. Precisamos fazer isso porque iremos substituir o conteúdo da célula D2. Lembre-se de depois de preencher a célula D2 com a nova fórmula, copiar e colar nas células abaixo para replicar para os demais vendedores.

Após fazer essa mudança nossa tabela deverá se assemelhar com a figura abaixo.

Está feito! Para o caso de surgir alguma dúvida quanto aos passos que executamos acima, deixarei a planilha de exemplo completa com todas as colunas, ou se preferir, sinta-se à vontade para fazer perguntas através dos comentários.

Exemplo Ocultar Erros usando ÉERROS.xls (Excel 2003)
Exemplo Ocultar Erros usando ÉERROS.xlsx (Excel 2007/2010)

Um grande abraço do Dr. Planilha!


10 comentários:

  1. Estou usando uma formula de variação em porcentagem, só que está dando o erro DiV!0, como é q eu faço pra acertar?

    ResponderExcluir
  2. Estou usando uma formula de variação em porcetagem
    cujo é esta: =ARRED((G15-D15)/D15;4) está dando erro DIV!0, como é q eu faço pra acertar?

    ResponderExcluir
    Respostas
    1. Olá amigo,

      Ao que me parece, provavelmente sua célula D15 deve estar vazia ou com valor igual a zero. Se essa é uma situação que pode acontecer e você quer evitar o DIV!0, uma forma de resolver isso é usando o exemplo citado neste artigo.

      Sua fórmula alterada ficaria da seguinte forma:

      =SE(ÉERROS(ARRED((G15-D15)/D15;4));0;ARRED((G15-D15)/D15;4))

      O que fizemos aqui foi dizer para o Excel que:

      1. Se a fórmula >>ARRED((G15-D15)/D15;4)<< resultar em um erro (DIV!0, por exemplo), a célula deverá ser preenchida com o valor 0 (zero).

      2. Se a fórmula >>ARRED((G15-D15)/D15;4)<< não resultar em erro, a célula deverá ser preenchida com o resultado da própria fórmula >>ARRED((G15-D15)/D15;4)<<.

      Faça o teste aí e me diga se funcionou.

      Abraços do Dr. Planilha!

      Excluir
  3. Muito Bom, resolvi meu problema. Obrigado!!!

    ResponderExcluir
  4. o que significa o "-1", apos a formula da planilha ?

    ResponderExcluir
    Respostas
    1. Olá Gustavo!

      O -1 foi usado para calcular o percentual de variação. Vamos usar como exemplo a linha 3 da vendedora Maria.

      O valor vendido no mês anterior foi de R$ 1500 e no mês atual de R$ 1000, ou seja, tivemos uma queda nas vendas em comparação com o mês anterior.

      Para calcular a diferença em percentual entre os dois meses, dividimos o valor anterior pelo do mês atual, então temos: C3/B3 ou 1000/1500 que dá 0,6666. Isso significa que 1000 equivale a 0,67% de 1500.

      Mas o que queremos saber não é quanto o mês atual equivale em comparação com o mês anterior, queremos saber QUAL A DIFERENÇA entre os dois períodos. Por isso adicionamos -1 à equação, para retirar a parte dos 1000 dos 100%. É -1 e não 1- porque precisamos que, em caso de queda, o percentual seja negativo.

      Espero que isso tenha clareado sua dúvida.

      Se gostou do blog, não deixe de compartilhar com seus amigos e colegas.

      Abraços do Dr. Planilha!

      Excluir
  5. O melhor metodo para ocultar o erro é utilizar a formula SEERRO. Ela pode ser usada por qualquer tipo de formula...
    Se vc por exemplo tem uma formula do procv --->> =PROCV(A1;A1:B5;2;1). você poderá colocar o SEERRO nela, desta maneira ----> =SEERRO(PROCV(A1;A1:B5;2;1);"0"). Perceba que ao final da formula procv, foi acrescentado ;"0") que é onde será retornado o valor caso seja erro.. vc poderá substituir o zero por qualquer informação, -, 0,.. Abraços

    ResponderExcluir
    Respostas
    1. Olá Paulo!

      De fato o SEERRO() é uma boa opção, especialmente porque economiza no tamanho da fórmula e, consequentemente, facilita a leitura e manutenção. Aproveitarei o gancho do seu comentário para adicionar ao artigo o seu uso como uma alternativa para resolver o mesmo problema.

      O único cuidado no seu uso é que ela só é compatível com o Office 2007/2010 em diante, diferente da função ÉERROS(), que seria compatível com ambas.

      Um grande abraço e obrigado pela contribuição e por prestigiar o blog!

      Excluir
  6. Estou chegando a pouco no Blog, gostei de seu contéudo!
    Utilizei a fórmula =SEERRO(ABS(H14/D14)-1;"-") para obter a variação percentual entre dois meses. Só que quando a Mês 1 está "zerado" e o mês seguinte contêm algum valor...ele não me dá a variação, me aponta apenas o valor "0".
    Como incremento a fórmula para que ela me dê essa variação...no meu caso, seria um acréscimo de 100%.
    Att.

    ResponderExcluir

Use esse espaço para enviar suas dúvidas ou fazer críticas e sugestões. Todos os comentários serão postados após passarem pela moderação do Dr. Planilha para que seja mantido o alto nível do conteúdo do blog.

Related Posts Plugin for WordPress, Blogger...