Erros de fórmula no Excel e soluções

“Os erros são uma oportunidade para melhorar”. Em qualquer tarefa ou sistema, ocorrem erros. O Excel não é exceção. Ao tentar fazer algo com uma fórmula, você encontrará vários tipos de erros do Excel. Esses erros podem fazer com que sua fórmula / painel / relatórios seja um desperdício absoluto. E se você não sabe por que um tipo específico de erro ocorreu, pode ser necessário gastar horas para resolvê-los.

Ao trabalhar no Excel, encontrei muitos erros do Excel. Com alguma dificuldade e pesquisas no Google, contornei esses erros. Neste artigo, explicarei alguns erros comuns e irritantes do Excel que ocorrem no Excel. Discutiremos por que esses erros ocorrem e como resolvê-los.

O que são erros de fórmula do Excel
A aplicação de uma fórmula que resulta em erros definidos do Excel (#NA, #VALUE, #NAME etc.) é chamada de erros de fórmula do Excel. Esses erros são detectados pelo excel e impressos nas planilhas. As razões para esses erros podem ser valores indisponíveis, tipos incorretos de argumentos, divisão por 0, etc. Eles são fáceis de detectar e corrigir.

Os erros lógicos não são detectados pelo Excel e são mais difíceis de corrigir. Inconsistência nos dados, entrada incorreta de dados, erros humanos, etc. são razões comuns por trás desses erros. Eles também podem ser consertados, mas exigem tempo e esforço. É melhor preparar seus dados perfeitamente antes de operar neles.
Captura de erros de fórmula do Excel:

Existem algumas funções dedicadas no Excel para detectar e lidar com um tipo específico de erros (como a função ISNA). Mas a função ISERROR e a função IFERROR são duas funções que podem capturar qualquer tipo de erro do Excel (exceto lógico).

Erro Excel #NA
#NA erro ocorre no Excel quando um valor não é encontrado. Significa simplesmente NÃO DISPONÍVEL. O erro #NA é frequentemente encontrado na função VLOOKUP do Excel.

Na imagem acima, estamos obtendo o erro #NA quando procuramos por "Divya" na coluna A. Isso ocorre porque "Divya não está na lista.

Resolvendo o erro #NA

Se você tiver certeza de que o valor de pesquisa deve existir na lista de pesquisa, a primeira coisa que você deve fazer é verificar o valor de pesquisa. Verifique se os valores de pesquisa estão digitados corretamente. Se não, corrija-o.

Em segundo lugar, você pode fazer uma correspondência parcial com VLOOKUP ou qualquer função de pesquisa. Se você tiver certeza de que algumas partes do texto devem corresponder, use isso.
Se você não tiver certeza se o valor existe ou não, ele pode ser usado para verificar se um valor existe ou não na lista. Na imagem acima, podemos dizer que Divya não está na lista.

Se você deseja detectar o erro #NA e imprimir ou fazer outra coisa em vez de imprimir o erro #NA, você pode usar a função Excel ISNA. A função ISNA retorna TRUE se uma função retornar o erro #NA. Usando isso, podemos evitar o erro #NA. ISNA funciona incrivelmente com a função VLOOKUP. Confira aqui.

Erro Excel #VALUE
O #VALUE ocorre quando o argumento fornecido não é de um tipo compatível. Por exemplo, se você tentar adicionar dois textos usando o operador aritmético de adição (+), receberá um erro #VALUE. A mesma coisa acontecerá se você tentar obter um ano de um formato de data inválido usando a função YEAR.

Como corrigir um erro #VALUE?

Primeiro, confirme o tipo de dados ao qual você está se referindo. Se sua função requer um número, certifique-se de referir-se a um número. Se um número for formatado como texto, use a função VALUE para convertê-lo em um número. Se uma função exigir texto (como a função DATEVALUE) e você se referir a um número ou tipo de data, converta-os em texto.

Se você espera que possa haver um erro #VALUE e deseja capturá-los, você pode usar ISERR, ISERROR ou IFERROR para capturar e fazer outra coisa.

Erro Excel #REF
O 'ref' em #REF significa referência. Este erro ocorre quando uma fórmula se refere a um local que não existe. Isso acontece quando excluímos células de intervalos aos quais uma fórmula também se refere.

No gif abaixo, a fórmula da soma refere-se a A2 e B2. Quando excluo A2, a fórmula se transforma em um erro #REF.

Resolva o erro #REF do Excel:
O melhor é ter cuidado antes de excluir células nos dados. Certifique-se de que nenhuma fórmula se refere a essa célula.

Se você já tiver o erro #REF, rastreie e exclua-o da fórmula.

Por exemplo, depois de obter um erro #REF, sua fórmula ficará assim.

= A2 + # REF!

Você pode simplesmente remover o #REF! A partir da fórmula para obter uma fórmula sem erros. Se você quiser fazer isso em massa, use o recurso localizar e substituir. Pressione CTRL + H para abrir localizar e substituir. Na caixa de localização, escreva #REF. Deixe a caixa de substituição vazia. Clique no botão Substituir tudo.

Se você quiser reajustar a referência a uma nova célula, faça-o manualmente e substitua o #REF! Com essa referência válida.

Erro do Excel #NAME
O #NOME ocorre no excel quando não consegue identificar um texto em uma fórmula. Por exemplo, se você digitar incorretamente o nome de uma função, o Excel mostrará o erro #NOME. Se uma fórmula se referir a um nome que não existe na planilha, ela mostrará o erro #NOME.

Na imagem acima, a célula B2 possui a fórmula = PODERES (A2,2). POWERS não é uma função válida no Excel, portanto, retorna um erro #NOME.

Na célula B3, temos = SUM (números). O SUM é uma função válida do Excel, mas o intervalo denominado "números" não existe na planilha. Portanto, o Excel retorna #NOME? Erro.

Como evitar o erro #NOME no excel?

Para evitar o erro #NOME no Excel, sempre soletre os nomes das funções corretamente. Você pode usar as sugestões do Excel para ter certeza de que está usando uma função válida. Sempre que digitarmos caracteres após o sinal de igual, o Excel mostra funções e intervalos nomeados na planilha, a partir desse (s) caractere (s). Role para baixo até o nome da função ou nome do intervalo na lista de sugestões, pressione tab para usar essa função.

Excel # DIV / 0! Erro
Como o nome sugere, esse erro ocorre quando uma fórmula resulta na divisão por zero. Este erro também pode ocorrer quando você exclui algum valor de uma célula da qual depende uma fórmula de divisão.

Como resolver # DIV / 0! Erro.

Isso pode ser facilmente resolvido tomando cuidado com os dados e marcando se uma fórmula resultará em # DIV / 0! erro. Aqui está um exemplo de fórmula para fazer isso.

= SE (B2 = 0, A2, A2 / B2)

Teremos erro DIV / 0 apenas se o divisor for 0 ou estiver em branco. Portanto, verificamos o divisor (B2), se for zero, imprimamos A2, caso contrário, divida A2 com B2. Isso também funcionará com células em branco.

Erro Excel #NUM

Este erro ocorre quando um número não pode ser exibido na tela. O motivo pode ser que o número é muito pequeno ou muito grande para ser exibido. Outra razão pode ser que um cálculo não pode ser executado com o número fornecido.

= SQRT (ABS (A3))

Isso retornará 4. Se você quiser obter o valor negativo, use o sinal negativo antes da função. Então, é claro, você pode usar a função de tratamento de erros, é claro.
Para resolver o erro #NUM, temos um artigo dedicado. Você pode checar aqui.

#NULL Erro no Excel
Este é um tipo raro de erro. #NULL erro causado por referência de célula incorreta. Por exemplo, se você deseja fornecer a referência de um intervalo A2: A5 na função SUM, mas por engano você digita A2 A5. Isso irá gerar o erro #NULL. Como você pode ver na imagem abaixo, a fórmula retorna o erro #NULL.
Se você substituir o espaço pela coluna (:), o erro #NULL desaparecerá e você obterá a soma de A2: A5. Se você substituir o espaço por vírgula (,), obterá a soma de A2 e A5.

Como resolver o erro #NULL?

Como sabemos, o erro #NULL é causado por erro de digitação. Para evitá-lo, tente selecionar intervalos usando o cursor em vez de digitá-lo manualmente.

Haverá momentos em que você terá que digitar o endereço do intervalo no teclado. Sempre tome cuidado com a coluna do símbolo de conexão (:) ou vírgula (,) para evitar.

Se você tiver o erro #NULL existente, verifique as referências. Muito provavelmente você esqueceu a coluna (:) ou vírgula (,) entre duas referências de célula. Substitua-os pelo símbolo apropriado e você está pronto para ir.
###### Erro no Excel
Às vezes pensamos que esse erro é causado por espaço insuficiente para mostrar um valor, mas não é o caso. Neste caso, você pode simplesmente estender a largura da célula para visualizar o valor na célula. Não vou chamar isso de erro.
Na verdade, esse erro é causado quando tentamos mostrar um valor de tempo negativo (não existe tempo negativo). Por exemplo, se tentarmos subtrair 1 de 12:21:00 PM, ele retornará ######. No excel, a primeira data é 01/01/1900 00:00. Se você tentar subtrair o tempo que vai antes disso, o Excel mostrará o erro ######. Quanto mais você expandir a largura, mais # você obterá. Eu elaborei neste artigo.

Como evitar ###### erro do Excel?
Antes de fazer cálculos aritméticos no Excel com o valor do tempo, mantenha essas coisas em mente.

O valor mínimo de tempo é 01/01/1900 00:00. Você não pode ter uma data válida antes disso no excel
1 é igual a 24 horas (1 dia) no excel. Ao subtrair horas, minutos e segundos, converta-os em valores equivalentes. Por exemplo, para subtrair 1 hora de 12:21 PM, você precisa subtrair 1/24 dela.

Rastreando Erros no Excel
Agora sabemos o que são fórmulas comuns do Excel e por que ocorrem. Também discutimos qual pode ser a solução possível para cada tipo de erro do Excel.
Às vezes, em relatórios do Excel, obtemos erros e não podemos saber de onde o erro está realmente ocorrendo. É difícil resolver esse tipo de erro. Para rastrear esses erros, o Excel fornece a funcionalidade de rastreamento de erros na guia de fórmula.

Eu discuti o rastreamento de erros no Excel em detalhes.

Resolvendo Erros Lógicos na Fórmula

Erros lógicos são difíceis de encontrar e resolver. Nenhuma massagem mostrada pelo Excel quando você tem um erro lógico em sua função. Tudo parece bem. Mas só você sabe que algo está errado aí. Por exemplo, ao obter a porcentagem de uma parte do todo, você dividiria a parte pelo total (= (parte / total) * 100). Deve ser sempre igual ou inferior a 100%. Quando você obtém mais de 100%, sabe que algo está errado.

Este foi um erro lógico simples. Mas às vezes seus dados vêm de várias fontes; nesse caso, fica difícil resolver problemas lógicos. Uma maneira é avaliar sua fórmula.

Na guia fórmula, a opção avaliar fórmula está disponível. Selecione sua fórmula e clique nela. Cada etapa do seu cálculo será mostrada a você que leva ao seu resultado final. Aqui você pode verificar onde ocorreu o problema ou onde o cálculo deu errado.

Você também pode rastrear dependentes e precedentes para ver de quais referências sua fórmula depende e quais fórmulas dependem de uma célula perticular.

Então, sim pessoal, esses são alguns tipos de erros comuns que todos os usuários do Excel enfrentam. Aprendemos por que ocorre cada tipo de erro e como podemos evitá-los. Aprendemos também sobre a função dedicada de manipulação de erros do Excel. Neste artigo, temos links para páginas relacionadas que discutem o problema em detalhes. Você pode verificá-los. Se você tiver algum tipo específico de erro que o esteja incomodando, mencione-o na seção de comentários abaixo. Você obterá a solução de maneira ordenada.