Prevenindo erros de fórmula no Excel usando IFERROR ou IFNA. Estes são melhores do que os antigos ISERROR ISERR e ISNA. Saiba mais aqui.
Erros de fórmula podem ser comuns. Se você tiver um conjunto de dados com centenas de registros, uma divisão por zero ou um # N / A provavelmente aparecerá de vez em quando.
No passado, prevenir erros exigia esforços hercúleos. Acene com a cabeça conscientemente se você já desmaiou =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Além de ser muito longa para digitar, essa solução requer que o Excel faça o dobro de VLOOKUPs. Primeiro, você faz uma VLOOKUP para ver se a VLOOKUP vai gerar um erro. Em seguida, você faz o mesmo PROCV novamente para obter o resultado sem erro.
O Excel 2010 introduziu o = IFERROR (fórmula, valor se erro) bastante aprimorado. Eu sei que IFERROR soa como as velhas funções ISERROR, ISERR, ISNA, mas é completamente diferente.
Esta é uma função brilhante: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Se você tiver 1.000 VLOOKUPs e apenas 5 retornar # N / A, os 995 que funcionaram exigem apenas um único VLOOKUP. É apenas o 5 que retornou # N / A que precisa passar para o segundo argumento de IFERROR.
Estranhamente, o Excel 2013 adicionou a função IFNA (). É como IFERROR, mas procura apenas erros # N / A. Pode-se imaginar uma situação estranha em que o valor na tabela de pesquisa é encontrado, mas a resposta resultante é uma divisão por 0. Se você quiser preservar o erro de divisão por zero por algum motivo, IFNA () fará isso.
Obviamente, a pessoa que construiu a tabela de pesquisa deve ter usado IFERROR para evitar a divisão por zero em primeiro lugar. Na figura abaixo, o “nm” é o código de um antigo gerente para “não significativo”.
Agradecimentos a Justin Fishman, Stephen Gilmer e Excel by Joe.
Assistir vídeo
- Nos velhos tempos, você usaria
=IF(ISNA(Formula),0,Formula) - A partir do Excel 2010,
=IFERROR(Formula,0) - Mas IFERROR trata erros DIV / 0 da mesma forma que um # N / A! erros
- A partir do Excel 2013, use
=IFNA(Formula,0)para detectar apenas erros # N / A - Agradecimentos a Justin Fishman, Stephen Gilmer e Excel by Joe.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2042 - IFERROR e IFNA!
Estarei fazendo um podcast de todas as minhas dicas deste livro, clique no “i” no canto superior direito para obter a lista de reprodução inteira!
Tudo bem, vamos voltar aos velhos tempos, Excel 2007 ou antes, se você precisava evitar o # N / A! a partir de uma fórmula VLOOKUP como esta, costumávamos fazer essa coisa maluca. Junte todos os caracteres de PROCV, exceto =, Ctrl + C para colocá-lo na área de transferência, = IF (ISNA (, pressione a tecla End para chegar ao final, se for # N / A !, então nós diga “Not Found”, vírgula, senão fazemos PROCV! Certo, eu colo lá, e olha como é longa a fórmula, Ctrl + Enter, acrescento a) ali, Ctrl + Enter, tudo bem olha, isso é fofo. Tudo bem, mas o problema é que, embora ele resolva o problema do # N / A! Cada fórmula faz a VOOKUP duas vezes. Ele não quer que digamos "Ei, isso é um erro? Oh, não, não é um erro. Vamos fazer de novo! ”Portanto, leva o dobro do tempo para fazer todas essas VLOOKUPs. No Excel 2010,eles nos dão a fórmula incrível do IFERROR!
Agora eu sei que parece o que tínhamos antes, ISERROR ou ISERR, mas isso é IFERROR. E do jeito que funciona, pega qualquer fórmula que possa retornar um erro, e aí você diz = IFERROR, tem a fórmula, vírgula, o que fazer se for um erro, então “Não Encontrado”. Tudo bem agora, o que é bonito sobre isso é, digamos que você tivesse mil VLOOKUPs para fazer e 980 deles funcionassem. Para o 980 ele só faz VLOOKUP, não é um erro, ele retorna a resposta, nunca passa a fazer o segundo VLOOKUP, essa é a beleza do IFERROR. O IFERROR do Excel veio no Excel 2010, mas é claro, o problema realmente estúpido aqui é que a própria tabela está retornando um erro. Certo, alguém tinha quantidade 0, receita / quantidade, e então estamos recebendo um # DIV / 0! ali, e esse erro também será detectado como um erro pelo IFERROR. Tudo bem,e vai parecer que não foi encontrado, ele foi encontrado, só que quem construiu esta mesa não fez um bom trabalho ao construir esta mesa.
Tudo bem, escolha # 1, Excel 2013 ou mais recente, mude para a função que eles adicionaram em 2013 que não procura todos os erros, apenas procura # N / A! Ctrl + Enter, e agora obteremos o Not Found for ExcelIsFun, mas está retornando o # DIV / 0! erro. Porém, na verdade, o que deveríamos fazer é aqui nesta fórmula, deveríamos manipular esta fórmula para evitar essa divisão por zero em primeiro lugar. Então = IFERROR, isso funciona para todos os tipos de coisas, pressione a tecla End para chegar ao fim, vírgula e, em seguida, o que fazer? Eu sempre colocaria um zero aqui como preço médio.
Eu tive uma gerente uma vez, Susanna (?), “Isso não é matematicamente correto, você tem que colocar“ nm ”para não ser significativo.” Exatamente assim, é louco quanto tempo meu gerente me deixou louco com seu pedido louco, sso, vamos copiar isso, Colar fórmulas especiais, alt = "" ES F. Agora temos um erro “não significativo” aqui, o “ Não encontrado ”é encontrado pelo IFERROR, e o“ não significativo ”é na verdade o resultado do VLOOKUP. Tudo bem, então algumas maneiras diferentes de seguir, provavelmente a coisa mais segura a fazer aqui é usar IFNA, desde que você tenha o Excel 2013, e todos com quem você está compartilhando sua pasta de trabalho tenham o Excel 2013. Este livro, além de vários outros estão neste livro, está cheio de dicas picantes, 200 páginas, fácil de ler, totalmente colorido, incrível, livro incrível. Confira, clique no “I” no canto superior direito,você pode comprar o livro.
Tudo bem, recapitulação do episódio: nos velhos tempos, usávamos um formato longo = IF (ISNA (a fórmula, 0, caso contrário, a fórmula, a fórmula foi calculada duas vezes, o que é horrível para VLOOKUPs. A partir do Excel 2010, = IFERROR , basta colocar a fórmula uma vez, vírgula, o que fazer se for um erro. IFERROR, entretanto, trata # DIV / 0! erros da mesma forma que erros # N / A !, portanto, a partir do Excel 2013, a função IFNA funciona como IFERROR, mas detectará apenas os erros # N / A !.
Essa dica por sinal, sugerida pelos leitores Justin Fishman, Stephen Gilmer e Excel por Joe. Obrigado a eles por sugerir isso, e obrigado por passar por aqui, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2042.xlsm
