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!


Related Posts Plugin for WordPress, Blogger...