Aprenda neste artigo como usar a auditoria de fórmulas no Excel.
1. Introdução Auditoria Excel
A auditoria de fórmulas é um recurso poderoso para auxiliar a construção e revisão de fórmulas no Excel. Há diversos comandos extremamente úteis para avaliarmos as fórmulas, rastrear precedentes e dependentes, mostrar fórmulas, rastrear e verificar erros, analisar e corrigir referências circulares.
Estas opções estão disponíveis na guia Fórmulas, no grupo Auditoria de Fórmulas.
2. Explorando as Opções da Auditoria de Fórmulas no Excel
2.1. Rastrear precedentes
Esta função é interessante para a edição de fórmulas, uma vez que permite saber quais referências de células estão sendo usadas para se chegar ao resultado. Ela indica quais endereços a fórmula atualmente analisada está usando, ou seja, os endereços precedentes.
Para usar o recurso, selecionamos a célula com a fórmula a analisar e clicamos no comando Rastrear Precedentes, como ilustra a próxima imagem.
Ao inserirmos no endereço E3 a fórmula =C3+2*D3 possuirá os precedentes C3 e D3. Ao usarmos o recurso, com a seleção em E3 (célula que contém a fórmula a ser analisada), será exibida a seta que evidencia estes endereços. Desta forma, visualmente, conseguimos saber quais células fornecem valores para a nossa fórmula atual. A imagem a seguir ilustra o exemplo descrito.
Desta forma, saberemos que ao editar tais endereços, estaremos potencialmente alterando o resultado de nossa fórmula atual. Este recurso pode ser usado como uma segurança a mais na hora de deletar células, por exemplo, a fim de evitarmos impactos indesejados em fórmulas. Ele pode nos auxiliar rapidamente a saber se podemos excluir tranquilamente os endereços ou não.
2.2. Rastrear Dependentes
Esta função realiza o inverso do rastreamento dos precedentes. Ela permite visualizar rapidamente quais referências de células contém fórmulas que usam o nosso endereço atual. Em outras palavras: permite saber quais são os endereços que possuem resultados provenientes de fórmulas e que usam o endereço analisado, ou seja: que dele dependem.
Para usar o recurso, selecionamos a célula na qual está a célula com a fórmula a analisar e clicamos no comando Rastrear Dependentes, conforme imagem a seguir.
A imagem a seguir mostra que há 2 fórmulas dependentes de J5. A referência J5 fornece valores para as fórmulas das células H3 e H4.
Em H3 há uma fórmula que retorna os 3 primeiros caracteres de J5 e em H4 há uma fórmula que retorna os 3 últimos caracteres de H4. Note que há 2 setas e que cada uma delas aponta para um dependente diferente de J5. Se houvesse mais dependentes, obviamente, haveria mais setas indicando.
Da mesma forma que o rastreamento de precedentes, o rastreamento de dependentes permite mais segurança na edição de fórmulas ao mapear os endereços que colaboram para os resultados das fórmulas.
2.3. Remover Retas Auditoria Excel
Quando usamos o recurso de rastrear precedentes e rastrear dependentes, as setas exibidas é que indicam as precedências e dependências. No entanto, estas setas permanecem em tela como uma marcação visual até as removermos.
Para isso, nós devemos acessar o comando Remover Setas.
A imagem a seguir mostra um rastreamento de precedentes ao lado esquerdo e a remoção de setas executada ao lado direito.
Como foi possível notar, podemos fazer o rastreamento de precedentes e dependentes de várias células de uma só vez. A remoção de setas também poderá ser seletiva: podemos remover todas as setas, remover apenas as setas dos precedentes ou remover apenas as setas dos dependentes.
2.4. Mostrar Fórmulas
Você pode acompanhar as várias opções que temos no Excel para exibir as fórmulas nas células, visitando o artigo do link: https://www.guiadoexcel.com.br/exibir-formulas-excel/.
Através da auditoria de fórmulas podemos acessar o comando Mostrar Fórmulas para visualizar as fórmulas nas células.
O atalho correspondente ao comando é CTRL + `. Para desativar a visualização, basta clicar no mesmo comando ou executar o mesmo atalho sugerido.
A imagem a seguir mostra ao lado esquerdo um intervalo com resultados retornados por fórmulas e ao lado direito a exibição das respectivas fórmulas através da ativação do comando de Mostrar Fórmulas.
A visualização das fórmulas pode auxiliar rapidamente a identificar as células que contém fórmulas e com isso pode ajudar a proteger estes endereços. O recurso, quando aplicado, exibe as fórmulas apenas da planilha ativa no momento da exibição, devendo ser realizada para cada planilha desejada, se necessário.
2.5. Verificação de erros
Este comando possui 3 opções: Verificar Erros, Rastrear Erro e Referências Circulares.
Estas opções serão esclarecidas a seguir.
a) Verificação de Erros
A verificação de erros mais clássica do Excel é aquela que sinaliza números armazenados como texto. As células com esta condição possuem uma espécie de marca verde em seu vértice superior esquerdo.
Considere os dados da imagem seguinte.
Ao acionarmos o recurso de Verificação de Erros com a planilha destes dados ativa, será exibida a janela de verificação de erros.
Em nosso caso, os códigos apresentados na imagem anterior estão armazenados como texto, embora possuam dígitos numéricos. O Excel então oferece a opção de converter para número ou ignorar, além de editar diretamente na barra de fórmulas estes valores. Não podemos afirmar que temos erros, mas o Excel tenta auxiliar com correção rápida caso concordemos. Se nós aceitarmos as sugestões, os códigos serão convertidos em número.
Podemos também selecionar as células com estas verificações sinalizadas em verde e clicar no comando da verificação de erros.
A cor verde é a cor padrão, mas podemos alterá-la. Além disso, podemos também editar as verificações que desejamos que o Excel execute, desativando várias opções. Para isso, acessarmos as opções Arquivo > Opções > Fórmulas para visualizar as opções a seguir.
b) Rastrear Erro
Este recurso serve para rastrear erros de fórmulas, como #N/D, #REF!, #DIV/0!, #VALOR!. Tomemos como base a imagem a seguir.
Ao selecionar a célula com erro e clicar no comando Rastrear Erro, veremos as setas que indicam quais células afetam o valor de erro da célula selecionada no momento.
Para removermos as setas, basta seguir o que está orientado no item 2.3.
c) Referências circulares
As referências circulares ocorrem quando um resultado depende do outro e vice-versa. Quando uma fórmula em uma certa célula usa o endereço de outra, estando esta outra com uma fórmula que contém endereço da primeira, teremos uma referência circular. Em resumo: o resultado de uma célula depende do resultado da outra, ou seja, temos uma dependência recíproca.
O Excel notifica as referências circulares ao abrirmos uma pasta de trabalho ou fazermos alterações:
Veja a imagem a seguir com um exemplo diferente de referência circular. Temos um exemplo de referência circular em que a fórmula inserida em uma certa célula usa o seu próprio endereço para o cálculo.
A fórmula inserida em B9 está somando o próprio resultado de B9. Como isso é possível, se estamos ainda tentando obter um valor para B9?
Note que a fórmula inserida em B9 certamente tentou somar o intervalo que vai de B3 até B8, mas B9 foi incluído, o que ocorre muitas vezes acidentalmente. Neste caso, o valor de B9 fica indeterminado. Basta um simples ajuste para a correção, editando o intervalo, que deverá ir até B8. Podemos tentar contornar as referências circulares executando o cálculo iterativo no Excel.
Podemos buscar todas as referências circulares existentes. Ao clicar no comando, teremos a exibição dos endereços que contém referências circulares, inclusive caso estejam em outras planilhas, que é o exemplo ilustrado a seguir.
Podemos e devemos acessar os devidos endereços e ajustar as fórmulas para eliminar as referências circulares existentes.
Mais um exemplo: na imagem a seguir temos 2 células com fórmulas interdependentes: D4 depende de D5 e D5 depende de D4 para realizar os cálculos.
O Excel, neste caso, exibe a seta que evidencia a referência circular.
2.6. Avaliar fórmula
Este recurso permite analisar as etapas de resolução das fórmulas, ajudando a depurar os resultados. Pode ser bastante útil para entendermos os resultados das fórmulas ao enxergamos cada parte do cálculo, a fim de encontrar eventuais erros cometidos.
Veja a fórmula destacada na imagem a seguir que enfatiza o uso da função SE.
O resultado da fórmula em E3 foi Apto. Ao usar o recurso de Avaliar fórmula, vemos cada etapa de resolução até este resultado. A série de imagens a seguir mostra passo a passo a resolução da fórmula. Devemos clicar sempre em Avaliar para avançar em cada etapa da resolução da fórmula.
Note o resultado de cada teste da função OU e como ele é usado para teste lógico da função SE. Uma alternativa é usarmos a tecla F9 para avaliar as fórmulas ou suas partes.
Download Planilha Como Usar Auditoria no Excel
Clique no botão abaixo para realizar o download da planilha de exemplo: