“Oh Meu Deus” excelentes fórmulas não funcionarem no meu relatório.
Sons familiares direito?
Se assim for, não se preocupe, você é apenas um entre muitos excelentes utilizadores que enfrentam este problema com muita frequência.
P>P>P>P>Você construiu um modelo excel para Previsão de Vendas usando várias lógicas com fórmulas bastante complicadas.
E quando tenta usar esse modelo, vê algumas das suas fórmulas ou não funcionam ou parecem quebradas.
Basicamente, duas coisas podem acontecer aqui:
Uma pode querer dizer algo em voz alta .
Segundo, esforça-se mais e corrige todas as fórmulas.
P>Então, para evitar todo este incómodo, escrevemos este artigo aprofundado cobrindo a maioria das razões para as fórmulas de excelência não funcionarem.
E como corrigi-las.
Fórmulas de excelência não funcionarem: Porque & Como corrigi-las
De acordo com o artigo do Washington post sobre erros do excel, “Os investigadores australianos descobriram que aproximadamente 1 em cada 5 destes artigos incluíam erros nas suas listas de genes que se deviam ao Excel convertendo automaticamente nomes de genes para coisas como datas de calendário ou números aleatórios.”
A pesquisa original foi conduzida pela Journal Genome Biology descobriu que existe uma tendência crescente ano após ano em termos de um número de erros.
P>Pode descarregar materiais de investigação completos do website SourceForge (Open Source) para leitura posterior.
O trabalho de investigação acima referido pode não estar directamente ligado a fórmulas que não funcionam em excel, mas há um ponto aqui:
Muito bem construído &o software testado como o MS Excel pode correr mal em termos de compreensão dos nossos dados & me?
Então, aqui estão as nossas 10 principais razões
#1 Opções de Cálculo
Mudar a opção de Cálculo para ‘manual’ é uma melhor prática, mas precisa de ter muito cuidado ao usar.
Por defeito, a opção de Cálculo será definida para automático no Excel.
Mas há casos em que poderá ter transformado isso em ‘manual’ para acelerar o processo.
Se activar esta opção ‘ON’ então as suas fórmulas não serão actualizadas automaticamente, isto por sua vez aumenta a velocidade de processamento da folha de cálculo.
Após ter terminado, precisa de clicar em Guardar ou Actualizar a folha de cálculo para recalcular. Se ainda não o fez, então acabará com resultados incorrectos.
Como corrigir este problema:
Cabeçar para Fórmulas → Cálculo → Clique em Calculate now/Calculate Sheet option.
P>Pode, em alternativa, usar a tecla de atalho F9 para actualizar a pasta de trabalho.
Aqui está o nosso guia vídeo sobre opções de cálculo Excel.
Dicas:
- Faça um hábito de pressionar Ctl+S
- Não utilize esta opção para cada livro de trabalho, a menos que tenha um ficheiro enorme com muitas fórmulas
- Esta opção é específica da pasta de trabalho, pelo que necessita de ligar/desligar todas as pastas de trabalho que pretende utilizar
#2 fórmula do Excel não calculando apenas a fórmula
Mostrar fórmulas é uma opção em excel para exibir todas as fórmulas da pasta de trabalho com um único clique.
Esta opção é muito útil para descobrir todas as fórmulas numa folha de trabalho.
Dizendo que, precisamos de ter pouco cuidado ao usar a opção mostrar fórmulas, caso contrário, pode acabar com algo como abaixo.
Pode ver que a fórmula Today está a ser exibida como uma fórmula em vez de mostrar a data real?
O mesmo acontece com as fórmulas de Randbetween também.
p>
Como resolver este problema:
É muito simples:
Chegar a Fórmulas → Sob Auditoria de Fórmulas → Clique em “Mostrar Fórmulas” (Com o primeiro clique, as fórmulas aparecerão e com o segundo clique, elas esconder-se-ão atrás das células e começarão a funcionar)
Também ler: 51 best excel tips and tricks
#3 Extra Space(s) is an extra headache
You have been extracting data using CSV (comma separated values) format, then you’re data mostly exposed to unwanted spaces.
Sabes que os espaços extra causam muitos problemas ao usar a fórmula Vlookup?
Por vezes é bastante difícil identificar estes espaços.
Por exemplo, digamos que se quiser fazer Vlookup usando o nome do funcionário como base, então o seu nome de funcionário parece-se com abaixo
P>Deu-se conta?
Embora ambos os nomes pareçam semelhantes ao nosso olho nu, existe um espaço em branco no segundo nome.
Isso não satisfaria os critérios exactos de correspondência na fórmula Vlookup, resultando assim em erro #N/A.
Como corrigir este problema:
O problema do(s) espaço(s) extra pode ser corrigido usando a fórmula Trim.
Em geral, a fórmula Trim descobrirá mais do que um espaço entre palavras e apagará.
Dicas:
- É sempre boa ideia usar a fórmula de Trim antes do Vlookup em ambas as tabelas (lookup & tabela de dados)
- A função Trim irá ignorar espaços em branco entre letras (só funcionará com palavras)
#4 Livrar-se de caracteres não imprimíveis/ocultos
similar aos espaços principais, Os caracteres não imprimíveis são também uma das razões para as fórmulas de excelência não funcionarem.
No caso de ter dados com estes caracteres, é melhor retirá-los antes de continuar a trabalhar.
Como resolver este problema:
Similiar para aparar, pode usar a fórmula LIMPAR para apagar estes caracteres.
#5 Formatação Excel – não tente comparar Maçãs com Laranjas
É muito importante ter uma formatação correcta antes de usar fórmulas como Vlookup, Hlookup, e Match & Índice.
Números formatados como um texto é uma das razões comuns para as fórmulas não funcionarem em excel.
P>Embora pareçam números mas não são.
Por exemplo, no lado esquerdo, pode ver o ID de cliente parecido com números.
Mas, se consultar a secção de formatação acima, pode ver que foram formatados como texto.
Isso, por sua vez, desencadeará #N/A erro ao adicionar a fórmula Vlookup.
A pior parte é que é um pouco difícil de identificar.
Como corrigir este problema:
Certifique-se de que todos os seus números estão formatados como números em vez de texto. Vale a pena re-formatar a coluna de pesquisa antes de usar a fórmula Vlookup.
Tips:
- Em geral, os números serão sempre alinhados ao lado direito da célula (mas no nosso exemplo acima não estão)
- li>Por vezes pode ver uma pequena ponta verde no canto superior esquerdo, que indica algo de errado com essa célula
#6 Referências circulares (Erros devidos a um círculo sem fim)
Em termos simples, a sua gama de fórmulas está num loop sem fim ou está num círculo.
Como por regras de cálculo excel, as suas fórmulas não devem referenciar células de resultados como um intervalo. Se o fizerem, aparecerão erros de referência circulares.
Pessoalmente, já tive muitas vezes dificuldades com este tipo de erros.
Por exemplo, na nossa tabela de orçamento de despesas abaixo, precisamos de adicionar algum plug (número de ajuste) para corresponder aos alvos.
Então, calculámos o montante diferencial na linha 13, e agora só precisamos de os colocar na linha 9.
Uma forma simples é ligando as células da linha 13 na linha 9.
Se o fizer, a sua fórmula de ligação não funcionará em vez disso, poderá ver zero e possivelmente também poderá ver setas de cor azul a apontar referências de células.
Em geral, deve funcionar.
Mas tecnicamente, será um erro porque as nossas referências de células de fórmula estão em loop.
Como corrigir este problema:
P>Primeiro, precisamos de identificar células de erro.
Vá para o separador Fórmulas → clique na seta pendente Verificação de erros → coloque o cursor em Referências de células → agora verá intervalo(s)
Segundo, agora conhece o intervalo de referências circulares, vá para cada célula de erro e corrija as fórmulas.
#7 Usando aspas duplas incorrectamente – erro humano
Usamos frequentemente aspas duplas enquanto escrevemos as fórmulas.
Mas, é muito importante compreender quando usar e quando não usar.
Por exemplo, para juntar dois números, podemos usar uma fórmula como a que se segue.
=1234&5678 = 12345678
Alternativamente, para juntar duas cadeias de texto que não se pode fazer o mesmo, é necessário especificar texto dentro de aspas duplas.
=John&Smith: Forma errada de usar
=”John “&”Smith” = Esta é a forma correcta.
O mesmo se aplica a fórmulas condicionais como IF, AND, OR. É muito importante especificar texto dentro de aspas duplas.
Outra, esta é uma das razões para que as fórmulas do excel não funcionem.
#8 BODMAS – regra básica para cada cálculo
Sabe que cada cálculo em Excel é baseado na regra ‘BODMAS’?
Oh, espere… o que é a regra BODMAS?
É uma regra matemática para determinar uma ordem de operações.
p>
Ok, vamos simplificar mais.
p>p>P>Pode adivinhar qual é a resposta para o abaixo?
10+10*2 =?
P>Is that 40, parabéns está errado.
P>Pode tentar o mesmo cálculo em excel?
P>Vai muito provavelmente ver 30, de facto é a resposta correcta… mas como?
Segundo a regra BODMAS, o cálculo terá lugar com base numa sequência, ou seja → B- Parênteses, O- Ordem, D- Divisão, M- Multiplicação, A- Condição & S- subtracção.
Então, a sequência da fórmula acima é → 10*2 = 20 → 20+10 =30.
As suas fórmulas podem correr mal se não considerar BODMAS.
Como resolver este problema:
É muito simples, basta alterar a sequência usando parênteses como abaixo.
=(30+40)*2
P>Então, qualquer coisa entre parênteses será calculada primeiro e depois passará à operação seguinte.
##9 Utilização incorrecta de referenciação “absoluta”
Todos os que usam Excel sabem sobretudo o que é referenciação absoluta.
Se não, por favor parem aqui mesmo e aprendam sobre isso. Aqui está um tutorial rápido sobre referências a células.
Com a ajuda da referenciação absoluta, pode fixar o intervalo da sua fórmula para que possa copiar de uma célula para outra.
É de facto uma funcionalidade muito útil. Dito isto, se não souber como utilizá-la então as suas fórmulas não funcionarão como pretendem.
P>Certeza, esta é uma das principais razões para a fórmula de excel não funcionar quando copiada de uma célula para outra célula.
Por exemplo, na nossa tabela acima, utilizámos a referência absoluta de células para nos facilitar a cópia de fórmulas.
Mas, cometemos um erro.
Em vez de usar o congelamento parcial, utilizámos a tecla de atalho F4 e fixámos todo o intervalo.
Isso resultou de facto em resultados incorrectos.
Então, a forma correcta deveria ter sido utilizar referência absoluta apenas para linhas como esta =SUM(D$3:D$6).
Como corrigir este problema:
Felizmente, não há correcções rápidas para este tipo de erros. É preciso fazê-lo manualmente analisando todas as fórmulas.
Mas, a melhor maneira que encontrei até agora foi através da verificação cruzada das fórmulas & resultados assim que utilizar resultados absolutos & referências relativas.
Por favor informe-me sobre as suas soluções neste momento.
Dicas:
- Não usar a tecla de atalho F4 para congelamento parcial
- Tente incluir um sinal de dólar ($) manualmente para intervalo parcial
#10 Argumentos de fórmula incorrectos
Argumentos de fórmula incorrectos podem ter um impacto negativo nos resultados da fórmula.
Apenas para lhe dar alguma perspectiva, vamos entender abaixo a sintaxe de vlookup especialmente a última .
A discussão de pesquisa de gama tem duas opções a seleccionar.
- Verdade – Partida aproximada
- Falso – Partida exacta
Pessoalmente, sinto que usar a opção “Partida aproximada” é como cometer suicídio.
P>Porque a correspondência aproximada nunca retornará resultados exactos.
/p>
No nosso exemplo acima, adicionámos a fórmula Vlookup na célula F3 para descobrir o montante de receitas correspondente.
Se observar atentamente, o montante de receitas 25k para a identificação do cliente 6378993 está incorrecto. Deveria ter sido 65k em vez disso.
Então, o que correu mal?
Como mencionei anteriormente, usei ‘Partida aproximada’ sob o argumento de procura de intervalo.
A “correspondência” é outra fórmula em que tem uma opção para seleccionar argumentos semelhantes.
Como resolver este problema:
Nunca use “correspondência aproximada” nas suas fórmulas vlookup.
Agora é a sua vez
P>Pensa que tem outras razões para as fórmulas de excelência não funcionarem?
Or, talvez tenha uma pergunta a fazer.