IFERROR + XLOOKUP: Criação de fórmulas Excel à prova de erros
Quando usar o argumento if_not_found de IFERROR, IFNA e XLOOKUP - e quando ocultar erros é exatamente o movimento errado. Padrões práticos para fórmulas que falham ruidosamente apenas quando deveriam.
#N/A espalhados em um relatório parecem quebrados, então o reflexo é agrupar tudo em IFERROR e seguir em frente. Às vezes está certo. Freqüentemente, ele esconde um problema real – uma pesquisa quebrada, uma divisão por zero, um erro de digitação em uma chave – sob uma célula em branco organizada.
Este guia cobre as três ferramentas para lidar com erros de fórmula, a diferença entre elas e os padrões que ocultam os erros esperado, deixando os inesperado visíveis.
Resposta rápida
Use Argumento if_not_found de if_not_found ou IFNA quando uma correspondência ausente for esperada. Use IFERROR somente quando todos os erros possíveis da expressão encapsulada devem compartilhar o mesmo substituto. Para denominadores zero e outras condições conhecidas, teste a condição diretamente com IF; ele documenta o motivo e deixa visíveis os erros não relacionados.
| Situação | Padrão recomendado | Por que |
|---|---|---|
| A pesquisa pode não ter correspondência | XLOOKUP(...,"Not found") |
Lida apenas com a falha esperada |
| VLOOKUP/INDEX-MATCH pode falhar | IFNA(formula,"Not found") |
Mantém #REF! e #VALUE! visíveis |
| O denominador pode ser zero | IF(B2=0,"",A2/B2) |
Testa a condição real |
| Qualquer falha significa verdadeiramente a mesma coisa | IFERROR(formula,fallback) |
Captação ampla adequada |
As três ferramentas
IFERROR captura o tipo de erro cada:
=IFERROR(A2/B2, 0)
Se a divisão produzir #DIV/0!, #VALUE!, #REF! - qualquer coisa - você obtém 0. Essa amplitude também é seu perigo: um #REF! de uma coluna excluída merece atenção, não um 0 silencioso.
IFNA captura apenas #N/A:
=IFNA(VLOOKUP(A2, Prices!A:B, 2, FALSE), "not listed")
Isso é quase sempre o que você deseja nas pesquisas: "valor não encontrado" é uma condição esperada, enquanto #VALUE! ou #REF! da mesma fórmula ainda sinaliza um bug genuíno.
XLOOKUP integrado do if_not_found torna o substituto parte da pesquisa em si:
=XLOOKUP(A2, Prices!A:A, Prices!B:B, "not listed")
Mais limpo do que empacotar e apenas lida com o caso não encontrado - outros erros ainda surgem.
Regra prática: lidar com o esperado, expor o inesperado
Faça uma pergunta por fórmula: qual erro é normal aqui?
- Uma pesquisa que pode legitimamente perder → lidar com
#N/A(IFNA ou substituto do XLOOKUP). - Uma proporção cujo denominador pode legitimamente ser zero → testar o denominador explicitamente:
=IF(B2=0, "", A2/B2)
Testar a condição é melhor do que detectar o erro: IF(B2=0,…) documenta por que o substituto existe, enquanto IFERROR em torno da mesma divisão também engoliria um #VALUE! causado pelo texto na coluna B.
- Todo o resto → deixe erro. Um
#REF!visível custa um minuto; um invisível custa um relatório errado.
Os erros clássicos
Cobertura IFERROR sobre uma coluna inteira. Você envia um relatório com 40 zeros, três dos quais são zeros reais e 37 dos quais são uma planilha renomeada que ninguém percebeu.
IFERROR(..., "") alimentando matemática. Uma string vazia em uma coluna numérica torna SUMs downstream sutilmente errados e produz #VALUE! em aritmética - o erro que você escondeu volta duas colunas depois, mais longe de sua causa.
Captura de erros que indicam dados sujos. Se VALUE(A2) erros porque a coluna mistura texto e números, a correção é limpando a coluna, não detectando o sintoma.
Auditando uma planilha cheia de erros ocultos
Herdando uma pasta de trabalho onde cada fórmula é agrupada em IFERROR? Dois movimentos:
- Conte o que está sendo capturado. Em uma coluna auxiliar, repita a fórmula interna sem o wrapper e conte os erros com
=SUM(--ISERROR(...))inserido no intervalo. - Peça a um assistente para auditá-lo. IA para Excel pode digitalizar as fórmulas de uma planilha, listar quais células estão suprimindo erros no momento e que tipo de erro é, e distinguir "falta de pesquisa, tratada corretamente" de "referência quebrada, ocultada silenciosamente" - e então corrigir aqueles que você aprova, com um backup automático antes de qualquer alteração.
Esse tipo de auditoria de fórmula é tedioso e rápido para uma ferramenta que lê a pasta de trabalho programaticamente. Se você preferir descrever o objetivo em uma frase do que construir colunas auxiliares, experimente o complemento gratuitamente - e para saber o caminho em inglês simples para escrever essas fórmulas em primeiro lugar, consulte Geração de fórmula de IA.
Padrões de fórmulas mais confiáveis
Retorna um status em vez de retornar zero silenciosamente:
=IF(B2=0, "CHECK DENOMINATOR", A2/B2)
Mantenha uma falha de pesquisa distinta de um resultado em branco:
=XLOOKUP(A2, Prices!A:A, Prices!B:B, NA())
Valide a chave antes de procurá-la:
=IF(TRIM(A2)="", "MISSING KEY", XLOOKUP(TRIM(A2), Prices!A:A, Prices!B:B, "NOT LISTED"))
Esses estados visíveis são mais fáceis de contar, filtrar e investigar do que strings vazias. Se for necessária uma apresentação clara, mantenha a fórmula de diagnóstico em uma coluna auxiliar e apresente um resultado separado voltado para o usuário.
Referências oficiais
- Microsoft: Função IFNA
- Microsoft: Função XLOOKUP
Perguntas frequentes
Qual é a diferença entre IFERROR e IFNA?
IFERROR captura todos os tipos de erro; IFNA captura apenas #N/A (o erro "não encontrado"). Nas pesquisas, prefira o argumento if_not_found de IFNA ou XLOOKUP para que erros estruturais como #REF! fique visível.
Devo usar IFERROR em todos os lugares?
Não. Trate apenas os erros esperados (geralmente erros de pesquisa e denominadores zero) e deixe que erros inesperados sejam exibidos. Um erro visível são as informações de diagnóstico; um oculto é um relatório futuro incorreto.
Como encontro todas as células com erros no Excel?
Pressione F5 → Especial → Fórmulas → verifique apenas erros. Excel seleciona todas as células de erro na planilha. Um assistente de IA pode ir além e categorizá-los por tipo de erro e causa.