Ferramentas de auditoria de fórmulas no Excel
Como todos sabemos, o MS Excel é principalmente usado e popular por suas funções, fórmulas e macros. Mas e se estivermos tendo algum problema ao escrever a fórmula ou não formos capazes de obter o resultado desejado em uma célula porque não formulamos a função corretamente. É por isso que o MS Excel oferece muitas ferramentas integradas para auditoria de fórmulas e fórmulas de solução de problemas.
As ferramentas que podemos usar para auditoria e solução de problemas de fórmulas no Excel são:
- Rastrear precedentes
- Dependentes de rastreamento
- Remova as setas
- Mostrar Fórmulas
- Verificação de erros
- Avalie a fórmula
Exemplos de ferramentas de auditoria no Excel
Aprenderemos sobre cada uma das ferramentas de auditoria acima, uma a uma, usando alguns exemplos no Excel.
Você pode baixar este modelo de ferramentas de auditoria aqui - modelo de ferramentas de auditoria em Excel# 1 - Traçar precedentes
Suponha que temos a seguinte fórmula na célula D2 para calcular os juros de uma conta FD em um banco.
Agora, se quisermos verificar os precedentes da fórmula, podemos pressionar F2 para entrar no modo de edição após selecionar a célula necessária para que as células precedentes sejam contornadas com várias cores e na mesma cor, a referência de célula é escrita.
Podemos ver que A2 é escrito com a cor azul na célula da fórmula e com a mesma cor, a célula A2 é limitada.
Do mesmo jeito,
A célula B2 tem uma cor vermelha.
A célula C2 tem uma cor roxa.
Essa maneira é boa, mas temos uma maneira mais conveniente de verificar precedentes para a célula da fórmula.
Para rastrear precedentes, podemos usar o 'Rastrear precedentes' comando no 'Auditoria de fórmulas' grupo sob o 'fórmulas' guia.
Precisamos apenas selecionar a célula da fórmula e clicar no comando 'Rastrear precedentes' . Então você pode ver uma seta conforme mostrado abaixo.
Podemos ver que as células precedentes são destacadas com pontos azuis.
# 2 - Remova as setas
Para remover essas setas, podemos usar o 'Remover Arrows' comando no 'Auditoria de fórmulas' grupo sob o 'fórmulas' guia.
# 3 - Rastrear dependentes
Este comando é usado para rastrear a célula que depende da célula selecionada.
Vamos usar este comando usando um exemplo.
Suponha que temos 4 valores que podemos investir. Queremos saber quanto juros podemos ganhar se investirmos.
Podemos ver que na imagem acima, aplicamos uma fórmula de cálculo de juros com o Montante 1 e especificamos o percentual de juros e a duração no ano.
Vamos copiar a fórmula e colá-la nas células adjacentes para a quantidade 2, quantidade 3 e quantidade 4. Pode-se notar que usamos uma referência de célula absoluta para as células G2 e I2, pois não queremos alterar essas referências enquanto copiando e colando.
Agora, se quisermos verificar se quais células são dependentes da célula G2. Então vamos usar o 'Traço Dependentes' comando disponível no 'Auditoria de fórmulas' grupo sob o 'fórmulas' guia.
Selecione a célula G2 e clique no comando 'Trace Dependents' .
Na imagem acima, podemos ver as linhas de seta onde as setas indicam quais células são dependentes das células.
Agora vamos remover as linhas de seta usando o comando 'Remover setas' .
# 4 - Mostrar fórmulas
Podemos usar este comando para exibir fórmulas escritas na planilha do Excel. A tecla de atalho para este comando é 'Ctrl + ~' .
Veja a imagem abaixo onde podemos ver as fórmulas na célula.
Podemos ver que, em vez dos resultados da fórmula, podemos ver a fórmula. Para valores, o formato da moeda não é visível.
Para desativar este modo, pressione 'Ctrl + ~' novamente ou podemos clicar no comando 'Mostrar fórmulas' .
# 5 - Verificação de erros
Este comando é usado para verificar o erro na fórmula ou função especificada.
Vamos dar um exemplo para entender isso.
Veja a imagem abaixo onde temos um erro na função aplicada para o resultado.
Agora, para resolver esse erro, usaremos o comando 'Verificação de erros' .
As etapas seriam:
Selecione a célula onde a fórmula ou função foi escrita e clique em 'Verificação de Erros'.
Quando clicamos no comando, obtemos a seguinte caixa de diálogo com a legenda 'Verificação de erros' .
Na caixa de diálogo acima, pode ser visto que há algum erro de nome inválido. A fórmula contém texto não reconhecido.
Se estivermos usando a função ou construímos a fórmula pela primeira vez, podemos clicar no botão 'Ajuda sobre este erro' , que abrirá a página de ajuda para a função no navegador, onde podemos ver todas as informações relacionadas online e entender a causa e encontrar todas as soluções possíveis.
Ao clicar neste botão agora, encontraremos a seguinte página.
Nesta página, vamos saber sobre o erro que esse erro ocorre quando
- A fórmula se refere a um nome que não foi definido. Isso significa que o nome da função ou intervalo nomeado não foi definido anteriormente.
- A fórmula contém um erro de digitação no nome definido. Isso significa que há algum erro de digitação.
Se já usamos a função anteriormente e sabemos sobre a função, podemos clicar no botão 'Mostrar etapas de cálculo' para verificar como a avaliação da função resulta em um erro.
Se clicarmos neste botão, as seguintes etapas serão exibidas:
- A seguinte caixa de diálogo é exibida quando clicamos no botão 'Mostrar etapas de cálculo' .
- Depois de clicar no botão 'Avaliar' , a expressão sublinhada, isto é, 'IIF', é avaliada e fornece as seguintes informações conforme exibido na caixa de diálogo.
Como podemos ver na imagem acima, a expressão 'IIF' avaliada como um erro que é '#NOME?' Agora a próxima expressão ou referência, isto é, B2 foi sublinhada. Se clicarmos no botão 'Step In' , também podemos verificar os detalhes internos de uma etapa e sair pressionando o botão 'Step Out' .
- Agora clicaremos no botão 'Avaliar' para verificar o resultado da expressão sublinhada. Após clicar, obtemos o seguinte resultado.
- Após clicar no botão 'Avaliar' , obtemos o resultado da função aplicada.
- Como resultado, obtivemos um erro e ao analisarmos a função passo a passo, ficamos sabendo que existe algum erro em 'IIF'. Para isso, podemos usar o comando 'Inserir Função' no grupo 'Biblioteca de Funções' em ' Guia Fórmulas.
Conforme digitamos o 'if' , temos uma função semelhante na lista, precisamos escolher a função apropriada.
Após selecionar a função 'If' , obtemos a seguinte caixa de diálogo com caixas de texto para o argumento e preencheremos todos os detalhes.
Após clicar em 'Ok' , obtemos o resultado na célula. Vamos copiar a função para todos os alunos.
Coisas para lembrar
- Se ativarmos o comando 'Mostrar fórmulas', as datas também são mostradas no formato numérico.
- Ao avaliar a fórmula, também podemos usar F9 como um atalho no Excel.
