A função FORMULATEXT é frequentemente descartada porque apenas transforma uma fórmula em texto. No entanto, esse texto bruto é exatamente o que o torna tão valioso. Portanto, pare de pensar nisso como um truque único: é a ferramenta perfeita para auditoria, controle de entrada e validação lógica.
A função FORMULATEXT possui um argumento:
=FORMULATEXT(ref)
onde referência é uma referência a uma célula ou, nas versões modernas do Excel, a um intervalo de células.
Se o referência argumento for para outra pasta de trabalho, essa pasta de trabalho deverá estar aberta. Caso contrário, a fórmula retornará o erro #N/A. Você também verá esse erro se a célula referenciada não contiver uma fórmula.
Aqui estão três maneiras de usá-lo em suas planilhas hoje.
Fórmulas de depuração
O uso mais simples da função FORMULATEXT é a auditoria direta de fórmulas.
Suponha que você tenha recebido uma planilha repleta de #DIV/0!, #VALUE!, #REF!, #SPILL! e outros erros. Selecionar cada célula uma por uma para revisar a fórmula na barra de fórmulas é demorado. Além do mais, detectar padrões nos erros é praticamente impossível.
Em vez disso, você pode usar FORMULATEXT com IF e ISERROR em uma única fórmula para exibir apenas as fórmulas das células onde um problema foi detectado.
Para fazer isso, selecione a célula superior esquerda onde deseja que a auditoria da fórmula comece e insira a seguinte fórmula (substituindo ambos os casos de A2:D19 pelo intervalo que deseja auditar em sua planilha):
=IF(ISERROR(A2:D19),FORMULATEXT(A2:D19),"")
O resultado dessa fórmula única se espalha por toda a área de auditoria, retornando apenas as fórmulas das células que contêm erros.
Este efeito de derramamento automático está disponível no Excel 2021 ou posterior, no Excel para Microsoft 365, no Excel para a Web e no tablet e aplicativos móveis do Excel. Em versões mais antigas, insira a fórmula como uma fórmula de matriz herdada selecionando todas as células na área de auditoria, digitando a fórmula na primeira célula selecionada e pressionando Ctrl+Shift+Enter para confirmar a fórmula em cada célula.
Essa visualização lado a lado permite revisar as fórmulas instantaneamente e diagnosticar as causas dos erros. Assim que você as corrige, as fórmulas desaparecem da área de auditoria. Além do mais, permite identificar facilmente se a mesma lógica defeituosa é aplicada a várias células.
Esses 5 recursos pouco conhecidos do Excel me economizam horas todas as semanas
Meus colegas de trabalho sempre se perguntam como termino meu trabalho no Excel tão rápido.
Documentando fórmulas principais
Você pode usar FORMULATEXT para criar uma biblioteca de todas as fórmulas principais em sua pasta de trabalho. Isso tem três benefícios principais:
- Melhor compreensão: Ele fornece um local central onde qualquer pessoa pode entender a lógica central de uma pasta de trabalho.
- Transferências simplificadas: Se outra pessoa for delegada para cuidar da pasta de trabalho, a biblioteca de fórmulas serve como documentação útil de como tudo funciona.
- Rede de segurança de fórmula: Você pode copiar periodicamente todas as fórmulas principais em outra planilha (colar valores) ou documento para proteção. Carimbe a data das fórmulas coladas para criar um histórico de como uma fórmula evoluiu ao longo do tempo.
Criar uma chave de fórmula é simples, envolvendo apenas duas colunas em uma planilha dedicada: um rótulo de fórmula na coluna A e o texto da fórmula na coluna B.
Agora, analise cada planilha e cada vez que você encontrar uma célula contendo uma fórmula importante – como o cálculo do lucro bruto final em sua planilha de Vendas – digite um rótulo de fórmula na coluna A e na coluna B, digite:
=FORMULATEXT(
Em seguida, selecione a célula-chave, feche os parênteses e pressione Enter.
Por fim, repita o processo para todas as outras fórmulas principais da sua pasta de trabalho.
Formate sua biblioteca de fórmulas principais como uma tabela do Excel. Dessa forma, cada vez que você digitar um rótulo de fórmula em uma nova linha na coluna A, a tabela será expandida para capturar a adição mais recente, abrigando seu índice em um único objeto nomeável.
Verificando a consistência da fórmula
Se você gerencia um grande conjunto de dados, saberá que a consistência da fórmula em uma coluna é uma de suas maiores dores de cabeça no controle de qualidade.
Por exemplo, se alguém substituir acidentalmente uma fórmula por um número codificado ou outra fórmula, porque a célula aparece para ter as informações corretas, o erro não é detectado. Nesta planilha, embora as células J8, J11 e J14 olhar legítimas à primeira vista, elas não contêm a mesma mecânica subjacente que as outras células na coluna J.
Quando usada junto com a formatação condicional, a função FORMULATEXT ajuda a lidar com esse problema.
Primeiro, certifique-se de que a fórmula da primeira célula da coluna esteja correta, pois ela será usada como uma célula âncora absoluta contra a qual você verificará a consistência do restante da coluna. Em seguida, selecione a coluna que deseja verificar (excluindo a âncora) selecionando a segunda célula e pressionando Ctrl+Shift+Seta para baixo. Em seguida, na guia Página inicial, clique em Formatação condicional > Nova regra.
Agora, clique em “Usar uma fórmula para determinar quais células formatar” e insira a seguinte fórmula (supondo que você deseja verificar a coluna J):
=IF(NOT(ISFORMULA(J3)),TRUE,IF(NOT(ISFORMULA($J$2)),FALSE,FORMULATEXT(J3)<>FORMULATEXT($J$2)))
onde:
- SE(NÃO(ÉFÓRMULA(J3)),VERDADEIRO verifica se a célula atual é um valor codificado. Se for (TRUE), todas as outras verificações serão ignoradas e a célula retornará TRUE imediatamente.
- SE(NÃO(ÉFÓRMULA($J$2)),FALSO verifica se a célula âncora é um valor codificado. Se for, o processo é interrompido (FALSO).
- FÓRMULATEXTO(J3)<>FÓRMULATEXTO($J$2)) verifica se a célula atual contém uma fórmula diferente da célula âncora (J2). Se sim, retorna TRUE.
O guia para iniciantes em lógica booleana no Microsoft Excel
Aumente seu benefício booleano.
Como a referência à célula J3 é relativa, a regra de formatação condicional é ajustada para cada linha. Por outro lado, a célula âncora é absoluta (daí os cifrões), por isso serve consistentemente como ponto de comparação.
Em seguida, clique em “Formatar” e escolha um preenchimento de célula amarela para destacar as inconsistências.
Por fim, clique em “OK” duas vezes para fechar ambas as caixas de diálogo e ver o resultado.
Se a célula âncora for um valor codificado, nenhuma célula será destacada, então você corre o risco de interpretar a coluna como livre de erros, em vez de a verificação ter sido abortada. É por isso que é crucial que você verifique novamente a precisão da célula âncora.
Assim que você corrigir a fórmula em uma célula destacada, ela perderá a formatação – e, antes que você perceba, você terá uma planilha consistente e sem erros.
O Excel oferece muitas maneiras de auditar sua planilha. Uma das minhas favoritas é a ferramenta Ir para especial, que permite selecionar e editar rapidamente todas as células de um tipo específico, como todas as células que contêm fórmulas, constantes ou formatação condicional. No entanto, esta ferramenta integrada apenas informa onde as fórmulas são. Ao tratar a fórmula como uma sequência de texto, a função FORMULATEXT se transforma de um simples visualizador em uma poderosa ferramenta de controle de qualidade.