As fórmulas do Excel não funcionam: possíveis motivos e como corrigi-lo!

Se você trabalha com fórmulas no Excel, mais cedo ou mais tarde encontrará o problema em que as fórmulas do Excel não funcionam (ou dão o resultado errado).

Embora teria sido ótimo se houvesse apenas algumas razões possíveis para o mau funcionamento das fórmulas. Infelizmente, há muitas coisas que podem dar errado (e muitas vezes acontecem).

Mas, como vivemos em um mundo que segue o princípio de Pareto, se você verificar alguns problemas comuns, é provável que resolva 80% (ou talvez 90% ou 95% dos problemas em que as fórmulas não funcionam no Excel).

E neste artigo, destacarei os problemas comuns que são provavelmente a causa de seu As fórmulas do Excel não funcionam.

Então vamos começar!

Sintaxe incorreta da função

Deixe-me começar apontando o óbvio.

Cada função no Excel tem uma sintaxe específica - como o número de argumentos que pode aceitar ou o tipo de argumentos que pode aceitar.

E, em muitos casos, o motivo pelo qual as fórmulas do Excel não estão funcionando ou fornecem o resultado errado pode ser um argumento incorreto (ou argumentos ausentes).

Por exemplo, a função PROCV leva três argumentos obrigatórios e um argumento opcional.

Se você fornecer um argumento errado ou não especificar o argumento opcional (onde ele é necessário para que a fórmula funcione), você receberá um resultado errado.

Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo, onde você precisa saber a pontuação de Mark no Exame 2 (na célula F2).

Se eu usar a fórmula abaixo, obterei o resultado errado, porque estou usando o valor errado no terceiro argumento (um que pede o número do Índice da Coluna).

= PROCV (A2, A2: C6,2, FALSO)

Nesse caso, a fórmula calcula (pois retorna um valor), mas o resultado está incorreto (em vez da pontuação no Exame 2, dá a pontuação no Exame 1).

Outro exemplo em que você precisa ter cuidado com os argumentos é ao usar PROCV com a correspondência aproximada.

Visto que você precisa usar um argumento opcional para especificar onde deseja que VLOOKUP faça uma correspondência exata ou aproximada, não especificar isso (ou usar o argumento errado) pode causar problemas.

Abaixo está um exemplo em que tenho os dados das notas de alguns alunos e desejo extrair as notas do Exame 1 para os alunos da tabela à direita.

Quando uso a fórmula VLOOKUP abaixo, ocorre um erro para alguns nomes.

= PROCV (E2, $ A $ 2: $ C $ 6,2)

Isso acontece porque não especifiquei o último argumento (que é usado para determinar se devo fazer uma correspondência exata ou uma correspondência aproximada). Quando você não especifica o último argumento, ele faz uma correspondência aproximada automaticamente por padrão.

Como precisávamos fazer uma correspondência exata neste caso, a fórmula retorna um erro para alguns nomes.

Embora eu tenha tomado o exemplo da função PROCV, neste caso, isso é algo que pode ser aplicável a muitas fórmulas do Excel que também têm argumentos opcionais.

Leia também: Identificar erros usando a depuração de fórmulas do Excel

Espaços extras causando resultados inesperados

Espaços à esquerda e à direita são difíceis de descobrir e podem causar problemas quando você usa uma célula que os contém nas fórmulas.

Por exemplo, no exemplo abaixo, se eu tentar usar PROCV para buscar a pontuação de Marcos, recebo o erro # N / A (o erro não disponível).

Embora eu possa ver que a fórmula está correta e o nome ‘Mark’ está claramente na lista, o que não consigo ver é que há um espaço à direita na célula que tem o nome (na célula D2).

O Excel não considera o conteúdo dessas duas células o mesmo e, portanto, considera uma incompatibilidade ao buscar o valor usando PROCV (ou poderia ser qualquer outra fórmula de pesquisa).

Para corrigir esse problema, você precisa remover esses caracteres de espaço extras.

Você pode fazer isso usando qualquer um dos seguintes métodos:

1. Limpe a célula e remova todos os espaços iniciais / finais antes de usá-la em fórmulas
2. Use a função TRIM dentro da fórmula para garantir que quaisquer espaços à esquerda / à direita / duplos sejam ignorados.

No exemplo acima, você pode usar a fórmula abaixo para ter certeza de que funciona.

= PROCV (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Embora eu tenha pegado o exemplo VLOOKUP, esse também é um problema comum ao trabalhar com funções TEXT.

Por exemplo, se eu usar a função LEN para contar o número total de caracteres em uma célula, se houver espaços à esquerda ou à direita, eles também seriam contados e forneceriam o resultado errado.

Tirar / Como consertar: Se possível, limpe seus dados removendo quaisquer espaços iniciais / finais ou duplos antes de usá-los em fórmulas. Se você não pode alterar os dados originais, use a função TRIM em fórmulas para cuidar disso.

Usando cálculo manual em vez de automático

Esta configuração pode deixá-lo louco (se você não sabe o que está causando todos os problemas).

O Excel tem dois modos de cálculo - Automático e Manual.

Por padrão, o modo automático está habilitado, o que significa que caso eu use uma fórmula ou faça alguma alteração nas fórmulas existentes, ele automaticamente (e instantaneamente) faz o cálculo e me dá o resultado.

Este é o cenário a que todos estamos acostumados.

Na configuração automática, sempre que você fizer qualquer alteração na planilha (como inserir uma nova fórmula até mesmo algum texto em uma célula), o Excel recalcula tudo automaticamente (sim, tudo).

Mas, em alguns casos, as pessoas habilitam a configuração de cálculo manual.

Isso é feito principalmente quando você tem um arquivo Excel pesado com muitos dados e fórmulas. Nesses casos, você pode não querer que o Excel recalcule tudo quando você fizer pequenas alterações (pois pode levar alguns segundos ou até minutos) para que esse recálculo seja concluído.

Se você habilitar o cálculo manual, o Excel não fará o cálculo, a menos que você o force a fazê-lo.

E isso pode fazer você pensar que sua fórmula não é calculista.

Tudo que você precisa fazer neste caso é definir o cálculo de volta para automático ou forçar um recálculo pressionando a tecla F9.

Abaixo estão as etapas para alterar o cálculo de manual para automático:

1. Clique na guia Fórmula
2. Clique em Opções de Cálculo
3. Selecione Automático

Importante: caso você esteja alterando o cálculo de manual para automático, é uma boa ideia criar um backup de sua pasta de trabalho (caso isso faça sua pasta de trabalho travar ou faça o Excel travar)

Tirar / Como consertar: Se você notar que suas fórmulas não estão dando o resultado esperado, tente algo simples em qualquer célula (como adicionar 1 a uma fórmula existente. Depois de identificar o problema como aquele em que o modo de cálculo precisa ser alterado, faça um cálculo de força usando F9.

Excluindo linhas / colunas / células que levam a #REF! Erro

Uma das coisas que podem ter um efeito devastador em suas fórmulas existentes no Excel é quando você exclui qualquer linha / coluna que foi usada em cálculos.

Quando isso acontece, às vezes, o Excel ajusta a própria referência e verifica se as fórmulas estão funcionando bem.

E às vezes… Eu não posso.

Felizmente, uma indicação clara que você obtém quando as fórmulas quebram ao excluir células / linhas / colunas é o #REF! erro nas células. Este é um erro de referência que informa que há algum problema com as referências na fórmula.

Deixe-me mostrar o que quero dizer com um exemplo.

Abaixo, usei a fórmula SUM para adicionar as células A2: A6.

Agora, se eu excluir qualquer uma dessas células / linhas, a fórmula SUM retornará um #REF! erro. Isso acontece porque, quando excluí a linha, a fórmula não sabe ao que fazer referência agora.

Você pode ver que o terceiro argumento na fórmula tornou-se #REF! (que anteriormente se referia à célula que excluímos).

Tirar / Como consertar: Antes de excluir quaisquer dados que estão sendo usados ​​nas fórmulas, certifique-se de que não haja erros após a exclusão. Também é recomendável que você crie um backup do seu trabalho regularmente para se certificar de que sempre tem algo para se apoiar.

Colocação incorreta de parênteses (BODMAS)

À medida que suas fórmulas começam a ficar maiores e mais complexas, é uma boa ideia usar parênteses para deixar absolutamente claro qual parte pertence à outra.

Em alguns casos, você pode colocar os parênteses no lugar errado, o que pode gerar um resultado incorreto ou um erro.

E, em alguns casos, é recomendado usar parênteses para garantir que a fórmula entenda o que precisa ser agrupado e calculado primeiro.

Por exemplo, suponha que você tenha a seguinte fórmula:

=5+10*50

Na fórmula acima, o resultado é 505, pois o Excel primeiro faz a multiplicação e depois a adição (pois há uma ordem de precedência quando se trata de operadores).

Se você quiser que ele faça primeiro a adição e depois a multiplicação, você precisa usar a fórmula abaixo:

=(5+10)*50

Em alguns casos, a ordem de precedência pode funcionar para você, mas ainda é recomendável que você use o parêntese para evitar qualquer confusão.

Além disso, caso você esteja interessado, abaixo está a ordem de precedência para vários operadores frequentemente usados ​​em fórmulas:

Operador	Descrição	Ordem de Precedência
: (dois pontos)	Alcance	1
(espaço único)	Interseção	2
, (vírgula)	União	3
-	Negação (como em -1)	4
%	Percentagem	5
^	Exponenciação	6
* e /	Multiplicação e divisão	7
+ e -	Adição subtração	8
&	Concatenação	9
= =	Comparação	10

Tirar / Como consertar: Sempre use parênteses para evitar qualquer confusão, mesmo se você souber a ordem de precedência e estiver usando-a corretamente. Ter parênteses facilita a auditoria de fórmulas.

Uso incorreto de referências celulares absolutas / relativas

Quando você copia e cola fórmulas no Excel, ele ajusta automaticamente as referências. Às vezes, isso é exatamente o que você deseja (principalmente quando você está copiando e colando fórmulas na coluna), e às vezes você não quer que isso aconteça.

Uma referência absoluta é quando você fixa uma referência de célula (ou referência de intervalo) para que ela não mude quando você copia e cola fórmulas, e uma referência relativa é aquela que muda.

Você pode ler mais sobre referências absolutas, relativas e mistas aqui.

Você pode obter um resultado incorreto caso se esqueça de alterar a referência para um absoluto (ou vice-versa). Isso é algo que acontece frequentemente comigo quando estou usando fórmulas de pesquisa.

Deixe-me mostrar-lhe um exemplo.

Abaixo, tenho um conjunto de dados onde desejo obter a pontuação no Exame 1 para os nomes na coluna E (um caso de uso simples de VLOOKUP)

Abaixo está a fórmula que uso na célula F2 e depois copio para todas as células abaixo dela:

= PROCV (E2, A2: B6,2,0)

Como você pode ver, esta fórmula dá erro em alguns casos.

Isso acontece porque eu não bloqueei o argumento da matriz da tabela - é A2: B6 na célula F2, enquanto deveria ter sido $ A $ 2: $ B $ 6

Por ter esses cifrões antes do número da linha e do alfabeto da coluna em uma referência de célula, estou forçando o Excel a manter essas referências de célula fixas. Portanto, mesmo quando copio esta fórmula, a matriz da tabela continuará a se referir a A2: B6

Dica Profissional: Para converter uma referência relativa em absoluta, selecione essa referência de célula dentro da célula e pressione a tecla F4. Você notaria que ele muda adicionando os cifrões. Você pode continuar pressionando F4 até obter a referência que deseja.

Referência incorreta a nomes de planilhas / pastas de trabalho

Ao se referir a outras planilhas ou pastas de trabalho em uma fórmula, você precisa seguir um formato específico. E caso o formato esteja incorreto, você obterá um erro.

Por exemplo, se eu quiser me referir à célula A1 na Planilha2, a referência seria = Folha2! A1 (onde há um sinal de exclamação após o nome da folha)

E no caso de haver várias palavras no nome da planilha (digamos que sejam dados de exemplo), a referência seria = ’Exemplo de dados’! A1 (onde o nome é colocado entre aspas simples seguidas por um sinal de exclamação).

Caso você esteja se referindo a uma pasta de trabalho externa (digamos que você esteja se referindo à célula A1 na ‘Folha de Exemplo’ na pasta de trabalho chamada ‘Exemplo de Pasta de Trabalho’), a referência será como mostrado abaixo:

= '[Exemplo de pasta de trabalho.xlsx] Folha de exemplo'! $ A $ 1

E no caso de você fechar a pasta de trabalho, a referência mudaria para incluir todo o caminho da pasta de trabalho (conforme mostrado abaixo):

= 'C: \ Users \ sumit \ Desktop \ [Exemplo de pasta de trabalho.xlsx] Folha de exemplo'! $ A $ 1

No caso de você acabar mudando o nome da pasta de trabalho ou da planilha a que a fórmula se refere, isso vai te dar um #REF! erro.

Leia também: Como encontrar links externos e referências no Excel

Referências circulares

Uma referência circular é quando você se refere (direta ou indiretamente) à mesma célula onde a fórmula está sendo calculada.

Abaixo está um exemplo simples, onde uso a fórmula SOMA na célula A4 enquanto a utilizo no próprio cálculo.

= SOMA (A1: A4)

Embora o Excel mostre um prompt informando sobre a referência circular, ele não o fará para todas as instâncias. E isso pode dar o resultado errado (sem qualquer aviso).

Caso suspeite de uma referência circular em jogo, você pode verificar quais células a possuem.

Para fazer isso, clique na guia Fórmula e no grupo ‘Auditoria de fórmula’, clique no ícone suspenso Verificação de erros (a pequena seta apontando para baixo).

Passe o cursor sobre a opção Referência circular e ela mostrará a célula que tem o problema de referência circular.

Células formatadas como texto

Se você se encontrar em uma situação em que, assim que digitar a fórmula pressionando Enter, verá a fórmula em vez do valor, é um caso claro de a célula sendo formatada como texto.

Quando uma célula é formatada como texto, ela considera a fórmula como uma string de texto e a mostra como está.

Não o obriga a calcular e dar o resultado.

E tem uma solução fácil.

1. Altere o formato para ‘Geral’ de ‘Texto’ (está na guia Página Inicial no grupo Numbers)
2. Vá para a célula que contém a fórmula, entre no modo de edição (use F2 ou clique duas vezes na célula) e pressione Enter

Caso as etapas acima não resolvam o problema, outra coisa a verificar é se a célula tem um apóstrofo no início. Muitas pessoas adicionam um apóstrofo para converter fórmulas e números em texto.

Se houver um apóstrofo, você pode simplesmente removê-lo.

Texto sendo convertido automaticamente em datas

O Excel tem o péssimo hábito de converter algo parecido com uma data em uma data real. Por exemplo, se você inserir 1/1, o Excel o converterá em 01 de janeiro do ano atual.

Em alguns casos, isso pode ser exatamente o que você deseja e, em alguns casos, isso pode funcionar contra você.

E como o Excel armazena os valores de data e hora como números, assim que você inserir 1/1, ele os converterá em um número que representa 1º de janeiro do ano atual. No meu caso, quando faço isso, ele o converte no número 43831 (para 01-01-2020).

Isso pode atrapalhar suas fórmulas se você estiver usando essas células como um argumento em uma fórmula.

Como consertar isto?

Novamente, não queremos que o Excel escolha automaticamente o formato para nós, então precisamos especificar claramente o formato nós mesmos.

Abaixo estão as etapas para alterar o formato de texto para que ele não converta automaticamente o texto em datas:

1. Selecione as células / intervalo onde deseja alterar o formato
2. Clique na aba Home
3. No grupo Número, clique na lista suspensa Formato
4. Clique no Texto

Agora, sempre que você inserir algo nas células selecionadas, será considerado como texto e não será alterado automaticamente.

Nota: As etapas acima só funcionam para os dados inseridos após a alteração da formatação. Isso não mudará nenhum texto convertido até a data anterior à alteração da formatação.

Outro exemplo em que isso pode ser realmente frustrante é quando você insere um texto / número com zeros à esquerda. O Excel remove automaticamente esses zeros à esquerda, pois os considera inúteis. Por exemplo, se você inserir 0001 em uma célula, o Excel irá alterá-lo para 1. Caso queira manter esses zeros à esquerda, siga as etapas acima.

Linhas / colunas ocultas podem gerar resultados inesperados

Este não é um caso de fórmula dando a você o resultado errado, mas de usar a fórmula errada.

Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo e eu queira obter a soma de todas as células visíveis na coluna C.

Na célula C12, usei a função SUM para obter o valor total de venda de todos esses registros fornecidos.

Até agora tudo bem!

Agora, aplico um filtro à coluna do item para mostrar apenas os registros de vendas da impressora.

E aqui está o problema - a fórmula na célula C12 ainda mostra o mesmo resultado - ou seja, a soma de todos os registros.

Como eu disse, a fórmula não está dando resultado errado. Na verdade, a função SUM está funcionando bem.

O problema é que usamos a fórmula errada aqui.

A função SUM não pode contabilizar os dados filtrados e fornecer o resultado para todas as células (ocultas ou visíveis). Se você deseja obter apenas a soma / contagem / média das células visíveis, use as funções SUBTOTAL ou AGGREGATE.

Principal lição - Compreender o uso correto e as limitações de uma função do Excel.

Estas são algumas das causas comuns que tenho visto onde o seu As fórmulas do Excel podem não funcionar ou fornecer resultados inesperados ou incorretos. Tenho certeza de que há muitos outros motivos para uma fórmula do Excel não funcionar ou ser atualizada.

Caso você saiba de algum outro motivo (talvez algo que o irrite com frequência), avise-me na seção de comentários.

Espero que você tenha achado este tutorial útil!