Análise de dados do Excel - auditoria de fórmulas

Você pode querer verificar a precisão das fórmulas ou encontrar a origem de um erro. Os comandos de auditoria de fórmulas do Excel fornecem uma maneira fácil de encontrar

  • Quais células estão contribuindo no cálculo de uma fórmula na célula ativa.
  • Quais fórmulas se referem à célula ativa.

Essas descobertas são mostradas graficamente por linhas de seta que tornam a visualização fácil. Você pode exibir todas as fórmulas na planilha ativa com um único comando. Se suas fórmulas se referem a células em uma pasta de trabalho diferente, abra essa pasta de trabalho também. O Excel não pode ir para uma célula em uma pasta de trabalho que não está aberta.

Configurando as opções de exibição

Você precisa verificar se as opções de exibição das pastas de trabalho que está usando estão definidas corretamente.

  • Clique FILE > Options.
  • Na caixa de diálogo Opções do Excel, clique em Avançado.
  • Em opções de exibição para a pasta de trabalho -
    • Selecione a pasta de trabalho.
    • Verifique se em Para objetos, mostrar, Tudo está selecionado.
  • Repita essa etapa para todas as pastas de trabalho que você está auditando.

Rastreamento de precedentes

As células precedentes são aquelas que são referidas por uma fórmula na célula ativa.

No exemplo a seguir, a célula ativa é C2. Em C2, você tem a fórmula=B2*C4.

B2 e C4 são células precedentes para C2.

Para rastrear os precedentes da célula C2,

  • Clique na célula C2.
  • Clique na guia Fórmulas.
  • Clique em Rastrear precedentes no grupo Auditoria de fórmulas.

Duas setas, uma de B2 a C2 e outra de C4 a C2, serão exibidas, traçando os precedentes.

Observe que, para rastrear precedentes de uma célula, a célula deve ter uma fórmula com referências válidas. Caso contrário, você receberá uma mensagem de erro.

  • Clique em uma célula que não contém uma fórmula ou clique em uma célula vazia.
  • Clique em Rastrear precedentes no grupo Auditoria de fórmulas.

Você receberá uma mensagem.

Removendo as setas

Clique em Remover setas no grupo Auditoria de fórmula.

Todas as setas na planilha desaparecerão.

Rastreando Dependentes

As células dependentes contêm fórmulas que se referem a outras células. Isso significa que, se a célula ativa contribui para uma fórmula em outra célula, a outra célula é uma célula dependente da célula ativa.

No exemplo abaixo, C2 tem a fórmula =B2*C4. Portanto, C2 é uma célula dependente das células B2 e C4

Para rastrear os dependentes da célula B2,

  • Clique na célula B2.
  • Clique na guia Fórmulas.
  • Clique em Rastrear Dependentes no grupo Auditoria de Fórmula.

Uma seta aparece de B2 a C2, mostrando que C2 depende de B2.

Para rastrear os dependentes da célula C4 -

  • Clique na célula C4.
  • Clique na guia Fórmula> Rastrear Dependentes no grupo Auditoria de Fórmula.

Outra seta aparece de C4 a C2, mostrando que C2 também depende de C4.

Clique Remove Arrowsno grupo Auditoria de Fórmula. Todas as setas na planilha desaparecerão.

Note- Para rastrear dependentes de uma célula, a célula deve ser referenciada por uma fórmula em outra célula. Caso contrário, você receberá uma mensagem de erro.

  • Clique na célula B6 não referenciada por nenhuma fórmula ou clique em qualquer célula vazia.
  • Clique em Rastrear Dependentes no grupo Auditoria de Fórmula. Você receberá uma mensagem.

Trabalhando com Fórmulas

Você entendeu o conceito de Precedentes e Dependentes. Agora, considere uma planilha com várias fórmulas.

  • Clique em uma célula em Categoria de aprovação na tabela Resultados do exame.
  • Clique em Rastrear precedentes. A célula à sua esquerda (marcas) e o intervalo E4: F8 serão mapeados como precedentes.
  • Repita para todas as células em Categoria de aprovação na tabela Resultados do exame.

  • Clique em uma célula em Categoria de aprovação na tabela Notas do aluno.

  • Clique em Rastrear Dependentes. Todas as células na tabela Categoria de aprovação nos resultados do exame serão mapeadas como dependentes.

Mostrando Fórmulas

A planilha a seguir contém o resumo das vendas dos vendedores nas regiões Leste, Norte, Sul e Oeste.

  • Clique na guia FÓRMULAS na Faixa de Opções.

  • Clique em Mostrar Fórmulas no grupo Auditoria de Fórmula. As Fórmulas na planilha aparecerão, para que você saiba quais células contêm fórmulas e quais são as fórmulas.

  • Clique em uma célula em TotalSales.

  • Clique em Rastrear precedentes. Um ícone de planilha aparece no final da seta. O ícone da planilha indica que os precedentes estão em uma planilha diferente.

Clique duas vezes na seta. UMAGo TO a caixa de diálogo aparece, mostrando os precedentes.

Como você pode observar, existem quatro precedentes, em quatro planilhas diferentes.

  • Clique em uma referência de um dos precedentes.
  • A referência aparece na caixa Referência.
  • Clique OK. A planilha contendo esse precedente é exibida.

Avaliando uma Fórmula

Para descobrir como uma fórmula complexa em uma célula funciona passo a passo, você pode usar o comando Avaliar Fórmula.

Considere a fórmula NPV (Ano do Meio) na célula C14. A fórmula é

=SQRT (1 + C2)*C10

  • Clique na célula C14.
  • Clique na guia FÓRMULAS na Faixa de Opções.
  • Clique em Avaliar Fórmula no grupo Auditoria de Fórmula. A caixa de diálogo Avaliar fórmula é exibida.

No Evaluate Formulacaixa de diálogo, a fórmula é exibida na caixa em Avaliação. Ao clicar noEvaluatebotão várias vezes, a fórmula é avaliada passo a passo. A expressão com um sublinhado sempre será executada a seguir.

Aqui, C2 está sublinhado na fórmula. Portanto, é avaliado na próxima etapa. CliqueEvaluate.

A célula C2 tem valor 0,2. Portanto, C2 será avaliado como 0,2.1+0.2está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

1 + 0,2 será avaliado como 1,2. SQRT(1.2)está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

SQRT (1.2) será avaliado como 1.09544511501033. C10está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

C10 será avaliado como 4976,8518518515.

1.09544511501033 * 4976.8518518515 está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

1.09544511501033 * 4976.8518518515 será avaliado como 5.451,87.

Não há mais expressões para avaliar e esta é a resposta. oEvaluate botão será alterado para Restart botão, indicando a conclusão da avaliação.

Verificação de erros

É uma boa prática fazer uma verificação de erro quando sua planilha e / ou pasta de trabalho estiver pronta com os cálculos.

Considere os seguintes cálculos simples.

O cálculo na célula resultou no erro # DIV / 0 !.

  • Clique na célula C5.

  • Clique na guia FÓRMULAS na Faixa de Opções.

  • Clique na seta ao lado de Verificação de Erros no grupo Auditoria de Fórmula. Na lista suspensa, você encontrará queCircular References está desativado, indicando que sua planilha não possui referências circulares.

  • Selecione Trace Error na lista suspensa.

As células necessárias para calcular a célula ativa são indicadas por setas azuis.

  • Clique em Remover setas.
  • Clique na seta ao lado de Verificação de erros.
  • Selecione Verificação de erros na lista suspensa.

o Error Checking a caixa de diálogo aparece.

Observe o seguinte -

  • Se você clicar Help on this error, A ajuda do Excel sobre o erro será exibida.

  • Se você clicar Show Calculation Steps, A caixa de diálogo Avaliar fórmula é exibida.

  • Se você clicar Ignore Error, a caixa de diálogo Verificação de erros fecha e se você clicar Error Checking comando novamente, ele ignora este erro.

  • Se você clicar Edit in Formula Bar, você será direcionado para a fórmula na barra de fórmulas, para que possa editar a fórmula na célula.